집계와 서브쿼리

집계함수

일반적인 함수는 인수로 하나의 값을 지정하는데 집계함수는 함수의 인자로 집합을 지정한다.

COUNT

인수로 주어진 집합의 개수를 구해 반환하는 함수로, 테이블의 행의 개수를 구할 수 있다.

 

ignore NULL

집합 안에 NULL 값이 있을 경우 무시한다.

 

조건식 WHERE

WHERE을 이용하여 조건을 지정할 수도 있다.
SELECT 구와 함께 사용하므로 alias 또한 가능하다.
위에서 생략된 city = NULL 값의 개수를 count 하며, 테이블 이름을 지정해보자.

 

중복제거 DISTINCT

SELECT + DISTINCT

SELECT + COUNT + DISTINCT = COUNT 때문에 NULL 값이 생략된다.

 

SUM

집합의 합계를 구하는 집계함수이다. 수치형만 가능하며, COUNT 와 마찬가지로 NULL 값은 무시한다.

AVG

집합의 평균을 구하는 집계함수이다. SUM 과 마찬가지로 수치형만 가능하고, NULL 값은 무시한다.

MIN / MAX

최소/최대값을 구하는 집계함수이다. 문자열형과 날짜형, 시간형에도 사용 가능하며 NULL 값은 무시한다. 문자열의 경우 사전에 등재되어있는 순서대로 최소/최대값이 결정된다.

GROUP BY

GROUP BY 는 집계함수로 넘겨줄 집합을 그룹으로 나누기 위해 사용되는 구이다. DISTINCT 와 마찬가지로 중복을 제거하는 효과가 있다.

 

조건식 HAVING

집계함수는 WHERE 구에서 사용할 수 없으므로 HAVING 을 사용하여 조건식을 지정한다.

// WHERE 을 사용할 수 없는 이유는 연산의 우선 순위 때문이다
// alias 또한 지정 할 수 없다.
WHERE => GROUP BY => SELECT => ORDER BY

 

정렬 ORDER BY

서브쿼리

서브쿼리란 명령에 의한 데이터 질의로, 하부의 부수적인 질의를 의미한다. 쉽게 말하자면 하나의 쿼리문 안에 있는 또 다른 쿼리이다. 괄호로 묶어 지정한다.

how to use

UPDATE

테스트하던 도중에 id 5~7 의 city 값이 변경되어 아래의 모습과 같아졌다.

 

 

여기에서 city='LoL' 인 행의 city 값을 'delete' 로 UPDATE 해보자.
보통의 서브쿼리 사용문은 아래와 같으나,

// UPDATE 테이블명 SET 열=값 WHERE 열=(변수)
UPDATE users
SET city='delete'
WHERE city=(SELECT city='LoL' FROM users

mysql 의 경우 데이터를 추가하거나 갱신할 때에 동일한 테이블을 서브쿼리에서 사용할 수 없게 되어있으므로 한번 더 감싸서 alias 를 지정해주어야 한다.

mysql> UPDATE users
    -> SET city = 'delete'
    -> WHERE city=(SELECT city FROM
    -> (SELECT city WHERE city='LoL')
    -> AS x);

 

DELETE

이번에는 위에서 UPDATE 해주었던 city='delete' 인 값들을 삭제해보자.
UPDATE 와 같은 방법으로 진행하면 된다.

 

스칼라 값

SELECT 명령이 하나의 값만 반환하는 것을 '스칼라 값을 반환한다' 고 하고, 스칼라값을 반환하는 서브쿼리는 '스칼라 서브쿼리' 라고 한다. 앞서 HAVING 구를 설명할때 집계함수는 WHERE 구에서 사용할 수 없다고 한 바 있다. 그러나 스칼라 서브쿼리라면 WHERE 구에 사용할 수 있으므로 집계함수를 사용해 집계한 결과를 조건식으로 사용할 수 있게 된다.

// 스칼라 서브쿼리의 기본 사용 방법

mysql> SELECT
    -> (SELECT COUNT(*) FROM users) AS COUNT_USER;

+------------+
| COUNT_USER |
+------------+
|          4 |
+------------+
1 row in set (0.00 sec)

SET 구에서 서브쿼리 사용하기

UPDATE 의 SET 에서도 서브쿼리를 사용할 수 있다.
age 의 모든 열의 값을 age 의 최대값으로 갱신해보자.

mysql> UPDATE users
    -> SET age = (SELECT age FROM
    -> (SELECT MAX(age) AS age FROM users) AS x);
Query OK, 3 rows affected (0.00 sec)
Rows matched: 4  Changed: 3  Warnings: 0

mysql> SELECT * from users;
+----+-------+-----+--------+
| id | name  | age | city   |
+----+-------+-----+--------+
|  1 | keria |  26 | 부산   |
|  2 | cuzz  |  26 | 구미   |
|  3 | faker |  26 | 서울   |
|  4 | teddy |  26 | 안양   |
+----+-------+-----+--------+
4 rows in set (0.00 sec)

FROM 구에서 서브쿼리 사용하기

SELECT 명령 안에 SELECT 명령이 들어있는 듯 보이는 구조로, 중첩구조(nested) 혹은 내포구조 라고 부른다.

mysql> SELECT * FROM (SELECT * FROM users) AS new;
+----+-------+-----+--------+
| id | name  | age | city   |
+----+-------+-----+--------+
|  1 | keria |  26 | 부산   |
|  2 | cuzz  |  26 | 구미   |
|  3 | faker |  26 | 서울   |
|  4 | teddy |  26 | 안양   |
+----+-------+-----+--------+
4 rows in set (0.00 sec)

INSERT 명령과 서브쿼리

서브쿼리를 사용하면 INSERT 에서 VALUES 의 일부로 서브쿼리를 사용하는 방법과, VALUES 를 대신하여 SELECT 명령을 사용하는 두 가지 방법이 있다.

서브쿼리를 사용하는 방법

mysql> INSERT INTO nums VALUES(8,
    -> (SELECT COUNT(no) FROM nums AS a),
    -> (SELECT COUNT(price) FROM nums AS b),
    -> (SELECT COUNT(quantity) FROM nums AS c));
Query OK, 1 row affected (0.00 sec)

// 같은 테이블 내에서 지정하는 경우 alias 를 붙여주지 않으면 specify target error 발생

mysql> SELECT * from nums;
+----+----+-------+----------+
| id | no | price | quantity |
+----+----+-------+----------+
|  1 |  1 |    10 |        1 |
|  2 |  1 |    10 |        2 |
|  3 |  1 |    10 |        3 |
|  4 |  2 |    17 |       10 |
|  5 |  2 |    17 |       17 |
|  6 |  2 |   100 |        4 |
|  7 |  3 |   517 |       93 |
|  8 |  7 |     7 |        7 | // 8번째 행이 추가되었다.
+----+----+-------+----------+
8 rows in set (0.00 sec)

SELECT 를 사용하는 방법

mysql> INSERT INTO nums SELECT 9, 9, 9, 9;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

// SELECT가 결과값으로 9, 9, 9, 9 라는 상수를 반환하므로,
// INSERT INTO nums VALUES (9,9,9,9)와 같은 결과를 불러온다. 

mysql> SELECT * from nums;
+----+----+-------+----------+
| id | no | price | quantity |
+----+----+-------+----------+
|  1 |  1 |    10 |        1 |
|  2 |  1 |    10 |        2 |
|  3 |  1 |    10 |        3 |
|  4 |  2 |    17 |       10 |
|  5 |  2 |    17 |       17 |
|  6 |  2 |   100 |        4 |
|  7 |  3 |   517 |       93 |
|  8 |  7 |     7 |        7 |
|  9 |  9 |     9 |        9 | // 9번째 행이 추가되었다.
+----+----+-------+----------+
9 rows in set (0.00 sec)

nums 와 nums1 의 열 구성이 같다고 가정할때, 아래와 같은 방법으로 nums1 의 행을 복사해올수도 있다.

INSERT INTO nums SELECT * FROM nums1;

상관 서브쿼리

상관 서브쿼리란 부모 명령과 자식인 서브쿼리가 서로 연관되는 경우를 의미한다. 상관 서브쿼리를 사용함으로써 두 테이블에 걸쳐 조작할 수 있다.

EXISTS 술어를 이용하여 서브쿼리가 반환하는 결과값이 있는지 확인할 수 있다.
EXISTS 를 사용하는 경우, 서브쿼리가 반환하는 결과값이 있는지를 확인하여 있으면 참 없으면 거짓을 반환하기 때문에 서브쿼리가 스칼라값을 반환하지 않아도 된다.