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