MySQL视图&索引&执行计划&相关十五道面试题分享

已于 2024-01-09 09:59:54 修改
阅读量1.3k 收藏 25
点赞数 21
CC 4.0 BY-SA版权
文章标签： android mysql 数据库
于 2024-01-06 14:40:07 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/lijie1025/article/details/135426040
本文详细介绍了MySQL数据库中的视图、索引以及执行计划的概念、操作、优缺点和适用场景。包括创建、修改、删除视图，理解索引对查询性能的影响，以及如何通过执行计划分析SQL查询。同时提供了一些涉及多表联查、内外连接和子查询的实际面试题供学习者参考。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

目录

一. 视图

1.1 含义

1.2 操作

创建视图

修改视图

删除视图

查看视图

二. 索引

2.1 什么是索引

2.2 为什么要使用索引

2.3 优点

2.4 缺点

2.5 何时不适用索引

2.6 索引何时失效

三. 执行计划

3.1 什么是执行计划

3.2 执行计划的作用

四. 面试题

表结构

表数据

题目

一. 视图

1.1 含义

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

1.2 操作

创建视图

create view 视图名 as 查询语句;

修改视图

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

2、alert view 视图名 as 查询语句;

删除视图

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

查看视图

1、desc 视图名; ➡查看视图相关字段；

2、show create view 视图名; ➡查看视图相关语句

二. 索引

2.1 什么是索引

索引是一种特殊的数据库结构，由数据表中的一列或多列组成，可以用来快速查询数据表中有某一特定值的记录。

2.2 为什么要使用索引

使用索引可以很大程度上提高数据库的查询速度，还有效提升了数据库系统的性能。

2.3 优点

  • 通过创建唯一索引可以保证数据库表中每一行数据的唯一性。
  • 可以给所有的MySQL列类型设置索引。
  • 可以大大加快数据的查询速度，这是使用索引最主要的原因。
  • 在实现数据的参考完整性方面，可以加快表与表之间的连接。
  • 在使用分组和排序子句进行数据查询时，可以减少查询中分组的排序的时间

2.4 缺点

  • 创建和维护索引组需要耗费时间，并且随着数据量的增加所耗费的时间也会增加。
  • 索引需要占磁盘空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间。如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。
  • 当对表中的数据进行增删改时，索引也要动态维护，这就降低了数据的维护速度。

2.5 何时不适用索引

  •  表记录太少。
  • 经常增删改的表
  • 数据重复且分布均匀的表字段，只应该为经常查询和最经常排序的数据列建立索引（如果某个数据类包含太多的重复数据，建立索引没有太大意义）
  • 频繁更新的字段不适合创建索引（会增加IO负担）
  • where条件里用不到的字段不创建索引

2.6 索引何时失效

  • like以通配符%开头索引失效
  • 当全表查询比走索引查询快时，会使用全表扫描，而不走索引
  • 字符串不加单引号索引会失效
  • where中索引列使用了函数（例如substring字符串截取函数）
  • where中索引列有运算（用了<or>右边的索引会失效，用<=or>=索引不会失效）
  • is null可以走索引，is not null无法使用索引（取决于某一列的具体情况）
  • 复合索引没有用到左列字段（最左前缀法则，如果没用到最左列索引，或中间跳过了某列有索引的列，索引会部分失效）
  • 条件中有or，前面的列有索引，后面的列没有，索引会失效。想让索引生效，只能将or条件中的每个列都加上索引

三. 执行计划

3.1 什么是执行计划

使用explain关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的sql语句的。分析你的查询语句或是表结构的性能瓶颈。

3.2 执行计划的作用

  • 表的读取顺序
  • 数据读取操作的操作类型
  • 哪些索引可以使用
  • 哪些索引被实际使用
  • 表之间的引用
  • 每张表有多少行被优化器查

四. 面试题

表结构

-- 1.学生表-t_mysql_student
-- sid 学生编号,sname 学生姓名,sage 学生年龄,ssex 学生性别

-- 2.教师表-t_mysql_teacher
-- tid 教师编号,tname 教师名称

-- 3.课程表-t_mysql_course
-- cid 课程编号,cname 课程名称,tid 教师名称

-- 4.成绩表-t_mysql_score
-- sid 学生编号,cid 课程编号,score 成绩

表数据

-- 学生表
insert into t_mysql_student values('01' , '赵雷' , '1990-01-01' , '男');
insert into t_mysql_student values('02' , '钱电' , '1990-12-21' , '男');
insert into t_mysql_student values('03' , '孙风' , '1990-12-20' , '男');
insert into t_mysql_student values('04' , '李云' , '1990-12-06' , '男');
insert into t_mysql_student values('05' , '周梅' , '1991-12-01' , '女');
insert into t_mysql_student values('06' , '吴兰' , '1992-01-01' , '女');
insert into t_mysql_student values('07' , '郑竹' , '1989-01-01' , '女');
insert into t_mysql_student values('09' , '张三' , '2017-12-20' , '女');
insert into t_mysql_student values('10' , '李四' , '2017-12-25' , '女');
insert into t_mysql_student values('11' , '李四' , '2012-06-06' , '女');
insert into t_mysql_student values('12' , '赵六' , '2013-06-13' , '女');
insert into t_mysql_student values('13' , '孙七' , '2014-06-01' , '女');

-- 教师表
insert into t_mysql_teacher values('01' , '张三');
insert into t_mysql_teacher values('02' , '李四');
insert into t_mysql_teacher values('03' , '王五');

-- 课程表
insert into t_mysql_course values('01' , '语文' , '02');
insert into t_mysql_course values('02' , '数学' , '01');
insert into t_mysql_course values('03' , '英语' , '03');

-- 成绩表
insert into t_mysql_score values('01' , '01' , 80);
insert into t_mysql_score values('01' , '02' , 90);
insert into t_mysql_score values('01' , '03' , 99);
insert into t_mysql_score values('02' , '01' , 70);
insert into t_mysql_score values('02' , '02' , 60);
insert into t_mysql_score values('02' , '03' , 80);
insert into t_mysql_score values('03' , '01' , 80);
insert into t_mysql_score values('03' , '02' , 80);
insert into t_mysql_score values('03' , '03' , 80);
insert into t_mysql_score values('04' , '01' , 50);
insert into t_mysql_score values('04' , '02' , 30);
insert into t_mysql_score values('04' , '03' , 20);
insert into t_mysql_score values('05' , '01' , 76);
insert into t_mysql_score values('05' , '02' , 87);
insert into t_mysql_score values('06' , '01' , 31);
insert into t_mysql_score values('06' , '03' , 34);
insert into t_mysql_score values('07' , '02' , 89);
insert into t_mysql_score values('07' , '03' , 98);

题目

考核点：多表联查、内外连接、子查询

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 sc WHERE sc.cid = '01' ) t1,
	( SELECT * FROM t_mysql_score sc WHERE sc.cid = '02' ) t2 
WHERE
	s.sid = t1.sid 
	AND s.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 sc WHERE sc.cid = '01' ) t1,
	( SELECT * FROM t_mysql_score sc WHERE sc.cid = '02' ) t2 
WHERE
	s.sid = t1.sid 
	AND s.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 sc WHERE sc.cid = '01' ) t1 ON s.sid = t1.sid
	LEFT JOIN ( SELECT * FROM t_mysql_score sc WHERE sc.cid = '02' ) t2 ON s.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_score sc ,
	t_mysql_student s
WHERE
s.sid=sc.sid and
	sc.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 ) ) 平均成绩 
FROM
	t_mysql_student s,
	t_mysql_score sc 
WHERE
	s.sid = sc.sid 
GROUP BY
	s.sid,
	s.sname 
HAVING
	平均成绩 >= 60


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

SELECT
	s.sid,
	s.sname
FROM
	t_mysql_student s,
	t_mysql_score sc 
WHERE
	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,
	t_mysql_score sc 
WHERE
	s.sid = sc.sid 
GROUP BY
	s.sid,
	s.sname


08）查询「李」姓老师的数量

SELECT
	count( * ) 
FROM
	t_mysql_teacher 
WHERE
	tname LIKE '李%'


09）查询学过「张三」老师授课的同学的信息

SELECT
	s.*,
	t.tname,
	c.cname,
	sc.score 
FROM
	t_mysql_student s,
	t_mysql_teacher t,
	t_mysql_course c,
	t_mysql_score sc 
WHERE
	s.sid = sc.sid 
	AND sc.cid = c.cid 
	AND c.tid = t.tid 
	AND t.tname = '张三'


10）查询没有学全所有课程的同学的信息

SELECT
	s.sid,
	s.sname,
	count( sc.score ) 课程总数 
FROM
	t_mysql_student s,
	t_mysql_score sc 
WHERE
	s.sid = sc.sid 
GROUP BY
	s.sid,
	s.sname 
HAVING
	课程总数 < ( SELECT count( * ) FROM t_mysql_course )


11）查询没学过"张三"老师讲授的任一门课程的学生姓名

SELECT
	s.* 
FROM
	t_mysql_student s 
WHERE
	s.sid NOT IN (
SELECT
	sc.sid 
FROM
	t_mysql_teacher t,
	t_mysql_score sc,
	t_mysql_course c 
WHERE
	sc.cid = c.cid 
	AND c.tid = t.tid 
	AND t.tname = '张三' 
	)


12）查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

SELECT
	s.sid,
	s.sname,
	ROUND( AVG( sc.score ), 2 ) 平均成绩,
	count( sc.cid ) 课程总数 
FROM
	t_mysql_student s,
	t_mysql_score sc 
WHERE
	s.sid = sc.sid 
	AND sc.score < 60 GROUP BY s.sid, s.sname HAVING 课程总数 >=2


13）检索" 01 "课程分数小于 60，按分数降序排列的学生信息

SELECT
	s.* 
FROM
	t_mysql_student s,
	t_mysql_score sc 
WHERE
	s.sid = sc.sid 
	AND sc.cid = '01' 
	AND sc.score < 60 
ORDER BY
	sc.score DESC


14）按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

SELECT
	s.sid,
	s.sname,
	ROUND( AVG( sc.score ), 2 ) 平均成绩,
	sum( ( CASE WHEN sc.cid = '01' THEN sc.score END ) ) 语文,
	sum( ( CASE WHEN sc.cid = '02' THEN sc.score END ) ) 数学,
	sum( ( CASE WHEN sc.cid = '03' THEN sc.score END ) ) 英语 
FROM
	t_mysql_student s,
	t_mysql_score sc 
WHERE
	s.sid = sc.sid 
GROUP BY
	s.sid,
	s.sname 
ORDER BY
	平均成绩 DESC


15）查询各科成绩最高分、最低分和平均分：
以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

SELECT
	c.cid,
	c.cname,
	max( sc.score ) 最高分,
	min( sc.score ) 最低分,
	ROUND( avg( sc.score ), 2 ) 平均分,
CONCAT(ROUND(sum(if(sc.score>=60,1,0))/count(sc.score)*100),'%')及格率,
CONCAT(ROUND(sum(if(sc.score>=70 and sc.score<80,1,0))/count(sc.score)*100),'%')中等率,
CONCAT(ROUND(sum(if(sc.score>=80 and sc.score<90,1,0))/count(sc.score)*100),'%')优良率,
CONCAT(ROUND(sum(if(sc.score>=90,1,0))/count(sc.score)*100),'%')优秀率,
count( sc.score ) 选修人数 
FROM
	t_mysql_score sc,
	t_mysql_course c 
WHERE
	sc.cid = c.cid 
GROUP BY
	c.cid,
	c.cname 
ORDER BY
	选修人数 DESC,
	c.cid ASC

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

不念那年晚春

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值