📌 DDL (Data Definition Language)
- 데이터 정의어 (DDL)
- 테이블이나 관계의 구조를 생성하는데 사용
- 스키마, 도메인, 테이블, 뷰, 인덱스를 정의/변경/제거
CREATE, ALTER, DROP 문
📌 CREATE
- 데이터베이스(스키마), 테이블, 뷰, 인덱스, 사용자계정 등 생성(정의)
CREATE SCHEMACREATE DATABASECREATE TABLECREATE VIEWCREATE INDEXCREATE USER
1️⃣ Schema 생성/삭제
1. Schema Wizard 활용
1) 좌측 Schemas - 마우스 우클릭 - Create Schema...
2) Name 설정
3) Charset/Collation : 데이터에 따로 유동적이나 통상적으로 utf8mb4 사용
4) Apply 클릭
5) 좌측과 같은 쿼리문이 실행될 것이라는 안내, Apply 클릭
6) 에러가 발생하면 에러 내용을 안내하고, 없다면 우측과 같이 Finish 클릭
2. 쿼리문으로 직접 생성
CREATE SCHEMA 데이터베이스명 [DEFAULT CHARACTER SET utf8mb4];
CREATE DATABASE 데이터베이스명 [DEFAULT CHARACTER SET utf8mb4];
CREATE SCHEMA shopdb DEFAULT CHARACTER SET utf8mb4;
3. Schema 삭제
DROP DATABASE 데이터베이스명
DROP SCHEMA 데이터베이스명
DROP SCHEMA shopdb;
2️⃣ Table 생성/삭제
1. Table Wizard 활용
1) 좌측 Schemas - Schema 선택 - Tables 우클릭 - Create Table...
2) Table Name 설정
3) Charset/Collation : 데이터에 따로 유동적이나 통상적으로 utf8mb4 사용
4) Column Name 설정
5) Datatype : Column 의 데이터 형태에 맞는 타입 선택
6) PK (Primary Key) : 기본 키
- 후보 키 중 대표 키로 선택된 필드
- 기본 키는 하나 이상의 필드 조합으로 생성 가능
7) NN (Not Null) : Null 값 허용 여부
- Primary Key 는 Not Null 이 기본값
8) Apply 클릭, Fnish
2. 쿼리문으로 직접 생성
- 테이블 생성 전 사용할 데이터베이스(스키마)를 선택해야 한다.
USE 데이터베이스명
USE shopdb;
CREATE TABLE 테이블명 (
열이름 데이터타입 [제약조건],
열이름 데이터타입 [제약조건],
열이름 데이터타입 [제약조건],
CONSTRAINT 제약조건명 PRIMARY KEY 컬럼이름,
CONSTRAINT 제약조건명 FOREIGN KEY 컬럼이름
REFERENCES 테이블명(컬럼이름)
);
- 제약조건
NOT NULL : 빈값(NULL) 허용XUNIQUE : 중복값 허용XDEFAULT : 기본값 설정PRIMARY KEY : 기본키 설정FOREIGN KEY 컬럼명 REFERENCES 테이블명(컬럼명) : 외래키 설정CHECK 체크조건 : 특정 내용의 제약조건 (값 범위 등)ON DELETE / ON UPDATE : 참조되는 테이블의 행 삭제/갱신 시 옵션
CONSTRAINT 는 PK / FK 지정시 별칭 사용하고자 할 때 사용한다.
- Ex) Python 의 import pandas as pd 와 같은 별칭
CREATE TABLE product(
prdNo VARCHAR(10) NOT NULL,
prdName VARCHAR(30) NOT NULL,
prdPrice INT,
prdCompany VARCHAR(30),
CONSTRAINT PK_product_prdNo PRIMARY KEY (prdNo)
);
CREATE TABLE publisher(
pubNo VARCHAR(10) NOT NULL PRIMARY KEY,
pubName VARCHAR(30) NOT NULL
);
FOREIGN KEY : 외래키, 다른 테이블의 기본키를 갖는 컬럼, 두 테이블을 연결
CREATE TABLE book(
bookNo VARCHAR(10) NOT NULL,
bookName VARCHAR(30) NOT NULL,
bookPrice INT DEFAULT 10000 CHECK(bookPrice > 1000),
bookDate DATE,
pubNo VARCHAR(10) NOT NULL,
CONSTRAINT PK_book_bookNo PRIMARY KEY (bookNo),
CONSTRAINT FK_book_pubNo FOREIGN KEY (pubNo) REFERENCES publisher(pubNo)
);
3️⃣ Table 정보 조회
1) 첫번째 방법
- Schemas 에서 Table 을 선택한 후 좌측 하단을 보면
Information UI가 있다. 여기서는 간단하게 테이블명과 컬럼명, 일부 속성을 확인할 수 있다.
bookNo 의 경우 볼드체와 밑줄이 그어져 있는데, 이는 Primary Key 임을 나타낸다.pubNo 의 경우 볼드체이나 밑줄은 없는데, 이는 Foreign Key 임을 나타낸다.
2) 두번째 방법
- Table 생성시에 이용했던 마법사를 활용하는 방법으로, Schemas 에서 원하는 Table 위에 마우스를 올려 스패너 모양을 클릭하면 마법사로 이동하여 정보 조회 및 설정 변경이 가능하다.
4️⃣ 주의사항
- 쿼리문으로 테이블 생성시
FOREIGN KEY 를 가진 테이블이 여러 개일 경우, 테이블 생성 순서가 중요하다.
- 테이블A 의 컬럼을 참조하는 테이블B 를 테이블A 생성 전에 생성하고자 하면 당연하게도 에러가 발생한다. 따라서 이 경우에는 테이블A - 테이블B 의 순서로 테이블을 생성해야 한다.
- 다음 쿼리문을 확인해보자.
CREATE SCHEMA testdb2 DEFAULT CHARACTER SET utf8mb4;
USE testdb2;
CREATE TABLE department(
dptNo CHAR(5) NOT NULL,
dptName VARCHAR(30) NOT NULL,
dptTel VARCHAR(15),
CONSTRAINT PK_department_dptNo PRIMARY KEY (dptNo)
);
department 의 컬럼 참조를 위해 department 테이블 생성
CREATE TABLE student(
stdNo CHAR(10) NOT NULL,
stdName VARCHAR(30) NOT NULL,
stdYear INT DEFAULT 4 CHECK (stdYear >=1 AND stdYear <=4),
stdAddress VARCHAR(50),
stdBirthDay DATE,
dptNo CHAR(5) NOT NULL,
CONSTRAINT PK_student_stdNo PRIMARY KEY (stdNo),
CONSTRAINT FK_student_dptNo FOREIGN KEY (dptNo) REFERENCES department(dptNo)
);
CREATE TABLE professor(
profID VARCHAR(10) NOT NULL,
profName VARCHAR(30) NOT NULL,
profPos VARCHAR(20),
profTel VARCHAR(15),
dptNo CHAR(5) NOT NULL,
CONSTRAINT PK_professor_profID PRIMARY KEY (profID),
CONSTRAINT FK_professor_dptNo FOREIGN KEY (dptNo) REFERENCES department(dptNo)
);
professor 의 컬럼 참조를 위해 professor 테이블 생성
CREATE TABLE course(
courseID VARCHAR(10) NOT NULL,
courseTitle VARCHAR(30) NOT NULL,
courseCredit INT,
profID VARCHAR(10) NOT NULL,
CONSTRAINT PK_course_courseID PRIMARY KEY (courseID),
CONSTRAINT FK_course_profID FOREIGN KEY (profID) REFERENCES professor(profID)
);
CREATE TABLE scores(
stdNo CHAR(10) NOT NULL,
courseID VARCHAR(10) NOT NULL,
score INT,
grade VARCHAR(2),
CONSTRAINT PK_scores_stdNo_courseID PRIMARY KEY (stdNo, courseID),
CONSTRAINT FK_scores_stdNo FOREIGN KEY (stdNo) REFERENCES student(stdNo),
CONSTRAINT FK_scores_courseID FOREIGN KEY (courseID) REFERENCES course(courseID)
);
Error Code: 1068 Multiple primary key defined
CREATE TABLE scores(
stdNo CHAR(10) NOT NULL PRIMARY KEY,
courseID VARCHAR(10) NOT NULL PRIMARY KEY,
score INT,
grade VARCHAR(2)
);
5️⃣ AUTO_INCREMENT (자동증가)
- 특정 컬럼에
AUTO_INCREMENT 옵션을 통해 NOT NULL 옵션을 무시하고 자동으로 데이터가 입력되게 할 수 있다.
- 아래 테이블에서는
boardId 에 데이터를 입력하지 않아도 자동으로 1, 2, 3 순으로 채워진다.
CREATE TABLE board(
boardId INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
boardTitle VARCHAR(30) NOT NULL,
boardAuthor VARCHAR(30),
boardContent VARCHAR(200) NOT NULL
);
ALTER TABLE 테이블명 AUTO_INCREMENT = 정수;
- 초기값을 설정할 수 있다.
ALTER TABLE board2 AUTO_INCREMENT = 100;
- 단,
AUTO_INCREMENT 는 한개의 컬럼에만 적용할 수 있다.
- 두 개의 컬럼에 적용하려 하니 아래와 같은 에러가 발생
Error Code: 1075. Incorrect table definition; there can be only one auto column and it must be defined as a key
SET @@auto_increment_increment=3;
- 자동증가폭을 설정할 수 있다.
- 일부 데이터가 있는 경우 마지막 데이터 값에서부터 증가폭이 적용된다.
- Ex) 1 2 3 데이터가 이미 있는 경우 4 7 10 순서대로 채워진다.
- 단, 이 옵션은 스키마에 있는 모든 테이블에 적용되기 때문에 사용시 주의해야 한다.
SET @@auto_increment_increment=3;
