MySQL - 그룹함수, 서브쿼리, DML, 비교조건

그룹 함수

다수의 행을 하나로 집합하여 그룹 당 하나의 결과를 생성

↔ 단일행 함수 : 한 줄 한 줄 마다 아웃풋을 처리 (예, UCASE() - 문자를 모두 대문자로 변환)

• 그룹함수는 기본적으로 널값을 빼고 계산
  • ifnull(컬럼1, 컬럼2) - 컬럼1 값이 null이면 컬럼2 값으로 대체. 함수 인자로 사용.
• 그룹함수의 중첩도 가능 (예, MAX(AVG(salary)) )

그룹함수의 종류:

• AVG - 평균
• COUNT - 행의 수 반환
  • COUNT(*) : 테이블 전체 행의 수
  • COUNT(expr) : null이 아닌 행 수를 반환
  • COUNT(DISTINCT expr) : 중복되지 않는 널이 아닌 값의 수를 반환
• MAX - 최대값
• MIN - 최소값
• STDDEV - 표준편차
• SUM - 합계
• VARIANCE - 분산

SELECT 그룹함수1(열이름), 그룹함수2(열이름), 그룹함수3(열이름), ...
FROM 테이블명;

GROUP BY

데이터 그룹 생성 구문. 테이블을 더 작은 단위의 그룹으로 나누어서 반환

SELECT 열이름, 그룹함수(열이름)
FROM 테이블명
GROUP BY 열이름

select department_id, job_id, sum(salary)
from employees
group by department_id, job_id;

select avg(salary)
from employees
group by department_id
order by avg(salary) desc;

• SELECT 절에 있는 열은 그룹함수 안에 있는 열을 제외하고 모두 GROUP BY 절에 포함되어야 한다.

  • 여러 열에 GROUP BY를 사용하여 그룹 내 그룹을 만들 수 있다. 정렬 순서를 앞에 오는 열부터.

• GROUP BY 열을 반드시 SELECT 절에 포함시키지 않아도 된다. (그룹함수 열만 표시)

• ORDER BY 절에 그룹 함수를 사용할 수도 있다.

• WHERE 절을 사용하여 그룹을 제한할 수 없고, WHERE 절에서 그룹 함수를 사용할 수 없다.

  ⇒ 대신 HAVING 절 사용

HAVING

• 그룹을 제한하기 위해 WHERE 대신 사용 → HAVING 절과 일치하는 그룹 표시
• 그룹 함수 적용 가능
• 순서 상 HAVING 절은 WHERE 절 다음에 넣는다.

select department_id, max(salary)
from employees
group by department_id
having max(salary) > 10000;

# department_id와 max(salary) 열이 department_id 열을 기준으로 그룹화되고
# salary가 10000 이상인 department_id의 행만 표시된다 

서브쿼리

두 개 이상의 질의를 해결할 때 사용, 두 질의를 결합하여 한 질의(서브쿼리)를 다른 질의(메인쿼리) 내부에 포함시켜서 해결한다. WHERE 절 조건으로 주로 사용됨

SELECT 열이름1, 열이름2, ...  #메인쿼리
FROM 테이블명
WHERE 조건문 (SELECT 열이름 FROM 테이블명 WHERE 조건문)  #서브쿼리

• 서브쿼리는 괄호로 묶어준다
• 비교 조건의 오른쪽에 서브쿼리를 넣는다
• ORDER BY 절은 특별한 경우가 아니면 서브쿼리에서 필요로 하지 않으며, 전체쿼리에 한번만 사용하면 된다.
• 단일행 서브 쿼리에는 단일 행 연산자(>, <, =,… 등)를 사용하고, 다중 행 서브 쿼리에는 다중 행 연산자를 사용한다.)
• 다중행 서브쿼리: 서브쿼리의 결과 행이 하나가 아닌 여러개일 경우 아래 비교 연산자를 사용
  • IN : 목록에 있는 임의의 멤버와 동일
  • ANY : 값을 서브 쿼리에 의해 반환된 각 값과 비교
  • ALL : 값을 서브 쿼리에 의해 반환된 모든 값과 비교

데이터 조작

DDL : 데이터 정의어. CREATE TABLE, CREATE VIEW, CREATE PROCEDURE, CREATE FUNCTION

DML : 데이터 조작어. INSERT, UPDATE, DELETE

⇒ 이후 COMMIT을 꼭 하자! DDL과 DCL은 자동커밋이 실행되지만 DML은 그렇지 않음

DCL : 데이터 제어어. GRANT, REVOKE

DML 문의 실행 경우:

• 테이블에 새 행 추가
• 테이블의 기존 행 수정
• 테이블에서 기존 행 삭제

INSERT : 새 행 삽입

INSERT INTO 테이블명(열이름1, 열이름2, ...)
VALUES (열이름1 값, 열이름2 값, ...)

insert into test1 values('aaa', '111', 'nameA');  #테이블의 모든 컬럼에 값을 넣겠다고 선언.
insert into test1(name, pwd, id) values('nameB', '222', 'bbb');  #테이블의 지정 컬럼에 값을 넣겠다고 선언.
insert into test1(id, pwd) values('ccc', '333');  #name에는 자동으로 null 입력
insert into test1 values('ddd', '444', null);  #컬럼 이름을 명시하지 않은 경우 모든 값을 입력해줘야 하므로 null을 직접 입력

• SYSDATE : 현재 날짜 및 시간 기록

• 다른 테이블에서 행을 복사하고 싶을 경우 서브쿼리를 사용

  • VALUES 절은 사용 안함

  #테이블 복사 생성
  create table emp
  as
  select * from employees where 1=0;  #'1=0': 불가능한 조건. 행은 복사하지 말고 테이블의 구조만 복사하라는 의미
    
  #행 복사 삽입
  insert into emp
  select * from employees where job_id like '%REP%'
  

UPDATE : 기존 행 수정

UPDATE 테이블명
SET 열이름1 = 값1, 열이름2 = 값2, ...
WHERE 조건문  #조건이 없으면 컬럼의 모든 값을 지정된 값으로 통일시키게 된다!

update test1 set name='nameC' where id='ccc';
update test1 set name='nameD', hire_date=sysdate() where id='ddd';
update test1 set hire_date=date('2020-05-05') where id='ddd';

• WHERE 절을 사용해서 행을 하나만 지정할 수도 있고, 여러 행을 동시에 변경할 수도 있다
• 서브쿼리를 사용할 수도 있고, 한번에 여러 열을 수정할 수도 있음
  • INSERT 와 마찬가지로 서브쿼리를 통해 다른 테이블의 값을 기반으로 업데이트 할 수도 있다

DELETE : 기존 행 제거

DELETE FROM 테이블명 WHERE 조건문;

delete from test1 where id='ccc';
delete from test1;  # 조건문이 없으면 모든 행이 삭제된다!

비교조건

IF 내장 함수

#if(수식, 참일때실행문, 거짓일때실행문)
SELECT IF(hire_date like '2005%', last_name, null)
FROM employees;

#ifnull(컬럼/값, 널일때실행문)
select last_name, commission_pct, 12*salary+12*salary*commission_pct  #커미션이 없는 경우는 값이 널이 되어버린다
from employees;

select last_name, commission_pct, 12*salary+12*salary*ifnull(commission_pct, 0)  #커미션이 없는 경우 0을 곱한다
from employees;

#nullif(exp1, exp2): exp1과 exp2가 같으면 null, 같지 않으면 exp1 반환
select last_name, length(last_name), first_name, length(first_name), nullif(length(last_name), length(first_name))
from employees;

CASE 내장 함수: 비교할 조건이 여러개 일 때 사용

case exp
	when 값1 then 실행문
    when 값2 then 실행문
    when 값3 then 실행문
    else 실행문
end as '컬럼별칭'

SELECT department_id,
case department_id
	when 10 then '10번 부서'
    when 20 then '20번 부서'
    when 30 then '30번 부서'
	else '이외 부서'
end as '부서명'
from employees;