[DB] 쉬운코드 SQL개념과 자료형 및 Constraint

SQL

• Relational DBMS의 표준언어
• DDL+DML+VDL 모두 제공
• Reloational data model 용어 → SQL 용어

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

• SQL에서 Relation은?
  • (중복가능한) tuple의 집합

Database VS Schema

• MYSQL에서는 Database와 schema는 같은 의미
  • ex) CREATE DATABASE company = CREATE SCHEMA company
• BUT DBMS마다 다르긴 함
  • ex) PostgreSQL에서는 schema가 DATABASE 내의 namespace를 의미

attribute data type : 숫자

• cf) 표준 SQL ↔ MYSQL 기준은 조금씩 다를 수 있음
  • ex) MYSQL에서는 DECIMAL, NUMERIC모두 정확하게 해당 자리수까지 표기하나,
    표준MYSQL에서는 DECIMAL은 조금 더 크게(유연하게) 저장한다.

attribute data type : 문자열

• 고정크기 문자열 : CHAR(N)
  • 저장된 문자열의 크기가 N보다 작으면 나머지를 SPACE로 채워서 저장
• 가변크기 문자열: VARCHAR(N)
  • 저장될 문자열의 길이만큼만 저장
  • CF) VARCHAR이 더 좋아보일 수 있지만 DBMS마다 어떻게 구현했느냐에 따라 다름
    • ex) PostgreSQL에서는 VARCHAR를 권장 BUT, MYSQL에서는 저장공간 상에서 이점은 있지만, 시간 성능상에서는 더 안 좋다고 함.
• 사이즈 큰 문자열
  • TINYTEXT, TEXT, MEDIUMTEXT,LONGTEXT
  • cf) MYSQL에서 TINYTEXT, TEXT는 VARCHAR과 동일하거나 작은 양만 저장 가능

날짜와 시간

• 날짜 : DATE
  • 년,월,일을 저장
  • -YYYY-MM-DD
• 시간 : TIME
  • 시,분,초 저장
  • -hh:mm:ss or -hhh:mm:ss
  • 경과된 시간을 표현하고 싶을때 hhh(세자리)로 표현
• 날짜와 시간: DATETIME, TIMESTAMP
  • 날짜와 시간을 표현
  • TIMESTAMP는 TIMEZONE이 반영됨
    • TIMESTAMP는 UTC(표준시간대)로 저장
    • MYSQL 서버 TIMEZONE 또는 TIMEZONE의 임의 설정시간으로 저장
    • TIMEZONE 시간 대비 UTC로 변환해서 표준시간대로 저장.
    • 읽어올때도 현재의 TIMEZONE으로 변환해서 값을 가져옴
  • 또, DATETIME의 범위와 TIMESTAMP의 범위가 다름
  • 하단 그림 참조

그 외

• byte-string
  • BINARY,VARBINARY, BLOB type
  • 보안과 관련된 암호화
• boolean
  • MYSQL에서는없음
  • TINYINT로 대체하여 저장
• 위치관련 : GEOMETRY
• JSON : JSON 형태 저장

PRIMARY KEY

• 하나 이상의 attribute로 구성 가능
• 중복 불가, NULL 불가
• 하나의 PK로 구성될 때와 하나 이상의 PK로 구성될 때 DDL이 다름
• 예시

// 1. 하나의 PK로 구성될 때(2와 같은 방식도 가능함)
CREATE TABLE PLAYER(
id int PRIMARY KEY
name ...
);

// 2. 하나 이상의 PK로 구성될 때
CREATE TABLE EMPLOYEE(
team_id int VARCHAR(10),
back_number INT,
...
PRIMARY KEY(team_id, back_number)
);

UNIQUE KEY

• UNIQUE KEY로 지정된 attribute(S)는 중복된 값을 가질 수 없다.
• NULL은 중복 허용할 수도 있음(DBMS마다 다름)
• 정의 방식은 PK와 동일

NOT NULL

• NULL 불가
• 자료형 다음에 선언해야함
• UNIQUE와 같이 이용되는 경우 많음

CREATE TABLE STUDENT(
phone_number INT NOT NULL UNIQUE
);

DEFAULT

• attribute의 default값을 정의할때 사용
• 새로운 tuple을 저장할 때 값이 없으면 default value로 저장

CREATE TABLE ORDERS(
menu varchar(15) DEFAULT '짜장면'
);

CHECK Constraint

• attribute의 값을 제한할때 사용
• PK와 마찬가지로 attribute 하나로 구성될 때와 하나 이상으로 구성될때가 다름

// 하나로 구성될 때
CREATE TABLE EMPLOYEE(
age INT CHECK(age>=20)
);

// 하나 이상으로 구성될 때
CREATTE TABLE PROJECT(
start_date DATE,
end_date DATE,
CHECK(start_date<end_date));

FOREIGN KEY(Referential integrity constraint)

• attribute(s)가 다른 table의 PK나 UNIQUE KEY를 참조할 때 사용
• 한 table이 다른 table의 Pk를 참조하여 FK 관계를 생성할때, 참조되는 Table의 attribute(PK)가 반드시 존재해야함.
• 참조하고 있는 값이 어떻게 되었을때 어떤 옵션을 줄 지 아래와 같이 설정 가능.

CREATE TABLE EMPLOYEE(
dept_id INT,
...
FOREIGN KEY(dept_id) references DEPARTMENT(id)
on delete SET NULL(reference_option)
on update CASCADE(reference_option)
);

• reference_option
  1. CASCADE
     • 참조 값의 삭제,변경을 그대로 반영
  2. SET NULL
     • 참조값의 삭제,변경을 NULL로 변경
  3. RESTRICT
     • 참조값이 삭제,변경되는 것을 금지
  4. NO ACTION
     • RESTRICT와 유사
  5. SET DEFAULT(MYSQL 지원 x)
     • 참조값이 삭제,변경 시 default 값으로 변경

Constraint 이름 명시

• 이름을 붙이면 어떤 Constraint를 위반했는지 파악 가능
• constraint를 삭제하고 싶을 때 해당 이름으로 삭제 가능

CREATE TABLE TEST(
age INT CONSTAINT age_over_20 CHECK(age>20)
);

ERR

• age>20을 불만족하는 데이터를 INSERT할경우 DBMS는 아래와 같은 ERROR 메시지를 발생

💡 CHECK constraint ‘age_over_20’ is violated

ALTER TABLE

• 필요한 내용은 RDMBS 문서 찾아서 그때그때 활용해도 OK

• 이미 서비스 중인 table의 schema를 변경하는 것이라면 변경 작업때문에
  서비스의 영향이 없을지 검토 후에 변경 필요

DROP TABLE

• Table 삭제 명령어

참고
쉬운코드 데이터베이스