[MySQL] SQL 문법 정리 (2) : CREATE TABLE (자료형, 관계(기본키, 외래키), 제약 조건)

 

 

 

CREATE TABLE : 테이블 생성

 

CREATE TABLE TABLE_NAME {
	#...
};

 

 

---

 

1. Table의 컬럼과 자료형 지정 

 

 

CREATE TABLE employee (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    age TINYINT,
    department_id SMALLINT,
    salary INT
);

 

정수형 (Integer Types)

• TINYINT: 1 바이트
• SMALLINT: 2 바이트
• MEDIUMINT: 3 바이트
• INT (또는 INTEGER): 4 바이트

 

 

CREATE TABLE product (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    price DECIMAL(10, 2),
    weight FLOAT
);

 

부동소수점형 (Floating-Point Types)

• FLOAT: 4 바이트 부동소수점 숫자
• DOUBLE: 8 바이트 부동소수점 숫자
• DECIMAL 또는 NUMERIC: 고정 소수점 숫자
  • DECIMAL(p, s) 데이터 형식에서 p는 전체 자릿수(precision)를, s는 소수점 이하 자릿수(scale)

 

 

 

 

CREATE TABLE customer (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    content TEXT
);

 

문자열형 (String Types)

• CHAR(n): 고정 길이 문자열, 최대 길이 n까지의 문자
• VARCHAR(n): 가변 길이 문자열, 최대 길이 n까지의 문자
• TEXT: 긴 문자열
• ENUM: 몇 가지 문자열 값 중 하나
• SET: 몇 가지 문자열 값 중 여러 개

 

 

 

CREATE TABLE order (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    order_date DATE,
    delivery_date DATETIME,
    last_updated TIMESTAMP
);

 

날짜 및 시간형 (Date and Time Types)

• DATE: 날짜 
• TIME: 시간 
• DATETIME: 날짜와 시간 
• TIMESTAMP: 날짜와 시간 
• YEAR: 연도 

 

 

 

CREATE TABLE document (
    document_id INT AUTO_INCREMENT PRIMARY KEY,
    document_name VARCHAR(100),
    content BLOB,
    info JSON
);

 

이진형 (Binary Types)

• BINARY(n): 고정 길이 이진 데이터
• VARBINARY(n): 가변 길이 이진 데이터
• BLOB: 긴 이진 데이터

 

 

 

CREATE TABLE document (
    location GEOMETRY,
    is_active BOOLEAN,
    info JSON
);

 

기타 형식 (Miscellaneous Types)

• BOOLEAN: 참 또는 거짓 
• JSON: JSON 데이터
• GEOMETRY: 지리 정보

 

 

 

---

 

2. 연관 관계 설정 : PRIMARY KEY와 FOREIGN KEY

 

기본키와 외래키 제약조건을 설정하여 테이블 간의 관계를 정의할 수 있다.

기본 키 (Primary Key)

기본키는 테이블 내의 각 행(Row, Record)을 고유하게 식별하는 열(Column)을 말한다.

단일 Column 뿐 만 아니라 Column의 조합을 통해서도 사용할 수 있다. 중복된 값을 허용하지 않으며, 모든 행에 대해 값을 가져야 한다.

기본 키를 정의하려면 CREATE TABLE 문에서 해당 열에 PRIMARY KEY 제약 조건을 추가한다.
주로 자동 증가(auto-increment)되는 정수형 열이 PK로 사용된다.

외래 키 (Foreign Key)

외래 키는 한 테이블에서 다른 테이블의 Primary Key와 관계를 맺는 열(Column)을 말한다.
다른 테이블과의 관계를 정의하고 데이터 무결성을 유지한다.

외래 키를 정의하려면 CREATE TABLE 문에서 해당 열에 FOREIGN KEY 제약 조건을 추가하고 참조하는 테이블과 열을 지정한다.

 

 

 

 

 

 

 

# create book : Book(bookid(PK), bookname, publisher, price)
create table Book(
 bookid int AUTO_INCREMENT PRIMARY KEY,
 bookname varchar(30) NOT NULL,
 publisher varchar(30),
 price int
);

• bookid는 PK이고, 컬럼을 지정할 때 함께 PK로 지정하면서 AUTO_INCREMENT로 정수가 자동으로 생성되도록 조건을 설정하였다.

 

# create customer : Customer(custid(PK), name, address, phone)

create table Customer(
 custid char(5),
 name varchar(30),
 address varchar(30),
 phone varchar(15),
 PRIMARY KEY(custid)
);

• custid는 PK이고, 컬럼을 지정한 후, 따로 제약조건을 설정하는 방식으로도 Key 설정을 할 수 있다.

 

# create orders : Orders(orderid(PK), custid(FK), bookid(FK), saleprice, orderdate)

create table Orders(    
 orderid char(5) PRIMARY KEY,
 saleprice int, 
 orderdate DATETIME,     
 custid char(5),
 bookid int,
 FOREIGN KEY (custid) REFERENCES Customer(custid),     
 FOREIGN KEY (bookid) REFERENCES Book(bookid) ON DELETE CASCADE ON UPDATE CASCADE
);

• bookid와 custid는 FK이고, 해당 키를 통해서 Customer와 Book과의 관계를 가지며, 참조하여 조회가 가능하다.
• CASCADE 옵션은 데이터베이스에서 FK 관계가 설정되어 있을 때 사용되며, 부모 테이블의 레코드가 UPDATE 또는 DELETE될 때 자식 테이블에 어떤 작업을 수행해야 하는지를 정의한다. 데이터 일관성을 유지하고 연관된 데이터를 일괄적으로 처리하는데 사용된다.
• DELETE CASCADE와 UPDATE CASCADE 옵션을 설정한 Book의 경우, 연관된 Book의 레코드가 수정되면, Orders의 bookid도 함께 수정되고, 삭제되면 Orders 테이블의 연관된 레코드들도 함께 삭제된다.

 

 

 

---

 

 

3. 제약 조건 (CONSTRAINT)

 

제약 조건은 데이터베이스에서 특정 컬럼 또는 테이블에 적용되며, 데이터가 특정 조건을 충족해야 함을 정의한다.

위의 PK와 FK 설정 또한 제약조건 설정에 해당된다.

 

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE,
    email VARCHAR(100) UNIQUE NOT NULL,
    age INT CHECK (age >= 18),
    INDEX (age)
);

 

• UNIQUE : 중복된 값을 허용하지 않음
• NOT NULL : NULL 값을 허용하지 않아야 함 (항상 값을 가져야 함)
• CHECK : 특정 조건을 충족하게 할 수 있다. 
• INDEX : 컬럼에 대한 인덱스를 생성하여 데이터베이스에서 검색 및 쿼리 성능을 향상시킨다. 검색 조건과 정렬을 빠르게 수행하기 위해 사용된다.