MySQL之视图&案例

目录

一、视图

含义

操作

创建视图

视图的修改

查看视图

删除视图

二、连表查询案例

三、思维导图​

一、视图

含义

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

操作

创建视图

create view 视图名

as
查询语句

视图的修改

alter view 视图名

as
查询语句

查看视图

DESC 视图名;查看视图相关字段

SHOW CREATE VIEW 视图名；查看视图相关语句

删除视图

drop view 视图名视图名,...;

二、连表查询案例

01）查询" 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 
    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 
*
from 
    ( SELECT * FROM t_mysql_score WHERE cid = '01' ) t1
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 sc.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 '李%' 

三、思维导图