MySQL之视图&内连接、外连接、子查询

一、视图

1.1 含义

虚拟表，和普通表一样使用

视图（view）是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，数据库中只存放了视图的定义，而并没有存放视图中的数据，这些数据存放在原来的表中。

2.1 视图的基本语法

创建视图

create view 视图名
as
查询语句;

查看视图有两种方式

方式1：
这里看到的是视图的字段
DESC 视图名;
方式2：
这里可以看到视图的创表语句
SHOW CREATE VIEW 视图名

视图的修改

修改视图有两种方式

方式1：
创建视图的时候你可以更改视图
CREATE OR REPLACE VIEW
AS
查询语句;

方式2：
ALTER VIEW 视图名
AS
查询语句;

删除视图

DROP 视图名

二、案例

01）查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数

? 1.涉及表：t_mysql_student,t_mysql_score
? 2.连接方式：内连接
? 3.行转列：流程函数

• SELECT
  ?s.*,
  ? (CASE WHEN t1.cid='01' then t1.score END) 语文,
  ?? ?(CASE WHEN t2.cid='02' then t2.score END) 数学
  FROM
  ?? ?t_mysql_student s,
  ?? ?( SELECT * FROM t_mysql_score WHERE cid = '01' ) t1,
  ?? ?( SELECT * FROM t_mysql_score WHERE cid = '02' ) t2?
  WHERE
  ?? ?s.sid = t1.sid?
  ?? ?AND t1.sid = t2.sid?
  ?? ?AND t1.score > t2.score

02）查询同时存在" 01 "课程和" 02 "课程的情况?

? 1.涉及表：t_mysql_student,t_mysql_score
? 2.连接方式：内连接
? 3.行转列：流程函数

• SELECT
  s.*,
  ? (CASE WHEN t1.cid='01' then t1.score END) 语文,
  ?? ?(CASE WHEN t2.cid='02' then t2.score END) 数学
  FROM
  ?? ?t_mysql_student s,
  ?? ?( SELECT * FROM t_mysql_score WHERE cid = '01' ) t1,
  ?? ?( SELECT * FROM t_mysql_score WHERE cid = '02' ) t2?
  WHERE
  ?? ?s.sid = t1.sid?
  ?? ?AND t1.sid = t2.sid

?03）查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )

? 1.涉及表：t_mysql_student,t_mysql_score
? 2.连接方式：外连接
? 3.行转列：流程函数

• SELECT
  s.*,
  ?? ?(CASE WHEN t1.cid='01' then t1.score END) 语文,
  ?? ?(CASE WHEN t2.cid='02' then t2.score END) 数学
  FROM
  ?? ?t_mysql_student s INNER JOIN?
  ?? ?( SELECT * FROM t_mysql_score WHERE cid = '01' ) t1
  ?? ?ON s.sid = t1.sid LEFT JOIN?
  ?? ?( SELECT * FROM t_mysql_score WHERE cid = '02' ) t2?
  ?? ?ON t1.sid = t2.sid

04）查询不存在" 01 "课程但存在" 02 "课程的情况

? 1.下面代码使用的是子查询

• ?SELECT
  s.*,
  ?? ?(CASE WHEN sc.cid='01' then sc.score END) 语文,
  ?? ?(CASE WHEN sc.cid='02' then sc.score END) 数学?
  FROM
  ?? ?t_mysql_student s,
  ?? ?t_mysql_score sc?
  WHERE
  ?? ?s.sid = sc.sid and?
  ?? ?s.sid NOT IN ( SELECT sid FROM t_mysql_score WHERE cid = '01' )
  AND sc.cid = '02'

05）查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

? 1.涉及表：t_mysql_student,t_mysql_score
? 2.连接方式：外连接

• SELECT
  s.sid,
  ?? ?s.sname,
  ?? ?ROUND(avg(sc.score),2) 平均分数
  FROM
  ?? ?t_mysql_student s LEFT JOIN?
  ?? ?t_mysql_score sc on s.sid = sc.sid
  GROUP BY s.sid,s.sname
  HAVING 平均分数 >= 60?

06）查询在t_mysql_score表存在成绩的学生信息

? 1.涉及表：t_mysql_student,t_mysql_score
? 2.连接方式：内连接

• SELECT
  s.sid,s.sname
  FROM
  ?? ?t_mysql_student s INNER JOIN
  ?? ?t_mysql_score sc ON s.sid = sc.sid
  GROUP BY s.sid,s.sname

07）查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )?

??1.涉及表：t_mysql_student,t_mysql_score
? 2.连接方式：外连接

• SELECT
  s.sid,
  ?? ?s.sname,
  ?? ?count(sc.score) 选课总数,
  ?? ?sum(sc.score) 总成绩
  FROM
  ?? ?t_mysql_student s
  ?? ?LEFT JOIN t_mysql_score sc ON s.sid = sc.sid?
  GROUP BY
  ?? ?s.sid,
  ?? ?s.sname?

08）查询「李」姓老师的数量

• SELECT
  ?? ?count(*)?
  FROM
  ?? ?t_mysql_teacher?
  WHERE
  ?? ?tname LIKE '%李%'?

三、思维导图