10.13 视图
1、什么是视图 视图就是通过查询得到一张虚拟表，然后保存下来，下次用的直接使用即可
2、为什么要用视图 如果要频繁使用一张虚拟表，可以不用重复查询
3、如何使用视图
视图记录的增、删、改和表方法相同，但改变视图记录，原始表也跟着改，所以不要修改视图记录，只用于查看
create
view
teacher2course
as
#创建视图
select
*
from
teacher
inner
join
course
on
teacher.tid
=
course.teacher_id;
alter
view
teacher2course
as
#修改视图名
select
*
from
teacher
inner
join
course
on
teacher.tid
=
course.teacher_id;
drop
view
teacher2course; #删除视图
强调： 1、在硬盘中，视图只有表结构文件，没有表数据文件 2、视图通常用于查询，尽量不要修改视图中的数据
10.14 触发器
触发器：在满足对某张表数据的 增、删、改 的情况下，自动触发的功能称之为触发器 触发器专门针对我们对某一张表数据 增insert 、 删delete 、 改update 的行为，这类行为一旦执行就会触发触发器的执行，即自动运行另外一段sql代码
创建触发器语法：
# 针对插入(
insert
)
create
trigger
tri_after_insert_t1 after
insert
on
表名
for
each row
begin
sql代码...
end
create
trigger
tri_before_insert_t2 before
insert
on
表名
for
each row
begin
sql代码...
end
# 针对删除(
delete
)
create
trigger
tri_after_delete_t1 after
delete
on
表名
for
each row
begin
sql代码...
end
create
trigger
tri_before_delete_t2 before
delete
on
表名
for
each row
begin
sql代码...
end
# 针对修改(
update
)
create
trigger
tri_after_update_t1 after
update
on
表名
for
each row
begin
sql代码...
end
create
trigger
tri_before_update_t2 before
update
on
表名
for
each row
begin
sql代码...
end
举例：
CREATE
TABLE
cmd (
id
INT
PRIMARY
KEY
auto_increment,
USER
CHAR
(
32
),
priv
CHAR
(
10
),
cmd
CHAR
(
64
),
sub_time
datetime
, #提交时间
success enum (
'
yes
'
,
'
no
'
) );
CREATE
TABLE
errlog (
id
INT
PRIMARY
KEY
auto_increment,
err_cmd
CHAR
(
64
),
err_time
datetime
);
delimiter $$
create
trigger
tri_after_insert_cmd after
insert
on
cmd
for
each row
begin
if
NEW.success
=
'
no
'
then
#等值判断只有一个等号
insert
into
errlog(err_cmd,err_time)
values
(NEW.cmd,NEW.sub_time);
end
if
;
end
$$
delimiter ;
insert
into
cmd (
USER
,priv,cmd,sub_time,success)
values
(
'
egon
'
,
'
0755
'
,
'
ls -l /etc
'
,NOW(),
'
yes
'
),
(
'
egon
'
,
'
0755
'
,
'
cat /etc/passwd
'
,NOW(),
'
no
'
),
(
'
egon
'
,
'
0755
'
,
'
useradd xxx
'
,NOW(),
'
no
'
),
(
'
egon
'
,
'
0755
'
,
'
ps aux
'
,NOW(),
'
yes
'
);
mysql
>
select
*
from
errlog; #查询错误日志，发现有两条
+
--
--+-----------------+---------------------+
|
id
|
err_cmd
|
err_time
|
+
--
--+-----------------+---------------------+
|
1
|
cat
/
etc
/
passwd
|
2017
-
09
-
14
22
:
18
:
48
|
|
2
|
useradd xxx
|
2017
-
09
-
14
22
:
18
:
48
|
+
--
--+-----------------+---------------------+
删除触发器：
drop
trigger
tri_after_insert_cmd;
10.15 事务
什么是事务： 开启一个事务可以包含一些sql语句，这些sql语句要么同时成功，要么都不成功，称之为事务的原子性 作用：事务用于将某些操作的多个SQL作为原子性操作，一旦有某一个出现错误，即可回滚到原来的状态，从而保证数据库数据完整性。
create
table
user
(
id
int
primary
key
auto_increment,
name
char
(
32
),
balance
int
);
insert
into
user
(name,balance)
values
(
'
wsb
'
,
1000
),
(
'
egon
'
,
1000
),
(
'
ysb
'
,
1000
);
start
transaction
; #开启事务
update
user
set
balance
=
900
where
name
=
'
wsb
'
; #买支付100元
update
user
set
balance
=
1010
where
name
=
'
egon
'
; #中介拿走10元
update
user
set
balance
=
1090
where
name
=
'
ysb
'
; #卖家拿到90元,出现异常没有拿到
rollback
; #出现异常，回滚到初始状态
commit
; #无异常，提交结果，提交后回滚无效
10.16 存储过程
存储过程包含了一系列可执行的sql语句，存储过程存放于MySQL中，通过调用它的名字可以执行其内部的一堆sql
存储过程的优点：1、用于替代程序写的SQL语句，实现程序与sql解耦 2、基于网络传输，传别名的数据量小，而直接传sql数据量大
存储过程的缺点：程序员扩展功能不方便
10.161 创建与执行存储过程
创建简单存储过程(无参):
delimiter $$
create
procedure
p1()
BEGIN
select
*
from
blog;
insert
into
blog(name,sub_time)
values
("xxx",now());
END
$$
delimiter ;
#在mysql中调用
call p1()
#在python中基于pymysql调用
cursor
.callproc(
'
p1
'
)
print
(
cursor
.fetchall())
创建存储过程(有参）:
delimiter $$
create
procedure
p2(
in
m
int
, #只可传入
in
n
int
,
out res
int
) #只可返回 # inout 既可以传入又可以当作返回值
begin
select
tname
from
teacher
where
tid
>
m
and
tid
<
n;
set
res
=
1
; #执行成功res返回0
end
$$
delimiter ;
#在mysql中调用
set
@res
=
0
;
call p2(
3
,
2
,
@res
)
select
@res
; #0代表假（执行失败），1代表真（执行成功）
#在python中基于pymysql调用
cursor
.callproc(
'
p2
'
,(
2
,
3
,
0
)) #0相当于set
@res
=
0
print
(
cursor
.fetchall()) #查询select的查询结果
cursor
.
execute
(
'
select @_p2_2;
'
) #
@_p2_2代表第三个参数
，即返回值
print
(
cursor
.fetchall())
将事务封装入存储过程：
delimiter
//
create
PROCEDURE
p5(
OUT p_return_code
tinyint
)
BEGIN
DECLARE
exit
handler
for
sqlexception
BEGIN
--
ERROR
set
p_return_code
=
1
;
rollback
;
END
;
DECLARE
exit
handler
for
sqlwarning
BEGIN
--
WARNING
set
p_return_code
=
2
;
rollback
;
END
;
START
TRANSACTION
;
DELETE
from
tb1; #执行失败
insert
into
blog(name,sub_time)
values
(
'
yyy
'
,now());
COMMIT
;
--
SUCCESS
set
p_return_code
=
0
; #0代表执行成功
END
//
delimiter ;
#在mysql中调用存储过程
set
@res
=
123
;
call p5(
@res
);
select
@res
;
#在python中基于pymysql调用存储过程
cursor
.callproc(
'
p5
'
,(
123
,))
print
(
cursor
.fetchall()) #查询select的查询结果
cursor
.
execute
(
'
select @_p5_0;
'
)
print
(
cursor
.fetchall())
10.162 删除存储过程
drop
procedure
proc_name;
回到顶部
