《MySQL数据库应用案例教程》教学课件第12章视图

1、12视图第章第1页，共33页。12.1视图概述第2页，共33页。视图是从数据库中的一张或多张表中导出的表。创建视图时所引用的表称为基表。视图中的数据并不是像表中那样存储在数据库中，它只是读取基表中的数据。对视图的操作与对表的操作一样，可以对其进行查询、修改和删除等操作。在对视图中的数据进行修改时，相应的基本表中的数据也会发生变化；同时，若基本表的数据发生变化，则这种变化也会反映到视图中。与直接操作数据表相比，视图主要有以下优势：简单：视图中看到的即是需要的，用户完全不需要考虑对应的表结构、关联条件和筛选条件。对于用户来说，经常使用的查询可以被定义为视图。安全：视图可以作为一种安全机制。通过视图

2、，用户只能查看和修改他们所看到的数据，数据库中的其他数据或表既看不见也不可以访问，这样用户就被限制在了数据的不同子集上。数据独立：视图可以使应用程序和数据表在一定程度上独立，创建视图之后，应用程序可以建立在视图之上，从而使程序与数据表分割开来，这样就可以屏蔽表结构变化带来的影响。第3页，共33页。12.2创建视图第4页，共33页。视图可以以一张表为基础创建，也可以以多张表为基础创建。创建视图使用CREATE VIEW语句，其基本语法格式如下：CREATE OR REPLACE VIEW view_name (column_list) AS SELECT_statement;创建视图时应注意以下

3、几点：定义中引用的任何表或视图都必须存在。如果在创建视图后，删除定义时引用的表或视图，则使用视图时将导致错误。创建视图不能引用临时表。SELECT语句中最大列名长度为64个字符。第5页，共33页。在MySQL中，可以在单个数据表上创建视图，这也是最简单的一种创建方法。12.2.1 在单表上创建视图CREATE VIEW v_table AS SELECT id,name FROM goods;【实例12-1】以第5章中“实例5-1”创建的goods表为基表创建视图。步骤1 启动并登录MySQL。步骤3 执行SQL语句，基于goods表创建视图v_table。步骤2 执行以下语句，选择数据库db

4、_shop。USE db_shop;mysql SELECT * FROM v_table;+-+-+| id | name|+-+-+| 4| 休闲西服| 5| 果汁| 3| 水果糖| 9| 牛仔裤 | 2| 牛奶糖 | 10| 红楼梦 | 8| 苹果 | 1| 西游记 | 7| 西瓜 | 6| 论语 |+-+-+10 rows in set (0.05 sec)步骤4 执行SELECT语句，查询视图v_table。SQL语句及其执行结果如下：第6页，共33页。默认情况下，视图的字段名与基表的字段名相同。但是，为了增加数据安全性，也可以为视图字段指定不同的名称。12.2.1 在单表上创建视图

5、mysql SELECT * FROM v_table2;+-+-+| goods_id| goods_name|+-+-+| 4| 休闲西服 | 5| 果汁 | 3 | 水果糖 | 9 | 牛仔裤 | 2 | 牛奶糖 | 10 | 红楼梦 | 8 | 苹果 | 1 | 西游记 | 7 | 西瓜 | 6 | 论语 |+-+-+10 rows in set (0.00 sec)【实例12-2】以第5章中“实例5-1”创建的goods表为基表创建视图，并重新为视图字段命名。SQL语句如下：CREATE VIEW v_table2(goods_id,goods_name) AS SELECT id,

6、name FROM goods;语句执行成功，查看v_table2视图中的数据，结果如下：第7页，共33页。12.2.2 在多表上创建视图mysql SELECT * FROM v_table3;+-+-+| o_id| name |+-+-+| 2 | 牛奶糖 | 4 | 休闲西服 | 6 | 论语 | 3 | 水果糖 | 1 | 西游记 | 5 | 果汁 |+-+-+6 rows in set (0.03 sec)【实例12-3】以第5章中“实例5-1”创建的goods表和“实例5-2”创建的orders表为基表，创建视图，并重新为视图字段命名。SQL语句如下：CREATE VIEW v_

7、table3(o_id,name) AS SELECT orders.o_id, FROM goods JOIN orders ON goods.id=orders.o_id;语句执行成功后，查看v_table3视图中的数据，结果如下：第8页，共33页。使用Navicat for MySQL也可以创建视图，具体操作步骤如下：12.2.3 使用图形化工具创建视图步骤1 启动Navicat for MySQL并连接MySQL后，双击需要操作的数据库，此处为db_shop。步骤2 单击“视图”按钮，显示前面创建的视图。单击“新建视图”按钮，可以打开视图编辑界面，如图12-1所示。

8、第9页，共33页。使用Navicat for MySQL也可以创建视图，具体操作步骤如下：12.2.3 使用图形化工具创建视图步骤3 切换到“视图创建工具”选项卡，在左侧双击要创建视图的基本表，将其添加至右侧窗口，之后在右侧相应字段的复选框中单击可选择要在视图中显示的字段，最后在右下角的窗口中进一步编辑创建视图的SQL语句，如图12-2所示。步骤4 编辑好后单击“保存”按钮，在弹出的“视图名”对话框中输入视图名，并单击“确定”按钮即可。第10页，共33页。12.3查看视图第11页，共33页。使用DESC语句可以查看视图的结构，其基本语法格式如下：DESC 视图名;mysql DESC v_ta

9、ble;+-+-+-+-+-+-+| Field| Type | Null| Key | Default | Extra|+-+-+-+-+-+-+| id | int(11) | NO | | 0 | | name | char(30)| YES | | NULL| |+-+-+-+-+-+-+2 rows in set (0.02 sec)【实例12-4】使用DESC语句查看视图v_table的结构。SQL语句及其执行结果如下：12.3.1 查看视图基本信息第12页，共33页。mysql DESC goods;+-+-+-+-+-+-+| Field | Type | Null | Key

10、| Default| Extra|+-+-+-+-+-+-+| id | int(11) | NO | PRI | NULL| auto_increment| type | char(30) | YES | | NULL| | name | char(30) | YES | UNI| NULL | | price | decimal(7,2) unsigned| YES | | NULL | | num | int(11) | YES | | 0 | | add_time| datetime | YES | | NULL| |+-+-+-+-+-+-+6 rows in set (0.00 s

11、ec)【实例12-5】使用DESC语句查看基本表goods的结构。SQL语句及其执行结果如下：12.3.1 查看视图基本信息第13页，共33页。使用SHOW CREATE VIEW语句可以查看视图定义语句，其基本语法格式如下：SHOW CREATE VIEW 视图名;mysql SHOW CREATE VIEW v_table2 G* 1. row * View: v_table2 Create View: CREATE ALGORITHM=UNDEFINED DEFINER=rootlocalhost SQL SECURITY DEFINER VIEW v_table2 AS select

12、goods.id AS goods_id, AS goods_name from goods character_set_client: gbkcollation_connection: gbk_chinese_ci1 row in set (0.00 sec)【实例12-6】使用SHOW CREATE VIEW语句查看视图v_table2的定义语句。SQL语句及其执行结果如下：12.3.2 查看视图定义语句第14页，共33页。在MySQL中，所有视图的详细信息都存储在系统数据库information_schema下的views表中。通过views表，可以查看数据库中所有视

13、图的详细信息。mysql SELECT * FROM information_schema.views G* 1. row * TABLE_CATALOG: def TABLE_SCHEMA: db_shop TABLE_NAME: v_table VIEW_DEFINITION: select db_shop.goods.id AS id,db_ AS name from db_shop.goods CHECK_OPTION: NONE IS_UPDATABLE: YES DEFINER: rootlocalhost SECURITY_TYPE: DEFINER

14、CHARACTER_SET_CLIENT: gbkCOLLATION_CONNECTION: gbk_chinese_ci* 2. row * TABLE_CATALOG: def TABLE_SCHEMA: db_shop TABLE_NAME: v_table2【实例12-7】通过views表查看数据库中视图的详细信息。SQL语句及其执行结果如下：12.3.3 通过views表查看视图详细信息第15页，共33页。12.3.4 使用图形化工具查看视图使用Navicat for MySQL也可以查看视图，具体操作步骤如下：步骤1 启动Navicat for MySQL并连接MySQL后，双击需

15、要操作的数据库，此处为db_shop。步骤2 单击“视图”按钮，然后右击要查看的视图，在弹出的快捷菜单中选择“对象信息”，如图12-3所示。第16页，共33页。12.3.4 使用图形化工具查看视图步骤3 “常规”选项卡中显示了视图的相关参数信息，“DDL”选项卡中显示视图的定义语句，如图12-4所示。第17页，共33页。12.4修改视图第18页，共33页。当基本表的某些字段发生改变时，可以通过修改视图来保持与基本表的一致性。MySQL提供CREATE OR REPLACE VIEW和ALTER语句来修改视图。使用CREATE OR REPLACE VIEW语句修改视图的语法形式如下：12.4.

16、1 使用CREATE OR REPLACE VIEW语句修改视图CREATE OR REPLACE VIEW view_name (column_list) AS SELECT_statement;【实例12-8】使用CREATE OR REPLACE VIEW语句修改视图v_table。步骤1 执行以下语句，选择数据库db_shop，并使用DESC语句查看视图结构。USE db_shop;mysql DESC v_table;+-+-+-+-+-+-+| Field| Type | Null| Key | Default | Extra|+-+-+-+-+-+-+| id | int(11)

17、 | NO | | 0 | | name | char(30)| YES | | NULL| |+-+-+-+-+-+-+2 rows in set (0.02 sec)第19页，共33页。12.4.1 使用CREATE OR REPLACE VIEW语句修改视图mysql DESC v_table;+-+-+-+-+-+-+| Field | Type | Null| Key| Default| Extra|+-+-+-+-+-+-+| id | int(11) | NO | | 0 | | name | char(30) | YES | | NULL | | price | decimal

18、(7,2) unsigned| YES | | NULL | |+-+-+-+-+-+-+3 rows in set (0.00 sec)步骤3 使用DESC语句查看视图结构。SQL语句及其执行结果如下：步骤2 使用CREATE OR REPLACE VIEW语句修改视图v_table。SQL语句及其执行结果如下：mysql CREATE OR REPLACE VIEW v_table AS SELECT id,name,price FROM goods;Query OK, 0 rows affected (0.03 sec)第20页，共33页。12.4.2 使用ALTER语句修改视图ALTE

19、R VIEW view_name (column_list) AS SELECT_statement;【实例12-9】使用ALTER语句修改视图v_table2。步骤1 执行以下语句，选择数据库db_shop，并使用DESC语句查看视图结构。USE db_shop;mysql DESC v_table2;+-+-+-+-+-+-+| Field | Type | Null | Key | Default | Extra |+-+-+-+-+-+-+| goods_id | int(11) | NO | | 0 | | goods_name | varchar(30) | YES | | NUL

20、L | |+-+-+-+-+-+-+2 rows in set (0.00 sec)第21页，共33页。12.4.2 使用ALTER语句修改视图步骤2 执行ALTER语句，修改视图v_table2。SQL语句及其执行结果如下：mysql ALTER VIEW v_table2(goods_id,goods_name,goods_price) AS SELECT id,name,price FROM goods;Query OK, 0 rows affected (0.04 sec)步骤3 使用DESC语句查看视图结构。SQL语句及其执行结果如下：mysql DESC v_table2;+-+-

21、+-+-+-+-+| Field | Type | Null | Key| Default | Extra|+-+-+-+-+-+-+| goods_id | int(11) | NO | | 0 | | goods_name| char(30) | YES| | NULL | | goods_price| decimal(7,2) unsigned| YES| | NULL | |+-+-+-+-+-+-+3 rows in set (0.00 sec)第22页，共33页。12.4.3 使用图形化工具修改视图步骤1 启动Navicat for MySQL并连接MySQL后，双击需要操作的数据

22、库，此处为db_shop。步骤2 单击“视图”按钮，然后选中需要修改的视图，并单击“设计视图”按钮，如图12-5所示。图12-5 修改视图第23页，共33页。12.4.3 使用图形化工具修改视图步骤3 打开视图编辑界面，可在“视图创建工具”选项卡右下方编辑SQL语句，如图12-6所示。图12-6 修改视图定义语句第24页，共33页。12.5删除视图第25页，共33页。使用DROP VIEW语句可删除一个或多个视图。其基本语法格式如下：DROP VIEW IF EXISTS 视图1,视图n;【实例12-10】删除视图v_table。步骤1 执行以下语句，选择数据库db_shop。步骤2 执行以下

23、语句，删除视图v_table。USE db_shop;DROP VIEW IF EXISTS v_table;12.5.1 使用DROP VIEW语句删除视图步骤3 执行SQL语句，查看数据库中的数据表。SQL语句及其执行结果如右：mysql SHOW TABLES;+-+| Tables_in_db_shop|+-+| goods | new_goods | orders | people | people1 | people2 | people3 | people4 | reply | test_goods | v_table2 | v_table3 |+-+12 rows in set

24、(0.00 sec)第26页，共33页。12.5.2 使用图形化工具删除视图步骤1 启动Navicat for MySQL并连接MySQL后，双击需要操作的数据库，此处为db_shop。步骤2 单击“视图”按钮，然后选择需要删除的视图，单击“删除试图”按钮，最后确认删除即可，如图12-7所示。图12-7 删除视图第27页，共33页。12.6操作视图中数据第28页，共33页。由于视图是一张虚拟表，所以对视图中的数据进行操作，实际上就是对基表进行操作。当视图中的数据被修改，基表中的数据会同时被修改。同样，当基表中的数据被修改，视图中的数据也会被修改。【实例12-11】修改视图v_table2中的数

25、据。步骤1 修改视图中数据之前，分别执行SQL语句查看视图和基表的信息。结果如下：mysql SELECT * FROM v_table2;+-+-+-+| goods_id | goods_name | goods_price |+-+-+-+| 1 | 西游记 | 50.40 | 2 | 牛奶糖 | 7.50 | 3 | 水果糖 | 2.50 | 4 | 休闲西服 | 800.00 | 5 | 果汁 | 2.50 | 6 | 论语 | 109.00 | 7 | 西瓜 | 1.50 | 8 | 苹果 | 3.00 | 9 | 牛仔裤 | 120.00 | 10 | 红楼梦 | 50.50 |

26、+-+-+-+10 rows in set (0.04 sec)12.6.1 修改视图中数据mysql SELECT id,name,price FROM goods;+-+-+-+| id | name | price |+-+-+-+| 1 | 西游记 | 50.40 | 2 | 牛奶糖 | 7.50 | 3 | 水果糖 | 2.50 | 4 | 休闲西服 | 800.00 | 5 | 果汁 | 2.50 | 6 | 论语 | 109.00 | 7 | 西瓜 | 1.50 | 8 | 苹果 | 3.00 | 9 | 牛仔裤 | 120.00 | 10| 红楼梦 | 50.50 |+-+-+

27、-+10 rows in set (0.00 sec)第29页，共33页。步骤2 执行SQL语句，修改视图中数据。执行结果如下：12.6.1 修改视图中数据mysql UPDATE v_table2 SET goods_name=哈姆雷特 WHERE goods_id=1;Query OK, 1 row affected (0.10 sec)Rows matched: 1 Changed: 1 Warnings: 0步骤3 再次查看视图和基表数据。结果如下：mysql SELECT * FROM v_table2;+-+-+-+| goods_id| goods_name| goods_price|+-+-+-+| 1 | 哈姆雷特 | 50.40| 2 | 牛奶糖 | 7.50 | 3 | 水果糖 | 2.50 | 4 | 休闲西服 | 800.00