집계함수
GROUP BY
crime_status 에서 경찰서로 그룹화 해서 경찰서 이름 조회
mysql> select police_station
-> from crime_status
-> group by police_station;
+----------------+
| police_station |
+----------------+
| 중부 |
| 종로 |
| 남대문 |
| 서대문 |
| 혜화 |
| 용산 |
| 성북 |
| 동대문 |
| 마포 |
| 영등포 |
| 성동 |
| 동작 |
| 광진 |
| 서부 |
| 강북 |
| 금천 |
| 중랑 |
| 강남 |
| 관악 |
| 강서 |
| 강동 |
| 종암 |
| 구로 |
| 서초 |
| 양천 |
| 송파 |
| 노원 |
| 방배 |
| 은평 |
| 도봉 |
| 수서 |
+----------------+
31 rows in set (0.20 sec)
경찰서 별로 총 발생 범죄 건수 조회
mysql> select police_station, sum(case_number) 발생건수
-> from crime_status
-> where status_type like '발생'
-> group by police_station
-> order by 발생건수 desc
-> ;
+----------------+--------------+
| police_station | 발생건수 |
+----------------+--------------+
| 송파 | 5410 |
| 관악 | 5261 |
| 영등포 | 5217 |
| 서대문 | 5066 |
| 강남 | 4754 |
| 강서 | 4415 |
| 구로 | 4175 |
| 중부 | 4114 |
| 강동 | 3788 |
| 서초 | 3765 |
| 노원 | 3743 |
| 중랑 | 3726 |
| 마포 | 3688 |
| 광진 | 3601 |
| 동대문 | 3401 |
| 종로 | 3388 |
| 양천 | 3216 |
| 용산 | 3210 |
| 동작 | 3200 |
| 혜화 | 2816 |
| 강북 | 2770 |
| 남대문 | 2708 |
| 수서 | 2602 |
| 금천 | 2598 |
| 성동 | 2362 |
| 도봉 | 2179 |
| 은평 | 2028 |
| 서부 | 1362 |
| 종암 | 1359 |
| 성북 | 1208 |
| 방배 | 836 |
+----------------+--------------+
31 rows in set (0.20 sec)
경찰서별로 평균 범죄 검거 건수 검색
mysql> select police_station, avg(case_number) 평균검거건수
-> from crime_status
-> where status_type like '검거'
-> group by police_station
-> order by 평균검거건수 desc;
+----------------+--------------------+
| police_station | 평균검거건수 |
+----------------+--------------------+
| 관악 | 771.6000 |
| 송파 | 708.8000 |
| 강서 | 678.6000 |
| 강남 | 674.6000 |
| 영등포 | 674.2000 |
| 구로 | 596.8000 |
| 중랑 | 556.2000 |
| 강동 | 532.4000 |
| 노원 | 516.2000 |
| 마포 | 506.8000 |
| 광진 | 502.2000 |
| 서초 | 495.2000 |
| 동대문 | 493.0000 |
| 강북 | 451.8000 |
| 동작 | 417.2000 |
| 양천 | 417.2000 |
| 금천 | 393.2000 |
| 수서 | 374.4000 |
| 성동 | 354.8000 |
| 서대문 | 341.2000 |
| 도봉 | 298.0000 |
| 은평 | 293.2000 |
| 용산 | 281.3750 |
| 중부 | 281.2000 |
| 종로 | 235.8000 |
| 서부 | 213.0000 |
| 종암 | 205.0000 |
| 혜화 | 198.8000 |
| 성북 | 184.4000 |
| 남대문 | 183.4000 |
| 방배 | 115.2000 |
+----------------+--------------------+
31 rows in set (0.20 sec)
경찰서별로 평균 범죄 발생 건수와 평균 범죄 검거 건수 검색
mysql> select police_station, status_type, avg(case_number)
-> from crime_status
-> group by police_station, status_type;
+----------------+-------------+------------------+
| police_station | status_type | avg(case_number) |
+----------------+-------------+------------------+
| 중부 | 발생 | 411.4000 |
| 중부 | 검거 | 281.2000 |
| 종로 | 발생 | 338.8000 |
| 종로 | 검거 | 235.8000 |
| 남대문 | 발생 | 270.8000 |
| 남대문 | 검거 | 183.4000 |
| 서대문 | 발생 | 506.6000 |
| 서대문 | 검거 | 341.2000 |
| 혜화 | 발생 | 281.6000 |
| 혜화 | 검거 | 198.8000 |
| 용산 | 발생 | 401.2500 |
| 용산 | 검거 | 281.3750 |
| 성북 | 발생 | 241.6000 |
| 성북 | 검거 | 184.4000 |
| 동대문 | 발생 | 680.2000 |
| 동대문 | 검거 | 493.0000 |
| 마포 | 발생 | 737.6000 |
| 마포 | 검거 | 506.8000 |
| 영등포 | 발생 | 1043.4000 |
| 영등포 | 검거 | 674.2000 |
| 성동 | 발생 | 472.4000 |
| 성동 | 검거 | 354.8000 |
| 동작 | 발생 | 640.0000 |
| 동작 | 검거 | 417.2000 |
| 광진 | 발생 | 720.2000 |
| 광진 | 검거 | 502.2000 |
| 서부 | 발생 | 272.4000 |
| 서부 | 검거 | 213.0000 |
| 강북 | 발생 | 554.0000 |
| 강북 | 검거 | 451.8000 |
| 금천 | 발생 | 519.6000 |
| 금천 | 검거 | 393.2000 |
| 중랑 | 발생 | 745.2000 |
| 중랑 | 검거 | 556.2000 |
| 강남 | 발생 | 950.8000 |
| 강남 | 검거 | 674.6000 |
| 관악 | 발생 | 1052.2000 |
| 관악 | 검거 | 771.6000 |
| 강서 | 발생 | 883.0000 |
| 강서 | 검거 | 678.6000 |
| 강동 | 발생 | 757.6000 |
| 강동 | 검거 | 532.4000 |
| 종암 | 발생 | 271.8000 |
| 종암 | 검거 | 205.0000 |
| 구로 | 발생 | 835.0000 |
| 구로 | 검거 | 596.8000 |
| 서초 | 발생 | 753.0000 |
| 서초 | 검거 | 495.2000 |
| 양천 | 발생 | 643.2000 |
| 양천 | 검거 | 417.2000 |
| 송파 | 발생 | 1082.0000 |
| 송파 | 검거 | 708.8000 |
| 노원 | 발생 | 748.6000 |
| 노원 | 검거 | 516.2000 |
| 방배 | 발생 | 167.2000 |
| 방배 | 검거 | 115.2000 |
| 은평 | 발생 | 405.6000 |
| 은평 | 검거 | 293.2000 |
| 도봉 | 발생 | 435.8000 |
| 도봉 | 검거 | 298.0000 |
| 수서 | 발생 | 520.4000 |
| 수서 | 검거 | 374.4000 |
+----------------+-------------+------------------+
62 rows in set (0.19 sec)
2. HAVING
경찰서 별로 발생한 범죄 건수의 합이 4000 건보다 큰 경우 검색
mysql> select police_station, sum(case_number) count
-> from crime_status
-> where status_type like '발생'
-> group by police_station
-> having count > 4000;
+----------------+-------+
| police_station | count |
+----------------+-------+
| 중부 | 4114 |
| 서대문 | 5066 |
| 영등포 | 5217 |
| 강남 | 4754 |
| 관악 | 5261 |
| 강서 | 4415 |
| 구로 | 4175 |
| 송파 | 5410 |
+----------------+-------+
8 rows in set (0.19 sec)
경찰서 별로 발생한 폭력과 절도의 범죄 건수 평균이 2000보다 큰 경우를 검색
mysql> select police_station, avg(case_number) count
-> from crime_status
-> where (crime_type like '폭력' or crime_type like '절도')
-> and status_type like '발생'
-> group by police_station
-> having count > 2000;
+----------------+-----------+
| police_station | count |
+----------------+-----------+
| 영등포 | 2444.5000 |
| 강남 | 2112.0000 |
| 관악 | 2421.5000 |
| 강서 | 2067.0000 |
| 송파 | 2552.0000 |
+----------------+-----------+
5 rows in set (0.20 sec)
0111danye
오전 11:06
경찰서 별로 절도 범죄 평균 발생 건수를 가장 많은 건수 순으로 10개 검색하고 확인하라.
mysql> select police_station, avg(case_number)
-> from crime_status
-> where crime_type like '절도' and status_type like '발생'
-> group by police_station
-> order by avg(case_number) desc
-> limit 10;
+----------------+------------------+
| police_station | avg(case_number) |
+----------------+------------------+
| 송파 | 2429.0000 |
| 관악 | 2229.0000 |
| 영등포 | 2188.0000 |
| 강남 | 1941.0000 |
| 구로 | 1763.0000 |
| 강서 | 1689.0000 |
| 서초 | 1673.0000 |
| 광진 | 1664.0000 |
| 강동 | 1640.0000 |
| 양천 | 1517.0000 |
+----------------+------------------+
10 rows in set (0.21 sec)
2. 경찰서 별로 많이 검거한 범죄 건수를 가장 적은 순으로 5개 검색하세요.
mysql> select police_station, max(case_number) from crime_status where status_type like '검거' group by police_station order by max(case_number) limit 5;
+----------------+------------------+
| police_station | max(case_number) |
+----------------+------------------+
| 방배 | 365 |
| 남대문 | 494 |
| 성북 | 610 |
| 혜화 | 628 |
| 종암 | 630 |
+----------------+------------------+
5 rows in set (0.21 sec)
3. 경찰서 별로 가장 적게 검거한 건수 중에서 4건보다 큰 경우를 건수가 큰 순으로 정리하여 검색하라.
mysql> select police_station, min(case_number)
-> from crime_status
-> where status_type like '검거'
-> group by police_station
-> having min(case_number) > 4
-> order by min(case_number) DESC
-> ;
+----------------+------------------+
| police_station | min(case_number) |
+----------------+------------------+
| 중랑 | 6 |
| 송파 | 6 |
| 동대문 | 5 |
| 강서 | 5 |
| 구로 | 5 |
+----------------+------------------+
5 rows in set (0.21 sec)
4. 대문으로 끝나는 이름의 경찰서 별 범죄 발생 건수의 평균이 500건 이상인 경우를 검색하시오.
mysql> select police_station, avg(case_number)
-> from crime_status
-> where police_station like '%대문%' and status_type like '발생'
-> group by police_station
-> having avg(case_number) >= 500
-> ;
+----------------+------------------+
| police_station | avg(case_number) |
+----------------+------------------+
| 서대문 | 506.6000 |
| 동대문 | 680.2000 |
+----------------+------------------+
2 rows in set (0.21 sec)
0111danye
오후 4:59
scalar functions
ucase
mysql> select menu from sandwich where price > 15;
+----------------------------------+
| menu |
+----------------------------------+
| Lobster Roll |
| Grilled Laughing Bird Shrimp and |
| Shaved Prime Rib |
+----------------------------------+
3 rows in set (0.22 sec)
mysql> select ucase(menu) from sandwich where price > 15;
+----------------------------------+
| ucase(menu) |
+----------------------------------+
| LOBSTER ROLL |
| GRILLED LAUGHING BIRD SHRIMP AND |
| SHAVED PRIME RIB |
+----------------------------------+
3 rows in set (0.25 sec)
2. lcase
mysql> select lcase('THIS IS LCASE TEST');
+-----------------------------+
| lcase('THIS IS LCASE TEST') |
+-----------------------------+
| this is lcase test |
+-----------------------------+
1 row in set (0.30 sec)
mysql> select lcase(menu) from sandwich where price <5;
+--------------+
| lcase(menu) |
+--------------+
| meatball sub |
+--------------+
1 row in set (0.28 sec)
3. mid
sandwich 중 11위 카페 이름 중 두번째 단어만 조회
mysql> select cafe from sandwich where ranking = 11;
+-----------+
| cafe |
+-----------+
| Lula Cafe |
+-----------+
1 row in set (0.19 sec)
mysql> select mid(cafe, 6, 4) from sandwich where ranking = 11;
+-----------------+
| mid(cafe, 6, 4) |
+-----------------+
| Cafe |
+-----------------+
1 row in set (0.19 sec)
4. length 문자열 길이를 반환하는 함수 / 파이썬 len()
mysql> select length('this is len test');
+----------------------------+
| length('this is len test') |
+----------------------------+
| 16 |
+----------------------------+
1 row in set (0.19 sec)
아무 문자가 없으면 0, 공백이 하나 있으면 길이 1 이라고 나옴, null 값은 null이라고 표현해준다.
sandwich 테이블에서 top 3 주소 길이를 검색하자.
5. round : 지정한 자리에서 숫자를 반올림하는 함수
mysql> select round(2323.252523);
+--------------------+
| round(2323.252523) |
+--------------------+
| 2323 |
+--------------------+
1 row in set (0.19 sec)
mysql> select round(44.9224231, 4);
+----------------------+
| round(44.9224231, 4) |
+----------------------+
| 44.9224 |
+----------------------+
1 row in set (0.19 sec)
mysql> select round(3425.85272834, 2);
+-------------------------+
| round(3425.85272834, 2) |
+-------------------------+
| 3425.85 |
+-------------------------+
1 row in set (0.19 sec)
mysql> select round(64847.54537, -3);
+------------------------+
| round(64847.54537, -3) |
+------------------------+
| 65000 |
+------------------------+
1 row in set (0.19 sec)
숫자 - 면 일의 자리부터 반올림
sandwich 테이블에서 소숫점 자리는 반올림에서 1달러 단위까지만 표시하자, 최하위 3개만 표시
mysql> select ranking, cafe, menu, price, round(price) from sandwich
-> order by ranking desc
-> limit 3;
+---------+-------------------+-----------------+-------+--------------+
| ranking | cafe | menu | price | round(price) |
+---------+-------------------+-----------------+-------+--------------+
| 50 | Phoebe’s Bakery | The Gatsby | 6.85 | 7 |
| 49 | Toni Patisserie | Le Végétarien | 8.75 | 9 |
| 48 | Zenwich | Beef Curry | 7.5 | 8 |
+---------+-------------------+-----------------+-------+--------------+
3 rows in set (0.19 sec)
6. now : 현재 날짜 및 시간을 반환하는 함수
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2023-06-01 07:12:35 |
+---------------------+
1 row in set (0.19 sec)
7. format: 숫자를 천단위 콤마가 있는 형식으로 반환하는 함수
mysql> select format(12345.6789, 0);
+-----------------------+
| format(12345.6789, 0) |
+-----------------------+
| 12,346 |
+-----------------------+
1 row in set (0.19 sec)
mysql> select format(123141242.234625235, 8);
+--------------------------------+
| format(123141242.234625235, 8) |
+--------------------------------+
| 123,141,242.23462524 |
+--------------------------------+
1 row in set (0.19 sec)
oil price 테이블에서 가격이 백원 단위에서 반올림 했을 때 2000 원 이상인 경우 천원 단위에서 콤마를 넣어서 조회
mysql> select format(가격, 0) from oil_price where round(가격, -3) >= 2000;
+-------------------+
| format(가격, 0) |
+-------------------+
| 1,509 |
| 1,598 |
| 1,635 |
| 2,160 |
+-------------------+
4 rows in set (0.26 sec)