[MySQL] 선택학습, DB 연결 및 테이블 생성

Dev/SQL

[MySQL] 선택학습, DB 연결 및 테이블 생성

syuare 2025. 4. 4. 20:14

오늘은 대부분 사전캠프 퀘스트 문제를 푸는데 시간을 대부분 사용했다.

어떤 문제였는지는 아래의 링크에서 확인할 수 있다.

SQL 퀘스트 - 연습 문제를 풀어보자

'Dev/Practice - SQL' 카테고리의 글 목록

syuare 님의 블로그 입니다.

syuare.tistory.com

현재 날짜 / 시간 함수

*5) 공부하다보니 팀 프로젝트 시간이 왔어요! - ㅁㅁㅁ테이블에서 현재 진행중인 프로젝트를 선택하는 쿼리 풀이 중

SQL에서 현재 날짜와 시간을 데이터로 가져오고 싶다면 아래의 함수를 사용해보자.

Now(), current_timestamp() : 현재 날짜 + 시간
curdate(), current_date(): 현재 날짜
curtime(), current_time(): 현재 시각 반환

-- 2025-04-04 14:56 기준
Now()
current_timestamp(),
curdate()
current_date()
curtime()
current_time()

DB 연결 / TABLE 생성

지금까지는 강의대로 SQL 쿼리문 작성해서 존재하는 데이터를 조회만 했었는데, 연습문제를 풀려고 보니 해당 데이터가 있는 테이블도 없고 머릿속으로만 쿼리를 만들려니, 만든 쿼리가 잘 작동하는지 알 수 없다.

그래서 무작정 나만의 데이터베이스를 생성해서 테이블을 만들어 보려고 했는데,

단순히 MySQL 쿼리를 작성할 수 있는 프로그램만 설치한다고 해서 바로 DB 생성, 자기만의 TABLE을 생성 및 활용을 할 수 없었다.

(정확히는 신규 데이터베이스를 생성하더라도 연결이 안된다.)

아래의 오류를 해결하려고 여러 방법을 찾아봤는데 몇 시간동안 헛수고를 한 상황..

DBeaver Error - Communications link failure

새 데이터베이스(로컬 서버)연결을 하려면 MySQL 서버 부터 설치해야 한다.

https://dev.mysql.com/downloads/mysql/

MySQL :: Download MySQL Community Server

Select Version: 9.2.0 Innovation 8.4.4 LTS 8.0.41 Select Operating System: Select Operating System… Microsoft Windows Ubuntu Linux Debian Linux SUSE Linux Enterprise Server Red Hat Enterprise Linux / Oracle Linux Fedora Linux - Generic Oracle Solaris mac

dev.mysql.com

설치 과정에서 root 비밀번호 설정이라던지 여러 설정을 하는데, 그것은 구글링을 해보자

설치 후 root 비밀번호 입력 후 로컬 서버의 새 데이터베이스에 정상적으로 연결되었다.

그리고 새로운 DB도 생성해주었다.

테이블 데이터는 Excel의 데이터를 csv 파일로 가져오면 되지만, 직접 테이블을 생성하고 데이터를 입력할 수도 있다.

신규 테이블 생성

테이블 생성 시에는 컬럼명과 해당 컬럼명이 가지는 자료형을 정해줘야한다.

#기본 구조
create table '테이블 명'(
	'컬럼명1', '자료형' PRIMARY KEY,
	'컬럼명2', '자료형' [옵션], 
	...
);

#예시
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    product VARCHAR(100),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

PRIMARY KEY? 옵션? 이게 무엇일까?

옵션	설명
UNIQUE	해당 열에 중복된 값 입력 불가
NOT NULL	NULL 값 허용 안됨
PRIMARY KEY	기본키로 설정 (UNIQUE + NOT NULL)
FOREIGN KEY	다른 테이블과의 연결 칼럼
DEFAULT	값을 넣지 않을 경우 기본 값으로 설정
CHECK	값이 특정 범위 안에 들어오도록 설정 e.g. CHECK (AGE >= 10)
AUTO_INCREMENT	행이 추가될 때마다 자동으로 고유한 값을 가지도록 설정

위와 같이 테이블 생성 시 각 컬럼명/자료형과 함께 명시하는 옵션은 SQL DB 설계할 때 고려해야 하는 중요한 요소이다.

무결성 유지, 검색 성능 향상, 업데이트/삭제 용이 등

옵션에 대해 조금 더 자세히 알아봅시다.

PRIMARY KEY(PK)란?

테이블에서 각 데이터를 유일하게 식별할 수 있는 기준 제공
테이블 내 절대 중복될 수 없어야 하는 값이고, NULL이 될 수 없는 컬럼에 설정
예시
- 학번
- 주민등록번호
- 시스템에서 자동 부여하는 'id'

#단일 컬럼을 Primary Key로 설정
CREATE TABLE employees (
  employee_id INT NOT NULL,
  name VARCHAR(100),
  PRIMARY KEY (employee_id)
);

#여러 컬럼을 묶어서 Primary Key로 설정
CREATE TABLE orders (
  order_id INT NOT NULL,
  product_id INT NOT NULL,
  quantity INT,
  PRIMARY KEY (order_id, product_id) -- 복합 PK
);

PRIMARY KEY, 왜 필요할까?

데이터 무결성
- 테이블에서 같은 PK(주 키)를 가진 레코드가 여러 개 생기면, 어느 게 진짜인지 식별하기가 어려워진다.
- 해당 옵션을 통해 항상 “이 행은 유일하게 식별 가능하다”라는 조건을 설정할 수 있다.
조회 및 수정 용이
- “어떤 학생(id=12345)을 찾아서 이름을 변경한다”처럼, PK를 기준으로 특정 레코드를 검색하면 빠르고 정확하다.
- PK 컬럼에는 인덱스가 기본적으로 적용되므로, 쿼리 성능도 좋아진다.
관계 설정 (Foreign Key)
- 다른 테이블에서 이 테이블을 참조할 때, PK를 기준으로 ‘참조 무결성’을 유지된다.
- 예: 주문 테이블에서 “user_id”가 유저 테이블의 PK “id”를 참조.

AUTO_INCREMENT 옵션은 왜 쓰는 걸까?

AUTO_INCREMENT 옵션은

새로운 데이터가 입력될 때 MySQL이 자동으로 유일한 숫자 값을 증가시켜 저장해주는 옵션

CREATE TABLE users (
  user_id INT AUTO_INCREMENT,
  username VARCHAR(50),
  PRIMARY KEY (user_id)
);

-- 데이터가 입력될때 마다 user_id가 1부터 시작해서 자동으로 증가됨
-- user_id의 별도 입력이 필요없어짐

편의성
- 고유한 아이디를 자동 부여해주므로, 사람이 실수할 여지를 줄입니다.
- 매번 ‘다음 번호’를 추적할 필요가 없습니다.
유니크(중복 없음)
- 동일한 숫자 값이 두 번 들어가는 것을 막아주어, 자연스럽게 PK 역할을 가능
일관된 관리
- 데이터가 삭제되어도, 이미 발급된 번호는 다시 사용하지 않는 것이 일반적임(기본 설정).
- 한 번 발급된 ID는 서비스를 통틀어 고유값이므로, 추적과 감사 기록에 유용함

AUTO_INCREMENT 옵션의 주의할 점

보통 INT 자료형에만 설정하지만, 필요하면 BIGINT 등 더 큰 정수에도 설정 가능함
하나의 테이블에서 보통 1개 컬럼만 AUTO_INCREMENT 옵션 사(두 개 동시에 사용은 불가능하거나 의미가 모호해짐)
기본적으로 1부터 시작해서 1씩 증가하지만, ALTER TABLE … AUTO_INCREMENT = N; 으로 초기값이나 증가값 조정 가능.
- AUTO_INCREMET 옵션을 설정해둔 테이블에서 특정 데이터를 삭제한 후 다시 데이터 입력을 할 경우, 동일한 숫자 값이 두 번 들어갈 수 없기 때문에 삭제된 데이터는 다시 값으로 사용할 수 없음

ALTER TABLE '테이블명' AUTO_INCREMENT = '원하는 숫자'

PRIMARY KEY, AUTO_INCREMENT KEY 같은 것 없이
그냥 컬럼명, 자료형만 설정해주면 안되나?

"해도 된다"

다만 아래의 이유로 인해서 설정해주는 것이 좋다

중복 방지 & 식별 목적
- 컬럼/자료형만 설정해주어도 상관은 없으나, 중복된 값이 있을 경우 구분이 불가능하다.
- 이를 위해 PRIMARY KEY나 AUTO_INCREMENT처럼 고유 식별 컬럼으로 옵션을 지정해주면 구분이 가능해진다.
DB 설계의 기초
- 관계형 DB(RDBMS)에서 관계를 설정하고 무결성을 유지하기 위해서는 PK가 핵심이 된다.
  - 나중에 다른 테이블과 연결(FOREIGN KEY)할 때 해당 테이블의 대표 컬럼을 반드시 정해둬야 명확해짐

FOREIGN KEY

한 테이블의 칼럼이 다른 테이블의 Primary key 혹은 Unique Key를 참조하도 설정하는 것.

한 테이블에 여러 Foreign Key 가능하다

CREATE TABLE orders (
  order_id INT AUTO_INCREMENT,
  user_id INT,
  product_id INT,
  PRIMARY KEY (order_id),
  FOREIGN KEY (user_id) REFERENCES users(user_id)
);

다른 테이블의 참조 컬럼이 실제로 해당 데이터 값이 있어야 의미가 있음

Foreign Key 설정 시, 참조 대상 테이블에 없는 값은 넣으려 해도 에러가 발생하게 되어, 실수 방지 가능!

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    product VARCHAR(100),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- orders.user_id가 users.id를 참조(Foreign Key).
-- orders.user_id에 users 테이블에 없는 값이 들어갈 수 없음.

UNIQUE KEY

해당 옵션의 컬럼에는 중복된 값이 존재할 수 없다! 를 설정하는 것

*Primary Key와 비슷하게 중복을 허용하지 않는다는 점이 같으나,

NULL에 대한 처리나 테이블 내 복수 선언 가능 여부에서 차이가 있음

Primary Key vs Unique Key

NULL 허용 여부
- Primary Key는 NULL이 불가능하지만,
- Unique Key는 DB 엔진 설정에 따라 NULL을 허용할 수도 있(일반적으로 한 번만 허용)
테이블 내 여러 개 선언 가능
- Primary Key는 한 테이블에 오직 1개
- Unique Key는 여러 개 둘 수 있

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) UNIQUE,
    name VARCHAR(100)
);

데이터 입력

INSERT INTO '테이블명' ('컬럼1', '컬럼2') VALUES (데이터1, 데이터2);

데이터 삭제

Delete from '테이블명'
where '조건' #where 조건문 없으면 모든 데이터 삭제

테이블 삭제

Drop table '테이블명';

데이터 수정

update '테이블명' set '수정할 컬럼' = '값'
where '조건식'  #where 조건문 없다면 모든 데이터 수정

Appendix. AUTO_INCREMENT 옵션이 있는 테이블에서 데이터를 삭제할 경우

AUTO_INCREMENT 옵션은 자동으로 숫자가 증가하는 옵션이다.

그렇다면 만약 해당 옵션을 사용한 테이블에서 데이터를 삭제할 경우 어떻게 될까?

당연히 삭제한 데이터 값이 없이 조회된다.

auto_increment 값을 다시 재정렬하려면 어떻게 해야할까?

물론 auto_increment 값을 초기화해주면 된다.

그러나 중요한 문제가 발생!

auto_increment 초기화 방법인 'alter table' 함수 사용 방법은 초기값 혹은 증가만 가능하다??

????

실제로 그런지 확인해보자!

-- Alter table/테이블 변경
-- (부연설명) 데이터를 총 id 7까지 추가했다가 5,6,7을 삭제한 상황

alter table lol_users auto_increment = 5 -- alter table 을 이용해서 auto increment 값을 5로 변경

insert into lol_users(name, region, rating, join_date) value ('구구이', '한국', 1400, '2021-01-07');
insert into lol_users(name, region, rating, join_date) value ('구구이', '한국', 1400, '2021-01-07');
-- 위와 같이 2개의 데이터를 추가 입력

이를 통해 알 수 있었던 사실은

AUTO_INCREMENT를 초기화(변경)할 때는

변경할 AUTO_INCREMENT의 값이 해당 컬럼에서 가장 큰 값보다 커야한다.

삭제한 데이터의 auto_increment 값을 사용하려면 어떻게 해야할까?

auto_icrement 옵션을 가진 컬럼의 값을 재정렬할 필요가 있다.

set @count = 0; -- 변수 count 값을 0으로 설정
update '테이블명' set '컬럼명' = @count:= @count+1; -- auto_increment 컬럼의 값을 1로 재정렬

auto_increment 컬럼값이 재정렬된 것을 확인했으니,

alter table 함수를 통해 원하는 숫자로 변경 후 데이터를 넣어보자.

# id 컬럼 값의 최대 값이 6인 것을 확인했으니 7로 설정
alter table lol_users auto_increment = 

insert into lol_users( name, region, rating, join_date) value ('르탄이', '한국', 1300, '2019-06-15');

다시 auto_increment 값이 잘 정렬된 것을 확인할 수 있었다.

Appendix2. csv 파일 import 시 인코딩 오류에 대해

테이블을 직접 생성 및 데이터 입력할 필요없이 외부에서 가져올 수 있다 (import)

바로 csv 파일을 가져오는 것!

다만 가져올 때 문제가 발생할 수 있는데....

아래와 같이 한글과 같은 값의 경우 정상적으로 가져오지 못할 수 있다.

이때 쉽게 해결하는 방법은 데이터를 가져올 때,

인코딩 옵션이 utf-8로 설정되어 있다면 euc-kr로 변경해주면 해결된다.