0. mysql 자동완성 끄기
mySQL workbench를 쓰면 글자 뒤에 자동완성탭이 뜨는 경우가 있는데
쓸모도 없고 도움 1도 안되면서 거슬리기만 하다.
이렇게 애매한 기능은 아에 안 넣느니만 못한데 말이다.
edit -> preferences 옵션의 Query editor에 들어가서
위 붉은색 박스 항목을 보면 체크표시가 기본으로 되어 있을텐데
이를 비 활성화 하자.
1. 형 변환 함수
cast(), converter()
CAST('1' AS UNSIGNED)
CAST(2 AS CHAR(1))
CONVERT('1', UNSIGNED)
CONVERT(2, CHAR(1))데이터 타입 변환에 사용
2. 제어흐름 함수
if
SELECT
IF(12500 * 450 > 5000000, '초과달성', '미달성');
결측치 검지 함수로 ifnull(), nullif()가 존재함
SELECT
ifnull(1,0),
ifnull(NULL, 0),
ifnull(1/0, "ok"),
nullif(12*10, 120),
nullif(12*10, 1200);
조건이 여러개 일시에는 case()
SELECT
CASE WHEN 12500*450 > 500000 THEN '초과달성'
WHEN 12500*450 > 400000 THEN '달성'
ELSE '미달성' END;
3. 집계함수
count,sum,avg,max,min,stddev
# 고객 테이블에서 고객번호, 도시, 지역의 개수 조회
SELECT
count(고객번호) as 고객번호개수,
count(도시) as '총 도시 종류',
count(지역) as '총 지역 종류'
FROM 고객;
# 고객 테이블에서 '서울특별시' 고객에 대한
# 마일리지 합/평균/최소/최대 조회
SELECT
sum(마일리지) as 합,
avg(마일리지) as 평균,
min(마일리지) as 최소,
max(마일리지) as 최대
FROM 고객
WHERE 도시 = '서울특별시';
GROUP BY
# 고객 테이블에서 도시별 고객수
# 해당 도시 고객들의 평균마일리지
SELECT 도시,
COUNT(*) AS 고객수,
AVG(마일리지) AS 평균마일리지
FROM 고객
GROUP BY 도시;
# 담당자 직위별로 묶고,
# 같은 담당자에 대해서는 도시별로 묶어서
# 집계 결과(고객수, 평균 마일리지)
# 이때 담당자 직위, 도시 순으로 정렬
SELECT 담당자직위, 도시,
COUNT(*) AS '고객 수',
AVG(마일리지) AS '평균 마일리지'
FROM 고객
GROUP BY 담당자직위, 도시
ORDER BY 담당자직위, 도시;
Having
GROUP BY절에 추가 조건을 넣을 때 사용
# 고객 테이블에서 도시별로 그룹을 묶어서
# 고객 수와 평균 마일리지를 구하고,
# 이 중 고객 수가 10명 이상인 레코드를 걸러내시오
SELECT 도시,
COUNT(*) AS 고객수,
AVG(마일리지) AS '평균 마일리지'
FROM 고객
GROUP BY 도시
HAVING 고객수 >= 10;
# 고객번호가 'T'로 시작하는 고객에 대해
# 도시별로 묶어서 고객의 마일리지 합을 구하시오
# 이때 마일리지 합이 1000 이상인 레코드만 보이시오
SELECT 도시,
SUM(마일리지) AS 마일리지총계
FROM 고객
WHERE 고객번호 LIKE 'T%'
GROUP BY 도시
HAVING 마일리지총계 >= 1000;
WITH ROLLUP
그룹별 소계와 전체 총계를 한번에 확인하고 싶을 때
# 도시별로 고객수, 평균 마일리지 조회
# 이때 맨 마지막 행에
# 전체 고객수, 전체 고객에 대한평균 마일리지
# 조회하기
SELECT 도시,
COUNT(*) AS 고객수,
AVG(마일리지) AS 고객마일리지
FROM 고객
GROUP BY 도시
WITH ROLLUP;
# 담당자직위에 '마케팅'이 들어가 있는
# 고객에 대하여 고객(담당자직위, 도시)별
# 고객수를 보이시오.
# 담당자직위별 고객 수와 전체 고객 수도
# 함께 볼 수 있도록 조회하시오
SELECT 담당자직위, 도시,
COUNT(*) AS 고객수
FROM 고객
WHERE 담당자직위 LIKE '%마케팅%'
GROUP BY 담당자직위, 도시
WITH ROLLUP;
grouping()
WITH ROLLUP의 결과로 나온 NULL에 대해서는 1을 반환
그렇지 않은 NULL은 0으로 처리
-> 발생한 NULL이 WITH ROLLUP으로 발생한 것인지?
-> 원래 있던 NULL인지 확인하는 구문
# 담당자 직위가 '대표이사'인 고객에 대해
# 지역별로 묶어서 고객수를 보이고
# 전채 고객 수도 함께 보이면서
# NULL 출처도 구분하기
SELECT 지역,
COUNT(*) AS 고객수,
GROUPING(지역) AS 'NULL출처'
FROM 고객
WHERE 담당자직위 = '대표 이사'
GROUP BY 지역
WITH ROLLUP;
group_concat()
각 행에 있는 값을 결합함
# group_concat를 사용해
# 사원 테이블에 들어있는 이름을
# 한 행에 나열
SELECT group_concat(이름)
FROM 사원;
# 고객 테이블에 있는 지역을
# 한 행에 나열하되
# 중복 제거
SELECT DISTINCT group_concat(지역) as 결과
FROM 고객;
# 고객 테이블에서 도시별로
# 고객 회사명 나열
SELECT 도시,
group_concat(고객회사명) as 회사목록
FROM 고객
GROUP BY 도시;
4. 점검문제
# 고객 테이블의 도시 컬럼에는
# 몇개의 도시가 있나요?
# 도시 수와 중복값을 제외한 도시 수를 보이시오
SELECT COUNT(도시) AS 도시개수,
COUNT(DISTINCT 도시) AS 중복제외
FROM 고객;
# 주문 테이블에서 주문 년도별로
# 주문 건수 조회
SELECT
YEAR(주문일) AS 주문년도,
COUNT(주문번호) AS 주문건수
FROM 주문
GROUP BY YEAR(주문일);
# 주문 테이블에서 (주문년도, 분기)별 주문 건수,
# 주문 년도별 주문건수
# 전체 주문건수를 한번에 조회
SELECT
YEAR(주문일) AS 주문년도,
quarter(주문일) AS 분기,
COUNT(주문번호) AS 주문건수
FROM 주문
GROUP BY 주문년도, 분기
WITH ROLLUP;
# 주문 테이블에서 요청일보다
# 발송일이 늦어진 주문내역이
# 별로 몇건씩인지 요약하여 조회
# 이때 주문 월 순서대로 정렬
SELECT MONTH(주문일) AS 주문월,
COUNT(CASE WHEN 요청일 < 발송일 THEN 1 END)
AS 늦은주문건수
FROM 주문
GROUP BY 주문월
ORDER BY 주문월;
# 제품 테이블에서 '아이스크림'
# 제품에 대한 제품명 별로 재고 합을 구하시오
SELECT 제품명, SUM(재고) AS 재고합
FROM 제품
WHERE 제품명 LIKE '%아이스크림%'
GROUP BY 제품명;
# 고객 테이블에서 마일리지가 50,000점 이상
# 인 고객은 VIP고객
# 나머지 고객은 일반고객 으로 구분하고
# 구분된 고객별로 고객수 / 평균마일리지 조회
SELECT
IF(마일리지 >= 50000, 'VIP고객', '일반고객')
AS 고객구분,
COUNT(고객번호) AS 고객수,
AVG(마일리지) AS 평균마일리지
FROM 고객
GROUP BY 고객구분
5. join 함수
2개 이상의 테이블을 연결하여 데이터를 검색하는 방법
JOIN의 조인 종류는 CROSS, INNER, OUTER 3가지로 결정가능 (Default : INNER)
JOIN절에 대한 조건은 ON절에 작성하고 나머지 조건은
WHERE절에 작성함
크로스조인
# 사원 테이블과 부서 테이블을 크로스 종니하여
# '배재용'사원에 대한 정보
# 이름, 사원-부서번호, 부서-부서번호, 부서명)을 출력
SELECT 사원.부서번호,
부서.부서번호,
이름, 부서명
FROM 부서
CROSS JOIN 사원
WHERE 사원.이름 = '배재용';
이너조인
# 고객 회사들이 주문한 주문 건수를 정렬하여 조회
# 이때 조회할 정보 : 고객번호, 담당자명, 고객회사명, 주문건수
SELECT 고객.고객번호, 고객.담당자명, 고객.고객회사명,
COUNT(주문.주문일) AS 주문건수
FROM 고객
INNER JOIN 주문
ON 고객.고객번호 = 주문.고객번호
GROUP BY 고객.고객번호, 고객.담당자명, 고객.고객회사명
ORDER BY 주문건수 DESC;
# '이소미'사원의 사원번호, 직위, 부서번호, 부서명 조회
SELECT 사원.사원번호, 사원.직위, 부서.부서번호, 부서.부서명
FROM 사원
INNER JOIN 부서
ON 부서.부서번호 = 사원.부서번호
WHERE 사원.이름 = '이소미';
# 고객별(고객번호, 담당자명, 고객회사명)로
# 주문금액 합을 조회, 이때 합이 높은 순으로 정렬
SELECT 고객.고객번호, 고객.담당자명, 고객.고객회사명,
SUM(주문세부.주문수량 * 주문세부.단가) AS 총주문금액
FROM 고객
INNER JOIN 주문
ON 고객.고객번호 = 주문.고객번호
JOIN 주문세부
ON 주문.주문번호 = 주문세부.주문번호
GROUP BY 고객.고객번호, 고객.담당자명, 고객.고객회사명
ORDER BY 총주문금액 DESC;
# 고객 테이블에서 담당자가 '이은광' 인 경우의
# 고객번호, 고객회사명, 담당자명, 마일리지, 마일리지등급 조회
SELECT 고객.고객번호, 고객.고객회사명, 고객.담당자명,
고객.마일리지, 마일리지등급.등급명
FROM 고객
INNER JOIN 마일리지등급
ON 고객.마일리지 >= 마일리지등급.하한마일리지
AND 고객.마일리지 < 마일리지등급.상한마일리지
WHERE 고객.담당자명 = '이은광';
외부조인 (LEFT / RIGHT)
# 고객테이블에서 담당자가 '이은광'인 경우
# 고객번호, 고객회사명, 담당자명, 마일리지, 마일리지등급 조회
SELECT 고객.고객번호, 고객.고객회사명, 고객.담당자명,
고객.마일리지, 마일리지등급.등급명
FROM 고객
LEFT JOIN 마일리지등급
ON 고객.마일리지 >= 마일리지등급.하한마일리지
AND 고객.마일리지 < 마일리지등급.상한마일리지
WHERE 고객.담당자명 = '이은광';
셀프 조인
# 사원번호, 이름, 상사의 사원번호, 상사의 이름을 조회
SELECT A.사원번호,
A.이름 as 사원이름,
B.사원번호,
B.이름 as 상사이름
FROM 사원 A
INNER JOIN 사원 B
ON A.상사번호 = B.사원번호;
6. 점검문제
SELECT 제품.제품명,
SUM(주문세부.주문수량) AS 주문수량합,
SUM(주문세부.주문수량 * 주문세부.단가) AS 주문금액합
FROM 제품
INNER JOIN 주문세부
ON 제품.제품번호 = 주문세부.제품번호
GROUP BY 제품.제품명
ORDER BY 제품.제품명;
# 주문, 주문세부, 제품 테이블을 활용해
# 제품명에 '아이스크림'이 포함된 제품에 대하여
# 주문년도별로 주문수량합을 계산
SELECT YEAR(주문.주문일) AS 주문년도,
제품.제품명,
SUM(주문세부.주문수량) AS 주문수량합
FROM 제품
INNER JOIN 주문세부
ON 제품.제품번호 = 주문세부.제품번호
INNER JOIN 주문
ON 주문세부.주문번호 = 주문.주문번호
WHERE 제품.제품명 LIKE '%아이스크림%'
GROUP BY 주문년도, 제품.제품명;
# 제품, 주문세부 테이블을 활용하여
# 제품명 별로 주문수량합을 조회
SELECT 제품.제품명,
SUM(주문세부.주문수량) AS 주문수량합
FROM 제품
INNER JOIN 주문세부
ON 제품.제품번호 = 주문세부.제품번호
GROUP BY 제품.제품명
ORDER BY 제품.제품명;
# 고객 회사 중 마일리지 등급이 'A'인
# 고객 정보(고객번호, 담당자명, 고객회사명, 등급명, 마일리지) 조회
SELECT 고객.고객번호, 고객.담당자명, 고객.고객회사명,
마일리지등급.등급명, 고객.마일리지
FROM 고객
INNER JOIN 마일리지등급
ON 고객.마일리지 >= 마일리지등급.하한마일리지
AND 고객.마일리지 < 마일리지등급.상한마일리지
WHERE 마일리지등급.등급명 = 'A';
자율차 공부중