CH16) Scalar Functions

사전준비
sandwich.csv 파일 준비

import mysql.connector
import pandas as pd

#sandwich csv파일을 db에 sandwich table에 insert하기 
import mysql.connector

conn = mysql.connector.connect(
    host ="database-.amazonaws.com" ,
    port='3306',
    user ="admin",
    password ="Supervis0r",
    database="zerobase"
)

df=pd.read_csv('sandwich.csv',encoding='utf-8')
#마지막 부분 데이터 보기 
df.tail()
#정보보기 
df.info()

sql ="create table sandwich (ranking int, cafe varchar(32), menu varchar(32), price float, address varchar(32))"
cursor= conn.cursor(buffered=True)
cursor.execute(sql)

cursor.execute("desc sandwich")
result = cursor.fetchall()

for row in result:
    print(row)

sql ="insert into sandwich values (%s,%s,%s,%s,%s)"

for i, row in df.iterrows():
    cursor.execute(sql, tuple(row))
    print(tuple(row))
    conn.commit()

conn.close()

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

1.ucase : 대문자치환
2.lcase: 소문자 치환
3.mid: 문자열 부분반환 (substr같은애)
4.length:문자열 길이 반환
5.round: 지정한 자리 숫자 반올림
6.now: 현재 날짜 및 시간 반환

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2024-02-13 13:16:08 |
+---------------------+
1 row in set (0.03 sec)

7.format : 숫자를 천단위 콤마가 있는 형식으로 반환

mysql> select format(123456.89456,0);
+------------------------+
| format(123456.89456,0) |
+------------------------+
| 123,457                |
+------------------------+
1 row in set (0.01 sec)

mysql> select format(123456.894562,2); 
+-------------------------+
| format(123456.894562,2) |
+-------------------------+
| 123,456.89              |
+-------------------------+

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

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.01 sec)

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

mysql> select ranking , cafe, mid(menu,-3,3) from sandwich where ranking=10; 
+---------+------+----------------+
| ranking | cafe | mid(menu,-3,3) |
+---------+------+----------------+
|      10 | Nana | and            |
+---------+------+----------------

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

mysql> select avg(length(menu)) from sandwich; 
+-------------------+
| avg(length(menu)) |
+-------------------+
|           13.9600 |
+-------------------+
1 row in set (0.01 sec)

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

select round(price, -2)  from oil_price  

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

mysql> select format(가격,0), 가격 
    -> from oil_price
    -> where round(가격,-2)>=2000