데이터베이스 Day2 - 데이터 정의어(DDL)

SQL

SQL (데이터베이스 표준 질의어)

• 관계형 데이터베이스 관리 시스템(RDBMS)의 데이터베이스, 데이터를 관리하기 위해 설계된 특수 목적의 프로그래밍 언어
• 질의어(Query Launguage)는 검색 언어라는 의미
• 데이터를 검색하는 역할 외에 데이터 입력, 수정, 삭제, 제어, 복구 등 다양한 기능을 제공하는 종합적인 언어
• Structured Query Language
• 에스큐엘 또는 시퀄이라 읽음
• DBMS 제품 별 특화된 SQL 사용

SQL 문의 기능별 분류

데이터 정의어(DDL)

• 데이터베이스, 테이블 구조를 생성 / 수정 / 삭제하는데 사용
• 스키마, 테이블, 뷰, 인덱스 정의, 변경, 삭제할 때 사용하는 명령문
• CREATE / ALTER / DROP
  • CREATE: 데이터베이스, 테이블 등 객체 생성
  • ALTER: 기존에 존재하는 데이터베이스 객체 변경
  • DROP: 데이터베이스 등 객체 삭제

데이터 조작어(DML)

• 테이블의 데이터를 검색, 삽입, 수정, 삭제하는데 사용
• 고급 프로그래밍 언어로 작성된 응용프로그램에 내포되어 사용
• SELECT / INSERT / UPDATE / DELETE
  • SELECT: 데이터 검색
  • INSERT: 데이터 입력
  • UPDATE: 데이터 수정
  • DELETE: 데이터 삭제

데이터 제어어(DCL)

• 데이터의 사용 권한 등 관리에 사용
• GRANT / REVOKE / COMMIT / ROLLBACK
  • GRANT: 권한 부여
  • REVOKE: 권한 취소
  • COMMIT: 변경된 내용을 영구적으로 DB에 반영하는 것
  • ROLLBACK: 변경된 내용을 취소하고 DB를 이전 상태로 되돌리는 것(특정 시점)

스키마 생성 (CREATE)

• CREATE SCHEMA / CREATE DATABASE
• SCHEMA와 DATABASE는 동일 의미

CREATE SCHEMA 스키마명;
CRREATE DATABASE 스키마명;

-- 스키마 (데이터베이스) 생성
CREATE SCHEMA sqldb DEFAULT CHARACTER SET utf8;
CREATE DATABASE sqldb2 DEFAULT CHARACTER SET utf8mb4;

스키마 삭제 (DROP)

• DROP SCHEMA / DROP DATABASE
• SCHEMA와 DATABASE는 동일 의미이기 때문에
• DROP SCHEMA sqldb2를 해도 스키마 삭제가 가능

DROP SCHEMA 스키마명;
DROP DATABASE 스키마명;

-- 스키마(데이터베이스) 삭제 (DELETE) 
-- DROP SCHEMA sqldb2를 해도 동일한 의미이므로 삭제 됨
DROP DATABASE sqldb2;

테이블 생성

• CREATE TABLE
• 속성(열)과 속성에 관한 제약 정의
• 기본키(PRIMARY KEY), 외래키(FOREIGN KEY) 정의

CREATE TABLE 테이블명(
    열이름 데이터타입(크기) [제약조건 리스트(생략 가능)],
    열이름 데이터타입(크기) ~~~,
    열이름...
);

사용할 데이터베이스 지정

use sqldb;

상품 테이블 생성

• 제약조건
  • 기본키: prdNO
  • prdNO, prdName: NOT NULL
  • prdPrice, prdCompany

CREATE TABLE product(
    prdNo VARCHAR(10) NOT NULL PRIMARY KEY,
    prdName VARCHAR(30) NOT NULL,
    prdPrice INT,
    prdCompany VARCHAR(30)
);

PRIMARY KEY 제약조건

• 기본키 제약조건
• 열에 지정
• NULL 값 안됨
• (자동으로 중복 안됨)

PK 제약조건 설정방법 1

CREATE TABLE product(
    prdNo VARCHAR(10) NOT NULL PRIMARY KEY,
    prdName VARCHAR(30) NOT NULL,
    prdPrice INT,
    prdCompany VARCHAR(30)
);

PK 제약조건 설정방법 2

CREATE TABLE product2(
    prdNo VARCHAR(10) NOT NULL,
    prdName VARCHAR(30) NOT NULL,
    prdPrice INT,
    prdCompany VARCHAR(30),
    PRIMARY KEY(prdNo)
);

PK 제약조건 설정방법 3

CREATE TABLE product3(
    prdNo VARCHAR(10) NOT NULL,
    prdName VARCHAR(30) NOT NULL,
    prdPrice INT,
    prdCompany VARCHAR(30),
    CONSTRAINT PK_product_prdNo PRIMARY KEY(prdNo)
);

상세 정보 출력

DESCRIBE product;

출판사 테이블 생성(출판사 번호, 출판사명)

• 제약조건 설정
  • 기본키로 pubNo NOT NULL
  • pubName NOT NULL

CREATE TABLE publisher(
    pubNo VARCHAR(10) NOT NULL PRIMARY KEY,
    pubName VARCHAR(30) NOT NULL
);

FOREIGN KEY 제약조건

• REFERENCES 참조하는 테이블명(기본키) : 현재의 외래키가 기본키로 소속되어 있는 테이블(부모 테이블)
• CONSTRAINT: 제약조건

CONSTRAINT 외래키이름 FOREIGN KEY(외래키로 사용하는 열이름) REFERENCES 참조하는 테이블명(기본키)

도서 테이블 설정

• 외래키(출판사 번호 pubNo) 제약조건 설정
• 외래키(출판사 번호 pubNo) 제약조건 설정
• 기타 제약조건 설정
  • 기본키: bookNo NOT NULL
  • 외래키: pubNo (참조 테이블의 기본키와 동일하게 설정)
  • bookPrice: 기본값(DEFAULT) 10000, 1000보다 크게 설정(CHECK(bookPrice > 1000))

CREATE TABLE book(
    bookNo VARCHAR(10) NOT NULL PRIMARY KEY,
    bookName VARCHAR(30) NOT NULL,
    bookPrice INT DEFAULT 10000 CHECK(bookPrice > 1000),
    bookDate date,
    pubNo VARCHAR(10) NOT NULL,
    CONSTRAINT FK_book_publisher FOREIGN KEY(pubNo) REFERENCES publisher(pubNo)
);

• PRI: 기본키
• MUL: 중복 가능한 키(외래키)
• 외래키로 지정된 열에는 동일 값(중복값) 저장 가능
  • 강남 출판사에서 출간한 도서가 여러 권 있는 것과 동일

테이블 생성 순서 주의!!

• 외래키로 제약조건을 설정할 경우
• 테이블 생성순서
  1. publisher(기본키로 설정 해놓기)
  2. book (book 테이블에서 외래키로설정 가능하기 때문에)
• 테이블 생성 후 데이터 입력 시 주의사항
  • publisher의 기본키인 pubNo에 값을 먼저 입력해야 book의 pubNo 입력할 때 오류 없음
  • 즉, 외래키 값을 입력할 때는 참조되는 테이블의 기본 키로서의 값과 동일해야함(참조 무결성 제약조건)

테이블 삭제 시 주의!!

• publisher 먼저 삭제하면 외래키로 사용 중이라는 오류 발생 (book에서 pubNo 사용중이기 때문)
• book 테이블 먼저 삭제하고 publisher 삭제
• 즉, 외래키로 사용 중인 경우에는 참조되는 테이블(부모 테이블)의 기본키를 삭제할 수 없음(참조 무결성 제약조건)

데이터 입력 (INSERT 배우기 전이므로 표에 직접 삽입)

• publisher 테이블 먼저 입력
  1. 서울 출판사
  2. 도서출판 강남
  3. 정보 출판사
• book 테이블에 데이터 입력
  • 설정된 제약조건
    • bookName에 NULL값 삽입 불가능
    • bookPrice에 1000이하 값을 입력 시 CHECK 제약조건 충돌오류 발생
    • bookPrice에 입력하지 않으면(NULL) 10000으로 default 값 입력됨
    • book 테이블의 pubNo 입력 시 1,2,3 이외의 값 입력 시 외래키 제약조건 위배로 충돌 발생

연습문제

• 학생과 학과테이블 생성, 데이터 최소 3개씩 입력
• 제약 조건
  • 기본키 설정
  • 필요한 경우 외래키 설정
  • 학생은 학과에 소속
  • 학생 이름과 학과 이름은 NULL 값을 허용하지 않음
  • 학년은 4를 기본값으로, 범위는 1 ~ 4로 설정(AND 키워드 사용)
• 테이블 생성 순서
  1. 학과 테이블(department)
  2. 학생 테이블(student) : 학과 번호를 외래키로 설정

-- 학과 테이블 생성
CREATE TABLE department(
    dNo VARCHAR(30) NOT NULL PRIMARY KEY,
    dName VARCHAR(10) NOT NULL,
    dTel VARCHAR(13) 
);

-- 학생 테이블 생성
CREATE TABLE student(
    stdNo VARCHAR(10) NOT NULL PRIMARY KEY,
    stdName VARCHAR(30) NOT NULL,
    stdGrade INT DEFAULT 4 CHECK(stdGrade >= 1 AND stdGrade <=4),
    stdAddress VARCHAR(50),
    stdBirth DATE,
    dNo VARCHAR(10) NOT NULL,
    CONSTRAINT FK_student_department FOREIGN KEY(dNo) REFERENCES department(dNo)
);

• 테이블 3개 추가 생성 (교수 / 과목 / 성적)
• 학과 테이블
  • 학과코드(PK)
  • 학과명
  • 전화번호
• 학생 테이블
  • 학번(PK)
  • 성명
  • 학년
  • 주소
  • 생년월일
  • 학과코드(FK)
• 교수 테이블
  • 교수번호(PK)
  • 교수명
  • 직위
  • 전화번호
  • 학과코드(FK)
• 과목 테이블
  • 과목코드(PK)
  • 과목명
  • 학점수
  • 교수번호(FK)
• 성적
  • 학번(FK)
  • 과목코드(FK)
  • 성적
  • 등급

-- 교수 테이블 생성
CREATE TABLE professor(
    proNo VARCHAR(10) NOT NULL PRIMARY KEY,
    proName VARCHAR(30) NOT NULL,
    proPosition VARCHAR(20),
    proTel VARCHAR(13),
    dNo VARCHAR(10) NOT NULL,
    CONSTRAINT FK_professor_department FOREIGN KEY(dNo) REFERENCES department(dNo)
);

-- 과목 테이블 생성
CREATE TABLE subjects(
    subNo VARCHAR(10) NOT NULL PRIMARY KEY,
    subName VARCHAR(30) NOT NULL,
    subCnt INT,
    proNo VARCHAR(10) NOT NULL,
    CONSTRAINT FK_subject_professor FOREIGN KEY(proNo) REFERENCES professor(proNo)
);

-- 성적 테이블 생성
CREATE TABLE score (
    stdNo VARCHAR(10) NOT NULL,
    subNo VARCHAR(10) NOT NULL,
    scScore INT,
    scGrade VARCHAR(2),
    CONSTRAINT FK_score_stdNo_subNo PRIMARY KEY(stdNo, subNo),
    CONSTRAINT FK_score_student FOREIGN KEY(stdNo) REFERENCES student(stdNo),    
    CONSTRAINT FK_score_subjects FOREIGN KEY(subNo) REFERENCES subjects(subNo)    
);

기본키 값 자동 증가

• AUTO_INCREMENT
  • 속성 값을 자동으로 증가(아무 것도 지정하지 않으면 기본 1부터 시작해서 1씩 증가)
• AUTO_INCREMENT = 100
  • 기본 시작 값을 100으로 설정
• SET @@AUTO_INCREMENT_INCREMENT = 3
  • 3씩 증가

실습

• 게시판 테이블 생성
• 조건
  • 1부터 1씩 증가
  • 초기값 100으로 설정하고 3씩 증가

-- 게시판 테이블 생성
CREATE TABLE board(
    boardNo INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    boardTitle VARCHAR(30) NOT NULL,
    boardWriter VARCHAR(20),
    boardContent VARCHAR(100) NOT NULL
);

-- board2 생성 및 초기값 100으로 설정하고 3씩 증가
CREATE TABLE board2(
    boardNo INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    boardTitle VARCHAR(30) NOT NULL,
    boardWriter VARCHAR(20),
    boardContent VARCHAR(100) NOT NULL
);

ALTER TABLE board2 AUTO_INCREMENT = 100;

SET @@AUTO_INCREMENT_INCREMENT=3;

• 다시 1부터 1씩 증가하도록 변경

Safe Updates 모드 해제

• 수정, 삭제, 기본키 수정 등 안전성 작업을 못하도록 기본 설정되어있는 것 해제
• Edit / Preferences 창 열고 SQL Editor 선택한 후에 맨 아래에 있는 Safe Updates 체크해제하고 [OK] 닫음
• 반드시 Workbench 종료하고 다시 시작

-- 100부터 시작해서 3씩 증가하는 것을 
-- 1부터 1씩 증가하는 것으로 변경
SET @COUNT = 0;
UPDATE board2 SET boardNo = @COUNT:=@COUNT+1;

• 중간에 있는 값 삭제하고 전체를 다시 1부터 1씩 증가하도록 재정렬
  • 2번 항목을 삭제하고 새로 추가했더니 4부터 시작하느 것이 아니라 106 다음의 109부터 시작
  • 다시 0으로 설정 후 초기값 1로 다시 설정

SET @COUNT = 0;
UPDATE board2 SET boardNo = @COUNT:=@COUNT+1;

ALTER TABLE board2 AUTO_INCREMENT = 1;

• 4번 항목 다시 추가하면 원래대로 1부터 1씩 증가하는 테이블 생성됨

테이블 수정(ALTER)

• 테이블에 대한 정의(구조) 변경
• 새로운 열 추가, 특정 열의 디폴트값 변경, 특정 열 삭제 등 수정
• ALTER TABLE 테이블명
  • ADD: 열 추가
  • RENAME COLUMN: 열 이름 변경
  • MODIFY: 열의 데이터 형식 변경
  • CHANGE: 열 이름과 데이터 형식 동시 변경
  • DROP COLUMN: 열 삭제
  • DROP: 여러 개 열 삭제
  • DROP PRIMARY KEY: 기본 키 삭제
  • DROP CONSTRAINT: 제약조건 삭제

ALTER TABLE 테이블명 ADD ...
ALTER TABLE 테이블명 RENAME COLUMN ...
...

학생 테이블에 열 추가

• stdTel 열 추가

ALTER TABLE student ADD stdTel VARCHAR(13);

• 여러 개의 열 추가
  • stdAge, stdAddress 열 추가

ALTER TABLE student ADD (stdAge VARCHAR(2), stdAddress2 VARCHAR(50));

• 열의 데이터 형식 변경
  • stdAge 열의 데이터 타입 변경

ALTER TABLE student MODIFY stdAge INT;

• 열의 제약조건 변경
  • stdName을 NULL 허용으로 변경

ALTER TABLE student MODIFY stdName VARCHAR(2) NULL;

• 열 이름 변경
  • stdTel을 stdHP로 변경 (데이터 타입을 적으면 구문오류!!!)

ALTER TABLE student RENAME COLUMN stdTel TO stdHP;

• 열 이름과 데이터 타입 변경

ALTER TABLE student CHANGE stdAddress stdAddress1 VARCHAR(30);

• 열 삭제
  • stdHP 열 삭제

ALTER TABLE student DROP COLUMN stdHP;

• 여러 개의 열 삭제

ALTER TABLE student DROP stdAge, DROP stdAddress1, DROP stdAddress2;

연습문제

1. product 테이블에 숫자 값을 갖는 prdStock과 제조일을 나타내는 prdDate 열 추가
2. product 테이블의 prdCompany 열을 NOT NULL로 변경
3. publisher 테이블에 pubPhone, pubAddress 열 추가
4. publisher 테이블에서 pubPhone 열 삭제

-- 1번
ALTER TABLE product ADD (prdStock INT, prdDate DATE);

-- 2번
ALTER TABLE product MODIFY prdCompany VARCHAR(30) NULL;

-- 3번
ALTER TABLE publisher ADD(pubPhone VARCHAR(13), pubAddress VARCHAR(30));

-- 4번
ALTER TABLE publisher DROP COLUMN pubPhone;

기본키 삭제

• 외래키 제약조건이 설정된 경우 기본키 테이블의 기본키 삭제 시 오류 발생
  • 밑의 시긍ㄹ 실행하면 외래키 제약조건에 위배되기 때문에 실행되지 않음

ALTER TABLE department DROP PRIMARY KEY;

• 외래키 제약조건 먼저 삭제하기
  • 학생과 교수 테이블에서 외래키로 사용하고 있기 때문에 둘 다 끊어줘야함

ALTER TABLE student DROP CONSTRAINT FK_student_department;
ALTER TABLE professor DROP CONSTRAINT FK_professor_department;

• 기본키 삭제하기

ALTER TABLE department DROP PRIMARY KEY;

기본키 / 외래키 추가

• 기본키 제약조건 추가: department 테이블

ALTER TABLE department ADD CONSTRAINT PK_department_dptNo PRIMARY KEY(dptNo);

-- 또는
ALTER TABLE department ADD PRIMARY KEY(dNo);

• 외래키 제약조건 추가: student, professor테이블

ALTER TABLE student 
    ADD CONSTRAINT FK_student_department 
    FOREIGN KEY(dNo) REFERENCES department(dNo);

ALTER TABLE professor 
    ADD CONSTRAINT FK_professor_department 
    FOREIGN KEY(dNo) REFERENCES department(dNo);

ON DELETE CASCADE

• 기준 테이블의 데이터가 삭제되었을 때, 외래키로 사용하는 테이블의 데이터도 자동으로 삭제되도록 설정
  • department 테이블의 dNo 값 2가 삭제되었을 때
  • student 테이블에서 dNo 값을 2로 갖고 있는 레코드도 자동 삭제
• student 테이블의 기존 외래키 삭제하고 다시 설정

ALTER TABLE student DROP CONSTRAINT FK_student_department;

• ON DELETE CASCADE로 다시 외래키 설정

ALTER TABLE student ADD CONSTRAINT FK_student_department FOREIGN KEY(dNo) REFERENCES department(dNo) ON DELETE CASCADE;

테이블 삭제(DROP TABLE)

• 스키마, 테이블, 뷰, 인덱스 등 삭제하는 명령문
• 테이블 구조와 데이터 모두 삭제 (데이터만 삭제 시 DELETE문 사용 - DML)

DROP TABLE 테이블명;

테이블 삭제하기

• book, publisher 테이블 삭제

DROP TABLE book;
DROP TABLE publisher;

모든 제약조건 출력

select * from information_schema.table_constraints;

• 특정 데이터베이스의 특정 테이블 제약조건 출력

select * from information_schema.table_constraints where table_schema="sqldb1" AND table_name = "student";

• 특정 테이블의 제약조건 출력

select * from information_schema.table_constraints where table_name = "student";