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

发布时间:2024年01月06日

目录

一.视图

? ? ? ? 1.1 含义

? ? ? ? 1.2 操作

二.案例

三.思维导图


一.视图

? ? ? ? 1.1 含义

虚拟表,查询方面和普通表一样使用。

? ? ? ? 1.2 操作

1.创建视图:

  • create or replace view 视图名
    as
    查询语句;

2.视图的修改:

  • 方式1
    • create or replace view 视图名
      as
      查询语句;
  • 方式2
    • alter view 视图名
      as
      查询语句;

3.删除视图:

  • drop view 视图名,视图名,...

4.查看视图:

  • DESC 视图名; —— 查看视图相关字段
  • SHOW CREATE VIEW 视图名; —— 查看视图相关语句

二.案例

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 "课程的情况

  • 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 )

  • 连接方式:外连接
  • 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 "课程的情况

  • 考核:子查询
  • 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 cid = '02'

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

  • 考核:聚合函数意味着分组,外连接
  • SELECT
    ?? ?s.sid,
    ?? ?s.sname,
    ?? ?ROUND( avg( sc.score ), 2 ) n?
    FROM
    ?? ?t_mysql_student s
    ?? ?LEFT JOIN t_mysql_score sc ON s.sid = sc.sid?
    GROUP BY
    ?? ?s.sid,
    ?? ?s.sname?
    HAVING
    ?? ?n >= 60

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

  • 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 )

  • 考核:聚合函数,外连接
  • 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 '李%'

三.思维导图

文章来源:https://blog.csdn.net/weixin_65972586/article/details/135426795
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。