SQL - DDL

DDL ( Data Definition Language )
。DB Schema를 구축하는 SQL
▶ 테이블, 인덱스를 정의, 수정, 삭제 시 사용하는 SQL

。자동으로 Auto Commit 되는 특성이 존재

。CREATE , DROP , ALTER , TRUNCATE

CREATE :
。데이터베이스 생성 / 테이블/View 생성 / DB사용자 생성 / Index 생성하는 명령어

• 데이터베이스 생성

create database 'kf16'@'localhost';

DB 네이밍 컨벤션 : 계정명@도메인
▶ 원격지 접속과 로컬 접속 구분용도
ex ) root@localhost

• 테이블 생성

create table if exists employees(
	empno int primary key comment '테이블 DDL 확인 시 참고할 주석',
    empname char(10) unique,
    title char(10) default '사원',
    salary int check(salary < 60000),
    supervisor int,
    dno int references department(deptno)
      on delete restrict
      on update cascade
) comment '테이블에 대한 주석';

。테이블 네이밍 컨벤션 : 복수형

。 외래키 등의 제약조건은 따로 ALTER로 추가하는게 좋다.

。comment : 주석의 역할을 수행
▶ 이후 SHOW CREATE TABLE 테이블명 시 출력되는 SQL 쿼리 내에서 확인 가능

• 인덱스 생성

  CREATE UNIQUE INDEX Stud_idx
  ON 테이블명(속성명 ASC)

▶ 속성값을 오름차순 정렬하여 stud_idx의 인덱스 생성

• DB 계정 생성
  CREATE USER '유저이름'@'도메인' IDENTIFIED BY '비밀번호'
  。DB의 사용자 계정을 생성
  
  。 root 계정은 오직 DBA만 사용하며, DBA 조차도 root 계정을 사용하지 않고, admin 계정을 별도로 생성하여 활용
  
  。도메인( = IP주소 )를 설정하여 해당 사용자 계정으로 접속할 수 있는 IP대역을 제한할 수 있다.
  ▶ CREATE USER '유저이름'@'%'를 설정 시 해당 계정으로 로그인 시 모든 IP대역으로 접근 가능

 CREATE USER '유저명'@'도메인'
 IDENTIFIED BY '비밀번호'

。생성된 USER 계정은 System DB : mysql 내 User Table 내 저장됨

   SELECT
      USER,
      HOST
      FROM
        USER
      WHERE
        USER = '유저아이디';

▶ 데이터베이스:mysql의 테이블:user에서 생성된 유저 계정이 해당 DB에 저장된 것을 확인 가능

。이후 Mysql에 접속 시 docker exec -it 도커컨테이너명 mysql -h 127.0.0.1 -u kf16 -p password1 해당 계정으로 접속이 가능

• VIEW 생성
  。실제 데이터를 저장하지 않는 가상의 테이블로서 쿼리 결과를 테이블처럼 저장하는 기능
  ▶ 조인 등이 활용된 복잡한 쿼리를 단순화하고 유지보수를 높이는 용도로서 사용
  
  。데이터는 원본 테이블에 존재하여 항상 원본데이터의 최신 데이터를 반영
  
  。원본테이블에서 공개할 컬럼만 따로 추출함으로써 보안 용도로서 활용 가능
  ▶ 원본테이블의 불필요한 컬럼을 숨길 수 있음

  • View의 제한사항
    。특정 조건의 View는 수정할 수 없고, 성능 이슈가 발생할 수 있음.
    
    。인덱스 직접 생성이 불가능.

  CREATE VIEW 뷰명 AS
      SELECT *
      FROM 테이블명;
      ##
  SELECT *
      FROM 뷰명;

  • VIEW의 수정 및 삭제

      -- 수정
  CREATE OR REPLACE VIEW 뷰_이름 AS
      SELECT ...
      -- 삭제
  DROP VIEW 뷰_이름

DROP
。테이블 삭제/ Index 삭제 / 사용자 계정 삭제 하는 명령어

drop table if exists department;

• 사용자 계정 삭제 : DROP USER '계정명'@'도메인'

ALTER
。테이블 구조를 변경하는 명령어
▶ 테이블의 column을 추가하거나 제약조건을 추가 설정가능

ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 제약조건

• 기본키 제약조건 추가

alter table department add constraint pk_deptno
    primary key(deptno);

• 외래키 제약조건 추가

alter table employee add constraint fk_dno
  foreign key(dno) references department (deptno)
  	on delete restrict
  	on update cascade;

• 테이블 제약조건 삭제

alter table employee drop constraint fk_dno;

• 인덱스 수정

alter unique index stud_idx on 테이블명(속성명 DESC)

• ADD COLUMN 컬럼명 데이터타입 옵션 : 컬럼 추가

ALTER TABLE 테이블명
    ADD COLUMN 컬럼명 데이터타입 옵션;
--
ALTER TABLE emp
    ADD COLUMN dept_id BIGINT UNSIGNED NOT NULL;

• MODIFY COLUMN 컬럼명 변경할데이터타입 옵션 : 컬럼의 명세 변경
  
  
• CHANGE COLUMN 이전컬럼명 변경할컬럼명 변경할데이터타입 옵션 : 컬럼의 이름과 명세 변경
  
  
• DROP COLUMN 컬럼명 : 컬럼 삭제
  
  
• ALTER TABLE 테이블명 REMATE TO 새로운테이블명 : 테이블명 수정
  ▶ 데이터베이스는 명칭 변경이 불가능

TRUNCATE
。모든 레코드를 삭제
▶ TRUNCATE는 DML처럼 데이터를 조작하지만 분류상 DDL로 분류됨

。DML인 DELETE와 달리 Auto Commit과 처리속도가 빠른 특징이 존재