1.  단일행 서브쿼리

💡 서브쿼리란? 쿼리문 안에 또 쿼리문이 들어 있는 것

💡 서브쿼리는 다른 쿼리문안에 내장되어 있는 SELECT 문이며 연속적으로 실행할 쿼리를 하나로 합침

💡 SELECT로 할 수 있는 질문은 짧은 단문만 가능, FROM 절이 하나밖에 없어 한 테이블에 있는 정보만 조사할 수 있다.

      ➡️ 실제 작업을 할때는 복잡한 여러 단계의 질문을 한꺼번에 하는 경우가 많이 생김.

     ➡️ 복잡한 쿼리를 실행할 수 있는 방법이 바로 서브쿼리.

  📖  최대 인구수를 가진 도시를 구하는 문제

      🔎  MAX 함수의 결과는 집계한 하나의 값이고 name 필드는 도시명 여러 개여서 이 둘을 같이 출력 불가

      🔎  MAX(popu)는 최대 인구수를 집계한 것이 맞지만 name 도시중 어떤 도시가 그 인구수를 가진 것인지는 알 수 없음.

             ➡️ 즉, 왼쪽 열과 오른쪽 열이 연관성이 없음.

  📖  기계가 이 명령을 알아 들으려면 문법적으로 가능한 질문을 순서대로 해야 한다.

  1. 전체 도시의 인구 중 가장 큰 값을 조사

  2. 이 인구수를 가진 도시를 찾음

  📖  최대 인구수를 조사하고 이 수로부터 도시의 이름을 구해야 하니 두 번의 쿼리가 필요

        ➡️ 두 개의 쿼리를 하나로 묶어 실행할 때 쓰는 것이 서브쿼리

  💡 이 때 서브쿼리를 감싸는 전체 쿼리를 외부쿼리라 부름

       ➡️ 외부쿼리와 구분하고 실행 순서를 명확히 지정하기 위해 서브쿼리를 괄호로 감쌈

  📖  서브쿼리로 최대 인구수를 가진 도시명을 구함

        1. 괄호 안의 서브쿼리를 먼저 실행하여 최대 인구수를 구함.

        2. 서브쿼리를 포함하고 있는 외부쿼리를 실행하여 popu가 서브쿼리가 구한 최대 인구수와 같은 도시를 조사하여 출력.

💡 단일행 서브쿼리는 하나의 결과만 리턴하며 주로 WHERE, HAVING 등의 조건절에 사용

💡 복합 질문의 앞쪽 질문에 해당하는 값을 서브 쿼리로 조사해 놓고 외부쿼리에서 그 결과값을 사용하는 식으로 작성한다.

2.  서브쿼리 중첩

💡 서브쿼리는 독립적인 하나의 명령이기 때문에 외부쿼리와는 '다른 테이블을 읽을 수도 있음'

💡 순차적으로 실행되므로 두 쿼리문의 FROM 절에 각각 다른 테이블을 지정해도 된다.

💡 서브쿼리의 중첩 횟수에는 제약이 없어 서브쿼리내에 또 다른 서브쿼리를 포함할 수 있음

💡 리턴 값이 여러 개면 에러 처리되는데 이때는 TOP 1 (LIMIT 1) 을 넣어 첫 해당 값을 찾으면 된다.

3.  다중행 서브쿼리

💡 서브쿼리의 '결과가 하나뿐인 유형'을 단일행 서브쿼리

      ➡️ 단일값이므로 조건절에서 =, <, > 등의 비교 연산자와 함께 사용할 수 있음.

      ➡️ 비교 연산자는 필드의 값과 비교하는 것이어서 '우변이 반드시 하나의 확정된 값'이어야함.

💡 이에 비해 여러 행을 리턴하는 것이 다중행 서브쿼리

      ➡️ 단일값이 아닌 목록을 리턴하기 때문에 값끼리 비교하는 비교 연산자와 함께 사용할 수 없음

  📖  서브쿼리는 구입목록인 tOrder 테이블에서 향단이가 뭘 샀는지 조사

        ➡️ 안쪽 서브쿼리만 실행해 보면 향단이가 산 것은 대추와 사과 2개

        ➡️ = 연산자는 필드값을 두 개의 결과와 비교할 수 없어 다음 에러를 출력.

  📖 ' = '연산자로 비교하려면 조건절의 쿼리문은 반드시 하나의 단일 값을 리턴해야만 한다.

  📖  꼭 조사하려면 결과 셋 중 하나의 값만 조사해서 비교하면 됨

📖 구입한 상품 중 하나의 상품만 리턴하면 = 연산자로 비교할 수 있음.

📖 그러나 질문대로 향단이가 구입한 모든 상품의 가격을 알고 싶다면 IN 연산자를 사용

💡  IN은 = 과 달리 여러 개의 값과 비교

  📌 서브쿼리가 두 개 이상의 값을 리턴하면 결과셋을 괄호 안에 나열하며 IN 연산자는 이 값을 순서대로 비교

💡 결과가 없으면 NULL을 리턴하며 조건절에서 필드와 NULL을 비교하는 것은 문법적으로 가능.

      ➡️ 아무 결과셋도 출력하지 않을 뿐 에러는 아니다.

4.  다중열 서브쿼리

💡 단일행, 다중행 서브쿼리는 '결과셋의 컬럼이 하나'밖에 없으며 그래서 특정값과 비교할 수 있음.

💡 다중열 서브쿼리는 결과셋의 컬럼이 여러 개이며 한꺼번에 여러 값과 비교

  📌  윤봉길과 같은 부서에 근무하는 같은 성별의 직원 목록을 조사

  📌  먼저 윤봉길이 어느 부서에 근무하고 성별이 무엇인지 조사해야 하며 그 결과로부터 조건에 맞는 직원을 찾는다

  📌  WHERE 절에 비교 대상 필드를 괄호 안에 (depart, gender) 적으면 서브쿼리의 컬럼과 1:1로 비교하여 두 필드가 일치하는 레코드를 검색

  📌  단 일괄 비교가 성립하려면 비교 대상과 서브쿼리의 컬럼 개수는 반드시 일치해야 함.

💡  서브쿼리가 다중열이면서 다중행인 결과셋을 리턴하면 복수 레코드의 복수 필드를 한꺼번에 비교 가능

  📌  부서별 최고 월급자의 목록을 조사. 여러 개의 부서가 있고 각 부서명과 최고 월급을 비교

1. DELETE

👩🏻‍💻 레코드를 삭제할 때 사용한다.

👩🏻‍💻 삭제는 항상 레코드 단위로 수행하므로 필드에 대한 지정은 없음.

👩🏻‍💻 삭제 동작은 특정 조건에 맞는 레코드를 찾아 제거하는 경우가 대부분이어서 WHERE 절이 항상 뒤따라 온다.

2. 삭제 안전 장치

👩🏻‍💻  삭제시 문제가 되는 부분은  1) 조건절을 빼거나 2) 잘못된 조건을 사용하는 경우

👩🏻‍💻  조회만 하는 SELECT 명령은 테이블을 변경하지 않아 실수해도 조건을 바꿔 다시 조회하면 됨.

       INSERT 명령은 테이블을 변경하지만 혹시라도 잘못 삽입했으면 다시 지우면 되어서 그다지 위험하지 않음

       하지만 잘못 내린 DELETE 명령은 즉시처리해 버림.

👩🏻‍💻  DELETE문은 특정 레코드를 삭제하기 위해 WHERE 절과 함께 사용하지만 실수로 WHERE 절을 생략해 버리면 모든 데이터가 삭제됨

📌  만일 월급이 300 초과인 직원을 삭제한다면 다음 절차대로 작업

3. TRUNCATE

👩🏻‍💻  테이블 초기화 할 때 쓰는 명령어

👩🏻‍💻  DELETE 명령은 수십만건의 레코드를 일일이 지우게 되면 서버에 부하가 생긴다.

       ➡️ 이 경우 TRUNCATE 명령을 사용하는 것이 효율적

       ➡️ 물리적인 기억 장소를 깨끗이 비우고 임시 영역에 로그도 남기지 않아 훨씬 빠르다

​

​

📌  경기도 지역의 도시만으로 tSudo 테이블을 생성

​

​

​

1. GROUP BY

🍋 기준이 되는 필드를 뒤에 적어주면 기준 필드가 같은 레코드를 모아 통계값을 구함

🍋 GROUP BY 구문은 그룹핑을 해 줄 뿐이며 어떤 통계를 낼 것인가는 필드 목록의 집계 함수에 따라 달라짐

🍋 여러 개의 집계함수를 동시에 사용 가능

📌  부서별 월급 평균을 알고 싶어 부서마다 AVG 함수를 호출하게 되면 부서 목록을 만들고 각 부서마다 명령을 일일이 실행해야 하고 결과도 따로 출력되어 보기도 불편하다. 또한, 집계함수와 일반필드를 같이 적을 수 없어 필드 목록에 부서명을 한 번 더 적어야 한다.

💡 이 경우에 필요한 구문이 GROUP BY. 기준 필드는 집계 함수와 같이 쓸 수 있어서 목록도 보기 좋게 출력 할 수 있음.

📌  부서별 월급 평균을 구하려면 depart 필드 기준으로 그룹핑

    1)  기준 필드순으로 정렬하여 같은 그룹끼리 구분

    2)  통계 대상 필드를 순서대로 읽어 집계를 구함

2. 기준 필드

🍋 GROUP BY의 기준 필드는 중복 값이 있을 때만 의미있다.

     ➡️ 레코드별로 고유한 값을 가지는 필드는 그룹핑 기준으로 부적합(예 : 아이디)

     ➡️ 한 부서에서 여러 직원이 소속되어 있고 부서가 같은 직원이 많기 때문에 부서별 집계 가능.

     ➡️ 성별도 중복값이어서 그룹핑 필드로 적합

🔖  중복 값을 가지는 필드만 그룹핑의 기준이 되는 것은 아니며 임의의 필드를 기준으로 그룹핑 할 수 있다.

🔖  기준 필드를 콤마로 구분하여 두 개 이상도 쓸 수 있다.

      ➡️ 이 경우 첫 번째 기준으로 그룹을 나누고 그 그룹 내에서 다시 두 번째 기준으로 그룹을 나눈다.

🔖  기준 필드의 순서는 그룹핑 과정에 영향을 주지만 결과에는 영향을 미치지 않는다.

      ➡️ 순서는 ORDER BY로 조정 가능

3.  GROUP BY의 필드 목록

⚡️  <그룹핑 쿼리의 기본 형식>

🍋 GROUP BY 절이 있으면 필드 목록에는 기준 필드나 집계 함수만 와야 한다.

🍋 필드 목록의 제일 처음에는 통상 기준 필드를 출력하여 어떤 그룹에 대한 통계인지 표시

      ➡️ 기준 필드를 빼고 집계 함수만으로 쿼리를 구성하면, 계산은 똑바로 됐지만 각 행이 어떤 부서에 대한 통계치인지 알아 볼 수 없다.

🍋  집계 함수는 다중 값이 있어야 계산할 수 있으므로 그룹핑을 할 때만 유용

       ➡️ GROUP BY 절 없이 일반 필드와 집계 함수를 같이 출력하면 의미가 없다

  📌  레코드별로 고유한 부서명과 통계에 의해 계산하는 월급 총합은 같이 출력할 수 있는 값이 아니라서

        이 명령은 다음 둘 중에 하나로 수정하는게 적당.

 📌  뒤에 GROUP BY 절을 붙여 부서별로 그룹핑하면 기준 필드인 부서명도 출력할 수 있고

       부서의 월급 총합을 구하는 SUM 함수도 사용할 수 있다.

4. HAVING

🍋 HAVING은 GROUP BY 다음에 오며 통계 결과 중 출력할 그룹의 조건을 지정 (즉 HAVING은 GROUP BY 문의 조건절)

  📌  부서별 평균 월급을 출력하는 명령은 별다른 조건이 없으면 모든 부서의 평균 월급을 출력.

        평균 월급이 340을 넘는 부서만 출력하고 싶다면 GROUP BY 다음에 HAVIING 절을 추가.

🍋 출력 순서를 지정할 때는 ORDER BY 절을 뒤에 붙인다.

🍋 GROUP BY 다음에 HAVING이 오고 ORDER BY는 항상 제일 마지막에 위치한다.

🍋 WHERE 와 HAVING은 적용 시점과 제한 대상이 다르다.

🍋 WHERE 절은 GROUP BY 앞에 나타나며 통계 대상 레코드의 조건을 제한한다.

(1) COUNT() : 행의 개수를 센다

​

📍 COUNT는 개수를 조사할 필드명을 전달하는데 * 지정하면 필드에 상관없이 조건에 맞는 레코드 개수를 리턴

📍 WHERE 절을 붙이면 조건에 맞는 레코드의 개수를 구한다.

​

​

​

📍 집계는 모든 레코드의 값을 참고하여 하나의 값을 구하는 것이어서 결과셋은 목록이 아닌 딱 하나의 값

       ➡️ 집계하는 말 자체가 다중값으로 부터 단일 값을 산출한다는 의미.

📍 조건에 맞는 레코드가 없어도 결과 값은 역시 하나

​

📍 특정 필드의 개수를 구할때는 인수로 필드명을 적는다.

      ➡️ 지정한 필드 값이 존재하는 레코드의 개수를 구함

📍 중복 부서를 제외하고 부서의 종류가 몇 개인지 알고 싶으면 필드명 앞에 DISTINCT 키워드를 붙인다.

SELECT COUNT(DISTINCT depart) FROM tStaff;

📍 COUNT 함수는 '필드값이 제대로 들어 있는 레코드의 개수만 구하며' 필드값이 NULL인 레코드는 개수에서 제외.

📍 name이나 depart는 NULL이 없어 전체 직원수와 같지만, score 필드는 NULL 값이 있음.

SELECT COUNT(score) FROM tStaff; 
SELECT COUNT(*) - COUNT(score) FROM tStaff; 
SELECT COUNT(*) FROM tStaff WHERE score IS NULL;

​

(2) SUM(), AVG(), MIN(), MAX()

SELECT SUM(popu), AVG(popu) FROM tCity; // 도시 목록에서 인구의 총합과 평균을 구함. 
SELECT MIN(area), MAX(area) FROM tCity; // 모든 도시의 area 필드를 조사하여 가장 작은 값과 가장 큰 값을 찾음.

📍 문자열끼리는 더할 수 없어 총합을 계산할 수 없고 평균도 의미가 없다.

📍 but! 문자열이나 날짜끼리는 사전순으로 비교할 수 있고, 날짜에 대해서는 MIN, MAX 함수 사용 가능하다.

📍 집계함수와 일반필드은 같이 사용하면 안된다.

SELECT SUM(name) FROM tStaff; 
SELECT MIN(name) FROM tStaff; 
SELECT MAX(popu), name FROM tCity; (x) 
-> SELECT popu, name FROM tCity ORDER BY popu DESC LIMIT 1;

​

(3) 집계 함수와 NULL

​

📍 NULL은 값을 알수 없는 특수한 상태로 모든 집계 함수는 NULL을 무시하고 통계를 계산한다.

📍 단, 예외적으로 레코드 개수를 세는 COUNT(*)는 NULL 도 포함하지만, 인수로 필드를 지정하면 NULL을 세지않는다.

SELECT AVG(salary) FROM tStaff; 
SELECT SUM(salary)/COUNT(*) FROM tStaff; // 평균은 총합을 개수로 나누어서 구함 위의 문장과 동일

📍 score의 경우에는 다른 값이 나온다.

      ➡️ AVG 함수는 NULL 값을 제외하고 계산을 하지만, COUNT(*)의 경우 NULL 값도 포함

      ➡️ 시스템 규칙에 따라 NULL이 0을 나타내는 것일 수 있음.

SELECT AVG(score) FROM tStaff; 
SELECT SUM(score)/COUNT(*) FROM tStaff; 
SELECT SUM(score)/COUNT(score) FROM tStaff;

📍 COUNT(*)는 없다는 뜻의 0을 리턴, 다른 집계함수는 계산 대상이 없어서 존재하지 않는 0이 아니라 NULL을 반환