sql 学习笔记（一）

1.查询全部数据：

#1

 SELECT * FROM 表名;

#2 推荐

SELECT [列名1]，[列名2],[列名3],... FROM 表名;

注意：除平时练习使用，其他情况都不推荐使用 SELECT * FROM XXX

zzselect *与select 所有字段的查询效率比较_figerZpeng的专栏-CSDN博客_select所有字段

2.数据去重：

SELECT DISTINCT 列名 FROM 表名;

例：从用户信息表中取出学校的去重数据

SELECT DISTINCT univerSity FROM user_profile;

3.将查询后的列重新命名/列别名：

SELECT 列名 AS 新列名 FROM 表名;

注：可省略AS，直接写别名。

4.使用Limit 子句限制查询结果返回行数：

SELECT * FROM 表名 limit i,n
# i：为查询结果的索引值(默认从0开始)，当i=0时可省略i
# n：为查询结果返回的数量
# i与n之间使用英文逗号","隔开
limit n 等同于 limit 0,n

举例见：

刷题日记-SQL 查找最晚入职员工的所有信息_努力学习编程中-CSDN博客

5：不等于  != 或 <>

例：年龄不为5

可写为age != 5  或  age <> 5

6.为空 IS NULL  /  不为空 IS NOT NULL

7. in和 not in用法

在某个不连续的数据集合中 IN；不在某个不连续的数据集合中 NOT IN

例.找到学校为北大、复旦和山大的同学的相关数据

SELECT device_id,gender,age,university,gpa FROM user_profile WHERE university in('北京大学','复旦大学','山东大学');

8. 运算符及其优先级

优先级由低到高排列	运算符
1	=(赋值运算）、:=
2	II、OR
3	XOR
4	&&、AND
5	NOT
6	BETWEEN、CASE、WHEN、THEN、ELSE
7	=(比较运算）、<=>、>=、>、<=、<、<>、!=、 IS、LIKE、REGEXP、IN
8	|
9	&
10	<<、>>
11	-(减号）、+
12	*、/、%
13	^
14	-(负号）、〜（位反转）
15	!

and的优先级大于or，这里写大括号方便区分,表示两个条件或两个条件，可以省略。

例.gpa在3.5以上(不包括3.5)的山东大学用户 或 gpa在3.8以上(不包括3.8)的复旦大学同学

SELECT  device_id,gender,age,university,gpa FROM user_profile WHERE (gpa > 3.5 and university = '山东大学') OR (gpa > 3.8 and university = '复旦大学') ;

9.模糊查询 like

SELECT 列名 FROM 表名 WHERE 列名 LIKE '..._...';

SELECT 列名 FROM 表名 WHERE 列名 LIKE '...%...';

%代表多个字符，包括0个

_ 代表一个字符

注：转义字符 "\",紧跟其后的字符只是一个字符。

10.聚合函数结果作为筛选条件时，不能用where，而是用having语法

11.​​​​​​sql语句

书写顺序：

SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ...;

执行顺序：

FROM ... WHERE ... GROUP BY ... HAVING ... SELECT ... ORDER BY ...;

12.ROUND() 函数

ROUND 函数用于把数值字段舍入为指定的小数位数。

SELECT ROUND(column_name,decimals) FROM table_name;

参数	描述
column_name	必需。要舍入的字段。
decimals	必需。规定要返回的小数位数。

13.排序 ORDER BY

ASC:正序，默认

DESC:逆序

注：只作用于前面紧跟着的那一个，若多个排序，则分别写。

14.分组->过滤->筛选

 SELECT ... FROM ... WHERE ... GROUP BY ... HAVING...(过滤条件);

15.表联结

sql中联结表_xnlay的博客-CSDN博客https://blog.csdn.net/xnlay/article/details/98474648SQL的各种连接Join详解 - 章朔 - 博客园 (cnblogs.com)https://www.cnblogs.com/reaptomorrow-flydream/p/8145610.html

16.连接三张表

例.【牛客网 SQL23】 统计每个学校各难度的用户平均刷题数

题意明确：

计算每个学校用户不同难度下的用户平均答题题目数

---

问题分解：

• 限定条件：无；
• 每个学校：按学校分组group by university
• 不同难度：按难度分组group by difficult_level
• 平均答题数：总答题数除以总人数count(qpd.question_id) / count(distinct qpd.device_id)
• 来自上面信息三个表，需要联表，up与qpd用device_id连接，qd与qpd用question_id连接。

---

细节问题：

• 表头重命名：as
• 平均值精度未明确要求，忽略

完整代码：

1 2 3 4 5 6 7 8 9 10 11 12 13

select     university,     difficult_level,     count(qpd.question_id) / count(distinct qpd.device_id) as avg_answer_cnt from question_practice_detail as qpd left join user_profile as up on up.device_id=qpd.device_id left join question_detail as qd on qd.question_id=qpd.question_id group by university, difficult_level

拓展：连接多张表：mysql三个表连接语句_INNER JOIN连接两个表、三个表、五个表的SQL语句_保研学长说的博客-CSDN博客https://blog.csdn.net/weixin_42544663/article/details/113545404

17.SQL UNION 操作符

SQL UNION 操作符合并两个或多个 SELECT 语句的结果。

请注意，UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同。

SQL UNION 语法

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

注释：默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。

SQL UNION ALL 语法

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

注释：UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。

18.CASE函数

是一种多分支的函数，可以根据条件列表的值返回多个可能的结果表达式中的一个。
可用在任何允许使用表达式的地方，但不能单独作为一个语句执行。
分为：
        简单CASE函数
        搜索CASE函数

简单 CASE函数

1 2 3 4 5 6

CASE 测试表达式 WHEN 简单表达式1 THEN 结果表达式1 WHEN 简单表达式2 THEN 结果表达式2 … WHEN 简单表达式n THEN 结果表达式n [ ELSE 结果表达式n+1 ] END

计算测试表达式，按从上到下的书写顺序将测试表达式的值与每个WHEN子句的简单表达式进行比较。
如果某个简单表达式的值与测试表达式的值相等，则返回第一个与之匹配的WHEN子句所对应的结果表达式的值。
如果所有简单表达式的值与测试表达式的值都不相等，
若指定了ELSE子句,则返回ELSE子句中指定的结果表达式的值；
若没有指定ELSE子句，则返回NULL。

例48. 查询班级表中的学生的班号、班名、系号和班主任号，并对系号作如下处理：
当系号为1时，显示 “计算机系”；
当系号为2时，显示 “软件工程系”；
当系号为3时，显示 “物联网系”。

1 2 3 4 5 6 7

SELECT 班号 ,班名, CASE 系号 WHEN 1 THEN '软件工程系' WHEN 2 THEN '计算机系' WHEN 3 THEN '物联网系' END AS 系号,班主任号 FROM 班级表

搜索CASE函数

1 2 3 4 5 6

CASE WHEN 布尔表达式1 THEN 结果表达式1 WHEN 布尔表达式2 THEN 结果表达式2 … WHEN 布尔表达式n THEN 结果表达式n [ ELSE 结果表达式n+1 ] END

按从上到下的书写顺序计算每个WHEN子句的布尔表达式。
返回第一个取值为TRUE的布尔表达式所对应的结果表达式的值。
如果没有取值为TRUE的布尔表达式，
则当指定了ELSE子句时,返回ELSE子句中指定的结果；
如果没有指定ELSE子句，则返回NULL。

例48用搜索CASE来做：

1 2 3 4 5 6 7

SELECT 班号 ,班名, CASE WHEN 系号=1 THEN '软件工程系' WHEN 系号=2 THEN '计算机系' WHEN 系号=3 THEN '物联网系' END AS 系号,班主任号 FROM 班级表

例49.查询“M01F011”号课程的考试情况，列出学号、课程号和成绩，同时将百分制成绩显示为等级。

1 2 3 4 5 6 7 8 9 10

SELECT 学号,课程号, CASE WHEN 成绩 >= 90 THEN '优' WHEN 成绩 BETWEEN 80 AND 89 THEN '良' WHEN 成绩 BETWEEN 70 AND 79 THEN '中' WHEN 成绩 BETWEEN 60 AND 69 THEN '及格' WHEN 成绩 <60 THEN '不及格' END 成绩 FROM 成绩表 WHERE 课程号 = 'M01F011'

CASE函数(续)

例50.统计每个班男生和女生的数量各是多少，统计结果的表头为，班号，男生数量，女生数量。

1 2 3 4

SELECT 班号, COUNT(CASE WHEN 性别=‘男’ THEN ‘男’ END) 男生数, COUNT(CASE WHEN 性别=‘女’ THEN ‘女’ END) 女生数 FROM 学生表 GROUP BY 班号

例51.判断成绩的等级，85-100为“优”，70-84为“良”，60-69为“及格”，60以下为“不及格”，并统计每一等级的人数。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

SELECT CASE WHEN GRADE BETWEEN 85 AND 100 THEN '优' WHEN GRADE BETWEEN 70 AND 84 THEN '良' WHEN GRADE BETWEEN 60 AND 69 THEN '及格' ELSE '不及格' END 等级, COUNT(*) 人数 FROM SC GROUP BY CASE WHEN GRADE BETWEEN 85 AND 100 THEN '优' WHEN GRADE BETWEEN 70 AND 84 THEN '良' WHEN GRADE BETWEEN 60 AND 69 THEN '及格' ELSE '不及格' END

题解

题目：现在运营想要将用户划分为25岁以下和25岁及以上两个年龄段，分别查看这两个年龄段用户数量

1 2 3 4 5

SELECT CASE WHEN age < 25 OR age IS NULL THEN '25岁以下'             WHEN age >= 25 THEN '25岁及以上'             END age_cut,COUNT(*)number FROM user_profile GROUP BY age_cut

示例：user_profile

根据示例，你的查询应返回以下结果：

19.时间表达

例.【牛客网sql28】2021年8月每天用户练习题目的数量

---

问题分解：

• 限定条件：2021年8月，写法有很多种，比如用year/month函数的year(date)=2021 and month(date)=8，比如用date_format函数的date_format(date, "%Y-%m")="202108"
• 每天：按天分组group by date
• 题目数量：count(question_id)

---

细节问题：

• 表头重命名：as
• 输出示例中每天的字段只取了几号，要去掉年月，用day函数即可

完整代码：

1 2 3 4 5 6

select     day(date) as day,     count(question_id) as question_cnt from question_practice_detail where month(date)=8 and year(date)=2021 group by date

20.字符串截取substring_index用法

例1.【牛客网sql30】统计每个性别的用户分别有多少参赛者

---

问题分解：

• 限定条件：无；

• 每个性别：按性别分组group by gender，但是没有gender字段，需要从profile字段截取，按字符,分割后取出即可。可使用substring_index函数可以按特定字符串截取源字符串。

  substring_index(FIELD, sep, n)可以将字段FIELD按照sep分隔：

  (1).当n大于0时取第n个分隔符(n从1开始)左边的全部内容；

  (2).当n小于0时取倒数第n个分隔符(n从-1开始)右边的全部内容；

  因此，本题可以直接用substring_index(profile, ',', -1)取出性别。 附：substring_index函数解析https://zhuanlan.zhihu.com/p/109778760

• 多少参赛者：计数统计，count(device_id)

---

细节问题：

• 表头重命名：as

完整代码：

1 2 3 4 5

select     substring_index(profile, ',', -1) as gender,     count(device_id) as number from user_submit group by gender

例2.【牛客网sql32】统计每个年龄的用户分别有多少参赛者

---

问题分解：

• 限定条件：无；
• 每个年龄：按年龄分组group by age，但是没有age字段，需要从profile字段截取，按字符,分割后取出即可。可使用substring_index函数可以按特定字符串截取源字符串。
  substring_index(FIELD, sep, n)可以将字段FIELD按照sep分隔：
  (1).当n大于0时取第n个分隔符(n从1开始)之后的全部内容；
  (2).当n小于0时取倒数第n个分隔符(n从-1开始)之前的全部内容；
  因此，本题可以先用substring_index(profile, ',', 3)取出"180cm,75kg,27"，然后用substring_index(profile, ',', -1)取出27。
  当然也可以用substring_index(substring_index(profile, ",", -2), ",", 1)取出27。
  附：substring_index函数解析https://zhuanlan.zhihu.com/p/109778760
• 多少参赛者：计数统计，count(device_id)

---

细节问题：

• 表头重命名：as

完整代码：

1 2 3 4 5

select     substring_index(substring_index(profile, ',', 3), ',', -1) as age,     count(device_id) as number from user_submit group by age

21.窗口函数

例.【牛客网sql33】每个学校里gpa最低的同学

问题分解：

• 限定条件：gpa最低，看似min(gpa)，但是要留意，是每个学校里的最低，不是全局最低。min(gpa)的时候对应同学的ID丢了，直接干是拿不到最低gpa对应的同学ID的；
• 每个学校最低：
  第一种方式是用group by把学校分组，然后计算得到每个学校最低gpa，再去找这个学校里和这个gpa相等的同学ID。注意这样如果最低gpa对应多个同学，都会输出，题目没有明确此种情况，心理明白就行。
  第二种方式是利用窗口函数，先按学校分组计算排序gpa，得到最低gpa的记录在用子查询语法拿到需要的列即可。此题中row_number可以得到排序后的位序，取位序为1即可得到最小值（升序时）。
• 窗口函数语法：row_number/rank/dense_rank over (partition by FIELD1 order by FIELD2)，传送链接https://zhuanlan.zhihu.com/p/92654574

---

细节问题：

• 如果题目明确了有多个最低gpa情况下，输出结果情况，需要留意
• 表头重命名：as

完整代码：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

select device_id, university, gpa from (     select *,     row_number() over (partition by university order by gpa) as rn     from user_profile ) as univ_min where univ_min.rn=1 # 方法二： select a.device_id, a.university, a.gpa from user_profile a right join (     select university, min(gpa) as gpa     from user_profile     group by university ) as b on a.university=b.university and a.gpa=b.gpa order by a.university

22.if()

题意明确：

复旦大学的每个用户在8月份练习的总题目数和回答正确的题目数情况

---

问题分解：

• 限定条件：需要是复旦大学的（来自表user_profile.university），8月份练习情况（来自表question_practice_detail.date）
• 从date中取month：用month函数即可；
• 总题目：count(question_id)
• 正确的题目数：sum(if(qpd.result='right', 1, 0))
• 按列聚合：需要输出每个用户的统计结果，因此加上group by up.device_id

---

细节问题：

• 8月份没有答题的用户输出形式：题目要求『对于在8月份没有练习过的用户，答题数结果返回0』因此明确使用left join即可，即输出up表中复旦大学的所有用户，如果8月没有练习记录，输出0就好了
• 老样子-表头：as语法重命名后两列就好

完整代码：

1 2 3 4 5 6 7 8 9 10

select up.device_id, up.university,     count(question_id) as question_cnt,     sum(if(qpd.result='right', 1, 0)) as right_question_cnt from user_profile as up left join question_practice_detail as qpd   on qpd.device_id = up.device_id and month(qpd.date) = 8 where up.university = '复旦大学' group by up.device_id

23.【牛客网SQL29】题解：计算用户的平均次日留存率

题目分析

所谓次日留存，指的是同一用户（在本题中则为同一设备，即device_id）在当天和第二天都进行刷题。注意，在这题我们不关心同一用户（设备）在这天答了什么题、答题结果如何，只关心他是否答题，因此对于这题来说存在重复的数据（如下图红框所示），需要使用 DISTINCT 去重。 

而次日留存率可以这样表示：

次日留存率=去重的数据表中符合次日留存的条目数目去重的数据表中所有条目数目次日留存率=\frac{去重的数据表中符合次日留存的条目数目}{去重的数据表中所有条目数目}次日留存率=去重的数据表中所有条目数目去重的数据表中符合次日留存的条目数目​

具体而言，使用两个子查询，查询出两个去重的数据表，并使用条件（q2.date应该是q1.date的后一天）进行筛选，如下所示（数据未显示完全，从左至右顺序，列表名为 q1.device_id, q1.date, q2.device_id, q2.date)

因为使用的是q1左级联q2，所以q1的所有信息是显示的；而q2中只显示留存的信息，否则为null。

最后，分别统计q1.device_id 和 q2.device_id 作去重后的所有条目数和去重后的次日留存条目数，即可算出次日留存率。

具体实现

1 2 3 4 5 6 7

SELECT     COUNT(q2.device_id) / COUNT(q1.device_id) AS avg_ret FROM     (SELECT DISTINCT device_id, date FROM question_practice_detail)as q1 LEFT JOIN     (SELECT DISTINCT device_id, date FROM question_practice_detail) AS q2 ON q1.device_id = q2.device_id AND q2.date = DATE_ADD(q1.date, interval 1 day)

注意，MySQL中 COUNT在对列进行计数时不统计值为 null的条目