什么是数据库：
数据库 = 用来存数据的地方 + 一套管理这些数据的系统
可以永久储存数据
常见的数据库软件，比如 MySQL，你可以把它理解为：一个专门“管数据”的软件，感觉就类似于c语言里面的文件，只不过更高级一点
数据库的结构
先在脑子里有个概念，明白怎么操作数据库
这个是类似于分层的，
MySQL（管理系统）
↓
数据库（database）
↓
表（table）
↓
数据（一行一行）
就是这种感觉，首先进入mysql，mysql里面有很多数据库，然后可以查看有哪些数据库，然后可以选择其中某个进入，进入数据库可以看这个数据库里面的表，也就是数据，表存着数据;
MySQL 软件（整个程序）
└── 数据库A（比如：学生管理系统）
│ ├── 表1：学生信息表
│ ├── 表2：课程表
│ └── 表3：选课表
│
└── 数据库B（比如：电商系统）
├── 表1：用户表
├── 表2：商品表
└── 表3：订单表
简单操作一下
进入mysql
开启mysql，一般下载的是默认开机自动打开，主包这边把那个关了，如何开启呢
win+R，写入services.msc
找到MYSQL80，手动打开mysql（这边建议在属性里面把mysql启动类型改成手动）
开启后进入 法1：
菜单搜索mysql，点击进入
输入密码
法2：
直接在终端输入mysql -u root -p 终端也是在菜单搜索就行
输入密码
总结一下最基础的操作代码
登录和退出
-- 登录（在终端输入，不是 MySQL 里面）
mysql -u root -p
-- 退出（在 MySQL 里面输入）
exit;
数据库操作
-- 1. 查看当前有哪些数据库
SHOW DATABASES;
-- 2. 创建一个新数据库
CREATE DATABASE mydb;
-- 3. 进入某个数据库（使用它）
USE mydb;
-- 4. 查看当前在哪个数据库
SELECT DATABASE();
-- 5. 删除一个数据库（谨慎操作）
DROP DATABASE mydb;
表操作
-- 1. 查看当前数据库里有哪些表
SHOW TABLES;
-- 2. 查看某张表的结构
DESC users;
-- 或者
DESCRIBE users;
-- 3. 查看建表语句（看这张表是怎么建的）
SHOW CREATE TABLE users;
日常操作就是这几个步骤：
登录 → SHOW DATABASES → USE xxx → SHOW TABLES → 干活
sql语法基础
SELECT 列名
FROM 表名
WHERE 条件
ORDER BY 列名 ASC/DESC
LIMIT 数量;
顺序要记住：SELECT → FROM → WHERE → ORDER BY → LIMIT，顺序乱了会报错
下面系统性学习一下数据库
数据类型
比如：
- 年龄 → 整数
- 姓名 → 字符串
- 生日 → 日期
- 工资 → 小数
所以创建表时：
CREATE TABLE student(
name VARCHAR(20),
age INT
);
这里：
VARCHAR(20)表示字符串INT表示整数
一、数值类型
数值类型就是：
存数字
MySQL 中最常用的是：
| 类型 | 作用 |
|---|---|
| TINYINT | 很小的整数 |
| INT | 普通整数 |
| BIGINT | 大整数 |
| FLOAT | 单精度小数 |
| DOUBLE | 双精度小数 |
| DECIMAL | 精确小数 |
二、整数类型
1. TINYINT
占 1 个字节
范围很小
age TINYINT
适合：
- 年龄
- 性别
- 状态位
因为范围够用，而且省空间
2. INT
最常用的整数类型
占 4 个字节
id INT
score INT
开发里：
大部分整数都直接用 INT
3. BIGINT
超大整数
占 8 个字节
qq BIGINT
phone BIGINT
适合：
- QQ号
- 雪花算法ID
- 大数据编号
现在很多项目主键都开始用 BIGINT
三、小数类型
1. FLOAT
单精度浮点数
price FLOAT
优点：
- 节省空间
缺点：
- 不精确
所以：
金额一般不用 FLOAT。
因为可能出现：
0.1 + 0.2 ≠ 0.3
2. DOUBLE
双精度浮点数
比 FLOAT 更精确
height DOUBLE
但依然存在精度问题
3. DECIMAL（重点）
精确小数
开发里：
金额必须用 DECIMAL
语法：
DECIMAL(总位数, 小数位数)
例如：
salary DECIMAL(10,2)
表示：
总共 10 位
其中 2 位小数
能存：
12345678.99
不能存：
123456789.99
因为超位数了
四、字符串类型
最常用
| 类型 | 作用 |
|---|---|
| CHAR | 定长字符串 |
| VARCHAR | 变长字符串 |
| TEXT | 长文本 |
五、CHAR 和 VARCHAR
这是面试高频
1. CHAR
定长字符串
name CHAR(10)
无论存：
a
还是：
abcdef
都会占满 10 个字符空间。
特点：
- 查询速度快
- 浪费空间
适合：
- 性别
- 身份证固定长度
- 手机号固定长度
2. VARCHAR（重点）
变长字符串
name VARCHAR(20)
如果存：
abc
只占 3 个字符长度。
特点：
- 节省空间
- 开发最常用
现在开发里：
基本默认 VARCHAR
3. CHAR 和 VARCHAR 区别
| 类型 | CHAR | VARCHAR |
|---|---|---|
| 长度 | 固定 | 可变 |
| 空间 | 浪费 | 节省 |
| 速度 | 快 | 稍慢 |
| 场景 | 固定长度 | 普通字符串 |
六、TEXT 类型
存大文本
content TEXT
适合：
- 博客内容
- 文章
- 评论
- 富文本
因为 VARCHAR 长度有限
TEXT 可以存很多字符
七、日期时间类型
| 类型 | 作用 |
|---|---|
| DATE | 日期 |
| TIME | 时间 |
| DATETIME | 日期时间 |
| TIMESTAMP | 时间戳 |
八、DATE
只存日期
格式：
YYYY-MM-DD
例如：
birthday DATE
存：
2026-05-08
九、TIME
只存时间
格式：
HH:MM:SS
例如：
start_time TIME
存：
12:30:45
十、DATETIME（重点）
存日期 + 时间
格式：
YYYY-MM-DD HH:MM:SS
例如：
create_time DATETIME
存：
2026-05-08 12:30:45
开发里非常常见
十一、TIMESTAMP
也是日期时间
但是：
它和时区有关
并且：
- 占用空间更小
- 自动更新时间
很多项目会这样：
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
表示：
插入数据时自动记录当前时间。
十二、常见字段设计
用户表
CREATE TABLE user(
id BIGINT,
username VARCHAR(30),
password VARCHAR(100),
age TINYINT,
salary DECIMAL(10,2),
create_time DATETIME
);
这里：
| 字段 | 类型原因 |
|---|---|
| id | BIGINT 主键 |
| username | VARCHAR 字符串 |
| password | VARCHAR |
| age | TINYINT 足够 |
| salary | DECIMAL 防止精度问题 |
| create_time | DATETIME |
十三、最常用的数据类型
| 类型 | 用途 |
|---|---|
| INT | 普通整数 |
| BIGINT | 主键ID |
| VARCHAR | 字符串 |
| DECIMAL | 金额 |
| DATETIME | 时间 |
| TEXT | 大文本 |
十四、面试高频问题
1. 金额为什么不用 FLOAT？
因为 FLOAT 存在精度丢失。
金额必须精确。
所以：
DECIMAL
才是标准方案。
2. VARCHAR(20) 的 20 是什么？
表示：
最多存 20 个字符。
不是字节。
3. CHAR 和 VARCHAR 怎么选？
固定长度：
CHAR
普通字符串：
VARCHAR
开发里基本默认：
VARCHAR
MySQL 数据类型主要分三类：
| 类型 | 作用 |
|---|---|
| 数值类型 | 存数字 |
| 字符串类型 | 存文本 |
| 日期类型 | 存时间 |
INT
BIGINT
VARCHAR
DECIMAL
DATETIME
TEXT
数据库常用约束
什么是约束
在 MySQL 里，约束（Constraint）用来限制表中数据的有效性和完整性
约束保证了数据的合法性，防止脏数据进入数据库。
常见约束包括：
- 主键约束（PRIMARY KEY）
- 唯一约束（UNIQUE）
- 非空约束（NOT NULL）
- 默认值（DEFAULT）
- 外键约束（FOREIGN KEY）
- 自动递增（AUTO_INCREMENT）
- 检查约束（CHECK）
一、主键约束 PRIMARY KEY
- 唯一标识表中的每一条记录
- 不允许重复，也不能为空
示例：
CREATE TABLE student(
id INT PRIMARY KEY,
name VARCHAR(20),
age TINYINT
);
特点：
- 每张表通常只有一个主键
- 主键列默认隐含 NOT NULL
二、唯一约束 UNIQUE
- 保证列的值唯一
- 可以有多个唯一约束列
示例：
CREATE TABLE user(
id INT PRIMARY KEY,
username VARCHAR(30) UNIQUE,
email VARCHAR(50) UNIQUE
);
说明：
- username、email 都不允许重复
- 但可以为空（如果不加 NOT NULL）
三、非空约束 NOT NULL
- 不允许字段为 NULL
- 确保每条记录都有值
示例：
CREATE TABLE student(
id INT PRIMARY KEY,
name VARCHAR(20) NOT NULL,
age TINYINT
);
说明：
- name 字段不能为空
- age 可以为空
四、默认值 DEFAULT
- 给字段设置默认值
- 插入数据时如果不写该字段，则使用默认值
示例：
CREATE TABLE student(
id INT PRIMARY KEY,
name VARCHAR(20) NOT NULL,
age TINYINT DEFAULT 18
);
说明：
- age 没有指定值时，自动为 18
五、自动递增 AUTO_INCREMENT
- 常用在主键列
- 每插入一条记录自动加 1
示例：
CREATE TABLE student(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) NOT NULL
);
说明：
- 插入数据时不写 id，系统会自动生成
- 通常配合主键一起使用
六、外键约束 FOREIGN KEY
- 用于保证表之间的数据一致性
- 一张表的字段引用另一张表的主键
示例：
CREATE TABLE class(
id INT PRIMARY KEY,
name VARCHAR(20)
);
CREATE TABLE student(
id INT PRIMARY KEY,
name VARCHAR(20),
class_id INT,
FOREIGN KEY (class_id) REFERENCES class(id)
);
说明：
- student.class_id 必须存在于 class.id
- 防止出现没有对应班级的学生
七、检查约束 CHECK
- 用于限制字段值的范围
- MySQL 8.0+ 才完全支持
示例：
CREATE TABLE student(
id INT PRIMARY KEY,
name VARCHAR(20),
age TINYINT CHECK(age BETWEEN 0 AND 120)
);
说明：
- age 必须在 0~120 之间
- 不符合条件的插入会报错
八、约束总结
| 约束 | 用途 | 常用场景 |
|---|---|---|
| PRIMARY KEY | 主键唯一且不为空 | 表唯一标识 |
| UNIQUE | 保证字段值唯一 | 用户名、邮箱 |
| NOT NULL | 字段不能为空 | 必填字段 |
| DEFAULT | 默认值 | 插入数据时未指定字段 |
| AUTO_INCREMENT | 自动递增 | 主键自增 |
| FOREIGN KEY | 外键约束 | 表关联 |
| CHECK | 数据有效性限制 | 数值范围、枚举等 |
建表常用组合示例
CREATE TABLE user(
id BIGINT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(30) NOT NULL UNIQUE,
password VARCHAR(100) NOT NULL,
email VARCHAR(50) UNIQUE,
age TINYINT DEFAULT 18,
create_time DATETIME DEFAULT CURRENT_TIMESTAMP
);
说明：
- 主键 id 自动递增
- username 不允许为空且唯一
- email 唯一
- age 默认 18
- create_time 默认当前时间
DDL（数据定义）
什么是 DDL
DDL，全称 Data Definition Language（数据定义语言）
创建数据库， 创建表， 修改表结构， 删除数据库/表都是DDL
一、数据库操作
1. 查询数据库
查询当前 MySQL 中所有数据库： 复数
SHOW DATABASES;
查询当前正在使用的数据库：单数
SELECT DATABASE();
2. 创建数据库
语法：
CREATE DATABASE 数据库名;
示例：
CREATE DATABASE school;
指定字符集： default表示默认
CREATE DATABASE school
DEFAULT CHARSET utf8mb4;
现在基本都推荐：
utf8mb4
因为它支持： 中文，emoji，特殊字符
如果数据库已存在，防止报错：
CREATE DATABASE IF NOT EXISTS school;
完整写法：
CREATE DATABASE IF NOT EXISTS school
DEFAULT CHARSET utf8mb4;
3. 删除数据库
语法：
DROP DATABASE 数据库名;
示例：
DROP DATABASE school;
防止数据库不存在报错：
DROP DATABASE IF EXISTS school;
4. 使用数据库
进入某个数据库：
USE school;
之后所有表操作都会在这个数据库下进行
二、表操作
1. 查看当前数据库中的表
复数
SHOW TABLES;
2. 创建表
语法：单数
CREATE TABLE 表名(
字段名 数据类型,
字段名 数据类型
);
示例：
CREATE TABLE student(
id INT,
name VARCHAR(20),
age INT
);
这里：
- id → 整数
- name → 字符串
- age → 整数
3. 查看表结构
查看表字段：
DESC student;
或者：
DESCRIBE student;
查看建表 SQL：
SHOW CREATE TABLE student;
这个特别重要
很多时候：
看别人怎么建表
直接用这个
三、修改表
1. 添加字段
语法：
ALTER TABLE 表名
ADD 字段名 数据类型;
示例：
ALTER TABLE student
ADD gender VARCHAR(10);
2. 修改字段类型
语法：
ALTER TABLE 表名
MODIFY 字段名 新数据类型;
示例：
ALTER TABLE student
MODIFY name VARCHAR(50);
把 name 长度改成 50。
3. 修改字段名
语法：
ALTER TABLE 表名
CHANGE 旧字段名 新字段名 新数据类型;
示例：
ALTER TABLE student
CHANGE name username VARCHAR(50);
注意：
CHANGE 必须重新写数据类型。
4. 删除字段
语法：
ALTER TABLE 表名
DROP 字段名;
示例：
ALTER TABLE student
DROP gender;
5. 修改表名
语法：
RENAME TABLE 旧表名 TO 新表名;
示例：
RENAME TABLE student TO students;
四、删除表
1. 删除表
语法：
DROP TABLE 表名;
示例：
DROP TABLE student;
2. 防止不存在报错
DROP TABLE IF EXISTS student;
五、TRUNCATE（清空表）
语法：
TRUNCATE TABLE 表名;
示例：
TRUNCATE TABLE student;
作用：
- 删除表中所有数据
- 保留表结构
相当于：
DELETE FROM student;
但是：
TRUNCATE 更快。
六、DDL 常见面试题
1. DELETE 和 TRUNCATE 区别
DELETE：
- 属于 DML
- 一行一行删除
- 可以回滚
- 可以加 WHERE
DELETE FROM student WHERE id = 1;
TRUNCATE：
- 属于 DDL
- 直接清空表
- 速度快
- 不能加 WHERE
- 一般不能回滚
TRUNCATE TABLE student;
2. DROP、DELETE、TRUNCATE 区别
DELETE
删除数据：
DELETE FROM student;
保留：
- 表结构
TRUNCATE
清空整个表：
TRUNCATE TABLE student;
保留： 表结构
重置： 自增 ID
DROP
直接删表：
DROP TABLE student;
删除整个表
全部没了
七、DDL 核心总结
DDL 主要就干四件事：
| 操作 | 关键字 |
|---|---|
| 创建 | CREATE |
| 修改 | ALTER |
| 删除 | DROP |
| 清空 | TRUNCATE |
DML
什么是 DML
DML，全称 Data Manipulation Language（数据操作语言）
用来操作表中的数据，包括增、删、改、查。
一、插入数据 INSERT
1. 插入单条记录
语法：
INSERT INTO 表名 (列1, 列2, 列3)
VALUES (值1, 值2, 值3);
示例：
INSERT INTO student (id, name, age)
VALUES (1, '张三', 18);
2. 插入多条记录
语法：
INSERT INTO 表名 (列1, 列2)
VALUES
(值1, 值2),
(值3, 值4),
(值5, 值6);
示例：
INSERT INTO student (id, name)
VALUES
(2, '李四'),
(3, '王五');
3. 插入全部列
如果顺序和表列一致，可以省略列名：
INSERT INTO student
VALUES (4, '赵六', 20);
二、删除数据 DELETE
1. 删除指定条件的记录
语法：
DELETE FROM 表名
WHERE 条件;
示例：
DELETE FROM student
WHERE id = 3;
说明：
- 没有 WHERE 会删除整张表的数据
- DELETE 是 DML，可以回滚
2. 删除全部数据
DELETE FROM student;
或者使用 TRUNCATE（属于 DDL，速度更快）：
TRUNCATE TABLE student;
三、更新数据 UPDATE
1. 更新指定记录
语法：
UPDATE 表名
SET 列1=新值1, 列2=新值2
WHERE 条件;
示例：
UPDATE student
SET age = 19
WHERE id = 1;
说明：
- WHERE 很重要，否则会更新整张表
2. 更新全部记录
UPDATE student
SET age = 18;
四、查询数据 SELECT
1. 查询全部列
SELECT * FROM student;
2. 查询指定列
SELECT name, age FROM student;
3. 条件查询
SELECT * FROM student
WHERE age > 18;
4. 排序查询
SELECT * FROM student
ORDER BY age DESC;
5. 限制查询条数
SELECT * FROM student
LIMIT 5;
6. 聚合函数
SELECT COUNT(*) FROM student;
SELECT AVG(age) FROM student;
SELECT MAX(age) FROM student;
SELECT MIN(age) FROM student;
7. 分组查询
SELECT age, COUNT(*)
FROM student
GROUP BY age;
五、DML 特点
- 操作的是表数据
- 可以回滚（事务支持下）
- 包括增、删、改、查
常用组合示例
-- 插入
INSERT INTO student (name, age) VALUES ('张三', 18);
-- 更新
UPDATE student SET age = 19 WHERE name='张三';
-- 删除
DELETE FROM student WHERE age < 18;
-- 查询
SELECT * FROM student WHERE age >= 18 ORDER BY age DESC LIMIT 10;
掌握这几个操作，日常开发中对数据的增删改查基本够用了。
DQL
这个就到重点了 这个建议直接做牛客SQL篇的基础42题，一边做题一边巩固
| 关键字 | 作用 | 示例 |
|---|---|---|
SELECT | 选列 | SELECT name, age |
AS | 起别名 | SELECT name AS 姓名 |
DISTINCT | 去重 | SELECT DISTINCT university |
FROM | 指定表 | FROM students |
WHERE | 条件筛选 | WHERE age > 20 |
AND / OR / NOT | 逻辑运算 | WHERE age > 20 AND gender = '男' |
IS NULL / IS NOT NULL | 判断空值 | WHERE age IS NOT NULL |
LIKE | 模糊匹配 | WHERE name LIKE '张%' |
REGEXP | 正则匹配 | WHERE phone REGEXP '^1[0-9]{10}$' |
IN | 多值匹配 | WHERE id IN (1, 2, 3) |
BETWEEN | 区间 | WHERE age BETWEEN 20 AND 30 |
ORDER BY | 排序 | ORDER BY age DESC |
LIMIT | 限制条数 | LIMIT 10 |
GROUP BY | 分组 | GROUP BY university |
执行顺序
SELECT 列名 -- 5. 选哪些列
FROM 表名 -- 1. 从哪张表
JOIN 表2 ON 条件 -- 2. 联表
WHERE 条件 -- 3. 筛选行
GROUP BY 分组列 -- 4. 分组
HAVING 分组筛选条件 -- 5. 筛选分组
ORDER BY 排序列 -- 6. 排序
LIMIT 条数; -- 7. 限制条数
正则表达式
就是模糊匹配LIKE的高级进阶版，用来判断一个字符串是否符合某种模式
怎么使用？
WHERE 字段 REGEXP '规则' -- 匹配
WHERE 字段 NOT REGEXP '规则' -- 不匹配
基础语法
1. 单字符匹配
SELECT 'a' REGEXP 'a'; -- true
2. 点号（.）
. 表示任意单个字符
SELECT 'abc' REGEXP 'a.c'; -- true
3. 字符集合 []
[abc] 表示 a 或 b 或 c
SELECT 'a' REGEXP '[abc]'; -- true
SELECT 'd' REGEXP '[abc]'; -- false
4. 范围匹配
[a-z] 小写字母
[A-Z] 大写字母
[0-9] 数字
SELECT 'g' REGEXP '[a-z]'; -- true
5. 取反
[^abc] 表示不是 a、b、c
SELECT 'd' REGEXP '[^abc]'; -- true
定位符
1. ^ 开头
SELECT 'abc' REGEXP '^a'; -- true
2. $ 结尾
SELECT 'abc' REGEXP 'c$'; -- true
3. 完整匹配
^...$ 表示整个字符串必须完全符合规则
SELECT 'abc' REGEXP '^abc$'; -- true
SELECT 'abcd' REGEXP '^abc$'; -- false
重复匹配（核心）
1. *
* 表示前一个字符出现 0 次或多次
SELECT 'aaa' REGEXP 'a*'; -- true
2. +
+ 表示前一个字符至少出现 1 次
SELECT 'a' REGEXP 'a+'; -- true
SELECT '' REGEXP 'a+'; -- false
3. ?
? 表示前一个字符出现 0 次或 1 次
4. {n}
a{3} 表示连续出现 3 次的 a
SELECT 'aaa' REGEXP 'a{3}'; -- true
5. {n,m}
a{2,4} 表示 a 出现 2 到 4 次
SELECT 'aa' REGEXP 'a{2,4}'; -- true
SELECT 'aaa' REGEXP 'a{2,4}'; -- true
SELECT 'aaaa' REGEXP 'a{2,4}'; -- true
SELECT 'a' REGEXP 'a{2,4}'; -- false
看个题就明白了：
SELECT
id,
name,
phone_number
FROM
contacts
WHERE
phone_number REGEXP '^[1-9][0-9]{9}$'
OR phone_number REGEXP '^[1-9][0-9]{2}-[0-9]{3}-[0-9]{4}$'
ORDER BY
id ASC;
解释： 格式1：^[1-9][0-9]{9}$
^ → 从开头开始匹配
[1-9] → 第一位：1-9（不能是0）
[0-9]{9} → 后面9位：0-9
$ → 到这里必须结束
合起来：刚好10位数字，第一位不是0
格式2：^[1-9][0-9]{2}-[0-9]{3}-[0-9]{4}$
^ → 开头
[1-9][0-9]{2} → 3位数字，第一位不是0
- → 一个横杠
[0-9]{3} → 3位数字
- → 一个横杠
[0-9]{4} → 4位数字
$ → 结尾
合起来：3位-3位-4位，第一位不是0
MySQL 多表查询
一、先搞清楚一件事：为什么要多表？
数据库里的数据，不会全放在一张表里
举个最简单的例子：
用户表 user
| id | name |
|---|---|
| 1 | 张三 |
| 2 | 李四 |
订单表 orders
| id | user_id | price |
|---|---|---|
| 1 | 1 | 100 |
| 2 | 1 | 200 |
现在问题来了：
我要查：“张三买了多少钱”
会发现：
- user 表里没有 price
- orders 表里没有 name
所以必须：
把两张表“拼在一起”查
这就是多表查询。
二、最原始的多表查询
先看一个朴素的方法：
SELECT *
FROM user, orders;
结果会是：
两张表乱配对
张三 + 订单1
张三 + 订单2
李四 + 订单1
李四 + 订单2
明显有问题
三、加一个条件
SELECT *
FROM user, orders
WHERE user.id = orders.user_id;
现在就正常了： 只匹配“对得上的”
张三 + 订单1
张三 + 订单2
四、进化版写法：JOIN
推荐用 JOIN：
SELECT *
FROM user
JOIN orders
ON user.id = orders.user_id;
你可以这样理解：
- FROM user → 先拿用户表
- JOIN orders → 把订单表拿过来
- ON → 规定怎么匹配
五、第一种连接：INNER JOIN（默认）
SELECT *
FROM user
INNER JOIN orders
ON user.id = orders.user_id;
解释：
只要“匹配成功”的数据
也就是说：
- 有订单的用户 → 保留
- 没订单的用户 → 直接消失
六、第二种连接：LEFT JOIN（重点）
现在加一个用户：
| id | name |
|---|---|
| 3 | 王五 |
但他没有订单
用 INNER JOIN：
SELECT *
FROM user
JOIN orders
ON user.id = orders.user_id;
王五直接没了 （错误）
用 LEFT JOIN：
SELECT *
FROM user
LEFT JOIN orders
ON user.id = orders.user_id;
结果：
张三 + 订单
李四 + NULL
王五 + NULL
一句话理解：
LEFT JOIN = 左边的表，全都要！
右边没有就补 NULL
七、总结
核心就这两句：
INNER JOIN
只要匹配上的
LEFT JOIN
左表全要，右表没有就 NULL
八、易错：
错误1：不写 ON
JOIN orders
直接炸（不知道怎么匹配）
错误2：字段不加表名
SELECT id
多表里 id 会冲突
正确写法：
SELECT user.id
或者用别名：
SELECT u.id
错误3：LEFT JOIN + WHERE 写错
SELECT *
FROM user u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.price > 100;
这会把 LEFT JOIN 变成 INNER JOIN
因为 NULL 被过滤掉了
九、提问：
如何查没有订单的用户？
SELECT u.*
FROM user u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;
- 先 LEFT JOIN
- 再找 NULL
建立一下“感觉”
写多表查询，脑子里就想：
1. 我要哪几张表？
2. 它们怎么连？（ON）
3. 我要保留谁？（JOIN 还是 LEFT JOIN）
字符串函数 SUBSTRING_INDEX
在 MySQL 里面，字符串处理非常常见 ,这时候就经常会用到：
SUBSTRING_INDEX()
第一次看到这个函数会有点懵
因为它和：
LEFT()
RIGHT()
SUBSTRING()
这种函数不太一样
一、SUBSTRING_INDEX 基本语法
SUBSTRING_INDEX(str, delim, count)
参数解释：
| 参数 | 含义 |
|---|---|
| str | 原字符串 |
| delim | 分隔符 |
| count | 取第几个分隔位置 |
str如果是字符串加'' 如果是列名不加'' count不需要加''
二、最核心的理解
这个函数本质上就一句话：
按照某个分隔符，把字符串切开，然后取其中一部分
重点在于：
count
是正数还是负数
因为：
- 正数 → 从左往右数
- 负数 → 从右往左数
三、count 为正数（从左往右）
先看字符串：
'a,b,c,d'
1.count = 1
SELECT SUBSTRING_INDEX('a,b,c,d', ',', 1);
含义：
从左往右，
找到第1个逗号，
取它前面的内容
结果：
a
2.count = 2
SELECT SUBSTRING_INDEX('a,b,c,d', ',', 2);
含义：
从左往右，
找到第2个逗号，
取它前面的内容
结果：
a,b
3.count = 3
SELECT SUBSTRING_INDEX('a,b,c,d', ',', 3);
结果：
a,b,c
四、count 为负数（从右往左）
负数就是从右边开始数。
1.count = -1
SELECT SUBSTRING_INDEX('a,b,c,d', ',', -1);
含义：
从右往左，
找到第1个逗号，
取它后面的内容
结果：
d
2.count = -2
SELECT SUBSTRING_INDEX('a,b,c,d', ',', -2);
含义：
从右往左，
找到第2个逗号，
取它后面的内容
结果：
c,d
3.count = -3
SELECT SUBSTRING_INDEX('a,b,c,d', ',', -3);
结果：
b,c,d
五、实际开发中的经典用法
1.获取邮箱后缀
比如：
abc@qq.com
想获取：
qq.com
写法：
SELECT SUBSTRING_INDEX(
'abc@qq.com',
'@',
-1
);
结果：
qq.com
2.获取文件后缀
比如：
test.jpg
获取：
jpg
写法：
SELECT SUBSTRING_INDEX(
'test.jpg',
'.',
-1
);
3.获取 IP 最后一段
192.168.1.100
获取：
100
写法：
SELECT SUBSTRING_INDEX(
'192.168.1.100',
'.',
-1
);
4.获取路径最后一级目录
/home/user/mysql
获取：
mysql
写法：
SELECT SUBSTRING_INDEX(
'/home/user/mysql',
'/',
-1
);
六、嵌套使用
这个函数最强的地方：
可以套娃。
比如：
a,b,c,d
想取：
b
怎么办？
第一步
先取前两个：
SELECT SUBSTRING_INDEX(
'a,b,c,d',
',',
2
);
结果：
a,b
第二步
再从右边取一个：
SELECT SUBSTRING_INDEX(
SUBSTRING_INDEX(
'a,b,c,d',
',',
2
),
',',
-1
);
结果：
b
当分隔符不存在
比如：
SELECT SUBSTRING_INDEX(
'abcd',
',',
1
);
因为没有逗号
结果会直接返回：
abcd
SUBSTRING_INDEX 本质就一句话：
按照某个分隔符切割字符串
正数
从左往右取
负数
从右往左取
SQL 窗口函数
普通聚合函数：多行变一行
窗口函数：保留每一行，同时额外计算统计结果
普通聚合 vs 窗口函数
GROUP BY
GROUP BY 会把多行压缩成一行，原来的明细数据会消失
SELECT class, AVG(score)
FROM student
GROUP BY class;
结果：
| class | avg(score) |
|---|---|
| 1班 | 95 |
| 2班 | 91 |
只能看到分组后的结果，看不到每个学生
窗口函数 OVER()
窗口函数不会合并数据
原来的每一行都保留，只是在右边新增统计列
SELECT
name,
class,
score,
AVG(score) OVER(PARTITION BY class) avg_score
FROM student;
结果：
| name | class | score | avg_score |
|---|---|---|---|
| 张三 | 1班 | 98 | 95 |
| 李四 | 1班 | 92 | 95 |
| 王五 | 2班 | 91 | 91 |
基础语法
函数() OVER(
PARTITION BY 字段
ORDER BY 字段
ROWS/RANGE 范围
)
OVER() 里面三部分
PARTITION BY
作用：分区（类似分组）
PARTITION BY class
表示：
按班级切分数据，每个班单独计算
不写的话，默认整个表是一组
ORDER BY
作用：组内排序，窗口函数的ORDER BY相当于写了最后那个参数（范围）从第一行 到 当前行，（ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW）
ORDER BY score DESC
表示：
每个班内部按成绩降序排列
排名、累加都会按照这个顺序进行
ROWS
作用：控制计算范围
常见写法：
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
意思：
从第一行累计到当前行
不写ROWS的话，默认从第一行到最后一行（前提是没在窗口函数GROUP被写的情况下）
排名类窗口函数
准备数据：
| name | class | score |
|---|---|---|
| 张三 | 1班 | 98 |
| 李四 | 1班 | 98 |
| 王五 | 1班 | 90 |
| 赵六 | 2班 | 95 |
| 孙七 | 2班 | 88 |
SQL：
SELECT
name,
class,
score,
ROW_NUMBER() OVER(
PARTITION BY class
ORDER BY score DESC
) rn1,
RANK() OVER(
PARTITION BY class
ORDER BY score DESC
) rn2,
DENSE_RANK() OVER(
PARTITION BY class
ORDER BY score DESC
) rn3
FROM student;
结果：
| name | class | score | rn1 | rn2 | rn3 |
|---|---|---|---|---|---|
| 张三 | 1班 | 98 | 1 | 1 | 1 |
| 李四 | 1班 | 98 | 2 | 1 | 1 |
| 王五 | 1班 | 90 | 3 | 3 | 2 |
| 赵六 | 2班 | 95 | 1 | 1 | 1 |
| 孙七 | 2班 | 88 | 2 | 2 | 2 |
三种排名区别
ROW_NUMBER()
连续编号，不管是否重复
1 2 3 4
即使分数相同，也会强制排不同序号
RANK()
并列排名，会跳号
1 1 3 4
两个第一名，下一名直接第三
DENSE_RANK()
并列排名，但不跳号
1 1 2 3
两个第一名，下一名还是第二
聚合类窗口函数
普通聚合函数都能当窗口函数使用： SUM()，AVG()，MAX()，MIN()，COUNT()
统计班级总分和平均分
SELECT
name,
class,
score,
SUM(score) OVER(
PARTITION BY class
) class_sum,
AVG(score) OVER(
PARTITION BY class
) class_avg
FROM student;
每个学生都还在，同时带出了班级统计数据，不需要子查询，不需要自连接
累加求和
SELECT
name,
score,
SUM(score) OVER(
ORDER BY score
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) running_sum
FROM student;
作用：
从第一行开始，一直累加到当前行
前后行函数
LAG()
取上一行数据
LAG(字段, n)
n 表示往前取几行
LEAD()
取下一行数据。
LEAD(字段, n)
n 表示往后取几行。
LAG 示例
SELECT
dt,
sales,
LAG(sales,1) OVER(
ORDER BY dt
) prev_sales
FROM sales_data;
结果：
| 日期 | 当天销售额 | 前一天销售额 |
|---|---|---|
| 1号 | 100 | null |
| 2号 | 150 | 100 |
| 3号 | 180 | 150 |
GROUP BY
把多行压缩成一行
窗口函数
保留所有原始数据，再额外增加统计列。
PARTITION BY：先分组
ORDER BY：组内排序
OVER()：告诉窗口函数怎么算
GROUP BY：合并行
OVER()：保留行
写几个窗口函数
结果就多几列
MySQL 常见数学函数总结
ABS() 绝对值
返回一个数的绝对值。
SELECT ABS(-10);
结果：
10
CEIL() / CEILING() 向上取整
向上取整。
SELECT CEIL(3.1);
结果：
4
SELECT CEILING(5.01);
结果：
6
FLOOR() 向下取整
向下取整。
SELECT FLOOR(3.9);
结果：
3
ROUND() 四舍五入
保留整数
SELECT ROUND(3.6);
结果：
4
保留小数
SELECT ROUND(3.1415926,2);
结果：
3.14
第二个参数表示保留几位小数。
TRUNCATE() 截断小数
直接截断，不会四舍五入。
SELECT TRUNCATE(3.1415926,2);
结果：
3.14
SELECT TRUNCATE(3.199,1);
结果：
3.1
MOD() 求余
求余数。
SELECT MOD(10,3);
结果：
1
等价于：
SELECT 10 % 3;
RAND() 随机数
生成 0~1 之间随机数。
SELECT RAND();
结果类似：
0.726381
随机获取数据
SELECT *
FROM student
ORDER BY RAND()
LIMIT 3;
随机取 3 条数据。
POW() / POWER() 幂运算
计算次方。
SELECT POW(2,3);
结果：
8
表示：
2³
SQRT() 开平方
SELECT SQRT(16);
结果：
4
SIGN() 判断正负
返回：
- 1：正数
- 0：0
- -1：负数
SELECT SIGN(-20);
结果：
-1
PI() 圆周率
SELECT PI();
结果：
3.141593
GREATEST() 最大值
返回多个值中的最大值。
SELECT GREATEST(10,20,30);
结果：
30
LEAST() 最小值
返回多个值中的最小值。
SELECT LEAST(10,20,30);
结果：
10
EXP() e 的指数
计算：
e^x
SELECT EXP(1);
结果：
2.718282
LOG() 对数
以 e 为底
SELECT LOG(10);
指定底数
SELECT LOG(2,8);
结果：
3
表示：
log₂8
SIN() / COS() / TAN()
三角函数
SELECT SIN(PI()/2);
结果：
1
判断奇偶数
SELECT MOD(id,2)
FROM student;
结果：
- 0：偶数
- 1：奇数
随机抽奖
SELECT *
FROM user
ORDER BY RAND()
LIMIT 1;
最后总结
| 函数 | 作用 |
|---|---|
| ABS() | 绝对值 |
| CEIL() | 向上取整 |
| FLOOR() | 向下取整 |
| ROUND() | 四舍五入 |
| TRUNCATE() | 截断小数 |
| MOD() | 求余 |
| RAND() | 随机数 |
| POW() | 次方 |
| SQRT() | 开平方 |
| SIGN() | 判断正负 |
| PI() | 圆周率 |
| GREATEST() | 最大值 |
| LEAST() | 最小值 |
牛客SQL快速入门42题
SQL1 查询所有列
答：
SELECT*
FROM user_profile
select就是选择哪列，*表示全部，from后面加被选择的表
SQL2 查询多列
答：
SELECT device_id, gender, age, university
FROM user_profile;
列名和列名之间用 英文逗号 , 隔开
最后一个列名后面不加逗号 ，最后一个列名直接跟 FROM
SQL3 查询结果去重
答：
SELECT DISTINCT university
FROM user_profile;
表里"北京大学"出现了两次，但题目只要每个学校出现一次，直接查 university 列会有重复，得用 DISTINCT 去掉重复值，distinct在英文就是截然不同的，清晰的意思，在数据库起去重作用，只需要在列名之前加上distinct就行
DISTINCT 也可以对多列组合去重：
SELECT DISTINCT university, province FROM user_profile;
意思是：学校和省份的组合不重复，比如"北京大学 + Beijing"和"北京大学 + Shanghai"算两条不同的结果
SQL4 查询结果限制返回行数
答：
SELECT device_id
FROM user_profile
LIMIT 2;
LIMIT 2 的意思是只取前2条数据
如果写 LIMIT 5，就取前5条
LIMIT 永远放在 SQL 语句的最后面 LIMIT 还可以配合偏移量
-- LIMIT 偏移量, 数量
SELECT device_id FROM user_profile LIMIT 0, 2; -- 从第1条开始，取2条（同上）
SELECT device_id FROM user_profile LIMIT 2, 2; -- 从第3条开始，取2条
SQL 5 将查询后的列重新命名
SELECT device_id AS user_infos_example
FROM user_profile
LIMIT 2;
给列起别名，关键字：AS AS 就是给列名起一个别名，查出来的数据，列名会显示成 user_infos_example，而不是原来的 device_id
AS 也可以省略不写，直接 SELECT device_id user_infos_example FROM ...，但建议保留 AS，更清晰，不写AS那两个之间没逗号哈，有逗号表示表中的两个列名
-- 没逗号：给 device_id 起别名叫 user_infos_example
SELECT device_id user_infos_example FROM user_profile;
-- 有逗号：查两个列，一个叫 device_id，一个叫 user_infos_example
SELECT device_id, user_infos_example FROM user_profile;
SQL 6 查找后排序
SELECT device_id, age
FROM user_profile
ORDER BY age ASC;
order在英文中就有顺序的意思
ORDER BY age 意思是按 age 这一列排序，ASC 是升序（从小到大），这也是默认的，不写也一样，DESC 是降序（从大到小）
SQL7 查找后多列排序
SELECT device_id, gpa, age
FROM user_profile
ORDER BY gpa ASC, age ASC;
ORDER BY gpa ASC, age ASC：先按 gpa 升序排,当 gpa 相同的时候，再按 age 升序排,如果 gpa 不同，age 的排序就不起作用
多列排序规则:
ORDER BY 列1 排序方式, 列2 排序方式
优先级：列1 > 列2，写在前面的优先级高,只有列1的值相同，才会进入列2的比较
当然还可以先降序再升序:
-- gpa降序，gpa相同时年龄升序
ORDER BY gpa DESC, age ASC;
SQL8 查找后降序排列
SELECT device_id,gpa,age
FROM user_profile
ORDER BY gpa DESC,age DESC;
和上一个题类似
SQL9 查找学校是北大的学生信息
SELECT device_id,university
FROM user_profile
WHERE university="北京大学";
WHERE相当于c语言中的if，还有一定要记得sql中的判断是一个等号=，相当于c语言的==
SQL10 查找年龄大于24岁的用户信息
SELECT device_id,gender,age,university
FROM user_profile
WHERE age>24;
我想想，WHERE需要注意的可能也就是WHERE 必须写在 FROM 后面、ORDER BY 前面
SQL11 查找某个年龄段的用户信息
SELECT device_id,gender,age
FROM user_profile
WHERE age>=20 AND age<=23;
这里用到关键字AND，AND就类似于c里面的&&
| SQL | C 语言 | 意思 |
|---|---|---|
AND | && | 两边条件都满足 |
OR | || | 满足其中一个就行 |
NOT | ! | 取反 |
SQL12 查找除复旦大学的用户信息
SELECT device_id,gender,age,university
FROM user_profile
WHERE university != "复旦大学";
where条件里面写大学不等于复旦大学就可以了
SQL13 用where过滤空值练习
SELECT device_id,gender,age,university
FROM user_profile
WHERE age IS NOT NULL;
这里不能使用！=NULL，必须用IS NOT NULL;
SQL14 高级操作符练习(1)
SELECT device_id,gender,age,university,gpa
FROM user_profile
WHERE gender="male" AND gpa>3.5;
SQL15 高级操作符练习(2）
SELECT device_id,gender,age,university,gpa
FROM user_profile
WHERE university="北京大学" OR gpa>3.7;
使用OR运算符
SQL16 Where in 和Not in
SELECT device_id,gender,age,university,gpa
FROM user_profile
WHERE university="北京大学" OR university="复旦大学" OR university="山东大学";
SQL17 操作符混合运用
SELECT device_id,gender,age,university,gpa
FROM user_profile
WHERE (gpa>3.5 AND university="山东大学")OR(gpa>3.8 AND university="复旦大学");
SQL18 查看学校名称中含北京的用户
SELECT device_id,age,university
FROM user_profile
WHERE university LIKE"%北京%";
LIKE：模糊匹配
%：通配符，代表任意多个字符（包括 0 个）
'%北京%'：包含"北京"的字符串，前后都可以有任意字符
SQL19 查找GPA最高值
SELECT MAX(gpa) AS gpa
FROM user_profile
WHERE university="复旦大学";
聚合函数 MAX() MAX(gpa)：取 gpa 这一列的最大值 ,WHERE university = '复旦大学'：只筛选复旦的学生,AS gpa：给结果列起别名叫 gpa，和题目要求列名一致
常用聚合函数
| 函数 | 作用 |
|---|---|
MAX() | 最大值 |
MIN() | 最小值 |
AVG() | 平均值 |
SUM() | 求和 |
COUNT() | 计数 |
SQL20 计算男生人数以及平均GPA
SELECT
COUNT(*) AS male_num,
ROUND(AVG(gpa),1) AS avg_gpa
FROM user_profile
WHERE gender="male";
注意这个不是从上往下执行的，实际执行顺序是这样的：
第一步：FROM user_profile → 先找到这张表
第二步：WHERE gender = 'male' → 筛出男用户
第三步：SELECT COUNT(*), AVG(gpa) → 对筛完的数据做聚合计算
COUNT(*)就是计算所有的被WHERE条件选中的，然后AS重新起个名字，ROUND的用法是（左边是原始的数值，右边是四舍五入后保留几位数字） 例如：
-- 保留 1 位小数
SELECT ROUND(3.625, 1); → 3.6
-- 保留 2 位小数
SELECT ROUND(3.625, 2); → 3.63
-- 保留 0 位小数（取整）
SELECT ROUND(3.625, 0); → 4
-- 不写第二个参数，默认取整
SELECT ROUND(3.625); → 4
AVG算平均值
重新走一遍这个过程，先从user_profile这个表里面找到满足条件的,gender='male'的，然后走SELECT那一步，先算人数，再算平均值，都重新起个名字
SQL21 分组计算练习题
SELECT
gender,
university,
COUNT(*) AS user_num,
ROUND(AVG(active_days_within_30), 1) AS avg_active_day,
ROUND(AVG(question_cnt), 1) AS avg_question_cnt
FROM
user_profile
GROUP BY
gender,
university
ORDER BY
gender ASC,
university ASC;
执行顺序是先 GROUP BY，再 ORDER BY 这里GROUP的作用就是分组，先分组，看看按照什么分，分组之后SELECT后面的聚合函数计算的都是分组之后的数据
SQL22 分组过滤练习题
SELECT
university,
ROUND(AVG(question_cnt),3) AS avg_question_cnt,
ROUND(AVG(answer_cnt),3) AS avg_answer_cnt
FROM user_profile
GROUP BY university
HAVING AVG(question_cnt)<5 OR AVG(answer_cnt)<20;
对分组后的结果进行筛选，关键字：HAVING
这个是分组后进行筛选，WHRER是分组前筛选
大概步骤如下：① FROM：拿到 7 行 ② GROUP BY university：按学校分成 4 组 ③ SELECT：每组算 AVG(question_cnt)、AVG(answer_cnt) ④ HAVING：筛出平均发帖<5 或 平均回帖<20 的组
SQL23 分组排序练习题
SELECT
university,
ROUND(AVG(question_cnt),4) AS avg_question_cnt
FROM user_profile
GROUP BY university
ORDER BY avg_question_cnt ASC;
执行顺序：分组计算排序
FROM → GROUP BY → SELECT → ORDER BY
↑
别名在这生效，所以 ORDER BY 能用
SQL24 浙江大学用户题目回答情况
SELECT
q.device_id,
q.question_id,
q.result
FROM
question_practice_detail q
JOIN user_profile u ON q.device_id = u.device_id
WHERE
u.university = '浙江大学'
ORDER BY
q.question_id ASC;
先将两个要链接的表起别名简写通过device_id联系在一起，再筛选浙江大学的，最后选出列排序即可
SQL25 统计每个学校的答过题的用户的平均答题数
SELECT
u.university,
ROUND(COUNT(q.question_id)/COUNT(DISTINCT q.device_id),4) AS avg_answer_cnt
FROM user_profile u
JOIN question_practice_detail q ON u.device_id=q.device_id
GROUP BY u.university
ORDER BY u.university ASC;
先将两个表建立联系，按学校分组，分组后用问题数除以人数即可
SQL26 统计每个学校各难度的用户平均刷题数
SELECT
u.university,
qd.difficult_level,
ROUND(COUNT(qp.question_id)/COUNT(DISTINCT qp.device_id),4) AS avg_answer_cnt
FROM user_profile u
JOIN question_practice_detail qp ON u.device_id=qp.device_id
JOIN question_detail qd ON qp.question_id=qd.question_id
GROUP BY u.university,qd.difficult_level
三个表连接，非常简单，和两个表是一样的道理，继续JOIN ON就行了
SQL27 统计每个用户的平均刷题数
SELECT
u.university,
qd.difficult_level,
ROUND(COUNT(qp.question_id)/COUNT(DISTINCT qp.device_id),4) AS avg_answer_cnt
FROM user_profile u
JOIN question_practice_detail qp ON u.device_id=qp.device_id
JOIN question_detail qd ON qd.question_id=qp.question_id
WHERE u.university="山东大学"
GROUP BY qd.difficult_level;
这个和上一题几乎一样，只不过多了个WHERE条件筛选
SQL28 查找山东大学或者性别为男生的信息
-- 先查山东大学
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';
题目的意思是把所有山东大学的输出一遍，再把所有男性的输出一遍，两部分拼在一起，中间不删重复的 这个时候就用到UNION ALL
| 关键字 | 效果 |
|---|---|
UNION ALL | 拼接结果，不去重，全部保留 |
UNION | 拼接结果，去重，重复的只留一次 |
SQL29 计算25岁以上和以下的用户数量
SELECT
CASE
WHEN age < 25
OR age IS NULL THEN '25岁以下'
ELSE '25岁及以上'
END AS age_cut,
COUNT(*) AS number
FROM
user_profile
GROUP BY
age_cut;
这个用到CASE WHEN语法 ：
CASE
WHEN 条件1 THEN '结果1'
WHEN 条件2 THEN '结果2'
ELSE '默认结果'
END
然后这里还有疑问的就是，大家都知道GROUP BY是在SELECT 前面执行的，那么为什么GROUP BY还可以使用SELECT 里面起的别名age_cut，MySQL 特殊处理：MySQL 的 GROUP BY 对别名做了特殊兼容，允许使用 SELECT 里的别名;这是 MySQL 独有的宽松语法，但在其他数据库（如 Oracle，SQL Server，PostgreSQL）里，GROUP BY age_cut 会报错
SQL30 查看不同年龄段的用户明细
SELECT
device_id,
gender,
CASE
WHEN age<20 THEN"20岁以下"
WHEN age IS NULL THEN"其他"
WHEN age >=20 AND age <=24 THEN"20-24岁"
WHEN age >24 THEN"25岁及以上"
END AS age_cut
FROM user_profile;
和上以题逻辑差不多，都是case when
SQL31 计算用户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;
SQL32 计算用户的平均次日留存率
SELECT
ROUND(
SUM(CASE WHEN p2.device_id IS NOT NULL THEN 1 ELSE 0 END) / COUNT(*) ,
4
) AS avg_ret
FROM
(SELECT DISTINCT device_id, date FROM question_practice_detail) p1
LEFT JOIN
(SELECT DISTINCT device_id, date FROM question_practice_detail) p2
ON
p1.device_id = p2.device_id
AND p2.date = DATE_ADD(p1.date, INTERVAL 1 DAY);
SQL33 统计每种性别的人数
SELECT
SUBSTRING_INDEX(profile, ',', -1) AS gender,
COUNT(*) AS number
FROM user_submit
GROUP BY gender;
这里用到：SUBSTRING_INDEX
正数：从左往右数
SUBSTRING_INDEX('a,b,c,d', ',', 1)
从左往右，找到第 1 个逗号，取它前面的内容。
结果：a
SUBSTRING_INDEX('a,b,c,d', ',', 2)
从左往右，找到第 2 个逗号，取它前面的内容。
结果：a,b
SUBSTRING_INDEX('a,b,c,d', ',', 3)
结果：a,b,c
负数：从右往左数
SUBSTRING_INDEX('a,b,c,d', ',', -1)
从右往左，找到第 1 个逗号，取它后面的内容。
结果：d
SUBSTRING_INDEX('a,b,c,d', ',', -2)
从右往左，找到第 2 个逗号，取它后面的内容。
结果：c,d
SUBSTRING_INDEX('a,b,c,d', ',', -3)
结果：b,c,d
SQL34 提取博客URL中的用户名
和上一个题很类似
SQL35 截取出年龄
SELECT
SUBSTRING_INDEX(SUBSTRING_INDEX(profile, ',', -2), ',', 1) AS age,
COUNT(*) AS number
FROM user_submit
GROUP BY age
ORDER BY age DESC;
嵌套 SUBSTRING_INDEX 两步取：
第一步：取最后两段
SUBSTRING_INDEX(profile, ',', -2)
结果：27,male
第二步：取这段的第一段
SUBSTRING_INDEX(上面的结果, ',', 1)
得:27
SQL36 找出每个学校GPA最低的同学
SELECT u.device_id, u.university, u.gpa
FROM user_profile u
JOIN (
SELECT university, MIN(gpa) AS min_gpa
FROM user_profile
GROUP BY university
) t ON u.university = t.university AND u.gpa = t.min_gpa
ORDER BY u.university ASC;
这个是先在内部建立一张表，储存学校和最低的gpa，用"学校相同 且 GPA 等于最低"做关联; 窗口函数，就是在保留原来数据的基础之上可以多建一列数据
或者使用窗口函数：
WITH ranked_gpa AS (
SELECT
device_id,
university,
gpa,
ROW_NUMBER() OVER (PARTITION BY university ORDER BY gpa ASC) AS rn
FROM user_profile
)
SELECT
device_id,
university,
gpa
FROM ranked_gpa
WHERE rn = 1
ORDER BY university ASC;
WITH ranked_gpa AS (...)：定义一个临时表，名叫 ranked_gpa，括号里的 SELECT 就是这个临时表的内容，这个表只在当前 SQL 里有效，执行完就没了
临时表ranked_gpa：
| device_id | university | gpa | rn |
|---|---|---|---|
| 6543 | 北京大学 | 3.2 | 1 |
| 2138 | 北京大学 | 3.4 | 2 |
| 4321 | 复旦大学 | 3.6 | 1 |
| 3214 | 复旦大学 | 4.0 | 2 |
| 2131 | 山东大学 | 3.3 | 1 |
| 5432 | 山东大学 | 3.8 | 2 |
| 2315 | 浙江大学 | 3.6 | 1 |
从这个表里面截取rn=1的就好了
SQL37 统计复旦用户8月练题情况
SELECT
u.device_id,
u.university,
COUNT(q.question_id) AS question_cnt,
SUM(
CASE
WHEN q.result = 'right' THEN 1
ELSE 0
END
) AS right_question_cnt
FROM
user_profile u
LEFT JOIN question_practice_detail q ON u.device_id = q.device_id
AND MONTH(q.date) = 8
WHERE
u.university = '复旦大学'
GROUP BY
u.device_id,
u.university
ORDER BY
u.device_id;
很综合的一道题，题目要求没有回答问题的要记录0，所以不能使用inner join，否则就把没有答题的给删了，此时使用左连接，保留前面那个表的用户，右边如果没有答题就填空，然后计算正确题目数量，sum函数和case when语句结合，then 后面的是返回值，答案正确也就是result=right就返回1，否则返回0，就是啥也不加
SQL38 浙大不同难度题目的正确率
SELECT
d.difficult_level,
SUM(
CASE
WHEN q.result = 'right' THEN 1
ELSE 0
END
) / COUNT(*) AS correct_rate
FROM
user_profile u
JOIN question_practice_detail q ON u.device_id = q.device_id
JOIN question_detail d ON q.question_id = d.question_id
WHERE
u.university = '浙江大学'
GROUP BY
d.difficult_level
ORDER BY
correct_rate ASC;
个是先把三个表连接，选出浙江大学的，按题目难度分组，最后正确率是对的题目除以总题目
SQL39 21年8月份练题总数
SELECT
COUNT(DISTINCT device_id) AS did_cnt,
COUNT(*)AS question_cnt
FROM question_practice_detail
WHERE MONTH(date)=8 AND YEAR(date)=2021;
这个是先选出2021年8月的，然后直接去重算用户，计算总题数
SQL40 电话号码格式校验
SELECT
id,
name,
phone_number
FROM
contacts
WHERE
phone_number REGEXP '^[1-9][0-9]{9}$'
OR phone_number REGEXP '^[1-9][0-9]{2}-[0-9]{3}-[0-9]{4}$'
ORDER BY
id ASC;
解释： 格式1：^[1-9][0-9]{9}$
^ → 从开头开始匹配
[1-9] → 第一位：1-9（不能是0）
[0-9]{9} → 后面9位：0-9
$ → 到这里必须结束
合起来：刚好10位数字，第一位不是0
格式2：^[1-9][0-9]{2}-[0-9]{3}-[0-9]{4}$
^ → 开头
[1-9][0-9]{2} → 3位数字，第一位不是0
- → 一个横杠
[0-9]{3} → 3位数字
- → 一个横杠
[0-9]{4} → 4位数字
$ → 结尾
合起来：3位-3位-4位，第一位不是0
SQL41 计算每日累计利润
SELECT
profit_id,
profit_date,
profit,
SUM(profit) OVER (ORDER BY profit_date ASC) AS cumulative_profit
FROM daily_profits
ORDER BY profit_date ASC;
窗口函数只写个GROUP BY不写ROWS是默认从第一行当当前行的计算
SQL42 基本数学函数
SELECT
id,
value,
ABS(value) AS absolute_value,
CEIL(value) AS ceiling_value,
FLOOR(value) AS floor_value,
ROUND(value, 1) AS rounded_value
FROM numbers
ORDER BY id ASC;
都是常见数学函数
