SQL ( DCL / DML / TCL )

SQL ( Structured Query Language )
。RDBMS에서 데이터를 조작하고 관리하기 위한 선언형 언어
▶ 선언형 : 개발자가 무엇을 원하는지만 간단하게 정의하고, 로직 상 처리 방법은 시스템이 알아서 처리
▶ DB의 구조를 설계하고, DB에 저장된 데이터를 효율적으로 조회 및 수정하며, DB의 접근 권한을 제어

。DDL, DML, DCL이 존재
▶ TCL은 Transaction 단위로 수행

。주석문 : --, #

。ANSI SQL에 따라 표준화되어 다양한 RDBMS의 SQL 구문이 통일

• 백틱 :
  。MySQL에서 주로 테이블명, 컬럼명, 데이터베이스명에 대해 보호기능을 제공
  ▶ 예약어와 구분하는 용도로 주로 사용.

CREATE TABLE departments(
	`dept_id` BIGINT PRIMARY KEY,
	`dept_name` varchar(50) not NULL
);

• SQL의 READ 순서

 SELECT    -> 5
    FROM   -> 1
	WHERE  -> 2
	GROUP BY -> 3
	HAVING -> 4
    ORDER BY -> 6
    LIMIT -> 7

쿼리 ( Query )
。SQL을 작성하여 DB와 소통할 수 있는 수단

• 쿼리의 처리 과정
  

1. 파싱 ( Parsing )
   。입력된 쿼리 문자열이 문법적으로 문제가 있는지 검사
   ▶ 파싱을 통과 시 Parsing Tree가 도출
   
   
2. 의미 분석 ( Seman )
   。테이블 및 컬럼 존재여부 확인 + 테이블 접근권한 확인
   
   
3. 최적화 (Optimization)
   。옵티마이저에서 가장 비용이 낮은 방법을 탐색하여 Query Plan을 작성
   
   
4. 실행 (Execution)
   。Query Plan에 따라 데이터를 읽고, 필터링 & 정렬 & 집계를 수행

mySQL 관련 문법

• SHOW DATABASES :
  。데이터베이스 조회
  
  
• SHOW TABLES :
  。특정 데이터베이스 내 테이블 조회
  
  
• USE 데이터베이스명 :
  。해당 DB를 사용
  
  
• DESC 테이블명 :
  。해당 테이블 구조를 출력
  
  
• DESC SQL문 = EXPLAIN SQL문
  해당 SQL문의 쿼리플랜을 도출
  
  
• SHOW CREATE TABLE 테이블명
  。이미 생성된 테이블의 생성 시 사용된 SQL 쿼리문을 출력

DCL ( Data Control Language )
。데이터의 사용 권한을 관리하는데 사용하는 SQL
▶ DBA가 DB에 대한 접근 권한과 보안을 제어하는 용도로 사용

。최소 권한 원칙 : 실무에서 *를 통한 권한 부여 금지

。GRANT, ROLLBACK이 존재

• GRANT
GRANT 권한내용 ON 데이터베이스.테이블명 TO 사용자@도메인
  。사용자에게 특정 명령에 대한 권한 부여
  ex ) GRANT UPDATE ON STUDENT TO 사용자이름;

GRANT ALL PRIVILEGES
    ON *.*
    TO 'kf16'@'%';
FLUSH PRIVILEGES;

。ON *.* : 모든 데이터베이스와 테이블에 대해 허용

。 실무에서는 % , *의 사용을 권고하지 않는다.
▶IP를 특정해야하면서 어떤 데이터베이스인지 직접적으로 명시해야하므로.

• REVOKE 권한내용 ON 데이터베이스.테이블명 FROM 사용자@도메인
  。특정 사용자로 부터 특정 명령에 대한 권한 회수
  ex ) REVOKE SELECT ON DEPARTMENT FROM 사용자;

  • ALL PRIVILEGES
    。GRANT로 부여할 수 있는 모든 권한 ( = UPDATE, DELETE, ...)
    
    
  • FLUSH PRIVILEGES
    。변경사항을 현재 세션에서 적용
    ▶ 반영을 안하는 경우 다음 세션에서 적용됨

DML ( Data Manipulation Language )
。DB Table의 데이터를 검색 / 삽입 / 삭제 / 수정 시 사용하는 SQL
▶ 트랙잭션에 영향을 받고 적용됨.

。SELECT / INSERT / UPDATE / DELETE

• SELECT
  。데이터 조회하는 명령어

select * from table;

• INSERT
  。데이터 삽입하는 명령어

INSERT INTO 테이블명(필드명1, ... ) VALUES(값1,...),
(값1,...),
(값1,...);

• UPDATE
  。데이터 업데이트하는 명령어

update member
	set name = ?,
	birthday = ?
	where loginid = ?;

• DELETE
  。데이터 삭제하는 명령어

DELETE FROM 테이블
  WHERE 제약조건;

DELETE의 삭제 방식
。DELETE는 데이터 복구가 힘드므로 다음 두 방식의 삭제방식이 존재

• HARD DELETE :
  。데이터가 즉시 삭제
  
  
• SOFT DELETE :
  。실제 DB상에는 남아있으나 삭제된것처럼 적용된 경우
  ▶ 데이터의 유예기간을 설정 후 삭제

TCL ( Transaction Control Language )
。DB 트랜잭션을 제어하는 SQL
▶ 여러 DML을 하나의 트랜잭션으로 묶을 때 사용

。일반적으로 DBMS는 SQL문 실행 시 자동으로 COMMIT하는 특징이 존재.
▶ 실무에서는 해당 기능을 off하고 TCL을 사용

。트랜잭션 작성 시 COMMIT과 ROLLBACK은 함께 관행적으로 작성.

BEGIN TRANSACTION;
# 코드 작성
COMMIT;
ROLLBACK;

• BEGIN
  。트랜잭션 시작
  BEGIN TRANSACTION;
  
  
• COMMIT
  。Transaction 단위에 속한 SQL Query에 의한 모든 변경사항을 DB에 반영

    BEGIN TRANSACTION;
    UPDATE 학생 SET 연락처='232-0077' WHERE 성명='김길동';
    COMMIT;

▶ UPDATE 명령에 의한 연산결과를 정상적으로 종료 및 그대로 유지

。PostgreSQL의 경우 Transaction 중 실행된 Query에 의한 모든 변경사항을 영구적으로 적용하도록 시도하므로 COMMIT 속도가 가장 빠르다.
▶ 원래라면 일일이 COMMIT을 입력해서 커밋해야함

。DBMS에서는 SQL 실행 시 트랜잭션으로서 자동으로 Auto Commit이 반영

• ROLLBACK
  。Transaction 단위에 속한 SQL Query에 의한 모든 변경사항을 취소하여 트랜잭션 이전으로 복구하는 명령어

      BEGIN TRANSACTION;
      DELETE FROM 학생 WHERE 성명='최길동';
      COMMIT;
      ROLLBACK;

▶ 삭제명령을 취소하여 삭제된 자료를 복구

• SAVEPOINT 포인트명
  。중간지점 설정
  ▶ ROLLBACK TO SAVEPOINT 포인트명을 통해 복구 가능
  
  。현재는 정합성 문제로 인해 일관성 오류가 발생할 수 있어 사용을 권장하지 않음.