MySQL视图(简介 & 练习)
1、视图简介
1)创建视图原因
- 经常要进行连接查询,步骤繁琐且相同;
- 数据库存在部分数据信息敏感,需要保密对外不可见。
2)视图定义
- 视图通过定制形式显示来自一个或多个表的数据(相当于将结果放在一个空间中);
- 视图是数据库对象,可以像查询普通表一样查询视图;
- 视图没有存储任何数据,只是对表的一个查询;
- 视图的定义保存在数据字典(information_schema)内。
3)视图的作用&优点
作用:
- 控制安全
- 保存查询数据
优点:
- 提供了灵活一致级别安全性
- 隐藏了数据的复杂性
- 简化了用户的SQL指令
- 通过重命名列(从另一个角度提供数据)
4)视图使用规则
- 视图必须有唯一命名;
- 在mysql中视图的数量没有限制;
- 创建视图必须从管理员那里获得必要的权限;
- 视图支持嵌套(可以利用其他视图检索出来的数据创建新的视图);
- 在视图中可以使用 OREDR BY ;
【注:如果视图内已经使用该排序子句,则视图的 ORDER BY 将覆盖前面的 ORDER BY 】 - 视图不能索引、不能关联触发器或默认值;
- 视图可以和表同时使用;
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)