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 '%李%' 

三、思维导图