[DB] chapter 5 데이터 검색과 그루핑

SELECT --- FROM 문

SELECT 문의 형식

SELECT
	[ALL | DISTINCT | DISTINCTROW ]
    	[HIGH_PRIORITY]
        [MAX_STATEMENT_TIME = N]
        [STRAIGHT_JOIN]
        [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
        [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROW]
     select_expr [, select_expr ..]
     [FROM table_references
     	[PARTITION partition_list]
     [WHERE where_condition]
     [GROUP BY {col_name | expr | position}
     	[ASC | DESC], ... [WITH ROLLUP]]
     [HAVING where_condition]
     [ORDER BY {col_name | expr | position}
     	[ASC | DESC], ... ]
     [LIMIT {[offset,] row_count | row_count | row_count OFFSET offset}]
     [PROCEDURE procedure_name(argument_list)]
     [INTO OURFILE 'file_name'
     	[CHARACTER SET charset_name]
        export_options
        | INTO DUMPFILE 'file_name'
        | INTO var_name [, var_name]]
      [FOR UPDATE | LOCK IN SHARE MODE]]
     

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문의 기본적인 틀은 다음과 같다

SELECT 열이름
FROM 테이블이름
WHERE 조건

USE 문

사용할 데이터베이스를 지정하기 위한 쿼리문

USE 데이터베이스이름;

지금부터 수행되는 모든 쿼리는 이 데이터베이스에서 수행한다 라는 쿼리

SELECT ... FROM 문

SELECT ... FROM 문은 데이터를 열 단위로 검색하는 구문.
SELECT 다음에는 검색하려는 열 이름을, FROM 다음에는 검색하려는 테이블 이름을 넣는다.

4.1 모든 열 검색

• 는 SQL에서 '모든 것'을 뜻한다. 열 이름 자리에 있을 때는 '모든 열'이라는 의미로 사용된다.

SELECT * FROM TABLE;

4.2 원하는 열만 검색

SELECT col_name FROM table_name;

위와 같은 sql문을 이용해 원하는 열만 추출할 수 있다.

MySQL의 주석

• '--' 이후 문구는 주석 처리
• '/* */' 여러 문구를 주석 처리할 때 쓰인다.

열 이름의 별칭
열 이름 뒤에 'AS 별칭'형식을 붙여 열 이름을 별도의 별칭으로 지정 가능하다.
필드 제목이 길어서 알아보기 힘들거나 복잡해질 때 별칭을 사용한다.
ex) SELECT col_name AS alias FROM table;

SELECT ... FROM ... WHERE 문

SELECT FROM 문에 WHERE 절을 추가하면 특정한 조건을 만족하는 데이터만 조회할 수 있다.

SELECT 열이름 FROM 테이블이름 WHERE 조건식;

예를 들어 이렇게 사용할 수 있다.

SELECT * FROM userTBL WHERE userName = '강호동';

조건 연산자와 관계 연산자

WHERE 절에 조건 연산자 (=, <, >, <=, >=, <>, !=)와
관계 연산자(NOT, AND, OR)를 잘 조합해 사용하면 다양한 조건의 쿼리문 작성 가능

BETWEEN ... AND, IN(), LIKE 연산자

SELECT userName, height FROM userTBL WHERE height >= 180 AND height <= 182;

키가 180~182cm 인 회원을 조회하는 쿼리문이다.
이 쿼리문을 BETWEEN을 사용해 작성할 수 도 있다.

SELECT userName, height FROM userTBL WHERE height BETWEEN 180 AND 182;

숫자와 같은 값들은 BETWEEN을 사용할 수 있지만 이산적인 값은 사용이 불가하다.
이럴때는 IN을 사용할 수 있다.

SELECT userName, addr FROM userTBL WHERE addr='경남' OR addr='충남' OR addr='경북';

위 쿼리문을 IN을 이용해

SELECT userName, addr FROM userTBL WHERE addr IN ( '경남', '충남', '경북');

와 같이 사용할 수도 있다.

문자열의 내용을 검색하려면 LIKE 연산자를 사용한다.

SELECT userName, height FROM userTBL WHERE userName LIKE '김%';

위 쿼리는 성이 '김' 이고 뒤는 무엇이든 허용한다는 의미.
무엇이든 허용은 % 연산자를 사용한다.
만약 한 글자만 매치시키려면 '_'를 사용한다.

예를들어 성은 관계없고 이름이 형주인 사람을 검색하려면

SELECT userName, height FROM userTBL WHERE userName LIKE '_형주';

로 사용할 수 있다.

서브쿼리와 ANY, ALL, SOME 연산자

쿼리문 안에 또 쿼리문을 넣어 사용하는 것을 서브쿼리라고 한다.
예를들어 원래는 김용만보다 키가 크거나 같은 사람의 이름과 키를 출력하고 싶을 때,
WHERE 조건에 김용만의 키를 직접 넣는다 .

SELECT userName, height FROM userTBL WHERE height > 177;

하지만 김용만의 키를 모르거나 직접쓰지 않고 사용하려면 서브 쿼리를 이용할 수 있다.

SELECT userName. height FROM userTBL WHERE height > (SELECT height FROM userTBL WHERE userName = '김용만');

지역이 경기인 사람보다 키가 크거나 같은 사람을 추출하고 싶을때도 서브 쿼리를 이용

SELECT userName, height FROM userTBL
	WHERE height >= (SELECT height FROM usreTBL WHERE addr = '경기'); 

하지만 서브 쿼리문의 결과가 복수일때는 오류가 발생한다.

이럴 때는 ANY 연산자를 사용할 수 있다.

SELECT userName, height FROM userTBL
	WHERE height >= ANY (SELECT height FROM usreTBL WHERE addr = '경기'); 

OR 연산 처럼 둘 중 어느것이라도 만족한다면 추출을 하는 연산자이다.

OR의 반대 AND 처럼 ANY가 조건 중 하나라도 만족을 하는 것이라면
ALL은 모든 조건을 만족해야한다.

SELECT userName, height FROM userTBL
	WHERE height >= ALL (SELECT height FROM usreTBL WHERE addr = '경기'); 

이 경우는 모든 지역이 경기인 사람보다 키가 커야 추출이 되기 때문에
경기 지역의 가장 큰 사람보다 크거나 같은 사람과 같은 의미가 된다.

SOME은 ANY와 동일한 의미로 사용이 된다.

ORDER BY 절

ORDER BY는 결과에는 영향이 없지만 결과가 출력될 때 정렬에 관여한다.
기본적으로 ORDER BY는 오름차순으로 정렬된다.

SELECT userName, mDate FROM userTBL ORDER BY mDate;

출력결과를 mDate 의 오름차순 기준으로 표시한다.

내림차순을 사용하고 싶다면 열 이름 뒤에 DESC를 추가한다. (오름차순은 ASC 생략가능)

SELECT userName, mDate FROM userTBL ORDER BY mDate DESC;

정렬 기준을 복수로 설정 할 수 있다.

SELECT userName, mDate FROM userTBL ORDER BY mDate DESC. userNAme ASC;

DISTINCT 키워드

회원 테이블에서 회원들의 거주 지역이 몇 곳인지 출력할 때

SELECT addr FROM userTBL;

하지만 이렇게 출력하면 중복이 발생한다.
이럴 때 DISTINCT를 사용해 중복을 제거할 수 있다.

SELECT DISTINCT addr FROM userTBL;

LIMIT 절

LIMIT 절을 사용해 양이 많은 데이터베이스에서 쿼리를 사용하지 않고 빠르게 상위 N개 의 데이터를 출력할 수 있다.

SELECT emp_no, hire_date FROM employees
	ORDER BY hire_date ASC
    LIMIT 5;

'LIMIT 개수 OFFSET 시작' 형식으로도 사용할 수 있다.
시작은 0부터 시작한다.
예를 들어

LIMIT 2 OFFSET 0;

은 처음 두 행만 나오게 할 수 있다. OFFSET은 생략 가능하다.

CREATE TABLE ... SELECT 문

CREATE TABLE ... SELECT 구문은 테이블을 복사하여 사용할 때 많이 사용된다.

CREATE TABLE 새로운 테이블 (SELECT 복사할열 FROM 기존테이블)

create table의 경우 키 조건은 함께 복사되지 않는다.

GROUP BY ... HAVING 문

GROUP BY 절

SELECT 문의 형식 중에서 GROUP BY ... HAVING 절의 위치는 다음과 같다.

SELECT select_expr
	[FROM table_references]
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}]
    [HAVING wherer_condition]
    [ORDER BY {col_name | expr | position}]

GROUP BY 절은 말 그대로 그룹을 묶는 역할을 한다.

집계 함수는 주로 GROUP BY 절과 함꼐 쓰이며 데이터를 그룹 짓는 기능을 한다.

집계 함수

SUM()외에 GROUP BY 절과 함께 자주 사용되는 집계 함수 ( or 집합 함수)는 다음과 같다.

함수	설명
AVG()	평균을 구한다.
MIN()	최솟값을 구한다.
MAX()	최댓값을 구한다.
COUNT()	행의 개수를 센다.
COUNT(DISTINCT)	행의 개수를 센다(중복은 1개만 인정).
STDEV()	표준편차를 구한다.
VAR_SAMP()	분산을 구한다.

전체적으로 한 번 구매할 때마다 평균 몇 개를 구매했는지 구하는 쿼리문

SELECT AVG(amount) AS '평균 구매 개수' FROM buyTBL;

회원 별로 확인하고 싶을 때는

SELECT userID, AVG(amount) AS '평균 구매 개수'
	FROM buyTBL GROUP BY userID;

휴대폰이 있는 회원수를 세고 싶을 때

SELECT COUNT(*) FROM userTBL;

HAVING 절

집계 함수를 사용해 회원별 총 구매액

SELECT userID AS '사용자', SUM(price*amount) AS '총구매액'
	FROM buyTBL
    GROUP BY userID;

총 구매액이 1000 이상인 회원만 조회하고 싶을 때,
WHERE이 가장 먼저 떠오르지만 집계함수를 WHERE 절에 사용할 수 없다.
이럴 때는 HAVING 절을 사용한다.
HAVING 절은 WHERE 절과 비슷한 개념으로 조건을 제한하지만 집계함수에 대해서만 조건을 제한한다.

그렇기 때문에 HAVING 절은 반드시 GROUP BY 절 다음에 쓴다.

SELECT userID AS '사용자', SUM(price*amount) AS '총구매액'
	FROM buyTBL
    GROUP BY userID
    HAVING SUM(price*amount) > 1000;

WITH ROLLUP 절

총합 또는 중간 합계를 구해야 한다면 GROUP BY 절과 함께 WITH ROLLUP 절을 사용한다.
실행 결과 중간중간에 num 열이 NULL인 추가 행이 각 그룹의 소합계이고 마지막이 각 소합계의 합계인 총합이다.

SELECT num, groupName, SUM(price * amount) AS '비용'
	FROM buyTBL
    GROUP BY groupName, num
    WITH ROLLUP;

위의 쿼리문에서 num은 기본키로, 각 항목이 보이도록 하기 위해 넣은 것으로 소합계와 총합만 필요하다면 다음과 같이 num을 제외할 수 있다.

SELECT groupName, SUM(price * amount) AS '비용'
	FROM buyTBL
    GROUP BY groupName, num
    WITH ROLLUP;