Database 3. SQL 개념과 각종 관련 지식

SQL(Structured Query Language)

• 현업에서 쓰이는 relational DBMS의 표준 언어
• 종합적인 database 언어 : DDL + DML + VDL
• 아래에서는 MySQL을 기준으로 설명한다.

주요 용어

SQL에서 relation이란?

• multi-set of tuples 이다.
• 중복된 tuple을 허용한다.

SQL & RDBMS

• SQL은 RDBMS의 표준 언어이지만 실제 구현에 강제가 없기 때문에
• RDBMS마다 제공하는 SQL의 스펙이 조금씩 다르다.

문법

Database 생성

CREATE DATABASE company;
USE company;
select database(); -- 사용 중인 데이터 베이스 확인

Table 생성

create table DEPARTMENT(
  id INT PRIMARY KEY,
  name VARCHAR(20) NOT NULL UNIQUE,
  leader_id INT
);

Attribute data type:숫자

Attribute data type:문자열

Attribute data type:날짜와 시간

Attribute data type:그 외

Key 선언

• 중복될 수 없으며, NULL값은 올 수 없다는 제약사항이 있다.

create table PLAYER( -- key가 attribute 하나로 구성될 때
  id INT PRIMARY KEY
);

create table PLAYER( -- key가 attribute 하나 이상으로 구성될 때
  team_id VARCHAR(12),
  back_number INT
  PRIMARY KEY(team_id, back_number)
);

UNIQUE

• UNIQUE로 지정된 Attribute는 중복된 값을 가질 수 없다.
• 단, NULL은 중복을 허용할 수도 있다(RDBMS 마다 다르다)
• 선언하는 방법은 PRIMARY KEY 선언 방식과 동일하다.

NOT NULL

• attribute가 NOT NULL로 지정되면 해당 attribute는 NULL을 값으로 가질 수 없다.
• 선언 방식이 attribute level로만 가능(attribute 여러개를 한꺼번에 선언할 수 없다.)

DEFAULT

• 저장할 때 값을 따로 명시하지 않는다면 default로 지정된 값을 정의할 때 사용한다.

CHECK

create table EMPLOYEE(
  id INT PRIMARY KEY,
  name VARCHAR(30) NOT NULL,
  birth_date DATE,
  sex CHAR(1) CHECK(sex in ('M', 'F')),
  position VARCHAR(10),
  salary INT DEFAULT 500000,
  dept_id INT,
  FOREIGN KEY (dept_id) references DEPARTMENT(id)
    on delete SET NULL on update CASCADE,
  CHECK(salary >= 500000)
)

• attribute의 값을 제한하고 싶을 때 사용

Referential integrity constraint:FOREIGN KEY

• attribute가 다른 table의 primary key나 unique key를 참조할 때 사용한다.
• 반드시 참조 테이블에 존재하고 있는 키여야 한다.
• 참조 제약 사항
  • 여기서 RESTRICT와 NO ACTION은 유사하지만 SQL 표준 스펙에서는 NO ACTION은 transaction 내에서는 참조값이 변경되는 것을 허용하지만 transaction이 끝났을 때에는 제약사항을 지키고 있으면 된다. MySQL에서는 RESTRICT와 NO ACTION이 완전히 동일하다.

constraint에 이름 명시하기

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

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

ALTER TABLE

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

기존의 테이블에 foreign key update 하기

ALTER TABLE department ADD FOREIGN KEY (leader_id) REFERENCES employee(id) on update CASCADE on delete SET NULL;

DROP TABLE

• table을 삭제할 때 사용

DROP TABLE table_name;

database 구조를 정의할 때 중요한 점

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