SQL笔记——基础篇

前言

针对本人不熟悉的SQL语法练习进行记录，题目来源于牛客SQL练习。

1 去重

• select distinct 列名 from …

select distinct university from user_profile;

2 重命名+限制行数

• select 实际列名 as 自定义列名
• limit 数字1,数字2; // 表示选择 [ 数字1 , 数字2 )范围

select device_id as user_infos_example from user_profile limit 0,2

3 排序

• … from … order by 列1 asc,列2 desc; // 表示先列名1升序，再对列2进行降序。

select gpa,age from user_profile order by gpa asc,age desc;

4 返回统计数据

函数	作用
COUNT	用于计算某列中值的数量。
SUM	用于计算某列中值的总和。
AVG	用于计算某列中值的平均数。
MAX	用于计算某列中的最大值。
MIN	用于计算某列中的最小值。
ROUND(列名,n)	保留n位小数

• select count (列名) as 自定义列名 from …

select
    count(DISTINCT gender) as male_num,
    round(avg(gpa), 1) as avg_gpa
from
    user_profile
where
    gender = "male";

5 包含某数据

• … where 列名 in ()
• … where 列名 not in ()

# 找到学校为北大、复旦和山大的同学
select device_id,gpa from user_profile where university in('北京大学','复旦大学','山东大学');

select device_id,university FROM user_profile WHERE university NOT IN ("复旦大学")

6 操作符运算优先级

• 优先级：and＞or

# 找到gpa在3.5以上(不包括3.5)的山东大学用户 或 gpa在3.8以上(不包括3.8)的复旦大学同学
select device_id,gpa from user_profile where university='山东大学' and gpa>3.5 or university='复旦大学' and gpa>3.8 

7 LIKE模糊查询

• _ ：匹配任意一个字符；

• % ：匹配0个或多个字符；

• []: 匹配其中的任意一个字符；

• [^]: ^代表 非，取反的意思；不匹配中的任意一个字符。

# 查看所有大学中带有北京的用户的信息
SELECT device_id,age,university FROM user_profile WHERE university LIKE '%北京%'

8 最值

• select max(列名) as 自定义列名 where…
• … order by 列名 desc limit 1

# 方法1
select max(gpa) as gpa from user_profile where university='复旦大学';
 
# 方法2
select gpa from user_profile where university='复旦大学' order by gpa desc limit 1

9 having

• 聚合函数结果作为筛选条件时，不能用where，而是用having语法，配合重命名即可；
  • 如统计平均数，自定义列名为avg_num，再对平均数avg_num进行筛选，则必须使用having。
• having子句用于分组后筛选，where子句用于行条件筛选
• having一般都是配合 group by 和聚合函数一起出现，如(count(),sum(),avg(),max(),min())
• 顺序：… where … group by … 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

10 多表查询

SELECT 姓名,班名,系名
FROM 起始表 别名
JOIN 联合表1 别名 ON （起始表）别名.班号 = （联合表1）别名.班号
JOIN 联合表2 别名 ON （联合表1）别名.系号 = （联合表2）别名.系号
WHERE …

题目来源 浙江大学用户题目回答情况

SELECT q.device_id,question_id,result
FROM question_practice_detail q JOIN user_profile u ON q.device_id=u.device_id
WHERE university='浙江大学'

引入：多表连接中已摒弃的92写法（2022年我专业的MySQL课程通用这种）
SELECT t1.university, t2.difficult_level,
FROM user_profile as t1,question_practice_detail as t2,
WHERE t1.device_id = t2.device_id

• 这种写法称为1992年语法。在92语法中，多张表的连接条件会放到where子句中，同时where需要对表进行条件过滤，因此相当于过滤条件和连接条件揉到一起，较为混乱。
• 99语法修正此缺点，把连接条件、过滤条件分开来，并引入了新的table join语法结构。

11 联合查找

• UNION ALL：不去重

select 
    device_id, gender, age, gpa
from user_profile
where university='山东大学' or gender='male'
# 运行效果与下面的SQL语句运行的一致

select 
    device_id, gender, age, gpa
from user_profile
where university='山东大学'
union #若本行是 union all，则会不去重
select 
    device_id, gender, age, gpa
from user_profile
where gender='male'

union all（索引） VS or in（全表扫描） ？

单表的筛选字段	索引	索引+非索引	非索引
union all	√	√
or in		√	√

• √ 表示，如筛选字段是索引时，则优先使用union all。

多表联合查询需考虑：连接方式、查询表数据量分布、索引等方能确定。

12 CASE函数¹

• 简单CASE函数：类似Java的switch()函数

SELECT 班号 ,班名,
(CASE 列名
WHEN 1 THEN ‘软件工程系’
WHEN 2 THEN ‘计算机系’
END ) AS 自定义列名,
teacher AS 班主任名
FROM 班级表

• 搜索版CASE函数：当对检索列设计较复杂的筛选，如筛选列的值在某个区间，则应使用搜索版

SELECT 学号,课程号,
(CASE
WHEN 列名系号=1 THEN ‘软件工程系’
WHEN 列名系号=2 THEN ‘计算机系’
END ) as 自定义列名,
teacher as 班主任名
FROM 班级表

• 分条件更新字段值

将工资低于3000的员工涨幅工资20%,工资等于高于3000的员工涨幅8%

• 如若不使用CASE函数，如我低于3000的工资按照第一个条件涨幅后高于3000，此时符合了第二个涨幅条件，出现继续涨幅的情况

update
    t_salary
set
    salary = (
        case
            when salary < 3000 then salary + salary * 0.2
            when salary >= 3000 then salary + salary * 0.08
            else salary
        end
    )

• 检查表中字段值是否一致

select name,
(case 
	when 待检查列名 in(select 待检查列名 from 表2) then '一致'
	else '不一致'
end) as 比较结果（自定义的列名）
from 表一

• 行转列（常考）

将表中数据按照每个学生姓名 、科目、成绩进行排序

name	class	grade
小明	语文	90
小明	数学	90
小明	英语	80
小白	语文	60
小白	数学	60
小白	英语	70

SELECT
    NAME as 姓名,
    max(
        CASE
            WHEN class = '语文' THEN grade
            ELSE 0
        END
    ) as 语文,
    max(
        CASE
            WHEN class = '数学' THEN grade
            ELSE 0
        END
    ) as 数学,
    max(
        CASE
            WHEN class = '英语' THEN grade
            ELSE 0
        END
    ) as 英语
FROM
    t_source
GROUP BY
    NAME

姓名	语文	数学	英语
小明	90	90	80
小白	60	60	70

13 日期函数

• 获取系统时间

#获取当前系统的日期时间
SELECT NOW();  # 2021-12-22 13:50:58
 
#获取当前系统的日期
SELECT CURDATE(); # 2021-12-22
 
#获取当前系统的时间
SELECT CURTIME(); # 13:53:11

• 从日期格式的字符串中提取：extract(type from data)

# 直接筛选所需的时间段
… where YEAR(时间列名) = 2022 and MONTH(时间列名)=7 # 筛选时间列中值为2022年7月某数据

YEAR()
MONTh()
DAY()
HOUR()
MINUTE()
SECOND()

# 从日期格式的字符串中获取年、月、日、时、分、秒 ，示例字符串：2021-12-22 13:50:58
SELECT EXTRACT(YEAR FROM '2021-12-22 13:50:58'); # 2021
 
SELECT EXTRACT(MONTH FROM '2021-12-22 13:50:58'); # 12
 
SELECT EXTRACT(DAY FROM '2021-12-22 13:50:58'); # 22
 
SELECT EXTRACT(HOUR FROM '2021-12-22 13:50:58'); # 13
 
SELECT EXTRACT(MINUTE FROM '2021-12-22 13:50:58'); # 50
 
SELECT EXTRACT(SECOND FROM '2021-12-22 13:50:58'); # 58

• 日期增加date_add(date,INTERVAL exp type) 或 减少date_sub(date,INTERVAL exp type)

#增加1天
SELECT DATE_ADD('2021-12-22 13:50:58', INTERVAL 1 DAY);  # 2021-12-23 13:50:58
#增加1小时
SELECT DATE_ADD('2021-12-22 13:50:58', INTERVAL 1 HOUR);  # 2021-12-23 14:50:58
 
#日期减少，使用函数
#减少1天
SELECT DATE_SUB('2021-12-01 13:50:58', INTERVAL 1 DAY); # 2021-11-30 13:50:58
 
#其他间隔
INTERVAL 1 YEAR
INTERVAL 1 MONTH
INTERVAL 1 DAY
INTERVAL 1 HOUR
INTERVAL 1 MINUTE
INTERVAL 1 SECOND

• 日期格式化：'%Y-%m-%d %H:%i:%s'

# 日期格式化，注意mysql中日期时间格式化字符串的写法：'%Y-%m-%d %H:%i:%s'
SELECT DATE_FORMAT('2021-12-01 13:50:58', '%Y/%m/%d %H:%i:%s'); # 2021/12/01 13:50:58
 
# '%Y%m-%d %H:00:00',取日期和时间的整点数，分钟和秒钟为00
SELECT DATE_FORMAT('2021-12-01 13:50:58', '%Y-%m-%d %H:00:00'); # 2021/12/01 13:00:00

• 日期间隔：DATEDIFF()

DATEDIFF(day1,day2)=1 # day1和day2相差一天的数据

14 平均次日留存率

题目来源 SQL29 计算用户的平均次日留存率

思路 / 要求	实现
题目仅给一张表，表中含连续刷题和不连续刷题的用户	left outer join做自表联结
用户刷题后第二天再来刷题的平均概率	连续两天刷题的人数 / 前一天做题的总人数
区分连续两天刷题的人数	DATEDIFF区分第一天和第二天在线的device_id
当天用户可能多刷题 / 登录多次	distinct q2.device_id,q2.date，双重去重

SELECT
    COUNT(DISTINCT q2.device_id, q2.date) / COUNT(DISTINCT q1.device_id, q1.date) as avg_ret
FROM
    question_practice_detail as q1
    left outer join question_practice_detail as q2 on (
        q1.device_id = q2.device_id
        and DATEDIFF (q2.date, q1.date) = 1
    )

• count(date1)得到左表全部的date记录数作为分母（去重之后便是全部人数）
• count(date2)得到右表关联上了的date记录数作为分子（关联上说明日期差为1，满足次日留存的条件）²

使用q1左级联q2，所以q1的所有信息是显示的；而q2只显示留存的信息，否则为null。为了便于理解，做了以下打印。

以下是另一种解法，用DATE_ADD方法，让右表为左表的日期推迟一天后的数据进行匹配。

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)

15 字符串截断

一开始的思路是as出male和female两个行头，看了其他大佬的思路，直接对字符串进行截断，对出现过female和male做打印和累计。

• SUBSTRING_INDEX的写法，可以参考SUBSTRING_INDEX用法

substring_index(str,delim,count)

• str:要处理的字符串
• delim:分隔符
• count:计数

例子：str=www.wikidm.cn

• substring_index(str,‘.’,1) 结果是：www
• substring_index(str,‘.’,2) 结果是：www.wikidm

如果count是正数，则从左往右数，第N个分隔符左边的全部内容

如果count是负数，则从右往左数，第N个分隔符右边的所有内容

• substring_index(str,‘.’,-2) 结果为：wikidm.cn

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

• LIKE的写法

SELECT
    IF (profile LIKE '%female', 'female', 'male') as gender,
    COUNT(*) as number
FROM
    user_submit
GROUP BY
    gender;

---

剩余题目自我感觉难度较大，涉及窗口函数等等，我将其归至下一章的进阶版

---

1. 知识点 参考链接。 ↩︎

2. 来自 解题思路链接 ↩︎