📌Aggregate Functions
| Function | Description |
|---|
| COUNT | 총 갯수 계산 |
| SUM | 합계 계산 |
| AVG | 평균 계산 |
| MIN | 최소 값 찾기 |
| MAX | 최대 값 찾기 |
| FIRST | 첫번째 결과 리턴 |
| LAST | 마지막 결과 리턴 |
count
SELECT COUNT(column)
FROM tablename
mysql> select count(*) from police_station;
+
| count(*) |
+
| 31 |
+
1 row in set (0.05 sec)
mysql> select count(distinct crime_type) from crime_status;
+
| count(distinct crime_type) |
+
| 6 |
+
1 row in set (0.05 sec)
sum
SELECT SUM(column)
FROM tablename
mysql> select sum(case_number) from crime_status
-> where status_type='검거' and police_station='중부';
+
| sum(case_number) |
+
| 1406 |
+
1 row in set (0.04 sec)
AVG
SELECT AVG(column)
FROM tablename
mysql> select avg(case_number) from crime_status
-> where police_station like '중부' and crime_type='강도';
+
| avg(case_number) |
+
| 3.5000 |
+
1 row in set (0.05 sec)
MIN
SELECT MIN(column)
FROM tablename
mysql> select min(case_number)
-> from crime_status
-> where police_station like '강남' and crime_type='강도';
+
| min(case_number) |
+
| 10 |
+
1 row in set (0.04 sec)
MAX
SELECT MAX(column)
FROM tablename
mysql> select max(case_number)
-> from crime_status
-> where crime_type='살인';
+
| max(case_number) |
+
| 12 |
+
1 row in set (0.04 sec)
mysql> select * from crime_status
-> where crime_type='살인'
-> order by case_number desc limit 5;
+
| year | police_station | crime_type | status_type | case_number | reference |
+
| 2020 | 관악 | 살인 | 발생 | 12 | 서울관악경찰서 |
| 2020 | 관악 | 살인 | 검거 | 12 | 서울관악경찰서 |
| 2020 | 중랑 | 살인 | 발생 | 10 | 서울중랑경찰서 |
| 2020 | 동대문 | 살인 | 발생 | 10 | 서울동대문경찰서 |
| 2020 | 강서 | 살인 | 발생 | 10 | 서울강서경찰서 |
+
5 rows in set (0.04 sec)
GROUP BY
SELECT column1, column2, ...
FROM table1
WHERE condition
GROUP BY column1, column2, ...
ORDER BY column1, column2, ...
mysql> select police_station 경찰서, sum(case_number) 발생건수
-> from crime_status
-> where status_type like '발생'
-> group by police_station
-> order by 발생건수 desc limit 5;
+
| 경찰서 | 발생건수 |
+
| 송파 | 5410 |
| 관악 | 5261 |
| 영등포 | 5217 |
| 강남 | 4754 |
| 강서 | 4415 |
+
5 rows in set (0.04 sec)
HAVING
SELECT column1, column2, ...
FROM table1
WHERE condition
GROUP BY column1, column2, ...
HAVING condition (Aggregate Functions)
ORDER BY column1, column2, ...
mysql> select police_station, avg(case_number)
-> from crime_status
-> where crime_type in ('폭력' ,'절도') and status_type='발생'
-> group by police_station
-> having avg(case_number) >= 2000;
+
| police_station | avg(case_number) |
+
| 영등포 | 2444.5000 |
| 강남 | 2112.0000 |
| 관악 | 2421.5000 |
| 강서 | 2067.0000 |
| 송파 | 2552.0000 |
+
5 rows in set (0.04 sec)
📌Scalar Functions
| Function | Description |
|---|
| UCASE | 영문->대문자 |
| LCASE | 영문->소문자 |
| MID | 문자열 부분 반환 |
| LENGTH | 문자열 길이 반환 |
| ROUND | 반올림 |
| NOW | 현재 날짜 및 시간 |
| FORMAT | 천단위 콤마 표기 |
UCASE
SELECT UCASE(string);
mysql> select ucase('uCase TEst');
+
| ucase('uCase TEst') |
+
| UCASE TEST |
+
1 row in set (0.04 sec)
LCASE
SELECT LCASE(string);
mysql> select lcase('LCAse TEst');
+
| lcase('LCAse TEst') |
+
| lcase test |
+
1 row in set (0.04 sec)
MID
SELECT MID(string, start, length);
mysql> select mid('mid test', 5, 4);
+
| mid('mid test', 5, 4) |
+
| test |
+
1 row in set (0.05 sec)
LENGTH
SELECT LENGTH(string);
mysql> select 'seoul', length('seoul');
+
| seoul | length('seoul') |
+
| seoul | 5 |
+
1 row in set (0.04 sec)
mysql> select length(NULL);
+
| length(NULL) |
+
| NULL |
+
1 row in set (0.04 sec)
ROUND
SELECT ROUND(number, decimal_point_position);
mysql> select round(1234.5678, 2);
+
| round(1234.5678, 2) |
+
| 1234.57 |
+
1 row in set (0.04 sec)
mysql> select round(1234.5678, -2);
+
| round(1234.5678, -2) |
+
| 1200 |
+
1 row in set (0.05 sec)
NOW
SELECT NOW();
mysql> select now() 현재시간;
+
| 현재시간 |
+
| 2023-02-19 08:49:43 |
+
1 row in set (0.06 sec)
SELECT FORMAT(number, decimal_point_position);
mysql> select format(1234.56, 2);
+
| format(1234.56, 2) |
+
| 1,234.56 |
+
1 row in set (0.04 sec)
mysql> select format(1234.56, -1);
+
| format(1234.56, -1) |
+
| 1,235 |
+
1 row in set (0.05 sec)
mysql> select 상호, format(가격, 0) from oil_price
-> where round(가격, -3) >= 2000;
+
| 상호 | format(가격, 0) |
+
| 쌍문주유소 | 1,509 |
| 21세기주유소 | 1,598 |
| 살피재주유소 | 1,635 |
| 뉴서울(강남) | 2,160 |
+
4 rows in set (0.04 sec)
📌Subquery
- 하나의 SQL 문안에 포함되어 있는 또 다른 SQL문
- Subquery는 괄호로 묶어서 사용, order by 사용불가
- 메인쿼리가 서브쿼리를 포함하는 종속적인 관계
- 서브쿼리는 메인쿼리 칼럼 사용 가능
- 메인쿼리는 서브쿼리 칼럼 사용 불가
- Scalar Subquery - SELECT절 사용
- Inline Subquery - FROM절 사용
- Nested Subquery - WHERE절 사용
Scalar Subquery
- SELECT 절에서 사용하는 서브쿼리
- 결과는 하나의 칼럼
SELECT column1, (SELECT column2 FROM table2 WHERE condition)
FROM table1
WHERE condition;
- 서울은평경찰서의 강도 검거 건수와 서울시 경찰서 전체의 평균 강도 검거 건수를 조회하라
mysql> select case_number,
-> (select avg(case_number)
-> from crime_status
-> where crime_type='강도' and status_type='검거') avg
-> from crime_status
-> where police_station like '은평' and crime_type='강도' and status_type='검거';
+
| case_number | avg |
+
| 1 | 4.1935 |
+
1 row in set (0.05 sec)
Inline Subquery
- FROM 절에 사용하는 서브쿼리
- 메인쿼리에서는 인라인 뷰에서 조회한 칼럼만 사용가능
SELECT a.column, b.column
FROM table1 a, (SELECT column1, column2 FROM table2) b
WHERE condition;
- 경찰서 별로 가장 많이 발생한 범죄 건수와 범죄 유형을 조회
mysql> 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 limit 3;
+
| police_station | crime_type | case_number |
+
| 중부 | 폭력 | 997 |
| 종로 | 폭력 | 964 |
| 남대문 | 절도 | 699 |
+
3 rows in set (0.05 sec)
Nested Subquery
- WHERE 절에서 사용하는 서브쿼리
- Single Row - 하나의 행을 검색
- Multiple Row - 다수의 행을 검색
- Multiple Column - 다수의 열을 검색
Single Row Subquery
SELECT column_names
FROM table_name
WHERE column_name = (SELECT column_name
FROM table_name
WHERE condition)
ORDER BY column_name;
- SNL에 출연했고 SNL.ID가 1인 연예인 조회
mysql> select name from celeb
-> where name=(select host from snl_show where id=1);
+
| name |
+
| 강동원 |
+
1 row in set (0.07 sec)
Multiple Row Subquery : IN
SELECT column_names
FROM table_name
WHERE column_name IN (SELECT column_name
FROM table_name
WHERE condition)
ORDER BY column_name;
mysql> select host from snl_show
-> where host in (select name from celeb where job_title like '%영화배우%');
+
| host |
+
| 강동원 |
| 차승원 |
+
2 rows in set (0.04 sec)
Multiple Row Subquery : EXISTS
SELECT column_names
FROM table_name
WHERE EXISTS (SELECT column_name
FROM table_name
WHERE condition)
ORDER BY column_name;
- 범죄 검거 혹은 발생 건수가 2000건 보다 큰 경찰서 조회
mysql> select name from police_station p
-> where exists (select police_station from crime_status c
-> where p.name=c.reference and case_number > 2000);
+
| name |
+
| 서울강남경찰서 |
| 서울강서경찰서 |
| 서울관악경찰서 |
| 서울구로경찰서 |
| 서울노원경찰서 |
| 서울송파경찰서 |
| 서울영등포경찰서 |
| 서울중랑경찰서 |
+
8 rows in set (0.05 sec)
Multiple Row Subquery : ANY
SELECT column_names
FROM table_name
WHERE column = ANY (SELECT column_name
FROM table_name
WHERE condition)
ORDER BY column_name;
- 연예인 중 가장 어린 남성연예인보다 나이가 많은 연예인
- 남성연예인 연령(28, 41, 50, 48) 중 최소조건 28보다 나이가 많은 연예인
mysql> select name, age from celeb
-> where age > any (select age from celeb where sex='M');
+
| name | age |
+
| 아이유 | 29 |
| 강동원 | 41 |
| 유재석 | 50 |
| 차승원 | 48 |
+
4 rows in set (0.04 sec)
Multiple Row Subquery : ALL
SELECT column_names
FROM table_name
WHERE column = ANY (SELECT column_name
FROM table_name
WHERE condition)
ORDER BY column_name;
- 연예인 중 모든 여성 연예인보다 나이가 많은 연예인
- 여성연예인 연령(29, 28, 23) 중 모든 값보다 나이가 많은 연예인
mysql> select name, age from celeb
-> where age > all (select age from celeb where sex='F');
+
| name | age |
+
| 강동원 | 41 |
| 유재석 | 50 |
| 차승원 | 48 |
+
3 rows in set (0.04 sec)
Multiple Column Subquery
- 서브쿼리 내에 메인쿼리 컬럼이 같이 사용되는 경우
SELECT columns
FROM tablename a
WHERE (a.column1, a.column2, ...)
IN (SELECT b.column1, b.column2, ...
FROM tablename b
WHERE a.column = b.column)
ORDER BY columns;
- 강동원과 성별, 소속사가 같은 연예인의 이름, 성별, 소속사 조회
mysql> 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 rows in set (0.05 sec)
