SQL Data Type
CHAR(n)
- 항상 n개 문자를 저장
- data
- 최대 255 문자
- IO가 빠르지만 디스크 용량 손해
VARCHAR(n)
- 최대 n개 문자까지 저장
- 1 or 2 byte prefix + data,
- prefix = length prefix, n 값을 기록함
- 앞에 prefix가 있는 이유는 문자열 길이를 통해 모든 문자열을 가져오기 위해서
- 최대 65,535 문자 기록 가능
- 디스크 용량 손해지만 IO가 느림
- 예전에는 하드디스크 비용이 비싸서 문제였지만 요새는 하드디스크 비용이 싸져서 큰문제는 없음
TEXT 문자열
- VARCHAR(n)과 동일, n 값은 시스템이 계산함
- TINYTEXT : 1-byte prefix + data 최대 255 문자(2^8 = 256)
- 255
- TEXT : 2-byte prefix + data 최대 65,535 문자
- 65,535
- MEDIUMTEXT : 3-byte prefix + data
- 16M
- LONGTEXT : 4-byte prefix + data
- 4G
CHAR vs VARCHAR
- CHAR가 50 길이보다 작으면 스페이스로 채워서 50을 맞춘다.
- VARCHAR가 CHAR에 비해 작은 공간을 사용함
- 반면에 VARCHAR는 CHAR에 비해 접근 속도가 약간 느려질 수 있음
- 문자열의 비교 방법
- CHAR : 짧은 쪽의 끝에 공백을 채워서 겉은 길이를 만든 후에 비교('SQL' = 'SQL ' 같은 문자열로 처리)
- 마치 비교할 대상 문자열을 CHAR로 캐스팅한 후 비교 하는것 같다
- VARCHAR : 맨 처음부터 한 문자씩 비교('SQL' != 'SQL '다른 문자열로 처리)
- CHAR : 짧은 쪽의 끝에 공백을 채워서 겉은 길이를 만든 후에 비교('SQL' = 'SQL ' 같은 문자열로 처리)
ENUM, SET 같은 값도 제공하지만 PK, FK로는 사용 불가능
BINARY(n)
- 고정길이 바이너리 데이터
- CHAR와 같지만 바이너리 데이터 저장
VARBINARY(n)
- 가변길이 바이너리 데이터
- VARCHAR와 같지만 바이너리 데이터 저장
BLOB(Binary Large Object) 바이너리 데이터
- VARBINARYH(n)과 동일, n 값은 시스템이 계산
- ~TEXT와 동일하지만 바이너리 데이터 저장
- TINYBLOB ~ TINYTEXT
- BLOB ~ TEXT
- MEDIUMBLOB ~ MEDIUMTEXT
- LONGBLOB ~ LONGTEXT
DATETYPE
- DATETIME
- 2019-08-04 12:15:24
- TIMESTAMP
- 2019-08-04 12:15:24
- 디스플레이 형식은 DATETIME과 동일
- 실제로는 unix epoch 1970-01-0:00:00:01.0000 UTC에서 부터의 초의 개수, 즉 카운터로 저장됨
- Unix epoch 이후면 양수, 이전이면 음수
- DATETIME에 비해 저장 공간을 줄일 수 있음(5 -> 4 바이트)
- 저장 공간 조금 줄이는것은 사실 큰 의미가 없음
- 날짜를 다루는 데이터가 많으면 충분히 고려할 사항이 됨
- 초로된 데이터를 날짜로, 날짜로된 데이터를 초로 변환하는 추가적인 작업 필요(그래서 잘 사용하지 않음)
DDL
DROP TABLE
- 생성한 스키마를 제거
- DROP을 한다고 무조건 테이블을 제거할 수 있는것은 아니다
- EX)
- 자식 테이블에 데이터가 있던 없더 자식 테이블이 있으면 부모 테이블 삭제 불가
CREATE TABLE
- 테이블의 구조와 컬럼의 제약조건을 명세
- 제약조건
- NOT NULL
- UNIQUE[KEY]
- PRIMARY KEY
- FOREGIN KEY : 부모 테이블의 PK 값, NULL값 가질 수 있음
- CHECK : 입력 혹은 수정 가능한 값의 범위를 제한하는 논리식 서술
EX) 컬럼 레벨 제약조건
- FOREGIN KEY, REFERENCE : customers의 PK customerId는 orders의 customerId로 오고 FK이다
EX) 테이블 레벨 제약조건
- CONSTRAINT 키워드를 사용하여 제약조건 걸었다
- NOT NULL 제약조건 빼고는 모두 CONSTRAINT를 사용.
- 제약조건을 걸때 pk_order처럼 접두어를 사용하여 제약조건 이름을 지정함
- 나중에 제약조건 이름을 지정하여 제거 가능
DESCRIBE
- 생성된 테이블의 구조 확인
SELECT 문을 이용한 테이블 생성
EX)
CREATE TABLE orders_temp AS
SELECT *
FROM orders;
DESCRIBE orders_temp;- 이렇게 생성하면 NOT NULL만 새로 생성되는 테이블에 적용됨. PK, FK, UNIQUE, CHECK등의 다른 제약 조건은 사라짐
참조 무결성 옵션
- RESTRICT : 삭제할 테이블의 Row를 참조하는 Row가 자식 테이블에 하나라도 존재하면, DBMS가 이 명령의 실행을 거부
- CASCADE : 삭제할 테이블의 Row를 참조하는 Row가 자식 테이블에 하나라도 존재하면, DBMS가 자식 테이블의 참조하는 Row들도 함께 제거함
ALTER TABLE
- 컬럼의 삭제/추가/수정
- EX) 컬럼 이름 변경, 제약 조건 추가
- 제약조건의 삭제/추가
- 수정은 제공되지 않음. 수정하려는 제약조건을 삭제한 후 다시 수정된 제약조건을 추가하는 간접적인 방법으로 수정해야함
- MODIFY COLUMN 절
- 기존 데이터가 있는 경우를 고려해야 하므로, 적용이 제한적
- 컬럼의 크기를 늘릴 수는 있지만 줄이지는 못함
- 기존 데이터가 훼손될 수 있기 때문
- 컬럼이 NULL 값만 가지고 있거나 테이블에 아무 행도 없으면, 컬럼의 크기를 줄일 수 있음.
- 컬럼이 NULL 값만을 가지고 있으면, 데이터 타입을 변경할 수 있음
- 컬럼의 DEFAULT 값을 바꾸면, 변경 작업 이후 발생하는 행 삽입에만 영향을 미침
- 컬럼에 NULL 값이 없을 경우에만, NOT NULL 제약조건을 추가할 수 있음
ETC
- DLL의 테이블/로우 삭제
- DDL 명령어는 하드디스크에 저장된 테이블에 직접 적용함
- 트랜잭션 로그에 기록이 남지 않음
- DDL 명령이 실행되는 즉시, 해당 작업이 완료됨.(AUTO COMMIT)
- DROP TABLE : 스키마 + 데이터 삭제
- TRUNCATE TABLE : 데이터 삭제
- DDL 명령의 특징
- 트랜젝션 로그에 기록을 남기지 않으므로, 시스템 부하가 적음
- Rollback을 통한 데이터 복구 불가
- DDL 명령어는 하드디스크에 저장된 테이블에 직접 적용함
- DML의 로우 삭제 명령어
- DML 명령어는 테이블을 메모리에 로딩하여 작업함.
- 실시간으로 하드디스크의 테이블에 적용되지 않음
- 메모리에서 실행된 모든 명령은 트랜젝션 로그에 기록함
- COMMIT 명령일 실행하여, 로그에 기록된 명령을 하드디스크의 테이블에 실제로 반영함
- DELETE FROM
- 데이터 삭제
- 트랜젝션 로그에 기록을 남김으로, 시스템에 부하를 줌
- Rollback을 통한 데이터 복구 가능
- DML 명령어는 테이블을 메모리에 로딩하여 작업함.
mohadang