[RDBMS] SQL(DDL, DML) 간단 정리

• Data Definition Language (DDL) : 테이블 구조 정의
• Data Manipulation Language (DML) : 데이터 조회/삽입/삭제/변경

DDL

Data Types

• INT : 모든 숫자
• FLOAT(M, D) : 소수(approximate) (M:소수를 포함한 실수 자릿수, D: 소수점 자릿수)
• DECIAML(M, D) : 소수(precise)
• CHAR(N) : 고정길이 문자열
• VARCHAR(N) : 가변길이 문자열
• ENUM(‘M’, ‘F’) : 정의된 리스트의 값
• BOOLEAN : 참 거짓
• DATE : 날짜 (YYYY-MM-DD)
• DATETIME : 날짜와 시간 (YYYY-MM-DD HH-MM-SS)
• TIME : 시간 (HHH-MM-SS)
• YEAR : 연도 (YYYY)

Primary key

• 테이블 내에서 Uniquely identify 할 수 있는 컬럼 또는 컬럼의 집합
• 반드시 unique해야함
• NULL 일 수 없음
• 테이블에는 하나의 pk만 허용

Foreign key

• 두 테이블을 연결하기 위해 사용
• 다른 테이블의 pk를 컬럼으로 가짐
• pk를 가진 테이블을 reference, 또는 parent 테이블이라하며, fk 테이블은 child table
• 테이블은 여러 fk를 가질 수 있음

sql example (coffee_store)

CREATE DATABASE coffee_store;
USE coffee_store;
-- 테이블 생성
CREATE TABLE products (

	id INT AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(30),
	price DECIMAL(3,2),
	coffee_origin VARCHAR(30)
);

CREATE TABLE customers (

	id INT AUTO_INCREMENT PRIMARY KEY,
	first_name VARCHAR(30),
	last_name VARCHAR(30),
	gender ENUM('M','F'),
	phone_number VARCHAR(11)
);

CREATE TABLE orders (

	id INT AUTO_INCREMENT PRIMARY KEY,
	product_id INT,
	customer_id INT,
	order_time DATETIME,
	FOREIGN KEY (product_id) REFERENCES products(id),
	FOREIGN KEY (customer_id) REFERENCES customers(id)
);

-- 테이블 컬럼 수정
ALTER TABLE products ADD COLUMN coffee_origin VARCHAR(30);
ALTER TABLE products DROP COLUMN coffee_origin VARCHAR(30);

-- 테이블 삭제
DROP TABLE coffee_store;
-- 테이블 비우기
TRUNCATE TABLE coffee_store;

-- PK 추가 및 삭제
ALTER TABLE <tablename> ADD PRIMARY KEY (columnname);
ALTER TABLE <tablename> DROP PRIMARY KEY;

-- FK 추가 및 삭제
ALTER TABLE <tablename> ADD CONSTRAINT <constraintname> FOREIGN KEY (<columnname>) REFERENCES <tablename>(<columnname>);
ALTER TABLE <tablename> DROP FOREIGN KEY <constraintname>;

-- Unique 조건 추가 및 삭제
ALTER TABLE <tablename> ADD CONSTRAINT <constraintname> UNIQUE (<columnname>);
ALTER TABLE <tablename> DROP INDEX <constraintname>;

-- 컬럼 이름 변경
ALTER TABLE <tablename> CHANGE `old_column_name` `new_column_name` <data type>;

-- 컬럼 데이터 타입 변경
ALTER TABLE <tablename> MODIFY <columnname> <data type>;

DML

sql example (coffee_store)

-- 데이터 삽입. VALUES 는 ','로 여러 record insert 가능
INSERT INTO <table_name> (<col1>, <col2>, <col3>)
VALUES ('val1', 'val2', 'val3');

-- 데이터 업데이트
UPDATE <table_name>
SET <column_name> = 'value'
WHERE <column_name> = 'value';

-- 데이터 안전 업데이트 해제 (안전모드는 mysql workbench 디폴트 기능인 것 같다)
-- 안전모드 : key를 기준으로 데이터를 업데이트
SET SQL_SAFE_UPDATES = 0;

-- 데이터 삭제 (where 없으면 모든 rows 삭제)
DELETE FROM <table_name>
WHERE <column_name> = 'value';

-- DELETE vs TRUNCATE
-- DELETE : 해당 테이블의 record를 모두 삭제
-- TRUNCATE : 테이블을 DROP 후, 새 테이블을 생성
-- TRUNCATE 응답이 훨씬 빠르지만 roll-back 되지 않기 때문에 덜 안전하다

-- 데이터 읽기
SELECT <column_name> FROM <table_name>; 

-- where 문은 AND, IN () 등으로 여러 조건을 필터링할 수 있음
WHERE <column_name> = 'value' AND <another_col> = 'value';
WHERE <column_name> IN ('val1', 'val2'); -- 'NOT IN' 으로 아닌 것 여러 개 필터링
-- NULL check 는 'IS NULL' 'IS NOT NULL'
WHERE <column_name> IS NULL;
WHERE <column_name> IS NOT NULL;
-- BETWEEN : 구간 정해서 필터링
WHERE <column_name> BETWEEN 'A' AND 'B';
-- LIKE : 문자열 필터링 (% : any number of characters, _ : just one character)
WHERE <column_name> LIKE '_IK%'; -- IK 앞에 한 자리, 뒤에 아무 문자열(NIKE, BIKEEE 등)

-- ORDER BY : 오름차순 ASC(default), 내림차순 DESC
ORDER BY <column_name> DESC;
 
-- DISTINCT : 중복 컬럼 제거 (여러 컬럼을 함께 묶으면, 모든 컬럼이 중복되는 것을 제외하고 출력)
SELECT DISTINCT coffee_origin FROM products; -- 커피 원산지 중복 없이 출력

-- LIMIT : 해당 테이블에서 상위 n 개 제한해서 가져오기
-- (optional)OFFSET m : m번 이동해서 n 개까지 가져오기
SELECT * FROM customers
LIMIT 5 OFFSET 5;

Join

• 한 statement로 여러 테이블의 데이터를 가져올 수 있음
• 두 테이블을 join하려면 관련성이 있어야함
• 다양한 join 존재
• Inner join, Left join, Right join

-- INNER JOIN : 두 테이블이 공통적으로 가지고 있는 값을 가져옴 (그냥 join 써도 됨)
-- ON을 써서 두 테이블이 관련된 값을 조건으로 필터링
SELECT o.id, c.phone_number FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.product_id = 4;

-- 'A' LEFT JOIN 'B' : 교집합과 A의 나머지 가져오기
-- 'A' RIGHT JOIN 'B' : 교집합과 B의 나머지 가져오기

-- Join을 여러번 써서 multi table select
SELECT p.name, p.price, o.order_time FROM products p
JOIN orders o ON p.id = o.product_id
JOIN customers c ON o.customer_id = c.id
WHERE c.gender = ‘F’

---

ref :
What are DDL and DML?