1.知识点

distince去重:

select distince id from user;

limit查询结果限制返回行数:

select device_id from user_profile limit 0,2---运行效率更高

select device_id from user_profile limit 2 ---运行效率低

也可结合 limit offset：一起使用时，limit表示要取的数量，offset表示跳过的数量

select device_id from user_profile limit 2 offset 0 // 跳过0条，从第一条数据开始取，取两条数据 ---运行效率中

查询条件

in和not in：

select device_id,gender,age,university,gpa from user_profile
where university in('北京大学','复旦大学','山东大学');

like模糊匹配:

一般形式为：

列名 [NOT ] LIKE

匹配串中可包含如下四种通配符：
_：匹配任意一个字符；
%：匹配0个或多个字符；
[ ]：匹配[ ]中的任意一个字符(若要比较的字符是连续的，则可以用连字符“-”表达 )；
[^ ]：不匹配[ ]中的任意一个字符。例23．查询学生表中姓‘张’的学生的详细信息。

1	`SELECT` * ``FROM 学生表 ``WHERE 姓名 ``LIKE `‘张%’`

例24．查询姓“张”且名字是3个字的学生姓名。

复制代码

1	`SELECT` * ``FROM 学生表 ``WHERE 姓名 ``LIKE `'张__’`
例25.查询学生表中姓‘张’、姓‘李’和姓‘刘’的学生的情况。

1	`SELECT` * ``FROM 学生表 ``WHERE 姓名 ``LIKE `'[张李刘]%’`

例26.查询学生表表中名字的第2个字为“小”或“大”的学生的姓名和学号。

1	`SELECT` 姓名,学号 ``FROM 学生表 ``WHERE 姓名 ``LIKE `'_[小大]%'`

例27.查询学生表中所有不姓“刘”的学生。

1	`SELECT` 姓名 ``FROM 学生 ``WHERE 姓名 ``NOT `LIKE` `'刘%’`

例28.从学生表表中查询学号的最后一位不是2、3、5的学生信息。

1	`SELECT` * ``FROM 学生表 ``WHERE 学号 ``LIKE `'%[^235]'`

使用聚合函数汇总数据

SQL提供的统计函数有：
COUNT()：统计表中元组个数；
COUNT([DISTINCT] <列名>)：统计本列列值个数；
SUM( <列名> )：计算列值总和；
AVG( <列名> )：计算列值平均值；
MAX( <列名> )：求列值最大值；
MIN( <列名> )：求列值最小值。
上述函数中除COUNT(【Shift+8】)外，其他函数在计算过程中均忽略NULL值。

统计函数不能出现在WHERE子句中。
例如，查询成绩最高的学生的学号，如下写法是错误的：
SELECT 学号 FROM 成绩表
WHERE 成绩 = MAX(成绩)

例29.统计学生总人数。

1	`SELECT` COUNT``(*) ``FROM `学生表`

例30.统计选修了课程的学生的人数。

12	`SELECT` `COUNT` (``DISTINCT 学号)``FROM `成绩表`

例31.计算学号为“11214D24”的学生的考试总成绩之和。

12	`SELECT` SUM``(成绩) ``FROM 成绩表``WHERE `学号 = ‘11214D24 '`

例32.计算“M01F011”课程的学生的考试平均成绩。

12	`SELECT` AVG``(成绩) ``FROM 成绩表``WHERE `课程号 = ‘M01F011 ‘`

例33.查询选修了“M01F011” 课程的最高分和最低分。

复制代码

123	`SELECT` MAX``(成绩) 最高分,``MIN``(成绩) 最低分 ``FROM 成绩表``WHERE `课程号 = ‘M01F011 '`

round保留小数后几位:

select count(gender) as male_num, round(avg(gpa), 1) as avg_gpa//保留一位小数 from user_profile where gender="male";

groupy by分组:

每个学校每种性别的用户数、30天内平均活跃天数和平均发帖数量

select 
    gender, university,
    count(device_id) as user_num,
    avg(active_days_within_30) as avg_active_days,
    avg(question_cnt) as avg_question_cnt
from user_profile
group by gender, university

where与having的区别:

WHERE与HAVING的根本区别在于：

WHERE子句在GROUP BY分组和聚合函数之前对数据行进行过滤；
HAVING子句在GROUP BY分组和聚合函数之后对数据行进行过滤。

因此，WHERE子句中不能使用聚合函数。例如，以下语句将会返回错误：

-- 查找人数大于 5 的部门
select dept_id, count(*)
from employee
where count(*) > 5
group by dept_id;

-- 查找人数大于 5 的部门
select dept_id, count(*) sum
from employee
where sum > 5
group by dept_id;

正确的方法是使用HAVING对聚合之后的结果进行过滤：

-- 查找人数大于 5 的部门
select dept_id, count(*)
from employee
group by dept_id
having count(*) > 5;

另一方面，HAVING子句中不能使用除了分组字段和聚合函数之外的其他字段。例如，以下语句将会返回错误：

-- 统计每个部门月薪大于等于 30000 的员工人数
select dept_id, count(*)
from employee
group by dept_id
having salary >= 30000;

因为经过GROUP BY分组和聚合函数之后，不再存在 salary 字段，HAVING子句中只能使用分组字段或者聚合函数。

题目：现在运营想查看每个学校用户的平均发贴和回帖情况，寻找低活跃度学校进行重点运营，请取出平均发贴数低于5的学校或平均回帖数小于20的学校。

select university ,avg(question_cnt) as avg_question_cnt,avg(answer_cnt) as avg_answer_cnt
from user_profile
group by university
having avg(question_cnt)<5 OR
avg(answer_cnt) <20;

order by 排序:

order by num 升序排列 order by num desc 降序排列不同大学的用户平均发帖情况，并按照平均发帖情况进行升序排列

select university,
    avg(question_cnt) as avg_question_cnt
from user_profile
group by university
order by avg_question_cnt

left join on ,right join on,inner join on 的区别

left join(左联接) ：返回包括左表中的所有记录和右表中联结字段相等的记录
right join(右联接) ：返回包括右表中的所有记录和左表中联结字段相等的记录
inner join(等值连接)：只返回两个表中联结字段相等的行

仅查看山东大学的用户在不同难度下的每个用户的平均答题题目数法一

SELECT 
    t1.university,
    t3.difficult_level,
    COUNT(t2.question_id) / COUNT(DISTINCT(t2.device_id)) as avg_answer_cnt
from 
    user_profile as t1,
    question_practice_detail as t2,
    question_detail as t3
WHERE 
    t1.university = '山东大学'
    and t1.device_id = t2.device_id
    and t2.question_id = t3.question_id
GROUP BY
    t3.difficult_level;

法二

select 
    "山东大学" as university,
    difficult_level,
    count(qpd.question_id) / count(distinct qpd.device_id) as avg_answer_cnt
from question_practice_detail as qpd

inner join user_profile as up
on up.device_id=qpd.device_id and up.university="山东大学"

inner join question_detail as qd
on qd.question_id=qpd.question_id

group by difficult_level

union与union all

使用 UNION可以实现将多个查询结果集合并为一个结果集。所有查询语句中列的个数和列的顺序必须相同。
所有查询语句中对应列的数据类型必须兼容。
ORDER BY语句要放在最后一个查询语句的后边。例46．查询系号是1和2的班级的班号、班名、系号，系号是1 的记录在前，2在后。

SELECT 班号, 班名, 系号
FROM 班级表 WHERE 系号= 1
UNION
SELECT 班号, 班名, 系号
FROM 班级表 WHERE 系号= 2

等价于：

SELECT 班号, 班名, 系号
FROM 班级表 WHERE 系号= 1
UNION
SELECT 班号, 班名, 系号
FROM 班级表 WHERE 系号= 2
ORDER BY 系号 DESC

题目：现在运营想要分别查看学校为山东大学或者性别为男性的用户的device_id、gender、age和gpa数据，请取出相应结果，结果不去重。

SELECT device_id,gender,age,gpa FROM user_profile
WHERE university='山东大学' 
UNION ALL
SELECT device_id,gender,age,gpa FROM user_profile
WHERE gender='male'

case函数

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

简单 CASE函数

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时，显示 “物联网系”。

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

搜索CASE函数

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

例48用搜索CASE来做：

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

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

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
等级

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

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

时间函数

year(date)返回date的年

month(date)返回date的月

day(date)返回date是几号

求2021年8月每天用户练习题目的数量

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

字符串截取substring_index函数

统计每个性别的用户分别有多少参赛者 法一：

SELECT SUBSTRING_INDEX(profile,",",-1) gender,COUNT(*) number
FROM user_submit 
GROUP BY gender;

法二:

SELECT 
CASE 
WHEN profile LIKE '%,male' then 'male'
WHEN profile LIKE '%,female' then 'female'
end as gender,
COUNT(*) AS number 
FROM user_submit
GROUP BY gender;

判断函数if()

IF(条件表达式,值1,值2)

如果条件表达式为True，返回值1，为False,返回值2.
返回值可以是任何值，比如：数值，文本，日期，空值，NULL，数学表达式，函数等。

浙江大学的用户在不同难度题目下答题的正确率情况，按照准确率升序输出

select
    qd.difficult_level,
    sum(if (qpd.result = 'right', 1, 0)) / count(qpd.device_id) as correct_rate
from
    user_profile as up,
    question_practice_detail as qpd,
    question_detail as qd
where
    up.device_id = qpd.device_id
    and qpd.question_id = qd.question_id
    and up.university = '浙江大学'
group by
    qd.difficult_level
order by
    correct_rate

sql刷题笔记