[혼공학습단, 혼공SQL] 6주차 Chap 07. 스토어드 프로시저, Chap08. SQL과 파이썬 연결

Jihun Kim·2023년 8월 20일
mysqlsql혼공혼공SQL혼자공부하는SQL
0

[혼공학습단] 혼자 공부하는 SQL

목록 보기
6/6
post-thumbnail

혼자 공부하는 SQL - 6주차

  • 6주차 마지막의 분량은 다음과 같습니다.

스토어드 프로시저

  • 스토어드 프로시저는 SQL에 프로그래밍 기능을 추가해 일반 프로그래밍과 비슷한 효과를 내는 것으로, 복잡한 SQL을 한 번에 실행 가능하게 해 줍니다.
  • 간단한 형식은 다음과 같습니다. begin ~ end 사이에 코드를 구현합니다.
delimiter  $$
create procedure 스토어드_프로시저_이름 ( in 또는 out 매개변수 )
begin

    # SQL 프로그래밍 코드 작성
    
end $$
delimiter ;
  • 호출은 아래와 같이 합니다.
call 스토어드_프로시저_이름(매개변수);
  • 삭제는 아래와 같이 합니다.
drop procedure user_proc;
  • 스토어드 프로시저는 매개변수를 사용할 수 있는데, 입력 매개변수는 스토어드 프로시저에 값을 전달하고, in 을 사용합니다. 출력 매개변수는 스토어드 프로시저에서 계산된 결과를 돌려받는데, out을 사용합니다.
  • 동적 SQL은 다이나믹하게 sql을 생성하고 실행하는데, prepare과 exeute문을 사용합니다.

스토어드 함수 / 커서

  • 스토어드 함수는 MySQL이 제공하는 함수 외에 직접 함수를 만들어 사용하는 것으로 형식은 다음과 같습니다.
delimiter $$
create function 스토어드_함수_이름(매개변수)
    returns 반환형식
begin
    이 부분에 프로그래밍 코딩
    return 반환값;
end $$
delimiter ;

select 스토어드_함수_이름();
  • 스토어드 함수 안에서는 select 를 사용 불가합니다.
  • 스토어드 프로시저는 여러 SQL 문이나 숫자 계산 등의 다양한 용도이며, 스토어드 함수는 어떤 계산을 통해 하나의 값을 반환하는데 주로 사용합니다.
  • 스토어드 함수를 사용하기 위해서는 먼저 다음 SQL로 스토어드 함수 생성 권한을 허용해 주어야 합니다.
set global log_bin_trust_function_creators = 1;
  • 함수의 삭제는 drop function 사용합니다.
drop function calcYearFunc;
  • 커서는 테이블에서 한 행씩 처리하기 위한 방식으로 스토어드 프로시저에서 사용합니다.
  • 일반적으로 다음 순서로 처리됩니다.
  1. 커서 선언하기
  2. 반복 조건 선언하기
  3. 커서 열기
  4. 데이터 가져오기
  5. 데이터 처리하기
  6. 커서 닫기
  • 여기서 4, 5 번을 반복해서 처리합니다.
  • 커서는 행이 끝날 때까지 계속 반복합니다. 행의 끝을 판단하기 위해 변수 endOfRow 를 준비하고 true 값인지 체크하는 방식을 사용합니다.

트리거

  • 트리거는 insert, update, delete 문이 작동할 때 자동으로 실행되는 프로그래밍 기능으로, 데이터의 무결성을 지켜주도록 도와줍니다. 테이블에 미리 부착되는 프로그램 코드와 같습니다.
create table if not exists trigger_table (id int, txt varchar(10));
insert into trigger_table values(1, '레드벨벳');
insert into trigger_table values(2, '잇지');
insert into trigger_table values(3, '블랙핑크');
drop trigger if exists myTrigger;
delimiter $$
    create trigger myTrigger
        after delete
        on trigger_table
        for each row 
begin 
    set @msg = '가수 그룹이 삭제됨'; -- 트리거 실행 시 작동되는 코드들
end $$
delimiter ;
  • delete 문 실행시 트리거가 작동합니다.
delete from trigger_table where id = 4;
select @msg;
+----------------------------+
| @msg                       |
+----------------------------+
| 가수 그룹이 삭제됨         |
+----------------------------+
1 row in set (0.00 sec)
  • 트리거에서 기존 데이터는 OLD 테이블에, 새로운 데이터는 NEW 테이블에 잠깐 저장되며, OLD 및 NEW 테이블은 MySQL이 내부적으로 관리합니다.

기본 미션



  • 먼저 member 테이블의 mem_id, mem_name, mem_number, addr 만 가지고 singer 테이블을 만들었습니다.
create table singer (select mem_id, mem_name, mem_number, addr from member);
  • 백업 테이블을 만들어, 변경 타입, 날짜, 사용자를 추가해줍니다.
create table backup_singer(
    mem_id      char(8) not null ,
    mem_name    varchar(10) not null,
    mem_number  int not null,
    addr        char(2) not null ,
    modType     char(2),    -- 변경된 타입. '수정' 또는 '삭제'
    modDate     date,       -- 변경된 날짜
    modUser     varchar(30) -- 변경한 사용자
);
  • update 문의 트리거를 만듭니다.
drop trigger if exists singer_updateTrg;
delimiter $$
create trigger singer_updateTrg -- 트리거 이름
    after update -- 변경 후에 작동하도록 지정
    on singer -- 트리거를 부착할 테이블
    for each row 
begin 
    insert into backup_singer values
          (OLD.mem_id, OLD.mem_name, OLD.mem_number, OLD.addr, '수정', curdate(), current_user());
end $$
delimiter ;
  • delete 문의 트리거를 만듭니다.

drop trigger if exists singer_deleteTrg;
delimiter $$
create trigger singer_deleteTrg -- 트리거 이름
    after delete -- 변경 후에 작동하도록 지정
    on singer -- 트리거를 부착할 테이블
    for each row 
begin 
    insert into backup_singer values
          (OLD.mem_id, OLD.mem_name, OLD.mem_number, OLD.addr, '삭제', curdate(), current_user());
end $$
delimiter ;
  • 테이블을 update, delete 후 백업 테이블을 확인해봅시다.
update singer set addr = '영국' where mem_id = 'BLK';
delete from singer where mem_number >= 7;
select * from backup_singer;

+--------+--------------+------------+--------+---------+------------+----------------+
| mem_id | mem_name     | mem_number | addr   | modType | modDate    | modUser        |
+--------+--------------+------------+--------+---------+------------+----------------+
| BLK    | 블랙핑크     |          4 | 경남   | 수정    | 2023-08-20 | root@localhost |
| GRL    | 소녀시대     |          8 | 서울   | 삭제    | 2023-08-20 | root@localhost |
| OMY    | 오마이걸     |          7 | 서울   | 삭제    | 2023-08-20 | root@localhost |
| SPC    | 우주소녀     |         13 | 서울   | 삭제    | 2023-08-20 | root@localhost |
| TWC    | 트와이스     |          9 | 서울   | 삭제    | 2023-08-20 | root@localhost |
+--------+--------------+------------+--------+---------+------------+----------------+
5 rows in set (0.00 sec)
  • 백업 테이블에 잘 저장된 것을 볼 수 있습니다. 그 다음으로 truncate 문으로 데이터를 모두 삭제해 보겠습니다.
truncate table singer;
select * from backup_singer;
+--------+--------------+------------+--------+---------+------------+----------------+
| mem_id | mem_name     | mem_number | addr   | modType | modDate    | modUser        |
+--------+--------------+------------+--------+---------+------------+----------------+
| BLK    | 블랙핑크     |          4 | 경남   | 수정    | 2023-08-20 | root@localhost |
| GRL    | 소녀시대     |          8 | 서울   | 삭제    | 2023-08-20 | root@localhost |
| OMY    | 오마이걸     |          7 | 서울   | 삭제    | 2023-08-20 | root@localhost |
| SPC    | 우주소녀     |         13 | 서울   | 삭제    | 2023-08-20 | root@localhost |
| TWC    | 트와이스     |          9 | 서울   | 삭제    | 2023-08-20 | root@localhost |
+--------+--------------+------------+--------+---------+------------+----------------+
5 rows in set (0.00 sec)
  • 우리는 delete 문에 트리거를 작성했기 때문에 truncate 문을 실행시 트리거가 동작하지 않습니다.

선택 미션

import pymysql
from tkinter import *
from tkinter import messagebox


def insertData():
    con, cur = None, None
    data1, data2, data3, data4 = "", "", "", ""
    sql = ""

    conn = pymysql.connect(host='127.0.0.1', user='root', password='', db='soloDB', charset='utf8')
    cur = conn.cursor()

    data1 = edt1.get();
    data2 = edt2.get();
    data3 = edt3.get();
    data4 = edt4.get()
    sql = "INSERT INTO userTable VALUES('" + data1 + "','" + data2 + "','" + data3 + "'," + data4 + ")"
    cur.execute(sql)

    conn.commit()
    conn.close()

    messagebox.showinfo('성공', '데이터 입력 성공')


def selectData():
    strData1, strData2, strData3, strData4 = [], [], [], []

    conn = pymysql.connect(host='127.0.0.1', user='root', password='', db='soloDB', charset='utf8')
    cur = conn.cursor()
    cur.execute("SELECT * FROM userTable")

    strData1.append("사용자 ID")
    strData2.append("사용자 이름")
    strData3.append("사용자 이메일")
    strData4.append("사용자 출생연도")
    strData1.append("-----------")
    strData2.append("-----------")
    strData3.append("-----------")
    strData4.append("-----------")

    while (True):
        row = cur.fetchone()
        if row == None:
            break
        strData1.append(row[0])
        strData2.append(row[1])
        strData3.append(row[2])
        strData4.append(row[3])

    listData1.delete(0, listData1.size() - 1)
    listData2.delete(0, listData2.size() - 1)
    listData3.delete(0, listData3.size() - 1)
    listData4.delete(0, listData4.size() - 1)

    for item1, item2, item3, item4 in zip(strData1, strData2, strData3, strData4):
        listData1.insert(END, item1)
        listData2.insert(END, item2)
        listData3.insert(END, item3)
        listData4.insert(END, item4)

    conn.close()


root = Tk()
root.geometry("600x300")
root.title("완전한 GUI 응용 프로그램")

edtFrame = Frame(root)
edtFrame.pack()
listFrame = Frame(root)
listFrame.pack(side=BOTTOM, fill=BOTH, expand=1)

edt1 = Entry(edtFrame, width=10)
edt1.pack(side=LEFT, padx=10, pady=10)
edt2 = Entry(edtFrame, width=10)
edt2.pack(side=LEFT, padx=10, pady=10)
edt3 = Entry(edtFrame, width=10)
edt3.pack(side=LEFT, padx=10, pady=10)
edt4 = Entry(edtFrame, width=10)
edt4.pack(side=LEFT, padx=10, pady=10)

btnInsert = Button(edtFrame, text="입력", command=insertData)
btnInsert.pack(side=LEFT, padx=10, pady=10)
btnSelect = Button(edtFrame, text="조회", command=selectData)
btnSelect.pack(side=LEFT, padx=10, pady=10)

listData1 = Listbox(listFrame, bg='black')
listData1.pack(side=LEFT, fill=BOTH, expand=1)
listData2 = Listbox(listFrame, bg='black')
listData2.pack(side=LEFT, fill=BOTH, expand=1)
listData3 = Listbox(listFrame, bg='black')
listData3.pack(side=LEFT, fill=BOTH, expand=1)
listData4 = Listbox(listFrame, bg='black')
listData4.pack(side=LEFT, fill=BOTH, expand=1)

root.mainloop()

profile
Jihun Kim
backend studying
이전 포스트

[혼공학습단, 혼공SQL] 5주차 Chap 06. 인덱스

0개의 댓글