2021-05-25

最新推荐文章于 2024-07-22 15:12:37 发布

CsdnCsdn695537

最新推荐文章于 2024-07-22 15:12:37 发布

阅读量208

点赞数

分类专栏： mysql 文章标签：数据库 mysql

本文链接：https://blog.csdn.net/CsdnCsdn695537/article/details/117247811

版权

mysql 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

三创建视图

学习目标

掌握创建视图的方式
知道视图（VIEW）与数据表（TABLE）之间的区别
知道视图的使用场景

0 数据集介绍

数据来自一个在线医疗网站，该网站可以在线比较各城市的医生，并可以在线预约医生，一共有三张表
- 医生，患者和就诊

Doctor 医生表

id	full_name	type	city	rating	price_per_visit
1	王建军	骨科	北京	4.5	95.00
2	李文慧	内分泌科	北京	3.8	100.00
3	孙伟	神经科	北京	4.3	100.00
4	刘琦	精神科	北京	4.1	110.00
5	张学武	风湿免疫科	深圳	3.2	80.00
6	蓝淑玲	儿科	广州	2.7	75.00
7	孙凌	精神科	天津	4.9	120.00
8	梁晓华	过敏反应科	上海	3.8	100.00
9	张兰玲	风湿免疫科	上海	4.1	90.00

patient 患者表

id	full_name
1	魏平
2	王晓磊
3	郭慧芳
4	刘丽宏
5	赵久良
6	梁东风
7	王红
8	钱晓静
9	王厚文
10	李若兰
11	张可欣
12	吴爱玲
13	钱国富

visit 就诊记录表

id	doctor_id	visit_date	patient_id
1	5	2020-02-20	1
2	4	2020-02-20	5
3	3	2020-02-20	2
4	6	2020-02-20	2
5	1	2020-02-20	6
6	3	2020-02-20	1
7	2	2020-02-21	4
8	5	2020-02-21	3
9	3	2020-02-21	7
10	7	2020-02-21	9
11	1	2020-02-21	1
12	9	2020-02-21	10
13	2	2020-02-21	8
14	5	2020-02-21	5
15	8	2020-02-22	12
16	3	2020-02-22	8
17	6	2020-02-22	9
18	4	2020-02-22	3
19	3	2020-02-22	13
20	5	2020-02-22	10
21	9	2020-02-22	7
22	8	2020-02-22	13
23	8	2020-02-23	11
24	7	2020-02-23	6
25	1	2020-02-23	12

如果想展示某次就诊的全部信息，需要把医生表，患者表和就诊表JOIN到一起，具体SQL如下：

 SELECT
   d.id AS doctor_id,
   d.full_name AS doctor_full_name,
   d.type AS doctor_type,
   d.rating AS doctor_rating,
   p.id AS patient_id,
   p.full_name AS patient_full_name,
   v.visit_date AS visit_date
 FROM doctor d
 JOIN visit v
   ON v.doctor_id = d.id
 JOIN patient p
   ON p.id = v.patient_id;

查询结果

doctor_id	doctor_full_name	doctor_type	doctor_rating	patient_id	patient_full_name	visit_date
5	张学武	风湿免疫科	3.2	1	魏平	2020/2/20
4	刘琦	精神科	4.1	5	赵久良	2020/2/20
3	孙伟	神经科	4.3	2	王晓磊	2020/2/20
6	蓝淑玲	儿科	2.7	2	王晓磊	2020/2/20
1	王建军	骨科	4.5	6	梁东风	2020/2/20
3	孙伟	神经科	4.3	1	魏平	2020/2/20
2	李文慧	内分泌科	3.8	4	刘丽宏	2020/2/21
5	张学武	风湿免疫科	3.2	3	郭慧芳	2020/2/21
3	孙伟	神经科	4.3	7	王红	2020/2/21
7	孙凌	精神科	4.9	9	王厚文	2020/2/21
1	王建军	骨科	4.5	1	魏平	2020/2/21
9	张兰玲	风湿免疫科	4.1	10	李若兰	2020/2/21
2	李文慧	内分泌科	3.8	8	钱晓静	2020/2/21
5	张学武	风湿免疫科	3.2	5	赵久良	2020/2/21
8	梁晓华	过敏反应科	3.8	12	吴爱玲	2020/2/22
3	孙伟	神经科	4.3	8	钱晓静	2020/2/22
6	蓝淑玲	儿科	2.7	9	王厚文	2020/2/22
4	刘琦	精神科	4.1	3	郭慧芳	2020/2/22
3	孙伟	神经科	4.3	13	钱国富	2020/2/22
5	张学武	风湿免疫科	3.2	10	李若兰	2020/2/22
9	张兰玲	风湿免疫科	4.1	7	王红	2020/2/22
8	梁晓华	过敏反应科	3.8	13	钱国富	2020/2/22
8	梁晓华	过敏反应科	3.8	11	张可欣	2020/2/23
7	孙凌	精神科	4.9	6	梁东风	2020/2/23
1	王建军	骨科	4.5	12	吴爱玲	2020/2/23

如果我们经常有这样的需求，需要把所有的信息放到一起展示，就需要反复写这样的查询语句。是否有办法避免这种重复劳动？使用视图（VIEW）就可以解决这样的问题

1 什么是视图

视图（VIEW）是一条SQL语句，我们用特定名称“保存”该语句，创建之后，可以把它当做一张表在其它查询中使用。视图通常称为虚拟表，因为我们可以像常规表一样查询它们

我们在创建DOCTOR表的时候使用了如下SQL语句：

 CREATE TABLE doctor (
   id integer PRIMARY KEY,
   full_name varchar(128),
   type varchar(32),
   city varchar(128),
   rating decimal(2, 1),
   price_per_visit decimal(5, 2)
 );

下面是一个简单的创建视图的示例：

 CREATE VIEW pediatrician AS
 SELECT *
 FROM doctor
 WHERE type = '儿科';

上面的代码以CREATE VIEW开头，然后是视图名称，即儿科医生。然后是AS关键字。之后，我们需要在儿科医生基础上进行进一步查询，直接把pediatrician当作一张表来使用即可
我们在Navicat的界面中双击视图pediatrician图标，即可查看视图数据

练习

创建视图就诊详细信息（visit_details）将医生，患者，就诊表连接在一起

 CREATE VIEW visit_details AS
 SELECT
   d.id AS doctor_id,
   d.full_name AS doctor_full_name,
   d.type AS doctor_type,
   d.rating AS doctor_rating,
   p.id AS patient_id,
   p.full_name AS patient_full_name,
   v.visit_date AS visit_date
 FROM doctor d
 JOIN visit v
   ON v.doctor_id = d.id
 JOIN patient p
   ON p.id = v.patient_id;

2 表和视图的区别

我们用CREATE VIEW 创建了视图，而且视图可以当做一张表来使用，那么究竟表和视图之间有啥区别？我们为什么要是用视图？
视图不包含任何数据不占用磁盘空间只是把查询的SQL语句保存了起来，数据是保存在表中的，每次查询视图时，都会查询视图下的一个或多个表来再次获取数据
视图的具体应用场景：
- 出于安全考虑，可以只向特定的用户暴露全部数据的部分行或列
- 某些时候需要在原始数据基础上创建一些临时列，例如把英里转换成公里，把摄氏度转换成华氏度
- 在SQL中如果需要经常进行聚合操作，可以在视图中提前处理

3 视图的应用

创建视图暴露部分数据,比如创建下面的视图，将医生和患者和姓名隐藏起来，访问visit_details_anonymous这个视图的用户不能看到敏感信息

 CREATE VIEW visit_details_anonymous AS
 SELECT
   doctor_id,
   doctor_type,
   doctor_rating,
   patient_id,
   visit_date
 FROM visit_details

为了方便不同地区的用户访问网站，需要调整价格显示的货币单位，比如我们目前的价格是以人民币为单位的，香港地区的用户访问我们希望显示港币价格，可以创建视图

 CREATE VIEW doctors_hdk AS
 SELECT id, type, rating, price_per_visit * 1.2 AS price_per_visit_hkd
 FROM doctor;

我们需要经常统计每个城市医生数量，并计算该城市医生的平均诊疗费用（只统计医生数量>2的城市），可以创建一个视图

 CREATE VIEW doctors_metrics AS
 SELECT city, COUNT(id) AS doctor_count, AVG(price_per_visit) AS avg_price_per_visit
 FROM doctor
 GROUP BY city
 HAVING COUNT(id) > 2;

创建视图的时候需要注意：
- 应该尽量避免在视图的基础上创建视图，多重视图不仅难于理解还会降低SQL 的性能
- 不能向视图里插入数据

小结

视图（View）可以看做一张虚拟表，它不存储数据，但是可以把视图当作一张表进行查询操作

创建视图的语法：

 CREATE VIEW textbooks_stats AS
 SELECT ...
 FROM ...
 ...

我们可以在一个视图的基础上创建另一个视图，但一般避免这么使用
视图只用于查询