1. scalar functions
1-1. sandwich 테이블 만들기
# 접속
remote = mysql.connector.connect(
host = "host",
port = 3306,
user = "admin",
password = "password",
database = "zerobase"
)
# csv 파일 읽어오기
df = pd.read_csv("경로", encoding="utf-8")
df["Price"] = df["Price"].str.replace("$", "").astype(float)
# 커서 만들기
cur = remote.cursor(buffered=True)
# 테이블 생성
sql = "CREATE TABLE sandwich (ranking int, cafe varchar(32), \
menu varchar(32), price float, address varchar(32))"
cur.execute(sql)
# 데이터 삽입
sql = "INSERT INTO sandwich VALUES (%s, %s, %s, %s, %s)"
for i, row in df.iterrows():
cur.execute(sql, tuple(row))
print(tuple(row))
remote.commit()
# 종료
remote.close()1-2. scalar functions
입력 값을 기준으로 단일 값을 반환하는 함수
UCASE : 영문을 대문자로 변환
select ucase(menu), price from sandwich where price > 15;
>>>
+----------------------------------+-------+
| ucase(menu) | price |
+----------------------------------+-------+
| LOBSTER ROLL | 16 |
| GRILLED LAUGHING BIRD SHRIMP AND | 17 |
| SHAVED PRIME RIB | 21 |
+----------------------------------+-------+LCASE : 영문을 소문자로 변환
select lcase(menu), price from sandwich where price < 5;
>>>
+--------------+-------+
| lcase(menu) | price |
+--------------+-------+
| meatball sub | 4.5 |
+--------------+-------+MID : 문자열 부분 반환
# SELECT MID(string, start_position, lenth)
# string: 원본 문자열
# start_position: 문자열 반환 시작 위치 (첫 글자 = 1, 마지막 글자 = -1)
# length: 반환할 문자열 길이
# 뒤에서 4번째 위치부터 4글자 조회
select mid("This is mid test", -4, 4);
>>>
+--------------------------------+
| mid("This is mid test", -4, 4) |
+--------------------------------+
| test |
+--------------------------------+LENGTH : 문자열 길이 반환
select length(" ") # 1
select length(NULL) # NULLROUND : 지정한 자리에서 숫자를 반올림 (0이 소수점 첫째 자리)
# SELECT ROUND(number, decimals_place)
# number: 반올림 대상
# decimals: 반올림할 소수점 위치(default = 0)
select round(315.625); # 316
select round(315.625, 1); # 315.6
select round(315.625, -1); # 320
select ranking, price, round(price) from sandwich order by ranking desc limit 3;
>>>
+---------+-------+--------------+
| ranking | price | round(price) |
+---------+-------+--------------+
| 50 | 6.85 | 7 |
| 49 | 8.75 | 9 |
| 48 | 7.5 | 8 |
+---------+-------+--------------+NOW : 현재 날짜 및 시간 반환
select now();FORMAT : 숫자를 천 단위 콤마가 있는 형식으로 반환
# SELECT FORMAT(number, decimal_place)
# number: 포맷을 적용할 문자/숫자
# decimals: 표시할 소수점 위치
select format(12345.6789, 0); # 12,345
select format(12345.6789, 2); # 12,345.68
select format(12345.6789, 10); # 12,345.6789000000
select 상호, format(가격, 0) from oil_price where round(가격, -3) >= 2000;round()는 숫자를 반환하지만, format()은 문자를 반환한다.
2. subquery
- 하나의 sql문 안에 포함되어 있는 또 다른 sql문
- 메인 쿼리가 서브 쿼리를 포함하는 종속 관계
- 메인 쿼리는 서브 쿼리의 컬럼 사용 불가
- 서브 쿼리는 메인 쿼리의 컬럼 사용 가능
- 서브 쿼리는 괄호로 묶어서 사용
- 단일 행, 복수 행 비교 연산자와 함께 사용 가능
- 서브 쿼리에서는 order by 사용 불가
- 스카라 서브쿼리(scalar subquery) : SELECT절에서 사용
- 인라인 뷰(inline view) : FROM절에서 사용
- 중첩 서브쿼리(nested subquery) : WHERE절에서 사용
2-1. scalar subquery
결과가 하나의 컬럼이어야 한다.
# 은평 경찰서의 강도 검거 건수와 서울시 경찰서 전체의 평균 강도 검거 건수
select case_number,
(select avg(case_number)
from crime_status
where crime_type='강도' and status_type='검거') avg
from crime_status
where police_station='은평' and crime_type='강도' and status_type='검거';
>>>
+-------------+--------+
| case_number | avg |
+-------------+--------+
| 1 | 4.1935 |
+-------------+--------+2-2. inline view
메인 쿼리에서는 인라인 뷰에서 조회한 컬럼만 사용 가능
from절에서 사용되는 서브 쿼리의 결과를 하나의 테이블처럼 사용
# 경찰서별로 가장 많이 발생한 범죄 건수와 범죄 유형
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='발생'
group by police_station) m
where c.police_station = m.police_station
and c.case_number = m.count;
>>>
+----------------+------------+-------------+
| police_station | crime_type | case_number |
+----------------+------------+-------------+
| 중부 | 폭력 | 997 |
| 종로 | 폭력 | 964 |2-3. nested subquery
2-3-1. single row : 하나의 행을 검색하는 서브 쿼리
서브 쿼리가 비교 연산자와 사용되는 경우, 서브 쿼리의 검색 결과는 한 개의 결과값을 가져야 한다. (두 개 이상이면 에러 발생)
# 서브 쿼리 검색 결과가 두 개 이상일 때 - 에러 발생
select name from celeb where name = (select host from snl_show);
>>>
Subquery returns more than 1 row# 서브 쿼리 검색 결과가 하나일 때
select name from celeb where name = (select host from snl_show where id = 1);2-3-2. multiple row : 하나 이상의 행을 검색하는 서브 쿼리
# IN
select host from snl_show
where host in (select name from celeb where job_title like "%actor%");IN 대신에 JOIN을 사용할 수 있고, JOIN의 효율이 더 좋다.
# EXISTS
# 서브 쿼리 결과에 값이 있으면 반환
# 범죄 검거 혹은 발생 건수가 2000건 이상인 경찰서 조회
select name from police_station p
where exists (select police_station
from crime_status c
where p.name = c.reference and case_number > 2000);# ANY
# 서브 쿼리 결과 중에 최소한 하나라도 만족하면 반환(비교 연산자 사용)
# snl에 출연한 적 있는 연예인 이름 조회
select name from celeb
where name = any (select host from snl_show);# ALL
# 서브 쿼리 결과를 모두 만족하면 반환(비교 연산자 사용)
select name from celeb
where name = all (select host from snl_show where id = 1);2-3-3. multiple column : 하나 이상의 열을 검색하는 서브 쿼리(연관 서브 쿼리)
# 강동원과 성별, 소속사가 같은 연예인의 이름, 성별, 소속사 조회
select name, sex, agency
-> from celeb
-> where (sex, agency) in (select sex, agency from celeb where name='강동원');
>>>
+-----------+------+--------+
| name | sex | agency |
+-----------+------+--------+
| 강동원 | M | YG |
| 차승원 | M | YG |
+-----------+------+--------+2-4. 연습문제
# 셀프 주유의 평균 가격과 SK에너지의 가장 비싼 가격 조회(scalar subquery)
select avg(가격) self_avg,
(select max(가격) from oil_price where 상표='SK에너지') sk_max
from oil_price
where 셀프='Y';
>>>
+-----------+--------+
| self_avg | sk_max |
+-----------+--------+
| 1517.6000 | 2160 |
+-----------+--------+# "상표별로 가장 비싼 가격"과 상호 조회(inline view)
# 1
select 상표, max(가격) from oil_price group by 상표;
+--------------------+--------------+
| 상표 | max(가격) |
+--------------------+--------------+
| SK에너지 | 2160 |
| 현대오일뱅크 | 1498 |
| S-OIL | 1509 |
| GS칼텍스 | 1498 |
+--------------------+--------------+
# 2
select o.상표, o.상호, m.max_price
from oil_price o,
(select 상표, max(가격) max_price from oil_price group by 상표) m
where o.상표 = m.상표 and o.가격 = m.max_price;
>>>
+--------------------+-------------------+-----------+
| 상표 | 상호 | max_price |
+--------------------+-------------------+-----------+
| 현대오일뱅크 | (주)MS주유소 | 1498 |
| S-OIL | 쌍문주유소 | 1509 |
| SK에너지 | 뉴서울(강남) | 2160 |
| GS칼텍스 | 신길주유소 | 1498 |
+--------------------+-------------------+-----------+# 평균 가격보다 높은 주유소 상호와 가격(nested subquery)
select 상호, 가격
from oil_price
where 가격 > (select avg(가격) from oil_price);# 앞에서 조회한 주유소에서 주유한 연예인의 이름과 주유소, 주유일(nested subquery)
select 이름, 주유소, 주유일
from refueling
where 주유소 in (select 상호 from oil_price
where 가격 > (select avg(가격) from oil_price));
>>>
+-----------+-------------------+------------+
| 이름 | 주유소 | 주유일 |
+-----------+-------------------+------------+
| 유재석 | 뉴서울(강남) | 2021-10-01 |
| 이미주 | 뉴서울(강남) | 2021-10-01 |
| 유재석 | 뉴서울(강남) | 2021-10-03 |
| 아이유 | 뉴서울(강남) | 2021-10-14 |
| 아이유 | 뉴서울(강남) | 2021-10-14 |
| 유재석 | 뉴서울(강남) | 2021-10-16 |
+-----------+-------------------+------------+# 10만 원 이상 주유한 연예인 이름, 상호, 상표, 주유 금액, 가격(inline view)
select r.이름, o.상호, o.상표, r.금액, o.가격
from oil_price o,
(select 이름, 금액, 주유소 from refueling where 금액 > 100000) r
where o.상호 = r.주유소;
>>>
+-----------+--------------------+-------------+--------+--------+
| 이름 | 상호 | 상표 | 금액 | 가격 |
+-----------+--------------------+-------------+--------+--------+
| 이상순 | 타이거주유소 | SK에너지 | 140000 | 1484 |
| 유재석 | 뉴서울(강남) | SK에너지 | 110000 | 2160 |
| 아이유 | 뉴서울(강남) | SK에너지 | 120000 | 2160 |
| 아이유 | 뉴서울(강남) | SK에너지 | 150000 | 2160 |
| 이미주 | 뉴서울(강남) | SK에너지 | 120000 | 2160 |
+-----------+--------------------+-------------+--------+--------+