| 主文章（我总结的面试题的索引目录—进不去就说明我还没写完） |
|---|
| https://blog.csdn.net/grd_java/article/details/122357831 |
文章目录
1. 关于IO磁盘的知识
1. 什么是局部性原理，什么是磁盘预读？
- 这两个是数据结构，尤其是树这种，深度太深影响效率的主要因素
- 局部性原理：
- 空间局部性：经常访问的数据，放在一起的可能性更高
- 时间局部性：上次查询过的数据，下次再查，性能可能获得提升
- 磁盘预读：磁盘中有一个页的逻辑单位（一般是4K或4K的整数倍），假设我取一个字符a，并不是只取字符a，而是取一页。（给电脑装SSD固态硬盘时，经常需要做4K对齐，就是这个页的最小单位）
2. 什么是顺序读写，什么是随机读写？
- 顺序读写：省去了磁盘寻址时间，按顺序进行读写，例如一个视频
- 随机读写：需要磁盘寻址，无法按顺序进行读写，文件位置各不相同。例如一个文件夹中很多碎片文件
- 假设1GB的视频文件传输，和500M的文件夹传输，很可能1GB视频文件传输更快，取决于500M文件夹中文件是否够混乱
2. mysql底层数据结构
1. Mysql索引采用什么数据结构，为什么采用此数据结构，其它数据结构不行么？
- Mysql索引底层使用B+树实现，B+树是B树的一种（升级版），所以很多人会说存储到B树中。就像，平衡二叉树是二叉树的一种，我们一般会说，用的是二叉树
- 根据官方文档的解释，大部分索引（PRIMARY KEY、UNIQUE、INDEX、FULLTEXT）存储到B-tree（B树中）。例外，空间数据类型上的索引使用R树，内存表也支持哈希索引，InnoDB使用反向列表作为FULLTEXT（全文检索）索引
- 使用B+树实现，是因为它对其它快速索引数据结构，更适合数据库场景
- 散列表（Hash表）的优点和缺点：虽然
等值查询很快，但是数据库文件众多，且大部分数据不是等值查询，使用散列表会浪费大量内存空间，并且速度得不到体现.而且说白了算哈希，最后存储到的是链表，一旦链表长度过长，需要转换为红黑树，而红黑树的问题就是深度会过深，影响效率
- Binary Search Tree二叉搜索树的优点缺点：有序，可以二分的快速检索目标，但是二叉搜索树，右子树一定比左子树大，这就有可能出现以下图片中的问题，
深度太深，反而没有了树的优点，查找变成了和链表一样的效率。硬件层面，如果我要找7，需要磁盘预读6次数据页，也就是频繁IO6次。
- AVL 平衡二叉树，它有一个旋转的效果，当最短分支和最长分支高度差超过1时，将
发生旋转，让它平衡。这是优点，也是缺点，优点在于，查询效率的提升。缺点在于，大体量的数据，在插入，删除过程中，旋转操作会频繁发生，浪费资源，影响效率。而需要建立索引的数据库，动辄几十万条数据，非常浪费资源。
- 红黑树，AVL平衡二叉树的变种，对旋转要求降低，最长子树层数不超过最短子树2倍即可，当然还有其它限制，新插入结点必须是红色，任何结点，不能连续有两个红色等等。这样做，它确实
在插入和查询方面做了平衡，但是二叉树这种形式本身就有一个问题。数据达到一定量级，树的深度会太深。影响查询效率。索引需要持久化到硬盘，红黑树深度太深，IO次数增多，也就是内存和磁盘数据交换就会多，IO是性能瓶颈，我们必须保证IO尽可能少，而且取数据次数要少（磁盘预读尽可能少发生）
- B树，性能方面，搜索可能在非叶子结点结束，性能逼近二分查找。每个结点最多m个子树，根结点至少2个子树，分支结点至少拥有m/2课子树（除根和叶子结点都是分支结点）。所有叶子结点都在同一层，每个节点最多有m-1个key，升序排列
- 缺点：每个结点占用一个磁盘块，
一个结点除了key，记录子树的指针，还有data数据，每页存储空间有限，如果data较大，每个节点存储key数量将变少- 存储数据量很大时，会导致深度较大，增大磁盘IO，影响查询性能
- 假设查找关键字28，先根据根结点找磁盘块1，读入内存，然后比较28，发现在16-34之间，根据p2指针找到磁盘块3读入内存，继续比较，根据p2找磁盘块8读入内存。共IO3次。
- B+树，只在叶子结点存储key+数据data的B树，非叶子结点只存储key，而不是在每一个结点都存储data。这样做，每个结点可以包含更多结点的指针key，降低树的高度，范围也变成多个区间，区间越多，数据检索越快。并且B+树叶子结点，两两指针相互连接（为了符合磁盘预读特性），顺序查询性能更高
- 为什么层级变少了，假设每个数据行（data）1KB，key为100bit，而一个结点，按一个磁盘块算4KB
- 那么3层的B树，一个结点需要存储data，一共可以存4个，4* 4 * 4 = 64条数据
- 而3层的B+树，一个结点不需要data，只存key，一个key100bit，一共存400个，400 * 400 * 400 = 640000条索引，最后，通过索引再去检索数据即可（叶子结点相互连接，顺序查找，很快）
- 上面只是个例子，粗糙的表达一下意思。
3. mysql索引基础
1. 为什么索引可以加快数据的查询
- 索引可以让我们查询时，和查字典一样，索引到对应页数，而索引就是建立一个索引目录
- 这些数据文件（索引）不会单纯放在内存，而是会持久化到硬盘，根据不同存储引擎（主流的是innodb和Myisam），对应存储文件也不同，我们可以通过索引（标注数据的位置），快速找到我们需要的数据。下面是两种存储引擎的存储文件形式
- 如果使用innodb存储引擎，emp表的存储形式是，emp.frm和emp.ibd两个文件。.frm表述表结构和相关元数据，.ibd文件是innodb存储文件，数据和索引都放在一起了(这种叫聚簇索引)
- 而Myisam存储引擎，MYD放数据文件，MYI放索引文件。.frm和innodb相同，表述表结构和相关元数据。不同于innodb存储引擎，innodb数据和索引文件放一起到.ibd。Myisam数据文件和索引分开存放。
2. InnoDB如何创建索引，什么情况下会回表
- 使用B+树，索引和数据文件放在一起。聚簇索引的形式（聚簇索引在msql索引细节中介绍）
- 如果是对主键创建索引，那么会通过B+Tree结构对主键创建索引，叶子结点存储记录，没有主键，会选择唯一键。没有唯一键，生成一个6位row_id作为主键
- 回表：如果创建索引的键是其它字段（比如就指明它生成一个username字段的索引），那么叶子结点中存储该记录主键，然后通过主键索引找对应记录。
3. MyISAM存储引擎，如何创建索引？
- 索引和数据文件分开，使用B+树
- 创建索引时，叶子结点不放数据，而是放数据地址。索引到后，需要通过地址去获取数据。
4. 如果数据库某表肯定需要建立索引时，主键该不该设置自增？
- 能自增就一定要自增，减少维护索引的成本，因为主键连续，可以方便页分裂和页合并
- 页合并：
- 当你删了一行记录时，实际上记录并没有被物理删除，记录被标记（flaged）为删除并且它的空间变得允许被其他记录声明使用
- 当页中删除的记录达到MERGE_THRESHOLD（默认页体积的50%），InnoDB会开始寻找最靠近的页（前或后）看看是否可以将两个页合并以优化空间使用
- 页分裂：
- 页可能填充至100%，在页填满了之后，下一页会继续接管新的记录
- 没有足够空间去容纳新（或更新）的记录。根据“下一页”的逻辑，记录应该由下一页负责,下一页也满了，数据也不可能不按顺序地插入，怎么办？
- B+树的叶子结点是双向链表，每个结点相互连接，所以要保证顺序查找，就得在合适的位置进行页分裂，然后重新规划页的关系
- 所以自增的主键，很大程度减少页合并和页分裂的成本。而随机生成的，可能需要频繁进行页分裂
5. 什么是自然主键，什么是代理主键？应该使用那种主键？
- 自然主键，就是和业务相关的字段作为组件，比如身份证号，完全可以作为主键来用，而不用单独建立一个主键，例如id字段。
- 代理主键，就是和业务无关的组件。
- 推荐代理主键，和业务解耦合，未来业务发送改变，比如不允许使用身份证号，那么代理主键不会被影响。
6. Mysql索引分几类？分别是什么？
- 5类。
- 主键索引、唯一索引、普通索引、组合索引、全文索引
- 组合索引：假设id、name、age。此表大部分查询基于name+age来查询。而对name+age建立的索引就是组合索引，就是一个索引里面包含多个其它索引。类似联合主键，用户表主键uid，对应角色表多个角色主键rid。采用最左匹配原则
4. mysql索引细节
1. 回表
- innodb为非主键建立索引时，B+树叶子结点会存储主键
- 然后通过主键查询主键索引，最后获取索引对应整行数据， 这个过程就是回表
2. 索引覆盖
- 假设我们为非主键建立索引，假设主键是id，我们知道B+树存储是主键id，最后会通过id回表，获取整行数据。
- 如果我查询结果就只需要主键，那么还需要回表么？回表是通过主键再去查主键索引，最后获取整行数据。而我们现在只需要主键id。
- 那么这种只查主键的情况下，是不需要回表的，也就是索引覆盖。
3. 最左匹配
- 当我们使用组合索引时，假设是name+age的组合索引
- 那么我们先匹配name再匹配age，也就是sql需要这样写 select * from table where name>? and age>?或者select *from table where age >? and name>?,都会使用索引
- 如果直接匹配age，那么不会使用这个组合索引，比如select * from table where age>?
4. 索引下推
- 假设 select name，age where name>?and age>?;
- 执行时，是先将name所有值查询出来，再跟age进行过滤快
- 还是先将name和age条件进行关联检索，然后查询需要的列快
- 是一次性查出来，再过滤快，因为和IO交互少了
- 而这种行为就是索引下推，减少整体一个IO量，把检索操作往下移，先查出所有
5. 使用索引应该注意哪些问题？
- 避免where子句中对字段进行null值判断，会导致引擎放弃索引进行全表扫描
- 避免where子句使用!=或<>操作符，会导致引擎放弃使用索引而进行全表扫描
- 避免where子句使用or来连接条件，会导致引擎放弃使用索引而进行全表扫描
- in和not in要慎用，否则会导致全表扫描
- 避免where 子句中对字段使用like模糊查询，会导致全表扫描
- 避免where子句中对字段进行函数操作，会导致引擎放弃索引进行全表扫描
- 避免where子句中对字段进行表达式操作，会导致引擎放弃索引进行全表扫描
- 索引按需建立，虽然可以提高select效率，但会降低insert和update的效率（有可能重建索引），一个表最好不要超过6个。
- 记住七字口诀：模型，数空运，最快（意思是，模型，就数空运的最快）
- 模（模糊）：模糊查询LIKE以%开头
- 型（类型）：数据类型错误
- 数（函数）：对索引字段使用内部函数
- 空（Null）：索引列是NULL，索引列不存储空值，如果索引列不设置not null，数据库会认为索引列存在null值，因此不会使用索引。
最新版的数据库引擎，null这种情况，不会失效了- 运（运算）：对索引列进行加减乘除等运算
- 最（最左）：复合索引不按索引列最左开始查找
- 快（更快）：全表查找预计比索引更快
6. 什么是聚簇索引？优缺点是什么？
- 聚簇索引就是按照每张表的主键构造一颗B+树,同时叶子节点中存放的就是整张表的行记录数据，也将聚集索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分
- InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构
- 没有主键，会选择唯一键。没有唯一键，生成一个6位row_id作为主键
- 反正就是索引和数据文件放一起，叶子结点放数据
- 聚簇索引的优点：
- 数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中
- 聚簇索引对于主键的排序查找和范围查找速度非常快
- 缺点：
- 插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键
- 更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新
- 二级索引（非聚簇索引）访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。也就是回表
7. 什么是非聚簇索引？
- 又名辅助索引、InnoDB下也可以叫二级索引，索引和数据文件不放在一起。在聚簇索引之上创建的索引称之为辅助索引
- 辅助索引访问数据总是需要二次查找，辅助索引叶子节点存储的不再是行的物理位置，而是主键值。通过辅助索引首先找到的是主键值，再通过主键值找到数据行的数据页，再通过数据页中的Page Directory找到数据行
- Innodb辅助索引（假设给非主键建立索引，就会建立为二级索引）的叶子节点并不包含行记录的全部数据，叶子节点除了包含键值外，还包含了相应行数据的聚簇索引键
- 辅助索引的存在不影响数据在聚簇索引中的组织，所以一张表可以有多个辅助索引。在innodb中有时也称辅助索引为二级索引
5. 事务/隔离级别/锁
1. 什么是事务？特性有哪些？
- 事务：数据库执行操作的最小执行单元（不可再分，一个事务中的操作，要么全都成功，要么全都失败）。
- 特性ACID
- 原子性：最小单元不可再分，一个事务中的操作，要么全成功，要么全失败。
- 一致性：
从一个正确的状态到另一个正确的状态。发生事务之前，一个正确的结果，发生事务之后，也必须是另一个正确的结果。比如A有1000给B转账（B有0），转账前，A+B=1000，事务完成之后，A和B的总额依然是1000.当然这个例子只是一个描述，并不能代表一致性。转账案例是用的最多的描述一致性的案例。- 隔离性：多事务并发执行时，事务之间相互独立，互不影响。
- 持久性：事务提交后，对数据库中数据的改变是永久的。
2. 简述一下数据库当中的事务在Java的使用？（暗藏杀机的题，验证你代码写过多少，碰过多少坑）
- Mysql默认情况下，增删改操作会自动开启事务，执行成功，自动提交事务。
- Mysql数据库默认开启事务，我们可以手动控制事务
- 一般在Java开发中，我们都会通过JDBC手动控制事务。但是不会修改事务隔离级别（用Mysql默认）
- 必须保证整个方法的事务处于同一个JDBC连接才能管理，而不在同一个连接，比如分布式事务情况下，就需要全局管理（可以用reids）
- 一般可以使用动态代理技术管理事务，也可以使用Spring的AOP。也可以选择Spring的事务管理器（用Spring AOP专门封装了管理事务的切面），通过@Transaction注解开启Spring事务管理
3. 查询时如何开启事务？
- 用for update指定当前操作为update操作（增删改都认为是update操作）。select … from … for update;
- for update仅使用与InnoDB，并且必须开启事务，在begin和commit之间才生效
4. 如何手动开启事务？
- 查询事务是否开启
# 查询自动提交是否开启，ON表示开启，增删改操作都会自动提交
show variables like 'autocommit';
- 设置事务手动，也就是让它不自动提交。on表示开启，off表示关闭
set autocommit = off;
- 之后执行语句，都需要手动开启和提交事务
begin; # 开启事务
update ... set ...; # update操作
commit; # 手动提交事务
5. 简述一下你对Mysql锁机制的了解（InnoDB引擎下，面试官没提什么引擎，你自己提在InnoDB下）。
- 当一个事务对某条数据进行update（增删改）操作时，会对这条数据添加排它锁（独占锁），也就是不允许其它事务对这条数据执行相同的操作（增删改），select默认不开启事务，所以可以查，但是如果开启事务，查也不行。
- 上面因为是给一条数据加锁，也就是行锁。
- 当一个事务进行for update的时候，另一个事务也有for update时会一直等待，直到之前的事务commit或rollback或断开连接释放锁才能拿到锁进行后面的操作（排它锁不能共存）
- InnoDB引擎默认对update，delete，insert加排它锁，select语句默认不加锁
- 加过排他锁的数据行在其他事务中是不能修改数据的，也不能通过for update和lock in share mode锁的方式查询数据，但可以直接通过select … from …查询数据，因为普通查询没有任何锁机制
- 不允许锁共存，也就是不能加别的锁。
- 也可以给整个表加锁(共享锁、读锁)，Mysql默认给行添加排它锁，表添加共享锁。
- 对于使用共享锁的事务，其他事务只能读，不可写
- 如果执行了更新操作则会一直等待，直到当前事务commit或者rollback
- 如果当前事务也执行了其他事务处于等待的那条sql语句，当前事务将会执行成功，而其他事务会报死锁
- 允许其他锁共存
6. 简述Mysql事务隔离级别？默认的是什么？解决什么问题？
- 事务并发中，需要解决的问题如下
- 脏读：一个线程中事务读到另一个线程中未提交数据
- 不可重复读：一个线程中事务读到另一个线程已提交的update数据，（前后内容不一样），一个事务读两次，读到的内容可以因为别的事务修改而不同。
- 幻读（虚读）：一个线程中事务读到另一个线程中已提交insert数据（前后数据条数不一样）
- 四种事务隔离级别和解决的问题。
- Serializable（串行化）：最高级别，速度最慢，避免脏读、不可重复读、幻读。
- Repeatable read（可重复读）：第二级，速度还行，避免脏读、不可重复读。幻读依然可能发生（对update进行加锁，其它insert，delete什么的不管）
- Read committed（可读已提交）：第三级，速度挺好，避免脏读，不可重复读和幻读一定会发生。
- Read uncommitted（可读未提交）：最低级别，速度最快，以上情况均无法保证。
- Mysql默认隔离级别为Repeatable read（可重复读，InnoDB引擎下），Myisam引擎没有隔离级别这个概念。另外SqlServer和Oracle默认都是Read committed（可读已提交）
| 事务隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| READ UNCOMMITTED | √ | √ | √ |
| READ COMMITTED | × | √ | √ |
| REPEATABLE READ（MySQL默认） | × | × | √ |
| SERIALIZABLE | × | × | × |
6. Sql注入和Sql优化、乐观锁和悲观锁
1. 什么是sql注入，怎么解决？
- sql注入就是用户通过非法输入sql语句（例如到地址栏、表单提交是用户名密码栏等），后台拿到这些参数，直接拼接到了sql中。比如输入or 1=1，此时后台拼接sql语句时，无论查询条件是什么，只要拼接上or 1 = 1，就有可能查出所有数据（假设机密数据）
- 解决方法就是使用预编译sql，采用占位符的形式，规定好sql格式。例如where a = ? and b = ?。此时就算输入张三 or 1 = 1，它的sql也只能是 where a = ‘张三 or 1 = 1’ and b = XXX。完全不会发生sql注入的问题。
# 会发生sql注入，假设用户输入 张三 or 1=1
select ... from ... where a = name;
# 此时可能整个表都会被查出来
select ... from ... where a = '张三' or 1 = 1;
# 不会发生sql注入
select ... from ... where a = ?;
.setString(1,name); # 模拟JDBC传动态参数
# 此时的sql是,把name='张三 or 1=1 '当成一个值，去数据库查询
select ... from ... where a = ?; String "张三 or 1=1"
2. 如何优化sql执行效率？
- 尽量不要使用*
- 使用代理主键，尽量设置主键自增。（为什么参考上面的sql索引基础里面的页分裂和页合并的知识）
- 尽量使用联查来替代嵌套查询（子查询）
- 当在某些进场查询的字段上添加索引后，需要额外注意一些情况（具体参考上面的mysql索引细节的第5道题）。
3. Mysql什么情况下会出现死锁举例说明？Mysql如何解决死锁问题。
- 唬你的！别管什么Mysql还是Java。死锁永远都是两个线程互相想要对方资源，而哥俩都不想先放手。比如事务A想要事务C的资源，事务C也想要事务A的资源。两个事务都在等对方提交或回滚。
- 假设A事务操作B表，C事务操作D表。A修改一条B表数据1加了锁，C修改一条D表数据2加了锁，接下来A要修改D表的数据2，C要修改A表的数据1，此时数据1是A加锁，数据2是C加锁。两个人都得阻塞住，等待对方释放资源，但是两个人都没法释放。就产生死锁了。
- 如何解决呢？一旦出现死锁，Mysql自动就给你报错了，说你现在要获取的锁，是死锁，请尝试重新开启一个事务。
4. 简述一下悲观锁和乐观锁。
- 只是思想，并不是实际的锁，就像IOC容器一样，IOC不是一个实际存储对象的容器，真正存数据的是Java中内些集合，例如HashMap
- 悲观锁也一样，底层需要我们用排它锁实现，乐观锁不需要锁，需要CAS机制来实现
- 悲观锁：事务A总是悲观的认为，它访问期间，总会有其它事务并发访问，为了保证正确性，事务A访问数据（增删改查），立即给数据加排它锁。其它事务无法访问该数据，从而保证数据安全性，正确性。安全性高，但是效率较低。查询需要我们自己用for update加锁
- 乐观锁：乐观的认为不会有其它事务操作，但为了保证并发安全，需要使用CAS机制，通过添加表字段，假设version。事务提交之前，获取version的值，然后与表中的version值进行比较，如果一致，说明没有其它事务访问过，那么将version的值+1后，进行提交。不一致进行回滚。
7. 数据库优化
如何进行数据库优化？
- 上面提到过索引的一些优化，自己翻阅即可。
- 使用慢查询日志（做系统优化，非常给力的工具，大多数性能问题，都可以通过这个日志发现，从而对其性能进行优化）
- 就是将查询慢的sql语句，记录到日志中
- 可以通过系统配置，配置具体多慢的sql语句需要被记录。默认是超过10秒的才会记录。
InnoDB和Myisam的区别？
- InnoDB支持行锁和表锁，Myisam只支持表锁
- InnoDB默认隔离级别是可重复读，Myisam没有隔离级别这个概念
- InnoDB有事务，Myisam不支持事务，所以没有隔离级别的概念
- InnoDB有聚簇索引，Myisam没有聚簇索引
8. sql题
记住，多个join 最后on 用and拼接连接条件，效率高，而一个join一个on，效率低
# 效率高
select * from a
join b
join c
join d
on a.id=b.id and b.id = c.id and c.id = d.id
# 效率低
select * from a
join b on a.id = b.id
join c on b.id = c.id
join d on d.id = c.id
5.1 题目一
| 表结构如下：一个班级有多名学生，一个学生只能属于一个班级，学生可能没有成绩 |
|---|
1. 查询所有学生信息（学号、姓名、性别、班级名称）
select
stu_no as 学号,
stu_name as 姓名,
stu_gender as 性别,
class_name as 班级名称
from
student as stu
inner join
class as cl
on
stu.class_id = cl.id
2. 查询所有人（包括无成绩学生）的课程分数（学号、姓名、性别、班级名、语文分数、数学分数）
select
stu_no as 学号,
stu_name as 姓名,
stu_gender as 性别,
class_name as 班级名称,
chinese as 语文分数,
math as 数学分数
from
student as stu
inner join
class as cl
on
stu.class_id = cl.id
left join
score as sc
on
sc.stu_id = stu.id
3. 查询语文分数比’张三’高的学生（学号，姓名，语文分数，数学分数）
select
stu_no as 学号,
stu_name as 姓名,
chinese as 语文分数,
math as 数学分数
from
student as stu
inner join
score as sc
on
stu.id = sc.stu_id
where
sc.chinese > (
select
chinese
from
score
where # 这里可以直接两表联查，思路有很多种，但是这里既然考查子查询，就全用子查询吧
stu_id = (
select
id
from
student
where
stu_name = '张三'
)
)
4. 查询各科都合格（分数>=60）的学生（学号、姓名、语文分数、数学分数）
select
stu_no as 学号,
stu_name as 姓名,
chinese as 语文分数,
math as 数学分数
from
student as stu
inner join
score as sc
on
stu.id = sc.stu_id
where
sc.chinese >= 60 and sc.math >= 60
5. 查询班级人数>=30的班级（班级编号、班级名称、人数）
select
cl.id as 班级编号,
class_name as 班级名称,
count(stu_no) as 人数
from
student as stu
inner join
class as cl
on
cl.id = stu.class_id
group by
cl.id
having
count(stu_no)>=30
5.2 题目二
| 表结构如下：典型的不给图，让你凭空想象表数据的题 |
|---|
student(sno,sname,sage,ssex)学生表
course(cno,cname,tno)课程表
sc(sno,cno,score)成绩表
teacher(tno,tname)教师表
1. 查询cno=1的课程成绩比cno=2的课程成绩高的所有学生信息
select
sno,sname,sage,ssex
from
student
where
sno in
(
select sc1.sno
from sc as sc1
join sc as sc2
on sc1.sno = sc2.sno
where
sc1.cno = 1 and
sc2.cno = 2 and
sc1.score>sc2.score
)
# 第二种写法，如果要输出成绩的话，必须用下面这种
select
student.sno,
sname,
sage,
ssex,
s1.score as 课程一成绩,
s2.score as 课程二成绩
from
student
join
sc as sc1
join
sc as sc2
on
sc1.sno = sc2.sno and
sc1.sno = student.sno
where
sc1.cno = 1 and
sc2.cno = 2 and
sc1.score>sc2.score
2. 查询课程名为’课程1’成绩比课程名为’课程2’成绩高的所有学生信息
select
sno,sname,sage,ssex
from
(
select
stu.sno,score
from
student as stu
join sc on stu.sno = sc.sno
join course on course.cno = sc.cno
where course.cname = '课程一'
) as 课程1
inner join
(
select
stu.sno,score
from
student as stu
join sc on stu.sno = sc.sno
join course on course.cno = sc.cno
where course.cname = '课程二'
) as 课程2
on
课程1.sno = 课程2.sno
where
课程1.score > 课程2.score
2. 查询平均成绩大于60的同学，学号和平均成绩
select
sno,
avg(score) as avg
from
sc
group by
sno
having
avg > 60
3. 查询学过’李四’老师所教课程的所有同学，学号姓名
select
distinct
student.sno,
sname
from
student
join sc
join teacher
join course
on
course.tno = teacher.tno and
sc.cno = course.cno and
student.sno = course.sno
where
teacher.tname = '李四'
4. 查询姓’李’的老师个数
select
count(tno)
from
teacher
where
tname like '李%'
5. 查询每门课程的选修人数(课程名称，学生数量)–存在没有人选的课
select
count(sno) as cou,
cname
from
course
left join
sc
on
sc.cno = course.cno
group by
course.cno
6. 删除’1002’同学的’1’课程的成绩
delete from
sc
where
sno = '1002' and
cno = '1'
7. 查询选修人数最多的课程（课程id、课程名称、学生数量）
# 方法一
select
course.cno,
cname,
count(sno) as cou
from
course
join
sc
on
sc.cno = course.cno
group by
sc.cno
having
cou =
(
select count(sno) as cou
from sc
group by cno
order by cou desc
limit 1
)
# 方法二：待验证
select
course.cno,
cname,
count(sno) as cou
from
course
join
sc
on
sc.cno = course.cno
group by
course.cno
having
cou = max(cou)
扫一扫
5472
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
