[DB] SQL -DDL

이 글은 데이터베이스 스터디 중 학습한 내용을 정리한 것입니다.

 

---

 

CREATE

데이터베이스, 테이블, 프로시저등을 생성하기 위해서 사용된다.

 

테이블 생성

-- 문법
CREATE TABLE_테이블_이름 (
	(1) 속성_이름 데이터_타입 [NOT NULL] [DEFAULT 기본_값]
	(2) [PRIMARY KEY (속성)]
	(3) [UNIQUE (속성_리스트)]
	(4) [FOREIGN KEY (속성_리스트) REFERENCES 테이블_이름(속성_리스트)]
		[ON DELETE 옵션] [ON UPDATE 옵션]
	(5) [CONSTRAINT 이름] [CHECK(조건)]
);

-- 예시
CREATE TABLE Customer(
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(50),
    Age int(2),
	  Phone int(10)
);

 

다른 테이블을 활용하여 테이블을 생성하는 것도 가능하다.

-- 문법
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    [(create_definition,...)]
    [table_options]
    [partition_options]
    [IGNORE | REPLACE]
    [AS] query_expression

-- 예시
CREATE TABLE SubTable AS
SELECT CustomerID, CustomerName
FROM customer;

 

 

속성의 정의

(속성은 DBMS 벤더사마다 다를 수 있게 때문에, 정확한 정보는 각 벤더사의 공식문서를 참고 바랍니다.)

 테이블을 구성하는 속성은 기본적으로 NULL 값을 허용한다. 따라서, NULL 값을 허용하지 않으려면, NOT NULL을 지정해줘야 한다.

 

데이터 타입	의미
INT	정수
SMALLINT	INT보다 작은 정수
CHAR(n)	길이 n의 고정 길이 문자열
VARCHAR(n)	최대 길이가 n인 가변 길이 문자열
NUMERIC(p, s) or DECIMAL(p, s)	고정 소수점 실수. p : 전체 숫자의 길이, s : 소수점 이하의 숫자 길이
FLOAT(n)	길이가 n인 부동 소수점 실수
REAL	부동 소수점 실수
DATETIME	년, 월, 일로 표현되는 날짜
TIME	시, 분, 초로 표현되는 시간

 

예시

• 고객 아이디 VARCHAR(20) NOT NULL : 고객 아이디 속성을 길이가 최대 20인 가변 길이 문자열로 지정하며, 널 값은 허용하지 않는다.
• 적립금 INT DEFAULT 0 : 적립금 필드를 정수형 데이터 타입으로 지정하고, 기본값은 0으로 한다.

 

VARCHAR vs CHAR

 

 두 데이터 타입은 단순히 가변 길이인지 고정 길이인지를 넘어, 데이터가 저장될 때 차이점이 존재한다. 어찌보면 치명적일 수 있기 때문에, 벤더사의 커뮤니티에서는 CHAR를 사용하지 말라고 권장하는 것 같다. 참조한 글은 아래와 같으며, 뒤 이어 예시를 들겠다.

Char Vs Varchar - Ask TOM

 

MySQL :: MySQL 5.7 Reference Manual :: 11.3.2 The CHAR and VARCHAR Types

mysql> create table char_varchar( a char(4), b varchar(4));
Query OK, 0 rows affected (0.14 sec)

mysql> insert into char_varchar value("","");
Query OK, 1 row affected (0.03 sec)

mysql> insert into char_varchar value("ab","ab");
Query OK, 1 row affected (0.01 sec)

mysql> insert into char_varchar value("ab  ","ab  ");
Query OK, 1 row affected (0.01 sec)

mysql> select concat('(',a,')') , concat('(' , b , ')') from char_varchar;
+-------------------+-----------------------+
| concat('(',a,')') | concat('(' , b , ')') |
+-------------------+-----------------------+
| ()                | ()                    |
| (ab)              | (ab)                  |
| (ab)              | (ab  )                |
+-------------------+-----------------------+

 

 위 내용을 설명하자면, char(4) 와 varchar(4) 에 해당하는 필드를 갖는 테이블을 생성하고, 각각의 필드에 “” , “ab” , “ab ” 데이터를 insert 하였다. 마지막 으로 insert한 “ab ”가 출력되는 부분을 보자.

 

 varchar(4) 에서는 데이터 출력이 정상적으로 (ab ) 인 반면, char(4) 에서는 (ab)가 출력되는 것을 볼 수 있다. char 데이터 타입에서 데이터가 입력됬을 때, 우측에 공백이 존재하면 해당 공백을 지워버리는 특성이 있다(좌측은 안 지운다). 만약, 공백을 유지해야 되는 데이터라면 이는 치명적일 수 있다. 따라서, 되도록이면 varchar를 사용하는 것을 권장하는 것 같다.

 

 물론, char 타입이 데이터 공간을 적게 차지하여, 메모리 효율적이라는 이야기가 있지만, 데이터가 훼손되는 것보다는 메모리를 덜(?) 효율적으로 사용하는 것이 더 나은 선택인 것 같다.

 

 추가적으로, LIKE 절을 활용할 때 입력된 문자열에 공백이 있을 경우 정상적으로 SELECT 되지 않는 경우가 있으니 이점도 주의해야 할 것 같다.

 

 

키의 정의

 CREATE TABLE 문에 PRIMARY KEY, UNIQUE, FOREIGN KEY 명령을 사용하여 기본키, 대체키, 외래키를 지정할 수 있다.

 

예시

• PRIMARY KEY(ID) : ID를 기본키로 지정
• PRIMARY KEY(ID,PW) : ID, PW의 복합키를 기본키로 지정
• UNIQUE(PW) : PW를 대체키로 지정
• 외래키와 관련하여,
  • ON DELETE NO ACTION(기본 설정)
  • ON DELETE CASCADE : 관련 튜퓨을 함께 삭제함
  • ON DELETE SET NULL : 관련 튜플의 외래키 값을 NULL로 변경함
  • ON DELETE SET DEFAULT : 관련 튜플의 외래키 값을 기본 값으로 변경함
  • ON UPDATE NO ACTION(기본 설정)
  • ON UPDATE CASCADE : 관련 튜플에서 외래키 값을 함께 변경함
  • ON UPDATE SET NULL : 관련 튜플에서 외래키 값을 NULL로 변경함
  • ON UPDATE SET DEFAULT : 관련 튜플에서 외래키 값을 기본 값으로 변경함

 

ALTER

 CREATE 문을 통해 생성한 DB, 테이블 등을 변경할 때 ALTER 문을 사용한다. ALTER 문은 옵션을 기준으로 크게 ADD, DROP, MODIFY 로 나눠진다. 물론, 테이블 자체의 옵션을 변경할 떄는 앞서 언급한 세 가지 옵션을 사용하지 않는다.

 

예시

ALTER TABLE t1 ENGINE = InnoDB;

ALTER TABLE t1 CHARACTER SET = utf8mb4;

 

주의할 점은, 외래키와 같은 제약조건이 걸려있을 떄 ALTER문이 동작하지 않을 수 있다는 점이다.

mysql> alter table PT ENGINE = MyISAM;
ERROR 3776 (HY000): Cannot change table's storage engine because the table participates in a foreign key constraint.

 

ADD

 

 컬럼, 인덱스, 제약조건 등을 추가할 때 ADD 를 사용한다.

mysql> alter table PT add pw varchar(10);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc PT;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| pw    | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> alter table PT add check(age < 20);
Query OK, 2 rows affected (0.09 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into PT values(6,2,21);
ERROR 3819 (HY000): Check constraint 'PT_chk_1' is violated.

 

DROP

 

테이블의 속성, 제약조건 등을 삭제할 때 사용하는 옵션이다. 주목할 점은, 관련된 제약조건이나 참조하는 다른 속성을 함께 삭제하기 위해 CASCADE를 지정하거나, 관련된 제약조건이나 참조하는 다른 속성이 존재하면 삭제가 수행되지 않도록 RESTRICT를 지정할 수 있는 점이다.

mysql> alter table PT drop age;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

 

MODIFY

 

 이름 그대로 기존 특성을 변경할 때 사용한다.

mysql> alter table PT modify pw varchar(10);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

 

DROP

 테이블, 데이터베이스 등을 삭제할 때 사용한다. 다만, 외래키와 같은 제약조건이 걸려있을 때는 DROP 문이 동작하지 않을 수 있다.

mysql> drop table PT;
ERROR 3730 (HY000): Cannot drop table 'PT' referenced by a foreign key constraint 'FT_ibfk_1' on table 'FT'.

mysql> drop table FT;
Query OK, 0 rows affected (0.02 sec)

 

 

 

VIEW

 뷰는 다른 테이블을 기반으로 만들어진 가상 테이블이다. 가상 테이블이라고 지칭하는 이유는 일반 테이블과는 달리 실제 저장되지는 않기 때문이다. 뷰를 통해 기본 테이블의 내용을 쉽게 검색할 수는 있지만, 기본 테이블의 내용을 변화시키는 작업은 제학적으로 이뤄진다.

(뷰의 생성과 삭제는 DDL에 해당한다.)

 

뷰의 생성

뷰를 생성하기 위해 아래의 CREATER VIEW 키워드를 사용한다. SELECT 문과 VIEW가 다른 점은, VIEW에서는 ORDER BY를 사용할 수 없다는 점이다. 이외에는 SELECT문과 동일하다. WITH CEHCK OPTION은 생성한 뷰에 삽입이나 변경 연산을 수행할 때, SELECT 문에서 정의한 VIEW의 정의 조건을 위반하면 수행되지 않도록 하는 제약 조건이다.

-- 문법
CREATE VIEW 뷰_이름[(속성_리스트)]
AS SELECT 문
[WITH CHECK OPTION];

-- 예시
mysql> CREATE VIEW KOREA_CITY(name, population)
    -> AS SELECT name, population
    -> FROM city
    -> WHERE CountryCode = 'KOR';
Query OK, 0 rows affected (0.04 sec)

mysql> select * from KOREA_CITY;
+------------+------------+
| name       | population |
+------------+------------+
| Seoul      |    9981619 |
| Pusan      |    3804522 |
| Inchon     |    2559424 |

 

뷰에 삽입 연산을 수행하면 뷰에 데이터가 삽입될까 아니면 기본 테이블에 데이터가 삽입될까? VIEW는 실제로 저장되는 테이블이 아니기 때문에, (VIEW가 참조하는) 기본 테이블에 데이터가 삽입될 것으로 보인다. 아래 예시를 보면, KOREA_CITY라는 뷰에 삽입연산을 수행했지만, city 테이블에 데이터 삽입된 것을 확인할 수 있다.

 

mysql> insert into KOREA_CITY VALUES('test',1234);
Query OK, 1 row affected (0.02 sec)

mysql> select * from KOREA_CITY where name = 'test';
Empty set (0.00 sec)

mysql> select * from city where name = 'test';
+------+------+-------------+----------+------------+
| ID   | Name | CountryCode | District | Population |
+------+------+-------------+----------+------------+
| 4081 | test |             |          |       1234 |
+------+------+-------------+----------+------------+
1 row in set (0.00 sec)

 

뷰의 활용

 

 뷰가 데이터를 실제로 저장하고 있지 않은 가상 테이블임에도 SELECT 문을 이용해 데이터를 검색할 수 있는 것은, 뷰에 대한 SELECT 문이 내부적으로 기본 테이블에 대한 SELECT 문으로 변환되어 수행되기 떄문이다. 이떄 뷰는 기본 테이블의 전체 구성요소들을 알 수 없도록 제한하는 역할을 수행한다. 뷰의 기능은 아래와 같다.

1. 질의문을 더 쉽게 작성할 수 있다 : 특정 조건(WHERE, GROPU BY, 집계함수, JOIN)을 만족하는 튜플들로 뷰를 미리 만들어 두면, 조건에 해당하는 쿼리문을 재작성하지 않고도 조회가 가능하다.
2. 데이터의 보안 유지에 도움이 된다 : 뷰를 통해서만 접근을 허용한다면, 뷰에 포함되지 않은 데이터를 사용자로부터 보호할 수 있다.
3. 데이터를 더 편리하게 관리할 수 있다 : 제공된 뷰와 관련된 기본 테이블의 다른 부분과 뷰와 관련없는 테이블들의 변화에 영향을 받지 않게된다.

 뷰에서 기본 테이블에 대한 접근을 제한하는 부분을 더 자세히 알아보자. 뷰에서 INSERT를 수행할 때, 오직 뷰에 포함된 속성에 대해서만 INSERT가 가능하다. 아래의 countryCode는 기본 테이블에는 존재하지만, 뷰에는 존재하지 않는 속성이다. 이 속성을 포함하여 VIEW에서 INSERT를 수행할 경우, 존재하지 않는 필드라는 오류 메시지를 받게된다.

mysql> insert into KOREA_CITY(name, population, countryCode) VALUES('test',1234,4321);
ERROR 1054 (42S22): Unknown column 'countryCode' in 'field list'

 

뷰의 삭제

 

 뷰를 삭제하기 위해 테이블에서와 동일하게 DROP 키워드를 사용한다.

DROP VIEW 뷰_이름 CASCADE | RESTRICT;

 

 이때 뷰의 삭제는 기본 테이블에 영향을 주지 않는다. 다만, 뷰를 참조하는 다른 뷰에게 영향을 줄 수는 있다.

 

 

 

참고자료

테이블의 제거

 

SQL ALTER TABLE - ADD, DROP, MODIFY - GeeksforGeeks