테이블은 정보를 저장하는 데이터베이스의 개체로, 데이터베이스를 구성하는 가장 기본적이고 핵심적인 요소.
테이블은 행과 열로 구성되며 행은 row 또는 record, 열은 column 또는 field라고 한다.
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
col_name column_definition
| [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_userName, ...) [index_option]
| {INDEX | KEY} [index_name] [index_type] (index_col_userName, ...) [index_option]
| [CONSTRAINT [[symbol]] UNIQUE [INDEX | KEY] [index_name] [index_type] (index_col_userName, ...) [index_option]
| {FULLTEXT | SPATIAL}[INDEX | KEY] [index_name] [index_type] (index_col_userName, ...) [index_option]
| [CONSTRAINT [[symbol]] FOREIGN KEY [INDEX | KEY] [index_name] (index_col_userName, ...) reference_definition
| CHECK (expr);
column_definition:
data_type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [UIQUE [KEY] | [PRIMARY] KEY]
[COMMENT 'string']
[COLUMN_FORMAT {FIXED | DYNAMIC | DEFAULT}]
[STORAGE {DISK | MEMORY | DEFAULT}]
[reference_definition]
| data_type [GENERATED ALWAYS] AS (expression)
[VIRTUAL | STORED] [UNIQUE [KEY]] [COMMENT comment]
[NOT NULL | NULL][[PRIMARY] KEY]
와 같은 옵션들로 테이블로 생성할 수 있다.
위는 복잡해보이지만
CREATE TABLE test (num INT);
위와 같은 형식에 옵션들만 추가해 테이블을 생성한다.
use tabledb;
CREATE TABLE userTBL
( userID CHAR(8),
userName VARCHAR(10),
birthYear INT,
addr CHAR(2),
mobile1 CHAR(3),
mobile2 CHAR(8),
height SMALLINT,
mDate DATE
);
CREATE TABLE buyTBL
(num INT,
userID CHAR(8),
prodName CHAR(6),
groupName CHAR(4),
price INT,
amount SMALLINT
);
열 이름과 데이터 형식만 지정하고 쉼표로 분리하여 계속 나열하면 된다.
위의 SQL문에 몇 가지 옵션을 추가
use tabledb;
CREATE TABLE userTBL
( userID CHAR(8) NOT NULL,
userName VARCHAR(10) NOT NULL,
birthYear INT NOT NULL,
addr CHAR(2) NOT NULL,
mobile1 CHAR(3) NULL,
mobile2 CHAR(8) NULL,
height SMALLINT NULL,
mDate DATE NULL
);
CREATE TABLE buyTBL
(num INT NOT NULL,
userID CHAR(8) NOT NULL,
prodName CHAR(6) NOT NULL,
groupName CHAR(4) NOT NULL,
price INT NOT NULL,
amount SMALLINT NOT NULL
);
use tabledb;
CREATE TABLE userTBL
( userID CHAR(8) NOT NULL PRIMARY KEY,
userName VARCHAR(10) NOT NULL,
birthYear INT NOT NULL,
addr CHAR(2) NOT NULL,
mobile1 CHAR(3) NULL,
mobile2 CHAR(8) NULL,
height SMALLINT NULL,
mDate DATE NULL
);
CREATE TABLE buyTBL
(num INT NOT NULL PRIMARY KEY,
userID CHAR(8) NOT NULL,
prodName CHAR(6) NOT NULL,
groupName CHAR(4) NOT NULL,
price INT NOT NULL,
amount SMALLINT NOT NULL
);
AUTO_INCREMENT는 자동으로 1부터 증가하는 값을 입력하는 키워드
use tabledb;
DROP TABLE IF EXISTS buyTBL;
CREATE TABLE buyTBL
(num INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
userID CHAR(8) NOT NULL,
prodName CHAR(6) NOT NULL,
groupName CHAR(4) NOT NULL,
price INT NOT NULL,
amount SMALLINT NOT NULL
);
use tabledb;
DROP TABLE IF EXISTS buyTBL;
CREATE TABLE buyTBL
(num INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
userID CHAR(8) NOT NULL,
prodName CHAR(6) NOT NULL,
groupName CHAR(4) NOT NULL,
price INT NOT NULL,
amount SMALLINT NOT NULL,
FOREIGN KEY(userID) REFERENCES userTBL(userID)
);
FOREIGN KEY(userID) REFERENCES userTBL(userID)
-> userTBL의 userID열과 외래키 관계를 맺으라
mysql> INSERT INTO buyTBL VALUES (NULL, 'KHD', '운동화', NULL, 30, 2);
mysql> INSERT INTO buyTBL VALUES (NULL, 'KHD', '노트북', '전자', 1000, 1);
mysql> INSERT INTO buyTBL VALUES (NULL, 'KYM', '모니터', '전자', 200, 1);
위 쿼리 실행하면 구매 테이블의 userID는 회원 테이블의 userID에 존재해야하지만 'KYM'이라는 회원이 없기때문에 오류가 발생한다.
제약 조건(constraint)은 데이터의 무결성을 지키기 위해 제한하는 조건.
특정 데이터를 입력할 때 무조건 입력받는 것이 아니라 제약 조건을 만족했을 때만 입력되도록 설정하는 것.
대부분의 DBMS에서는 데이터의 무결성을 보장하기 위해 다음과 같은 제약 조건을 제공
테이블에 존재하는 많은 행을 구분하는 식별자를 기본키라고 부름.
기본키는 중복 값도, NULL 값도 허용되지 않는다.
기본키는 테이블에서 중요한 역할을 한다.
기본키로 생성한 열에는 자동으로 클러스터형 인덱스가 생성된다.
기본키에 설정된 제약 조건의 이름을 DESCRIBE 문으로 확인
테이블마다 기본키는 하나이고, MySQL은 기본키 제약 조건의 이름을 항상 PRIMARY로 지정한다.
테이블에 지정된 제약 조건의 이름을 보려면 'SHOW KEYS FROM 테이블이름'을 사용
제약 조건을 설정하는 또 다른 방법은 이미 만들어진 테이블을 수정하는 ALTER TABLE 문을 사용하는 것이다.
DROP TABLE IF EXISTS userTBL;
CREATE TABLE userTBL
( userID CHAR(8) NOT NULL,
userName VARCHAR(10) NOT NULL,
birthYear INT NOT NULL
);
ALTER TABLE userTBL
ADD CONSTRAINT PK_userTBL_userID
PRIMARY KEY (userID);
기본키는 테이블마다 하나만 존재하지만, 기본키를 반드시 하나의 열로만 구성해야 하는 것은 아니다. 필요에 따라서 2개 또는 그 이상의 열을 합쳐서 하나의 기본키로 설정하는 경우도 있다.
제품 코드 | 제품 일련번호 | 제조일자 | 현 상태 |
---|---|---|---|
AAA | 0001 | 2019. 10. 10. | 판매 완료 |
AAA | 0002 | 2019. 10. 11. | 매장 진열 |
BBB | 0001 | 2019. 10. 12. | 재고 창고 |
AAA | 0001 | 2019. 10. 13. | 판매 완료 |
AAA | 0002 | 2019. 10. 14. | 매장 진열 |
이 상태에서 제품 코드만으로 모든 행을 구별할 수 없다.
따라서 제품 코드를 기본키로 활용할 수 없다.
이런 경우 제품 코드와 일련번호를 합치면 모든 행을 유일하게 구분할 수 있는 값이 되므로 (제품코드, 일련번호)의 쌍으로 기본키를 사용한다.
DROP TABLE IF EXISTS prodTBL;
CREATE TABLE prodTBL;
( prodCode CHAR(3) NOT NULL,
prodID CHAR(4) NOT NULL,
prodDate DATETIME NOT NULL,
prodState CHAR(10) NULL,
);
ALTER TABLE prodTBL
ADD CONSTRAINT PK_prodTbl_proCode_prodID
PRIMARY KEY (prodCode, prodID);
외래키 제약 조건은 두 테이블 사이의 관계를 선언함으로써 데이터의 무결성을 보장하는 역할.
테이블 사이에 외래키 관계를 설정하면 하나의 테이블이 다른 테이블에 의존하게 된다.
다음과 같이 외래키는 CREATE TABLE 문 내부에 FOREIGN KEY 키워드를 이용하여 설정한다.
DROP TABLE IF EXISTS buyTBL, userTBL;
CREATE TABLE userTBL
( userID CHAR(8) NOT NULL PRIMARY KEY,
userName VARCHAR(10) NOT NULL,
birthYear INT NOT NULL
);
CREATE TABLE buyTBL
( num INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
userID CHAR(8) NOT NULL,
prodName CHAR(6) NOT NULL,
FOREIGN KEY (userID) REFERENCES userTBL (userID)
);
위의 예에서는 외래키 테이블의 열이 참조하는 기준 테이블의 열이 기본키로 설정되어 있다.
만약 기준 테이블의 열이 기본키 또는 UNIQUE로 설정되지 않았다면 외래키 관계가 성립되지 않는다.
기본키 제약 조건의 이름을 지정할 때와 마찬가지로, 직접 외래키 제약 조건의 이름을 지정하려 할때는 마지막 행에서 쉼표로 분리한 후 아래에 다음과 같이 쓰면 된다.
DROP TABLE IF EXISTS buyTBL;
CREATE TABLE buyTBL
( num INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
userID CHAR(8) NOT NULL,
prodName CHAR(6) NOT NULL,
CONSTRAINT FK_userTBL_buyTBL FOREIGN KEY (userID) REFERENCES userTBL (userID)
);
위 예시에서는 기준 테이블의 열 이름과 외래키 테이블의 열 이름이 동일하지만 반드시 그래야하는 것은 아니다.
외래키 제약 조건을 지정하는 또 다른 방법은 ALTER TABLE 문을 이용하는 것이다.
DROP TABLE IF EXISTS buyTBL;
CREATE TABLE buyTBL
( num INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
userID CHAR(8) NOT NULL,
prodName CHAR(6) NOT NULL
);
ALTER TABLE buyTBL
ADD CONSTRAINT FK_userTBL_buyTBL
FOREIGN KEY (userID)
REFERENCES userTBL(userID);
외래키를 설정할 때 ON DELETE CASCADE 또는 ON UPDATE CASCADE 옵션을 상요할 수 있다.
이는 기준 테이블의 데이터가 변경되었을 떄 외래키 테이블에도 변경된 데이터가 자동으로 적용되도록 설정하는 옵션이다.
UNIQUE 제약 조건은 중복되지 않는 유일한 값을 입력해야 하는 조건을 말한다.
이 조건은 기본키 제약 조건과 거의 비슷하지만 NULL 값을 허용한다는 것이 차이점.
제약 조건의 이름을 지정할 때 일반적으로 기본키는 PK, 외래키는 FK, UNIQUE는 AK를 사용한다.
UNIQUE는 대체키라고도 부른다.
DEFAULT 제약 조건은 값을 입력하지 않았을 때 자동으로 입력되는 기본 값을 정의하는 조건.
CREATE TABLE userTBL
( userID CHAR(8) NOT NULL,
userName VARCHAR(10) NOT NULL,
birthYear INT NOT NULL DEFAULT -1,
addr CHAR(2) NOT NULL DEFAULT '서울',
mobile1 CHAR(3) NULL,
mobile2 CHAR(8) NULL,
height SMALLINT NULL DEFAULT 170,
mDate DATE NULL
);
ALTER TABLE 문에 DEFALUT 제약 조건을 저장할 때는 ALTER COLUMN 구문을 사용한다.
ALTER COLUMN birthYear SET DEFAULT -1;
테이블을 정의할 때 특정 열에 NULL 값이 입력되는 것을 허용하려면 NULL로 설정하고, 허용하지 않으려면 NOT NULL로 설정하는 조건
기본키로 설정된 열에는 NULl 값이 올 수 없으므로 특별히 설정하지 않아도 자동으로 NOT NULL로 인식한다.
NULL값은 '아무것도 없다'는 의미로 0, 빈 문자, 공백과는 다르다.
MySQL은 자체적으로 테이블 압축 기능을 제공한다.
압축 기능을 이용하면 대용량 테이블을 저장할 때 저장 공간을 절약할 수 있다.
테스트용 데이터베이스를 생성한 후 열이 동일한 2개의 테이블을 만든다.
단, 한 테이블의 정의 문 뒤에 ROW_FORMAT=COMPRESSED;구문을 붙여서 압축되도록 설정.
CREATE TABLE normalTBL (emp_no INT, first_name VARCHAR(14));
CREATE TABLE compressTBL (emp_no INT, first_name VARCHAR(14)) ROW_FORMAT=COMPRESSED;
압축된 테이블에 데이터를 입력할 때 시간이 더 오래 걸렸다. 압축하면서 데이터를 입력하기 때문에 시간이 더 걸린것이다.
입력된 두 테이블의 상태를 확인해보면 압축된 테이블의 평균 행 길이와 데이터 길이가 훨씬 작다. 물론 데이터 값의 분포에 따라서 압축률이 달라질 수 있으나 원래 데이터보다 작아진 것은 확실하다.
디스크 공간에 여유가 없거나 대용량 데이터를 저장하는 테이블이라면 테이블 압축하는 것도 좋은 방법이다.
임시 테이블은 말 그대로 임시로 잠깐 사용하는 테이블을 말한다. 임시 테이블을 생성하는 형식은 다음과 같다.
CREATE TEMPORARY TABLE [IF NOT EXISTS] 테이블이름 (열정의 ...)
위 구문은 테이블을 정의하는 CREATE TABLE 문에서 TABLE의 위치에 TEMPORARY TABLE이 들어간 것만 다르고 나머지는 동일하다.
단, 임시 테이블은 세션(session) 내에서만 존재하며 세션이 닫히면 자동으로 삭제된다. 또한 임시 테이블은 테이블을 생성한 클라이언트만 접근할 수 있고 다른 클라이언트는 접근할 수 있다.
임시 테이블의 이름은 데이터베이스 내 다른 테이블의 이름과 동일하게 지을 수 있다. 이름을 동일하게 하는 경우 임시 테이블이 있는 동안 기존 테이블에 접근할 수 없으며 무조건 임시 테이블에만 접근할 수 있다.
임시 테이블이 제거되는 시점은 다음과 같다.
테이블을 삭제하는 형식은 다음과 같다.
DROP TABLE 테이블이름;
테이블을 삭제할 때 외래키 제약 조건에 걸려 있는 기준 테이블은 삭제할 수 없으므로 주의해야 한다.
여러 개의 테이블을 동시에 삭제할 때는 DROP TABLE 테이블1, 테이블2, 테이블3;과 같이 계속 나열하면 된다.
앞에서 제약 조건을 추가할 때 ALTER TABLE 문을 사용했다.
ALTER TABLE 문을 이미 생성된 테이블의 구조에 무엇인가를 추가하거나 삭제하거나 변경할 떄 사용한다.
ALTER [IGNORE] TABLE tbl_name
[alter_specification [, alter_specification] ... ]
[partition_options]
alter_specification:
table_options
| ADD [COLUMN] col_name column_definition
[FIRST | AFTER col_name]
| ADD [COLUMN] (col_name column_definition, ...)
| ADD {INDEX | KEY} [index_name]
[index_type] (index_col_userName, ...) [index_option] ..
| ADD [CONSTRAINT [symbol]] PRIMARY KEY
[index_type] (index_col_userName, ...) [index_option]...
| ADD [CONSTRAINT [symbol]]
UNIQUE [INDEX | KEY][index_name]
[index_type] (index_col_userName, ...) [index_option] ...
| ADD FULLTEXT [INDEX | KEY] [index_name] (index_col_userName, ...) [index_option]
| ADD [CONSTRAINT [symbol]]
FOREIGN KEY [index_name] (index_col_userName, ...)
reference_definition
| ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
| CHANGE [COLUMN] old_col_name new_col_name column_definition
[FIRST | AFTER col_name]
| MODIFY [COLUMN] col_name column_definition
[FIRST | AFTER col_name]
| DROP [COLUMN] col_name
| DROP PRIMARY KEY
| DROP {INDEX | KEY} index_name
| DROP FOREIGN KEY fk_symbol
| DISABLE KEYS
| ENABLE KEYS
| RENAME [TO|AS] new_tbl_name
| RENAME {INDEX | KEY} old_index_name TO new_index_name
| ORDER BY col_name[, col_name] ...
USE tableDB;
ALTER TABLE userTBL
ADD homepage VARCHAR(30) ---열추가
DEFAULT 'http://www.hanbit.co.kr' -- 디폴트 값
NULL; --NULL 허용
_ 열 삭제
회원테이블에서 전화번호 열을 삭제하려면 다음과 같이 한다.
ALTER TABLE userTBL
DROP COLUMN mobile1;
ALTER TABLE userTBL
CHANGE COLUMN userName uName VARCHAR(20) NULL;
ALTER TABLE userTBL
DROP PRIMARY KEY;
외래키로 연결되어 있으면 오류가 발생하기 때문에 외래키 삭제
ALTER TABLE buyTBL
DROP FOREIGN KEY 외래키이름;
테이블과 똑같은 모양을 가진 뷰는 한마디로 '가상의 테이블'이라고 정의할 수 있다.
뷰는 실체가 없지만 마치 실체가 있는 것처럼 보인다.
뷰는 한 번 생성해 놓으면 테이블로 생각하고 사용해도 될 만큼 사용자가 볼 때 테이블과 거의 동일한 개체로 여겨진다.
CREATE VIEW v_userTBL
AS
SELECT userID, userName, addr FROM userTBL;
뷰를 생성한 후에는 새로운 테이블이라고 생각하면 된다.
SELECT * FROM v_userTBL;
뷰를 테이블로 여기고 접근해도 원래 테이블을 이용하여 접근한 것과 동일한 결과를 얻을 수 있다.
뷰의 데이터는 수정 가능할까? 뷰는 기본적으로 '읽기 전용'으로 사용되지만 뷰를 통해 원래 테이블의 데이터를 수정할 수도 있다. (바람직하진 않음)