실습환경 만들기
외부접속, cmd사용
mysql -h "database-1.ㅇㅇㅇㅇㅇㅇㅇㅇ.ap-northeast-1.rds.amazonaws.com" -P 3306 -u admin -p비밀번호입력
show databases;
use zerobase;
show tables;※
환경설정, 로컬환경 cmd접속 X, police_station, crime_status이 empty set상태임
show databases;
use zerobase;
show tables;About Subquery
하나의 SQL 문안에포함되어있는또다른 SQL 문을말한다.
메인쿼리가서브쿼리를포함하는종속적인관계이다.
- 서브쿼리는메인쿼리의칼럼사용가능
- 메인쿼리는서브쿼리의칼럼사용불가
■Subquery 사용시주의
- Subquery 는괄호로묶어서사용
- 단일행혹은복수행비교연산자와함께사용가능
- subquery 에서는 order by 를사용X
■Subquery 종류
1. 스칼라서브쿼리 (Scalar Subquery) - SELECT 절에사용
2. 인라인뷰 (Inline View) - FROM 절에사용
3. 중첩서브쿼리 (Nested Subquery) - WHERE 절에사용
1. 스칼라서브쿼리 (Scalar Subquery)
SELECT 절에서사용하는서브쿼리. 결과는하나의 Column 이어야한다.
SELECT column1, (SELECT column2 FROM table2 WHERE condition)
FROM table1
WHERE condition;스칼라서브쿼리 (Scalar Subquery) 예제
서울 은평경찰서의 강도검거건수와 서울시경찰서전체의평균강도검거건수를조회
#1. 은평강도검거: 1건 => 메인쿼리
SELECT case_number
FROM crime_status
WHERE police_station LIKE '은평' AND crime_type LIKE '강도' AND status_type LIKE '검거';#2. 서울시 경찰서 전체의 평균 강도검거건수, avg(case_number): 4.1935건 => 서브쿼리
서브쿼리: 메인 SELECT문에서 하나의 컬럼(또다른 select문, 서브쿼리)으로 들어감
SELECT avg(case_number)
FROM crime_status
WHERE crime_type LIKE '강도' AND status_type LIKE '검거';
=> select문에서 바로 집계함수 사용되어 연산되고 또 이것이 컬럼 그대로 사용됨
#1번과 2번 쿼리를 하나로 합치는 것이 '서브쿼리'
정답
SELECT case_number,
(SELECT avg(case_number)
FROM crime_status
WHERE crime_type LIKE '강도' AND status_type LIKE '검거') as avg
FROM crime_status
WHERE police_station LIKE '은평' AND crime_type LIKE '강도' AND status_type LIKE '검거';
case_number | avg |
+-------------+--------+
| 1 | 4.1935 |2. 인라인뷰 (Inline View)
<인라인뷰 특징 3가지>
- FROM 절에사용하는서브쿼리.
- 스칼라 서브쿼리와 달리 인라인뷰는 테이블 형태로 나와야 하고 여기서 나온 컬럼을 이용해서
조인을 통해 결과값을 도출해준다. - 메인쿼리에서는인라인뷰에서조회한 Column 만사용가능하다.
SELECT a.column, b.column
FROM table1 a, (SELECT column1, column2 FROM table2) b
WHERE condition;인라인뷰 (Inline View) 예제
경찰서별로 가장 많이 발생한 범죄건수와 범죄유형을 조회
(=> 경찰서별 : GROUP BY police_station)
'원래의 테이블'과 '경찰서별 범죄건수가 가장 큰 값 테이블(인라인뷰)'을 조인해줌
(FROM절 기술)
=> 내가 보고 싶은 값이 무엇이고 이를 구현하기 위해 어떤 쿼리를 작성하면 되는지
천천히 생각해봐야 함.
- 경찰서별 가장 많이 발생한 범죄건수
=> 많이 발생한max, FROM절에 인라인 뷰*로 사용될 구문
- 인라인뷰: FROM절에서 사용하는 서브쿼리
SELECT police_station, max(case_number)
FROM crime_status
WHERE status_type LIKE '발생'
GROUP BY police_station;
=> 결과값이 테이블 형태로 출력
(인라인뷰를 이용하기 위해선 결과값이 테이블 형태로 나와있어야 하고
테이블 형태로 나온 결과값을 이용해서 조인 기능도 이용하고 이 결과값을 활용할 수 있다.)
police_station | max(case_number) |
+----------------+------------------+
| 중부 | 997 |
| 종로 | 964 |
| 남대문 | 699 |
※ 출력오류예시: select문에서 group by는 썼는데 '집계함수 없이' 위의 구문을 실행하면 누락된 결과를 가져온다.
SELECT police_station, crime_type, status_type, case_number
FROM crime_status
WHERE status_type LIKE '발생'
GROUP BY police_station;
=> 예를 들어 위의 구문에서 max를 빼고 case_number를 가져오면
(무엇을 가져왔는지 알기 위해 crime_type, status_type 추가해서봄)
각 경찰서가 1번만 출력되기 때문에 crime_type이'살인 발생'한 건에 대해서만 출력됨
(강도 발생, 강간추행발생, 절도 발생, 폭력 발생 모두 미출력, 결국 출력오류임)
결과값 일부
| police_station | crime_type | status_type | case_number |
+----------------+------------+-------------+-------------+
| 중부 | 살인 | 발생 | 1 |
| 종로 | 살인 | 발생 | 9 |
| 남대문 | 살인 | 발생 | 1 |
| 서대문 | 살인 | 발생 | 3 |
#2. 정답
SELECT c.police_station, c.crime_type, c.case_number
FROM crime_status c,
(SELECT police_station, max(case_number) count
FROM crime_status
WHERE status_type LIKE '발생'
GROUP BY police_station) m
WHERE c.police_station = m.police_station
AND c.case_number = m.count;
=> SELECT 문 c.police_station, c.crime_type, c.case_number
: 어떤 경찰서에서 어떤 범죄유형이 많이 발생했는가를 보고 싶은 것
=> FROM 문의 2번째 컬럼 = #1의 내용: 경찰서별 가장 많이 발생한 범죄건수, 이는 테이블 형태★
별칭을 m으로 줌
=> WHERE 문(조인구문)
1. from절의 c(의 police_station)와 m(의 police_station)같고
AND 2. c의 건수와 m의 많이 발생 건수가 같은 것을 조인해줌
출력예시
| police_station | crime_type | case_number |
+----------------+------------+-------------+
| 중부 | 폭력 | 997 |
| 종로 | 폭력 | 964 |
| 남대문 | 절도 | 699 |
| 서대문 | 폭력 | 1292 |
| 혜화 | 폭력 | 747 |
| 용산 | 폭력 | 1617 |
| 성북 | 폭력 | 672 |
| 동대문 | 폭력 | 1784 |
| 마포 | 폭력 | 1844 |3. 중첩서브쿼리 (Nested Subquery)
■중첩서브쿼리 (Nested Subquery)
WHERE 절에서사용하는서브쿼리.
- Single Row Subquery - 하나의 열을 검색하는 서브쿼리
- Multiple Row Subquery - 하나 이상의 행을 검색하는 서브쿼리
- Multiple Column Subquery - 하나이상의 열을 검색하는 서브쿼리
Single Row Subquery p15-------------------------------------
서브쿼리가 비교연산자( =, >, >=, <, <=, <>, !=)와 사용되는경우
서브쿼리의 검색결과는 한 개의 결과값을 가져야 한다. (두개 이상인 경우 에러)
SELECT column_names
FROM table_name
WHERE column_name = (SELECT column_name
FROM table_name
WHERE codition)
ORDER BY column_names;예제 : 셀럽 테이블의 이름 컬럼이 snl_show 테이블에서 호스트 컬럼과 같은 것을 출력하라.
=> celeb와 snl_show 테이블 사용
SELECT name FROM celeb;
| name |
+--------+
| 아이유 |
| 이미주 |
| 송강 |
| 강동원 |- Single Row Subquery 예제 - 괄호없이 (에러)
SELECT name FROM celeb WHERE name = SELECT host FROM snl_show;
=> ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT host FROM snl_show' at line- Single Row Subquery 예제 - 한개이상의결과 (에러)
SELECT name FROM celeb WHERE name = (SELECT host FROM snl_show);
=> 괄호를 사용해도 에러가 뜸, ERROR 1242 (21000): Subquery returns more than 1 row
=> 서브쿼리는 1개 이상의 행을 반환해야 한다. Single Row Subquery 예제
SELECT name FROM celeb WHERE name = (SELECT host FROM snl_show
WHERE id=1);
=> (SELECT host FROM snl_show WHERE id=1) => 행 1개_snl_show에서 id가 1인 호스트 행1개
결과값
| name |
+--------+
| 강동원 |4.Multiple Row Subquery
[1] IN
Multiple Row - IN
서브쿼리결과중에포함될때
SELECT column_names
FROM table_name
WHERE column_name IN (SELECT column_name
FROM table_name
WHERE codition)
ORDER BY column_names;
Multiple Row - IN 예제
SNL 에출연한영화배우를조회#1. where절 내부 서브쿼리
SELECT name
FROM celeb
WHERE JOB_TITLE LIKE '%영화배우%';
=> 결과값의 행이 2개 이상이라는 것 (다중행 서브쿼리)
+--------+
| name |
+--------+
| 강동원 |
| 차승원 |#2. 정답
SELECT host
FROM snl_show
WHERE host IN (SELECT name
FROM celeb
WHERE JOB_TITLE LIKE '%영화배우%');
| host |
+--------+
| 강동원 |
| 차승원 |
[2] EXISTS
Multiple Row - EXISTS
서브쿼리결과에값이있으면반환
SELECT column_names
FROM table_name
WHERE EXISTS (SELECT column_name
FROM table_name
WHERE codition)
ORDER BY column_names;Multiple Row - EXISTS 예제
범죄검거 혹은 발생건수가 2000건보다 큰경찰서 조회
=> 궁극적으로 보고 싶은 것은 경찰서 이름 하나
#1. 사용 테이블 확인
select * from police_station limit 1;
| name | address |
+----------------+-------------------------------------+
| 서울강남경찰서 | 서울특별시 강남구 테헤란로 114길 11 |
select * from crime_status limit 1;
| year | police_station | crime_type | status_type | case_number | reference |
+------+----------------+------------+-------------+-------------+----------------+
| 2020 | 중부 | 살인 | 발생 | 1 | 서울중부경찰서 |
=> 이 두 테이블을 이용
crime_status 테이블의 정보를 가지고 검거와 발생이 2000건 이상인 경찰서 이름을 조회하고
그 이름이 police_station의 이름과 맞으면 출력하도록 작업- where절 서브쿼리 : 건수가 2000보다 큰 경우의 경찰서 이름 조회
SELECT police_station, reference
FROM crime_status
WHERE case_number > 2000;
| police_station | reference |
+----------------+------------------+
| 영등포 | 서울영등포경찰서 |
| 영등포 | 서울영등포경찰서 |
| 영등포 | 서울영등포경찰서 |
| 중랑 | 서울중랑경찰서 |
| 강남 | 서울강남경찰서 |#3. 정답
SELECT name
FROM police_station p
WHERE EXISTS (SELECT police_station
FROM crime_status c
WHERE p.name = c.reference AND case_number > 2000);
=> 내부 where절: p.name과 c.reference이 같고 건수가 (crime_status에서) 건수가 2000건 이상인 게 존재하면 crime_status 테이블에서 police_station 컬럼을 조회한 게 존재하면
(police_station 테이블의 name컬럼과
crime_status 테이블의 reference컬럼이 같고)
police_station 테이블에서 경찰서 이름을 조회해서 불러오세요.
결과값 일부
| name |
+------------------+
| 서울강남경찰서 |
| 서울강서경찰서 |
| 서울관악경찰서 |
| 서울구로경찰서 |
| 서울노원경찰서 |Multiple Row - ANY
서브쿼리결과중에최소한하나라도만족하면 (비교연산자사용)
SELECT column_names
FROM table_name
WHERE column_name = ANY (SELECT column_name
FROM table_name
WHERE codition)
ORDER BY column_names;Multiple Row - ANY 예제
SNL 에출연한적이있는연예인이름조회
=> 최종적으로 보고 싶은 것은 그냥 연예인의 이름
SELECT name
FROM celeb
WHERE name = ANY (SELECT host
FROM snl_show);
| name |
+--------+
| 강동원 |
| 유재석 |
| 차승원 |
| 이수현 |Multiple Row - ALL
서브쿼리결과를모두만족하면 (비교연산자사용)
SELECT column_names
FROM table_name
WHERE column_name = ALL (SELECT column_name
FROM table_name
WHERE condition);
ORDER BY column_names;Multiple Row - ALL 예제
서브쿼리결과를모두만족하면 (비교연산자사용)
SELECT name
FROM celeb
WHERE name = ALL (SELECT host
FROM snl_show
WHERE id = 1);
| name |
+--------+
| 강동원 |Multi Column Subquery - 연관서브쿼리
다중열 서브쿼리: where절에 컬럼이 여러개 사용되는 경우
서브쿼리 내에 메인쿼리 '컬럼'이 같이 사용되는 경우.
SELECT column_names
FROM tablename a
WHERE (a.column1, a.column2, ...) IN (SELECT b.column1, b.column2, ...)
FROM tablename b
WHERE a.column_name = b.column_name)
ORDER BY column_names;강동원과 성별, 소속사가 같은 연예인의 이름, 성별, 소속사를조회
#1. where절의 IN 안의 select절: 강동원의 성별과 소속사 조회
SELECT sex, agency
FROM celeb
WHERE name = '강동원';
=>
| sex | agency |
+------+----------------+
| M | YG엔터테이먼트 |#2. 1번과 같은 성별과 소속사의 이름, 성별, 소속사를 조회
정답
SELECT name, sex, agency
FROM celeb
WHERE (sex, agency) IN (SELECT sex, agency
FROM celeb
WHERE name = '강동원');
=> 핵심은 WHERE절에 컬럼이 여러개 사용되었다는 것, WHERE (sex, agency)
(다중열 서브쿼리: where절에 컬럼이 여러개 사용되는 경우)
결과값★
=> 최종 결과값이 where내부 in 안에 select절의 2개의 컬럼이 담긴 하나의 행이었고
여기의 2컬럼(sex,agency) 값을 celeb 테이블의 원래 컬럼(sex,agency)과 비교를 해서
거기에 있으면 이름과 성별과 소속사를 출력 , 이것이 다중행 서브쿼리
| name | sex | agency |
+--------+------+----------------+
| 강동원 | M | YG엔터테이먼트 |
| 차승원 | M | YG엔터테이먼트 |
+--------+------+----------------+<문제 5문>
- oil_price 테이블에서셀프주유의평균가격과 SK에너지의가장비싼가격을 Scalar Subquery 를사용하여조회
- oil_price 테이블에서상표별로가장비싼가격과상호를 Inline View 를사용하여조회하세요.
- 평균가격보다높은주유소상호와가격을 Nested Subquery 를사용하여조회하세요.
- 3번에서조회한주유소에서주유한연예인의이름과주유소, 주유일을 Nested Subquery 를사용하여조회하세요. (refueling 테이블)
- refueling 테이블과 oil_price 테이블에서 10만원이상주유한연예인이름, 상호, 상표, 주유금액, 가격을 Inline View 를사용하여조회하세요.