SQL - [Scalar Functions | UCASE, LCASE, MID, LENGTH, ROUND, NOW, FORMAT]

솔비·2024년 1월 25일

📚 SQL. 관계형 데이터베이스

목록 보기

12/19

UCASE

🧷 영문을 대문자로 반환하는 함수

mysql> SELECT UCASE('This is ucase Test.');
+------------------------------+
| UCASE('This is ucase Test.') |
+------------------------------+
| THIS IS UCASE TEST.          |
+------------------------------+
1 row in set (0.15 sec)

$15 가 넘는 메뉴를 대문자로 조회

mysql> SELECT UCASE(menu)
    ->  FROM sandwich
    -> WHERE price > 15;
+----------------------------------+
| UCASE(menu)                      |
+----------------------------------+
| BLT                              |
| FRIED BOLOGNA                    |
| WOODLAND MUSHROOM                |
| ROAST BEEF                       |
| PB&L                             |
| BELGIAN CHICKEN CURRY SALAD      |
| LOBSTER ROLL                     |
| SMOKED SALMON SALAD              |
| ATOMICA CEMITAS                  |
| GRILLED LAUGHING BIRD SHRIMP AND |
| HAM AND RACLETTE PANINO          |
| THE HAWKEYE                      |
| CHICKEN DIP                      |
| WILD BOAR SLOPPY JOE             |
| MEATBALL SUB                     |
| CORNED BEEF                      |
| TURKEY CLUB                      |
| FALAFEL                          |
| CRAB CAKE                        |
| CHICKEN SCHNITZEL                |
| TOASTED PIMIENTO CHEESE          |
| VEGETARIAN PANINO                |
| PASTRAMI                         |
| SMOKED HAM                       |
| JIBARITO                         |
| SHAVED PRIME RIB                 |
| TUNA SALAD                       |
| PARAMOUNT REUBEN                 |
| THE ISTANBUL                     |
| B.A.D.                           |
| DUCK CONFIT AND MOZZARELLA       |
| CROQUE MONSIEUR                  |
| GREEN GARBANZO                   |
| TUSCAN CHICKEN                   |
| THE MARTY                        |
| WHITEFISH                        |
| CAULIFLOWER MELT                 |
| CUBANA                           |
| KUFTA                            |
| DEBBIE’S EGG SALAD               |
| BEEF CURRY                       |
| LE VÉGÉTARIEN                    |
| THE GATSBY                       |
+----------------------------------+
43 rows in set (0.15 sec)

LCASE

🧷 영문을 소문자로 반환하는 함수

mysql> SELECT LCASE('This is LCASE Test.');
+------------------------------+
| LCASE('This is LCASE Test.') |
+------------------------------+
| this is lcase test.          |
+------------------------------+
1 row in set (0.14 sec)

$5 가 안되는 메뉴를 소문자로 조회

mysql> SELECT LCASE(menu), price
    -> FROM sandwich
    -> WHERE price < 5;
+--------------+-------+
| LCASE(menu)  | price |
+--------------+-------+
| meatball sub |   4.5 |
+--------------+-------+
1 row in set (0.15 sec)

MID

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

mysql> SELECT MID('This is MID Test.',1,4);
+------------------------------+
| MID('This is MID Test.',1,4) |
+------------------------------+
| This                         |
+------------------------------+
1 row in set (0.15 sec)

mysql> SELECT MID('This is MID Test.', 6,5);
+-------------------------------+
| MID('This is MID Test.', 6,5) |
+-------------------------------+
| is MI                         |
+-------------------------------+
1 row in set (0.17 sec)

mysql> SELECT MID('This is MID Test.',-4,4);
+-------------------------------+
| MID('This is MID Test.',-4,4) |
+-------------------------------+
| est.                          |
+-------------------------------+
1 row in set (0.16 sec)

mysql> Select MID('This is MID Test.',-8,3);
+-------------------------------+
| MID('This is MID Test.',-8,3) |
+-------------------------------+
| ID                            |
+-------------------------------+
1 row in set (0.15 sec)

11위 카페이름 중 두번째 단어만 조회 - 6번 위치에서 4글자

mysql> SELECT cafe
    -> FROM sandwich
    -> WHERE ranking = 11;
+-----------+
| cafe      |
+-----------+
| Lula Cafe |
+-----------+
1 row in set (0.15 sec)

mysql> SELECT MID(cafe, 6,4)
    -> FROM sandwich
    -> WHERE ranking = 11;
+----------------+
| MID(cafe, 6,4) |
+----------------+
| Cafe           |
+----------------+
1 row in set (0.15 sec)

LENGTH

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

mysql> SELECT LENGTH('This is len test');
+----------------------------+
| LENGTH('This is len test') |
+----------------------------+
|                         16 |
+----------------------------+
1 row in set (0.15 sec)

#문자가 없을경우 0
mysql> SELECT LENGTH('');
+------------+
| LENGTH('') |
+------------+
|          0 |
+------------+
1 row in set (0.15 sec)

#공백도 문자이므로 1
mysql> SELECT LENGTH(' ');
+-------------+
| LENGTH(' ') |
+-------------+
|           1 |
+-------------+
1 row in set (0.14 sec)

#null은 길이가 없음
mysql> SELECT LENGTH(null);
+--------------+
| LENGTH(null) |
+--------------+
|         NULL |
+--------------+
1 row in set (0.15 sec)

sandwich 테이블에서 Top 3의 주소 길이를 검색

mysql> SELECT LENGTH(address)
    -> FROM sandwich
    -> WHERE ranking <= 3;
+-----------------+
| LENGTH(address) |
+-----------------+
|              19 |
|              18 |
|              16 |
+-----------------+
3 rows in set (0.15 sec)

ROUND

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

📌 위치 미기재 시 default 0 (소수점자리 0에서 반올림)
일단위 위치는 -1 / 십단위 위치는 -2

mysql> SELECT ROUND(315.625);
+----------------+
| ROUND(315.625) |
+----------------+
|            316 |
+----------------+
1 row in set (0.15 sec)

mysql> SELECT ROUND(315.625,0);
+------------------+
| ROUND(315.625,0) |
+------------------+
|              316 |
+------------------+
1 row in set (0.15 sec)

sandwich 테이블에서 소수점 자리는 반올림해서 1달러 단위까지만 표시 (최하위 3개만 표시)

mysql> SELECT ranking, price, ROUND(price,0)
    -> FROM sandwich
    -> ORDER BY ranking DESC
    -> LIMIT 3;
+---------+-------+----------------+
| ranking | price | ROUND(price,0) |
+---------+-------+----------------+
|      50 |  6.85 |              7 |
|      49 |  8.75 |              9 |
|      48 |   7.5 |              8 |
+---------+-------+----------------+
3 rows in set (0.14 sec)

NOW

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

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2024-01-25 05:44:30 |
+---------------------+
1 row in set (0.15 sec)

FORMAT

🧷 숫자를 천단위 콤마가 있는 형식으로 반환하는 함수 (반올림까지)

📌 round와 마찬가지로 표시할 자리수의 숫자를 적어주면됨
일의자리 -1 / 십의자리 -2 ..

mysql> SELECT FORMAT(12345.6789,0);
+----------------------+
| FORMAT(12345.6789,0) |
+----------------------+
| 12,346               |
+----------------------+
1 row in set (0.15 sec)

mysql> SELECT FORMAT(12345.6789,2);
+----------------------+
| FORMAT(12345.6789,2) |
+----------------------+
| 12,345.68            |
+----------------------+
1 row in set (0.15 sec)

mysql> SELECT FORMAT(12345.6789,10);
+-----------------------+
| FORMAT(12345.6789,10) |
+-----------------------+
| 12,345.6789000000     |
+-----------------------+
1 row in set (0.15 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.01 sec)

연습문제

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

mysql> use zerobase
Database changed
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                       |
+---------------------------------+----------------------------------+
50 rows in set (0.15 sec)

sandwich 테이블에서 10위 메뉴의 마지막 단어를 조회하세요.

mysql> SELECT menu
    -> FROM sandwich
    -> WHERE ranking = 10;
+----------------------------------+
| menu                             |
+----------------------------------+
| Grilled Laughing Bird Shrimp and |
+----------------------------------+
1 row in set (0.15 sec)

mysql> SELECT MID(menu, -3,3)
    -> FROM sandwich
    -> WHERE ranking = 10;
+-----------------+
| MID(menu, -3,3) |
+-----------------+
| and             |
+-----------------+
1 row in set (0.21 sec)

sandwich 테이블에서 메뉴 이름의 평균 길이를 조회하세요

mysql> SELECT AVG ( LENGTH(menu) )
    -> FROM sandwich;
+----------------------+
| AVG ( LENGTH(menu) ) |
+----------------------+
|              13.9600 |
+----------------------+
1 row in set (0.15 sec)

oil_price 테이블에서 가격을 십원단위에서 반올림해서 조회하세요

mysql> SELECT FORMAT(가격,-1)
    -> FROM oil_price;
+-------------------+
| FORMAT(가격,-1)   |
+-------------------+
| 1,484             |
| 1,485             |
| 1,498             |
| 1,498             |
| 1,509             |
| 1,598             |
| 1,635             |
| 2,160             |
| 1,498             |
+-------------------+
9 rows in set (0.00 sec)

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

mysql> SELECT FORMAT(가격,0)
    -> FROM oil_price
    -> WHERE ROUND(가격,-2) >= 2000;
+------------------+
| FORMAT(가격,0)   |
+------------------+
| 2,160            |
+------------------+
1 row in set (0.00 sec)