视图

  • 对于复杂的查询，在多次使用后，维护是一件非常麻烦的事情
  • 解决：定义视图
  • 视图本质就是对查询的一个封装
  • 定义视图
1
2
3
create view stuscore as
select students.*,scores.score from scores
inner join students on scores.stuid=students.id;
  • 视图的用途就是查询
1
select * from stuscore;

操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 查询视图内容等同于查询表操作
SELECT * from bookallinfo where cataory = '历史传记';
-- 视图实现模糊查找
SELECT * from bookallinfo where bookname like "%小%";

-- 更新多字段
UPDATE booktable SET bookname = "小时代1",authorid=0,score = NULL  where id = 2;

-- 删除
-- 逻辑删除和物理删除

-- 逻辑删除小时代书
UPDATE booktable set isdelete = "true" where id = 2

-- 物理删除
-- DELETE from booktable WHERE bookname = "鬼吹灯"
DELETE from booktable