Chapter 03 스키마와 테이블 정의를 위한 명령문

1.  SQL(Structured Query Language)

 

  - SQL표준의 역사

  - 표준

    -> 국가표준: 강제적 사용가능

    -> 국제 표준: 사용 권고 (recommadation)

    -> 국제 표준이 국가 표준으로 채택되어야 강제적 적용이 가능

    -> 제조사별로 SQL문 법의 차이는 적어지고 있으나 데이터 유형과 내장함수는 아직 차이가 많다.

    -> SQL표준의 100%를 지원하는 제품은 없다. (보통, 80~90%)

  

  - DBMS 응용 프로그램 개발 팁

    -> 이식성이 보장된 응용프로그램 작성한다.

    -> 표준 기능과 특정 제품 고유의 기능(사용 자제)을 구별한다.

 

  - SQL 데이터 타입

    1) 숫자형

    2) 문자형: CHAR(n) - 고정 길이 문자형, VARCHAR(n) - 가변길이 문자열(varying character)

    3) 날짜형

 

    1) 숫자형: 정수형(integer)/ 실수형(fixed point고정 소수점)

    - 정수형: TINYINT(1), SMALLINT(2), MEDIUNINT(3), INT(4), BIGINT(8)

    - 실수형: DECIMAL(p, s): p(precision): 전체자리수 , s(scale) : 소수 이하 자리수

    Note) 컬럼의 디폴트 부호 특성은 SIGNED - 부호를 사용한다.

 

    2) 문자형

    - 고정길이 문자열: 항상 n개의 문자르 저장

    - 가변길이 문자열: 최대 n개 문자까지 저장

    - TEXT 문자열: varchar(n)과 동일. n값은 시스템이 저장한다.

    - ENUM(a, b, c, ..): 나열형 - 나열된 값 중 하나

    - SET(a, b, c, ..): symptom SET: 나열한 다중값 - NULL을 포함하여 1개 이상의 값은 갖는 타입

      -> PK, FK에는 ENUM과 SET 타입을 사용 불가 

 

    Ex)  고정길이(CHAR(50))와 가변길이 문자열 차이

    - right space padding(남는 부분 스페이스 처리)

    - 검색: trailing space를 제거(MySQL) 또는 trailling space가 포함된 값 그대로 리턴(대부분의 DBMS)

    - 공통점: 입력데이터가 n보다 클 경우, 넘치는 부분은 truncate된다.

    - 차이점: VARCHAR가 CHAR보다 작은 공간 사용, 속도는 느릴 수 있다.

 

    3) Binary String Data Type (이진 문자형)

    - 고정길이 이진 문자열: BINARY(n)

    - 가변길이 이진 문자열: VARBINARY(n)

    - BLOB(binary large object) 이진 문자열:  VARBINARY(n)과 동일, n값은 시스템이 계산한다 ????

    Ex) 이미지 파일은 사이즈가 크기 때문에 이진 문자형 필요하다.

 

    4) Temporal Data Type (날짜형)

    - YEAR / DATE / TIME / TIME(fsp) / DATETIME / DATETIME(fsp) / TIMESTAMP / TIMESTAMP(fsp)

 

 

  - SQL DDL문

 

    1) CREATE SCHEMA 문: 생성하려는 스키마의 명칭을 의미

    - 형식

CREATE { SCHEMA 또는 DATABASE } 스키마명;

 

    2) DRROP SCHEMA

    - 형식

DROP { SCHEMA 또는 DATABASE } 스키마명;

 

    3) CREATE TABLE문

    -  테이블의 구조와 컬럼의 제약조건을 명세

    - 컬럼 제약조건

      -> NOT NULL

      -> UNIQUE [KEY] 

      -> PRIMARY KEY: UNIQUE 그리고 NOT NULL (개체 무결성)

      -> FOREIGN KEY: PK값 또는 NULL (참조 무결성)

      -> CHECK: 입력 혹은 수정가능한 값의 범위를 제한하는 논리식을 서술

 

    - 일반 형식 (제약 조건의 수정은 불가능)

      (1) 컬럼 레벨 정의 방식 (Column Constraint)

      -> 일반적으로 NOT NULL에만 사용한다.

 

      (2) 테이블 레벨 정의 방식 (Table Constraint)

      -> UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK 사용 가능

 

  - 제약조건 명칭의 namespace

    -> 표준 SQL: 모든 타입의 제약조건이 깉은 namespace에 속한다.

    -> MySQL: 제약조건 별로 namespace가 존재한다.

 

  - CHECK 제약조건

    -> Column Constraint: 하나의 컬럼에만 적용되는 조건

    -> Table Constraint: 여러 컴럼에 적용되는 조건

 

  - DESCRIBE 테이블명; 생성된 테이블의 구조를 확인

 

  - SELECT문을 이용한 테이블 생성

    -> CTAS: CREATE TABLE AS SELECT 문 - (MySQL, Oracle 제공)

      - 기본키, 외래키, UNIQUE, CHECK 등 다른 제약 조건은 모두 사라짐

    -> INTO절 -  orders에 있는 모든 투플이 temp에 모두 카피. (In SQL Server) 

SELECT *
INTO orders_temp
FROM orders;

 

    4) DROP TABLE문

    - 테이블 정의와 테이블 데이터를 모두 삭제한다. 

    - 형식

DROP TABLE 테이블명 [ RESTRICT | CASCADE ];

       -> RESTRICT / CASCADE 옵션은 참조 무결성을 유지하기 위한 장치

 

  - 참조 무결성 옵션

  1) RESTRICT: 삭제한 테이블의 투플을 참조하는 투플이 자식 테이블에 하나라도 존재하면,

     DBMS가 이명령의 실행을 거부 

  2) CASCADE: 삭제할 테이블의 투플을 참조하는 투플이 자식 테이블에 하나라도 존재하면,

     DBMS가 자식테이블의 참조하는 투플들도 함께 제거한다. 

 

 

    5) ALTER TABLE문

    - 컬럼의 삭제/ 추가/ 수정

    - 제약조건의 삭제/ 추가 (수정 X)

 

       (1) ADD COLUMN 절 - FIRST/ AFTER절이 없으면 디폴트는 "AFTER 마지막_컬럼명"

ALTER TABLE employees
ADD aeeress VARCHAR(80);

       (2) DROP COLUMN 절 

       - RESTRICT | CASCADE 기능은 제공하지 않는다. (MySQL)

 

  - MODIFY COLUMN절 (기존 데이터가 있는 경우를 고려헤야 하므로 제한적이다.)

    1) 컬럼 크기 늘릴 수 있으나 줄일 수는 없다.

    2) 컬럼이 NULL값만 가지고 있거나 테이블에 아무 행 없으면 컬럼 크기 줄일 수 있다.

    3) 컬럼이 NULL값만 가지고 있으면 데이터 타입 변경 가능

    4) 컴럼의 DEFAULT 값을 바꾸면 변경 작업 이후 발생하는 행 삽입에만 영향 있다.

    5) 컬럼에 NULL값이 없을 경우에만 NOT NULL 제약조건을 추가 가능

 

  - RENAME COLUMN 절

    1) MySQL, Oracle 등 일부 DBMS에서만 제공

 

  - ADD CONSTRAINT 절

 

    6) RENAME TABLE문

    - 형식

RENAME TABLAE {테이블명 TO 테이블명}+;

 

 

    7) TRUNCATE TABLE문

    - 테이블 정의는 그대로 두고 테이블 데이터를 모두 삭제한다.

TRUNCATE TABLE 테이블명;

      ->  MySQL의 경우, DROP TABLE과 동일한 실행 조건을 가진다. (자식테이블 있으면 실행 거부)

 

 

  - DDL의 테이블/ 투플 삭제 명령어

    -> DROP(정의까지 모두 삭제)

    -> TRUNCATE(테이블 정의는 유지) : 원래는 DML의 기능이지만 편의를 위해 DDL에서도 고안됨.

    -> DDL 명령의 특징: 트랜젝션 로그에 기록을 남기지 않으므로 시스템 부하가 적다.

    -> Rollback을 통한 데이터 복구 불가능

 

   - DML의 투플 삭제 명령어

    -> DELETE 

    -> 테이블 정의는 유지. 데이터만 삭제

    -> 트랜젝션 로그에 기록을 남기므로 시스템에 부하를 준다.

    -> Rollback을 통한 데이터 복구 가능

 

 

본 게시글은 프로그래머스의 "학교 밖에서 듣는 전공 필수SQL/DB Essentials" 강의 내용을 토대로 업로드 했습니다.

(코드리뷰 개선 과정)

 

[스터디/2기] 학교 밖에서 듣는 전공필수 SQL/DB Essentials

https://programmers.co.kr/learn/courses/13484