SQL 문법의 종류

DDL(Data Definition Language) - 데이터 정의 언어

데이터베이스 및 테이블의 구조를 정의하는 역할이다.
명령어를 입력하는 순간 즉시 반영(Auto Commit)된다.

• CREATE : 데이터베이스 및 테이블 생성
• ALTER : 데이터베이스 및 테이블 구조 수정
• DROP : 데이터베이스 및 테이블 삭제
• RENAME : 데이터베이스 및 테이블 이름 변경
• TRUNCATE : 데이터베이스 및 테이블 초기화(데이터 초기화)

DML(Data Manipulation Language) - 데이터 조작 언어

내부 데이터 관리 역할이다. 즉시 반영되지않고, ROLLBACK으로 되돌릴 수 없다.
트랜잭션의 하위 작업으로 설정된다

• SELECT : 데이터 조회
• INSERT : 데이터 추가
• UPDATE : 데이터 수정
• DELETE : 데이터 삭제

DCL(Data Control Language) - 데이터 제어 언어

데이터를 보호하기 위한 보안 역할이다.
데이터베이스에 접근할 권한을 부여하거나 박탈할 수 있다.

• GRANT : 권한 설정
• REVOKE : 권한 삭제

TCL(Transaction Control Language) - 트랜잭션 제어 언어

트랜잭션을 제어하는 역할이다.
@Transactional 어노테이션을 통한 여러 DML의 트랜잭션은 최종적으로 아래 명령어로 변환되어 수행된다.

• COMMIT : 트랜잭션을 정상적으로 처리
• ROLLBACK : 트랜잭션을 다시 되돌림
• SAVEPOINT : COMMIT 이전의 특정시점을 지정할 수 있도록 하여 반영 혹은 ROLLBACK을 가능하게함

---

문법 사용예시

DDL 사용

• CREATE 구문

  CREATE DATABASE 데이터베이스이름;
  CREATE TABLE 테이블이름
  (
  	컬럼이름1 컬럼타입1 제약조건,
      컬럼이름2 컬럼타입2 제약조건,
      ...
  	[CONSTRAINT FK제약조건이름 
  		FOREIGN KEY (컬럼이름) 
  			REFERENCES 참조테이블이름(참조컬럼이름)]
  );

• CREATE TABLE 관련 제약 조건.
  • NOT NULL : 해당 컬럼은 항상 NULL이 아닌 값이 들어가야한다.
  • UNIQUE : 해당 컬럼은 중복된 값이 있으면 안된다.
  • PRIMARY KEY : NOT NULL 과 UNIQUE의 특성을 모두 가진다.
  • FOREIGN KEY : 다른 테이블의 PRIMARY KEY 와 연결 되는 컬럼이다. 테이블 간의 관련성을 표기함으로써, 데이터의 무결성을 보장한다.

• Primary Key(기본 키) 와 Foreign Key(외래키)

  • Primary Key
    • 데이터베이스에서 테이블 내 각 행을 유일하게 식별할 수 있는 컬럼 혹은 컬럼의 집합으로, 각 행이 Primary Key는 모두 다르다는 것을 보장하기 때문에 데이터를 식별할 수 있다. ID와 같은 역할을 한다.
  • Foreign Key
    • 관련 데이터 간의 연결을 설정해놓기 위해, 테이블 간에 공유되는 열이다.
      

• ALTER 구문

ALTER TABLE 테이블이름 ADD 컬럼이름 컬럼타입;			   	 - 컬럼 추가
ALTER TABLE 테이블이름 DROP 컬럼이름;					 - 컬럼 삭제
ALTER TABLE 테이블이름 MODIFY COLUMN 컬럼이름 컬럼타입;	 - 컬럼 데이터타입 변경
ALTER TABLE 테이블이름 RENAME TO 변경할 테이블이름			 - 컬럼 테이블 이름 변경

• DROP 구문

DROP DATABASE 데이터베이스이름;			- 데이터베이스 삭제
DROP TABLE 테이블이름;					- 테이블 삭제

• 테이블 간의 관계

  DDL로 만드는 테이블들 사이에는 관계라는 개념이 존재한다. 관계는 총 3가지로 표현할 수 있고, 1:1 관계, 1:N 관계, N:M 관계 가 있다. 이 관계에 따라, FK가 어떻게 위치해야 하는지 알 수 있다.

  • 1:1 관계

    • 하나의 레코드가 다른 테이블 레코드 한 개와 연결된 형태이다.
    • User는 Profile을 하나만 가질 수 있으므로, Profile 테이블이 있다고 가정하면 아래 처럼 표현된다.
    • FK는 두 테이블 중 어디에 구성해도 상관은 없지만, 보통 레코드가 존재하지 않을 수도 있는 Optional한 테이블쪽에 구성한다.

    

  • 1:N 관계

    • 하나의 레코드가 여러 개의 레코드와 연결될 수 있는 형태이다.
    • 우리가 앞서 예시로 들었던 Post와 Comment가 대표적이다, 부모 - 자식 관계라고 하기도 한다.
    • FK는 N에 해당하는 자식 테이블에 설정된다.
      

    

  • N:M 관계

    • 여러개의 레코드가 다른 테이블의 여러 레코드와 관계를 맺는 형태이다.
    • 여러개의 Group과 여러 User가 있다고 가정할 때, User는 여러 Group에 참여할 수 있다고 한다면 User와 Group의 관계는 N:M 관계가 된다.
    • N:M 관계를 띄고있는 두 Table이 직접적으로 연결되진 않는다. 각각의 테이블과 1: N 관계를 형성하고 있는 테이블을 통해서 N:M 관계를 나타낼 수 있다.
      

> 위 처럼 각 Entity 내부의 데이터와, 관계에 대해 표현한 다이어그램을 **ERD(Entity Relationship Diagram)** 이라 한다.

---

DML 사용

• INSERT 구문

INSERT INTO 테이블이름(컬럼1, 컬럼2, 컬럼3, ...) VALUES(데이터값1, 데이터값2, 데이터값3, ...);
INSERT INTO 테이블이름 VALUES(데이터값1, 데이터값2, 데이터값3, ...);

INSERT INTO comment (id, content, post_id) VALUES (5, '댓글3', 1), (6, '댓글4', 2);

• SELECT 구문

SELECT 컬럼이름1, 컬럼이름2, ... FROM 테이블이름 [WHERE 조건];
SELECT * FROM 테이블 이름 [WHERE 조건];

SELECT title FROM post
SELECT * FROM comment WHERE post_id=1;

• UPDATE 구문

UPDATE 테이블이름 SET 컬럼이름1=데이터값1, 컬럼이름2=데이터값2, ... WHERE 조건;

UPDATE post SET title='새로운 타이틀' WHERE id=1;

• DELETE 구문

DELETE FROM 테이블이름 WHERE 컬럼이름=데이터값;

• 연산자 : WHERE 뒤에 연산자를 활용하여 다양한 조건을 설정할 수 있다.
  • = , != , < , > , <= , >= : 일반적인 프로그램에서 사용하는 비교연산자와 동일한 기능이다.

  		SELECT * FROM comment where id >= 3;

  • AND , OR , NOT : Kotlin에서 사용하는 && , || , ! 과 유사하다.

  		SELECT * FROM 테이블이름 WHERE 조건1 AND 조건2;
  		SELECT * FROM 테이블이름 WHERE NOT 조건;

  • ORDER BY , ASC, DESC : 결과를 컬럼을 기준으로 오름차순이나 내림차순으로 정렬한다.

  		SELECT * FROM comment WHERE id > 1 ORDER BY id DESC;

  • LIMIT : 출력 결과의 수를 제한한다.

  		SELECT * FROM comment LIMIT 2;

  • IS NULL , IS NOT NULL : 값이 NULL이거나 NULL이 아닌 컬럼을 검색한다.

  		SELECT * FROM 테이블이름 WHERE 컬럼 IS NULL;

  • LIKE : 특정 패턴의 문자열을 검색한다. 패턴에는 % 와 가 사용되고, % 는 0개 혹은 여러개의 문자, 는 하나의 문자를 의미한다.

  		SELECT * FROM comment WHERE content LIKE '%댓글%';

  • IN : 컬럼 값이 여러 값중 하나인 조건을 나타낼때 사용한다.

  		SELECT * FROM comment WHERE id IN (1, 2, 3);

  • BETWEEN : 컬럼 값의 범위를 지정하여 조회한다.

  		SELECT * FROM comment WHERE id BETWEEN 2 AND 4;

---

JOIN 사용

JOIN은 두 개 이상의 테이블 간에 데이터를 결합하는데 사용되는 연산이다.
여러 테이블에서 필요한 데이터를 가져와 하나의 결과 집합으로 합칠 수 있다.
특정 조건에 따라 두 테이블의 행(Row)를 결합한다.

• 예시 테이블

id	title
1	제목1
2	제목2
3	제목3
4	제목4

|

id	content	post_id
1	댓글1	1
2	댓글2	1
3	댓글3	2
4	댓글4	2

INNER JOIN

• 두 테이블 간에 조건과 일치하는 행만 반환한다.

SELECT *
FROM post
INNER JOIN comment ON comment.post_id = post.id;

id	title	id	content	post_id
1	제목1	1	댓글1	1
1	제목1	2	댓글2	1
2	제목2	3	댓글3	2
2	제목2	4	댓글4	2

LEFT JOIN(LEFT OUTER JOIN)

• 왼쪽 테이블의 모든 행과 일치하는 오른쪽 테이블의 행을 반환한다.
• 오른쪽 테이블에 일치하는 행이 없으면 NULL 값으로 채운다.

SELECT *
FROM post
LEFT JOIN comment ON comment.post_id = post.id;

id	title	id	content	post_id
1	제목1	1	댓글1	1
1	제목1	2	댓글2	1
2	제목2	3	댓글3	2
2	제목2	4	댓글4	2
3	제목3	NULL	NULL	NULL
4	제목4	NULL	NULL	NULL

RIGHT JOIN(RIGHT OUTER JOIN)

• 오른쪽 테이블의 모든 행과 일치하는 왼쪽 테이블의 행을 반환한다.
• 왼쪽 테이블에 일치하는 행이 없으면 NULL 값으로 채운다.
• comment 는 항상, post_id를 FK로써 갖고 있기 때문에, 왼쪽 테이블(post)를 NULL 값으로 채울게 없어 INNER JOIN 과 동일한 결과를 응답한다.

SELECT *
FROM post
RIGHT JOIN comment ON comment.post_id = post.id;

id	title	id	content	post_id
1	제목1	1	댓글1	1
1	제목1	2	댓글2	1
2	제목2	3	댓글3	2
2	제목2	4	댓글4	2