一、视图
视图是一个虚拟表(非真实存在的),其本质是‘根据SQL语句获取动态的数据集,并为其命名‘ ,用户使用时只需使用“名称”即可获取结果集,可以将该结果集当做表来使用。
使用视图我们可以把查询过程中的临时表摘出来,用视图去实现,这样以后再想操作该临时表的数据时就无需重写复杂的SQL了,直接去视图中查找即可,但视图有明显的效率问题,并且视图是存放在数据库中的,如果我们程序中使用的SQL过分依赖数据库中的视图,即强耦合,那就意味着扩展SQL极为不便,因此并不推荐使用
1
2
3
4
5
6
7
|
视图有如下的特点:
1.
视图的列可以来自不同的表,是表的抽象和逻辑意义上建立的新关系
2.
视图是有基本表(实表)产生的表(虚表)
3.
视图的建立和删除不影响基本表
4.
对视图内容的更新(添加、删除和修改)直接影响基本表
5.
当视图来自多个基本表时,不允许添加,修改和删除数据
|
1.创建视图
1
|
语法:<br>create view 视图名称 as sql语句<br><br>ps:create view db1 as select name
from
ren;
|
1
2
3
4
|
#!!!注意注意注意:
#1. 使用视图以后就无需每次都重写子查询的sql,但是这么效率并不高,还不如我们写子查询的效率高
#2. 而且有一个致命的问题:视图是存放到数据库里的,如果我们程序中的sql过分依赖于数据库中存放的视图,那么意味着,一旦sql需要修改且涉及到视图的部分,则必须去数据库中进行修改,而通常在公司中数据库有专门的DBA负责,你要想完成修改,必须付出大量的沟通成本DBA可能才会帮你完成修改,极其地不方便
|
2 使用视图
1
2
|
语法
select
*
from
视图名称;ps:修改视图,原始表也会跟着改伪需求
1
)查询表select
*
from
course2)创建一个course的视图create view course_view as select
*
from
course;
3
)查询course_view视图select
*
from
course_vies
4
)更新course_view视图的内容update course_view
set
cname
=
'xxxx'
;
5
)往视图中插入数据insert into course_view values(
5
,
'yyy'
,
2
);
6
)查看原始表,发现原始表的记录也跟着修改了
|
select * from course;
我们不应该修改视图中的记录,而且在涉及多个表的情况下是根本无法修改视图中的记录的
3.修改视图
1
2
3
|
语法:
alter view 视图名称 as sql语句<br><br>ps:
|
alter view teacher_view as select * from course where cid>3;
4.删除视图
1
2
|
语法:
drop view 视图名称<br>ps:
|
DROP VIEW teacher_view
二、触发器
使用触发器可以定制用户对表进行【增、删、改】操作时前后的行为,无查询
1
2
3
4
5
6
7
|
触发器:监视某种情况,并触发某种操作
触发器创建语法四要素:
1.
监视地点(table)
2.
监视事件(insert
/
update
/
delete)
3.
触发时间(after
/
before)
4.
触发事件(insert
/
uodate
/
delete)
|
1.创建触发器语法
1
2
3
4
5
6
7
8
9
10
11
12
|
create trigger 触发器名称 after
/
before insert
/
update
/
delete
on 表名
for
each row
begin
#需要执行的sql语句
end
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
注意
1
:after
/
before:只能选一个,after 表示 后置触发,before 表示前置触发
注意
2
:insert
/
update
/
delete:只能选一个
|
需求:创建两张表
#商品表 create table goods( id int primary key auto_increment name varchar(20), num int ); | #订单表 create table order_table( oid int primary key auto_increment, gid int, much int ); |
问题
1.添加3条商品数据
1
|
insert into goods(name,num) values (
'苹果'
,
'10'
),(
'草莓'
,
'15'
),(
'金桔'
,
'20'
);
|
如果我们在没使用触发器之前:假设我们现在卖了3个商品1,我们需要做两件事
NO.1 往订单表插入一条记录
1
|
insert into ORDER_table(gid,much) VALUES (
1
,
3
) ;
|
NO.2 更新商品表苹果的剩余数量
1
|
update goods
set
num
=
num
-
3
WHERE name
=
'苹果'
;
|
------------------------------------------------------------------------------------------------------------------------------------------------
NOW现在,我们来创建一个触发器:
1
2
3
4
5
|
create trigger tg1 after insert on order_table
for
each row
begin
update goods
set
num
=
num
-
3
where
id
=
1
;
end
|
这时候我们只要执行:
1
|
insert into order_table (gid,much) values(
1
,
3
);
|
就会发现苹果的数量变成了7,说明在我们插入一条订单的时候,
触发器自动帮我们做了更新操作。
但现在会有一个问题,因为我们触发器里面num和ID都写死了,所以不管我们买那个商品,最终更新的都是苹果的数量。比如:我们往订单表再插入一条记录:
1
|
insert into order_table(gid,much) values(
2
,
3
);
|
执行完后会发现苹果的数量变4了,而草莓的数量没变,这样显然不是我们想要的结果。我们需要改改我们之前创建的触发器。
我们如何在触发器引用行的值,也就是说我们要得到我们新插入的订单记录中的gid或much的值。
对于insert而言,新插入的行用new来表示,行中的每一列的值用new.列名来表示。
所以现在我们可以这样来改我们的触发器:
1
2
3
4
5
6
7
|
create trigger tg2 after insert on order_table
for
each ROW
begin
UPDATE goods
set
num
=
num
-
new.much where
id
=
new.gid;
end
|
第二个触发器创建完毕,我们先把第一个触发器删掉
1
|
drop trigger tg1;
|
再来测试一下,插入一条订单记录:
1
|
insert into order_table(gid,name) values(
2
,
3
);
|
执行完代码,发现草莓的num变成了7,现在就对了