1 USE db_book;
2 SELECT * FROM v1;
3 DELETE FROM t_book WHERE id>4;-- 数据太多删掉
4 -- 第五节:修改视图
5 -- 5.1 CREATE OR REPLACE VIEW 语句修改视图
6 -- 格式:
7 -- CREATE OR REPLACE [ ALGORITHM ={ UNDEFINED | MERGE | TEMPTABLE }]
8 -- VIEW 视图名 [( 属性清单 )]
9 -- AS SELECT 语句
10 -- [ WITH [ CASCADED | LOCAL ] CHECK OPTION ];
11 SELECT * FROM v1;
12 CREATE OR REPLACE VIEW v1(书名,价格) AS SELECT bookName,price FROM t_book;
13 SELECT * FROM v1;
14
15 -- 5.2 ALTER 语句修改视图
16 -- 格式:
17 -- ALTER [ ALGORITHM ={ UNDEFINED | MERGE | TEMPTABLE }]
18 -- VIEW 视图名 [( 属性清单 )]
19 -- AS SELECT 语句
20 -- [ WITH [ CASCADED | LOCAL ] CHECK OPTION ];
21 SELECT * FROM v1;
22 ALTER VIEW v1 AS SELECT * FROM t_book;
23 SELECT * FROM v1;
24
25 -- 第六节:更行视图
26 -- 更新视图是指通过视图来插入（INSERT）、更新（UPDATE）和删除（DELETE）表中的数据。因为视图是一个虚
27 -- 拟的表，其中没有数据。通过视图更新时，都是转换基本表来更新。更新视图时，只能更新权限范围内的数据。
28 -- 超出了范围，就不能更新。
29 -- 6.1 插入(INSERT)
30 INSERT INTO v1 VALUES(NULL,"第一行代码",79,"郭霖",1);
31 SELECT * FROM v1;
32 SELECT * FROM t_book;
33
34 -- 6.2 更新(UPDATE)
35 UPDATE v1 SET bookName="第一行代码Android第二版" WHERE bookName="第一行代码";
36 SELECT * FROM v1;
37 SELECT * FROM t_book;
38
39 -- 6.3 删除(DELETE)
40 INSERT INTO v1 VALUES(NULL,"测试图书01",79,"作者01",3);
41 INSERT INTO v1 VALUES(NULL,"测试图书02",100,"作者02",2);
42 SELECT * FROM v1;
43 SELECT * FROM t_book;
44
45 DELETE FROM v1 WHERE id=6;
46 DELETE FROM v1 WHERE bookName="测试图书02";
47
48 SELECT * FROM v1;
49 SELECT * FROM t_book;
50
51 -- 第七节:删除视图
52 -- 删除视图是指删除数据库中已存在的视图。删除视图时，只能删除视图的定义，不会删除数据；
53 -- 格式:
54 -- DROP VIEW [ IF EXISTS ] 视图名列表 [ RESTRICT | CASCADE ]
55 DROP VIEW IF EXISTS v1;
