【数据库】SQL语法&实现原理
嵌套查询&子查询
https://www.w3cschool.cn/sql/zjdc1oz3.html
https://www.cnblogs.com/myworld7/p/10255181.html
带in的子查询
带比较运算符的子查询
带any（some）或all的子查询
带exists的子查询
基于派生表的查询
#求学分获得8分以上学生的学号 平均分 以及总学分 #需要注意此处的作用域不同，只有该课程的成绩大于60才会获得该课程的学分，平均分包括了所有课程（不及格和及格） #思路：先将查询到的总学分结果看做是一张表 再与sc表连接进行查询平均分 select sc.sno,total_cre,avg(score) from (select sno,SUM(ceredit) as total_cre from sc,course where sc.cno=course.cno and score >= 60 group by sno having SUM(ceredit) >= 8) as temptable, sc where temptable.sno=sc.sno group by sc.sno,sum_cre;
Join算法原理
在Join语句中，执行引擎优先扫描的表被称为驱动表，另一张表被称为被驱动表。执行引擎在选择驱动表时，除了必须要遵守的特定语义外，最重要的考虑便是执行效率。
首先列举两种特定语义约束驱动表选取的场景：
场景一：Straight join指定连接顺序，强制要求执行引擎优先扫描左侧的表。
场景二：Left/Right [outer] join，方向连接的特点是反方向表中如果不存在关联的数据则填充NULL值，这一特性要求方向查询时优先扫描相同方向的表。倘若where条件中明确指明反方向表中的部分列非空，则驱动表的选择就不受此语义的限制，执行引擎会依据效率选取驱动表。
当没有特定语义的约束时，执行引擎便会依据执行效率选取驱动表，如何判断哪张表作为驱动表的效率更高呢？下文会结合Join的两种算法更深入地探讨这个问题。
2.1.2 Block Nested-Loop Join
假设一个数据量为m行的驱动表与一个数据量为n行的被驱动表进行join查询。
最简单的一种算法：
从驱动表扫描一行数据；
对被驱动表进行全表扫描，得到的结果依次与驱动表的数据进行join并把满足条件的数据加入结果集；
接着扫描驱动表，每扫描一行数据，均重复执行一次步骤2，直至驱动表的全部数据被扫描完毕。
这种算法的磁盘扫描开销为m*n，非常低效，MySQL在实际中并未直接使用该算法，而是采用缓存的思想（分配一块Join buffer）对该算法进行改进，并命名为Block Nested-Loop join(BNL)。
BNL的算法步骤为：
从驱动表一次扫描K条数据，并把数据缓存在Join buffer；
对被驱动表进行全表扫描，得到的结果依次与驱动表的K条数据进行join并把满足条件的数据加入结果集；
清空join_buffer；
接着从驱动表再取出K条数据，重复步骤2、3，直至扫描完驱动表的全部数据。
上述算法中，驱动表分段取数的次数记为l，整个算法的磁盘扫描开销为m+ln。由于分段的次数与驱动表的数据成正相关，所以公式可以记为m+λmn，λ的取值范围为(0,1)。
当两张表的行数（m、n大小）固定的情况下，m对结果的影响更大，m越小整体扫描的代价越小，所以执行引擎优先选择数据量更小的表作为驱动表(符合“小表驱动大表”的说法)。
2.1.3 Index Nested-Loop Join
BNL算法使用了Join buffer结构，虽然有可能通过减少重复扫描来降低磁盘扫描开销，然而驱动表分段扫描的次数过多依然可能会导致查询的低效。索引是MySQL查询提效的重要结构，当被驱动表的关联键存在索引时，MySQL会使用Index Nested-Loop Join（NLJ）算法。
该算法的步骤为：
从驱动表扫描一行数据；
使用驱动表的关联键搜索被驱动表的索引树，通过被驱动表的索引结构找到被驱动表的主键，再通过主键回表查询出被驱动表的关联数据（暂不考虑覆盖索引的情况）；
接着扫描驱动表，每扫描一行数据，均重复执行一次步骤2，直至驱动表的全部数据被扫描完毕。
每次搜索一棵树的复杂度近似为log2 n，上述过程在被驱动表扫描一行数据的时间复杂度是2log2 n，算法的整体复杂度为m+2mlog2 n，在该算法中，依旧是m对结果的影响更大，m越小整体扫描的代价越小，所以执行引擎总是选择数据量更小的表作为驱动表(符合“小表驱动大表”的说法)。
Order by 算法原理
2.2.1 全字段排序
MySQL会为每个线程分配一块内存（Sort buffer）用于排序，当Sort buffer的空间不足时（通过系统参数sort_buffer_size设置Sort buffer的大小），执行引擎不得不开辟磁盘临时文件用于排序，此时排序的性能也会大幅降低。
全字段排序是将查询需要的所有字段进行暂存，并按照排序字段进行排序，并将排序后的结果集直接返回。
2.2.2 Rowid 排序
若要查询的数据单行占用空间较大，Sort buffer中可以容纳的排序行数将会减少，此时使用磁盘临时文件进行排序的概率将会增大。为了提高排序性能，执行引擎提供一种只存储排序字段的算法，称为Rowid排序算法。
该算法的步骤为：
将参与排序的字段和主键进行临时存储；
按照排序字段进行排序，得到有序的主键；
根据有序的主键进行回表，按顺序将所有要查询的数据返回。
Rowid排序在单行查询数据较大时可以通过节省临时排序空间从而达到降低排序开销的目的，然而该算法的代价是会增加磁盘扫描的次数（主键回表），所以是否选择使用该算法需要根据实际情况进行取舍（通过系统参数max_length_for_sort_data设置）。
浙公网安备 33010602011771号