MySQL视图 索引 面试题

一. 视图

视图：一种虚拟存在的表，行和列的数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的，只保存了sql逻辑，不保存查询结果

视图语法

-- 创建
create view 视图名
as
查询语句;

-- 使用
select * from 视图名;

-- 修改
create or replace view 视图名
as
查询语句;

alter view 视图名
as 查询语句;

应用场景：

• 多个地方用到同样的查询结果
• 该查询结果使用的sql语句较复杂

视图的好处：

• 重用sql语句
• 简化复杂的sql操作，不必知道它的查询细节
• 保护数据，提高安全性

创建：

create view 视图名 as 查询语句

? ? ? 如果在创建视图遇到了问题，可能是以下几种情况之一：

1. 表不存在：?请确保真实的数据表确实存在于你的数据库中。

2. 列不存在：?确保表中有列。可以通过执行?DESCRIBE 表名;?或者?SHOW COLUMNS FROM 表名;?来查看表的列信息。

3. 权限问题：?确保你有足够的权限来执行?CREATE VIEW?操作。

?查看：

show create view 视图名

?

使用：

select * from 视图名

?

修改：

方式 1：如果视图存在则进行修改
create or replace view 视图名 as 查询语句

方式 2：直接对已存在的视图进行修改
alter view 视图名 as 查询语句

删除：

drop view 视图名

更新内容：

insert into 视图名 values(值，...)

update 视图名 set 列名=值

delete from 视图名

• 在对视图更新的同时，对原表也进行了更新。
• 一般对视图中不进行更新，而且许多类型的视图不支持更新：
• 创建视图的sql语句中包含分组函数、distinct、group by、having、union。
• 常量视图。
• 创建视图的sql语句中select中包含子查询。
• 创建视图的sql语句中用到了join或其他连接（可以修改，不能插入删除）。
• 创建视图的sql语句中from了一个不能更新的视图。
• 创建视图的sql语句中where子句的子查询引用了from子句中的表。
• 视图与表：
• 创建语句不同。
• 视图没有实际占用数据存储的物理空间。而表实际占有。
• 视图一般不进行增删改，只进行查询。而表都可以进行。

二. 索引?

索引概述：

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。

数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构（B树）就是索引。总之，就是排序+查找功能。

为了加快查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录。

索引的目的在于提高查询效率，可以类比字典。

如果要查“mysql”这个单词，我们肯定需要定位到m字母，然后从下往下找到y字母，再找到剩下的sql。

如果没有索引，就需要遍历所有表项。

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。

平常所说的索引，如果没有特别指明，都是指B树（多路搜索树，并不一定是二叉的）结构组织的索引。其中聚集索引，次要索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。

索引：

提高数据检索的效率，降低数据库的IO成本。

通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。

劣势：

实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的。

虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。

索引只是提高效率的一个因素，如果有大数据量的表，就需要花时间研究建立最优秀的索引。

索引的分类：

Primary Key（聚集索引）：InnoDB存储引擎的表会存在主键（唯一非null），如果建表的时候没有指定主键，则会使用第一非空的唯一索引作为聚集索引，否则InnoDB会自动帮你创建一个不可见的、长度为6字节的row_id用来作为聚集索引。

单列索引：单列索引即一个索引只包含单个列

组合索引：组合索引指在表的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用。使用组合索引时遵循最左前缀集合

Unique（唯一索引）：索引列的值必须唯一，但允许有空值。若是组合索引，则列值的组合必须唯一。主键索引是一种特殊的唯一索引，不允许有空值

Key（普通索引）：是MySQL中的基本索引类型，允许在定义索引的列中插入重复值和空值

FULLTEXT（全文索引）：全文索引类型为FULLTEXT，在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建

SPATIAL（空间索引）：空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING和POLYGON。MySQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类似的语法创建空间索引。创建空间索引的列必须声明为NOT NULL

一般而言，一张表中的索引不超过5个。

索引的操作：

创建：

create index 索引名 on 表名(索引列)。
alter 表名 add index [索引名] on (索引列)。

可以将index替换为primary、unique、fulltext，分别表示主键索引、唯一索引和全文索引。

删除：

drop index [索引名] on 表名

查看：

show index from 表名

索引的结构：B树索引。

那些情况需要创建索引：

主键自动建立唯一索引。

频繁作为查询条件的字段应该创建索引。

查询中与其它表关联的字段，外键关系建立索引。

频繁更新的字段不适合创建索引。

Where条件里用不到的字段不创建索引。

在高并发下倾向创建组合索引。

查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度。

查询中统计或者分组字段。

那些情况不适合创建索引：

表记录太少。

经常增删改的表。

数据重复（差异率低）并且分布均匀。

索引的选择性：索引列不同的值个数 / 索引列记录数，越接近1效率越高。

性能分析：

MySQL Query Optimizer：优化select语句的执行效率。

MySQL常见瓶颈：

CPU：CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候。

IO：磁盘I/O瓶颈发生在装入数据远大于内存容量的时候。

服务器硬件的性能瓶颈：top，free，iostat和vmstat来查看系统的性能状态。

三 .MySQL面试题

3.1 SQL脚本

/*
 Navicat Premium Data Transfer
 Source Server         : localhost
 Source Server Type    : MySQL
 Source Server Version : 80018
 Source Host           : localhost:3306
 Source Schema         : mybatis_ssm
 Target Server Type    : MySQL
 Target Server Version : 80018
 File Encoding         : 65001
 Date: 04/07/2023 23:53:33
*/
 
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
 
-- ----------------------------
-- Table structure for t_mysql_course
-- ----------------------------
DROP TABLE IF EXISTS `t_mysql_course`;
CREATE TABLE `t_mysql_course`  (
  `cid` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '课程编号',
  `cname` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '课程名称',
  `tid` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '教师编号',
  PRIMARY KEY (`cid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '课程信息表' ROW_FORMAT = Dynamic;
 
-- ----------------------------
-- Records of t_mysql_course
-- ----------------------------
INSERT INTO `t_mysql_course` VALUES ('01', '语文', '02');
INSERT INTO `t_mysql_course` VALUES ('02', '数学', '01');
INSERT INTO `t_mysql_course` VALUES ('03', '英语', '03');
 
-- ----------------------------
-- Table structure for t_mysql_score
-- ----------------------------
DROP TABLE IF EXISTS `t_mysql_score`;
CREATE TABLE `t_mysql_score`  (
  `sid` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '学生编号，外键',
  `cid` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '课程编号，外键',
  `score` float NULL DEFAULT 0 COMMENT '成绩',
  INDEX `sid`(`sid`) USING BTREE,
  INDEX `cid`(`cid`) USING BTREE,
  CONSTRAINT `t_mysql_score_ibfk_1` FOREIGN KEY (`sid`) REFERENCES `t_mysql_student` (`sid`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `t_mysql_score_ibfk_2` FOREIGN KEY (`cid`) REFERENCES `t_mysql_course` (`cid`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '成绩信息表' ROW_FORMAT = Dynamic;
 
-- ----------------------------
-- Records of t_mysql_score
-- ----------------------------
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);
 
-- ----------------------------
-- Table structure for t_mysql_student
-- ----------------------------
DROP TABLE IF EXISTS `t_mysql_student`;
CREATE TABLE `t_mysql_student`  (
  `sid` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '学生编号',
  `sname` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '学生名称',
  `sage` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '学生年龄',
  `ssex` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '学生性别',
  PRIMARY KEY (`sid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '学生信息表' ROW_FORMAT = Dynamic;
 
-- ----------------------------
-- Records of t_mysql_student
-- ----------------------------
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', '女');
 
-- ----------------------------
-- Table structure for t_mysql_teacher
-- ----------------------------
DROP TABLE IF EXISTS `t_mysql_teacher`;
CREATE TABLE `t_mysql_teacher`  (
  `tid` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '教师编号',
  `tname` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '教师名称',
  PRIMARY KEY (`tid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '教师信息表' ROW_FORMAT = Dynamic;
 
-- ----------------------------
-- Records of t_mysql_teacher
-- ----------------------------
INSERT INTO `t_mysql_teacher` VALUES ('01', '张三');
INSERT INTO `t_mysql_teacher` VALUES ('02', '李四');
INSERT INTO `t_mysql_teacher` VALUES ('03', '王五');
 
SET FOREIGN_KEY_CHECKS = 1;

?3.2 面试题实战

01）查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数

SELECT
	s.*,
	( CASE WHEN t1.cid = '01' THEN t1.score END ) 01课程,
	( CASE WHEN t2.cid = '02' THEN t2.score END ) 02课程 
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 ) 01课程,
?? ?( CASE WHEN t2.cid = '02' THEN t2.score END ) 02课程?
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
?? ?*?
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 ) ) 分数?
FROM
?? ?t_mysql_student s
?? ?LEFT JOIN t_mysql_score sc ON 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
?? ?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.cid ),
?? ?SUM( sc.score )?
FROM
?? ?t_mysql_score sc,
?? ?t_mysql_student s?
WHERE
?? ?sc.sid = s.sid?
GROUP BY
?? ?s.sid,
?? ?s.sname

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

select count(*) from t_mysql_teacher where tname like '李%'

思维导图: