46.SQL-12

SOWA·2023년 5월 5일
sql
0

SQL

목록 보기
12/12

🧷 집계함수 (Aggregate Functions)

여러 칼럼 혹은 테이블 전체 칼럼으로부터 하나의 결과값을 반환하는 함수

FunctionDescription
COUNT총 갯수를 계산해주는 함수
SUM합계를 계산해주는 함수
AVG평균을 계산해주는 함수
MIN가장 작은 값을 찾아주는 함수
MAX가장 큰 값을 찾아주는 함수
FIRST첫번째 결과값을 리턴하는 함수
LAST마지막 결과값을 리턴하는 함수

🖇️ COUNT

총 갯수를 계산해주는 함수

SELECT COUNT(column)
FROM tablename
WHERE condition;

SELECT COUNT(DISTINCT police_station) FROM crime_status;
⇊

mysql> SELECT COUNT(DISTINCT police_station) FROM crime_status;
+--------------------------------+
| COUNT(DISTINCT police_station) |
+--------------------------------+
|                             31 |
+--------------------------------+

ㄴ crime_status 테이블에서 경찰서는 총 몇군데인지

🖇️ SUM

숫자 칼럼의 합계를 계산해주는 함수

SELECT SUM(column)
FROM tablename
WHERE condition;

🖇️ AVG

숫자 칼럼의 평균을 계산해주는 함수

SELECT AVG(column)
FROM tablename
WHERE condition;

🖇️ MIN

숫자 칼럼 중 가장 작은 값을 찾아주는 함수

SELECT MIN(column)
FROM tablename
WHERE condition;

🖇️ MAX

숫자 칼럼 중 가장 큰 값을 찾아주는 함수

SELECT MAX(column)
FROM tablename
WHERE condition;

🖇️ GROUP BY

그룹화하여 데이터를 조회

SELECT column2, column2, ...
FROM table
WHERE condition
GROUP BY dolumn1, column2, ...
ORDER BY column1, column2, ...

🖇️ HAVING

조건에 집계함수가 포함되는 경우 WHERE 대신 HAVING 사용

SELECT column1, column2, ...
FROM table
WHERE condition
GROUP BY dolumn1, column2, ...
HAVING condition (Aggregate Functions)
ORDER BY column1, column2, ...

🧷 Scalar Functions

입력값을 기준으로 단일 값을 반환하는 함수

FunctionDescription
UCASE영문을 대문자로 변환하는 함수
LCASE영문을 소문자로 변환하는 함수
MID문자열 부분을 반환하는 함수
LENGTH문자열 길이를 반환하는 함수
ROUND지정한 자리에서 숫자를 반올림하는 함수(0이 소숫점 첫째자리)
NOW현재 날짜 및 시간을 반환하는 함수
FORMAT숫자를 천단위 콤마가 있는 형식으로 반환하는 함수

🖇️ UCASE

영문을 대문자로 변환하는 함수

SELECT UCASE(string);


mysql> Select UCASE('This Is ucase Test.');
+------------------------------+
| UCASE('This Is ucase Test.') |
+------------------------------+
| THIS IS UCASE TEST.          |
+------------------------------+

🖇️ LCASE

영문을 소문자로 변환하는 함수

SELECT LCASE(string);


mysql> select LCASE('This Is LCASE Test.');
+------------------------------+
| LCASE('This Is LCASE Test.') |
+------------------------------+
| this is lcase test.          |
+------------------------------+
mysql> select ucase(cafe), lcase(menu) from sandwich;  
+---------------------------------+----------------------------------+
| ucase(cafe)                     | lcase(menu)                      |
+---------------------------------+----------------------------------+
| OLD OAK TAP                     | blt                              |
| AU CHEVAL                       | fried bologna                    |
| XOCO                            | woodland mushroom                |
| AL’S DELI                       | roast beef                       |
| PUBLICAN QUALITY MEATS          | pb&l                             |
| HENDRICKX BELGIAN BREAD CRAFTER | belgian chicken curry salad      |
| ACADIA                          | lobster roll                     |
| BIRCHWOOD KITCHEN               | smoked salmon salad              |
| CEMITAS PUEBLA                  | atomica cemitas                  |
| NANA                            | grilled laughing bird shrimp and |
| LULA CAFE                       | ham and raclette panino          |
| RICOBENE’S                      | breaded steak                    |
| FROG N SNAIL                    | the hawkeye                      |
| CROSBY’S KITCHEN                | chicken dip                      |
| LONGMAN & EAGLE                 | wild boar sloppy joe             |
| BARI                            | meatball sub                     |
| MANNY’S                         | corned beef                      |
| EGGY’S                          | turkey club                      |
| OLD JERUSALEM                   | falafel                          |
| MINDY’S HOTCHOCOLATE            | crab cake                        |
| OLGA’S DELICATESSEN             | chicken schnitzel                |
| DAWALI MEDITERRANEAN KITCHEN    | shawarma                         |
| BIG JONES                       | toasted pimiento cheese          |
| LA PANE                         | vegetarian panino                |
| PASTORAL                        | cali chèvre                      |
| MAX’S DELI                      | pastrami                         |
| LUCKY’S SANDWICH CO.            | the fredo                        |
| CITY PROVISIONS                 | smoked ham                       |
| PAPA’S CACHE SABROSO            | jibarito                         |
| BAVETTE’S BAR & BOEUF           | shaved prime rib                 |
| HANNAH’S BRETZEL                | serrano ham and manchego cheese  |
| LA FOURNETTE                    | tuna salad                       |
| PARAMOUNT ROOM                  | paramount reuben                 |
| MELT SANDWICH SHOPPE            | the istanbul                     |
| FLORIOLE CAFE & BAKERY          | b.a.d.                           |
| FIRST SLICE PIE CAFÉ            | duck confit and mozzarella       |
| TROQUET                         | croque monsieur                  |
| GRAHAMWICH                      | green garbanzo                   |
| SAIGON SISTERS                  | the hen house                    |
| ROSALIA’S DELI                  | tuscan chicken                   |
| Z&H MARKETCAFE                  | the marty                        |
| MARKET HOUSE ON THE SQUARE      | whitefish                        |
| ELAINE’S COFFEE CALL            | oat bread, pecan butter, and fru |
| MARION STREET CHEESE MARKET     | cauliflower melt                 |
| CAFECITO                        | cubana                           |
| CHICKPEA                        | kufta                            |
| THE GODDESS AND GROCER          | debbie’s egg salad               |
| ZENWICH                         | beef curry                       |
| TONI PATISSERIE                 | le végétarien                    |
| PHOEBE’S BAKERY                 | the gatsby                       |
+---------------------------------+----------------------------------+

ㄴ sandwich 테이블에서 가게이름은 대문자, 메뉴이름은 소문자로 조회



🖇️ MID

문자열 부분을 반환하는 함수

SELECT MID(string, start_position, length);

  • string :원본 문자열
  • start_position: 문자열 반환 시작위치(첫글자는 1, 마지막 글자는 -1)
  • length: 반환할 문자열 길이

mysql> select MID('This is mid test', 1, 4);
+-------------------------------+
| MID('This is mid test', 1, 4) |
+-------------------------------+
| This                          |
+-------------------------------+
mysql> select MID('This is mid test', -4, 4); 
+--------------------------------+
| MID('This is mid test', -4, 4) |
+--------------------------------+
| test                           |
+--------------------------------+

🖇️ LENGTH

문자열 길이를 반환하는 함수

mysql> select length('This is len test');
+----------------------------+
| length('This is len test') |
+----------------------------+
|                         16 |
+----------------------------+
mysql> select length(NULL);
+--------------+
| length(NULL) |
+--------------+
|         NULL |
+--------------+

ㄴNULL의 경우 길이가 없으므로 NULL


🖇️ ROUND

지정한 자리에서 숫자를 반올림하는 함수

SELECT ROUND(number, decimals_place);

  • number: 반올림할 대상
  • decimals: 반올림할 소수점 위치(Option)

mysql> select ROUND(315.625);
+----------------+
| ROUND(315.625) |
+----------------+
|            316 |
+----------------+

ㄴ 반올림할 위치를 지정하지 않을 경우, 소수점자리 0에서 반올림

mysql> select ROUND(315.625, 1); 
+-------------------+
| ROUND(315.625, 1) |
+-------------------+
|             315.6 |
+-------------------+
mysql> select ROUND(315.625, -1); 
+--------------------+
| ROUND(315.625, -1) |
+--------------------+
|                320 |
+--------------------+

ㄴ 1단위 위치는 -1

🖇️ NOW

현재 날짜 및 시간을 반환하는 함수

SELECT NOW();

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2023-05-05 14:36:31 |
+---------------------+

🖇️ FORMAT

숫자를 천단위 콤마가 있는 형식으로 반환하는 함수

SELECT FORMAT(number, decimal_place);

  • number: 포캣을 적용할 문자 혹은 숫자
  • decimals: 표시할 소수점 위치
mysql> select format(12345.6789, 0);
+-----------------------+
| format(12345.6789, 0) |
+-----------------------+
| 12,346                |
+-----------------------+

ㄴ 소수점을 표시하지 않을 경우 0


mysql> select format(12345.6789, 2); 
+-----------------------+
| format(12345.6789, 2) |
+-----------------------+
| 12,345.68             |
+-----------------------+

ㄴ소수점 두자리까지 표시할 경우 2


mysql> select format(12345.6789, 10); 
+------------------------+
| format(12345.6789, 10) |
+------------------------+
| 12,345.6789000000      |
+------------------------+

ㄴ 소수점 열자리까지 표시


mysql> select format(가격, 0) from oil_price where round(가격, -3) >= 2000;
+-------------------+
| format(가격, 0)   |
+-------------------+
| 1,509             |
| 1,598             |
| 1,635             |
| 2,160             |
+-------------------+

ㄴ oil_price 테이블에서 가격이 백원단위에서 반올림했을때 2000원 이상인 경우 천원단위에 콤마를 넣어서 조회


🧷 SQL Subquery

하나의 SQL문 안에 포함되어있는 또 다른 SQL문. 메인쿼리가 서브쿼리를 포함하는 종속적인 관계
- 서브쿼리는 메인쿼리의 칼럼 사용가능
- 메인 쿼리는 서브쿼리의 칼럼 사용불가
- 서브쿼리는 괄호로 묶어서 사용
- 단일행 혹은 복수행 비교 연산자와 함께 사용가능
- 서브쿼리에서는 order by를 사용하지 않음

🖇️ Scalar Subquery

Select 절에 사용. 결과는 하나의 컬럼이어야 함

SELECT collumn1, (SELECT column2 FROM table2 WHERE condition)
FROM table1
WHERE condition;
mysql> SELECT case_number,
    ->        (Select avg(case_number)
    ->         FROM crime_status
    ->         where crime_stype like '강도' and status_type like '검거') avg
    -> from crime_status
    -> where police_station like '은평' and crime_stype like '강도' and status_type like '검거';
+-------------+--------+
| case_number | avg    |
+-------------+--------+
|           1 | 4.1846 |
+-------------+--------+

ㄴ 서울은평경찰서의 강도 검거 건수와 서울시 경찰서 전체의 평균 강도 검거 건수를 조회


🖇️ Inline View

From 절에 사용. 메인쿼리에서는 인라인 뷰에서 조회한 컬럼만 사용가능

SELECT a.column, b.column
FROM table a, (SELECT column1, column2 FROM table2) b
WHERE condition;
mysql> select c.police_station, c.crime_stype, 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;
+----------------+-------------+-------------+
| police_station | crime_stype | case_number |
+----------------+-------------+-------------+
| 중부           | 폭력        |         997 |
| 중부           | 폭력        |         997 |
| 종로           | 폭력        |         964 |
| 남대문         | 절도        |         699 |
| 서대문         | 폭력        |        1292 |
| 혜화           | 폭력        |         747 |
| 용산           | 폭력        |        1617 |
| 성북           | 폭력        |         672 |
| 동대문         | 폭력        |        1784 |
| 마포           | 폭력        |        1844 |
| 영등포         | 폭력        |        2701 |
| 성동           | 폭력        |        1223 |
| 동작           | 폭력        |        1631 |
| 광진           | 폭력        |        1676 |
| 서부           | 폭력        |         748 |
| 강북           | 폭력        |        1817 |
| 금천           | 폭력        |        1471 |
| 중랑           | 폭력        |        2022 |
| 강남           | 폭력        |        2283 |
| 관악           | 폭력        |        2614 |
| 강서           | 폭력        |        2445 |
| 강동           | 폭력        |        1942 |
| 중부           | 폭력        |         997 |
| 종로           | 폭력        |         964 |
| 중부           | 폭력        |         997 |
| 중부           | 폭력        |         997 |
| 종로           | 폭력        |         964 |
| 중부           | 폭력        |         997 |
| 중부           | 폭력        |         997 |
| 중부           | 폭력        |         997 |
| 종로           | 폭력        |         964 |
| 남대문         | 절도        |         699 |
| 서대문         | 폭력        |        1292 |
| 혜화           | 폭력        |         747 |
| 용산           | 폭력        |        1617 |
| 성북           | 폭력        |         672 |
| 동대문         | 폭력        |        1784 |
| 마포           | 폭력        |        1844 |
| 영등포         | 폭력        |        2701 |
| 성동           | 폭력        |        1223 |
| 동작           | 폭력        |        1631 |
| 광진           | 폭력        |        1676 |
| 서부           | 폭력        |         748 |
| 강북           | 폭력        |        1817 |
| 금천           | 폭력        |        1471 |
| 중랑           | 폭력        |        2022 |
| 강남           | 폭력        |        2283 |
| 관악           | 폭력        |        2614 |
| 강서           | 폭력        |        2445 |
| 강동           | 폭력        |        1942 |
| 종암           | 폭력        |         758 |
| 구로           | 폭력        |        2204 |
| 서초           | 폭력        |        1750 |
| 양천           | 폭력        |        1582 |
| 송파           | 폭력        |        2675 |
| 노원           | 폭력        |        2163 |
| 방배           | 폭력        |         423 |
| 은평           | 폭력        |        1092 |
| 도봉           | 폭력        |        1234 |
| 수서           | 폭력        |        1394 |
+----------------+-------------+-------------+

ㄴ 경찰서 별로 가장 많이 발생한 범죄 건수와 범죄 유형을 조회


🖇️ Nested Subquery(중첩 서브쿼리)

Where절에 사용

─ Single Row

하나의 열을 검색하는 서브쿼리

  • 서브쿼리가 비교연산자(=, >, >=, <, <=, <>, !=)와 사용되는 경우, 서브쿼리의 검색결과는 한 개의 결과값을 가져야함 (두개이상인 경우 에러)
SELECT column_names
FROM table_name
WHERE column_name = (SELECT column_name
					 FROM table_name
                     WHERE condition)
ORDER BY column_names;

mysql> 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 1

ㄴ 괄호가 없어서 에러

mysql> select name from celeb where name = (select host from snl_show); 
ERROR 1242 (21000): Subquery returns more than 1 row

ㄴ 한 개이상의 결과가 나와서 에러

mysql> select name from celeb where name = (select host from snl_show where id = 1); 
+-----------+
| name      |
+-----------+
| 강동원    |
+-----------+

─ Multiple Row

하나 이상의 열을 검색하는 서브쿼리

  • IN
    : 서브쿼리 결과 중에 포함 될때 
SELECT column_names
FROM table_name
WHERE column_name IN = (SELECT column_name
					    FROM table_name
                        WHERE condition)
ORDER BY column_names;
mysql> select host
    -> from snl_show
    -> where host in (select name
    ->                from celeb
    ->                where job_title like '%영화배우%');
+-----------+
| host      |
+-----------+
| 강동원    |
| 차승원    |
+-----------+

ㄴ SNL에 출연한 영화배우 조회


  • EXISTS
    : 서브쿼리 결과에 값이 있으면 반환
SELECT column_names
FROM table_name
WHERE EXISTS = (SELECT column_name
			   FROM table_name
               WHERE condition)
ORDER BY column_names;

  • ANY
    : 서브쿼리중에 최소한 하나라도 만족하면(비교연산자 사용)
SELECT column_names
FROM table_name
WHERE column_name = ANY (SELECT column_name
			   			 FROM table_name
               			 WHERE condition)
ORDER BY column_names;
mysql> select name
    -> from celeb
    -> where name = any (select host
    ->                   from snl_show);
+-----------+
| name      |
+-----------+
| 강동원    |
| 유재석    |
| 차승원    |
| 이수현    |
+-----------+

ㄴ SNL에 출연한 적이 있는 연예인 이름 조회


  • ALL
    : 서브쿼리 결과를 모두 만족하면(비교연산자 사용)
SELECT column_names
FROM table_name
WHERE column_name = ALL (SELECT column_name
			   			 FROM table_name
               			 WHERE condition)
ORDER BY column_names;
mysql> select name
    -> from celeb
    -> where name = all (select host
    ->                   from snl_show
    ->                   where id = 1);
+-----------+
| name      |
+-----------+
| 강동원    |
+-----------+

─ Multiple Column(연관 서브쿼리)

하나 이상의 행을 검색하는 서브쿼리

  • 서브쿼리 내에 메인쿼리 컬럼이 같이 사용되는 경우
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;
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엔터테인먼트       |
+-----------+------+----------------------+

ㄴ 강동원과 성별, 소속사가 같은 연예인의 이름,성별, 소속사를 조회

from.제로베이스 데이터 취업스쿨 강의
profile
SOWA
이전 포스트

45.SQL-11

0개의 댓글