[DBMS] 8. 테이블 및 열

테이블

• 테이블(Table)은 실제로 데이터(레코드, Record)를 담기 위해 사용한다.
• 테이블 명명법 : 단/복수형 스네이크 케이스

만들기

• 테이블을 만들기 위해 아래와 같이 명령한다.
  • 테이블의 이름이 붕복되어 오류가 발생하는 것을 방지하기 위해 IF NOT EXITS 키워드를 추가할 수 있다.
• CREATE TABLE [IF NOT EXISTS]? `[소속 스키마 이름]`.`[테이블 이름]` ( [열 구조, .....] [,제약 조건, 구조, ....]? );

제약조건

• 제약조건(Constraint)은 제액조건의 종류와 그 조건에 따라 열이 가질 수 있는 레코드의 데이터를 제한하여 DBMS 무결성을 보장하기 위해 사용한다.

1.기본키 제약조건

• 기본키 제약조건(Primary Key Constraint)은 해당 테이블이 가지고 있는 열 중 그 의미가 식별자인 주축이 되는 열에 대한 제약조건이다.
• 하나의 테이블은 여러개의 기본키 제약조건을 가질수 없다.(가지고 있지 않거나, 한 개만 가질수 있음)
• 레코드에 대한 접근 참조로써, 인덱싱이 발생하여 조회속도가 빨라질 수 있다.(대부분의 테이블 하나의 기본키 제약조건을 가지는 것이 기본으로 한다.)
• 기본키 제약 조건은 유니크 제약조건의 속성을 같이 가져가기때문에, 기본키로 지정된 열(들)의 값이 중복될 수 없다.
• 기본키 제약조건은 적용된 열을 NULL값을 허용하지 않는다.
• 어떠한 열이 정수형이고 AUTO_INCREMENT가 적용되기 위해서는 반드시 해당 열이 기본키여야한다.
• 사용법 : CONSTRAINT PRIMARY KEY([기본키가 될 열 이름, .....])

2.유니크 제약조건

• 유니크 제약조건(Unique Key Constraint)은 해당 열이 가지는 값이 중복되어서는 안된다는 의미의 제약조건이다,
• 유니크 제약조건은 NULL 값을 허용하며 NULL간에는 중복검사를 하지 않는다.
• 사용법 : `CONSTRAINT UNIQUE([기본키가 될 열 이름, .....])`

3.체크 제약조건

• 레코드 삽입시 명시한 조건이 참일때에만 삽입이 발생하도록 하기위한 제약조건이다.
• 사용법 : CONSTRAINT CHECK ([조건])

4.외래키 제약조건

• 외래키 제약조건(Foreign Key Constraint)은 해당 제약조건이 부여된 테이블이 가질수 있는 열의 데이터로 하여, 다른 테이블(참조 대상이 되는 테이블)이 가지고 있는 데이터로 제한하고자 할 때 사용한다.
• 사용법 :
  • ON DELETE CASCADE : 외래키 제약조건의 피참조 레코드는 해당 키워드 없이 삭제될 수 없는데, 피참조 레코드가 삭제될 경우 본 레코드도 함께 삭제되게 하기위해 사용한다.
  • ON UPDATE CASCADE : 외래키 제약조건의 피참조 레코드는 해당 키워드 없이 수정될 수 없는데, 피참조 레코드가 수정될 경우 본 레코드도 함께 수정되게 하기위해 사용한다.
• CONSTRAINT FOREIGN KEY ([외래키 제약 조건 대상 열 이름]) ([조건]) REFERENCE [스키마 이름].[테이블 이름] ([참조 대상이 되는 열 이름], ....) [ON DELETE CASCADE]? [ON UPDATE CASCADE]?

조회하기

• 어떠한 스키마에 소속된 테이블을 조회하기 위해 아래와 같이 명령한다.
• SHOW TABLES IN `[스키마 이름]`;

테이블 열 구조 조회하기

• 어떠한 테이블이 가지고 있는 열의 구조를 조회하기 위해 아래와 같이 명령한다.
  • DESC : Describe
• DESC `[소속 스키마 이름]`.`[대상 테이블 이름]`

수정하기

1.테이블 이름 바꾸기

• 테이블의 이름을 변경하기 위해 아래와 같이 명령한다. 단, 소속된 스키마도 변경할 수 있으므로 참고한다.
  • 테이블의 소속 스키마를 변경하지 않으려면 소속 스키마 이름과 신규 스키마 이름이 같게하면 된다.
• ALTER TABLE `[소속 스키마 이름]`.`[대상 테이블 이름]` RENAME TO `[신규 스키마 이름]`.`[신규 테이블 이름]`;
• 스키마의 이름을 변경하는 기능이 없기 때문에, 테이블의 소속 스키마를 위 쿼리를 이용해서 옮긴 뒤, 기존의 스키마를 삭제하는 방식을 채택해야 한다.

2.열 추가하기

• 열을 추가하기 위해 아래와 같이 명령한다.
  • 새로 추가 하는 열은 테이블의 가장 마지막 자리에 추가된다.
• ALTER TABLE `[소속 스키마 이름]`.`[대상 테이블 이름]` ADD COLUMN [`추가할 열 구조`];
• 추가할 열의 위치를 명시하고자 한다면 아래와 같이 AFTER 키워드를 활용할 수 있다.
  • 새로 추가하는 열은 기준 열 이름 바로 뒷자리에 추가된다.
• ALTER TABLE `[소속 스키마 이름]`.`[대상 테이블 이름]` ADD COLUMN [`추가할 열 구조`] AFTER [`기준 열 이름`];
• 가장 앞자리에 열을 추가하고자 한다면 아래와 같이 FIRST 키워드를 활용할 수 있다.
  • 새로 추가하는 열은 가장 앞 자리에 추가된다.
• ALTER TABLE `[소속 스키마 이름]`.`[대상 테이블 이름]` ADD COLUMN [`추가할 열 구조`] FIRST;

3.열 수정하기

(1)열의 이름 변경하기

• 열의 이름만 변경하기 위해 아래와 같이 명령한다.
• ALTER TABLE `[소속 스키마 이름]`.`[대상 테이블 이름]` RENAME COLUMN [`변경할 열 이름`] TO [`새로운 열 이름`];
• 해당 기능은 MariaDB DBMS 10.5.2.(MySQL8.0)버전 부터만 지원됨에 유의한다.

(2)열의 타입(및 구조) 변경하기

• 열의 이름을 제외한 타입 및 구조를 변경하기 위해 아래와 같이 명령한다.(선택에 따라 위치도 옮길 수 있음.)
• ALTER TABLE `[소속 스키마 이름]`.`[대상 테이블 이름]` MODIFY COLUMN [`변경할 열 이름`] [이름을 제외한 열 구조] [AFTER `[기준 열 이름]` |FIRST]?;

(4)열의 이름, 타입 및 구조 변경하기

• 열의 이름, 타입 및 구조를 변경하기 위해 아래와 같이 명령한다.(선택에 따라 위치도 옮길 수 있음.)
• ALTER TABLE `[소속 스키마 이름]`.`[대상 테이블 이름]` CHANGE COLUMN [`변경할 열 이름`] [새로운 이름을 포함한 열 구조] [AFTER `[기준 열 이름]` |FIRST]?;

4.열 삭제하기

• 열을 삭제하기 위해 아래와 같이 명령한다.
• ALTER TABLE `[소속 스키마 이름]`.`[대상 테이블 이름]` DROP COLUMN [`삭제할 열 이름`];

삭제하기

• 테이블을 삭제하기 위해 아래와 같이 명령한다.
  • 존재하지 않는 테이블을 제거하려는 경우 오류가 발생할 수 있는데, 오류가 발생하는 것을 무시하기위해 IF EXISTS 키워드를 추가한다.
• DROP TABLE [IF EXISTS]? `[소속 스키마 이름]`.`[대상 테이블 이름]`
• 테이블을 삭제할 경우 이에 종속된 레코드가 모두 삭제되고, 되돌릴수 없기 때문에 굉장히 신중 해야 한다.

열

• 열(Column)은 레코드 하나가 가지는 데이터의 개수와 구조 등을 제약하기 위해 존재한다.

구조

• 열의 구조는 아래와 같다.
  • NOT NULL : 해당 열에 대응되는 레코드의 데이터는 비어 있을 수 없다(NULL 일 수 없다.)
  • DEFAULT x : 레코드 삽입(INSERT)시 해당 열에 대한 언급이 없을 떄, x값을 기본 값으로 사용하겠다는 의미이다.
  • AUTO_INCREMENT : 해당 열이 기본키(Primary Key)인 정수일 때, 레코드 삽입(INSERT)시, 별도의 업급이 없다면 1부터 시작하여 1씩 증가하는 일종의 순번 기준 값으로 사용하겠다는 의미이다.
• [열 이름] [데이터 타입] [NOT NULL]? [DEFAULT x]? [AUTO_INCREMENT]?

데이터 타입

1.정수형

• TINYINT : (1 Byte) -128 부터 128까지의 정수를 가질 수 있다.
• SMALLINT : (2 Byte) -32768부터 32767까지의 정수를 가질 수 있다.
• MEDIUMINT : (3 Byte) -8388608부터 8399607까지의 정수를 가질수 있다.
• INT : (4 Byte) -2147483648부터 2147483647까지의 정수를 가질 수 있다.
• BIGINT : (8 Byte) -9223372036854775808부터 9223372036854775807까지의 정수를 가질 수 있다.
• [정수형] UNSIGNED : 부호를 없애 음수부의 숫자 개수 만큼 추가적인 양수 값을 가질 수 있다.
• [정수형](n) : 정수형이 가지는 범위내에서 n자에 해당하는 정수를 가질 수 있다.

2.부동 소수형

• FLOAT : (4 Byte) -3.4E+38부터 3.4E+38까지의 소수점이 최대 7자리인 실수를 가질 수 있다.
• DOUBLE : (8 Byte) -1.79E+308부터 1.79E+308까지의 소수점이 최대 15자리인 실수를 가질 수 있다.
• [부동 소수형] UNSIGNED : 부호를 없애 음수부의 숫자 개수 만큼 추가적인 양수 값을 가질 수 있다.
• FLOAT와 DOUBLE이 데이터를 저장하는 방식에 의해 연산 결과나 소수점 끝자리가 크게 틀어질 수 있어 MySQL 8.0.17버전 이후로는 사용을 지야하는 것이 표준이 되었다.

3.고정 소수형

• DECIMAL(x, y) : (5 ~ 7 Byte, 유동적) 전체 길이가 x, 그중 소수부의 길이가 y인 고정 소수 값을 가진다.
  • x : 값의 전체 길이로 최대 65까지 지정할 수 있다.
  • y : 값 중 소수부의 길이로 최대 30까지 지정할 수 있다.

4.문자형

• VARCHAR(n) : (2n ~ 4n Byte) 문자를 담는다. n의 최대값은 65.535이다.
  • 테이블 하나가 가지는 (일부 데이터형을 제외한) 열 들의 용량 합이 65535 Byte를 초과하면 안되므로 유의해야 한다.
• TINYTEXT : (최대 255 Byte) 문자를 담는다.
• TEXT : (최대 65535 Byte) 문자를 담는다.
• MEDIUMTEXT : (최대 16777215 Byte = 16383 KB = 16 MB) 문자를 담는다.
• LONGTEXT : (최대 4294967295 Byte = 4194303 KB = 4096 MB = 4GB) 문자를 담는다.

5.날짜와 시간

• DATE : (3 Byte) 년, 월, 일
• TIME : (3 Byte) 시, 분, 초
• TIME(n) : (유동적) 시, 분, 초, 밀리초(n자), n은 최대 6
• DATETIME : (8 Byte) 년, 월, 일, 시 ,분, 초
• DATETIME(n) : (유동적) 년, 월, 일, 시, 분, 초, 밀리초(n자), n은 최대 6
• TIMESTAMP : (4 Byte) 년, 월, 일, 시, 분,초
• YEAR : (1 Byte) 년

6.논리형

• BOOLEAN : (1 Byte) 참(true) 혹은 거짓(false) 값을 가진다. 사실 BOOLEAN 타입은 존재하지 않고, TINYINT(1) UNSIGNED 로 취급된다. 이 때 참(true)은 1, 거짓(false)은 0 이다.