🐬 20강 행 개수 구하기 - COUNT
SQL에서 대표적인 집계함수(집합을 다루는 함수)는 다음과 같다. 일반적인 함수는 하나의 값을 인수로 받는 반면 집계함수는 집합을 인수로 받는다.
COUNT SUM AVG MIN MAX
20-1 COUNT로 행 개수 구하기
COUNT는 인수로 주어진 집합의 개수를 반환한다.
SELECT COUNT(*) FROM sample51;
SELECT COUNT(*) FROM sample51 WHERE name='A';
sample51에는 총 5개의 행이 있고, COUNT집계함수에서 *은 '모든열=테이블 전체'를 의미하기 때문에 위 명령은 5를 반환한다. 이는 WHERE구를 지정해 검색 조건을 추가할 수 있다.
20-2 집계함수와 NULL 값
집계함수는 집합 안에 NULL 값이 있을 경우 이를 제외하고 처리한다.
SELECT COUNT(no), COUNT(name) FROM sample51;
20-3 DISTINCT로 중복 제거
경우에 따라 집합 안에 중복된 값이 있는지 여부가 문제될 때도 있다. 데이터가 중복되지 않는 경우를 '유일한 값을 가진다'고 표현한다.
sample51 테이블의 1행과 2행은 name열에서 중복된 값을 갖는다. SQL 명령은 이런 중복된 값을 제거하는 DISTINCT 함수를 제공한다.
20-3 집계함수에서 DISTINCT
특정 열에서 NULL값을 제외하고 중복되지 않는 데이터의 개수를 COUNT하려면 어떻게 해야할까?
WHERE구에서는 검색조건을 지정할 뿐 DISTINCT같이 중복값을 확인하는 함수는 없다. SELECT DISTINCT COUNT(name)은 COUNT가 먼저 실행되기 때문에 안된다.
바로 집계함수의 인수로 DISTINCT를 사용한 수식을 지정하면된다.
SELECT COUNT(ALL name), COUNT(DISTINCT name) FROM sample51;
🐬 21강 COUNT 이외의 집계함수
21-1 SUM - 합계 구하기
SUM함수는 수치형 집합만을 지정한다.
SELECT SUM(quantity) FROM sample51;
21-2 AVG - 평균 내기
SUM/COUNT같이 반환값으로 연산도 가능하지만 AVG 함수로 쉽게 평균값을 구할 수 있다.
SELECT AVG(quantity), SUM(quantity)/COUNT(quantity) FROM sample51;
21-3 MIN. MAX로 최소값, 최대값 구하기
MIN과 MAX 집계합수는 문자열형과 날짜시간형에도 사용할 수 있다.
SELECT MIN(quantity),MAX(quantity),MIN(name),MAX(name) FROM sample51;
🐬 22강 그룹화 - GROUP BY
22-1 GROUP BY로 그룹화
GROUP BY구를 통해 지정된 열의 값이 같은 행을 하나의 그룹으로 묶어서 집계함수로 넘겨줄 수 있다.
SELECT name FROM sample51 GROUP BY name;
결과는 DISTINCT와 같다. GROUP BY의 의미는 집계함수와 함께 사용할 때 나타난다.
SELECT name, COUNT(name), SUM(quantity) FROM sample51 GROUP BY name;
GROUP BY에 의해 name이 4개의 그룹으로 나뉘고, 각 그룹별로 COUNT와 SUM의 결과가 반환됐다.
22-2 HAVING 구로 조건 지정
SELECT 열 FROM 테이블 GROUP BY 열 HAVING 조건
WHERE구는 GROUP BY보다 처리 순서가 앞서기때문에 집계함수에서는 WHERE구의 조건식에서는 GROUP BY를 사용할 수 없다.
하지만 그룹화보다 늦게 처리되는 ORDER BY등은 문제없이 사용이 가능하다.
내부처리 순서 :
WHERE→GROUP BY→HAVING→SELECT→ORDER BY
집계한 결과에서 조건에 맞는 값을 따로 걸러낼 때는 HAVING구를 사용한다.
SELECT name,COUNT(name)FROM sample51 GROUP BY name HAVING COUNT(name)=1;
SELECT name AS n,COUNT(name) AS cn FROM sample51 GROUP BY n HAVING cn=1; 같은 명령은 SELECT구가 후순위이기 때문에 원래는 불가능하지만, MySQL에서는 융통성있게 사용이 가능하다.
22-3 복수열의 그룹화
GROUP BY에 지정한 열 이외의 열은 집계함수 없이 SELECT구에 기술할 수 없다.
GROUP BY는 그룹마다 하나의 행만을 반환하는데, 나머지 열에 대에 그룹 중 어떤 값을 반환해야할지 지정되지 않아 에러가 생기는 것이다.
SELECT MIN(no),name,SUM(quantity) FROM sample51 GROUP BY name;
위와 같이 복수의 열을 지정할 때는 집계함수로 하나의 값을 지정해줘야한다.
22-4 결과값 정렬
GROUP BY로 그룹화 한 후에도 ORDER BY구를 사용해 결과를 정렬할 수 있다.
SELECT name, COUNT(name),SUM(quantity) FROM sample51 GROUP BY name ORDER BY SUM(quantity) DESC;
🐬 23강 서브쿼리
서브쿼리는 SELECT 명령으로 괄호로 묶어 지정하는 하부의 부수적인 질의이다. 보통 SQL명령의 WHERE구에 주로 지정된다.
23-1 DELETE의 WHERE구에서 서브쿼리 사용하기
sample54 테이블에서 a값이 가장 작은 행을 삭제하려한다.
보통 아래와 같은 순서로 진행된다.
SELECT MIN(a)으로 가장 작은 값 검색 →DELETE문으로 해당 행을 삭제
괄호로 서브쿼리를 지정하면 이 SELECT명령과 DELETE명령을 결합시킬 수 있다.
DELETE FROM sample54 WHERE a=(SELECT MIN(a) FROM sample54);
❗ MySQL에서는 데이터를 추가/갱신할 경우 동일한 테이블을 서브쿼리에서 사용할 수 없기 때문에 에러가 발생한다.
아래와 같이 인라인 뷰로 임시 테이블을 만들도록 처리하면 결과를 볼 수 있다.
DELETE FROM sample54 WHERE a=(SELECT a FROM(SELECT MIN(a) AS a FROM sample54) AS x);
23-2 스칼라 값
서브쿼리를 사용할 땐 그 SELECT명령이 어떤 값을 반환하는지 주의해야한다.
-
1행, 1열 패턴(하나의 값)
SELECT MIN(a) FROM sample54; -
N행, 1열 패턴
SELECT no FROM sample54; -
1행, N열 패턴
SELECT MIN(a),MAX(no) FROM sample54; -
N행, N열 패턴
SELECT no,a FROM sample54;
1번 패턴만이 다른 패턴과 다르게 하나의 값을 반환한다. 이렇게 SELECT문이 하나의 값만 반환하는 것을 '스칼라 값을 반환한다'고 한다.
스칼라 서브쿼리는 다음과 같은 특징이 있다.
- 스칼라 값을 반환하는 서브쿼리는 = 연산자로 비교가 용이하다.
- 집계함수는
WHERE구에서는 사용할 수 없지만, 스칼라 서브쿼리는 가능하다.
23-3 SELECT구에서 서브쿼리 사용하기
SELECT (SELECT COUNT(*) FROM sample51) AS sql1,(SELECT COUNT(*) FROM sample54) AS sql2;
위처럼 SELECT구에서 스칼라 서브쿼리를 사용할 수 있다.
서브쿼리가 아닌 상부의 SELECT명령에는 FROM구가 없는데, 이는 MySQL에서는 FROM구를 생략할 수 있기 때문이다.
23-4 SET구에서 서브쿼리 사용하기
SET구에서도 서브쿼리를 사용할 수 있다.
23-5 FROM구에서 서브쿼리 사용하기
FROM구에서는 테이블 이외의 것도 지정할 수 있다. 하지만 기본적으로 테이블을 지정하는 만큼 서브쿼리가 꼭 스칼라 값을 반환하지 않아도 된다.
SELECT * FROM (SELECT * FROM sample54) sq;
위와 같은 형태를 'nasted 구조', '중첩구조' 등으로 부른다. 하지만 예제같은 경우는 의미가 없기 때문에 실무에서는 다음과 같은 형태로 이용한다.
SELECT * FROM (SELECT * FROM sample54 ORDER BY a DESC) sq WHERE ROWNUM<=2;
Oracle에는 LIMIT구가 없기 때문에 정렬 후 추출할 행을 제한할때 위와 같이 명령한다.
23-6 INSERT 명령과 서브쿼리
INSERT명령에는 ①VALUES구의 일부로 서브쿼리를 사용하는 경우와, ②VALUES구 대신 SELECT명령을 사용하는 두가지 방법이 있다.
①의 경우 서브쿼리는 스칼라 서브쿼리로 지정해야하며, 자료형도 일치해야 한다.
INSERT INTO sample541 VALUES ((SELECT COUNT(*) FROM sample51), (SELECT COUNT(*) FROM sample54));
정수형 a,b열로 이루어진 빈 테이블 sample541에 위와 같이 값을 추가할 수 있다.
②의 경우는 INSERT SELECT라고 불리는 명령이다. SELECT의 결과를 INSERT INTO로 지정한 테이블에 전부 추가하기 때문에 데이터 복사나 이동에 자주 사용된다.
INSERT INTO sample542 SELECT * FROM sample543;
🐬 24강 상관 서브쿼리
24-1 EXISTS
EXISTS 술어는 서브쿼리를 사용해 검색할 때 데이터 존재 여부를 판별해 참/거짓으로 돌려준다.
sample551과 sample552에 대해 no=no2인 행의 값을 '있음'으로 수정하려 할 때 다음과 같이 EXISTS를 사용하면 조건에 맞는 행을 갱신할 수 있다.
UPDATE sample551 SET a="있음" WHERE EXISTS (SELECT * FROM sample552 WHERE no2=no);
24-2 NOT EXISTS
NOT EXISTS를 통해 반대의 경우도 반환할 수 있다.
UPDATE sample551 SET a="없음" WHERE NOT EXISTS (SELECT * FROM sample552 WHERE no2=no);
24-3 상관 서브쿼리
UPDATE sample551 SET a="있음" WHERE EXISTS (SELECT * FROM sample552 WHERE no2=no); 예제처럼 UPDATE명령(부모)와 WHERE구의 서브쿼리(자식)이 특정 관계를 맺는 것을 '상관 서브쿼리'라고 부른다.
단순 서브쿼리와 다르게 상관 서브쿼리의 서브쿼리는 단독 실행이 안된다.
SELECT * FROM sample552 WHERE no2=no → no2불분명 에러
만약 sample551과 sample552의 열의 이름이 'no'로 동일했다면 WHERE no=no같은 조건은 제대로 동작하지 않을 것이다. 이럴 때는 sample551.no, sample552.no처럼 명시적으로 지정해주면 된다.
24-4 IN
스칼라 값끼리 비교할 때는 =연산자를 쓰지만, 집합을 비교할 때는 IN을 통해 비교한다.
위 예제는 IN을 통해 아래와 같이 바꿀 수 있다.
SELECT * FROM sample551 WHERE no IN (SELECT no2 FROM sample552);
집계함수에서는 NULL값을 무시하고 처리하지만, IN에서는 NULL=NULL을 제대로 처리할 수 없으므로 IS NULL을 사용해 비교해야한다. 또한 NOT IN은 집합 안에 NULL이 있으면 참/거짓이 아닌 0이나 NULL등을 반환한다.
