mysql_14_视图的修改_更新_删除-优快云博客

 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;