[데이터베이스] SQL 개념, 데이터베이스 정의 방법

SQL 기본 개념

 

SQL 뜻

 

SQL이란, Structured Query Language의 약자로, 현업에서 쓰이는 relational DBMS의 표준 언어이다. SQL은 종합적인 database 언어이며 DDL, DML, VML의 모든 기능을 종합적으로 제공한다. 

 

 

SQL의 주요 언어

 

relational data model에서 표현하던 용어를 SQL 용어로 표현하면 다음과 같다.

 

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

 

 

SQL에서 relation의 의미

 

SQL에서 relation은 relational data model에서의 relation과 차이가 있다.

SQL에서 relation은 multiset(=beg) of tuples 을 의미한다. 이는 중복된 튜플을 허용한다는 의미이다.

 

 

SQL & RDBMS

 

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

 

예를 들면, MySQL에서 제공하는 문법, Oracle에서 제공하는 문법, PostgreSQL에서 제공하는 문법이 조금씩 다르다. 따라서 현업에서 개발할 때 사용하고 있는 RDBMS의 SQL 문법을 익히는 것이 중요하다.

 

 

 

MySQL로 데이터베이스 정의해보기

 

예시로 IT 회사와 관련된 DB를 만들어 본다고 가정해 보자.

 

이를 위해 부서, 사원, 프로젝트 관련 정보들을 저장할 수 있는 관계형 데이터베이스를 만들 것이며, RDBMS로 MySQL(InnoDB)를 사용한다.

 

 

데이터베이스 정의

 

데이터베이스 확인

 

먼저, MySQL을 설치하고 나서 MySQL에 어떤 데이터베이스가 있는지 확인하고 싶은 경우, SHOW DATABASES; 명령을 날리면 현재 이 데이터베이스가 어떤 것들을 갖고 있는지 보여준다.

 

 

데이터베이스 생성

 

이제 새로운 회사 관련 DB를 구축해야 하므로, 데이터베이스를 새로 생성해주어야 한다. 새로운 데이터 베이스를 생성할 때의 명령어는 다음과 같다.

CREATE DATABASE "생성할 데이터베이스명"

 

회사와 관련된 데이터베이스를 생성하는 것이므로, 'company' 라는 이름의 데이터베이스를 생성하자.

 

 

새로 생성한 company 데이터베이스가 잘 생성되었는지 확인하기 위해서는 다시 SHOW DATABASES; 명령어를 실행해 보면 되며, 실행 결과 정상적으로 생성된 것을 확인할 수 있다.

 

 

데이터베이스 활성화

 

생성한 데이터베이스를 사용하기 위해서는 해당 데이터베이스를 활성화해야 한다. 그전에, 사용할 데이터베이스를 활성화하기 위해 현재 활성화된 데이터베이스가 무엇인지 확인해야 하는데, 그 명령어는 다음과 같다.

SELECT database();

 

만약, 결과가 NULL이라면 현재 활성화되어 있는 데이터베이스가 아직 없다는 것을 의미한다. 

 

이제 company 데이터베이스를 활성화하기 위해 USE 키워드를 사용하여 데이터베이스를 활성화해 보자.

USE company;

 

select database();를 통해 확인해 보면, company 데이터베이스가 선택된 것을 확인할 수 있다.

 

 

활성화된 데이터베이스를 지우고 싶을 경우 다음과 같이 명령어를 입력하면 된다.

DROP DATABASE "데이터베이스명"

 

 

Database vs Schema

 

MySQL에서는 Database와 schema가 같은 뜻을 의미하지만, 다른 RDBMS에서는 두 의미가 다르게 쓰인다.

• PostgreSQL에서는 Schema가 database의 namespace를 의미한다.
• 따라서 MySQL에서는 데이터베이스 안에서 테이블이 정의되고, 다른 RDBMS에서는 데이터베이스 안에 스키마가 정의되고, 스키마 안에 테이블이 정의된다고 이해하면 된다.

 

 

 

table 생성하기

 

생성한 company 데이터베이스에 부서, 사원, 프로젝트 관련 정보들을 저장할 수 있는 관계형 데이터베이스를 만들어야 하는데, 이를 위해 IT 회사의 데이터베이스의 스키마를 정의하면 다음과 같다.

 

 

 

department 테이블 생성

 

먼저, department 테이블을 만들어 보자. 테이블을 만드는 방법은 다음과 같다.

 

 

create table "테이블명" 명령어를 통해 테이블을 생성할 수 있고, 중괄호 안에는 속성의 이름, 타입 등을 넣어주면 된다.

id의 경우 int 타입을 가지므로 int 타입을 지정해주어야 하고, department 테이블에서 primary key로 사용되기 때문에 이를 붙여주어야 한다.

 

다른 속성의 경우도 마찬가지로 데이터의 이름과 타입을 알맞게 지정해주어야 한다.

 

 

attribute data type (MySQL 기준)

 

MySQL을 기준으로 attribute의 data type을 자세히 살펴보면 다음과 같다.

 

숫자

 

종류	설명	사이즈	MySQL
정수	정수를 저장할 때 사용	1 byte	TINYINT
		2 byte	SMALLINT
		3 byte	MEDIUMINT
		4 byte	INT or INTEGER
		8 byte	BIGINT
부동 소수점 방식	- 실수를 저장할 때 사용 - 고정 소수점 방식에 비해 정확하지 않음	4 byte	FLOAT
		8 byte	DOUBLE or DOUBLE PRECISION
고정 소수점 방식	- 실수를 저장할 때 사용	variable	DECIMAL or NUMERIC
		variable

 

문자열

 

종류	설명	MySQL
고정 크기 문자열	- 최대 몇 개의 '문자'를 가지는 문자열을 저장할지를 지정 - 저장될 문자열의 길이가 최대 길이보다 작으면 나머지를 space로 채워서 저장 - name char(4) 일 때 다음과 같이 저장: 'a___' , '한국__' , '고고고고', 'wow_'	CHAR(n) (0 <= n <= 255)
가변 크기 문자열	- 최대 몇 개의 '문자'를 가지는 문자열을 저장할지를 지정 - 저장될 문자열의 길이 만큼만 저장 - name char(4) 일 때 다음과 같이 저장: 'a' , '한국' , '고고고고', 'wow'	VARCHAR(n) (0 <= n <= 255)
사이즈가 큰 문자열	- 사이즈가 큰 문자열을 저장할 때 사용	TINYTEXT TEXT MDEIUMTEXT LONGTEXT

참고: MySQL에서 varchar보다 더 많은 길이의 문자열을 저장할 수 있는 것은 mediumtext, longtext이다. tinytext나 text는 varchar와 동일한 길이를 저장하거나, 더 작은 길이의 문자열을 저장한다.

 

 

날짜와 시간

 

종류	설명	MySQL
날짜	- 년, 월, 일을 저장 - YYYY-MM-DD	DATE ('1000-01-01' ~ '9999-12-31')
시간	- 시, 분, 초를 저장 - hh:mm:ss or hhh:mm:ss	TIME ('-838:59:59' ~ '838:59:59')
날짜와 시간	- 날짜와 시간을 같이 표현 - YYYY-MM-DD hh:mm:ss - TIMESTAMP는 time-zone이 반영됨	TIMESTAMP ('1970-01-01 00:00:01' UTC ~ '2038-01-19 03:14:07' UTC)

 

 

Key constraints: Primary Key

• primary key는 table의 tuple을 식별하기 위해 사용하며, 하나 이상의 attribute(s)로 구성된다.
• primary key는 중복된 값을 가질 수 없다.
• primary key는 NULL 값을 가질 수 없다.

 

primary key를 선언하는 방법은 다음과 같다.

 

 

 

Key constraints: UNIQUE

• UNIQUE로 지정된 attribute(s)는 중복된 값을 가질 수 없다.
• 단, NULL은 중복을 허용할 수도 있다 (RDBMS마다 다름)
• MySQL, PostgreSQL은 NULL의 중복을 허용하지만, MSSQL의 경우 NULL의 중복을 허용하지 않는다.

 

UNIQUE를 선언하는 방법은 다음과 같다.

 

 

 

NOT NULL constraint

• attribute가 NOT NULL로 지정되면 해당 attribute는 NULL을 값으로 가질 수 없다.

 

NOT NULL을 선언하는 방법은 다음과 같다.

 

일반적으로 NOT NULL과 UNIQUE를 같이 사용한다.

 

 

employee 테이블 생성

 

이번에는 employee 테이블을 생성해 보자. 다음과 같이 테이블을 생성할 수 있다.

 

 

 

attribute DEFALUT

• attribute의 default 값을 정의할 때 사용한다.
• 새로운 tuple을 저장할 때 해당 attribute에 대한 값이 없다면, default 값으로 저장된다.

 

DEFAULT를 선언하는 방법은 다음과 같다.

 

 

 

CHECK constraint

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

 

선언하는 방법은 아래와 같다.

 

 

 

Referential integrity constraint: FOREIGN KEY

• attribute(s)가 다른 tuple의 primary key나 unique key를 참조할 때 사용한다.

 

foreign key를 선언하는 방법은 다음과 같다.

 

 

on delete / on update reference_option: 참조하고 있는 값이 업데이트되거나 삭제될 때 foreign key의 value를 어떻게 할 것인지 오른쪽 표와 같은 옵션을 지정할 수 있다.

 

MySQL은 NO ACTION과 SET DEFAULT를 잘 지원하지 않는다.

 

 

constraint 이름 명시

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

 

 

project, works_on 테이블 생성

 

지금까지의 내용을 바탕으로 project, works_on 테이블을 빠르게 생성해 보자.

 

먼저, project 테이블은 다음과 같이 생성할 수 있다.

 

 

마찬가지로, works_on 테이블도 다음과 같이 생성할 수 있다.

 

 

 

ALTER TABLE로 schema 변경하기

 

지금까지 company 데이터베이스에 대한 테이블을 다 만들어 보았다. 하지만, 하나 빼먹은 부분이 하나 있는데, 다시 department 테이블을 생성하는 부분으로 돌아가보자.

 

department 테이블을 생성할 당시에는 employee 테이블이 없었기 때문에 leader_id에 대한 foreign key를 걸어주지 못했다. 하지만, employee 테이블이 새로 생겼기 때문에 이 부분을 추가해 주어야 한다.

 

다음과 같은 명령어를 통해 스키마를 변경 가능하다.

 

 

즉, ALTER TABLE은 테이블이 생성되고 난 뒤, 테이블의 스키마를 변경해야 할 경우 사용되며, 다음과 같은 유형이 있다.

 

 

💡 주의 사항

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

 

 

DROP TABLE

마지막으로 DROP TABLE에 대해 살펴보자. DROP TABLE은 table을 삭제할 때 사용하는 명령어로, 다음과 같이 사용할 수 있다.

DROP TABLE "테이블명"

 

참고
https://www.youtube.com/watch?v=c8WNbcxkRhY&list=PLcXyemr8ZeoREWGhhZi5FZs6cvymjIBVe&index=3