[MySQL] #18 Data Type / DDL / DML / DCL

 /*
Data Type (MySQL) 책 237P
    1. 숫자 데이터
TINYINT
        SMALLINT
        INT
        FLOAT
        DECIMAL(M, D): M은 전체 길이, D는 소수점 이하 자리수 / 실수형에서 많이 사용
    2. 문자 데이터
CHAR(N): 고정길이 문자형 / 원하는 길이만큼의 입력을 유도할 때 많이 사용
        VARCHAR(N): 가변길이 문자형 / 
MySQL은 기본적으로 CHAR, VARCHAR가 모두 UTF-8 형태이므로 영문, 한글 등에 따라 내부적으로 크기가 달라지지만
                사용자 입장에서는 한영 구분없이 입력할 수 있다고 생각하면 됨
        LONGBLOB
    3. 날짜 데이터
DATE
        DATETIME
        YEAR
 */ 
/*
SQL
DDL(Data Definition Language): 데이터 정의
CREATE
            ALTER
            DROP
            TURNCATE
        DML(Data Manipulation Language): 데이터 조작
INSERT
            DELETE
            UPDATE
            SELECT
DCL(Data Control Language): 데이터 제어
GRANT
            REVOKE
            COMMIT
            ROLLBACK
 */  

-- 테이블 생성 / 데이터 입력 / 데이터 수정 / 컬럼변경 / 데이터 삭제
-- CREATE   / INSERT  / UPDATE   / ALTER / DELETE
-- 테이블 생성

CREATE TABLE custom(
num SMALLINT AUTO_INCREMENT,
    name VARCHAR(10) NOT NULL,
    addr VARCHAR(50) NOT NULL,
    birthday date,
    PRIMARY KEY(num));

-- 데이터 입력

INSERT INTO custom(name, addr, birthday) VALUES ('김재아', '서울', '1999-12-11');
INSERT INTO custom(name, addr, birthday) VALUES ('최조훈', '서울', '1997-12-23');
INSERT INTO custom(name, addr, birthday) VALUES ('이풍원', '서울', '1995-06-23');

-- 데이터 수정

UPDATE custom SET addr = '서울특별시' WHERE num = 1;
UPDATE custom SET addr = '수원시' WHERE num = 2;
UPDATE custom SET addr = '광주광역시', birthday = '1995-06-05' WHERE num = 3;

# PK는 수정의 대상이 아니다. -> 레코드의 존재 자체가 사라짐 delete and insert와 동일 / 수정하지 말것

-- 컬럼변경 (addr VARCHAR(50) -> address VARCHAR(30))

ALTER TABLE custom CHANGE addr address VARCHAR(30);

/*
삭제
    DELETE
    조건 부여해서 삭제 가능 (WHERE절 사용 안하면 모든 데이터 삭제)
    ROLLBACK 가능
    1) DELETE FROM custom; # 전부 다 지워짐
    2) DELETE FROM custom WHERE num = 3; # 조건에 해당하는 것만 지워짐
    
    TRUNCATE
    모든 데이터를 삭제 / 이 때 테이블의 구조는 남긴다(DROP + CREATE)
    많은 양의 데이터를 지우는데 구조는 남겨야 될 때 사용 / ex. 학교 전산실에서 대량의 학생 데이터 삭제
    ROLLBACK 불가능
    
    DROP
    TABLE 객체를 없앰
*/
-- 데이터 삭제

DELETE FROM custom WHERE num = 3;

TRUNCATE TABLE custom;

DROP TABLE custom;

-- 테이블 생성

CREATE TABLE custom(
num TINYINT AUTO_INCREMENT PRIMARY KEY, # pk는 not null, unique / fk는 null 허용
    name VARCHAR(10) not null,
    age SMALLINT,
    height DECIMAL(4,1),
    birthdate DATETIME);

    

INSERT INTO custom(name, age, height, birthdate) VALUES ('김재아', 25, 165.7, '1999-12-11');
INSERT INTO custom(name, birthdate) VALUES ('최조훈', '1997-12-23');

-- CTAS(CREATE TABLE (AS) SELECT) 사용해서 대용량의 샘플 데이터 로드 
-- 테이블 생성 + 데이터 입력 :: sub query

-- 1)
CREATE TABLE emp1
AS
(SELECT * FROM emp);
    
-- 2)
CREATE TABLE emp2
SELECT * FROM emp;   
    
-- 3)
CREATE TABLE emp3
SELECT empno, ename, sal, deptno FROM emp;    

-- 4)
CREATE TABLE emp4
SELECT empno, ename, sal, deptno FROM emp WHERE deptno = 10;

-- CTAS를 사용해서 employee, department를 생성, emp, dept 테이블의 데이터를 그대로 로드   

CREATE TABLE employee
SELECT * FROM EMP;
    
CREATE TABLE department
SELECT * FROM dept;

-- pk 제약조건 추가하기

ALTER TABLE employee ADD CONSTRAINT employee_empno_pk PRIMARY KEY(empno);
ALTER TABLE department ADD CONSTRAINT department_deptno_pk PRIMARY KEY(deptno);

-- fk 제약조건 추가하기

ALTER TABLE employee ADD CONSTRAINT employee_deptno_fk FOREIGN KEY(deptno) REFERENCES department(deptno);

-- employee 테이블에서 사원 한 명을 삭제 :: empno = 7788

DELETE FROM employee WHERE empno = 7788;
SELECT * FROM employee;

-- department 테이블에서 10번 부서를 삭제 (안됨)

DELETE FROM department WHERE deptno = 10;

# 자식을 가진 부모는 못 죽는다 -> fk를 가지고 있으면 삭제 불가능
# 따라서 자식을 먼저 죽이고 부모를 죽여라 OR 자식의 fk 값을 null로 변경
# => fk 제약 조건 시 옵션 주기 (on delete castcade / set null)

 

ALTER TABLE employee ADD CONSTRAINT employee_deptno_fk FOREIGN KEY(deptno) 
	REFERENCES department(deptno) ON DELETE CASCADE;

 

/*
TRANSACTION
    COMMIT
    ROLLBACK
    SAVEPOINT
    SET AUTO COMMIT
    
    성질
    Atomicity(원자성) 
Consistency(일관성)
Isolation(독립성, 격리성)
Durability(영속성, 지속성)
*/