数据库mysql 一文知识大总结（详细）

Honkers · 2026-5-11 17:45:12

什么是数据库：

数据库 = 用来存数据的地方 + 一套管理这些数据的系统

可以永久储存数据

常见的数据库软件，比如 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;

复制代码

都是常见数学函数

[数据库] 数据库mysql 一文知识大总结（详细）

什么是数据库：

数据库的结构

简单操作一下

进入mysql

开启后进入 法1：

法2：

总结一下最基础的操作代码

登录和退出

数据库操作

表操作

日常操作就是这几个步骤：

sql语法基础

下面系统性学习一下数据库

数据类型

一、数值类型

二、整数类型

1. TINYINT

2. INT

3. BIGINT

三、小数类型

1. FLOAT

2. DOUBLE

3. DECIMAL（重点）

四、字符串类型

五、CHAR 和 VARCHAR

1. CHAR

2. VARCHAR（重点）

3. CHAR 和 VARCHAR 区别

六、TEXT 类型

七、日期时间类型

八、DATE

九、TIME

十、DATETIME（重点）

十一、TIMESTAMP

十二、常见字段设计

用户表

十三、最常用的数据类型

十四、面试高频问题

1. 金额为什么不用 FLOAT？

2. VARCHAR(20) 的 20 是什么？

3. CHAR 和 VARCHAR 怎么选？

数据库常用约束

什么是约束

一、主键约束 PRIMARY KEY

二、唯一约束 UNIQUE

三、非空约束 NOT NULL

四、默认值 DEFAULT

五、自动递增 AUTO_INCREMENT

六、外键约束 FOREIGN KEY

七、检查约束 CHECK

八、约束总结

建表常用组合示例

DDL（数据定义）

什么是 DDL

一、数据库操作

1. 查询数据库

2. 创建数据库

3. 删除数据库

4. 使用数据库

二、表操作

1. 查看当前数据库中的表

2. 创建表

3. 查看表结构

三、修改表

1. 添加字段

2. 修改字段类型

3. 修改字段名

4. 删除字段

5. 修改表名

四、删除表

1. 删除表

2. 防止不存在报错

五、TRUNCATE（清空表）

六、DDL 常见面试题

1. DELETE 和 TRUNCATE 区别

2. DROP、DELETE、TRUNCATE 区别

DELETE

TRUNCATE

DROP

开启后进入法1：