Index
1. 데이터베이스 CRUD / 현재 있는 데이터베이스 확인
6. DDL (CREATE, ALTER, DROP, TRUNCATE)
7. DML (INSERT, SELECT, UPDATE, DELETE)
1. 데이터베이스 CRUD / 현재 있는 데이터베이스 확인
-- 데이터베이스 생성 (Create)
CREATE DATABASE my_shop;
-- 현재 있는 데이터베이스 확인 (Read)
SHOW DATABASES;
-- 현재 있는 테이블 확인 (Read)
SHOW TABLES;
-- 데이터베이스 삭제 (Delete)
DROP DATABASE my_shop;
2. 데이터 타입
| 데이터 타입 | 설명 | ||
| INT | 소숫점이 아닌 정수 | ||
| VARCHAR | 가변 문자열(Variable Character)을 의미 VARCHAR(5)는 최대 5글자 저장 가능 Ex) VARCHAR(5) 에서 '안녕' 을 넣었을 때 2글자 만큼의 용량 할당 |
CHAR | 고정 문자열을 의미 CHAR(5)는 항상 5글자 저장 Ex) CHAR(5) 에서 '안녕'을 넣었다면 5글자 만큼 용량을 할당하고 빈 자리는 공백으로 채워넣음 ※ 6글자를 사용해야 하는 상황이 오면 문제가 발생하므로 잘 사용하지 않음 |
| DATE DATETIME |
YYYY-MM-DD 형태로 저장 YYYY-MM-DD HH:MM:SS 형태로 저장 |
TIMESTAMP | YYYY-MM-DD HH:MM:SS 형태로 저장 ※ 단, 표현할 수 있는 기간이 1970~2038년 까지 이므로 현대에는 사용하지 않음 |
3. 제약 조건
| 제약 조건 | 설명 |
| NOT NULL | NULL이 없어야 함. ※ NULL은 값이 없음을 의미 |
| UNIQUE | 중복 불가 ※ 단, NULL은 중복으로 잡지 않음 |
| PRIMARY KEY(PK) | NOT NULL + UNIQUE |
| AUTO_INCREMENT | 새로운 데이터가 추가될 때마다 n씩 더해준다. 보통 거래정보id 와 같은 컬럼에서 많이 사용됨 |
| FOREIGN KEY(FK) | 테이블 간의 관계를 설정 ※ 참조 무결성을 보장 : 참조하는 것이 무조건 있다는 것을 보장한다. == FK는 반드시 참조하는 데이터가 있어야 한다. == 참조하는 열의 값은 반드시 참조되는 테이블의 PK 값 중 하나여야 한다. - [INSERT] orders 테이블의 customer_id (FK) 값이 99가 추가되는 INSERT 문을 작성했다고 가정하면, customers 테이블의 customer_id (PK) 값에는 99가 없으므로 이런 경우 데이터의 입력을 막는다. 단, FK가 NULL 허용인 경우 부모를 참조하지 않아도 NULL로 생성하는 것은 예외적으로 가능  - [DELETE] 삭제할 때 customer_id(FK)를 먼저 삭제하고, 연결되는 것이 없으면 customer_id(PK)를 삭제할 수 있다. customer_id(PK) 값이 2를 삭제(DELETE) 한다고 가정하면, 그와 연결된 customer_id(FK) 2는 참조하는 것이 없으므로 참조 무결성 제약 조건에 위배된다. 따라서 삭제할 때는 customer_id(FK) 값이 2인 것을 모두 삭제하고, 그와 연결된 customer_id(PK)을 삭제하면 된다. 삭제 할 때 참조되고 있는 데이터도 같이 삭제하려면 CASCADE 키워드를 사용한다. 삭제 할 때 참조되고 있는 데이터는 NULL으로 설정하려면 SET NULL 키워드를 사용한다. |
| CHECK | 컬럼에 입력되는 값이 특정 조건을 만족하는지 검사 Ex) age 컬럼에 10보다 작은 숫자가 입력되지 않도록 막는다. |
| DEFAULT | 값 미지정 시 기본값을 설정 ※ 날짜의 기본값 설정 옵션 CREATE TABLE test ( created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ); DATETIME DEFAULT CURRENT_TIMESTAMP : 새로운 데이터 행(row)가 추가될 때, 해당 컬럼에 별도의 값을 지정하지 않으면 현재의 날짜와 시간이 자동으로 입력된다. DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP : 새로운 데이터 행(row)가 추가될 때 또는 같은 행의 컬럼 값이 변경되어 업데이트 될 때, 이 컬럼의 값은 현재 날짜와 시간으로 자동 갱신된다. |
4. 테이블 구조 확인
// 테이블 구조 확인
DESC sample_tbl;
DESCRIBE sample_tbl;
5. PK와 FK 관계
- PK -> FK 관계
- 즉, PK는 참조를 하는 주체이고 FK는 참조를 당하는 주체
- 참조 무결성을 유지해야 하므로, PK-FK가 연결된 상태에서 PK쪽의 테이블을 멋대로 지우면(DROP, TRUNCATE) 오류가 발생한다.
- PK쪽의 테이블을 지워버리면 오류가 발생하는 이유 : FK는 참조하는 것이 무조건 있다는 것을 보장해야 하는데, 참조하고 있는 데이터인 student_id(PK)를 지워버리면 참조 무결성이 지켜지지 않기 때문이다.
CREATE TABLE orders(
order_id INT PRIMARY KEY AUTO_INCREMENT,
customer_id INT NOT NULL,
...
-- orders.customer_id 가 customers.customer_id 를 참조하도록 한다.
CONSTRAINT fk_orders_customers FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
);
6. DDL (CREATE, ALTER, DROP, TRUNCATE)
- 데이터 구조를 정의 (definition)
- 데이터베이스, 테이블 대상 : CREATE(C), ALTER(U), DROP(D), TRUNCATE(D), RENAME
- 대용량 테이블은 변경 시 잠금, 속도를 고려하여 새벽에 작업
- PK - FK 관계가 걸려 있으면 DROP, TRUNCATE 명령은 차단된다.
※ SHOW은 DDL이 아니다.
-- 데이터베이스 생성 (Create)
CREATE DATABASE my_shop_tbl;
-- 테이블 생성 (Create)
CREATE TABLE sample_tbl (
product_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
price INT,
stock_quantity INT,
release_date DATE,
join_date DATETIME DEFAULT CURRENT_TIMESTAMP
);
-- 테이블에서 칼럼 추가 ALTER TABLE (Update)
-- customers_tbl 테이블에서 point 칼럼 추가
ALTER TABLE customers_tbl
ADD COLUMN point INT NOT NULL DEFAULT 0;
-- customers_tbl 테이블에서 address 칼럼을 수정
ALTER TABLE customers_tbl
MODIFY COLUMN address VARCHAR(500) NOT NULL;
-- customers_tbl 테이블에서 point 칼럼을 제거
ALTER TABLE customers_tbl
DROP COLUMN point;
-- 테이블 삭제 (Delete)
DROP TABLE sample_tbl;
-- 테이블 구조는 남기고 내부 데이터만 삭제 (Delete)
-- ※ TRUNCATE는 AUTO_INCREMENT 값도 초기화됨
-- ※ 반면, DELETE는 AUTO_INCREMENT 값이 초기화되지 않음
TRUNCATE TABLE sample_tbl;
7. DML (INSERT, SELECT, UPDATE, DELETE)
- 데이터를 조작 (Manufactur)
- 테이블 내 데이터를 대상 : INSERT(C), SELECT(R), UPDATE(U), DELETE(D)
-- Create
-- 1개인 경우
INSERT INTO sample (product_id, name, price, stock_quantity, release_date)
VALUES (1, "청바지", 59900, 100, '2025-06-11');
-- 여러개인 경우
INSERT INTO sample (product_id, name, price, stock_quantity, release_date)
VALUES
(1, "청바지", 59900, 100, '2025-06-11'),
(2, "떡볶이", 3000, 110, '2025-06-12'),
(3, "연필", 500, 100, '2025-06-11');
-- Read
SELECT * FROM sample;
※ 실무에서는 UPDATE 작업 시 WHERE 절에 들어갈 대상을 미리 확인하고 UPDATE 하는 습관을 들이자.
-- Update
UPDATE sample
SET price = 40000, stock_quantity = 580
WHERE product_id = 1;
-- 안전 모드
-- ※ 실수로 WHERE 절을 쓰지 않으면 모든 데이터가 바뀌기 때문에 위험하다.
-- 그래서 MySQL Workbench에서는 자동적으로 안전 모드가 설정되어 WHERE 절을 쓰지 않은 경우나
-- WHERE을 썼더라도 PK가 아닌 데이터로 조건을 주면 오류를 발생시킨다.
-- (왜냐하면 의도하지 않게 여러 개가 지워질 수 있기 때문이다.)
-- 그러나, Connection을 통해 접근하는 애플리케이션에서는 안전 모드가 꺼져 있으므로 주의가 필요하다.
※ 실무에서는 DELETE 작업 시 WHERE 절에 들어갈 대상을 미리 확인하고 DELETE 하는 습관을 들이자.
-- Delete
DELETE FROM sample
WHERE product_id = 1;
-- 안전 모드
-- UPDATE문과 마찬가지로 안전 모드를 제공한다.
-- WHERE 절을 쓰지 않은 경우에만 안전 모드 덕분에 오류가 발생한다.
TRUNCATE는 DROP 하고 다시 테이블을 만든 느낌으로 이해하면 된다.
8. SELECT, WHERE
SELECT
- SELECT 구문에서 필요한 칼럼만 선택해서 추출하는 것이 성능상 더 좋다.
-- 전체 칼럼을 조회하면 조회 성능이 비교적 저하됨.
SELECT * FROM THDAY_TBL
-- 조회할 칼럼만 선택하여 조회하면 전체 칼럼 조회(*) 보다 성능이 향상
SELECT
INTEREST,
ACNT_NO
FROM
THDAY_TBL
➀ DISTINCT
- 값의 중복을 제거한다.
- DISTINCT ACNT_NO, INTEREST 이라면 DISTINCT (ACNT_NO, INTEREST) 처럼 동작한다. 즉, ACNT_NO 값도 같고, INTEREST 값도 같은 것을 중복으로 보고 제거한다. (하나만 같은 경우 중복으로 보지 않는다.)
- DISTINCT 연산 시 내부에서 정렬 등 여러가지 작업이 일어나기 때문에 데이터가 많은 경우 단순 SELECT 보다는 성능 저하가 일어날 수 있다. (즉, 데이터가 많은 경우 DISTINCT 연산 사용 시 성능 확인이 필요하다.)
SELECT
DISTINCT INTEREST,
ACNT_NO
FROM
THDAY_TBL
예시 ) DISTINCT INTERST, ACNT_NO
| INTEREST | ACNT_NO |
| 1.0 | 999929123456 |
| 2.0 | 999929123456 |
| 2.0 | 999929123456 |
| 2.0 | 999929111111 |
INTEREST, ACNT_NO가 동일한 칼럼만 중복 제거됨.
| INTEREST | ACNT_NO |
| 1.0 | 999929123456 |
| 2.0 | 999929123456 |
| 2.0 | 999929111111 |
WHERE
- =, !=, <, <=, >, >= 와 같은 연산자가 있으나, BETWEEN, IN, LIKE, IS NULL 과 같은 연산자로 가독성이 좋은 쿼리 작성이 가능하다.
➀ BETWEEN (NOT BETWEEN)
해당 범위도 포함하여 조회한다. (아래 코드에서 이자가 1(1도 포함) ~ 2(2도 포함)인 행을 조회)
SELECT *
FROM THDAY_TBL
WHERE INTEREST BETWEEN 1 AND 2 -- 이자가 1~2인 행만 조회
SELECT *
FROM THDAY_TBL
WHERE INTEREST NOT BETWEEN 1 AND 2 -- 이자가 1~2가 아닌 행만 조회
➁ IN (NOT IN)
특정 데이터만 골라서 조회한다.
SELECT *
FROM THDAY_TBL
WHERE INTEREST IN (1, 3, 5) -- 이자가 1,3,5인 행만 조회
SELECT *
FROM THDAY_TBL
WHERE INTEREST NOT IN (1, 3, 5) -- 이자가 1,3,5가 아닌 행만 조회
➂ LIKE (NOT LIKE)
특정 패턴과 일치하는 것을 조회한다.
% = 들어오는 글자 수 제한 없음 (0개 이상의 임의의 글자가 들어올 수 있으므로 뒤에 글자가 없는 경우도 가능)
_ = 들어오는 글자 수는 _ 하나 당 반드시 1개
/* LIKE */
SELECT *
FROM THDAY_TBL
WHERE ACNT_NO LIKE '999929%' -- 계좌번호가 999929 로 시작하는 모든 행을 조회
SELECT *
FROM THDAY_TBL
WHERE ACNT_NO LIKE '_999929' -- 계좌번호 앞 글자가 하나 존재하고 999929 인 모든 행을 조회
/* NOT LIKE */
SELECT *
FROM THDAY_TBL
WHERE ACNT_NO NOT LIKE '999929%' -- 계좌번호가 999929 로 시작하지 않는 모든 행을 조회
SELECT *
FROM THDAY_TBL
WHERE ACNT_NO NOT LIKE '_999929' -- 계좌번호 앞 글자가 하나 존재하고 999929 이지 않은 모든 행을 조회
Ex) LIKE '%999929'
| 999929 | 일치 |
| 9999291 | 불일치 |
| 12999929 | 일치 |
Ex) LIKE '999929%'
| 999929 | 일치 |
| 9999291 | 일치 |
| 12999929 | 불일치 |
Ex) LIKE '%999929%'
| 999929 | 일치 |
| 9999291 | 일치 |
| 12999929 | 일치 |
➃ WHERE 안에 여러 데이터를 비교해야 하는 유형
SELECT *
FROM products
WHERE (category, price) = ('전자기기', 75000);
-- WHERE category = '전자기기' AND price = 75000; 과 동일
9. ORDER BY, LIMIT
ORDER BY
ASC (default) : 오름차순
DESC : 내림차순
SELECT *
FROM THDAY_TBL
ORDER BY ACNT_NO, INTREST DESC -- ACNT_NO 기준 오름차순, 같다면 INTERST DESC 기준 내림차순
LIMIT
- 데이터가 100만건, 1000만건이라고 생각해보자. SELECT로 한 번에 다 조회하면 시스템에 부하를 주게 된다.
- 이때, 상위 n개나 하위 n개만 끊어서 조회한다는 의미의 명령어를 사용하면 시스템의 부하를 줄일 수 있다.
- 정렬(ORDER BY) 후에 사용해야 LIMIT에 의미가 있다. (물론 ORDER BY 절과 꼭 같이 써야하는건 아니다.)
/* 계좌번호 순으로 오름차순 정렬했을 때 상위 5개만 추출 */
SELECT *
FROM THDAY_TBL
ORDER BY ACNT_NO -- ACNT_NO 기준 오름차순
LIMIT 5
LIMIT - 오프셋(offset)
- n개를 건너 뛰고 그 다음 상위 k개를 출력할 수 있다.
- LIMIT 건너뛸개수(offset), 가져올개수;
- LIMIT 2 와 LIMIT 0,2 는 같은 의미이다.
/* 문법 1 */
SELECT *
FROM THDAY_TBL
ORDER BY ACNT_NO -- ACNT_NO 기준 오름차순
LIMIT 10, 5 -- 처음 상위 10개는 건너뛰고, 11번째 데이터부터 5개를 가져온다.
/* 문법 2 */
SELECT *
FROM THDAY_TBL
ORDER BY ACNT_NO -- ACNT_NO 기준 오름차순
LIMIT 5 OFFSET 10 -- 처음 상위 10개는 건너뛰고, 11번째 데이터부터 5개를 가져온다.
응용 - 페이징 처리
- 한 페이지에 상품을 2개씩을 보여준다고 가정하자. 그럼 아래와 같은 쿼리문으로 조회해야 할 것이다.
- 1페이지 : LIMIT 0, 2
- 2페이지 : LIMIT 2, 2
- 3페이지 : LIMIT 4, 2
- 규칙을 찾아볼까?
- offset = (페이지 번호 - 1 ) * 2
10. NULL
- DB에서 '값이 없음' 을 의미하는 키워드
- MySQL에서는 NULL 값을 가장 작은 값으로 취급한다.
- Oracle은 NULL을 가장 큰 값으로 취급한다.
- NULL은 IS NULL, IS NOT NULL 연산을 제외한 모든 연산에서 항상 False로 나온다. (김영한의 실전 데이터베이스 입문 51. 문제와 풀이 8:35 참고)
IS NULL (IS NOT NULL)
/* IS NULL */
SELECT *
FROM THDAY_TBL
WHERE INTEREST IS NULL -- INTEREST 값이 없는 것만(NULL인 것만) 조회
/* IS NOT NULL */
SELECT *
FROM THDAY_TBL
WHERE INTEREST IS NOT NULL -- INTEREST 값이 있는 것만(NULL이 아닌 것만) 조회
실무) NULL의 위치를 강제로 바꿔야 한다면?
- Q. MySQL에서 INTEREST를 기준으로 내림차순 정렬을 하되, INTEREST가 NULL인 것은 빨리 확인할 수 있도록 맨 앞으로 보내려면?
- 과정1) MySQL에서 NULL 값은 가장 작은 값으로 취급된다. 그래서 INTEREST가 NULL인 것은 맨 뒤에 배치된다. 어떻게 INTEREST가 NULL인 값을 앞으로 보낼 수 있을까?
- 과정2) 과정1을 처리했으면, 이제 INTEREST을 기준으로 내림차순 정렬을 한다.
/* 과정1 해결 */
SELECT INTEREST, INTEREST IS NULL
FROM THDAY_TBL
ORDER BY INTEREST IS NULL DESC -- INTEREST가 NULL이면 1, NULL이 아니면 0이 담기므로
-- 내림차순 정렬을 하여 NULL인 값이 가장 앞으로 나오도록 한다.
/* 과정2 해결 */
SELECT INTEREST, INTEREST IS NULL
FROM THDAY_TBL
ORDER BY INTEREST IS NULL DESC, INTEREST DESC
NULL을 처리하는 함수들
| (MySQL) IFNULL (Oracle) NVL (MSSQL) ISNULL |
IFNULL(value1, value2) NVL (value1, value2) ISNULL (value1, value2) value1 값이 NULL이면 value2 값을 출력 value1 값이 NULL이 아니면 value1 값을 출력 |
SELECT IFNULL(name, '이름 없음') SELECT NVL(name, '이름 없음') SELECT ISNULL(name, '이름 없음') |
| COALESCE | COALESCE(value1, value2, .... ) NULL이 아닐 때까지 찾아서 출력 (끝까지 가면 끝을 출력) |
SELECT COALESCE(name, price, '둘 다 없음') - name이 null이면 -> price가 null인지 보고 price도 null이면 '둘 다 없음' 출력 - name이 null이면 -> price가 null인지 보고 price가 null이 아니면 price 출력 |
11. 문자열 함수
칼럼의 name 값이 '피자', price가 1000, quantity가 2 라고 가정
| CONCAT | 문자열 합치기(더하기) | SELECT CONCAT(name, ' ', price); -- 피자 1000 |
| CONCAT_WS ※ MySQL 전용 함수임 |
구분자를 포함한 문자열 합치기(더하기) | SELECT CONCAT_WS('-', name, price, quantity); -- 피자-1000-2 SELECT CONCAT_WS('-', '010', '1234', '5678'); -- 010-1234-5678 |
| UPPER() | 문자열을 모두 대문자로 | SELECT UPPER('hi') -- HI |
| LOWER() | 문자열을 모두 소문자로 | SELECT LOWER('HI') -- hi |
| LENGTH() | 문자열의 길이를 byte 단위로 반환 | SELECT LENGTH("안녕") -- 6 (한글은 3바이트로 처리) |
| CHAR_LENGTH() | 문자열의 글자 수를 반환 | SELECT CHAR_LENGTH("안녕") -- 2 |
12. 집계 함수와 GROUP BY절
집계 함수
- 집계 함수는 GROUP BY 절과 함께 자주 사용된다.
| SUM(expression) | 합계 |
| AVG(expression) | 평균 |
| COUNT(expression) | NULL이 아닌 행의 수 |
| COUNT(*) | 테이블의 전체 행 수 (NULL 포함) |
| MAX(expression) | 최댓값 |
| MIN(expression) | 최솟값 |
NULL이 포함된 경우 집계 함수는 어떻게 처리될까?
- NULL은 없는 데이터로 취급한다.
아래 테이블명이 testing 인 테이블이 정의되어 있다고 가정하자.
| num1 | num2 |
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| NULL | 5 |
그리고 아래 쿼리를 실행하면 결과는 아래와 같다.
SELECT
AVG(num1),
AVG(num2),
SUM(num1),
SUM(num2)
FROM
testing;
| MIN(num1) | MAX(num1) | SUM(num1) | SUM(num2) | AVG(num1) | AVG(num2) |
| 1 | 4 | 10 | 15 | 2.5000 (1+2+3+4) / 4 |
3.0000 (1+2+3+4+5) / 5 |
GROUP BY
- SELECT 절에는 GROUP BY로 지정한 컬럼이나, 집계 함수만 올 수 있으며 그 외에는 오류가 발생한다.
SELECT
category,
COUNT(*) as `주문 건수`,
price, -- 오류 발생
SUM(price)
FROM order_stat
GROUP BY category;
-- HAVING 주문 건수 >= 5 -- MySQL에서는 가능, Oracle과 MSSQL은 오류
(원칙적으로는 HAVING 다음 SELECT 구문이 실행되므로 불가능한 것이 원칙이나 MySQL에서는 허용해줌)
DBMS 범용적으로 사용해야 하는 경우, COUNT(*)과 같이 집계 함수를 그대로 넣어서 사용하는 것을 권장
- WHERE 절과 HAVING 절의 차이
13. 별칭 지정 (alias)
- ` ` 백틱 방식 (추천)
- SELECT 절에서 사용하는 것은 상관이 없다.
- SELECT 절이 아닌 다른 절에서 alias 된 것을 사용해도 의도한대로 코드가 작동된다.
SELECT
category,
COUNT(*) as `주문 건수`
FROM order_stat
GROUP BY category
ORDER BY `주문 건수`; -- 오류 없이 의도대로 정상적으로 작동- ' ', " " 따옴표 방식 (비추천)
- SELECT 절에서 사용하는 것은 상관이 없다.
- SELECT 절이 아닌 다른 절에서 alias 된 것을 사용할 때 사용자가 의도한대로 코드가 작동되지 않는다.
- ' '과 " "는 문자 상수로 인식되기 때문에 아래 예시에서는 "주문 건수" 문자열 상수를 기준으로 정렬을 해버린다.
SELECT
category,
COUNT(*) as `주문 건수`
FROM order_stat
GROUP BY category
ORDER BY '주문 건수'; -- 백틱 유의
14. SQL 연산 순서
- From > Where > Group by > Having > Select > Order by
- 암기 방법 : From 왜 구 했 서 오
- SQL 연산 순서를 기억해야 하는 이유 : 문법적으로 사용 가능한지 체크하기 위해서
- 추후 실행 계획 학습에 중요한 개념
SELECT
category,
COUNT(*) as `premium_order_count`
FROM order_stat
WHERE price >= 100000 -- WHERE 절에서 별칭이 사용 불가능한 이유는,
-- SELECT는 아직 수행이 되지 않았기 때문에 프로그램은 별칭을 모르는 상태이기 때문이다.
GROUP BY category
HAVING COUNT(*) >= 2
ORDER BY premium_order_count;
-- ORDER BY에서 별칭이 사용 가능한 이유는, SELECT 수행을 마치고 그 이후에 ORDER BY가 실행되기 때문이다.
출처
일부 자료는 김영한의 실전 데이터베이스 입문 - 모든 IT인을 위한 SQL 첫걸음(SQL부터 차근차근) 을 참조하였음.
