작성 동기
학부 시절, 데이터베이스 과목을 들으면서 배웠던 SQL 문법에 대한 기억이 흐려졌다.
아무래도 SQL을 사용하는 대신 ORM을 이용해 기본적인 CRUD만 사용했기 때문인 것 같다.
하지만 SQL은 백엔드 개발자에게 기본 소양으로도 여겨진다.
복잡한 질의문이 필요할 때는 반드시 SQL을 사용해야한다.
그럼 MySQL을 기준으로 가장 기본이 되는 SQL 문법들을 예시와 함께 정리해보자.
DML vs DDL vs DCL
DML
Data Manipulation Language, 데이터 조작 언어
데이터 조작 -> 선택, 삽입, 수정, 삭제
DML 구문의 대상은 테이블의 행으로 사전에 정의된 테이블에 사용한다.
SELECT, INSERT, UPDATE, DELETE가 이 구문에 해당된다.
트랜잭션이 발생하는 SQL도 DML이다.
DDL
Data Definition Language, 데이터 정의어
테이블, 인덱스, 뷰 등 데이터베이스 개체를 생성하고 삭제 및 변경하는 역할을 한다.
CREATE, DROP, ALTER 등이 이에 해당된다.
DDL은 트랜잭션을 발생시키지 않기 때문에 실행 즉시 적용된다는 특징이 있다.
(ROLLBACK이나 COMMIT도 적용이 안된다)
DCL
Data Control Language, 데이터 제어 언어
주로 사용자에게 권한을 부여하거나 뺏을 때 사용하는 구문
GRANT, REVOKE, DENY 등이 이에 해당된다.
USE
사용할 데이터베이스를 지정한다.
기본 형식: USE db_name;
다른 DB를 사용하겠다고 명시하지 않는 이상 이후에 작성되는 모든 SQL문은 db_name에서 수행된다.
SELECT
가장 많이 사용되는 질의문으로 테이블에서 원하는 정보를 얻기 위해 사용한다.
쉬우면서도 어려운 질의문이기 때문에 잘 익힐 필요가 있다.
SELECT 구문은 다양한 옵션들을 가지고 있기 때문에 하나하나 보면 복잡해 보이기도 한다.
하지만 실제로 많이 사용되는 형태는 어느정도 정해져있다.
기본 형식:
SELECT select_expr
FROM table_references
WHERE where_condition
GROUP BY {col_name | expr | position}
HAVING where_condition
ORDER BY {col_name | expr | position}각 행으로 구분된 옵션은 생략이 가능하지만 순서는 지켜져야한다.
SELECT * FROM table;
: table에 있는 모든 열 데이터를 조회한다.
SELECT * FROM database_name.table_name;
: 앞에서 USE를 사용했기 때문에 그냥 테이블 이름을 주었는데
원래는 테이블의 전체 이름은 데이터베이스_이름.테이블_이름 으로 표현한다.
SELECT name FROM table;
SELECT name, age FROM table;
: 전체 열이 아닌 특정 열만 가져오고 싶을 때 * 대신 필요한 열의 이름을 입력하면 된다.
여기서는 테이블에 name 열만 조회하도록 했다.
여러 개의 열을 조회하고자 할 때는 콤마로 구분하면 된다.
WHERE절
DB의 모든 데이터를 확인하는 것은 딱히 의미가 없다, 원하는 데이터를 얻기 위해서는 조건에 맞는 데이터를 가져올 수 있어야한다.
WHERE절은 조회 결과에 특정한 조건을 주어서 원하는 데이터를 얻는데 사용한다.
SELECT * FROM member WHERE name = '김계란';
: 테이블에서 이름이 '김계란'인 사람만 찾는다.
SELECT * FROM member WHERE name LIKE '김%';
: LIKE는 문자열의 내용을 검색할 때 사용하면 유용하다.
김% 라고 하면 김으로 시작하는 이름을 추출한다.
% 문자는 꼭 LIKE 문으로 검색 해야한다.
만약 한 글자만 매치하고자 한다면 _ 을 사용하면 된다. (ex: LIKE '_계란')
SELECT name, age FROM member WHERE height >= 170 AND weight >= 60;
: 키가 170이상이고 몸무게가 60이상인 사람들만 찾아서 이름과 나이를 보여준다.
이 예시처럼 관계 연산자를 사용해서 조회가 가능하다.
SELECT name, age FROM member WHERE height >= 180 OR height < 160;
: OR 연산자를 사용해서 구성한 예시이다.
키가 180 이상이거나 160 미만인 사람만을 찾을 수 있다.
SELECT name, age FROM member WHERE height BETWEEN 170 AND 175;
: 앞의 예제처럼 부등호를 이용해 조회하는 것도 가능하지만 연속적인 값을 가지고 있다면
BETWEEN과 AND를 사용해도 된다. (키가 170 <= height <= 175인 사람 조회)
SELECT name, age FROM member WHERE addr='서울' OR addr='경기' OR addr='인천';
SELECT name, age FROM member WHERE addr IN ('서울', '경기', '인천');
: 연속적인 값이 아닐 때는 BETWEEN과 AND를 사용할 수 없다.
대신 IN을 사용하면 조금 더 편하게 사용할 수 있다.
서브쿼리(SubQuery, 하위쿼리)
서브쿼리는 이름 그대로 쿼리문 안에 들어가는 쿼리문을 말한다.
굉장히 자주 사용하기 때문에 익숙해지는 것이 좋다.
예시:
SELECT name, height FROM member
WHERE height > (SELECT height FROM member WHERE name = '김계란');만약 김계란 보다 키가 큰 사람들을 검색하고 싶다면 김계란의 키를 알아야한다.
김계란의 키를 직접 적어줘도 되지만 서브쿼리를 사용해서 김계란의 키를 받아서 넣어주는 식으로 쿼리문을 구성하는 것도 가능하다.
단 주의할 점은 위와 같은 쿼리문은 서브쿼리가 1건이 나와야지 문제가 없다.
SELECT name, height FROM member
WHERE height >= (SELECT height FROM member WHERE name LIKE '김%');만약 이와 같은 쿼리문을 실행했다면 어떻게 될까?
이름이 김으로 시작하는 사람이 딱 1명만 존재해야한다.
1명을 넘어가는 순간 쿼리문에 오류가 발생한다.
height와 크거나 같은지를 비교하고자 하는데 1명이 넘어가면 구조적으로 이상하다.
ANY
이럴 때 필요한 구문이 ANY 구문이다.
SELECT name, height FROM member
WHERE height >= ANY (SELECT height FROM member WHERE name LIKE '김%');ANY를 사용하면 검색된 결과 중에서 아무거나 크거나 같은 사람을 찾겠다는 의미로 해석된다.
김으로 시작하는 사람이 2명있고 각각 173, 177이라고 할 때, 173보다 크거나 같은 사람 또는 177보다 크거나 같은 사람 모두 출력한다.
ALL
ALL을 사용할 경우도 알아보자.
ALL을 사용하면 같은 조건일 때 (김으로 시작하는 사람이 2명있고 각각 173, 177이라고 할 때)
이 값에 대한 식을 둘 다 만족해야한다는 의미가 된다.
따라서 173보다 크거나 같으면서 177보다 크거나 같아야 하기 때문에 결과적으로 177보다 크거나 같은 사람이 조회된다.
ORDER BY
결과에는 영향을 주지 않고, 결과가 출력되는 순서를 조절하는 구문이다.
ORDER BY는 MySQL 성능을 떨어뜨릴 수 있기 때문에 안쓸 수 있다면 안쓰는 것이 좋다.
SELECT name, height FROM member ORDER BY age DESC;
: ORDER BY는 기본적으로 오름차순 정렬(ASC)이다.
내림차순 정렬을 하기 위해서는 DESC 를 붙여주어야 한다.
정렬을 두 가지 조건으로 할 수도 있다.
SELECT name, height FROM member ORDER BY age DESC, name ASC;
: 만약 age가 같다면 이름 순으로 정렬하겠다는 의미이다.
DISTINCT
중복을 제거해주는 구문, 자주 사용한다.
SELECT DISTINCT addr FROM member;
: 주소 중 중복된 것은 1개만 표시하도록 하여 조회한다.
LIMIT
출력의 개수를 제한해준다.
SELECT name, age FROM member LIMIT 5;
: 제한을 안두면 엄청나게 많은 데이터가 조회되어 DBMS에게 부담을 줄 수 있다.
LIMIT를 통해 조회 건수의 제한을 두고 원하는 개수만큼을 조회할 수 있다.
SELECT name, age FROM member LIMIT 0, 5;
SELECT name, age FROM member LIMIT 5 OFFSET 0;
: LIMIT절은 LIMIT 시작, 개수 또는 LIMIT 개수 OFFSET 시작 형식으도 표현할 수 있는데,
시작은 0부터 시작하며 5개를 출력한다는 의미이다.
SELECT 활용: 테이블 복사
예시:
USE table;
CREATE TABLE new_table (SELECT * FROM member);
SELECT * FROM new_table;SELECT로 member 테이블의 모든 열을 조회한 결과를 new_table에 넣게 된다.
물론 열을 직접 지정하면 new_table에 지정한 열만 복사된다.
주의: PK, FK 같은 테이블 내 제약 조건은 복사 되지 않는다.
GROUP BY
GROUP BY는 말 그대로 그룹으로 묶어주는 역할을 한다.
집계함수와 함께 자주 사용된다.
집계함수
- SUM(): 합 계산
- AVG(): 평균 계산
- MIN(): 최소값 계산
- MAX(): 최대값 계산
- COUNT(): 행의 개수 계산
- COUNT(DISTINCT): 행의 개수를 중복 없이 센다.
- VAR_SAMP(): 분산 계산
- STDEV(): 표준편차 계산
SELECT userID, SUM(amount) FROM member_buy GROUP BY userID;
: 사용자별로 구매 개수를 조회하고 싶을 때 이렇게 GROUP BY를 통해 데이터를 그룹화 한다.
사용자별로 amount의 합계를 계산해서 조회할 수 있다.
SELECT userID, SUM(price * amount) FROM member_buy GROUP BY userID;
: 사용자별 구매액의 총합을 보고자 할 때 price와 amount를 곱한 값들을 더해서 그룹화 하면 된다.
SELECT userID, AVG(amount) FROM member_buy GROUP BY userID;
: 사용자별 amount의 평균을 계산한 결과를 보여준다.
만약 height의 최소값과 최대값이 담긴 행을 조회하고 싶을 때는 어떻게 하면 될까?
SELECT name, height FROM member
WHERE height = (SELECT MAX(height) FROM member)
OR height = (SELECT MIN(height) FROM member);서브쿼리와 조합하면 수월하게 구성이 가능하다.
SELECT COUNT(*) FROM member;
: member의 전체 데이터 개수를 계산한다.
SELECT COUNT(phone_number) FROM member;
: 휴대폰 번호가 있는 member 데이터의 개수만 계산한다.
NULL 값인 경우는 제외한다.
HAVING
GROUP BY와 함께 사용되는 WHERE절, 집계함수를 사용해 조건을 부여할 수 있다.
집계함수는 WHERE 절에 사용될 수 없기 때문에 GROUP Function과 관련이 있는 것은 HAVING 절에 사용해야한다.
SELECT userID, SUM(price * amount) FROM member_buy
GROUP BY userID
HAVING SUM(price * amount) > 1000;이렇게 하면 구매액이 1000 이상인 결과만 조회할 수 있다.
INSERT
INSERT는 데이터를 삽입하는 명령
기본형식: INSERT INTO table_name(column1, column2... VALUES (value1, value2 ...)
테이블 이름 다음에 나오는 열은 생략이 가능하다.
다만 생략할 경우 VALUES 뒤에 나오는 값들의 순서와 개수가 테이블에 정의된 열 순서, 개수와 동일해야한다.
또한 순서를 바꿔서 입력하는 것도 가능하며, 이 때는 입력한 순서에 맞춰 열 이름과 데이터를 적어야 한다.
특정 값만 입력하고 싶을 때는 직접 지정하면 된다.
INSERT INTO table(id, username) VALUES (2, '계란');
: 이렇게 할 경우 생략된 열에는 NULL 값이 들어가게 된다.
AUTO_INCREMENT
자동으로 값이 입력되는 것으로, 기본은 1부터 증가하는 값이 입력된다.
테이블 속성에 AUTO_INCREMENT가 지정되어있다면 자동으로 입력되기 때문에 없다고 생각하면 된다.
AUTO_INCREMENT는 반드시 PK 또는 UNIQUE 값으로 지정이 되어야 한다.
또한 증가하는 값이기 때문에 타입은 숫자 형식만 사용 가능하다.
ALTER TABLE table_name AUTO_INCREMENT=100;
: 자동 증가되는 입력 값을 100부터 입력되도록 변경할 수 있다.
증가 값은 기본적으로 1씩 증가하는데 이를 바꾸려면 서버 변수를 바꿔야한다.
SET @@auto_increment_increment=5;
: 증가 값을 5로 변경한다.
시작 값은 위에서 100으로 바꾸었다고 가정하면 다음과 같다.
100 -> 105 -> 110 -> 115 ...
INSERT INTO 여러 개 입력하기
INSERT INTO table_name VALUES (value1, value2), (value3, value4), (value5, value6);
3개의 쿼리문으로 나눠서 하는 것도 가능하지만 이렇게 할 수도 있다.
조건부 데이터 입력, 변경
만약 INSERT 문을 10개 수행할 때 첫 번째 INSERT 문에서 오류가 발생하면 뒤에 있는 나머지 INSERT 문도 실행되지 않는다. 하지만 MySQL은 오류가 발생해도 계속 진행하는 방법을 제공한다.
INSERT문에 IGNORE를 붙여주면 된다.
예: INSERT IGNORE INTO ~
(오류 난 것을 무시하고 다음 것을 실행하라는 뜻이다.)
또한 키가 중복일 때 UPDATE로 처리하는 방법도 제공한다.
INSERT INTO table VALUES('KIM', '김계란', '서울')
ON DUPLICATE KEY UPDATE name='김계란', addr='서울');UPDATE
데이터를 수정하는 구문
기본 형식: UPDATE table_name SET columns=value, columns2=value2 .. WHERE condition;
UPDATE 문은 대부분 WHERE 절이 들어가야한다.
WHERE 절이 안들어가면 모든 데이터가 UPDATE 된다. (-> 이런 경우는 많이 없다.)
WHERE 절을 안쓰는 경우 예시
UPDATE buy_table SET price=price*1.5;
: 구매 테이블의 단가가 모두 1.5배 인상 되어서 수정하는 경우
DELETE
데이터(행)를 삭제하는 구문
기본 형식: DELETE FROM table_name WHERE condition;
주의: WHERE문이 생략되면 전체 데이터를 삭제한다.
따라서 WHERE절을 쓰는게 일반적이다.
DELETE FROM table_name WHERE addr = '부산' LIMIT 5;
: 주소지가 부산인 데이터를 상위 5건만 삭제하는 쿼리문이다.
대용량 테이블의 삭제
CREATE TABLE table1 (SELECT * FROM table);
CREATE TABLE table2 (SELECT * FROM table);
CREATE TABLE table3 (SELECT * FROM table);
DELETE FROM table;
DROP TABLE table;
TRUNCATE TABLE table;서로 무슨 차이가 있을까?
- DELETE
원하는 데이터를 한 행씩 삭제하며 WHERE절로 조건에 따라 행 삭제가 가능하다.
삭제된 행에 대해 각각 트랜잭션 로그를 기록한다.
삭제 후 잘못 삭제한 데이터를 되돌리기가 가능하다. - TRUNCATE
테이블에서 데이터를 한꺼번에 지우며 개별 행 삭제가 안된다.
트랜잭션 로그에 한 번만 기록된다.
삭제 후에는 되돌리기는 불가능하다. - DROP
테이블 자체를 삭제한다. (테이블 행, 인덱스, 권한 등이 모두 삭제된다.)
삭제 후 되돌릴 수 없다.
셋 중에서 한 행씩 삭제하는 DELETE가 제일 오래걸린다.
DROP은 빠르지만 테이블 구조가 아예 날라간다.
WITH절과 CTE
CTE: Common Table Expression, 재귀적, 비재귀적 두 가지가 존재한다.
기존 뷰, 임시 테이블 등으로 사용하던 것을 대신 할 수 있고 간결하게 보여지는 장점이 있다.
CTE에 집중하는 포스트가 아니므로 비재귀적 CTE만 간단하게 알아보자
비재귀적 CTE
WITH CTE_TABLE_NAME(열 이름)
AS
(
<쿼리문>
)
SELECT 열 이름 FROM CTE_TABLE_NAME;CTE는 복잡한 쿼리문장을 단순화 시키는데 적합하다.
예시:
WITH cte_test(userId, total)
AS
(SELECT userId, SUM(price*amount)
FROM buy_table GROUP BY userId)
SELECT * FROM cte_test ORDER BY total DESC;- 테이블 열 매칭 관계
userId -> userId
SUM(price*amount) -> total
