많은 RDBMS에서는 SQL 명령을 크게 데이터 정의 언어(DDL)와 데이터 조작 언어(DML), 데이터 제어 언어(DCL), 트랜잭션 제어 언어(TCL)로 나누고 있음
이 중 자주 언급되는 DDL, DML, TCL에 속한 명령 위주 SQL

데이터 정의 언어(DDL)

CREATE

CREATE 명령은 데이터베이스, 테이블, 혹은 뷰나 인덱스, 그 외 사용자까지 데이터베이스에서 관리될 수 있는 다양한 대상을 정의함

테이블을 만들려면 테이블이 저장될 데이터베이스가 먼저 만들어져야 함

// 데이터베이스 생성
CRATE DATABASE mydb;

SHOW DATABASES; // 데이터베이스의 조회
USE mydb;		// 데이터베이스의 사용

기본적으로 CREATE TABLE문을 통한 생성은 다음과 같은 형식으로 작성
소괄호 안의 한 줄, 한 줄(필드이름 필드타입)이 테이블의 열인 필드라고 볼 수 있음

유의할 점은 '필드_타입' 우측 혹은 CREATE TABLE 문 하단에 다음과 같은 키워드를 명시함으로써
특정 필드가 지켜야 할 제약 조건을 명시할 수 있다는 점

// users 테이블
CREATE TABLE users (
	user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    birthdate DATE,
    registration_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

• 기본 키인 user_id 값은 레코드가 추가될 때마다 1씩 자동 증가
• username은 최대 길이가 50인 가변 길이 문자열, NULL 값을 허용하지 않음
• email은 최대 길이가 100인 고유한 값의 문자열
• birthdate는 날짜 형식의 레코드
• registration_date는 현재 타임스탬프를 기본값으로 갖음

// posts 테이블
CREATE TABLE posts (
	post_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    title VARCHAR(50) NOT NULL,
    content VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

users 테이블의 user_id를 참조하는 외래 키 필드가 포함된 테이블을 만드는 SQL문
여기서 핵심은 가장 하단줄. 이는 필드 user_id가 외래 키로써 users 테이블의 user_id를 참조한다는 의미

users 테이블과 posts 테이블은 다음 그림처럼 표현할 수 있음

PRIMARY KEY, UNIQUE, FOREIGN KEY 제약 조건은 다음과 같이 CREATE TABLE문 하단에 추가될 수도 있고, 선택적으로 제약 조건에 이름을 붙일 수도 있음

// tests 테이블
CREATE TABLE tests (
	post_id INT AUTO_INREMENT,
    user_id INT,
    title VARCHAR(50) NOT NULL,
    content VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (post_id),
    CONSTRAINT FK_user_id FOREIGN KEY (user_id) REFERENCES users(user_id),
	CONSTRAINT UQ_title UNIQUE (title)
);

• post_id를 기본 키로 추가
• users 테이블의 user_id를 참조하는 FK_user_id라는 이름의 외래 키 제약 조건을 추가
• title 필드가 고유한 값을 갖도록 하는 UQ_title이라는 고유 키 제약 조건 추가

테이블 구조를 보여주는 명령어

DESCRIBE mydb;
DESC mydb;

둘은 기능상 아무런 차이가 없는 동일한 동작

데이터베이스에 속한 전체 테이블 조회

SHOW TABLES;
SHOW TABLES FROM mydb;

ALTER

ALTER 명령은 CREATE TABLE 문을 통해 생성된 테이블에 새로운 필드를 추가하거나 기존의 필드를 수정/삭제할 수 있고, 제약 조건 또한 새롭게 추가하거나 수정/삭제할 수 있음

-- 새로운 필드 추가
-- ALTER TABLE 테이블_이름 ADD COLUMN 필드_이름 필드_타입 [제약 조건]
ALTER TABLE posts ADD COLUMN new_field VARCHAR(50) NOT NULL;

-- 기존 필드 수정
-- ALTER TABLE 테이블_이름 CHANGE COLUMN 기존_필드_이름 새_필드_이름 필드_타입 [제약 조건]
ALTER TABLE posts CHANGE COLUMN new_field old_field VARCHAR(30) NOT NULL;

-- 기존 필드 삭제
-- ALTER TABLE 테이블_이름 DROP COLUMN 필드_이름
ALTER TABLE posts DROP COLUMN old_field;

-- 외래 키 제약 조건 추가
-- ALTER TABLE 테이블_이름 [ADD CONSTRAINT 제약_조건_이름]
	- ADD FOREIGN KEY (필드_이름) REFERENCES 참조_테이블_이름(참조_필드)
ALTER TABLE posts ADD FOREIGN KEY (user_id) REFERENCES users(user_id);

-- UNIQUE 제약 조건 추가
-- ALTER TABLE 테이블_이름 [ADD CONSTRAINT 제약_조건_이름] UNIQUE (필드_이름)
ALTER TABLE posts ADD UNIQUE (title);

-- NOT NULL 제약 조건 추가
-- ALTER TABLE 테이블_이름 MODIFY 필드_이름 필드_타입 NOT NULL
ALTER TABLE users MODIFY email VARCHAR(100) NOT NULL;

-- 기본 키 설정(PRIMARY KEY로 사용 중인 필드가 없을 경우)
-- ALTER TABLE 테이블_이름 ADD PRIMARY KEY (필드_이름)
ALTER TABLE posts ADD PRIMARY KEY (post_id);

예제의 ALTER 문은 ALTER TABLE로써 테이블을 대상으로만 적용되었지만, ALTER문은 테이블뿐만 아니라 뷰, 인덱스 등에도 적용 가능

DROP

테이블이나 데이터베이스를 삭제할 수 있는 DROP 명령은 다음과 같이 작성할 수 있음

DROP DATABASES 데이터베이스_이름;
DROP TABLE 테이블_이름;

TRUNCATE

TRUNCATE 명령은 테이블의 구조를 유지한 채로 테이블의 모든 레코드를 삭제함
다시 말해, 테이블의 레코드는 모두 삭제하되, 테이블 자체를 삭제하지는 않음

TRUNCATE TABLE 테이블_이름;

---

데이터 조작 언어(DML)

SQL의 DDL, DML, DCL 중 기술 면접과 실무에서 가장 자주 언급되는 것은 DML

INSERT

INSERT 명령은 테이블에 새로운 레코드(들)를 삽입하기 위해 사용됨
테이블_이름이라는 테이블에 '필드'에 맞는 '값'들을 삽입하는 명령어
삽입할 값이 지정되지 않은 필드의 경우, 기본값이 있다면 기본값으로 채워지고 기본값이 없다고 NULL로 채워짐

INSERT INTO 테이블_이름(필드1, 필드2) VALUES (값1, 값2);

여러 레코드를 한 번에 삽입하고 싶을 경우, 소괄호() 하나가 레코드 하나라고 생각하면 이해하기 쉬움

INSERT INTO 테이블_이름(필드1, 필드2) VALUES
	(값1, 값2, 값3),
	(값1, 값2, 값3),
	(값1, 값2, 값3);
    ...
    ;

위에서 만들었던 users테이블과 posts테이블에 레코드 삽입 예시

INSERT INTO users (username, email, brithdate) VALUES
	('kim', 'kim@example.com', '1999-11-11');

INSERT INTO users (username, email, brithdate) VALUES
	('kim', 'kim@example.com', '1999-11-11'),
	('lee', 'lee@example.com', '1993-12-01'),
	('park', 'park@example.com', '1991-01-31'),
	('choi', 'choi@example.com', '1992-05-21');

user_id와 registration_date 필드는 삽입할 데이터를 지정하지 않아도 기본값으로 각각 1부터 증가하는 정수와 현재 시간이 삽입됨

레코드 삽입이 올바르게 이루어졌는지는 다음과 같은 명령을 사용하면 됨

SELECT * FROM users;

이는 테이블의 레코드를 조회하는 SQL문인 SELECT문. users 테이블의 모든 레코드(*)를 조회

레코드 삽입 시 유의할 점은 무결성 제약 조건을 지켜야 함
INSERT 문으로 삽입되는 모든 레코드는 해당 제약 조건을 지켜야만 올바르게 실행됨
무결성 제약 조건에 위배될 경우 INSERT문의 실행이 거부됨

UPDATE / DELETE

UPDATE와 DELETE는 각각 레코드를 수정하고 삭제하는 SQL 명령

UPDATE 문에서는 기본적으로
SET: 어떤 값을 바꿀지 지정
WHERE: 어떤 행을 바꿀지 필터링
문법이 사용됨

WHERE 조건식은 생략이 가능하지만, 일반적으로 대부분의 UPDATE문에서 사용됨
이는 특정 조건에 부합하는 레코드만 선별하기 위한 일종의 필터
만약 WHERE절이 생략될 경우에는 모든 레코드가 갱신됨

UPDATE users
	SET email = 'kim_new@example.com'
    WHERE username = 'kim';

UPDATE posts
	SET title = 'update Title'
    WHERE post_id > 5;

DELETE 문도 UPDATE문과 유사
마찬가지로 WHERE절을 통해 삭제하고자 하는 레코드를 식별할 수 있음
WHERE절을 명시하지 않을 경우 테이블의 모든 데이터를 삭제하는 명령이 됨

DELETE FROM posts
	WHERE title = 'HI';

📌 외래 키 제약 조건 - ON UPDATE ON DELETE

UPDATE와 DELETE문의 핵심은 외래 키 참조 상황에서의 레코드 수정 및 삭제

한 테이블이 다른 테이블을 외래 키로 참조하는 상황에서 참조되는 레코드가 수정되거나 삭제될 경우, 참조하는 레코드는 다음과 같이 동작할 수 있음

이들은 CREATE TABLE문이나 ALTER TABLE문에서 제약 조건으로써 정의될 수 있음
참조된 레코드가 수정될 경우의 제약 조건은 ON UPDATE 뒤에 명시되고,
참조된 레코드가 삭제될 경우의 제약 조건은 ON DELETE 뒤에 명시 됨

CREATE TABLE posts (
	post_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    title VARCHAR(50) NOT NULL,
    content VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
    ON UPDATE CASCADE
    ON DELETE SET NULL
);

• posts 테이블의 user_id는 users 테이블의 user_id를 외래 키로 참조
• ON UPDATE CASCADE와 ON DELETE SET NULL은 각각 users 테이블의 user_id가 수정되면 이를 참조하는 posts 테이블의 user_id도 함께 수정하겠다는 의미
• users 테이블의 user_id가 삭제되면 이를 참조하는 posts 테이블의 user_id를 NULL로 변경하겠다는 의미

SELECT

가장 자주 사용되는 SQL 명령 중 하나
SELECT는 삽입된 레코드를 조회하는 명령으로 테이블 내 레코드를 다양하게 정렬하거나 조회하는 것도 가능

SELECT문의 기본 구조

SELECT 필드1, 필드2, ...
	FROM 테이블_이름
    WHERE 조건식
    GROUP BY 그룹화할_필드
    HAVING 필터_조건
    ORDER BY 정렬할 필드
    LIMIT 레코드_제한

• SELECT 뒤에는 하나 이상의 필드 이름이 명시될 수 있음
• WHERE절에 명시되는 조건식은 앞서 UPDATE문과 DELETE문에서 사용된 조건식과 같음

예제

CREATE TABLE students (
  id INT AUTO_INCREMENT PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  age INT,
  major VARCHAR(50),
  gpa DECIMAL(3, 2),
  enrollment_date DATE
);

INSERT INTO students (first_name, last_name, age, major, gpa, enrollment_date) VALUES
('Alice', 'Johnson', 20, 'Computer Science', 3.80, '2022-09-01'),
('Bob', 'Smith', 22, 'Mathematics', 3.50, '2020-09-01'),
('Charlie', 'Brown', 21, 'Physics', 3.90, '2021-09-01'),
('David', 'Williams', 23, 'Chemistry', 3.20, '2019-09-01'),
('Eve', 'Davis', 19, 'Biology', 3.60, '2023-09-01');

SELECT * FROM students;

students 테이블에서 모든 필드의 모든 레코드를 조회할 수 있음

WHERE절 이용 조회 레코드 제한

SELECT first_name, last_name, major
	FROM students
    WHERE major = 'Computer Science';

→ major가 Computer Science인 학생들의 이름과 전공을 조회

SELECT first_name, last_name, age
	FROM students
    WHERE age >= 21;

→ age가 21 이상인 학생들 조회

📌 패턴 검색과 연산/집계 함수

SELECT 문은 패턴 검색이나 연산/집계 함수와 함께 사용되는 경우가 많음

패턴 검색: 문자열, 데이터에서 특정 패턴을 찾는 기능
LIKE 연산자와 와일드카드 문자인 % _를 사용해 패턴 검색을 수행할 수 있음
여기서 %는 0개 이상의 임의의 문자와 일치한다는 의미고, _는 정확히 1개인 임의의 문자와 일치한다는 의미

SELECT first_name, last_name, major
	FROM students
    WHERE major = 'Computer Science';

이때 students 테이블의 major에 Science라는 단어가 포함된 모든 학생과 전공명을 찾고자 할 경우에 LIKE 연산자를 사용할 수 있음

SELECT first_name, last_name, major
	FROM students
    WHERE major LIKE '%Science%';

major의 두 번째 문자가 a인 모든 학생을 반환

SELECT first_name, last_name, major
	FROM students
    WHERE major LIKE '_a%';

연산/집계 함수는 이름 그대로 조회된 레코드에 대한 특정 연산을 수행하거나 집계하는 함수로 다음과 같은 함수들이 대표적

students 테이블의 레코드 수와 평균 학점, 최고 학점, 최저 학점 조회 명령

SELECT COUNT(*), AVG(gpa), MAX(gpa), MIN(gpa), FROM students;

GROUP BY

GROUP BY는 이름처럼 특정 필드를 기준으로 필드를 그룹화하기 위해 사용되며, 앞에서 언급한 연산/집계 함수와 함께 사용되는 경우가 많음

students 테이블에서 전공별 학생 수 조회

SELECT major, COUNT(*) AS studens_count
	FROM students
    GROUP BY major;

students 테이블에서 나이별 학생 수 조회

SELECT age, COUNT(*) AS studens_count
	FROM students
    GROUP BY age;

이처럼 특정 필드를 기준으로 레코드를 그룹화하고자 할 때 사용되는 절이 GROUP BY

예제에서 언급된 AS라는 키워드는 AS의 앞부분을 AS의 뒷부분으로 지칭하겠다는 의미
COUNT(*) AS student_count는 COUNT(*)의 결과를 student_count로 부르겠다는 것

HAVING

HAVING절은 GROUP BY절로 그룹화된 결과에 조건을 적용하기 위해 사용됨

WHERE절과 유사하지만, WHERE절에 명시되는 조건식이 '그룹화되기 전 개별 레코드'에 대한 조건식이라면,
HAVING절에 명시되는 조건식은 '그룹화된 레코드'에 대한 조건식이라는 점이 다름

students 테이블에서 평균 GPA가 3.6 이상인 전공을 조회

전공별 평균 GPA를 구해야 하므로 우선 major 필드를 기준으로 그룹화한 gpq 필드의 평균을 구함

SELECT major, AVG(gpa)
	FROM students
    GROUP BY major;

이 결과에서 평균 GPA가 3.6 이상인 레코드를 조회하면 '평균 GPA가 3.6 이상인 전공'이 될 것임
따라서 앞에서 작성한 명령어 다음과 같이 HAVING절을 추가하면 '평균 GPA가 3.6 이상인 전공' 레코드를 조회할 수 있음

SELECT major, AVG(gpa)
	FROM students
    GROUP BY major;
    HAVING AVG(gpa) >= 3.6;

다른 예시(students 테이블에서 평균 나이가 21세 이상인 전공)

SELECT major, AVG(age)
	FROM students
    GROUP BY major
    HAVING AVG(age) >= 21;

ORDER BY

ORDER BY는 특정 필드를 기준으로 데이터를 정렬하는 데 사용됨
오름차순(ASC)으로 정렬되는 것이 기본이지만, DESC 키워드를 사용하여 내림차순(DESC)으로 정렬할 수도 있음

SELECT first_name, last_name, gpa
	FROM students
    ORDER BY gpa DESC;

SELECT first_name, last_name, gpa
	FROM students
    ORDER BY last_name ASC;
    
SELECT first_name, last_name, enrollment_date
	FROM students
    ORDER BY enrollment_date ASC;

LIMIT

LIMIT는 조회할 레코드 수를 제한하기 위해 사용됨

SELECT * FROM students LIMIT 3;

이라는 명령으로 조회하면 상위 3개의 레코드만 조회됨

LIMIT을 통해 조회할 레코드의 시작점을 설정할 수도 있음

SELECT * FROM students LIMIT 2, 2;

LIMIT 2, 2는 두 번째로 떨어진 레코드부터 2개의 레코드를 조회하라는 의미

SELECT문 문법의 순서

SELECT문이 문법의 순서(작성 순서)와 실제 실행 순서에 차이가 있음
SELECT문은 순차적으로 실행되지 않고 다음과 같은 순서로 실행됨

• FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

SELECT문의 실제 실행 순서에 유의하지 않으면 다수의 레코드를 조회할 때 간혹 예기치 못한 성능 저하가 발생하거나 의도하지 않은 결과를 얻게 될 수 있음

---

트랜잭션 제어 언어(TCL)

TCL에는 트랜잭션을 제어하는 데 사용되는 SQL 명령들이 포함되어 있음

대표적인 TCL

앞서 설명한 것처럼 한 트랜잭션에는 여러 쿼리가 포함될 수 있음

COMMIT / ROLLBACK

accounts 테이블 예시

CRAETE TABLE accounts (
	account_id INT PRIMARY KEY,
    account_name VARCHAR(50),
    balance INT
);

INSERT INTO accounts (account_id, account_name, balance) VALUES (1, 'Kim', 1000);
INSERT INTO accounts (account_id, account_name, balance) VALUES (2, 'Lee', 500);

여기서 다음 2개의 UPDATE문이 반드시 함께 실행되어야 하는 경우, 두 UPDATE문을 하나의 트랜잭션으로 구성될 수 있음

UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;

여러 작업을 포함하는 트랜잭션을 나타낼 때는 START TRANSACTION 혹은 BEGIN 명령을 사용
이는 DBMS에게 '이제부터 트랜잭션이 시작됨'을 알리는 명령이라고 볼 수 있음

START TRANSACTION

UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;

트랜잭션의 결과는 둘 중 하나. 변경된 내용을 적용한 뒤 종료하는 COMMIT과 변경된 내용을 적용하지 않고 종료하는 ROLLBACK

COMMIT: 트랜잭션이 성공적으로 완료되어 트랜잭션에서 수행된 모든 변경 사항을 데이터베이스에 영구적으로 반영한다는 의미
ROLLBACK: 트랜잭션에서 수행된 변경 사항을 취소하고, 데이터베이스를 트랜잭션 시작 이전의 상태로 되돌리겠다는 의미

📌 자동 커밋

MySQL의 공식 문서에 따르면 DDL문은 자동으로 커밋됨

또한 MySQL에서는(DDL 이외의 SQL문에서도) 실행하는 매 SQL문이 자동으로 커밋되도록 하는 기능인 자동 커밋(auto commit)이 기본으로 켜져 있음
다만, START TRANSACTION을 실행하거나 BEGIN을 실행하면 자동 커밋이 꺼진 상태로 실행됨
즉, START TRANSACTION 혹은 BEGIN 직후에 명시된 작업들은 COMMIT 이나 ROLLBACK을 만나기 전까지는 커밋되지 않음
자동 커밋 기능은 명시적으로 끌 수 도 있음

SET autocommit=0;
// 반대로 자동 커밋 기능을 켜는 명령은
SET autocommit=1;

SAVEPOINT

SAVEPOINT문은 ROLLBACK으로 되돌아갈 시점을 지정하는 기능을 수행

세이브포인트_이름이라는 되돌아갈 시점을 지정하는 명령

SAVEPOINT 세이브포인트_이름

세이브포인트_이름으로 되돌아가는 명령

ROLLBACK TO SAVEPOINT 세이브포인트_이름

세이브포인트를 생성하고 중간 저장 지점으로 되돌아가는 예제
sp1, sp2, sp3라는 되돌아갈 시점을 생성하고, sp2, sp1로 되돌아가는 과정에 해당

START TRANSACTION;

-- 세이브포인트 생성 1
SAVEPOINT sp1;

UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;

-- 세이브포인트 생성 2
SAVEPOINT sp2;

UPDATE accounts SET account_name = 'new_Kim' - 100 WHERE account_id = 1;
UPDATE accounts SET account_name = 'new_LEE' - 100 WHERE account_id = 2;

-- 세이브포인트 생성 3
SAVEPOINT sp3;

SELECT * FROM accounts;

-- 특정 세이브포인트로 롤백
ROLLBACK TO SAVEPOINT sp2;
ROLLBACK TO SAVEPOINT sp1;

---

DCL

웹 서버를 비롯한 일반적인 서버 프로그램들이 계정 생성/인증, 권한 관리 기능을 제공하는 것처럼 RDBMS에서도 접속 가능한 사용자 계정을 생성(CRATE USER)하거나 삭제(DROP USER)할 수 있고, 사용자마다 사용 가능한 SQL 명령을 제한하는 등의 권한을 관리할 수 있음

대표적인 DCL 명령
GRANT: 사용자에게 권한 부여
REVOKE: 사용자로부터 권한 회수

---

참고: 북스터디 - 이것이 취업을 위한 컴퓨터 과학이다 (Chapter 6-3)