#mysql - python connection test
import pymysql #mysql과 연동하기 위해
#필요한 기본 DB 정보
host = "localhost"
user = "root"
pw = "password"
db = "my_db"
#DB에 접속
conn = pymysql.connect(host= host, user = user, password = pw, db = db)
#사용할 sql문
sql1 = "SELECT ename FROM emp WHERE empno=7521"
sql2 = "SELECT dname FROM emp, dept WHERE dept.deptno=emp.deptno and ename='scott'"
sql3 = "SELECT ename FROM emp WHERE job='salesman'"
sql4 = "SELECT dname FROM dept"
querys = [sql1, sql2, sql3, sql4]
for sql in querys:
print(sql)
# sql문 실행/데이터 받기
curs = conn.cursor() #sql 실행시 결과를 담고 있는 버퍼를 정렬
curs.execute(sql) #앞의 sql문 실행
row = curs.fetchone() #sql실행결과 모두 가져오기, fetchall 모든 행 가져옴, fetchone은 하나의 행만 가져옴
while(row):
print(row)
row = curs.fetchone()
print("==========================================================")
#db 접속 종료
curs.close()
conn.close()
Mysql 의 world 데이터베이스와 연동하여 다음과 같이 국가명을 입력하면 해당 국가의 정보를 출력하는 윈도우 프로그램
- 매치되는 국가가 없으면 모든 항목에 공백 출력
import sys
import pymysql
from PyQt5.QtWidgets import *
def connectDB():
#필요한 db 정보
host = "localhost"
user = "root"
pw = "Changmin97"
db = "world"
#db 접속
conn = pymysql.connect(host=host, user =user, password =pw, db =db)
return(conn) #connection 리턴
def disconnectDB(conn):
conn.close()
class MyApp(QWidget):
def __init__(self):
super().__init__()
self.initUI()
# UI 디자인 함수
def initUI(self):
#QLable 문자열 출력
label1 = QLabel('Country name')
label2 = QLabel('Continent')
label3 = QLabel('Population')
label4 = QLabel('GNP')
label5 = QLabel('Captial city')
label6 = QLabel('Language')
#text_Country_name
self.text_Country_name = QTextEdit()
self.text_Country_name.setFixedWidth(200)
self.text_Country_name.setFixedHeight(30)
#버튼 생성, 버튼 클릭시 btn_1_clicked 실행
btn_1 = QPushButton('Query')
btn_1.clicked.connect(self.btn_1_clicked)
#text_Continent
self.text_Continent = QTextEdit()
self.text_Continent.setFixedWidth(200)
self.text_Continent.setFixedHeight(30)
#text_Population
self.text_Population = QTextEdit()
self.text_Population.setFixedWidth(200)
self.text_Population.setFixedHeight(30)
#text_GNP
self.text_GNP = QTextEdit()
self.text_GNP.setFixedWidth(200)
self.text_GNP.setFixedHeight(30)
#text_Captial_city
self.text_Captial_city = QTextEdit()
self.text_Captial_city.setFixedWidth(200)
self.text_Captial_city.setFixedHeight(30)
#text_Language
self.text_Language = QTextEdit()
self.text_Language.setFixedWidth(200)
self.text_Language.setFixedHeight(30)
#화면 배치, grid를 만들어 배치
gbox = QGridLayout()
gbox.addWidget(label1,0,0)
gbox.addWidget(self.text_Country_name, 0, 1)
gbox.addWidget(btn_1, 0, 2)
gbox.addWidget(label2,1,0)
gbox.addWidget(self.text_Continent, 1, 1)
gbox.addWidget(label3,2,0)
gbox.addWidget(self.text_Population, 2, 1)
gbox.addWidget(label4,3,0)
gbox.addWidget(self.text_GNP, 3, 1)
gbox.addWidget(label5,4,0)
gbox.addWidget(self.text_Captial_city, 4, 1)
gbox.addWidget(label6,5,0)
gbox.addWidget(self.text_Language, 5, 1)
self.setLayout(gbox)
self.setWindowTitle('Country Info')
self.setGeometry(300, 300, 480,250) #창뜨는 위치, 크기
self.show()
#버튼 클릭 처리
def btn_1_clicked(self):
#사용자가 입력한 값 받아서 empno 저장
Country_name = self.text_Country_name.toPlainText()
sql = "SELECT country.name, continent, country.population, gnp, city.name, language \
From country, city, countrylanguage\
where country.code = city.countrycode \
and country.code = countrylanguage.countrycode\
and country.capital=city.id\
and IsOfficial = 'T' \
and country.name ="+"'"+Country_name+"'"
#ex) ename '"+ename+"'" =>문자일 경우 따옴표 주의!
conn = connectDB()
curs = conn.cursor()
curs.execute(sql)
result = curs.fetchone() # sql 실행 결과 가져오기
if result:
self.text_Country_name.setText(result[0])
self.text_Continent.setText(result[1])
self.text_Population.setText(str(result[2]))
self.text_GNP.setText(str(result[3]))
self.text_Captial_city.setText(result[4])
self.text_Language.setText(result[5])
else:
self.text_Country_name.setText("") #매치되는 국가가 없으면 모든 항목에 공백 출력
curs.close()
disconnect(conn)
#END Class
#프로그램 실행, class를 생성하고 실행
if(__name__ == '__main__'):
app = QApplication(sys.argv)
ex = MyApp()
sys.exit(app.exec_())
서버에 직접 접속, 원격접속 둘다 하려면 같은이름의 local, remote계정 두가지 많들어야한다.
권한은
1. 모든 DB
2. 패턴에 매칭되는 DB(이름으로 검색), 선택한 DB
에 접근할 수 있는 권한을 줄 수 있고,
select, insert, update, delete, execute, show view, create, alert, index, drop..... 와 같은 권한을 줄 수 있다
Mysql workbench는 DB단위로 권한을 부여하고 회수한다. => 세부적으로 되지 않는다.
따라서 sql 명령문 (grant, revoke)를 이용하여 테이블 단위로 권한을 부여하고 회수할 수 있다.
GRANT select ON my_db.emp TO user_1@localhost;
GRANT select, insert, update ON my_db.dept TO user_1@localhost;
root 사용자일 경우 sql문을 사용하여 사용자를 생성하고 권한부여, 회수가 가능하다.
localhost 사용자의 경우'
create user user_2@localhost identifited by '4321';
원격접속 사용자의 경우
create user 'user_2'@'%' identified by '4321';
생성된 사용자 확인
SELECT * from mysql.user;
mysql => system 카탈로그 정보가 있는 데이터 베이스
user => user테이블에 사용자 정보가 저장
권한의 부여
my_db에 대한 모든 권한 부여
grant all privileges on my_db.* to user_2@localhost;
일부 권한 부여
grant select, insert on my_db.* to user_1@localhost;
테이블에 대한 모든 권한 부여
grant all privilleges on my_db.emp to user_1@localhost;
테이블에 대한 일부 권한 부여
grant select, insert on my_db.emp to user_1@localhost;
with grant option
자신의 권한을 다른 user에게 부여할 수 있게 된다.
grant all privileges on my_db.* to user_2@localhost with grant option;
user_2로 로그인하여 다른 유저에거 자신의 권한을 grant 할 수 있다.
부여된 권한을 확인하려면
flush privileges; //변경된 내용을 메모리에 반영(권한 적용)
show grant for user_1@localhost;
권한 회수 => revoke
revoke delete on my_db.emp from user_2@localhost;
사용자 삭제
drop user user_2@localhost;
사용자 관리 - role
ex) 영업업무를 하는 user1, user2, user3이 있고 필요한 권한은 select, update일 경우
create role sales_role; //역할 생성
grant select, update on my_db.emp to sales_role; //역할에 권한 부여
grant sales_role to user1@localhost;
grant sales_role to user2@localhost;
grant sales_role to user3@localhost;
#mysql - python connection test
import pymysql #mysql과 연동하기 위해
#필요한 기본 DB 정보
host = "localhost"
user = "root"
pw = "XXXX"
db = "my_db"
#DB에 접속
conn = pymysql.connect(host= host, user = user, password = pw, db = db)
#사용할 sql문
sql = "SELECT * FROM emp LIMIT 10"
# sql문 실행/데이터 받기
curs = conn.cursor() #sql 실행시 결과를 담고 있는 버퍼를 정렬
curs.execute(sql) #앞의 sql문 실행
data = curs.fetchall() #sql실행결과 모두 가져오기, fetchall 모든 행 가져옴, fetchone은 하나의 행만 가져옴
type(data) #data 자료구조
data #data 내용출력
data[0] #첫번째 행
str(data[0][0]) #첫번째 행의 첫번째 컬럼
#db 접속 종료
curs.close()
conn.close()
import sys
import pymysql
from PyQt5.QtWidgets import *
def connectDB():
#필요한 db 정보
host = "localhost"
user = "root"
pw = "Changmin97"
db = "my_db"
#db 접속
conn = pymysql.connect(host=host, user =user, password =pw, db =db)
return(conn) #connection 리턴
def disconnectDB(conn):
conn.close()
class MyApp(QWidget):
def __init__(self):
super().__init__()
self.initUI()
# UI 디자인 함수
def initUI(self):
#QLable 문자열 출력
label1 = QLabel('ID')
label2 = QLabel('ename')
label3 = QLabel('job')
label4 = QLabel('department')
#self => 외부에서 접근 가능
#text박스 생성 = QTextEdit
self.text_id = QTextEdit()
self.text_id.setFixedWidth(200)
self.text_id.setFixedHeight(30)
#버튼 생성, 버튼 클릭시 btn_1_clicked 실행
btn_1 = QPushButton('Query')
btn_1.clicked.connect(self.btn_1_clicked)
#text박스 생성 = QTextEdit
self.text_ename = QTextEdit()
self.text_ename.setFixedWidth(200)
self.text_ename.setFixedHeight(30)
#text박스 생성 = QTextEdit
self.text_job = QTextEdit()
self.text_job.setFixedWidth(200)
self.text_job.setFixedHeight(30)
#text박스 생성 = QTextEdit
self.text_dept = QTextEdit()
self.text_dept.setFixedWidth(200)
self.text_dept.setFixedHeight(30)
#화면 배치, grid를 만들어 배치
gbox = QGridLayout()
gbox.addWidget(label1,0,0)
gbox.addWidget(self.text_id, 0, 1)
gbox.addWidget(btn_1, 0, 2)
gbox.addWidget(label2,1,0)
gbox.addWidget(self.text_ename, 1, 1)
gbox.addWidget(label3,2,0)
gbox.addWidget(self.text_job, 2, 1)
gbox.addWidget(label4,3,0)
gbox.addWidget(self.text_dept, 3, 1)
self.setLayout(gbox)
self.setWindowTitle('My Program')
self.setGeometry(300, 300, 480,250) #창뜨는 위치, 크기
self.show()
#버튼 클릭 처리
def btn_1_clicked(self):
#사용자가 입력한 값 받아서 empno 저장
empno = self.text_id.toPlainText()
sql = "SELECT ename, job, danme \
From emp e, dept d\
where e.deptno = d.deptno\
and empno = "+ empno
#ex) ename '"+ename+"'" =>문자일 경우 따옴표 주의!
conn = connectDB()
curs = conn.cursor()
curs.execute(sql)
result = curs.fetchone() # sql 실행 결과 가져오기
self.text_ename.setText(result[0])
self.text_job.setText(result[1])
self.text_dept.setText(restul[2])
curs.close()
disconnect(conn)
#END Class
#프로그램 실행, class를 생성하고 실행
if(__name__ == '__main__'):
app = QApplication(sys.argv)
ex = MyApp()
sys.exit(app.exec_())
ex 1)
- 사원번호가 7521인 사원의 이름을 출력한다
- 이름이 SCOTT 인 사원의 부서이름을 출력한다
- 담당업무가 SALESMAN 인 모든사원의 이름을 출력한다.
- 모든 부서의 이름을 출력한다.
import pymysql #mysql과 연동하기 위해
#필요한 기본 DB 정보
host = "localhost"
user = "root"
pw = "Changmin97"
db = "my_db"
#DB에 접속
conn = pymysql.connect(host= host, user = user, password = pw, db = db)
#사용할 sql문
sql1 = "SELECT ename FROM emp WHERE empno=7521"
sql2 = "SELECT dname FROM emp, dept WHERE dept.deptno=emp.deptno and ename='scott'"
sql3 = "SELECT ename FROM emp WHERE job='salesman'"
sql4 = "SELECT dname FROM dept"
querys = [sql1, sql2, sql3, sql4]
for sql in querys:
print(sql)
# sql문 실행/데이터 받기
curs = conn.cursor() #sql 실행시 결과를 담고 있는 버퍼를 정렬
curs.execute(sql) #앞의 sql문 실행
row = curs.fetchone() #sql실행결과 모두 가져오기, fetchall 모든 행 가져옴, fetchone은 하나의 행만 가져옴
while(row):
print(row)
row = curs.fetchone()
print("==========================================================")
#db 접속 종료
curs.close()
conn.close()
ex 2)
Mysql 의 world 데이터베이스와 연동하여 다음과 같이 국가명을 입력하면 해당 국가의 정보를 출력하는 윈도우 프로그램을 작성하시오
- 매치되는 국가가 없으면 모든 항목에 공백 출력
import sys
import pymysql
from PyQt5.QtWidgets import *
def connectDB():
#필요한 db 정보
host = "localhost"
user = "root"
pw = "Changmin97"
db = "world"
#db 접속
conn = pymysql.connect(host=host, user =user, password =pw, db =db)
return(conn) #connection 리턴
def disconnectDB(conn):
conn.close()
class MyApp(QWidget):
def __init__(self):
super().__init__()
self.initUI()
# UI 디자인 함수
def initUI(self):
#QLable 문자열 출력
label1 = QLabel('Country name')
label2 = QLabel('Continent')
label3 = QLabel('Population')
label4 = QLabel('GNP')
label5 = QLabel('Captial city')
label6 = QLabel('Language')
#text_Country_name
self.text_Country_name = QTextEdit()
self.text_Country_name.setFixedWidth(200)
self.text_Country_name.setFixedHeight(30)
#버튼 생성, 버튼 클릭시 btn_1_clicked 실행
btn_1 = QPushButton('Query')
btn_1.clicked.connect(self.btn_1_clicked)
#text_Continent
self.text_Continent = QTextEdit()
self.text_Continent.setFixedWidth(200)
self.text_Continent.setFixedHeight(30)
#text_Population
self.text_Population = QTextEdit()
self.text_Population.setFixedWidth(200)
self.text_Population.setFixedHeight(30)
#text_GNP
self.text_GNP = QTextEdit()
self.text_GNP.setFixedWidth(200)
self.text_GNP.setFixedHeight(30)
#text_Captial_city
self.text_Captial_city = QTextEdit()
self.text_Captial_city.setFixedWidth(200)
self.text_Captial_city.setFixedHeight(30)
#text_Language
self.text_Language = QTextEdit()
self.text_Language.setFixedWidth(200)
self.text_Language.setFixedHeight(30)
#화면 배치, grid를 만들어 배치
gbox = QGridLayout()
gbox.addWidget(label1,0,0)
gbox.addWidget(self.text_Country_name, 0, 1)
gbox.addWidget(btn_1, 0, 2)
gbox.addWidget(label2,1,0)
gbox.addWidget(self.text_Continent, 1, 1)
gbox.addWidget(label3,2,0)
gbox.addWidget(self.text_Population, 2, 1)
gbox.addWidget(label4,3,0)
gbox.addWidget(self.text_GNP, 3, 1)
gbox.addWidget(label5,4,0)
gbox.addWidget(self.text_Captial_city, 4, 1)
gbox.addWidget(label6,5,0)
gbox.addWidget(self.text_Language, 5, 1)
self.setLayout(gbox)
self.setWindowTitle('Country Info')
self.setGeometry(300, 300, 480,250) #창뜨는 위치, 크기
self.show()
#버튼 클릭 처리
def btn_1_clicked(self):
#사용자가 입력한 값 받아 저장
Country_name = self.text_Country_name.toPlainText()
sql = "SELECT country.name, continent, country.population, gnp, city.name, language \
From country, city, countrylanguage\
where country.code = city.countrycode \
and country.code = countrylanguage.countrycode\
and country.capital=city.id\
and IsOfficial = 'T' \
and country.name ="+"'"+Country_name+"'"
#ex) ename '"+ename+"'" =>문자일 경우 따옴표 주의!
conn = connectDB()
curs = conn.cursor()
curs.execute(sql)
result = curs.fetchone() # sql 실행 결과 가져오기
if result:
self.text_Country_name.setText(result[0])
self.text_Continent.setText(result[1])
self.text_Population.setText(str(result[2]))
self.text_GNP.setText(str(result[3]))
self.text_Captial_city.setText(result[4])
self.text_Language.setText(result[5])
else:
self.text_Country_name.setText("") #매치되는 국가가 없으면 모든 항목에 공백 출력
curs.close()
disconnect(conn)
#END Class
#프로그램 실행, class를 생성하고 실행
if(__name__ == '__main__'):
app = QApplication(sys.argv)
ex = MyApp()
sys.exit(app.exec_())
CREATE FUNCTION f_mgr (e_name varchar(10))
RETURNS varchar(10)
BEGIN
declare manager varchar(10);
select m.ename into manager
from emp e, emp m
where e.mgr=m.empno and e.ename = e_name;
RETURN manager;
END
(2) 부서번호를 입력하면 부서의 위치를 출력하는 함수를 작성하시오 (f_loc)
다음과 같이 함수를 테스트한 결과를 보이시오.
select empno, ename, job, f_loc(deptno) as loc
from emp
CREATE FUNCTION f_loc (d_no int)
RETURNS varchar(10)
BEGIN
declare d_loc varchar(10);
select loc into d_loc
from dept
where deptno = d_no;
RETURN d_loc;
END
1. 사원번호를 매개변수로 입력 받아 사원번호, 이름, 담당업무, 연봉, 소속부서명을 보여주는 stored procedure 를 작성하시오 (p_emp_sel_1)
CREATE PROCEDURE p_emp_sel_1(id int)
BEGIN
select empno, ename, job, sal, dname from emp, dept
where emp.deptno=dept.deptno and empno = id;
END
2. 부서번호, 부서명, 위치를 매개변수로 입력 받아 새로운 부서 정보를 생성하는 stored procedure 를 작성하시오 (p_dept_insert_1)
CREATE PROCEDURE p_dept_insert_1(d_num int , d_name varchar(10), d_loc varchar(10))
BEGIN
insert into dept values(d_num, d_name, d_loc);
END
3. 사원번호, 사원 이름을 매개변수로 입력 받아 이름을 수정하는 stored procedure 를 작성하시오 (p_emp_update_1)
CREATE PROCEDURE p_emp_update_1 (e_num int, e_name varchar(10))
BEGIN
update emp set ename=e_name where empno = e_num;
END
1. 사원번호를 매개변수로 입력 받아 사원의 담당업무가 ‘CLERK’ 이면 급여를 20% 올리고, 아닌 경우는 10%를 올리는 stored procedure 를 작성하시오 (p_emp_update_2)
CREATE PROCEDURE p_emp_update_2 (e_num int)
BEGIN
declare e_job varchar(10);
select job into e_job
from emp
where empno=e_num;
if(e_job = 'clerk') then
update emp
set sal=sal*1.2
where empno=e_num;
end if;
if(e_job != 'clerk') then
update emp
set sal=sal*1.1
where empno=e_num;
end if;
END
2. 사원번호를 매개변수로 입력 받은 후에 그 사원이 속한 부서 사람들의 연봉 합계를 구하여 출력하는 stored procedure 를 작성하시오 (p_emp_sel_2)
CREATE PROCEDURE p_emp_sel_2 (e_num int)
BEGIN
select sum(sal)
from emp
where deptno = (select deptno from emp where empno = e_num);
END
3. 사원번호를 매개변수로 입력 받은 후에 사원의 급여가 평균급여 이상이면 해당 사원의 근무지를 보이고, 그렇지 않으면 사원의 직무를 보이는 stored procedure 를 작성 하시오. (p_emp_sel_3)
CREATE PROCEDURE p_emp_sel_3 (e_num int)
BEGIN
declare e_sal decimal(10,4);
declare avg_sal decimal(10,4);
select sal into e_sal
from emp
where empno=e_num;
select avg(sal) into avg_sal
from emp;
if(avg_sal <= e_sal) then
select loc from empd where empno=e_num;
else
select job from emp where empno=e_num;
end if;
END
=> 어플리케이션마다 복수의 SQL문 기술할 필요 없이 만들어진 저장 프로시저를 사용하면 다른 어플리케이션을 수정하여 컴파일 할 필요 없음.
=> 저장 프로시저는 만들어지는 순간에 구문이 검사됨, 따라서 DBA의 에러를 감소시킬 수 있음(실행전 검사)
=> SQL문을 직접 실행시킬 수 없는 사용자들도 저장 프로시저만 실행시킬 수 있는 권한을 가지게 할 수 있다.
단점
=> 접하기 어렵다.
=> DBMS 제품마다 문법이 다르다(비표준화)
=> 저장프로시저를 남발하는 경우 유지보수가 어렵다.
함수와의 차이점
저장프로시저 : 일반적으로 return 값이 없는 프로그램, CALL에 의해서 호출
함수 : return 값이 있는 프로그램, MAX(), min()과 같이 SQL문 안에서 사용됨
- 저장 프로시저 생성
CREATE PROCEDURE 저장프로시저이름()
BEGIN
SQL문 1;
SQL문 2;
END
BEGIN ~ END 사이에 원하는 SQL문들을 작성하면 된다.
** 구분문자 문제
위와 같이 작성시 BEGIN ~ END안에서 SQL문이 작성이 덜 되었더라도 ;를 만나게되면 CREATE PROCEDURE 을 실행하게되어 완성되지 않은 상태로 저장프로시저가 생성된다.
CREATE PROCEDURE 저장프로시저이름(인수이름 자료형)
BEGIN
SQL문 1;
SQL문 2
END
와 같이 될 경우 MySQL 콘솔창은 ;이 입력되면 ;이전단계까지의 명령문을 실행하게 되기때문이다.
따라서
저장 프로시저에서 END를 입력하고난 뒤 CREATE PROCEFURE 명령이 실행되게 해야한다.
DELIMITER를 사용하여 구분문자를 ;대신 다른 문자로 변경한다.
EX) DELIMITER // 혹은 DELIMITER =
와 같이원하는 문자를 적으면 된다.
DELIMITER // =>구분 문자를 ;에서 //로 변경
CREATE PROCEDURE pr1()
BEGIN
SELECT * FROM tb;
SELECT * FROM tb1;
END
// =>//구분문자를 만났으므로 이전까지 입력된 명령을 수행
DELIMITER ; => 구분문자를 다시 ;로 변경
- 저장 프로시저 실행
CALL 저장프로시저이름;
ex) CALL pr1();를 실행하면
자동으로 SELECT * FROM tb;와 SELECT * FROM tb1;가 실행된다.
- 저장 프로시저 인수 사용
PROCEDURE 저장프로시저이름(인수이름 자료형);
예를 들어 sales가 200 이상인 값을 보려고 할때
select * from tb where sales>=d;와 같이 설정하면
DELIMITER //
CREATE PROCEDURE pr1(d INT)
BEGIN
SELECT * FROM tb WHERE sales>=d;
END
//
DELIMITER ;
d를 인자로 받아들여 pr1(200)을 실행하면
select * from tb where sales>=200;이 된다.
- 작성된 저장 프로시저 내용 표시
SHOW CREATE PROCEDURE 저장프로시저이름;
ex) SHOW CREATE PROCEDURE pr1;
pr1의 프로시저 내용을 볼 수 있게 된다.
- 저장 프로시저 삭제
DROP PROCEDURE 저장프로시저이름;
- 저장 함수
저장 프로시저와 유사하지만, 실행했을 때 값을 반환한다.
CREATE FUNCTION 저장함수이름(인수이름 자료형) RETURNS 반환값자료형
BEGIN
SQL문 ....
RETURN 반환값식
END
* DECLARE
저장 함수에서 변수를 사용하려면 DECLARE로 정의해야한다.
DECLARE 변수이름 자료형;
예시를 보면
CRETAE FUNCTION func() RETURNS DOUBLE => 함수의 반환형은 double
BEGIN
DECLARE r DOUBLE; => 변수 r 선언
SELECT AVG(sales) INTO r FROM tb; => AVG(sales)값을 r에 저장
RETURN r; => r을 반환
END
- 저장함수 삭제
DROP FUNCTION 저장함수이름;
- 저장함수 내용 표시
SHOW CREATE FUNCTION 저장함수이름;
저장 함수의 내용을 볼 수 있게된다.
- 트리거
테이블에 대해 어떤 처리를 실행하면 이에 반응하여 설정한 명령이 자동으로 실행되는 구조
INSERT, UPDATE, DELETE 등 명령이 실행될 때, 트리거로 설정한 명령이 자동으로 실행되게 할 수 있다.
EX) 테이블의 레코드 변경시, 변경한 내용을 다른 테이블에 기록하도록 트리거를 만들 수 있음
따라서 트리거는 처리를 기록하거나, 처리가 실패할 경우를 대비하여 만들어 놓으면 좋다.
트리거는 INSERT, UPDATE, DELETE 등 명령이 실행되기 직전(BEFORE) 또는 직후(AFTER)에 호출되어 실행된다.
즉, 어떤 데이터를 처리하기 전에 호출되거나 어떤 데이터를 처리한 후에 호출된다.
또한 테이블에서 어떤 데이터를 처리하기 전의 값은 OLD.칼럼이름.
처리한 후의 값은 NEW.칼럼이름으로 얻을 수 있다.(추출할 수 있다.)
명령에 따라서 칼럼 값을 추출할 수도 있고 없을 수도 있다.
명령
실행전(old.칼럼이름)
실행후(new.칼럼이름)
insert
old.칼럼이름 추출 불가
가능
delete
가능
new.칼럼이름 추출불가
update
가능
가능
- 트리거 생성
CREATE TRIGGER 트리거이름 BEFORE(또는 AFTER) DELETE(UPDATE,INSERT 등과 같은 명령)
ON 테이블 이름 FOR EACH ROW
BEGIN
변경전(OLD.칼럼이름)을 이용한 처리 ====> 또는 변경후(NEW.칼럼이름)
END
EX) tb1에서 삭제한 레코드를 tb1m에 삽입하는 트리거 작성
DELIMITER //
CREATE TRIGGER tr1 BEFORE DELETE ON tb1 FOR EACH ROW
===> delete에 반응, 삭제하기 직전의 값을 넣으므로 before, for each row 각 행에 대해 수행
BEGIN
INSERT INTO tb1m VALUES(OLD.number, OLD.name, OLD.age);
END
//
DELIMIER ;
tb1에서 레코드가 삭제될때, tb1m에 삭제된 레코드가 입력되게 된다.
DELETE FROM tb1;을 하여 모든 레코드를 삭제하더라도 tb1m에 삭제된 레코드들이 저장되게 된다.
따라서 INSERT INTO tb1 SELECT * FROM tb1m;과 같이 다시 복원을 할 수도 있다.
