Mysql之视图&索引&执行计划

LiZiZai_

已于 2024-01-07 10:13:52 修改

阅读量781

点赞数 21

文章标签： mysql 数据库

于 2024-01-07 00:54:21 首次发布

本文链接：https://blog.csdn.net/LiZiZai_/article/details/135434486

版权

本文详细介绍了MySQL中的视图概念、操作方法，包括创建、修改、删除以及查看视图，通过实际案例展示了内连接、外连接、行转列等技巧，并结合聚合函数和思维导图，呈现了多种查询场景的应用。

摘要由CSDN通过智能技术生成

一、视图

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