mysql视图:mysql视图简介来源: 发布时间:星期四, 2009年2月12日 浏览:231次 评论:0
![]() 视图是 ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() 对其中所引用 ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() 视图是存储在数据库中 ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() 视图:查看图形或文档 ![]() ![]() 视图是从 ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() 所以视图不是真实存在 ![]() ![]() ![]() ![]() ![]() ![]() ![]() 视图 ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() 当对通过视图看到 ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() 视图有很多优点 ![]() •视点集中 •简化操作 •定制数据 •合并分割数据 •安全性 2. 创建视图——CREATE VIEW 1. 语法 CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW [db_name.]view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]通过该语句可以创建视图 ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() 表和数据库共享数据库中相同 ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() 2. 使用举例 Eg. 本例创建 ![]() ![]() ![]() ![]() ![]() CREATE TABLE product ( product_id INT NOT NULL, name VARCHAR(50) NOT NULL, price DOUBLE NOT NULL ); INSERT INTO product VALUES(1, 'apple ', 5.5); CREATE TABLE purchase ( id INT NOT NULL, product_id INT NOT NULL, qty INT NOT NULL DEFAULT 0, gen_time DATETIME NOT NULL ); INSERT INTO purchase VALUES(1, 1, 10, NOW ![]() CREATE VIEW purchase_detail AS SELECT product.name as name, product .price as price, purchase.qty as qty, product .price * purchase.qty as total_value from product, purchase where product.product_id = purchase.product_id; 创建成功后 ![]() 运行效果如下: +-------+-------+-----+-------------+ | name | price | qty | total_value | +-------+-------+-----+-------------+ | apple | 5.5 | 10 | 55 | +-------+-------+-----+-------------+ 1 row in ![]() 3. 注意事项 创建视图存在如下注意事项: (1) 运行创建视图 ![]() ![]() ![]() ![]() ![]() (2) SELECT语句不能包含FROM子句中 ![]() (3) SELECT语句不能引用系统或用户变量; (4) SELECT语句不能引用预处理语句参数; (5) 在存储子 ![]() ![]() ![]() (6) 在定义中引用 ![]() ![]() ![]() ![]() ![]() ![]() ![]() (7) 在定义中不能引用TEMPORARY表 ![]() (8) 在视图定义中命名 ![]() (9) 不能将触发 ![]() ![]() (10) 在视图定义中允许使用ORDER BY ![]() ![]() ![]() ![]() ![]() ![]() 3. 修改视图——ALTER VIEW 1. 语法 ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]该语句用于更改已有视图 ![]() ![]() ![]() 2. 使用举例 Eg. 将上 ![]() ![]() ![]() ![]() ALTER VIEW purchase_detail AS SELECT product.name as name, product .price as price, product .price * purchase.qty as total_value from product, purchase where product.product_id = purchase.product_id; 此时通过语句:select * from purchase_detail;对视图进行查询时 ![]() +-------+-------+-------------+ | name | price | total_value | +-------+-------+-------------+ | apple | 5.5 | 55 | +-------+-------+-------------+ 3. 注意事项 修改视图 ![]() ![]() ![]() ![]() ![]() ![]() 该语句需要具有针对视图 ![]() ![]() ![]() ![]() ![]() ![]() 4. 删除视图——DROP VIEW 1. 语法 DROP VIEW [IF EXISTS] view_name [, view_name] ... [RESTRICT | CASCADE]该语句用户删除视图 ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() 2. 使用举例 Eg1. 删除在前面 ![]() ![]() Eg2. 删除 ![]() ![]() Eg3. 删除多个视图:DROP VIEW IF EXISTS test_view1, test_view2; 3. 注意事项 必须对要删除 ![]() ![]() ![]() ![]() 1
相关文章读者评论
发表评论 |