视图
创建数据库
mysql> CREATE DATABASE zhan;
Query OK, 1 row affected
mysql> CREATE DATABASE zhan;
Query OK, 1 row affected
使用数据库
mysql> USE zhan;
Database changed
mysql> USE zhan;
Database changed
一、视图
视图是从一个或多个表中导出来的表它是一种虚拟存在的表，
并且表的结构和数据都依赖于基本表。与直接基本操作表相比，
视图具有简化查询语句，安全性，逻辑数据独立性等优点
（1）创建视图
创建student表
mysql>CREATE TABLE student(
s_id INT(3),
name VARCHAR(20),
math FLOAT,
chinese FLOAT
);
Query OK, 0 rows affected
使用INSERT语句向student表中插入数据
mysql>INSERT INTO student(s_id,name,math,chinese) VALUES (1,'Tom',80,78);
Query OK, 1 row affected
mysql>INSERT INTO student(s_id,name,math,chinese) VALUES (2,'Jack',70,80);
Query OK, 1 row affected
mysql> INSERT INTO student(s_id,name,math,chinese) VALUES (3,'Lucy',97,95);
Query OK, 1 row affected
查看表中数据
mysql> SELECT*FROM student;
+------+------+------+---------+
| s_id | name | math | chinese |
+------+------+------+---------+
| 1 | Tom | 80 | 78 |
| 2 | Jack | 70 | 80 |
| 3 | Lucy | 97 | 95 |
+------+------+------+---------+
3 rows in set
创建student表的视图
mysql> CREATE VIEW view_stu AS SELECT math,chinese,math+chinese FROM student;
Query OK, 0 rows affected
使用SELECT语句查看视图
mysql> SELECT*FROM view_stu;
+------+---------+--------------+
| math | chinese | math+chinese |
+------+---------+--------------+
| 80 | 78 | 158 |
| 70 | 80 | 150 |
| 97 | 95 | 192 |
+------+---------+--------------+
3 rows in set
（2）再多表上创建视图
在student表上创建一个名为view_stu2的视图
mysql> CREATE VIEW view_stu2(math,chin,sum)AS SELECT math,chinese,math+chinese FROM student;
Query OK, 0 rows affected
查看view_stu2视图
mysql> SELECT*FROM view_stu2;
+------+------+-----+
| math | chin | sum |
+------+------+-----+
| 80 | 78 | 158 |
| 70 | 80 | 150 |
| 97 | 95 | 192 |
+------+------+-----+
3 rows in set
在student表和stu_info表上创建stu_glass视图，查询出s_id号
、姓名和班级，
创建stu_info表
mysql> CREATE TABLE stu_info(
-> s_id INT(3),
-> glass VARCHAR(50),
-> addr VARCHAR(100)
-> );
Query OK, 0 rows affected
向表中插入数据
mysql> INSERT INTO stu_info(s_id,glass,addr)VALUES(1,'erban','anhui');
Query OK, 1 row affected
mysql> INSERT INTO stu_info(s_id,glass,addr)VALUES(2,'sanban','chongqing');
Query OK, 1 row affected
mysql> INSERT INTO stu_info(s_id,glass,addr)VALUES(3,'yiban','shandong');
Query OK, 1 row affected
查询stu_info表
视图是从一个或多个表中导出来的表它是一种虚拟存在的表，
并且表的结构和数据都依赖于基本表。与直接基本操作表相比，
视图具有简化查询语句，安全性，逻辑数据独立性等优点
（1）创建视图
创建student表
mysql>CREATE TABLE student(
s_id INT(3),
name VARCHAR(20),
math FLOAT,
chinese FLOAT
);
Query OK, 0 rows affected
使用INSERT语句向student表中插入数据
mysql>INSERT INTO student(s_id,name,math,chinese) VALUES (1,'Tom',80,78);
Query OK, 1 row affected
mysql>INSERT INTO student(s_id,name,math,chinese) VALUES (2,'Jack',70,80);
Query OK, 1 row affected
mysql> INSERT INTO student(s_id,name,math,chinese) VALUES (3,'Lucy',97,95);
Query OK, 1 row affected
查看表中数据
mysql> SELECT*FROM student;
+------+------+------+---------+
| s_id | name | math | chinese |
+------+------+------+---------+
| 1 | Tom | 80 | 78 |
| 2 | Jack | 70 | 80 |
| 3 | Lucy | 97 | 95 |
+------+------+------+---------+
3 rows in set
创建student表的视图
mysql> CREATE VIEW view_stu AS SELECT math,chinese,math+chinese FROM student;
Query OK, 0 rows affected
使用SELECT语句查看视图
mysql> SELECT*FROM view_stu;
+------+---------+--------------+
| math | chinese | math+chinese |
+------+---------+--------------+
| 80 | 78 | 158 |
| 70 | 80 | 150 |
| 97 | 95 | 192 |
+------+---------+--------------+
3 rows in set
（2）再多表上创建视图
在student表上创建一个名为view_stu2的视图
mysql> CREATE VIEW view_stu2(math,chin,sum)AS SELECT math,chinese,math+chinese FROM student;
Query OK, 0 rows affected
查看view_stu2视图
mysql> SELECT*FROM view_stu2;
+------+------+-----+
| math | chin | sum |
+------+------+-----+
| 80 | 78 | 158 |
| 70 | 80 | 150 |
| 97 | 95 | 192 |
+------+------+-----+
3 rows in set
在student表和stu_info表上创建stu_glass视图，查询出s_id号
、姓名和班级，
创建stu_info表
mysql> CREATE TABLE stu_info(
-> s_id INT(3),
-> glass VARCHAR(50),
-> addr VARCHAR(100)
-> );
Query OK, 0 rows affected
向表中插入数据
mysql> INSERT INTO stu_info(s_id,glass,addr)VALUES(1,'erban','anhui');
Query OK, 1 row affected
mysql> INSERT INTO stu_info(s_id,glass,addr)VALUES(2,'sanban','chongqing');
Query OK, 1 row affected
mysql> INSERT INTO stu_info(s_id,glass,addr)VALUES(3,'yiban','shandong');
Query OK, 1 row affected
查询stu_info表
mysql> SELECT*FROM stu_info;
+------+--------+-----------+
| s_id | glass | addr |
+------+--------+-----------+
| 1 | erban | anhui |
| 2 | sanban | chongqing |
| 3 | yiban | shandong |
+------+--------+-----------+
3 rows in set
创建stu_glass视图
mysql> CREATE VIEW stu_glass(id,NAME,glass)
-> AS
-> SELECT student.s_id,student.name,stu_info.glass
-> FROM student,stu_info
-> WHERE student.s_id=stu_info.s_id;
Query OK, 0 rows affected
查看stu_glass视图
mysql> SELECT*FROM stu_glass;
+----+------+--------+
| id | NAME | glass |
+----+------+--------+
| 1 | Tom | erban |
| 2 | Jack | sanban |
| 3 | Lucy | yiban |
+----+------+--------+
3 rows in set
（3）查看视图
使用DESCRIBE语句查看stu_glass视图
mysql> DESCRIBE stu_glass;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(3) | YES | | NULL | |
| NAME | varchar(20) | YES | | NULL | |
| glass | varchar(50) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set
不同字段的讲解
NULL:表示该列是否可以存储NULL值
KEY:表示该列是否已经编制索引
Default:表示该列是否有默认值
Extra:表示获取到的与给定列相关的附加信息
使用SHOW TABLE STATUS语句查看stu_glass视图
mysql> SHOW TABLE STATUS LIKE'stu_glass'\G
+------+--------+-----------+
| s_id | glass | addr |
+------+--------+-----------+
| 1 | erban | anhui |
| 2 | sanban | chongqing |
| 3 | yiban | shandong |
+------+--------+-----------+
3 rows in set
创建stu_glass视图
mysql> CREATE VIEW stu_glass(id,NAME,glass)
-> AS
-> SELECT student.s_id,student.name,stu_info.glass
-> FROM student,stu_info
-> WHERE student.s_id=stu_info.s_id;
Query OK, 0 rows affected
查看stu_glass视图
mysql> SELECT*FROM stu_glass;
+----+------+--------+
| id | NAME | glass |
+----+------+--------+
| 1 | Tom | erban |
| 2 | Jack | sanban |
| 3 | Lucy | yiban |
+----+------+--------+
3 rows in set
（3）查看视图
使用DESCRIBE语句查看stu_glass视图
mysql> DESCRIBE stu_glass;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(3) | YES | | NULL | |
| NAME | varchar(20) | YES | | NULL | |
| glass | varchar(50) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set
不同字段的讲解
NULL:表示该列是否可以存储NULL值
KEY:表示该列是否已经编制索引
Default:表示该列是否有默认值
Extra:表示获取到的与给定列相关的附加信息
使用SHOW TABLE STATUS语句查看stu_glass视图
mysql> SHOW TABLE STATUS LIKE'stu_glass'\G
+-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------+----------+----------------+---------+
| stu_glass | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | VIEW |
+-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------+----------+----------------+---------+
1 row in set
（4）修改视图
通过DESC语句查看修改是之前view_stu视图和student表中的字段信息
mysql> DESC view_stu;
+--------------+--------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------+------+-----+---------+-------+
| math | float | YES | | NULL | |
| chinese | float | YES | | NULL | |
| math+chinese | double | YES | | NULL | |
+--------------+--------+------+-----+---------+-------+
3 rows in set
studnet 视图查询结果
mysql> DESC student;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| s_id | int(3) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| math | float | YES | | NULL | |
| chinese | float | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
4 rows in set
对view_stu视图进行修改
mysql> CREATE OR REPLACE VIEW view_stu AS SELECT*FROM student;
Query OK, 0 rows affected
使用DESC 语句查看视图
mysql> DESC view_stu;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| s_id | int(3) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| math | float | YES | | NULL | |
| chinese | float | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
4 rows in set
使用ALTER 语句修改视图
mysql> ALTER VIEW view_stu AS SELECT chinese FROM student;
Query OK, 0 rows affected
查看修改后的view_stu视图
mysql> DESC view_stu;
+---------+-------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------+------+-----+---------+-------+
| chinese | float | YES | | NULL | |
+---------+-------+------+-----+---------+-------+
1 row in set
（5）更新视图
更新视图是通过视图来更新、插入、删除基本表中的数据
使用SELECT 查询语句分别查看视图和表中chinese字段信息
mysql> SELECT chinese FROM view_stu;
+---------+
| chinese |
+---------+
| 78 |
| 80 |
| 95 |
+---------+
3 rows in set
mysql> SELECT chinese FROM student;
+---------+
| chinese |
+---------+
| 78 |
| 80 |
| 95 |
+---------+
3 rows in set
使用UPDATE语句更新视图view_stu中的chinese字段值
mysql> UPDATE view_stu SET chinese=100;
Query OK, 3 rows affected
Rows matched: 3 Changed: 3 Warnings: 0
再次分别查看chinese字段的数据信息
mysql> SELECT chinese FROM view_stu;
+---------+
| chinese |
+---------+
| 100 |
| 100 |
| 100 |
+---------+
3 rows in set
mysql> SELECT chinese FROM student;
+---------+
| chinese |
+---------+
| 100 |
| 100 |
| 100 |
+---------+
3 rows in set
mysql> SELECT*FROM view_stu2;
+------+------+-----+
| math | chin | sum |
+------+------+-----+
| 80 | 100 | 180 |
| 70 | 100 | 170 |
| 97 | 100 | 197 |
+------+------+-----+
3 rows in set
使用INSERT语句向studnet 表中插入一条数据
mysql> INSERT INTO student VALUES(4,'Lily',100,100);
Query OK, 1 row affected
使用SELECT语句查看student表中语句
mysql> SELECT*FROM student;
+------+------+------+---------+
| s_id | name | math | chinese |
+------+------+------+---------+
| 1 | Tom | 80 | 100 |
| 2 | Jack | 70 | 100 |
| 3 | Lucy | 97 | 100 |
| 4 | Lily | 100 | 100 |
+------+------+------+---------+
4 rows in set
在studne表中添加数据之前view_stu2中的数据信息
mysql> SELECT*FROM view_stu2;
+------+------+-----+
| math | chin | sum |
+------+------+-----+
| 80 | 100 | 180 |
| 70 | 100 | 170 |
| 97 | 100 | 197 |
| 100 | 100 | 200 |
+------+------+-----+
4 rows in set
使用DELETE语句在view_stu2视图中删除一条记录
mysql> DELETE FROM view_stu2 WHERE math=70;
Query OK, 1 row affected
使用SELECT语句查看删除数据后的view_stu2视图中的数据信息
mysql> SELECT*FROM view_stu2;
+------+------+-----+
| math | chin | sum |
+------+------+-----+
| 80 | 100 | 180 |
| 97 | 100 | 197 |
| 100 | 100 | 200 |
+------+------+-----+
3 rows in set
查看删除数据后的student表中的数据变化情况
mysql> SELECT*FROM student;
+------+------+------+---------+
| s_id | name | math | chinese |
+------+------+------+---------+
| 1 | Tom | 80 | 100 |
| 3 | Lucy | 97 | 100 |
| 4 | Lily | 100 | 100 |
+------+------+------+---------+
3 rows in set
当试图中包含有如下内容是，视图的更新操作将不能被执行：
视图中不包含基本表中被定义为费控的列
在定义视图的SELECT语句后的字段列表中使用了数学表达式
在定时视图的SELECT语句后的字段列表中使用聚合函数
在定义视图的SELECT语句中使用了DISTINCT ,UNION,TOP,GROUP BY,HAVING语句
（6）删除视图
删除stu_glass视图
mysql> DROP VIEW IF EXISTS view_stu2;
Query OK, 0 rows affected
查看view_stu2视图
mysql> SELECT*FROM view_stu2;
1146 - Table 'abc.view_stu2' doesn't exist
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------+----------+----------------+---------+
| stu_glass | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | VIEW |
+-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------+----------+----------------+---------+
1 row in set
（4）修改视图
通过DESC语句查看修改是之前view_stu视图和student表中的字段信息
mysql> DESC view_stu;
+--------------+--------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------+------+-----+---------+-------+
| math | float | YES | | NULL | |
| chinese | float | YES | | NULL | |
| math+chinese | double | YES | | NULL | |
+--------------+--------+------+-----+---------+-------+
3 rows in set
studnet 视图查询结果
mysql> DESC student;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| s_id | int(3) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| math | float | YES | | NULL | |
| chinese | float | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
4 rows in set
对view_stu视图进行修改
mysql> CREATE OR REPLACE VIEW view_stu AS SELECT*FROM student;
Query OK, 0 rows affected
使用DESC 语句查看视图
mysql> DESC view_stu;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| s_id | int(3) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| math | float | YES | | NULL | |
| chinese | float | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
4 rows in set
使用ALTER 语句修改视图
mysql> ALTER VIEW view_stu AS SELECT chinese FROM student;
Query OK, 0 rows affected
查看修改后的view_stu视图
mysql> DESC view_stu;
+---------+-------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------+------+-----+---------+-------+
| chinese | float | YES | | NULL | |
+---------+-------+------+-----+---------+-------+
1 row in set
（5）更新视图
更新视图是通过视图来更新、插入、删除基本表中的数据
使用SELECT 查询语句分别查看视图和表中chinese字段信息
mysql> SELECT chinese FROM view_stu;
+---------+
| chinese |
+---------+
| 78 |
| 80 |
| 95 |
+---------+
3 rows in set
mysql> SELECT chinese FROM student;
+---------+
| chinese |
+---------+
| 78 |
| 80 |
| 95 |
+---------+
3 rows in set
使用UPDATE语句更新视图view_stu中的chinese字段值
mysql> UPDATE view_stu SET chinese=100;
Query OK, 3 rows affected
Rows matched: 3 Changed: 3 Warnings: 0
再次分别查看chinese字段的数据信息
mysql> SELECT chinese FROM view_stu;
+---------+
| chinese |
+---------+
| 100 |
| 100 |
| 100 |
+---------+
3 rows in set
mysql> SELECT chinese FROM student;
+---------+
| chinese |
+---------+
| 100 |
| 100 |
| 100 |
+---------+
3 rows in set
mysql> SELECT*FROM view_stu2;
+------+------+-----+
| math | chin | sum |
+------+------+-----+
| 80 | 100 | 180 |
| 70 | 100 | 170 |
| 97 | 100 | 197 |
+------+------+-----+
3 rows in set
使用INSERT语句向studnet 表中插入一条数据
mysql> INSERT INTO student VALUES(4,'Lily',100,100);
Query OK, 1 row affected
使用SELECT语句查看student表中语句
mysql> SELECT*FROM student;
+------+------+------+---------+
| s_id | name | math | chinese |
+------+------+------+---------+
| 1 | Tom | 80 | 100 |
| 2 | Jack | 70 | 100 |
| 3 | Lucy | 97 | 100 |
| 4 | Lily | 100 | 100 |
+------+------+------+---------+
4 rows in set
在studne表中添加数据之前view_stu2中的数据信息
mysql> SELECT*FROM view_stu2;
+------+------+-----+
| math | chin | sum |
+------+------+-----+
| 80 | 100 | 180 |
| 70 | 100 | 170 |
| 97 | 100 | 197 |
| 100 | 100 | 200 |
+------+------+-----+
4 rows in set
使用DELETE语句在view_stu2视图中删除一条记录
mysql> DELETE FROM view_stu2 WHERE math=70;
Query OK, 1 row affected
使用SELECT语句查看删除数据后的view_stu2视图中的数据信息
mysql> SELECT*FROM view_stu2;
+------+------+-----+
| math | chin | sum |
+------+------+-----+
| 80 | 100 | 180 |
| 97 | 100 | 197 |
| 100 | 100 | 200 |
+------+------+-----+
3 rows in set
查看删除数据后的student表中的数据变化情况
mysql> SELECT*FROM student;
+------+------+------+---------+
| s_id | name | math | chinese |
+------+------+------+---------+
| 1 | Tom | 80 | 100 |
| 3 | Lucy | 97 | 100 |
| 4 | Lily | 100 | 100 |
+------+------+------+---------+
3 rows in set
当试图中包含有如下内容是，视图的更新操作将不能被执行：
视图中不包含基本表中被定义为费控的列
在定义视图的SELECT语句后的字段列表中使用了数学表达式
在定时视图的SELECT语句后的字段列表中使用聚合函数
在定义视图的SELECT语句中使用了DISTINCT ,UNION,TOP,GROUP BY,HAVING语句
（6）删除视图
删除stu_glass视图
mysql> DROP VIEW IF EXISTS view_stu2;
Query OK, 0 rows affected
查看view_stu2视图
mysql> SELECT*FROM view_stu2;
1146 - Table 'abc.view_stu2' doesn't exist
浙公网安备 33010602011771号