SQL (MYSQL) 기본 정리

SQL의 기본 쿼리들을 정리해봅니다.

기본중의 기본기를 정리해 봅니다.

DB 선택 `SELECT`

스페이스가 반드시 있어야 함.

SELECT * FROM 테이블명;

명령의 종류 / 모든 열 / 테이블명 / 명령의 마지막

크게는
SELECT 구 / FROM 구
로 나뉜다.

* 에스터리스크는 모든 열을 나타낸다.
예약어와 데이터베이스 객체명은 대소문자를 구별하지 않는다.

DB 테이블 정보를 본다 `DESC`

SQL 명령이 아니다.

DESC 테이블명;

테이블에 어떤 열이 정의 되었음을 알 수 있게 된다.

검색 조건을 지정한다 `WHERE`

SELECT 열1, 열2 FROM 테이블명 WHERE 조건식

열 선택은 SELECT
행 선택은 WHERE

SELECT 열1, 열2 FROM 테이블명

반드시 콤마 , 로 구별해야 한다.

WEHRE `조건식`

SELECT * FROM sample21 WHERE no = 2;
SELECT * FROM sample21 WHERE name = '김재진';

* `no = 2`
* `name = '김재진'` 

이런식으로 조건식을 주어야 한다.

같지 않은 경우는 <> 혹은 != 을 한다.
문자열 상수 경우에는 ' ' 혹은 " "으로 감싸준다.
날짜 시간형 상수에는 ' ' 로 감싸준다.
- 연, 월, 일 은 - 로 구분한다.
- 시간은 : 로 구분한다.

WEHRE `NULL`

SELECT * FROM sample21 WHERE birthday IS NULL;
SELECT * FROM sample21 WHERE birthday IS NOT NULL;

NULL의 경우는 = 로 확인이 불가능하다.
IS NULL을 이용한다.

조건식에 `AND` `OR` 넣기

SELECT * FROM sample24 WHERE a != 0 and b != 0;
SELECT * FROM sample24 WHERE a != 0 or b != 0;
SELECT * FROM sample24 WHERE (a=1 OR a=2) AND (b=1 OR b=2);

AND가 우선순위가 더 높다.
괄호를 통해 조절 할 수 있다.
OR 조건은 괄호로 묶는것이 좋다.
띄어 쓰기가 있어도 상관 없다.

조건식에 `NOT` 넣기

SELECT * FROM sample24 WHERE NOT (no = 1 or no = 2);

조건식이 2개 이상인경우 괄호를 넣어준다.

패턴매칭 `LIKE`

열 LIKE '패턴'
열 LIKE '패턴%'
열 LIKE '패턴_'

백틱 ` 이 아니라 ' 이다.

= 은 완전히 동일하는지 확인한다.
LIKE는 일부분이 매칭하는지 확인할 수 있다.
- _ 를 통해 임의의 문자 하나가 매칭되는지 확인 한다.
- % 를 통해 임의의 문자열이 매칭되는지 확인 한다.

SELECT *FROM sample25 WHERE text LIKE 'SQL%';
SELECT *FROM sample25 WHERE text LIKE '%SQL%';
SELECT *FROM sample25 WHERE text LIKE '%SQL';
SELECT *FROM sample25 WHERE text LIKE '%\%%';

%는 빈 문자열에 대해서는 매칭이 된다.
%를 패턴 검색하고 싶다면 \ 이스케이프를 사용한다.
- %를 패턴 검색하고 싶다면 \%로 한다.
- _를 패턴 검색하고 싶다면 \_로 한다.
' 를 검색하고 싶다면, 2개 연속으로 사용한다.
- ''''

정렬 `ORDER BY`

검색 결과를 정렬한다.
WHERE 구 뒤에 붙인다.

SELECT * FROM sample31 ORDER BY age;
SELECT * FROM sample31 ORDER BY address;
SELECT * FROM sample31 ORDER BY age DESC;

내림차순 정렬은 DESC로 한다. descendant
오름차순 정렬은 생략이 가능하며, ASC로 한다. ascendant
ORDERBY는 테이블에 영향을 주지 않는다. SELECT 이므로.
문자열 VARCHAR 와, 숫자 INT 시 주의
- 문자열은 10이 2 보다 작다고 인식한다.

복수의 열을 관리하기

SELECT * FROM sample32 ORDER BY a;

a열에 대해서만 정렬이 된다.

SELECT * FROM sample32 ORDER BY a, b;

a열에 대해서 정렬이 되고, b에 대해 정렬한다.

SELECT * FROM sample32 ORDER BY b, a;
b열에 대해서 정렬이 되고, a에 대해 정렬한다.

, 를 중간에 넣어 복수의 열을 관리할 수 있게 된다.

각 열에 대해서 정렬방법 지정하기

SELECT * FROM sample32 ORDER BY a DESC, b;

뒤조건에 DESC나 ASC 조건을 붙여주면 된다.
NULL의 경우는 가장 작은 값으로 인식된다.

결과행 제한 `LIMIT`

상위 몇개, 하위 몇개에만 관심을 가지는 경우.
표준 SQL이 아니다. MYSQL, PostgreSQL에만 사용이 가능하다.

SELECT 열명 FROM 테이블명 WHERE 조건식 ORDER BY 열명 LIMIT 행수

WHERE구 나 ORDER BY 뒤에 붙는다.

SELECT * FROM sample33 LIMIT 3;
SELECT * FROM sample33 ORDER BY no DESC LIMIT 3;

정렬을 한뒤 LIMIT 을 하면 순차적으로 진행된다.

수치연산

SELECT 구에서 연산하기

SELECT 식1, 식2 FROM 테이블명

, 를 통해서 새로운 식을 추가할 수 있다.
식 이름으로 새로운 열을 만들어서, SELECT이 가능하다.

SELECT * , price * quantity FROM sample34;

price * quantity라는 새로운 열이 생기고, 결과값이 행으로 들어간다.

별명 붙이기 `AS`

SELECT * , price * quantity AS amount FROM sample34;
SELECT * , price * quantity AS "금액" FROM sample34;

price * quantity 같은 열 이름은 가독성에 좋지 않다.
AS를 뒤에 붙여서 별명을 붙여줄 수 있다.
ASCII 문자 외에는 " " 더블 쿼트가 필요하다.
DB 객체명 에는 더블쿼트
문자열 상수 에는 싱글 쿼트 ' 를 쓴다.

`WHERE` 구에서 연산하기

SELECT * , price * quantity AS amount FROM sample34
-> WHERE price * quantity >= 2000;

WHERE에서 별명을 붙인 amount 로 대체가 불가능하다.
- 내부적으로 WHERE이 먼저 돌기 떄문이다.
- SELECT 구에서 지정한 별명은 WHERE 구에서 사용할 수 없다.
NULL은 0 이 아니다.

`ORDER BY` 구에서 연산하기

SELECT * , price * quantity AS amount FROM sample34
-> ORDER BY price * quantity DESC;


SELECT * , price * quantity AS amount FROM sample34
-> ORDER BY amout DESC;

ORDER BY 같은 경우는, SELECT 보다 서버에서 늦게 처리 되어지기 떄문에, 별명을 사용할 수 있다.

`함수` 를 이용하여 연산하기

반올림 한다. `ROUND`

DECIMAL 형에서는 실수도 저장할 수 있다.

SELECT amount, ROUND(amount) FROM sample341;
SELECT amount, ROUND(amount, 1) FROM sample341;
SELECT amount, ROUND(amount, -2) FROM sample341;

, 후에 숫자를 넣어주어, 반올림할 위치를 정해줄 수 있다.
- 1 의 경우 소수점 1자리 수까지
- -2 의 경우 10 자리수 까지

집계하기

COUNT
SUM
AVG
MIN
MAX

행 개수 구하기 `COUNT`

COUNT (집합)

SELECT COUNT(*) FROM sample51;

전체 행의 개수를 구한다.
괄호가 반드시 있어야 한다.

(*) 은 모든 열 을 나타내는 메타 문자이다.
(*) 을 메타문자로 사용할 수 있는것은 COUNT 밖에 없다.
5개의 행이 있으므로, 5가 반환된다.

SELECT COUNT(*) FROM sample51 WHERE name = 'A';

집계 함수는 WHERE 구 의 유무와 관계없이 결과값으로 하나의 행을 반환한다.

SELECT COUNT(*) FROM sample51;
SELECT COUNT(no), COUNT(name) FROM sample51;

전체 행의 개수는 5개 이다.
no에는 NULL이 없어 5가 리턴된다.
name에는 NULL이 하나 있어 4가 리턴된다.

중복을 제거하기 `DISTINCT`

SELECT ALL name FROM sample51;
SELECT DISTINCT name FROM sample51;

기본적으로 default 값은 ALL로 설정되어 있다.

중복을 제거하고 `COUNT` 하기

SELECT COUNT(ALL name), COUNT(DISTINCT name) FROM sample51;

COUNT 가 SELECT 보다 먼저 계산된다.
그러므로, COUNT 에 중복된것을 제거하고 넣어주어야 한다.
이때 DISTINCT는 인수가 아니므로, 콤마 , 를 붙이지 않는다.

`SUM`, `AVG` `MIN` `MAX` 구해보기

 SELECT SUM(quantity) FROM sample51;

 전체합 구하기

 SELECT AVG(quantity), SUM(quantity) / COUNT(quantity) FROM sample51;

 평균 구하기

SELECT MIN(quantity), MAX(quantity) FROM sample51;

최소, 최대 구하기

그룹화 `GROUP BY`

SELECT * FROM 테이블명 GROUP BY 열1, 열2, 열3....

집계함수로 넘겨줄 집합을 그룹으로 나눈다.
WHERE 처럼 테이블명 뒤에 붙는다.

SELECT name FROM sample51 GROUP BY name;

SELECT DISTINCT name FROM sample51; 결과와 동일하게 나온다.
name으로 GROUP화가 되었기 때문에, 중복을 제거하는 효과가 난다.

그룹화 하여 `COUNT` 해보기

SELECT COUNT(name) FROM sample51 WHERE quantity > 5;
가능하다.

SELECT name, COUNT(name) FROM sample51 WHERE quantity > 5; 
불가능하다. GROUP BY 로 처리해주어야 한다.

no	name	quantity
1	A	1
2	A	2
3	B	10
4	C	3
5	NULL	NULL

SELECT name, COUNT(name), sum(quantity) FROM sample51 GROUP BY name;

name	COUNT(name)	sum(quantity)
NULL	0	NULL
A	2	3
B	1	10
C	1	3

A 로 그룹화 된것은 2개이다. {A, 1} {A, 2}
- 총 개수는 2 , 총 합은 3이다.
B 로 그룹화 된것은 1개이다. {B, 10}
- 총 개수는 1 , 총 합은 10이다.

`HAVING` 구로 조건을 지정하기

집계 합수 (COUNT 등) 은 WHERE 조건식에 사용할 수 없다.
WHERE 처리가 GOUP BY 보다 빠르기 때문이다.
순서는 다음과 같다.
- WHERE GROUP BY SELECT ORDER BY

`HAVING` 구로 집계함수를 사용해 조건식을 지정할 수 있다.

GROUP BY 구의 뒤에 기술한다.
WHERE 와 동일하게 조건식을 지정한다.

SELECT name, COUNT(name) FROM sample51
-> GROUP BY name HAVING COUNT(name) = 1;

* 그룹화 한것중 `name`이 1인 것들을 골라낸다.


SELECT name, COUNT(name) FROM sample51
-> GROUP BY name HAVING SUM(quantity) > 5;

* 그룹화 한것중, 합이 5 초과 인것들을 골라낸다.

복수화 그룹화시 주의할점

GROUP BY에 지정한 열 이외의 열은 집계함수를 사용하지 않은채 SELECT에 기술 되어서는 안된다.

SELECT no, name, quantity FROM sample51 GROUP BY name; 

에러가 발생한다.

SELECT MIN(no), name, SUM(quantity) FROM sample51 GROUP BY name; 
SELECT no, quantity FROM sample51 GROUP BY no, quantity;

작동한다.

위의 쿼리 같은경우는, no 와 quantity 에서 어느값을 출력해야 하는지 모른다.
다시 말하면 GROUP BY에서 지정한 열 이외의 열은 집계 함수를 사용하지 않은채 SELECT 구에 지정할 수 없다

결과값 정렬.

SELECT name, COUNT(name), SUM(quantity) 
-> FROM sample51 GROUP BY name ORDER BY SUM(quantity) DESC;

끝에 ORDER BY로 정렬 해준다.

테이블을 결합하기 `JOIN`

테이블을 결합한다.
WHERE 과 AND를 이용한 결합은 구식이다

내부 결합 `INNER JOIN`

SELECT * FROM 테이블명1 INNER JOIN 테이블명2 ON 결합조건

JOIN은 연결시킨다는 것이다.
INNER JOIN은 내부 결합 이라는 말이다.
ON을 이용하여 결합조건을 지정한다.

SELECT S1.상품명, S2.상품명
-> FROM S1 INNER JOIN S2
-> ON S1.상품코드 = S2.상품코드

외부 결합

SELECT 상품3.상품명, 재고수.재고수
-> FROM 상품3 LEFT JOIN 재고수
-> ON 상품3.상품코드 = 재고수.상품코드
-> WHERE 상품3.상품분류 = '식료품';

상품코드가 같은것 중
식료품인것을 골라서
상품테이블을 왼쪽을 기준으로 잡고 외부 결합한다.

상품코드가 상품 3에 테이블에는 존재하나, 재고수 태이블에 없는 경우도 처리된다.
- 이때 SELECT의 재고수.재고수 에는 NULL로 표시된다.

서브쿼리와 변수

서브 쿼리

서브쿼리리를 통해 하부의 부수적인 쿼리를 하나더 이용할 수 있다.

--- 최소값을 가진것을 서브쿼리로 삭제하고 싶을때.
DELETE FROM 테이블명 WHERE target = (SELECT MIN(target) FROM 테이블명));

서브쿼리는 반드시 괄호를 쳐주어야 한다.
하지만 해당 쿼리는 MYSQL에서는 작동하지 않는다.
- MYSQL은 원칙적으로 추가, 삭제처럼 데이터를 조작하는경우, 같은 데이터를 서브쿼리에서 사용할 수 없게 설정되어있다.
  - 변수를 설정해준뒤 변수를 통해 조작해야한다.

변수

set @변수명 = 쿼리 or 넣고 싶은값

set @변수명 을 통해서 변수를 선언할 수 있다.
이것을 통해 위의 서브쿼리를 수정하면 아래와 같다.

set @taget = (SELECT MIN(target) FROM 테이블명);
DELETE FROM 테이블명 WHERE target = @target

주의할점은 변수명을 사용할때 @도 같이 붙여서 사용해줘야 한다.
만약 쿼리 도중에 변수를 수정하고 싶은경우 = 로 대입이 불가능하다.
- := 을 통해서 해주어야한다.
- 참고링크

조건

CASE WHEN THEN

검색 CASE

-- 검색 CASE 문
CASE
    WHEN 조건1
        THEN 조건1 결과
    WHEN 조건2
        THEN 조건2 결과
    ELSE 그 외 결과
END

검색 CASE인 경우에는, 조건을 상세하게 넣어주어야 한다.

SELECT CASE
        WHEN AGE >= 30
            THEN "당신은 젊지 않습니다."
        WHEN AGE <= 10
            THEN "당신은 젊습니다."
        ElSE "당신은 창창한 20대"
    END
FROM AGETABLE

단순 CASE

-- 단순 CASE문
CASE 식
    WHEN 예상되는 식1
        THEN 결과
    WHEN 예상되는 식2
        THEN 결과
    ELSE 그 외 결과
END

단순 케이스문에서는 NULL 을 구별하지 못한다.
- ISNULL을 쓸수 없는 구조이기 때문..
- WHEN NULL THEN을 하여도 CASE는 = 연산자를 통해서 구별하기 때문에 판별이 안된다.
- NULL 처리의 경우 COALSE를 이용해 주자.

CASE a
    WHEN 1 THEN "남자"
    WHEN 2 THEN "여자"
    ELSE "중성" 
END

IF

IF(조건식, true 결과, false 결과)

SELECT IF(score >= 85, "합격", "불합격") FROM SCORETABLE;

BETWEEN A AND B

A 와 B 사이에 있는것을 골라낸다

SELECT * FROM table1
WHERE col BETWEEN A AND B 

SELECT * FROM table1
WHERE col >= A AND col <= B

두 쿼리는 동일하게 작동한다!

문자열

문자열 길이재기

LENGTH, CHAR_LENGTH

LENGTH(“문자열”)
- byte 단위로 잰다.
CHAR_LENGTH(“문자열”)
- 한글자 단위로 잰다.
두가지의 방법이 있다.

SELECT LENGTH("정윤성");    // 9이 나온다. 한글은 3byte 이기 떄문
SELECT CHAR_LENGTH("정윤성");   // 3이 나온다.

정규식

REGEXP “정규식”, NOT REGEXP

"" 더블 쿼트로 감싼다.

SELECT NAME1 FROM TABLE1 WHERE NAME1 REGEXP "^[aeiou].*";
// NAME1 col에서 aeiou로 시작되는 것들을 찾아라. 
// ^가 대괄호 밖에 있을시 시작점이 맞는지를 확인한다.

SELECT NAME1 FROM TABLE1 WHERE NAME1 REGEXP "[^aeiou].*";
SELECT NAME1 FROM TABLE1 WHERE NAME1 NOT REGEXP "^[aeiou].*";
// NAME1 col에서 aeiou로 시작되지 않는 것들을 찾아라.

시작 문자가 매칭되는지?

범위 밖 앞에 ^ 를 쓴다.
- ^[범위].*

끝 문자가 매칭 되는지?

$ 를 쓴다.
- .*[범위]$

문자열 자르기

SUBSTR, SUBSTRING

둘다 동일하게 작동한다
필자의 경우 SUBSTRING 으로 설명해 보겠다.

앞에서부터 N개 자르기

SUBSTRING('문자열', N);

SUBSTRING('JungYoonSung', 4);
-- 결과 : Jung

N번쨰 위치부터 M개 자르기

SUSBSTRING('문자열', N, M)

SUBSTRING('JungYoonSung', 5, 4);
-- 결과 : Yoon

0 BASE 가 아닌것을 주의한다.

뒤에서부터 N개 자르기

SUSBSTRING('문자열', -N)

SUBSTRING('JungYoonSung', -4);
-- 결과 Sung

뒤의 N번째부터 M개 자르기

SUSBSTRING('문자열', -N, M)

SUBSTRING('JungYoonSung', -8, 4);
--- 결과 Yoon

두번째 파라미터는 -> 방향으로 읽습니다.

문자열을 이어 붙이기

CONCAT('Jung', ' ' , 'YOONSUNG')

SELECT 한것만 화면에 출력된다는 기억한다.

SELECT CONCAT('Hi ', Name) FROM NameTable;

이름 테이블에서 YoonSung을 뺴오고
- Hi YoonSung 과 같은 결과를 유도할 수 있다.

소문자로 바꾸기

LOWER('String')

SELECT LOWER(Name) FROM NameTable;
SELECT LOWER(SUBSTRING(Name, 1, 1)) FROM NameTable;

이름 테이블에서 나오는 결과를 소문자로 바꿀 수 있다.
SUBSTRING을 응용해서 특정 문자열들만 소문자로 바꿀 수 도 있다.

문자열 교체하기

REPLACE(STRING, 바꿀놈, 바뀌는값)

SELECT REPLACE('JungYeunSung', 'Yeun', 'Yoon');

JungYoonSung

REPLACE 를 숫자에도 써보기

INTEGER형 숫자에도 됩니다.

SELECT REPLACE(1001, '0', '1');

1111

날짜

기간별로 groupBy 하기

grouBy 절에 year, week 등을 쓰면된다.

SELECT DATE_FORMAT(created_at, '%Y-%m-%d') AS date, COUNT(*) AS count  
FROM your_table  
GROUP BY YEAR(created_at), WEEK(created_at)  
ORDER BY created_at DESC;  

created_at 을기준으로 7일간격으로 데이터를 세고 싶은경우, YEAR() 로 먼저 묶은뒤 2번째 조건으로 WEEK() 을 넣어 묶을 수 있다.
위 쿼리의 결과로 일주일 간격씩의 데이터를 카운팅 할 수 있다.
그외 date(), week(), month() 등등이 있다.

Reference

SQL 첫걸음
https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_week

SQL의 기본 쿼리들을 정리해봅니다.

DB 선택 SELECT

DB 테이블 정보를 본다 DESC

검색 조건을 지정한다 WHERE

WEHRE 조건식

WEHRE NULL

조건식에 AND OR 넣기

조건식에 NOT 넣기

패턴매칭 LIKE

정렬 ORDER BY

복수의 열을 관리하기

각 열에 대해서 정렬방법 지정하기

결과행 제한 LIMIT

수치연산

SELECT 구에서 연산하기

별명 붙이기 AS

WHERE 구에서 연산하기

ORDER BY 구에서 연산하기

함수 를 이용하여 연산하기

반올림 한다. ROUND

집계하기

행 개수 구하기 COUNT

중복을 제거하기 DISTINCT

중복을 제거하고 COUNT 하기

SUM, AVG MIN MAX 구해보기

그룹화 GROUP BY

그룹화 하여 COUNT 해보기

HAVING 구로 조건을 지정하기

HAVING 구로 집계함수를 사용해 조건식을 지정할 수 있다.

복수화 그룹화시 주의할점

결과값 정렬.

테이블을 결합하기 JOIN

내부 결합 INNER JOIN

외부 결합

서브쿼리와 변수

서브 쿼리

변수

조건

검색 CASE

단순 CASE

IF

BETWEEN A AND B

문자열

문자열 길이재기

정규식

시작 문자가 매칭되는지?

끝 문자가 매칭 되는지?

문자열 자르기

앞에서부터 N개 자르기

N번쨰 위치부터 M개 자르기

뒤에서부터 N개 자르기

뒤의 N번째부터 M개 자르기

문자열을 이어 붙이기

소문자로 바꾸기

문자열 교체하기

REPLACE 를 숫자에도 써보기

날짜

기간별로 groupBy 하기

Reference

DB 선택 `SELECT`

DB 테이블 정보를 본다 `DESC`

검색 조건을 지정한다 `WHERE`

WEHRE `조건식`

WEHRE `NULL`

조건식에 `AND` `OR` 넣기

조건식에 `NOT` 넣기

패턴매칭 `LIKE`

정렬 `ORDER BY`

결과행 제한 `LIMIT`

별명 붙이기 `AS`

`WHERE` 구에서 연산하기

`ORDER BY` 구에서 연산하기

`함수` 를 이용하여 연산하기

반올림 한다. `ROUND`

행 개수 구하기 `COUNT`

중복을 제거하기 `DISTINCT`

중복을 제거하고 `COUNT` 하기

`SUM`, `AVG` `MIN` `MAX` 구해보기

그룹화 `GROUP BY`

그룹화 하여 `COUNT` 해보기

`HAVING` 구로 조건을 지정하기

`HAVING` 구로 집계함수를 사용해 조건식을 지정할 수 있다.

테이블을 결합하기 `JOIN`

내부 결합 `INNER JOIN`