굉장히 엄청나!

문제 1 - 자동차 대여 기록에서 장기/단기 대여 구분하기

1. 문제 

https://school.programmers.co.kr/learn/courses/30/lessons/151138

2. 작성한 코드

• DATEDIFF () : 두개의 날짜값의 차이를 int로 반환하는 내장함수이다.
  
• 마자막 날짜에서 시작 날짜의 차이를 구하면 하루를 포함하지 않는 값이 나오므로 +1 해줘야 한다.

SELECT HISTORY_ID
        , CAR_ID
        , DATE_FORMAT(START_DATE, "%Y-%m-%d") AS START_DATE
        , DATE_FORMAT(END_DATE, "%Y-%m-%d") AS END_DATE
        , (CASE 
            WHEN (DATEDIFF(END_DATE, START_DATE) + 1) >= 30 THEN "장기 대여"
            ELSE "단기 대여"
            END) as RENT_TYPE
from CAR_RENTAL_COMPANY_RENTAL_HISTORY
where START_DATE like '2022-09%'
order by HISTORY_ID desc

문제 2 - 특정 옵션이 포함된 자동차 리스트 구하기

1. 문제 

https://school.programmers.co.kr/learn/courses/30/lessons/157343

2. 작성한 코드

SELECT CAR_ID, CAR_TYPE, DAILY_FEE, OPTIONS
from CAR_RENTAL_COMPANY_CAR
where OPTIONS LIKE '%네비게이션%'
order by CAR_ID DESC

문제 3 - 과일로 만든 아이스크림 고르기

1. 문제

https://school.programmers.co.kr/learn/courses/30/lessons/133025

2. 작성한 코드

SELECT A.FLAVOR
FROM FIRST_HALF A, ICECREAM_INFO B
WHERE (A.FLAVOR = B.FLAVOR 
       AND B.INGREDIENT_TYPE = "fruit_based"
 AND A.TOTAL_ORDER > 3000)
ORDER BY A.TOTAL_ORDER DESC

문제 4 - 인기있는 아이스크림

1. 문제 

https://school.programmers.co.kr/learn/courses/30/lessons/133024

2. 작성한 코드

SELECT FLAVOR
FROM FIRST_HALF
ORDER BY TOTAL_ORDER DESC, SHIPMENT_ID

문제 5 - 흉부외과 또는 일반외과 의사 목록 출력하기

1. 문제

https://school.programmers.co.kr/learn/courses/30/lessons/132203

2. 작성한 코드

SELECT DR_NAME
        , DR_ID
        , MCDP_CD
        , DATE_FORMAT(HIRE_YMD, '%Y-%m-%d') AS HIRE_YMD
FROM DOCTOR
where MCDP_CD IN ("CS", "GS")
order by HIRE_YMD desc, DR_NAME

문제 6 - 12세 이하인 여자 환자 목록 출력하기

1. 문제

https://school.programmers.co.kr/learn/courses/30/lessons/132201

2. 작성한 코드

SELECT PT_NAME,	PT_NO, GEND_CD,	AGE, IFNULL(TLNO, "NONE") AS TLNO
FROM PATIENT
WHERE GEND_CD = "W"
    AND AGE <= 12
ORDER BY AGE DESC, PT_NAME

문제 7 - 가장 비싼 상품 구하기

1. 문제

https://school.programmers.co.kr/learn/courses/30/lessons/131697

2. 작성한 코드

SELECT MAX(PRICE) AS MAX_PRICE
FROM PRODUCT

문제 8 - 조건에 맞는 회원수 구하기

1. 문제

https://school.programmers.co.kr/learn/courses/30/lessons/131697

2. 작성한 코드

SELECT COUNT(USER_ID) AS USERS
FROM USER_INFO
WHERE JOINED LIKE "2021%"
    AND AGE BETWEEN 20 AND 29

문제 9 - 나이 정보가 없는 회원 수 구하기

1. 문제

https://school.programmers.co.kr/learn/courses/30/lessons/131528

2. 작성한 코드

SELECT COUNT(USER_ID) AS USERS
FROM USER_INFO
WHERE AGE IS NULL

문제 10 - 경기도에 위치한 식품창고 목록 출력하기

1. 문제

https://school.programmers.co.kr/learn/courses/30/lessons/131114

2. 작성한 코드

SELECT WAREHOUSE_ID, WAREHOUSE_NAME, ADDRESS, IFNULL(FREEZER_YN, "N")
FROM FOOD_WAREHOUSE
WHERE ADDRESS LIKE "경기도%"
ORDER BY  WAREHOUSE_ID

문제 11 - 강원도에 위치한 생산공장 목록 출력하기

1. 문제

https://school.programmers.co.kr/learn/courses/30/lessons/131112

2. 작성한 코드

SELECT FACTORY_ID, FACTORY_NAME, ADDRESS
FROM FOOD_FACTORY
WHERE ADDRESS LIKE "강원도%"
ORDER BY FACTORY_ID

문제 12 - 경기도에 위치한 식품창고 목록 출력하기

1. 문제

https://school.programmers.co.kr/learn/courses/30/lessons/59415

2. 작성한 코드

SELECT MAX(DATETIME) AS 시간
FROM ANIMAL_INS

문제 13 - 이름이 있는 동물의 아이디

1. 문제

https://school.programmers.co.kr/learn/courses/30/lessons/59407

2. 작성한 코드

SELECT ANIMAL_ID
FROM ANIMAL_INS
WHERE NAME IS NOT NULL
ORDER BY ANIMAL_ID

문제 14 - 상위 n개 레코드

1. 문제

https://school.programmers.co.kr/learn/courses/30/lessons/59405

2. 작성한 코드

• MySQL에서 상위 N개의 데이터를 출력하기 위해선 LIMIT N 함수를 사용한다.
• ORACLE : ROWNUM < N
• MS - SQL : TOP N, TOP N WITH TIES 

SELECT NAME
FROM ANIMAL_INS
ORDER BY DATETIME
LIMIT 1

문제 15 - 여러 기준으로 정렬하기

1. 문제

https://school.programmers.co.kr/learn/courses/30/lessons/59404

2. 작성한 코드

SELECT ANIMAL_ID, NAME, DATETIME
FROM ANIMAL_INS
ORDER BY NAME, DATETIME DESC

문제 16 - 동물의 아이디와 이름

1. 문제

https://school.programmers.co.kr/learn/courses/30/lessons/59403

2. 작성한 코드

SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
ORDER BY ANIMAL_ID

문제 17- 이름이 없는 동물의 아이디

1. 문제

https://school.programmers.co.kr/learn/courses/30/lessons/59039

2. 작성한 코드

SELECT ANIMAL_ID
FROM ANIMAL_INS
WHERE NAME IS NULL
ORDER BY ANIMAL_ID

문제 18 - 어린 동물 찾기

1. 문제

https://school.programmers.co.kr/learn/courses/30/lessons/59037#fn1

2. 작성한 코드

SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE INTAKE_CONDITION != "Aged"
ORDER BY ANIMAL_ID

문제 19 - 아픈 동물 찾기

1. 문제

https://school.programmers.co.kr/learn/courses/30/lessons/59036

2. 작성한 코드

SELECT ANIMAL_ID, NAME
from ANIMAL_INS
where INTAKE_CONDITION = "Sick"
order by ANIMAL_ID

문제 20 - 역순 정렬하기

1. 문제

https://school.programmers.co.kr/learn/courses/30/lessons/59035

2. 작성한 코드

SELECT NAME, DATETIME
FROM ANIMAL_INS
ORDER BY ANIMAL_ID DESC

문제 21 - 모든 레코드 조회하기

1. 문제

https://school.programmers.co.kr/learn/courses/30/lessons/59034

2. 작성한 코드

SELECT ANIMAL_ID
        , ANIMAL_TYPE
        , DATETIME
        , INTAKE_CONDITION
        , NAME
        , SEX_UPON_INTAKE
FROM ANIMAL_INS
ORDER BY ANIMAL_ID

DB 프로그램 실습

🍀 DB프로그램

• X : 입력 값 바뀜
• △ : 고정으로 둘 수도 있고, 안 둘 수도 있다
• 나머지 : 무조건 고정 → class로 설정
  
  1. 데이터베이스 설정 정보 준비
  2. DB 접속하기 : 커넥션(connect)
  3. 커서 받아오기 : cursor()
  4. 구문 작성하기 : sql(조회, 입력, 수정, 삭제)  --- X
  5. 구문 실행하기 :  execute() -> 결괏값은 체크 --- △
  6. 조회인 경우 커서에서 데이터 추출하기 --- △
          → 한건 : fetchone()
          → 여러 건 : fetchall()
  7. 조회결과 데이터프레임 또는 웹페이지에 출력 --- △
      →  데이터프레임 : pd.DataFrame(여러 건), pd.DataFrame([한건])
  8. 조회가 아닌 경우 : 처리 결과 조건으로 확인하기 if "처리 성공" else "처리 실패" --- △
  9. DB정보 반환하기
          - 반환 순서 : cursor > connect

🍀 class 설정

1. 클래스 생성
   - 클래스 이름 : PowerClass
2. 클래스 내에 함수들 정의
3. 외부에서 조회/입력/수정/삭제 처리하기

🍀 작성한 코드

class PowerClass:
    def __init__(self):
        self.conn = None
        self.host = "localhost"
        self.user = "gjuser"
        self.password = "dbdb"
        self.db = "gjdb"
        self.charset = "utf8"
        self.cursorclass = pymysql.cursors.DictCursor
        self.autocommit = True
        
    def connect(self):
        try:
            self.conn = pymysql.connect(
                host=self.host,
                user=self.user,
                password=self.password,
                db=self.db,
                charset=self.charset,
                autocommit=self.autocommit,
                cursorclass=self.cursorclass
            )
            print("DB접속 성공 >>> ", self.conn)
        except Exception as e:
            print("DB Server Checking...", e)

    
    def cursor(self):
        cur = self.conn.cursor()
        return cur

    def close(self):
        self.cur = self.conn.cursor()
        try:
            self.cur.close()
            self.conn.close()
            print("접속종료")
        except :
            print("이미 모든 커서와 접속 정보가 반납되었습니다.")

def read(sql, cur):
    if sql[:6] == "Select" :
        cur.execute(sql)
        rows = cur.fetchall()
        ymd_power_df = pd.DataFrame(rows)
        print(ymd_power_df)
    else:
        print('다시입력')

def insert(sql, cur):
    if sql[:6] == "Insert" :
        rs_cnt = cur.execute(sql)
        if rs_cnt > 0:
            print(f'{rs_cnt}건이 입력되었습니다.')
        else:
            print(f'입력되지 않았습니다')
    else:
        print('다시입력')

def update(sql, cur):
    if sql[:6] == "Update" :
        rs_cnt = cur.execute(sql)
        if rs_cnt > 0:
            print(f'{rs_cnt}건이 수정되었습니다.')
        else:
            print(f'수정되지 않았습니다')
    else:
        print('다시입력')
            

def delete(sql, cur):
    if sql[:6] == "Update" :
        rs_cnt = cur.execute(sql)
        if rs_cnt > 0:
            print(f'{rs_cnt}건이 삭제되었습니다.')
        else:
            print(f'삭제되지 않았습니다')
    else :
        print('다시입력')

def main():
    db = PowerClass()
    db.connect()
    cur = db.cursor()
     
    
    while True:
        choice = input("""
                        < 조회/입력/수정/삭제 하기>
                        1. 조회
                        2. 입력
                        3. 수정
                        4. 삭제
                        5. 접속종료
                        원하는 번호(1~4)를 선택하세요
                        """)
        

        if choice == "1":
            sql = input("""
                        sql 작성하기
                    """)
            read(sql, cur)
               
        elif choice == "2":
            sql = input("""
                        sql 작성하기
                    """)
            insert(sql, cur)
                                                                                              
        elif choice == "3":
            sql = input("""
                        sql 작성하기
                    """)
            update(sql, cur)
        
        elif choice == "4":
            sql = input("""
                        sql 작성하기
                    """)
            delete(sql, cur)

        elif choice == "5":
            db.close()
            break
               
        else:
            print("다시 선택해주세요")

if __name__ == "__main__":
    main()

DB접속 성공 >>>  <pymysql.connections.Connection object at 0x000001E355B99150>

                        < 조회/입력/수정/삭제 하기>
                        1. 조회
                        2. 입력
                        3. 수정
                        4. 삭제
                        5. 접속종료
                        원하는 번호(1~4)를 선택하세요
                         2

                        sql 작성하기
                     Insert Into time_power_demand(         ymd, time, power     ) Values (         '2020-12-31', '24시', 1234     )
1건이 입력되었습니다.

                        < 조회/입력/수정/삭제 하기>
                        1. 조회
                        2. 입력
                        3. 수정
                        4. 삭제
                        5. 접속종료
                        원하는 번호(1~4)를 선택하세요
                         1

                        sql 작성하기
                     Select * From time_power_demand         Where ymd = '2020-12-31'
          ymd time   power
0  2020-12-31  24시  1234.0

                        < 조회/입력/수정/삭제 하기>
                        1. 조회
                        2. 입력
                        3. 수정
                        4. 삭제
                        5. 접속종료
                        원하는 번호(1~4)를 선택하세요
                         3

                        sql 작성하기
                     Update time_power_demand set power = 5679         Where ymd = '2020-12-31'             And time = '24시'
1건이 수정되었습니다.

                        < 조회/입력/수정/삭제 하기>
                        1. 조회
                        2. 입력
                        3. 수정
                        4. 삭제
                        5. 접속종료
                        원하는 번호(1~4)를 선택하세요
                         1

                        sql 작성하기
                     Select * From time_power_demand         Where ymd = '2020-12-31'
          ymd time   power
0  2020-12-31  24시  5679.0

                        < 조회/입력/수정/삭제 하기>
                        1. 조회
                        2. 입력
                        3. 수정
                        4. 삭제
                        5. 접속종료
                        원하는 번호(1~4)를 선택하세요
                         4

                        sql 작성하기
                     Delete         From time_power_demand         Where ymd = '2020-12-31'             And time = '24시'
1건이 삭제되었습니다.

                        < 조회/입력/수정/삭제 하기>
                        1. 조회
                        2. 입력
                        3. 수정
                        4. 삭제
                        5. 접속종료
                        원하는 번호(1~4)를 선택하세요
                         1

                        sql 작성하기
                     Select * From time_power_demand         Where ymd = '2020-12-31'
Empty DataFrame
Columns: []
Index: []

                        < 조회/입력/수정/삭제 하기>
                        1. 조회
                        2. 입력
                        3. 수정
                        4. 삭제
                        5. 접속종료
                        원하는 번호(1~4)를 선택하세요
                         5
접속종료

DB 프로그램 순서

1. 데이터베이스 설정 정보 준비
2. DB 접속하기 : 커넥션(connect)
3. 커서 받아오기 : cursor()
4. 구문 작성하기 : sql(조회, 입력, 수정, 삭제)
5. 구문 실행하기 :  execute() -> 결과값은 체크
6. 조회인 경우 커서에서 데이터 추출하기
        -> 한건 : fetchone()
        -> 여러건 : fetchall()
7. 조회결과 데이터프레임 또는 웹페이지에 출력
    -> 데이터프레임 : pd.DataFrame(여러건), pd.DataFrame([한건])
8. 조회가 아닌 경우 : 처리 결과 조건으로 확인하기 if "처리 성공" else "처리 실패"
9. DB정보 반환하기
        - 반환 순서 : cursor > connect
    

데이터 조회하기

🍀 데이터베이스 연결

import pymysql
### 접속 정보
# - 접속 허가를 위한 정보
# - 접속 id or 도메인
host = "localhost"
# - 사용자계정
user = "gjuser"
# - 패스워드
password = "dbdb"
# - 데이터베이스명
db = "gjdb"
# - 한글처리
charset = "utf8"
# - 조회시 컬럼명을 동시에 보여줄지 여부 설정
cursorclass = pymysql.cursors.DictCursor

🍀 DB 접속하기

• connect로 서버에 연결 허가를 요청하여 conn에 허가를 받아 옴

try :
    conn = pymysql.connect(host = host,
                           user = user,
                           password = password,
                           db = db,
                           charset = charset,
                           # autocommit = autocommit,
                           cursorclass = cursorclass)
    print("DB접속 성공 >>> ", conn)
except :
    print("DB Server Checking...")

🍀 커서 받아오기

cur = conn.cursor()
cur

 🍀 Select 문 생성 후 DB에게 요청 및 응답 받기

• execute가 "Select * from time_power_demand"라는 요청문을 서버에게 가지고 가서 그에 대한 답을 가져온다.

sql = "Select * from time_power_demand"
rs_cnt = cur.execute(sql)
print(f"{rs_cnt}건이 조회 되었습니다.")

🍀 조회결과가 여러 건인 경우

• non 맵핑 방식
• df.to_sql은 orm( object relation mapping, 객체 맵핑 방식)
• cursorclass설정 시 딕셔너리 형태로 컬럼명과 같이 가져옴
• cursorclass설정 안하면 튜플로 가져와서 컬럼명 없이 값만 나옴
  - [(), ()...] 

rows = cur.fetchall()
rows

🍀 조회결과 데이터프레임으로 출력

• cursorclass설정을 안해서 컬럼명 없이 받아온 경우
  - ymd_power_df = pd.DataFrame(rows, columns=["ymd", "time", "power"]) 처럼 컬럼명 지정해서 코드 작성

ymd_power_df = pd.DataFrame(rows)
ymd_power_df

🍀 조회결과가 한 건인 경우

• 한건 조회를 위한 sql 구문 생성하기
• sql 구문 쓸 때 컴마 뒤에 띄어쓰기 꼭 하기

sql = """
        Select * From time_power_demand
        Where ymd = '2021-01-01'
            And time = '1시'
"""
sql

• execute로 sql 구문을 DB서버에 요청하고, 결과 받아오기 

cur.execute(sql)

한 건의 결과가 나왔다는 의미

• 받아오고 데이터 추출하기

row = cur.fetchone()
row

• 데이터프레임에 담아서 보여주기
  - 데이터가 한 건만 추출 될 때는 딕셔너리 타입으로 추출되기 때문에 데이터프레임에 데이터를 담을 때는 리스트 타입으로 넣어야함

ymd_power_df = pd.DataFrame([row])
ymd_power_df

데이터 입력하기

🍀 입력을 위한 구문 생성하기

sql = """
    Insert Into time_power_demand(
        ymd, time, power
    ) Values (
        '2020-12-31', '24시', 1234
    )
"""
sql

🍀 구문 처리하고 결과 값 받아오기

• 저장 요청 시에는 결과값이 숫자값으로 반환된다
  - 몇 건이 수행 됐는지 숫자값으로 나옴

rs_cnt = cur.execute(sql)
rs_cnt
print(f'{rs_cnt}건이 처리되었습니다')

🍀 처리가 잘 되었는지 확인하기

• 0보다 크면 '1건이 입력되었습니다.' 출력
• 0이하인 경우에는  "입력되지 않았습니다." 출력 

if rs_cnt > 0 :
    print(f'{rs_cnt}건이 입력되었습니다.')
else :
    print(f'입력되지 않았습니다')

데이터 수정하기

### ymd가 2020-12-31이고, 시간이 24시 인 행을 찾아서 power의 값을 5678로 수정하기

sql = """
        Update time_power_demand set power = 5679
        Where ymd = '2020-12-31'
            And time = '24시'    
"""
rs_cnt = cur.execute(sql)
if rs_cnt > 0 :
    print(f'{rs_cnt}건이 수정되었습니다.')
else :
    print(f'수정되지 않았습니다')

데이터 삭제하기

### ymd가 2020-12-31이고, 시간이 24시 인 행을 찾아서 삭제하기

sql = """
        Delete
        From time_power_demand
        Where ymd = '2020-12-31'
            And time = '24시'    
"""
rs_cnt = cur.execute(sql)
if rs_cnt > 0 :
    print(f'{rs_cnt}건이 삭제되었습니다.')
else :
    print(f'삭제되지 않았습니다')

데이터 조회 문제

🍀조회해보기

• 한번도 구매한적이 없는 회원정보를 조회하기
• 조회 컬럼 : 회원아이디, 회원이름

sql = """
    SELECT mem_id, mem_name
    FROM member
    WHERE not exists (SELECT cart_member FROM cart
WHERE cart_member = mem_id)
"""
rs_cnt = cur.execute(sql)
if rs_cnt > 0 :
    print(f'{rs_cnt}건이 조회되었습니다.')
else :
    print(f'조회되지 않았습니다')

• 커서가 가지고 있는 결과 추출하여 데이터프레임 형식으로 나타내기

rows = cur.fetchall()
noMember = pd.DataFrame(rows)
noMember

종료하기

🍀커서 및 커넥션 정보 반납(종료)하기

try:
    cur.close()
    conn.close()
except :
    print("이미 모든 커서와 접속 정보가 반납되었습니다.")

📝문제 1 - 평균 일일 대여 요금 구하기

✏️ 작성한 코드

SELECT ROUND(AVG(DAILY_FEE)) as AVERAGE_FEE
from CAR_RENTAL_COMPANY_CAR
where CAR_TYPE = 'SUV'
GROUP BY CAR_TYPE;

📝문제 2 - 조건에 맞는 도서 리스트 출력하기

✏️ 작성한 코드

SELECT BOOK_ID
	, DATE_FORMAT(PUBLISHED_DATE, '%Y-%m-%d') as PUBLISHED_DATE
from BOOK
where PUBLISHED_DATE like '2021%'
    AND CATEGORY = '인문'
order by PUBLISHED_DATE;

📝문제

✏️작성한 코드

 어려웠던 점은 조건을 줄 때 CAR_ID에 대한 조건을 주고 전체 행에 대한 조건을 따로 줘야 한다는 것이다.  먼저 대여 시작일을 기준으로 2022년 8월 부터 2022년 10월까지 총 대여 횟수가 5회 이상인 자동차들에 대한 조건을 서브쿼리로 준다. 그리고 해당 기간 동안의 월별 CAR_ID 별 총 대여 횟수 리스트를 출력한다.

 작성해놓고 왜 이렇게 적었는지 설명을 못하겠다... 헷

SELECT SUBSTRING(START_DATE,6,2) AS MONTH
		, CAR_ID
    	, COUNT(CAR_ID) AS RECORDS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE CAR_ID IN (
    		SELECT CAR_ID 
    			FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
   					 WHERE SUBSTRING(START_DATE,1,7) BETWEEN '2022-08' AND '2022-10'
    		GROUP BY CAR_ID
    		HAVING COUNT(CAR_ID) >= 5)
	AND SUBSTRING(START_DATE,1,7) BETWEEN '2022-08' AND '2022-10'
GROUP BY SUBSTRING(START_DATE,6,2), CAR_ID
ORDER BY MONTH, CAR_ID DESC;

코드 실행 시 아래와 같은 형식으로 데이터가 출력된다.

🎄사용자  정의 함수

• 상품테이블에서 아래 컬럼 조회하기
  - 서브쿼리 사용 (join 사용하지 않기)
  - 조회컬럼 : 상품명, 상품분류명, 상품분류코드

 SELECT prod_name
 	, (select lprod_nm FROM lprod WHERE lprod_gu = prod_lgu) AS lprod_nm
    	, prod_lgu
 FROM prod;

• 상품분류명을 추출하는 함수 생성하기
  - IF문으로 NULL값 있는지 확인
  - Delimiter 뒤에는 무조건 띄어쓰기
  - 문자열이 맞지 않아서 CHARACTER SET UTF8MB4 적용

 -- 함수 구문 시작
Delimiter //

-- 사용자 정의함수
CREATE FUNCTION udfGetLprodNm(paramGu VARCHAR(10))

-- 리턴타입 정의
RETURNS VARCHAR(50) CHARACTER SET UTF8MB4

-- 함수 기능 시작
BEGIN
	
	-- 반환할 값을 저장할 변수 선언
	DECLARE result VARCHAR(50) CHARACTER SET UTF8MB4;

	-- 상품분류명을 추출하는 sql구문 작성
	SELECT lprod_nm INTO result
	FROM lprod
	WHERE lprod_gu = paramGu;
	
	-- 조회결과가 있는지 확인
	if result IS NULL then
	 SET result = 'null';
	END if;
	
	-- 반환하기
	RETURN result;
    
-- 함수 기능 종료
END //

-- 함수 전체 구문 종료
Delimiter ;

•  생성한 함수 호출하여 처리하기

SELECT prod_name
	,defGetLprodNm(prod_lgu) AS lprod_nm
    	,prod_lgu
FROM prod;

위에서 서브쿼리를 사용하여 작성한 코드와 동일한 결과

찾은 행 74 개

사용자 함수 정의 문제

문제 1

• 회원이름, 성별 조회하기
• 성별은 남성, 여성으로 조회
• 함수 이름 : udfGetMenWomen
  
• udfGetMenWomen 함수 정의
  - 남성 / 여성 추출하기

Delimiter //

CREATE FUNCTION udfGetMenWomen(paramNum INT)

-- 리턴 타입 정의
RETURNS VARCHAR(4) CHARACTER SET UTF8
BEGIN
	-- 반환할 변수 선언
	DECLARE result VARCHAR(4) CHARACTER SET UTF8;
	
	-- 남여 구분을 위한 조건문 처리
	if MOD(SUBSTRING(paramNum, 1, 1), 2) = 0 THEN
		SET result = '여성';
	ELSE 
		SET result = '남성';
	END if;

	RETURN result;
	
END //

Delimiter ;

• 생성한 함수 호출하여 처리하기

SELECT mem_name
, udfGetMenWomen(mem_regno2) AS gender
FROM member;

문제 2

• 새로운 주문이 발생했을 때 신규주문번호를 발급하는 함수 생성하기
• 함수이름 : udfGetNewCartNo()
  
• udfGetNewCartNo 함수 정의

Delimiter //

CREATE FUNCTION udfGetNewCartNo(paramNum VARCHAR(50))

RETURNS VARCHAR(50) CHARACTER SET UTF8

BEGIN
	DECLARE result VARCHAR(50) CHARACTER SET UTF8;
	
	if SUBSTRING(paramNum,1,8) = CURDATE() then	
		set result = paramNum + 1;
	ELSE
		SET result = concat(DATE_FORMAT(CURDATE(),'%Y%m%d'),'00001');
	END if;
	
	RETURN result;

END //

Delimiter ;

• 생성한 함수 호출하여 처리하기

SELECT udfGetNewCartNo(MAX(cart_no)) AS newnum
FROM cart;

• udfGetNewCartNo 함수 정의 - 강사님 

--
Delimiter //

CREATE FUNCTION udfGetNewCartNo()

RETURNS VARCHAR(13) CHARACTER SET UTF8

BEGIN
	DECLARE result VARCHAR(13) CHARACTER SET UTF8;
	
	SELECT if (MAX(cart_no) IS NOT NULL,
					MAX(cart_no) + 1,
						CONCAT(replace(CURDATE(), '-', ''),
								'00001')) INTO result
													
	FROM cart
	WHERE SUBSTRING(cart_no, 1, 8) = CURDATE();
	
	RETURN result;

END //

Delimiter ;

• 생성한 함수 호출하여 처리하기 - 강사님
  

SELECT udfGetNewCartNo();

🎄View

•  VIEW : 가상테이블
• 자주 사용되거나, SQL 구문이 긴 경우 조회 목적으로만 사용
• 미리 객체화 시켜서 테이블 처럼 사용하는 방식
• 조회만 가능하며, 입력/수정/삭제가 되지 않는다.
• 입력/수정/삭제가 되는 경우도 있지만, VIEW 사용 목적에 맞지 않는다.
  
• 구매 내역이 없는 회원 조회하기
  - 조회 컬럼 : 회원아이디, 회원이름

SELECT mem_id, mem_name
FROM member
WHERE mem_id NOT in (SELECT cart_member FROM cart);

• 구매내역이 없는 회원 view 생성

Delimiter //

CREATE VIEW viewNoCartMember AS

-- 조회할 select문 생성
SELECT mem_id, mem_name
FROM member
WHERE mem_id NOT in (SELECT cart_member 
FROM cart)//

Delimiter ;

• view 사용하기
  - 사용법 : 테이블과 동일

SELECT *
from viewNoCartMember;

🎄저장 프로시저(Stored Procedure; sp)

• 프로그램 영역이 아닌, DB자체에서 SQL구문을 관리하고자 할 때 사용
• 데이터베이스에 SQL구문을 객체화하여 호출방식으로만 사용하는 방식
  
  
• 구매내역이 없는 회원 프로시저 생성

Delimiter //

CREATE Procedure spNoCartMember()
Begin

-- 조회할 select문 생성
SELECT mem_id, mem_name
FROM member
WHERE mem_id NOT in (SELECT cart_member 
FROM cart);

END //
Delimiter ;

• 프로시저 호출하기

call spNoCartMember();

VIEW / 저장 프로시저 문제

문제 1

• 모든 거래처별 매출 금액의 총합 조회하기
• 단, 2005년도 주문내역
• 조회컬럼 : 거래처 코드, 거래처명, 매출금액의 총합
• 위 조회에 대한 select문을 view로 생성
• 프로시저에서 위에 생성한 view를 사용하여 call
• view이름 : viewGetBuyerAll
• 프로시저이름 : spGetBuyerAll
  
  
• viewGetBuyerAll VIEW 생성

Delimiter //

CREATE VIEW viewGetBuyerAll AS

	-- 조회할 select문 생성
	SELECT buyer_id
			, buyer_name
			, SUM(NVL(prod_sale*cart_qty,0)) AS total
	FROM buyer
		LEFT JOIN prod
			ON (buyer_id = prod_buyer)
		LEFT JOIN CART
			ON (prod_id = cart_prod 
					AND cart_no LIKE '2005%')
	GROUP BY buyer_id, buyer_name//

Delimiter ;

• spGetBuyerAll 프로시저 생성

Delimiter //

CREATE procedure spGetBuyerAll()
begin

	-- 조회할 select문 생성
	SELECT buyer_id
			, buyer_name
			, SUM(NVL(prod_sale*cart_qty,0)) AS total
	FROM buyer
		LEFT JOIN prod
			ON (buyer_id = prod_buyer)
		LEFT JOIN CART
			ON (prod_id = cart_prod 
					AND cart_no LIKE '2005%')
	GROUP BY buyer_id, buyer_name;
	
END //	

Delimiter ;

• VIEW / 프로시저 호출

SELECT *
FROM viewgetbuyerall;

CALL spGetBuyerAll();

🎄저장 모듈(Stored Module)

•  SQL 문장을 데이터베이스 서버에 저장하여 사용자와 애플리케이션 사이에서 공유할 수 있도록 만든 일종의 SQL 컴포넌트 프로그램이며, 독립적으로 실행되거나 다른 프로그램으로 부터 실행될 수 있는 완전한 실행 프로그램이다. Oracle의 저장 모듈에는 Procedure, User Defined Function, Trigger가 있다.
  
  
• Procedure와 Trigger의 차이점