常用sql

https://www.jianshu.com/p/5a2dae144238

mysql 行转列

1
2
3
4
5
6
7
8

select USER_NAME,
       max(case COURSE when '语文' then SCORE else 0 end) "语文",--通过case留下某行并转为列，因为语句使用了group by字句。所以内容选择必须嵌套在组函数中
       max(case COURSE when '数学' then SCORE else 0 end) '数学',
       max(case COURSE when '英语' then SCORE else 0 end) '英语',
       sum(SCORE)                                       '总分',
       avg(SCORE)                                       '平均分'
from student
group by USER_NAME;---多行化为一行

mysql 列转行

1
2
3
4
5
6
7
8
9

select USER_NAME, '语文' course, CN_SCORE score ---每次取一列转为一行
from grade
union ---一行拆为多列
select USER_NAME, '数学' course, MATH_SCORE score
from grade
union
select USER_NAME, '英语' course, EN_SCORE score
from grade
order by USER_NAME, score

30条sql建议

https://zhuanlan.zhihu.com/p/260536848

1. 前后模糊查询优化
   1. mysql5.7以下，加入反向字段，并建立索引
   2. 5.7以上，虚拟列，
   3. https://www.jianshu.com/p/9f83eebc8606

1、*查询SQL尽量不要使用select ，而是select具体字段。

理由： 只取需要的字段，节省资源、减少网络开销。 select * 进行查询时，很可能就不会使用到索引覆盖了，就会造成回表查询，到主键索引上查询刚才得到的数据。

2、如果知道查询结果只有一条或者只要最大/最小一条记录，建议用limit 1 理由： 加上limit 1后,只要找到了对应的一条记录,就不会继续向下扫描了,效率将会大大提高。 当然，如果name是唯一索引的话，是不必要加上limit 1了，因为limit的存在主要就是为了防止全表扫描，从而提高性能,如果一个语句本身可以预知不用全表扫描，有没有limit ，性能的差别并不大。

3、应尽量避免在where子句中使用or来连接条件

//使用union all（不去重） //或者分开两条sql写： 理由： 使用or可能会使索引失效，从而全表扫描。 对于or+没有索引的age这种情况，假设它先走了userId的索引，但是走到age查询条件时，它还得全表扫描，也就是需要三步过程：索引扫描+全表扫描+合并 如果它一开始就走全表扫描（从叶子节点的链表扫描），直接一遍扫描就完事。mysql是有优化器的，出于效率与成本考虑，遇到or条件，索引可能失效。

4、优化limit分页

//方案一 ：返回上次查询的最大记录(偏移量) //方案二：order by + 索引 //方案三：在业务允许的情况下限制页数： 理由： 当偏移量最大的时候，查询效率就会越低，因为Mysql并非是跳过偏移量直接去取后面的数据，而是先把偏移量+要取的条数全部查出来，然后再把前面偏移量这一段的数据抛弃掉再返回的。 如果使用优化方案一，返回上次最大查询记录（偏移量），这样可以跳过偏移量，效率提升不少。 方案二使用order by+索引，也是可以提高查询效率的。 方案三的话，建议跟业务讨论，有没有必要查这么后的分页啦。因为绝大多数用户都不会往后翻太多页。

5、优化你的like语句 把%放前面，并不走索引，如下： 把% 放关键字后面，还是会走索引的。如下：

6、使用where条件限定要查询的数据，避免返回多余的行

7、尽量避免在索引列上使用mysql的内置函数 索引列上使用mysql的内置函数，索引失效

8、应尽量避免在 where 子句中对字段进行表达式操作，这将导致系统放弃使用索引而进行全表扫描

9、Inner join 、left join、right join，优先使用Inner join，如果是left join，左边表结果尽量小 理由： 如果inner join是等值连接，或许返回的行数比较少，所以性能相对会好一点。 同理，使用了左连接，左边表数据结果尽量小，条件尽量放到左边处理，意味着返回的行数可能比较少。

10、应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

11、使用联合索引时，注意索引列的顺序，一般遵循最左匹配原则。 当我们创建一个联合索引的时候，如(k1,k2,k3)，相当于创建了（k1）、(k1,k2)和(k1,k2,k3)三个索引，这就是最左匹配原则。 联合索引不满足最左原则，索引一般会失效，但是这个还跟Mysql优化器有关的。

12、应考虑在 where 及 order by 涉及的列上建立索引，尽量避免全表扫描。

13、如果插入数据过多，考虑批量插入。

14、在适当的时候，索引覆盖要查询的列。 理由： 当一条查询语句符合覆盖索引条件时，sql只需要通过索引就可以返回查询所需要的数据，这样避免了查到索引后再回表操作，减少I/O提高效率。 注：遇到以下情况，执行计划不会选择覆盖查询 1.select选择的字段中含有不在索引中的字段 ，即索引没有覆盖全部的列。 2.where条件中不能含有对索引进行like的操作。

15、慎用distinct关键字 带distinct的语句cpu时间和占用时间都高于不带distinct的语句。因为当查询很多字段时，如果使用distinct，数据库引擎就会对数据进行比较，过滤掉重复数据，然而这个比较，过滤的过程会占用系统资源，cpu时间。在查询一个字段或者很少字段的情况下使用时，给查询带来优化效果。但是在字段很多的时候使用，却会大大降低查询效率。

16、删除冗余和重复索引 重复的索引需要维护，并且优化器在优化查询的时候也需要逐个地进行考虑，这会影响性能的。

17、如果数据量较大，优化你的修改/删除语句。 一次性删除太多数据，可能会有lock wait timeout exceed的错误，所以建议分批操作。

18、where子句中考虑使用默认值代替null 并不是说使用了is null 或者 is not null 就会不走索引了，这个跟mysql版本以及查询成本都有关。 如果mysql优化器发现，走索引比不走索引成本还要高，肯定会放弃索引，这些条件！=，>is null，is not null经常被认为让索引失效，其实是因为一般情况下，查询的成本高，优化器自动放弃的。 如果把null值，换成默认值，很多时候让走索引成为可能，同时，表达意思会相对清晰一点。

19、不要有超过5个以上的表连接 连表越多，编译的时间和开销也就越大。 把连接表拆开成较小的几个执行，可读性更高。 如果一定需要连接很多表才能得到数据，那么意味着糟糕的设计了。

20、exist & in的合理利用 因为exists查询的理解就是，先执行主查询（循环主表），获得数据后，再放到子查询中做条件验证，根据验证结果（true或者false），来决定主查询的数据结果是否保留。 in 是先执行子查询，得到数据后，再执行主查询（先循环子查询的结果数据，在循环主查询表得到数据） mysql优化原则，就是小表驱动大表，小的数据集驱动大的数据集，从而让性能更优。

因此，我们要选择最外层循环小的，也就是，如果B的数据量小于A，适合使用in，如果B的数据量大于A，即适合选择exist。

21、尽量用 union all 替换 union 如果使用union，不管检索结果有没有重复，都会尝试进行合并，然后在输出最终结果前进行排序。如果已知检索结果没有重复记录，使用union all 代替union，这样会提高效率。

22、索引不宜太多，一般5个以内。 索引并不是越多越好，索引虽然提高了查询的效率，但是也降低了插入和更新的效率。 insert或update时有可能会重建索引，所以建索引需要慎重考虑，视具体情况来定。 一个表的索引数最好不要超过5个，若太多需要考虑一些索引是否没有存在的必要。

23、尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型 相对于数字型字段，字符型会降低查询和连接的性能，并会增加存储开销。

24、索引不适合建在有大量重复数据的字段上，如性别这类型数据库字段。 因为SQL优化器是根据表中数据量来进行查询优化的，如果索引列有大量重复数据，Mysql查询优化器推算发现不走索引的成本更低，很可能就放弃索引了。

25、尽量避免向客户端返回过多数据量。 //分页查询 //如果是前端分页，可以先查询前两百条记录，因为一般用户应该也不会往下翻太多页，

26、当在SQL语句中连接多个表时,请使用表的别名，并把别名前缀于每一列上，这样语义更加清晰。

27、尽可能使用varchar/nvarchar 代替 char/nchar。 因为首先变长字段存储空间小，可以节省存储空间。 其次对于查询来说，在一个相对较小的字段内搜索，效率更高。

28、为了提高group by 语句的效率，可以在执行到该语句前，把不需要的记录过滤掉。

29、如何字段类型是字符串，where时一定用引号括起来，否则索引失效 为什么第一条语句未加单引号就不走索引了呢？这是因为不加单引号时，是字符串跟数字的比较，它们类型不匹配，MySQL会做隐式的类型转换，把它们转换为浮点数再做比较。

30、使用explain 分析你SQL的计划 日常开发写SQL的时候，尽量养成一个习惯吧。用explain分析一下你写的SQL，尤其是走不走索引这一块。

常见面试题

MySQL 必知必会50题 训练笔记 - 邓星宇的文章 - 知乎 https://zhuanlan.zhihu.com/p/143278196

– 1.查询课程编号为‘01’的课程比‘02’的课程成绩高的所有学生的学号、姓名和各自‘01’‘02’课程成绩（多表查询）

思路：通过学生编号将成绩表的课程‘01’成绩和课程‘02’成绩分别取出，join构建一张新表包含：学生编号、课程‘01’成绩、课程‘02成绩’，再将姓名join进来。

– 2、查询平均成绩大于60分的学生的学号和平均成绩

思路：以学生学号进行分组，算平均成绩，筛选输出平均成绩大于60分的学生学号和平均成绩

– 3、查询所有学生的学号、姓名、选课数、总成绩 思路：学生表通过学生学号左关联成绩表，以学生学号进行分组，count函数计算选课数，sum函数计算总成绩，ifnull函数将由于左关联产生的成绩表中没有的同学数据null变成0关键函数：GROUP BY、COUNT()、SUM()、IFNULL( , )、LEFT JOIN

– 4、查询姓“张”的老师的个数

思路：用like筛选老师姓名，避免姓名重复通过计算老师id来计算个数

– 5、查询没学过“张三”老师课的学生的学号、姓名

思路1：从教师表查出“张三”老师的id，从课程表通过t_id查出老师的课程，从成绩表查出有这些课程的学生，从学生表排除这些学生。 思路2：教师表–课程表–成绩表 通过教师编号、课程编号内连接，筛选出有‘张三’的课程成绩的学生编号，在学生表中选出学生编号不在上面选中的学生编号的学生姓名编号和姓名

– 6、查询学过“张三”老师所教的所有课的同学的学号、姓名

理解1：学过张三老师的课的学生信息

思路:构建一张表包含：学生学号、学生姓名、课程成绩、课程老师。再通过老师姓名筛选

用分组去重（虽然本题没有重复结果） 理解2：学了张三老师所有课的学生信息

思路:构建一张表包含：学生学号、学生姓名、课程成绩、课程老师。通过老师姓名筛选。再按学号分组，用统计函数count（课程）选出学生所选课程数等于张三老师所教的课程数的学生信息

– 7、查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名

思路1：通过子查询从成绩表将课程‘01’和课程‘02’的信息分别取出，并内连接得到新表，表中包含选了两门课程的学生id，从学生表中获取学生编号在新表中的学生标号和姓名

思路2：在学生表中选出满足两个条件的学生学号和学生姓名，条件1：有课程编号‘01’成绩的学生编号，条件2：有课程编号‘02’成绩的学生编号

– 8、查询各门的总成绩、平均成绩和人数

思路：score表的分组统计，以课程分组

– 9、查询所有课程中成绩小于60分的学生的学号、姓名

思路1：得出成绩表中小于60分的课程数，统计各同学共学了几门课，选出二者相等的学生学号、姓名

– 9、查询所有课程成绩全都小于60分的学生的学号、姓名

思路，得出各同学课程成绩小于60分的课程数，统计各同学共学了几门课，选出二者相等的学生学号、姓名

– 10、查询没有学全所有课的学生的学号、姓名

思路：将学生与成绩表通过学生学号左连接，计算每个学生的成绩数，选出小于课程总数的学生学号和姓名

– 11、查询至少有一门课程与学号为“01”的学生所学课程相同的其他同学的学号

思路：从成绩表表选出‘01’学生学过的课程，从成绩表找出这些上述课程的所有记录，并以学号distinct去重，去掉‘01’同学

– 12、查询和“01”号同学所学课程完全相同的其他同学的学号

思路1：将学号不为‘01’的学生课程编号连接形成新的字段，选出与学号为‘01’的学生课程编号连接形成字段相同的学生学号 关键函数：group_concat、group by 思路2：将学号不为‘01’的学生成绩表以课程号左连接学号为‘01’的学生成绩表，计算每一个学号不为‘01’的学生课程数，选出课程数和‘01’学生相同的学生学号

– 15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩 思路：对成绩表分组统计，符合条件的学号；学生表和成绩表连接后，选择用上述学号过滤，对过滤结果进行分组统计。

– 16、检索"01"课程分数小于60，按分数降序排列的学生信息 思路：学生表和成绩表通过学生学号链接，用课程编号为‘01’和课程成绩小于60两个条件筛选，最后通过分数降序

– 17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩 思路：按照学生学号进行分组排序，用avg计算平均成绩，利用max来显示每一门课的成绩（max函数没有实际意义，只是用来显示），用case when（课程编号为‘01’，输出成绩，否则输出null）得到每一门的课程成绩 注意：这种写法如果一个学生没有选任何一门课，那么他不会出现在结果中。

– 18、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率– 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90 思路：通过课程编号链接课程表和成绩表，通过课程编号分组，取出课程编号、课程名、最高分、最低分和平均分。将是否满足条件的分数通过case when函数变成01变量，求和便是符合条件的学生数，除以参与这一门考试的学生总数，便是各个分数段的概率（妙啊）

– 20、查询学生的总成绩并进行排名 注意：如果学生没有选课，则不会出现在排名中

– 21 、查询不同老师所教不同课程平均分从高到低显示

思路：成绩表通过课程编号和课程表进行连接为了获得课程名，再通过老师编号和教师表进行连接为了获得老师名，通过课程编号或者课程名字进行分组，输出课程编号、课程名、教师名、平均分，最后按照平均分排序

– 22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩

思路：学生表和成绩表通过课程编号相连接，通过row_number函数增加一列课程成绩在该课程的排名，最后通过子查询筛选出排名，即增加的排名列数字为2，3的数据 分组排序功能：row_number() over(partition by 分组列 order by 排序列 desc)

– 23、使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计各分数段人数：课程ID和课程名称

思路：通过课程编号将成绩表和课程表连接，再通过课程编号分组，得出课程成绩和课程编号，通过case when函数将每个分数段的人数转换为0/1或者1/null 形式，通过sum（0/1）或者count（1/null）得出每个分数段的人数

– 24、查询学生平均成绩及其名次 思路，如果rank函数不让用，可以用子查询算出平均数，在主查询用rownumber函数加上排名 关键函数：rank

– 25、查询各科成绩前三名的记录（不考虑成绩并列情况）

思路：学生表和成绩表通过课程编号相连接，通过row_number函数增加一列课程成绩在该课程的排名，最后通过子查询筛选出排名，即增加的排名列数字为1,2,3的数据

– 26、查询每门课程被选修的学生数

– 27、查询出只有两门课程的全部学生的学号和姓名

思路1：通过学生学号将成绩表和学生表连接，通过学生学号分组，计算学生选课成绩的数量，用having筛选出数量为2的信息，输出学生学号和姓名

思路2：成绩表通过学生学号分组，选出选课成绩为2的学生学号；通过子查询，在学生表中去的学生学号和姓名

– 28、查询男生、女生人数

思路1：通过性别进行分组，计算每个性别人数

– 29 查询名字中含有"风"字的学生信息

思路：用like进行字符串匹配

– 31、查询1990年出生的学生名单 思路1：用like进行字符串匹配的方法得到出生日期为1990年

– 32、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩

– 33、查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列 思路：成绩表按课程分组，使用avg函数统计平均成绩，并按平均成绩和课程号排列

– 34、查询课程名称为"数学”，且分数低于60的学生姓名和分数 思路：将学生表（需要学生姓名）、成绩表（需要数学成绩）、课程表（需要课程名）连接起来筛选即可

– 35、查询所有学生的课程及分数情况

思路2：成绩表通过学生编号和学生表连接再通过课程编号和课程表连接，通过学生编号进行分组，通过case when输出各科成绩，判断课程名称并输出，由于分组只输出第一条判断情况，所以用sum或者max

– 36、查询课程成绩在70分以上的学生姓名、课程名称和分数 思路：将学生表成绩表课程表相连接

– 37、查询不及格的课程的学生和成绩信息并按课程号从大到小排列

– 38、查询课程编号为03且课程成绩在80分以上的学生的学号和姓名

– 39、求每门课程的学生人数 思路：成绩表按学好分组，然后使用聚合函数count

– 40、查询选修“张三”老师所授每门课程的学生中成绩最高的学生姓名及其成绩 思路：连接成绩表，学生表，教师表，课程表，使用“张三”筛选，并用课程号分组，使用row_number得出每门课中的排名，上述作为子查询，然后在主查询中筛选出排名为1的记录

– 41.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

思路：1. 最内层子查询查出至少选2门课的学生id 2. 这些id与成绩表连接后至少有2条记录，按（学号、成绩）分组后，组数=不同成绩数=记录数 3. 对这个临时表按学号分组统计数目，只有一条的就是只有一个成绩的（各课程成绩相同的）。

– 43、统计每门课程的学生选修人数（超过5人的课程才统计）。 – 44、检索至少选修两门课程的学生学号和选课数 思路：score表按学号分组，统计选课数 – 45、 查询选修了全部课程的学生信息 思路：由于要获得学生信息就通过学生学号将学生表和成绩表连接，将新表通过学生学号进行分组，筛选条件为学生课程数等于课程表中的课程数

46、查询各学生的年龄（精确到月份） 思路：日期函数的使用

1
2

select s_id, s_birth, period_diff(extract(year_month from curdate()), extract(year_month from s_birth)) / 12
from student

– 50、查询下个月过生日的学生 思路：用now()获得现在的时间month取出现在的月份，生日的月份等于通过round取余数加一则是下周过生日