* 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 @ SQL
- 중복된 tuple을 허용한다.
SQL & RDBMS
- SQL은 RDBMS의 표준 언어이지만 실제 구현에 강제가 없기 떄문에
RDBMS마다 제공하는 SQL의 스펙이 조금씩 다르다.
[ 예제를 통해 SQL로 DB 정의하기 ]
IT 회사 관련 RDB 만들기
- 부서, 사원, 프로젝트 관련 정보들을 저장할 수 있는 관계형 데이터베이스를 만들자.
- 사용할 RDBMS는 MySQL (InnoDB)
[ cmd에서 실행 ]
C:\Users>cd ..
C:\>cd C:\Program Files\MySQL\MySQL Server 8.0\bin
C:\Program Files\MySQL\MySQL Server 8.0\bin>mysql -u root -p
이후에 패스워드 입력하면 실행된다.
> SHOW DATABASES;
> CREATE DATABASE company;
> SHOW DATABASES;
=> company라는 이름의 database 생성 확인 가능.
> SELECT database();
=> 아직 어떤 database를 사용할 지 지정이 되지 않아 NULL.
> USE company;
> SELECT database();
> DROP DATABASE company;
=> database를 지울 때.
DATABASE vs SCHEMA
- MySQL에서는 DATABASE와 SCHEMA가 같은 뜻을 의미
- CREATE DATABASE company = CREATE SCHEMA company
- 다른 RDBMS에서는 의미가 다르게 쓰임
i.g.) PostgreSQL 에서는 SCHEMA가 DATABASE의 namespace를 의미
[ Table 정의하기 ]
IT 회사 RDB 만들기 _ 부서, 사원, 프로젝트 관련 정보들을 저장할 수 있는 관계형 데이터베이스
=> 스키마 정의
DEPARTMENT 테이블 생성
> CREATE TABLE DEPARTMENT(
-> id INT PRIMARY KEY,
-> name VARCHAR(20) NOT NULL UNIQUE,
-> leader_id INT
-> );
여기에서 사용된 데이터 타입 / PRIMARY KEY / NOT NULL / UNIQUE에 대해서 알아보자~
[ attribute data type : 숫자 ]
=> DECIMAL (percision, scale) >> (총 자릿수, 소숫점 이하 자릿수)
* SQL 표준에서는 DECIMAL 과 NUMERIC 은 차이가 있음 ( NUMERIC 이 기준이 더 엄격 )
MySQL에서는 둘다 엄격 (percision으로 정해진 자릿수 만큼만 저장)
[ attribute data type : 문자열 ]
* PostgreSQL에서는 VARCHAR를 쓰도록 권장,
MySQL에서는 성능문제로 문자열이 고정되어 있는 경우에는 CHAR를 쓰지만, 아니라면 VARCHAR 권장.
* PostgreSQL에서는 사이즈가 큰 문자열 => TEXT 하나
MySQL에서는 VARCHAR보다 더 큰 문자열인 경우엔 MEDIUMTEXT or LONGTEXT 사용.
[ attribute data type : 날짜와 시간 ]
* 경과된 시간 표시 가능 hhh : mm : ss
* MySQL에서 TIMESTAMP는 MySQL 내부 타임존의 영향을 받는다.
[ attribute data type : 그 외 ]
[ Key constraints : PRIMARY KEY ]
- primary key : table의 tuple을 식별하기 위해 사용, 하나 이상의 attribute(s)로 구성.
- primary key는 중복된 값을 가질 수 없으며, NULL도 값으로 가질 수 없다.
* primary key를 선언하는 방법
- attribute 하나로 구성될 때
> create table PLAYER(
-> id INT PRIMARY KEY,
-> .......
-> );
- attribute 하나 이상으로 구성될 때 (하나여도 이런 형태 가능)
> create table PLAYER(
-> team_id Varchar(12),
-> back_number INT,
-> ........
-> PRIMARY KEY ( team_id , back_number )
-> );
[ Key constraints : UNIQUE ]
- UNIQUE로 지정된 attribute(s)는 중복된 값을 가질 수 없다.
- 단, NULL은 중복을 허용할 수도 있다. (RDBMS 마다 다름)
* unique key를 선언하는 방법
- attribute 하나로 구성될 때
> create table PLAYER(
-> id INT UNIQUE,
-> .......
-> );
- attribute 하나 이상으로 구성될 때 (하나여도 이런 형태 가능)
> create table PLAYER(
-> team_id Varchar(12),
-> back_number INT,
-> ........
-> UNIQUE ( team_id , back_number )
-> );
[ NOT NULL constraint ]
- attribute가 NOT NULL로 지정되면 해당 attribute는 NULL을 값으로 가질 수 없다.
* NOT NULL 을 선언하는 방법
> create table Student(
-> .......
-> phone_number INT NOT NULL UNIQUE,
-> .......
-> );
보통 NOT NULL 과 UNIQUE 를 함께 자주 씀.
EMPLOYEE 테이블 생성
mysql> 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 50000000,
-> dept_id INT,
-> FOREIGN KEY(dept_id) references DEPARTMENT(id)
-> on delete SET NULL on update CASCADE,
-> CHECK(salary>=50000000)
-> );
여기에서 사용된 DEFAULT / CHECK / FOREIGN KEY 에 대해서 알아보자~
[attribute DEFAULT]
- attribute의 default값을 정의할 때 사용.
- 새로운 tuple을 저장할 때 해당 attribute에 대한 값이 없다면 default 값으로 저장.
* DEFAULT 를 선언하는 방법
> create table Orders (
-> .......
-> menu varchar(15) DEFAULT '짜장면',
-> .......
-> );
[CHECK constraint]
- attribute의 값을 제한하고 싶을 때 사용.
* CHECK 를 선언하는 방법
- attribute 하나로 구성될 때
> create table EMPLOYEE(
-> .......
-> age INT CHECK (age >= 20),
-> .......
-> );
- attribute 하나 이상으로 구성될 때 (하나여도 이런 형태 가능)
> create table PROJECT(
-> start_date DATE,
-> end_date DATE,
-> ........
-> CHECK ( start_date < end_date )
-> );
[ Referential integrity constraint : FOREIGN KEY ]
- attribute(s)가 다른 table의 primary key나 unique key를 참조할 때 사용.
* FOREIGN KEY 를 선언하는 방법
on delete reference_option => 참조값이 삭제되었을 때의 옵션 지정
on update reference_option => 참조값이 변경되었을 때의 옵션 지정
* 표준 SQL에서는 RESTRICT 와 NO ACTION에 차이가 있음 (MySQL에서는 유사)
한 트랜잭션 내에 여러 SQL 문장이 실행 될 때는 참조 값의 변경/삭제를 허용하지만,
트랜잭션이 끝난 이후에는 허용하지X
* MySQL에서는 CASCADE, SET NULL, RESTRICT 세가지
PostgreSQL 에서는 다섯가지 모두 지원.
[ constraint : 이름 명시하기 ]
- 이름을 붙이면 어떤 constraint를 위반했는지 쉽게 파악할 수 있다.
- constraint를 삭제하고 싶을 때 해당 이름으로 삭제 가능.
* MySQL에서 constraint 이름을 생략한 경우 위반사항을 알고싶다면
> show create table 테이블이름 => 테이블 확인 가능
PROJECT 테이블 생성
mysql> create table PROJECT(
-> id INT PRIMARY KEY,
-> name VARCHAR(20) NOT NULL UNIQUE,
-> leader_id INT,
-> start_date DATE,
-> end_date DATE,
-> FOREIGN KEY(leader_id) references EMPLOYEE(id)
-> on delete SET NULL on update CASCADE,
-> CHECK (start_date < end_date)
-> );
WORKS_ON 테이블 생성
mysql> create table WORKS_ON(
-> empl_id INT,
-> proj_id INT,
-> PRIMARY KEY(empl_id, proj_id),
-> FOREIGN KEY(empl_id) references EMPLOYEE(id)
-> on delete CASCADE on update CASCADE,
-> FOREIGN KEY(proj_id) references PROJECT(id)
-> on delete CASCADE on update CASCADE
-> );
이제 모든 테이블이 생성되었으니
DEPARTMENT 테이블의 leader_id에도 FK지정 필요!
* 테이블 생성 이후 스키마 변경 => ALTER
mysql> ALTER TABLE DEPARTMENT ADD FOREIGN KEY(leader_id)
-> REFERENCES employee(id)
-> on update CASCADE
-> on delete SET NULL;
[ ALTER TABLE ]
table의 schema를 변경하고 싶을 때 사용.
- 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를 변경하는 것이라면,
변경 작업 때문에 서비스의 백엔드에 영향이 없을지 검토한 후에 변경하는 것이 중요.
[ DROP TABLE ]
- table 을 삭제할 때 사용
- DROP TABLE table_name;
* database 구조를 정의할 때 중요한 점
만들려는 서비스의 스펙과 데이터 일관성, 편의성, 확장성 등등을 종합적으로 고려하여,
DB 스키마를 적절하게 정의하는 것이 중요하다.
https://www.youtube.com/watch?v=c8WNbcxkRhY&list=PLcXyemr8ZeoREWGhhZi5FZs6cvymjIBVe&index=3
'Database > MySQL' 카테고리의 다른 글
| [데이터베이스(강의)] 6. SQL로 데이터 조회 (subquery / IN, EXIST, ANY, ALL) (0) | 2024.02.02 |
|---|---|
| [데이터베이스(강의)] 5. SQL로 데이터 조회 (Select) (0) | 2024.02.01 |
| [데이터베이스(강의)] 4. SQL (Insert / Update / Delete) (0) | 2024.01.30 |
| [데이터베이스(강의)] 2. 관계형 데이터베이스 (relational database) (0) | 2024.01.25 |
| [데이터베이스(강의)] 1. 데이터베이스 기본 개념 (0) | 2024.01.23 |
