目录
虚拟表,查询方面和普通表一样使用。
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.score02)查询同时存在" 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.sid04)查询不存在" 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 >= 6006)查询在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.sname07)查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 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.sname08)查询「李」姓老师的数量
- SELECT
?? ?count(*)?
FROM
?? ?t_mysql_teacher?
WHERE
?? ?tname LIKE '李%'