[데이터베이스] 3장 - SQL 개념과 SQL로 데이터베이스를 정의하는 법

✅ SQL 뜻?

: Structured Query Language 로, 현업에서 쓰이는 relational DBMS 의 표준 언어이다.

: 종합적인 database 언어 : DDL + DML + VDL  

✅ SQL 기본 개념 및 용어

relational data model	SQL
relation	table
attribute	column
tuple	row
domain	domain

 

- SQL 에서 relation 은 multiset(=bag) of tuples 를 허용한다. 즉, 중복된 tuple을 허용한다는 뜻이다. 기존 relaional data model 의 relation 과는 다른 점이다.

- SQL은 RDBMS 의 표준 언어이지만, 실제 구현에 강제가 없기 때문에 RDBMS마다 제공하는 SQL의 스펙이 조금씩 다르다. Postgresql, MySQL, Oracle 등에서 다루는 문법이 미세하게 다른 이유다.

✅ MySQL 로 예제를 통해 DB 정의하기

- IT 회사 관련 RDB 만들기

: 부서, 사원, 프로젝트 관련 정보들을 저장할 수 있는 관계형 데이터베이스를 만들자.

: 사용할 RDBMS는 MySQL(InnoDB)

SHOW DATABASES, CREATE DATABASE company

 

company 라는 데이터베이스를 만들었다. 이제 어떤 database를 쓸건지에 대한 명령어를 써보자. MySQL에서 지금 선택된 database가 무엇인지 알고싶을 때 명령어는 아래와 같다.

SELECT databse();

 

NULL 이 나온 걸 보면, 아직 어떠한 database도 선택되지 않은 것을 알 수 있다. 내가 이제 사용하고 싶은 database를 지정하고 싶을 땐, 어떻게 해야 할까?

USE company; SELECT databse();

이렇게, USE 라는 키워드를 사용해서 company 라는 데이터베이스를 사용한다고 하고 다시 확인해보면, 현재 company라는 DB가 활성화되어 있는 것을 확인할 수 있다.

그리고, 현재 바라보고 있는 database를 지우고 싶다하면, 아래와 같이 명령어를 치면 된다.

DROP DATABASE company; SELECT databse();

 

- MySQL 에서는 DataBase 와 Schema 가 같은 뜻을 의미한다. 즉, CREATE DATABASE company = CREATE SCHEMA company 가 같은 의미라는 것이다.

- 물론, 다른 RDBMS 에서는 의미가 다르게 쓰인다. 예를 들어, PostgreSQL 에서는 SCHEMA 가 DATABASE의 namespace를 의미한다.

- 즉, MySQL에서는 데이터베이스 안에서 테이블이 정의가 된다면, 다른 RDBMS 에서는 데이터베이스 안에 스키마가 정의가 되고 그 스키마 안에서 테이블이 정의가 된다고 이해하면 된다.

✅ Table 만들기

: 부서, 사원, 프로젝트 관련 정보들을 저장할 수 있는 관계형 데이터베이스

 

먼저, 스키마를 정의해보자.

IT 회사 데이터베이스의 스키마르 정의

- 참조관계를 살펴보자.

• DEPARTMENT 부터 살펴보면, leader_id 가 EMPLOYEE 의 id 를 참조한다.
• EMPLOYEE 에서는, dept_id 가 DEPARTMENT 의 id 를 참조한다.
• PROJECT 에서는, leader_id 가 EMPLOYEE 의 id 를 참조한다.
• WORKS_ON 에서는, eml_id 가 EMPLOYEE 의 id 를 참조하고, proj_id 는 PROJECT 의 id 를 참조한다. 

그럼 해당 스키마를 가지고 테이블을 생성해보자.

create table DEPARTMENT

지금은 터미널에서 생성해서 그렇지, 한 줄 한 줄 개행을 안해도 된다. 

순서는 우선, create table 테이블이름 으로 테이블을 생성해주고, 각 attribute 와 데이터타입을 정해주고, PK 면 PK 같은 것을 지정해주면 된다.

: 이 때, PK 두 개 이상이라면, PRIMARY KEY (team_id, back_number)  와 같은 형식으로 지정해준다. 물론 1 개여도, PRIMARY KEY (team_id) 라고 써줄 수 있다.

 

: UNIQUE 로 지정된 attribute는 중복된 값을 가질 수 없다. 단, NULL은 중복을 허용할 수 있다. 이것도 RDBMS 마다 다르다. UNIQUE 를 선언하는 방법도 PRIMARY 키를 선언하는 것처럼, UNIQUE(team_id, back_number) 와 같은 형식으로 가능하다.

 

: NOT NULL 로 지정된 attribute 는 말그대로 NULL 값을 가질 수 없다.

---

 

이제, EMPLOYEE 테이블을 만들어보자.

create table EMPLOYEE

 

여기서 DEFAULT 는 새로운 tuple 을 저장할 때, 해당 값이 없다면 default 값으로 저장한다는 의미이다. 즉, EMPLOYEE 테이블에 새로운 직원을 추가할 때, id, name, birth_date 값을 넣어줬는데 따로 salary 값은 넣어주지 않았다면, 기본값으로 50000000 이 들어간다.

: DEFAULT 를 선언하는 방식은 'DEFAULT 디폴트값' 순서로 적어준다.

 

: CHECK 는 attribute의 값을 제한하고 싶을 때 사용한다. CHECK에 걸리는 값은 들어가지 않는다. 선언하는 방식은 'CHECK(age >=20)' 이나 'CHECK(start_date < end_date)' 와 같은 형식으로 지정해준다. start_date 나 end_date 는 같은 테이블에 있는 속성값이다.

 

: FOREIGN KEY(Referential integrity constraint) 는 attribute 가 다른 table 의 PK 나 Unique Key 를 참조할 때 사용한다. 선언하는 방식은 아래와 같다.

create table Employee(
    ...
    dept_id INT,
    FOREIGN KEY (dept_id)
        references DEPARTMENT(id)
        on delete reference_option
        on update reference_option
);

 

현재, dept_id 는 FOREIGN KEY 에 해당한다. 지금은 attribute 가 하나지만, 2개 이상이라면, 괄호 안에다가 나열하면 된다. 그리고, 어떤 테이블을 참조하는지 적어줘야 하고 그 테이블에서도 어떤 attribute 를 참조하는지 적어줘야 한다. (references DEPARTMENT(id)) 

 

이 때,그 참조하고 있던 값이 다른 테이블에서 삭제되거나 업데이트 됐을 때, 어떻게 하면 좋을지에 대해서도 정해줘야 한다.

(on delete reference_option)(on update reference_option)

reference_option	설명
CASCADE	참조값의 삭제/변경을 그대로 반영
SET NULL	참조값이 삭제/변경 시 NULL 로 변경
RESTRICT	참조값이 삭제/변경되는 것을 금지
NO ACTION	RESTRICT와 유사
SET DEFAULT	참조값이 삭제/변경 시 default 값으로 변경

그 reference_option 이 위 표와 같다. 

SQL 표준 스펙에서는 총 5가지의 옵션이 있는데, 하나씩 살펴보자.

• CASCADE : 참조값이 사라지면, 해당 테이블에서도 그 값이 사라진다. 변경되면 그대로 변경된다.
• SET NULL : 참조값이 삭제되거나 변경되면, 그냥 NULL 로 바뀐다.
• RESTRICT : 참조값이 삭제,변경 되는 것을 금지해버린다. 
• NO ACTION : RESTRICT 과 유사하다. (MySQL 에서는 RESTRICT와 완전히 동일한 반면, 다른 RDBMS 에서도 거의 유사한데, 한 트랜잭션에서 여러개의 SQL 문이 실행되는 동안에는 참조값이 삭제/변경 되는 것을 허용을 하지만, 그 트랜잭션이 끝났을 때, referential integrity constraint를 위반하고 있다면, 이것을 금지한다는 것을 의미한다.)
• SET DEFAULT : 참조값이 삭제,변경 되면 해당 값은 DEFAULT 값으로 바뀐다. (MySQL 에서는 지원을 하지 않는다. PostgreSQL은 5가지 모두 지원한다.)

---

나머지 테이블도 빠르게 완성해보자.

create table PROJECT

create table WORKS_ON

 

아직 다 끝나지 않았다. 사실 아까 맨 처음에 DEPARTMENT 테이블을 만들 때, leader_id 에 대해서 FOREIGN KEY를 걸어주지 못했다.

leader_id 값에 참조키 못 걸어줬었음.

왜냐하면, DEPARTMENT 테이블을 만들 시점에는 아무런 테이블도 없었기 때문에, 참조할 수가 없었기 때문이다.

이제 EMPLOYEE 테이블이 생겼으니까 FK를 추가해줘야 한다.

지금 상황은 테이블이 만들어진 뒤에, 테이블의 스키마를 변경하는 것이므로 아래와 같이 ALTER 를 사용해야 한다.

ALTER TABLE department ADD FOREIGN KEY(leader_id) REFERENCES employee(id)

 

순서는 ALTER TABLE 을 먼저 써주고, 변경해야하는 테이블 이름을 적어준 뒤, MySQL 에서는 FOREIGN KEY 라고 적고 해당 속성값을 적어준다. 그리고 참조할 테이블(속성값)을 적어주면 된다. 

즉, ALTER TABLE 은 테이블이 생성되고 난 뒤에, 테이블의 스키마 변경이 필요할 때 사용되는 SQL 이라고 이해하면 된다.

 

※ ALTER TABLE : table의 스키마를 변경하고 싶을 때 사용

유형	MySQL 예제
attribute 추가	ALTER TABLE employee ADD blood VARCHAR(2);
attribute 이름 변경	ALTER TABLE employee RENAME COLUMN phone TO phone_num;
attribute 타입 변경	ALTER TABLE employee MODIFY COLUMN blood CHAR(2);
table 이름 변경	ALTER TABLE logs RENAME TO backend_logs;
primary key 추가	ALTER TABLE log ADD PRIMARY KEY(id);
...	...

 

이 때, 이미 서비스 중인 table 의 schema 를 변경하는 것이라면, 변경 작업 때문에 서비스의 백엔드에 영향이 없을지 검토한 후에 변경하는 것이 중요하다.

(실무에서 ALTER 쓰고 커밋은 안했던걸로 기억한다. 롤백 필수였다.)

✅ Constraints 이름 명시하기

: 이름을 붙이면 어떤 constraint 를 위반했는지 쉽게 파악할 수 있다. constraints 를 삭제하고 싶을 때, 해당 이름으로 삭제 가능하다.

create table TEST (
    age INT CONSTRAINT age_over_20 CHECK(age > 20)
);

 

위와 같은 예시를 살펴보면, age라는 속성이 20 이 넘어가는 constraint 이름을 'age_over_20' 이라고 명시해줬다.

- CONSTRAINT age_over_20 이름을 붙였을 때 → Check constraint 'age_over_20' is violated.

- CONSTRAINT age_over_20 이름을 생략했을 때 → Check constraint 'test_chk_1' is violated.

라고 뜬다. 즉, 어떤 제약조건을 위반했는지 알 수 있어서 디버깅하기 쉽다.

(※ MySQL 에서 'test_chk_1' 이 어떤 것을 의마하는지 알고 싶다면, show create table test_chk_1를 하면 어떤 것을 의미하는지 알 수 있다.)

✅ Table 삭제하기

: table을 삭제할 때는 DROP 키워드를 사용한다.

: DROP TABLE table_name;

✅ DB 구조를 정의할 때 중요한 점

: 만들려는 서비스의 스펙과 데이터 일관성, 편의성, 확장성 등등을 종합적으로 고려하여 DB 스키마를 적절하게 정의하는 것이 중요하다.

 

 

show tables;

---

참고자료

- 유튜브 쉬운코드