[DDL, 카카오 클라우드 스쿨 4주차 -2, 3] DDL

DDL(Data Definition Language)

-> 데이터 구조를 생성하고 변경하고 삭제하는 명령어

---

1.  테이블 생성

1) 기본 형식

CREATE [Temporary] TABLE 테이블이름(칼럼이름 자료형 [칼럼 제약 조건], ...  [테이블 제약 조건])조건 나열;

 

2) 자료형

• 숫자: TINYINT(1바이트 - 참/거짓), INT(INTEGER), FLOAT, DOUBLE
• 문자: CHAR(길이- 길이가 고정), VARCHAR(길이- 길이가 가변), TEXT(긴 문자열), BLOB(파일의 내용 저장)

파일을 데이터베이스에 저장하는 방법
- 파일의 경로를 저장하는 방법: 파일을 별도로 저장하고 그 경로를 저장  (대부분 이 방법을 사용)
- 파일의 내용을 저장하는 방법: 파일을 별도로 저장하지 않고 데이터베이스에 저장(BLOB)

• 날짜: DATE(날짜), DATETIME(날짜와 시간), TIMESTAMP(날짜와 시간), TIME(시간), YEAR(년도)
• 기타: JSON, GEOMETRY(공간 정보)

 

3) 조건 나열

• ENGINE: MyISAM(Indexed Sequential Acces Media - 조회에 유리)이나 InnoDB(삽입 삭제 갱신에 유리)를 설정할 수 있는데
• DEFAULT CHARSET: 한글이 깨지는 경우 한글 설정하는 옵션으로 utf8을 설정해주면 되는 Maria DB는 기본 utf8
• auto_increment=시작숫자: 일련번호를 사용할 때 시작 숫자부터 시작
• Timezone 설정: Mac에서 사용할 때 시간 대역이 안 맞아서 설정해주어야 하는 경우가 있습니다.
•  

4) 테이블 생성

• 테이블 이름: concat

칼럼
     num - 정수
     name - 문자열이고 영문 20자까지 저장하고 자주 변경
     address- 문자열이고 영문 100자까지 저장하고 자주 변경되지 않음
     tel- 문자열로 영문 20자까지 저장하고 자주 변경됨
     email- 문자열로 영문 100자까지 저장하고 자주 변경됨
     birthday - 날짜

비밀번호를 저장할 때는 대부분의 경우 해시를 수행해서 해시 코드를 저장하기 때문에 해시 방법에 따라 64, 128, 256으로 설정해야 합니다.

CREATE table concat(
	num integer,
    name varchar(20),
    address char(100),
    tel char(20),
    email char(100),
    birthday date
)ENGINE=MyISAM;

 

---

2. 테이블 구조 변경

1) 기본 형식

ALTER TABLE 테이블이름 작업 매개변수 나열

 

2) 테이블 구조 확인

DESC 테이블이름;

 

3) 칼럼 추가

ALTER TABLE 테이블이름 ADD 칼럼이름 자료형 제약조건;

 

-> 칼럼을 추가하는 경우 기존에 데이터가 존재했다면 모두 null로 삽입

// concat 테이블에 age라는 칼럼을 정수로 추가
ALTER TABLE concat ADD age integer;

 

4) 칼럼 삭제

ALTER TABLE 테이블이름 DROP 칼럼이름;

# concat 테이블에서 age 칼럼 삭제
ALTER TABLE concate DROP age;

 

5) 칼럼 변경

• 이름과 자료형 변경
  • ATLER TABLE 테이블이름 CHANGE 기존칼럼이름 새로운 칼럼이름 자료형 제약조건;
• 자료형만 변경하는 경우 - NOT NULL에 대한 설정 포함
  • 크기가 변경되는 경우 기존의 크기보다 커지는 것은 아무런 문제가 없지만 작아지는 것은 데이터의 손실이 발생할 수 있으므로 주의
  • ALTER TABLE 테이블이름 MODIFY 기존칼럼이름 자료형;

# concate 테이블에서 tel이라는 칼럼을 phone이라는 칼럼으로 변경
ALTER TABLE concat CHANGE tel phone varchar(11);

• 칼럼을 추가하거나 삭제하는 명령은 관계형 데이터베이스에서 거의 비슷하지만 칼럼을 변경하는 명령은 데이터베이스마다 다름

6) 칼럼 순서 조정

• 새로운 칼럼이 추가되면 맨 뒤에 추가
• 칼럼을 맨 앞으로 이동
  • ALTER TABLE 테이블이름 MODIFY COLUMN 칼럼이름 자료형 FIRST
• 칼럼을 특정 칼럼 뒤로 이동
  • ALTER TABLE 테이블이름 MODIFY COLUMN 칼럼이름 자료형 AFTER 앞에 있는 칼럼이름;

 

6) 테이블 이름 수정

ALTER TABLE 원래테이블이름 RENAME 새로운테이블이름

 

---

3. 테이블 삭제

1) 기본 형식

DROP TABLE 테이블이름;

 

2) 테이블이 삭제가 되지 않는 경우

-> 외래 키로 참조되는 테이블은 외래 키를 소유하고 있는 테이블이 먼저 삭제되어야 합니다.

 

# contact 테이블 삭제
DROP TABLE contact;

---

4. 테이블의 모든 데이터 삭제

TRUNCATE TABLE 테이블이름;

---

5. 테이블 압축

• CREATE TABLE 다음에 ROW_FORMAT=COMPRESSED 옵션을 추가하면 테이블을 압축해서 생성됨
• 저장 공간을 줄일 수 있지만 작업 속도는 느려집니다.

---

6. 주석 설정

COMMENT ON TABLE 테이블이름 IS '주석';

 

---

7. 제약조건(Constraint)

1) 무결성 제약 조건

• Entitiy Integrity(개체 무결성): 기본키는 NULL이거나 중복될 수 없다.
• Referential Integrity(참조 무결성): 외래키는 참조할 수 있는 값이나 NULL을 가져야 한다.
• Domain Integrity(도메인 무결성): 속성의 값은 정해진 도메인의 값을 가져야 한다.

 

2) NOT NULL

• NULL일 수 없다는 제약조건
• 필수 입력
• 칼럼의 크기와 관련이 있기 때문에 칼럼을 만들 때 제약조건을 설정해야 합니다.
  • 테이블 제약조건으로 만들 수 없습니다.
  • 칼럼 이름 자료형 NOT NULL의 형태로 설정
• 기본은 NULL을 허용하는 것

 

3) DEFAULT

• 데이터베이스 이론에서는 DEFAULT는 제약조건이 아님
• 입력하지 않았을 때 기본적으로 삽입되는 데이터
• DEFAULT 값의 형태로 지정
• 숫자는 0, 문자열의 경우 ''이나 'N/A' 등을 많이 설정하고 날짜의 경우는 현재 시간(CURRENT_TIMESTAMP나 NOW 등)을 많이 사용

 

4) CHECK

• 값의 종류나 범위를 제한하기 위한 제약조건
• 설정 방법
  • CHECK(칼럼 이름 조건);
• GENDER 칼럼은 문자 3자인데 남 또는 여만 가져야 하는 경우
  • GENDER CHAR(3) CHECK(GENDER IN('남', '여'));
• SCORE 칼럼은 정수인데 0~ 100까지의 값만 가져야 하는 경우
  • SCORE INTEGER CHECK(SCORE BETWEEN 0 AND 100);

 

5) PRIMARY KEY(기본키)

• 테이블에서 PRIMARY KEY는 한 번만 설정 가능
• 2개 이상의 칼럼으로 PRIMARY KEY(복합 키)를 설정하는 경우는 테이블 제약조건으로 설정해야 함
  
  • 학습을 할 때는 복합 키를 거의 사용하지 않지만 실무에서 복합키를 사용하는 경우가 종종 발생

# 칼럼 제약 조건으로 설정
CREATE TABLE MEMBER(
	ID VARCHAR(50) PRIMARY KEY
);

# 테이블 제약조건으로 설정
CREATE TABLE MEMBER(
	ID VARCHAR(50),
    
    PRIMARY KEY(ID)
);

# ID와 NAME을 합쳐서 PRIMARY KEY로 설정
CREATE TABLE MEMBER(
	ID VARCHAR(50),
    NAME VARCHAR(50),
    
    PRIMARY KEY(ID, NAME)
);

• PRIMARY KEY는 자동으로 클러스터 인덱스(저장 순서대로 만들어지는 인덱스- 하나만 생성)를 생성
  • PRIMARY KEY를 이용해서 조회할 때 가장 빠른 성능을 나타냄
  • PRIMARY KEY를 설정하는 것은 중요한 작업 중 하나
• PRIMARY KEY는 NOT NULL이고  UNIQUE

 

6) UNIQUE

• 중복 값을 가질 수 없도록 하는 제약조건
• NULL을 허용
• 인덱스를 생성하는데 PRIMARY KEY가 없으면 UNIQUE가 클러스터 인덱스가 되고 PRIMARY KEY가 있으면 보조 인덱스가 됩니다.
• PRIMARY KEY와 더불어 다른 테이블에서의 FOREIGN KEY가 될 수 있습니다.
  • 시험에서는 PRIMARY KEY만 FOREIGN KEY가 될 수 있다고 합니다.

 

7) 제약조건 이름 설정

• 제약조건을 설정할 때 앞의 CONSTRAINT 제약조건 이름을 추가하면 제약조건 이름이 만들어집니다.
• 일반적으로 테이블 이름과 제약조건의 약자로 조합해서 만드는 경우가 많습니다.
  • PRIMARY KEY - pk
  • NOT NULL - nn
  • UNIQUE - uk
  • CHECK - ck
  • FOREIGN KEY- fk

 

8) 제약조건 수정

• 제약조건 수정
  • ALTER TABLE 테이블이름 MODIFY 칼럼이름 자료형 [CONSTRAINT 이름] 제약 조건;
• 제약조건 추가
  • ADD TABLE 테이블이름 ADD [ CONSTRAINT 이름] 제약조건(칼럼이름);
  • NOT NULL을 추가로 설정하는 경우는 제약조건을 추가하는 것이 아니고 칼럼의 자료형을 수정하는 것입니다.
• 제약조건 삭제
  • ALTER TABLE 테이블이름 DROP CONSTRAINT 제약조건이름;

 

9) Sequence(일련번호)

• 칼럼 이름 뒤에 AUTO_INCREMENT를 설정하면 일련번호가 만들어집니다.
  • AUTO_INCREMENT가 설정된 칼럼은 값을 대입하지 않아도 됩니다.
• 테이블을 생성할 때 초기 값을 설정할 수 있습니다.
• 일련번호 초기값 수정
  • ALTER TABLE 테이블이름 AUTO_INCREMENT = 값;
• AUTO_INCREMENT는 PK나 UK를 설정해야 하고 테이블에서 한 번만 설정 가능

 

10) 참조 무결성

• tEmployee 테이블(직원에 대한 정보)과 tProject 테이블(직원이 수행한 프로젝트에 대한 정보) 생성
• FOREIGN KEY를 설정하지 않은 경우

CREATE TABLE tEmployee(
	name VARCHAR(20) PRIMARY KEY,
    salary INTEGER NOT NULL,
    addr CHAR(100) NOT NULL
);

CREATE TABLE tProject(
	projected INTEGER AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(20),
	project VARCHAR(50),
	cost INTEGER
);

INSERT INTO tProject(name, project, cost) VALUES('아이린', 'KB', 100);
# 아이유는 tEmployee 테이블에 존재하지 않지만 외래키가 설정되지 않았으므로 삽입 가능
INSERT INTO tProject(name, project, cost) VALUES('아이유', 'KB', 10000);

# tProject 삭제
DROP TABLE tProject;

• 외래 키 설정
  • 외래키는 상대방 테이블에서 PRIMARY KEY나 UNIQUE 제약 조건이 설정되어 있어야 함
  • 칼럼 제약 조건으로 설정- 칼럼 이름 자료형 [CONSTRAINT 제약조건이름] REFERENCES 참조하는테이블이름(칼럼이름) 옵션
  • 테이블 제약조건 설정- [CONSTRAINT 제약조건이름] FOREIGNKEY(칼럼이름) REFERENCES 참조하는테이블이름(칼럼이름) 옵션

# 외래키를 설정해서 테이블을 생성하고 데이터를 삽입
CREATE TABLE tProject(
	projected INTEGER AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(20) REFERENCES tEmployee(name),
	project VARCHAR(50),
	cost INTEGER
);

INSERT INTO tProject(name, project, cost) VALUES('수지', 'KB', 100);
#외래키는 NULL은 가능
INSERT INTO tProject(name, project, cost) VALUES(NULL, 'KB', 100);
#에러 발생
# 아이유는 tEmployee 테이블에 존재하지 않는 데이터이기 때문
INSERT INTO tProject(name, project, cost) VALUES('아이유', 'KB', 10000);

 

 

11) 외래키 옵션

• 옵션 없이 FOREIGN KEY를 설정하게 되면 외래키로 참조되는 데이터는 삭제할 수 없습니다.

     참조되지 않는 데이터는 삭제 가능합니다. 

• 외래키에 의해서 참조되는 테이블은 먼저 삭제할 수 없고 외래키를 소유하고 있는 테이블을 삭제한 후 삭제를 해야 합니다.

# 조이는 참조되지 않고 있기 때문에 삭제가 가능하지만 수지는 참조되고 있어서 삭제가 불가능
DELETE FROM tEmployee where name ='조이';
DELETE FROM tEmployee where name ='수지';

• 외래키 설정할 때 옵션
  • ON DELETE [ NO ACTION | CASCADE | SET NULL | SET DEFAULT ]
  • ON UPDATE [ NO ACTION | CASCADE | SET NULL | SET DEFAULT ]
  • NO ACTION은 아무것도 하지 않음
  • CASCADE 같이 삭제되거나 수정
  • SET NULL은 NULL로 변경
  • SET DEFAULT는 DEFAULT 값으로 변경
  • ON UPDATE는 잘 사용하지 않는데 이유는 일반적으로 PRIMARY KEY는 불변의 성격을 갖기 때문
  • 외래키를 설정할 때 SET NULL