MySQL视图（简介 & 练习）

1、视图简介

1）创建视图原因

1. 经常要进行连接查询，步骤繁琐且相同；
2. 数据库存在部分数据信息敏感，需要保密对外不可见。

2）视图定义

1. 视图通过定制形式显示来自一个或多个表的数据（相当于将结果放在一个空间中）；
2. 视图是数据库对象，可以像查询普通表一样查询视图；
3. 视图没有存储任何数据，只是对表的一个查询；
4. 视图的定义保存在数据字典（information_schema）内。

3）视图的作用&优点

作用：

• 控制安全
• 保存查询数据

优点：

• 提供了灵活一致级别安全性
• 隐藏了数据的复杂性
• 简化了用户的SQL指令
• 通过重命名列（从另一个角度提供数据）

4）视图使用规则

1. 视图必须有唯一命名；
2. 在mysql中视图的数量没有限制；
3. 创建视图必须从管理员那里获得必要的权限；
4. 视图支持嵌套（可以利用其他视图检索出来的数据创建新的视图）；
5. 在视图中可以使用 OREDR BY ；
   【注：如果视图内已经使用该排序子句，则视图的 ORDER BY 将覆盖前面的 ORDER BY 】
6. 视图不能索引、不能关联触发器或默认值；
7. 视图可以和表同时使用；

5）创建视图

CREATE VIEW view_name
	[(alias[, alias]...)]--为视图字段指定别名
	AS subquery
	[WITH READ ONLY];

6）修改视图

–CREATE OR REPLACE VIEW view_name
	[(alias[, alias]...)]--为视图字段指定别名
	AS subquery
	[WITH READ ONLY];

#或者

–ALTER VIEW view_name
	[(alias[, alias]...)]--为视图字段指定别名
	AS subquery
	[WITH READ ONLY];

7）删除视图

–DROP VIEW view_name;

2、素材

#创建学生表stu
CREATE TABLE stu 
(
s_id INT PRIMARY KEY,
s_name VARCHAR(20),
addr VARCHAR(50),
tel VARCHAR(50)
); 

#插入3条记录
INSERT INTO stu 
VALUES(1,'XiaoWang','Henan','0371-12345678'),
(2,'XiaoLi','Hebei','13889072345'),
(3,'XiaoTian','Henan','0371-12345670');


#创建报名表sign
CREATE TABLE sign 
(
s_id INT PRIMARY KEY,
s_name VARCHAR(20),
s_sch VARCHAR(50),
s_sign_sch VARCHAR(50)
); 

#插入3条记录
INSERT INTO sign 
VALUES(1,'XiaoWang','Middle School1','Peking University'),
(2,'XiaoLi','Middle School2','Tsinghua University'),
(3,'XiaoTian','Middle School3','Tsinghua University');


#创建成绩表stu_mark
CREATE TABLE stu_mark 
(
s_id INT PRIMARY KEY,
s_name VARCHAR(20),
mark int 
); 

#插入3条记录
INSERT INTO stu_mark VALUES(1,'XiaoWang',80),
(2,'XiaoLi',71),
(3,'XiaoTian',70);

3、练习要求

1）创建考上Peking University的学生的视图

mysql> create view Peking_University(学号,姓名,中学,考取学校,地址,电话,成绩) as
    -> select x.*,sm.mark
    -> from (select si.*,st.addr,st.tel from sign si,stu st where si.s_id=st.s_id)x,stu_mark sm
    -> where x.s_id=sm.s_id and s_sign_sch="Peking University";
Query OK, 0 rows affected (0.00 sec)

mysql> select * from Peking_University;
+--------+----------+----------------+-------------------+--------+---------------+--------+
| 学号   | 姓名     | 中学           | 考取学校          | 地址   | 电话          | 成绩   |
+--------+----------+----------------+-------------------+--------+---------------+--------+
|      1 | XiaoWang | Middle School1 | Peking University | Henan  | 0371-12345678 |     80 |
+--------+----------+----------------+-------------------+--------+---------------+--------+
1 row in set (0.00 sec)

2）创建考上Tsinghua University的学生的视图

mysql> create view Tsinghua_University(学号,姓名,中学,考取学校,地址,电话,成绩) as
    -> select x.*,sm.mark
    -> from (select si.*,st.addr,st.tel from sign si,stu st where si.s_id=st.s_id)x,stu_mark sm
    -> where x.s_id=sm.s_id and s_sign_sch='Tsinghua University';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from Tsinghua_University;
+--------+----------+----------------+---------------------+--------+---------------+--------+
| 学号   | 姓名     | 中学           | 考取学校            | 地址   | 电话          | 成绩   |
+--------+----------+----------------+---------------------+--------+---------------+--------+
|      2 | XiaoLi   | Middle School2 | Tsinghua University | Hebei  | 13889072345   |     71 |
|      3 | XiaoTian | Middle School3 | Tsinghua University | Henan  | 0371-12345670 |     70 |
+--------+----------+----------------+---------------------+--------+---------------+--------+
2 rows in set (0.00 sec)

3）XiaoTian的成绩在录入的时候录入错误多录了50分，对其录入成绩进行更正。

mysql> update stu_mark set mark=mark-50 where s_name="XiaoTian";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from stu_mark;
+------+----------+------+
| s_id | s_name   | mark |
+------+----------+------+
|    1 | XiaoWang |   80 |
|    2 | XiaoLi   |   71 |
|    3 | XiaoTian |   20 |
+------+----------+------+
3 rows in set (0.00 sec)

4）查看更新过后视图和表的情况。

mysql> select * from Tsinghua_University;
+--------+----------+----------------+---------------------+--------+---------------+--------+
| 学号   | 姓名     | 中学           | 考取学校            | 地址   | 电话          | 成绩   |
+--------+----------+----------------+---------------------+--------+---------------+--------+
|      2 | XiaoLi   | Middle School2 | Tsinghua University | Hebei  | 13889072345   |     71 |
|      3 | XiaoTian | Middle School3 | Tsinghua University | Henan  | 0371-12345670 |     20 |
+--------+----------+----------------+---------------------+--------+---------------+--------+
2 rows in set (0.00 sec)

5）查看视图的创建信息。

mysql> show create view Peking_University \G;
*************************** 1. row ***************************
                View: Peking_University
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `Peking_University` AS select `x`.`s_id` AS `学号`,`x`.`s_name` AS `姓名`,`x`.`s_sch` AS `中学`,`x`.`s_sign_sch` AS `考取学校`,`x`.`addr` AS `地址`,`x`.`tel` AS `电话`,`sm`.`mark` AS `成绩` from ((select `si`.`s_id` AS `s_id`,`si`.`s_name` AS `s_name`,`si`.`s_sch` AS `s_sch`,`si`.`s_sign_sch` AS `s_sign_sch`,`st`.`addr` AS `addr`,`st`.`tel` AS `tel` from (`homework`.`sign` `si` join `homework`.`stu` `st`) where (`si`.`s_id` = `st`.`s_id`)) `x` join `homework`.`stu_mark` `sm`) where ((`x`.`s_id` = `sm`.`s_id`) and (`x`.`s_sign_sch` = 'Peking University'))
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)

mysql> show create view Tsinghua_University \G;
*************************** 1. row ***************************
                View: Tsinghua_University
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `Tsinghua_University` AS select `x`.`s_id` AS `学号`,`x`.`s_name` AS `姓名`,`x`.`s_sch` AS `中学`,`x`.`s_sign_sch` AS `考取学校`,`x`.`addr` AS `地址`,`x`.`tel` AS `电话`,`sm`.`mark` AS `成绩` from ((select `si`.`s_id` AS `s_id`,`si`.`s_name` AS `s_name`,`si`.`s_sch` AS `s_sch`,`si`.`s_sign_sch` AS `s_sign_sch`,`st`.`addr` AS `addr`,`st`.`tel` AS `tel` from (`homework`.`sign` `si` join `homework`.`stu` `st`) where (`si`.`s_id` = `st`.`s_id`)) `x` join `homework`.`stu_mark` `sm`) where ((`x`.`s_id` = `sm`.`s_id`) and (`x`.`s_sign_sch` = 'Tsinghua University'))
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)

6）删除创建的视图。

mysql> drop view Peking_University;
Query OK, 0 rows affected (0.00 sec)

mysql> drop view Tsinghua_University;
Query OK, 0 rows affected (0.00 sec)