## 目录 [TOC] ## mysql认知  ## 三大范式是什么？ * 第一范式：数据表中的每一列（每个字段）必须是不可拆分的最小单元，也就是确保每一列的原子性； * 第二范式（2NF）：满足1NF后，要求表中的所有列，都必须依赖于主键，而不能有任何一列与主键没有关系，也就是说一个表只描述一件事情； * 第三范式：必须先满足第二范式（2NF），要求：表中的每一列只与主键直接相关而不是间接相关，（表中的每一列只能依赖于主键）； ## 怎么区分三大范式？   第 一范式和第二范式在于有没有分出两张表，第二范式是说一张表中包含了所种不同的实体属性，那么要必须分成多张表， 第三范式是要求已经分成了多张表，那么一张表中只能有另一张表中的id（主键），而不能有其他的任何信息（其他的信息一律用主键在另一表查询）。 ## 数据库五大约束是什么？ 1.primary KEY:设置主键约束； 2.UNIQUE：设置唯一性约束，不能有重复值； 3.DEFAULT 默认值约束，height DOUBLE(3,2)DEFAULT 1.2 height不输入是默认为1,2 4.NOT NULL：设置非空约束，该字段不能为空； 5.FOREIGN key :设置外键约束。 ## 主键是什么，怎么设置主键？ 主键默认非空，默认唯一性约束，只有主键才能设置自动增长，自动增长一定是主键，主键不一定自动增长； 在定义列时设置：ID INT PRIMARY KEY 在列定义完之后设置：primary KEY（id） ## 数据库的外键是什么？ 只有INNODB的数据库引擎支持外键。 不见已使用基于mysql的物理外键，这样可能会有超出预期的后果。推荐使用逻辑外键，就是自己做表设计，根据代码逻辑设定的外键，自行实现相关的数据操作。 ## innodb和myisam有什么区别？ * InnoDB支持事务，而MyISAM不支持事物，崩溃后无法安全恢复，表锁非常影响性能 * InnoDB支持行级锁，而MyISAM支持表级锁 * InnoDB支持MVCC，实现了四个标准的隔离级别 而MyISAM不支持 * InnoDB 表是基于聚族索引建立的，聚族索引对主键查询有很高的性能 * InnoDB支持外键，而MyISAM不支持 * MyISAM 存储引擎已经有了20年的历史，在1995年时，MyISAM 是 MySQL 唯一的存储引擎，服务了20多年，即将退居二线。随着mysql5.7，8版本的提升，myisam优点已经逐渐被 InnoDB 实现了。比如全文索引，表空间优化，临时表优化，高效的count(*) ## 什么是索引？ 索引是一种数据结构,可以帮助我们快速的进行数据的查找. ## 索引是个什么样的数据结构呢? 索引的数据结构和具体存储引擎的实现有关, 在MySQL中使用较多的索引有Hash索引,B+树索引等,而我们经常使用的InnoDB存储引擎的默认索引实现为:B+树索引. ## innodb索引的实现原理是什么？ InnoDB使用的是聚簇索引，将主键组织到一棵B+树中，而行数据就储存在叶子节点上，若使用"where id = 14"这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。若对Name列进行条件搜索，则需要两个步骤：第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。 ## btree和hash类型的索引有什么不同？ 首先要知道Hash索引和B+树索引的底层实现原理: hash索引底层就是hash表,进行查找时,调用一次hash函数就可以获取到相应的键值,之后进行回表查询获得实际数据.B+树底层实现是多路平衡查找树.对于每一次的查询都是从根节点出发,查找到叶子节点方可以获得所查键值,然后根据查询判断是否需要回表查询数据. 那么可以看出他们有以下的不同: * hash索引进行等值查询更快(一般情况下),但是却无法进行范围查询. 因为在hash索引中经过hash函数建立索引之后,索引的顺序与原顺序无法保持一致,不能支持范围查询.而B+树的的所有节点皆遵循(左节点小于父节点,右节点大于父节点,多叉树也类似),天然支持范围. * hash索引不支持使用索引进行排序,原理同上. * hash索引不支持模糊查询以及多列索引的最左前缀匹配.原理也是因为hash函数的不可预测.**AAAA**和**AAAAB**的索引没有相关性. * hash索引任何时候都避免不了回表查询数据,而B+树在符合某些条件(聚簇索引,覆盖索引等)的时候可以只通过索引完成查询. * hash索引虽然在等值查询上较快,但是不稳定.性能不可预测,当某个键值存在大量重复的时候,发生hash碰撞,此时效率可能极差.而B+树的查询效率比较稳定,对于所有的查询都是从根节点到叶子节点,且树的高度较低. 因此,在大多数情况下,直接选择B+树索引可以获得稳定且较好的查询速度.而不需要使用hash索引. ## 什么是覆盖索引？ 简单的说，select的数据列只用从索引中就能够取得，不必从数据表中读取，换句话说查询列要被所使用的索引覆盖。 覆盖索引必须要存储索引列的值，而哈希索引、空间索引和全文索引不存储索引列的值，所以mysql**只能用B-tree索引**做覆盖索引。 ## B+树在满足聚簇索引和覆盖索引的时候不需要回表查询数据，什么是聚簇索引? 在B+树的索引中,叶子节点可能存储了当前的key值,也可能存储了当前的key值以及整行的数据,这就是聚簇索引和非聚簇索引. 在InnoDB中,只有主键索引是聚簇索引,如果没有主键,则挑选一个唯一键建立聚簇索引.如果没有唯一键,则隐式的生成一个键来建立聚簇索引. 当查询使用聚簇索引时,在对应的叶子节点,可以获取到整行数据,因此不用再次进行回表查询. ## 在建立索引的时候,都有哪些需要考虑的因素呢? 建立索引的时候一般要考虑到字段的使用频率,经常作为条件进行查询的字段比较适合.如果需要建立联合索引的话,还需要考虑联合索引中的顺序.此外也要考虑其他方面,比如防止过多的所有对表造成太大的压力.这些都和实际的表结构以及查询方式有关. ## 联合索引/多列索引的注意事项是什么？ MySQL可以使用多个字段同时建立一个索引,叫做联合索引.在联合索引中,如果想要命中索引,需要按照建立索引时的字段顺序挨个使用,否则无法命中索引. 具体原因为: MySQL使用索引时需要索引有序,假设现在建立了"name,age,school"的联合索引,那么索引的排序为: 先按照name排序,如果name相同,则按照age排序,如果age的值也相等,则按照school进行排序. 当进行查询时,此时索引仅仅按照name严格有序,因此必须首先使用name字段进行等值查询,之后对于匹配到的列而言,其按照age字段严格有序,此时可以使用age字段用做索引查找,,,以此类推.因此在建立联合索引的时候应该注意索引列的顺序,一般情况下,将查询需求频繁或者字段选择性高的列放在前面.此外可以根据特例的查询或者表结构进行单独的调整. ## 导致索引失效的原因有哪些？ * 列参与了数学运算或者函数； * 如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)； * 对于多列索引，不符合最左匹配的命中规则； * like查询是以%开头； * 如果直接查比用索引快，那么数据库会自动选择最优方式，不用索引； * in 和 not in 也要慎用，否则会导致全表扫描。 ## Explain 怎么用来做sql优化？ EXPLAIN 命令用法十分简单, 在 SELECT 语句前加上 Explain 就可以了, 例如: ~~~ EXPLAIN SELECT * from user_info WHERE id < 300; ~~~ EXPLAIN 命令的输出内容大致如下: ~~~ mysql> explain select * from user_info where id = 2\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: user_info partitions: NULL type: const possible_keys: PRIMARY key: PRIMARY key_len: 8 ref: const rows: 1 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec) ~~~ 各列的含义如下: * id: SELECT 查询的标识符. 每个 SELECT 都会自动分配一个唯一的标识符. * select_type: SELECT 查询的类型. * table: 查询的是哪个表 * partitions: 匹配的分区 * type: 判断此次查询是`全表扫描`还是`索引扫描` * possible_keys: 此次查询中【可能】选用的索引 * key: 此次查询中【确切】使用到的索引. * key_len: 使用了索引的字节数，这个字段可以评估组合索引是否完全被使用。 * ref: 哪个字段或常数与 key 一起被使用 * rows: 显示此查询一共扫描了多少行. 这个是一个估计值.越小性能越好。 * filtered: 表示此查询条件所过滤的数据的百分比 * extra: 额外的信息， 需要重点关注的列：select_type，type，possible_keys，keys，key_len， rows，Extra。 （1）type 类型的性能比较： `ALL < index < range ~ index_merge < ref < eq_ref < const < system` `ALL`类型因为是全表扫描, 因此在相同的查询条件下, 它是速度最慢的. 而`index`类型的查询虽然不是全表扫描, 但是它扫描了所有的索引, 因此比 ALL 类型的稍快. 后面的几种类型都是利用了索引来查询数据, 因此可以过滤部分或大部分数据, 因此查询效率就比较高了. `system`: 表中只有一条数据. 这个类型是特殊的`const`类型。 `const`: 针对主键或唯一索引的等值查询扫描, 最多只返回一行数据. const 查询速度非常快, 因为它仅仅读取一次即可. `range`: 表示使用索引范围查询。 （2）rows基本是第一眼要看的指标。 （3）全表扫描时, possible_keys 和 key 字段都是 NULL。 （4）当 Extra 显示： * Using filesort 表示 MySQL 需额外的排序操作, 不能通过索引顺序达到排序效果. 一般有`Using filesort`, 都建议优化去掉, 因为这样的查询 CPU 资源消耗大。 * Using index "覆盖索引扫描", 表示查询在索引树中就可查找所需数据, 不用扫描表数据文件, 往往说明性能不错 * Using temporary 查询有使用临时表, 一般出现于排序, 分组和多表 join 的情况, 查询效率不高, 建议优化. ## 主键使用自增ID还是UUID? 推荐使用自增ID,不要使用UUID. 因为在InnoDB存储引擎中,主键索引是作为聚簇索引存在的,也就是说,主键索引的B+树叶子节点上存储了主键索引以及全部的数据(按照顺序),如果主键索引是自增ID,那么只需要不断向后排列即可,如果是UUID,由于到来的ID与原来的大小不确定,会造成非常多的数据插入,数据移动,然后导致产生很多的内存碎片,进而造成插入性能的下降. 总之,在数据量大一些的情况下,用自增主键性能会好一些. ## 字段为什么要求定义为not null? MySQL官网这样介绍: > NULL columns require additional space in the rowto record whether their values are NULL. For MyISAM tables, each NULL columntakes one bit extra, rounded up to the nearest byte. null值会占用更多的字节,且会在程序中造成很多与预期不符的情况. ## drop、delete与truncate分别在什么场景之下使用？ 我们来对比一下他们的区别： drop table * 1)属于DDL * 2)不可回滚 * 3)不可带where * 4)表内容和结构删除 * 5)删除速度快 truncate table * 1)属于DDL * 2)不可回滚 * 3)不可带where * 4)表内容删除 * 5)删除速度快 delete from * 1)属于DML * 2)可回滚 * 3)可带where * 4)表结构在，表内容要看where执行的情况 * 5)删除速度慢,需要逐行删除 总结： **不再需要一张表的时候，用drop** **想删除部分数据行时候，用delete，并且带上where子句** **保留表而删除所有数据的时候用truncate** ## MySQL中的varchar和char有什么区别? 1. char的长度是不可变的，而varchar的长度是可变的 。 2. 定义一个char\[10\]和varchar\[10\],如果存进去的是‘abcd’,那么char所占的长度依然为10，除了字符‘abcd’外，后面跟六个空格，而varchar就立马把长度变为4了，取数据的时候，char类型的要用trim()去掉多余的空格，而varchar是不需要的， 3. char的存取速度比varchar要快得多，因为其长度固定，方便程序的存储与查找；但是char也为此付出的是空间的代价，因为其长度固定，所以难免会有多余的空格占位符占据空间，可谓是以空间换取时间效率，而varchar是以空间效率为首位的。 4. char的存储方式是，对英文字符（ASCII）占用1个字节，对一个汉字占用两个字节；而varchar的存储方式是，对每个英文字符占用2个字节，汉字也占用2个字节，两者的存储数据都非unicode的字符数据。 5. char适合存储长度固定的数据，varchar适合存储长度不固定的。 ## varchar(10)和int(10)代表什么含义? varchar的10代表了申请的空间长度,也是可以存储的数据的最大长度,而int的10只是代表了展示的长度,不足10位以0填充.也就是说,int(1)和int(10)所能存储的数字大小以及占用的空间都是相同的,只是在展示时按照长度展示. ## 超大分页怎么处理? 超大的分页一般从两个方向上来解决. * 数据库层面,这也是我们主要集中关注的(虽然收效没那么大),类似于`select * from table where age > 20 limit 1000000,10`这种查询其实也是有可以优化的余地的. 这条语句需要load1000000数据然后基本上全部丢弃,只取10条当然比较慢. 当时我们可以修改为`select * from table where id in (select id from table where age > 20 limit 1000000,10)`.这样虽然也load了一百万的数据,但是由于索引覆盖,要查询的所有字段都在索引中,所以速度会很快. 同时如果ID连续的好,我们还可以`select * from table where id > 1000000 limit 10`,效率也是不错的,优化的可能性有许多种,但是核心思想都一样,就是减少load的数据. * 从需求的角度减少这种请求....主要是不做类似的需求(直接跳转到几百万页之后的具体某一页.只允许逐页查看或者按照给定的路线走,这样可预测,可缓存)以及防止ID泄漏且连续被人恶意攻击. 解决超大分页,其实主要是靠缓存,可预测性的提前查到内容,缓存至redis等k-V数据库中,直接返回即可. 在阿里巴巴《Java开发手册》中,对超大分页的解决办法是类似于上面提到的第一种. ## 关心过业务系统里面的sql耗时吗?统计过慢查询吗?对慢查询都怎么优化过? 在业务系统中,除了使用主键进行的查询,其他的我都会在测试库上测试其耗时,慢查询的统计主要由运维在做,会定期将业务中的慢查询反馈给我们. 慢查询的优化首先要搞明白慢的原因是什么? 是查询条件没有命中索引?是load了不需要的数据列?还是数据量太大? 所以优化也是针对这三个方向来的, * 首先分析语句,看看是否load了额外的数据,可能是查询了多余的行并且抛弃掉了,可能是加载了许多结果中并不需要的列,对语句进行分析以及重写. * 分析语句的执行计划,然后获得其使用索引的情况,之后修改语句或者修改索引,使得语句可以尽可能的命中索引. * 如果对语句的优化已经无法进行,可以考虑表中的数据量是否太大,如果是的话可以进行横向或者纵向的分表. ## 上面提到横向分表和纵向分表,可以分别举一个适合他们的例子吗? 横向分表是按行分表，假设我们有一张用户表，主键是自增ID且同时是用户的ID.数据量较大,有1亿多条，那么此时放在一张表里的查询效果就不太理想。我们可以根据主键ID进行分表，无论是按尾号分，或者按ID的区间分都是可以的，假设按照尾号0-99分为100个表，那么每张表中的数据就仅有100w，这时的查询效率无疑是可以满足要求的。 纵向分表是按列分表。假设我们现在有一张文章表，包含字段`id-摘要-内容`，而系统中的展示形式是刷新出一个列表。列表中仅包含标题和摘要，当用户点击某篇文章进入详情时才需要正文内容。此时，如果数据量大,将内容这个很大且不经常使用的列放在一起会拖慢原表的查询速度，我们可以将上面的表分为两张。`id-摘要`,`id-内容`.当用户点击详情，那主键再来取一次内容即可。而增加的存储量只是很小的主键字段.代价很小。 当然,分表其实和业务的关联度很高。在分表之前一定要做好调研以及benchmark.不要按照自己的猜想盲目操作、 ## LEFT JOIN 、RIGHT JOIN、INNER JOIN 区别？ * LEFT JOIN(左连接)：获取左表所有记录，即使右表没有对应匹配的记录 * RIGHT JOIN(右连接)： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录 * INNER JOIN(内连接)：获取两个表中字段匹配关系的记录 ## UNION、UNION ALL区别？ union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序； union All：对两个结果集进行并集操作，包括重复行，不进行排序； ## 说一说常用 的MySQL 函数 数学函数 * floor(x) 返回不大于 x 的最大整数值 * ceil/ceiling(x) 返回不小于 x 的最小整数 * round(x) 四舍五入 * rand() 随机函数\[0, 1) * abs(x) 返回 x 的绝对值 字符串函数 * concat(str1, str2, ...) 将参数连接成字符串返回 * length(str) 返回字符串长度 日期和时间函数 * now() 当前时间 * curdate() 当前日期 ~~~ SELECT UNIX_TIMESTAMP('2019-05-07 22:55:00'); #1557240900 SELECT FROM_UNIXTIME(1557240900); #2019-05-07 22:55:00 ~~~ 系统信息函数 * VERSION() 返回数据库的版本号 * LAST\_INSERT\_ID() 返回最后生成的 AUTO\_INCREMENT 值 加密函数 * PASSWORD(str) 对字符串 str 进行加密 * MD5(str) 对字符串 str 进行加密 格式化函数 * FORMAT(x, n) 可以将数字 x 进行格式化，保留到小数点后 n 位，四舍五入 ~~~ SELECT FORMAT(2.7895, 2); #2.79 ~~~ 获取当前时间（年月日时分秒） ~~~ mysql> select now(); +---------------------+ | now() | +---------------------+ | 2020-05-22 10:39:23 | +---------------------+ ~~~ 获取当前时间戳 ~~~ mysql> select unix_timestamp(now()); +-----------------------+ | unix_timestamp(now()) | +-----------------------+ | 1590115200 | +-----------------------+ 1 row in set (0.00 sec) ~~~ 获取年月日 ~~~ mysql> SELECT CURDATE(); +------------+ | CURDATE() | +------------+ | 2020-05-22 | +------------+ 1 row in set (0.00 sec) ~~~ ## 你的sql优化常用技巧有哪些？ 1. 使用参数化查询：防止SQL注入，预编译SQL命令提高效率 2. 去掉不必要的查询和搜索字段 3. 争取命中索引，或者根据已有的查询情景简历合理的索引 4. 不要使用select \*：不要使用select \*，以提高查询效率，减少输出的数据量，提高传输速度 5. 尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理 6. 减少访问数据库的次数，业务代码逻辑优化，避免for循环 7. 使用表的别名(Alias)：当在SQL语句中连接多个表时, 减少解析的时间，减少那些由Column歧义引起的语法错误 8. 使用列的别名：当列的名称很长的时候，使用简短的列的别名可以查询结果更清晰，更简洁 9. 状态，类型等字段使用tinyint类型 10. 字段设计not null 11. 索引可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因此索引并非越多越好，合理有效的使用 12. 尽量避免大事务操作，提高系统并发能力 已上要点，回答几个应该也就OK了，毕竟面试时间不能总是在聊一道题。 ## 同一个字段，用 int 还是 char 查询效率高? 从效率来说，INT效率更高。查询速度也和是否建立索引，字段长度占用的空间大小有关系。 ## SQL编写题，一些实例，考察一下 给定四个表: * `student`（学生表） * `teacher`（老师表） * `course`（课程表） * `sc`（成绩表） **「表结构以及部分数据如下图展示」**  根据题目要求，写出SQL语句。 **「问题列表」** ~~~ 1、查询每个学生的学号、姓名和每门课的成绩 2、查询都学过2号同学（sid=2）学习过的课程的同学的学号 3、查询“语文（cid=1）”课程比“数学（cid=2）”课程成绩高的所有学生的学号； 4、查询平均成绩大于60分的同学的学号和平均成绩； 5、查询所有同学的学号、姓名、选课数、总成绩； 6、查询姓“李”的老师的个数； 7、查询没学过“叶平”老师课的同学的学号、姓名； 8、查询学过“语文（cid=1）”并且也学过“数学（cid=2）”课程的同学的学号、姓名； 9、查询学过“叶平”老师所教的所有课的同学的学号、姓名； 10、查询课程编号“数学（cid=2）”的成绩比课程编号“语文（cid=1）”课程低的所有同学的学号、姓名； 11、查询所有课程成绩小于60分的同学的学号、姓名； 12、查询没有学全所有课的同学的学号、姓名； 13、按平均成绩从高到低显示所有学生的“语文“、“数学”、“英语”三门的课程成绩，按如下形式显示： 学生 ID,语文,数学,英语,有效课程数,有效平均分 14、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分 15、查询男生、女生人数, 以如下形式显示：男生人数，女生人数 16、查询课程名称为“数学”，且分数低于60的学生姓名和分数 17、查询两门以上不及格课程的同学的学号及其平均成绩； 18、检索“cid=4”课程分数小于60，按分数降序排列的同学学号 ~~~ ### **1、查询每个学生的学号、姓名和每门课的成绩；** （1）利用隐式内联，只输出两表中都有的字段 ~~~ SQL语句： SELECT student.sid AS 学生ID, student.sname AS 学生姓名, (SELECT course.cname FROM course WHERE course.cid=sc.cid) AS 课程名称, sc.score AS 成绩 FROM student,sc WHERE student.sid = sc.sid ~~~ （2）利用左连接，假如左表有，右表没有，则按左表为准，右表缺省字段置为NULL SQL语句： ~~~ SELECT student.sid AS 学生ID, student.sname AS 学生姓名, (SELECT course.cname FROM course WHERE course.cid=sc.cid) AS 课程名称, sc.score AS 成绩 FROM student LEFT JOIN sc ON student.sid = sc.sid ~~~ 因为 student 表 和 sc 表没有冗余字段，所以（1）和（2）两种查询方法结果一致。 ### **2、查询都学过2号同学（sid=2）学习过的课程的同学的学号** SQL语句： ~~~ SELECT sid FROM sc WHERE cid IN (SELECT cid FROM sc WHERE sid=2) GROUP BY sid HAVING COUNT(*) = (SELECT COUNT(*) FROM sc WHERE sid=2) ~~~ ### **3、查询“语文（cid=1）”课程比“数学（cid=2）”课程成绩高的所有学生的学号；** SQL语句： ~~~ SELECT a.sid FROM (SELECT sid, score FROM sc WHERE cid=1) a, (SELECT sid, score FROM sc WHERE cid=2) b WHERE a.sid=b.sid AND a.score > b.score; ~~~ ### **4、查询平均成绩大于60分的同学的学号和平均成绩；** SQL语句： ~~~ SELECT sid, AVG(score) FROM `sc` GROUP BY sid HAVING AVG(score) > 60; ~~~ ### **5、查询所有同学的学号、姓名、选课数、总成绩；** SQL： ~~~ SELECT a.sid, a.sname, COUNT(a.cid), SUM(a.score) FROM (SELECT student.sid, student.sname, sc.cid, sc.score FROM student JOIN sc WHERE student.sid=sc.sid) a GROUP BY sid; ~~~ ### **6、查询姓“周”的老师的个数；** SQL： ~~~ SELECT COUNT(*) FROM `teacher` WHERE tname LIKE '周%'; ~~~ ### **7、查询没学过“叶平”老师课的同学的学号、姓名；** SQL： ~~~ # 参考答案 SELECT student.sid,student.sname FROM student WHERE sid NOT IN ( SELECT DISTINCT(sc.sid) FROM sc, course, teacher WHERE sc.cid=course.cid AND teacher.tid=course.tid AND teacher.tname='叶平' ); ~~~ ### **8、查询学过“语文（cid=1）”并且也学过“数学（cid=2）”课程的同学的学号、姓名；** SQL： ~~~ SELECT student.sid,student.sname FROM student, sc WHERE student.sid=sc.sid AND sc.cid=1 AND EXISTS ( SELECT * FROM sc AS sc_2 WHERE sc_2.sid=sc.sid AND sc_2.cid=2); ~~~ ### **9、查询学过“叶平”老师所教的所有课的同学的学号、姓名；** SQL： ~~~ SELECT student.sid,student.sname FROM student WHERE sid IN ( SELECT sid FROM sc ,course ,teacher WHERE sc.cid=course.cid AND teacher.tid=course.tid AND teacher.tname='叶平' GROUP BY sid HAVING COUNT(sc.cid)= ( SELECT COUNT(cid) FROM course,teacher WHERE teacher.tid=course.tid AND teacher.tname='叶平' ) ); ~~~ ### **10、查询课程编号“数学（cid=2）”的成绩比课程编号“语文（cid=1）”课程低的所有同学的学号、姓名；** SQL： ~~~ SELECT sid,sname FROM student WHERE sid= ( SELECT a.sid FROM (SELECT sid, score FROM sc WHERE cid=1) a, (SELECT sid, score FROM sc WHERE cid=2) b WHERE a.sid=b.sid AND a.score > b.score ) ~~~ ### **「11」、查询所有课程成绩小于60分的同学的学号、姓名；** SQL： ~~~ SELECT sid,sname FROM student WHERE sid NOT IN ( SELECT student.sid FROM student AS s,sc WHERE s.sid=sc.sid AND score>60 ); ~~~ ### **12、查询没有学全所有课的同学的学号、姓名；** SQL： ~~~ SELECT student.sid, student.sname FROM student,sc WHERE student.sid=sc.sid GROUP BY sid HAVING COUNT(cid) < (SELECT COUNT(cid) FROM course) ~~~ ### **13、按平均成绩从高到低显示所有学生的“语文“、“数学”、“英语”三门的课程成绩，按如下形式显示： 学生ID,语文,数学,英语,有效课程数,有效平均分** SQL： ~~~ SELECT sid AS 学生ID, (SELECT score FROM sc WHERE sc.sid=sc_2.sid AND cid=1) AS 语文, (SELECT score FROM sc WHERE sc.sid=sc_2.sid AND cid=2) AS 数学, (SELECT score FROM sc WHERE sc.sid=sc_2.sid AND cid=3) AS 英语, COUNT(*) AS 有效课程数, AVG(score) FROM sc AS sc_2 GROUP BY sid ORDER BY AVG(sc_2.score) ~~~ ### **14、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分** SQL： ~~~ SELECT cid AS 课程ID, MAX(score) AS 最高分, MIN(score) AS 最低分 FROM sc GROUP BY cid ~~~ ### **15、查询男生、女生人数 ：以如下形式显示：男生人数，女生人数** SQL： ~~~ SELECT (SELECT COUNT(ssex) FROM student GROUP BY ssex HAVING ssex='男') AS 男生人数, (SELECT COUNT(ssex) FROM student GROUP BY ssex HAVING ssex='女') AS 女生人数; ~~~ ### **16、查询课程名称为“数学”，且分数低于60的学生姓名和分数** SQL： ~~~ SELECT student.sid, student.sname FROM student,sc WHERE student.sid=sc.sid AND cid=(SELECT cid FROM course WHERE cname='数学') AND score > 60 ~~~ ### **17、查询两门及两门以上不及格课程的同学的学号及其平均成绩** SQL： ~~~ SELECT sid, AVG(score) FROM sc WHERE sid IN (SELECT sid FROM sc WHERE score < 60 GROUP BY sid HAVING COUNT(*)>1) GROUP BY sid; ~~~ ### **18、检索“cid=4”课程分数小于60，按分数降序排列的同学学号** SQL： ~~~ SELECT sid FROM sc WHERE cid=4 AND score < 60 ORDER BY score DESC ~~~ ## hash索引的实现原理是什么？ 哈希索引的实现基于哈希算法。哈希算法是一种常用的算法，时间复杂度为`O(1)`。它不仅应用在索引上，各个数据库应用中也都会使用。 InnoDB存储引擎使用哈希算法来对字典进行查找，哈希碰撞采用转链表解决。所以当hash碰撞过多的时候，查询效率就会降低很多。 ## 讲一下你理解的B+树索引是怎么实现的？ B+树中的B不是代表的二叉（Binary） ，而是代表平衡（Balance），因为B+树是从最早的平衡二叉树演化而来，但是B+树不是一个二叉树。 **一步一步的迭代** 1. 二叉树：相当于一个二分查找，二叉查找能大大提升查询的效率，但是极端情况下，二叉树会变成一个线性链表结构。 2. 平衡二叉树：通过旋转让失衡二叉树恢复平衡。缺点是数据量达到几百万后，树的高度会很恐怖，导致搜索效率不足。其二，存储的数据内容太少，没有很好利用操作系统和磁盘数据交换特性。 3. 多路平衡查找树(Balance Tree，也叫B-tree)： B-Tree是为磁盘等外存储设备设计的一种平衡查找树。B-Tree结构的数据可以让系统高效的找到数据所在的磁盘块。 B-Tree相对于AVLTree缩减了节点个数，使每次磁盘I/O取到内存的数据都发挥了作用，从而提高了查询效率。 4. B+TREE：    B+Tree是在B-Tree基础上的一种优化，使其更适合实现外存储索引结构，在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。 **B+Tree相对于B-Tree有几点不同：** 1. **非叶子节点只存储键值信息。** 2. **所有叶子节点之间都有一个链指针。** 3. **数据记录都存放在叶子节点中** 可以参考：https://blog.csdn.net/qq_36098284/article/details/80178336 InnoDB存储引擎就是用B+Tree实现其索引结构。 ## 索引是如何存储在磁盘上的？ 数据库中的B+Tree索引可以分为聚集索引（clustered index）和辅助索引（secondary index）。  上面的B+Tree示例图在数据库中的实现即为聚集索引，聚集索引的B+Tree中的叶子节点存放的是整张表的行记录数据。 辅助索引与聚集索引的区别在于：       辅助索引的叶子节点并不包含行记录的全部数据，而是存储相应行数据的聚集索引键，即主键。 当通过辅助索引来查询数据时，InnoDB存储引擎会遍历辅助索引找到主键，然后再通过主键在聚集索引中找到完整的行记录数据。