MySql 整理

MySql核心概念

1. 数据库（Database）：存放多个表的容器（类似文件夹）。
2. 表（Table）：结构化的数据集合（类似 Excel 表），由行（记录）和列（字段）组成。
3. 字段（Column）：表的列，有数据类型（如数字、字符串）和约束（如是否允许为空）

MySQL 查询

create table student
(
	`sid` int primary key auto_increment,
	`name` char(20) not null unique,
	`age` int not null
)
insert into student(`name`,`age`) VALUES ('zs',18),('ls',20),('ww',18);

create table course
(
	`cid` int primary key auto_increment,
	`name` char(20) not null,
	`sid` int not NULL,
	`score` int not null
)
insert into course(`name`,`sid`,`score`) VALUES ('math',1,84),('english',1,52),('math',2,50),('english',2,52),('math',3,77),('english',3,61);

用以上表操作

基础查询（字段与结果处理）

查询所有字段

select * from student;
# 或者用下面的
select student.* from student;

查询选择字段

例如：我只想要查看名字和年龄

select `name`,`age` from student;

字段去重

例如：对年龄去重

select distinct age from student;

补充：
当 distinct 后跟多个字段时，会判断这些字段的组合是否完全重复，只有所有字段值都相同的行才会被去重

设置别名

字段别名
例子：设置name的别名为student_name，age为student_age

select `name`as `student_name`,`age` as `student_age` from student;

表别名
例子：设置表的别名

select s.* from student as s;

条件过滤（WHERE子句）

比较运算符

1. = 与 !=

# 查询学生编号为1的学生
select * from student as s where s.sid = 1;

#学生编号不等于1的所有学生
select * from student as s where s.sid != 1;

2. > 与 >= 与 < 与 =<

#学生年龄大于18岁的所有学生
select * from student as s where s.age > 18;

3. between
   语法结构：between [开始数字] and [结束数字]

#查询编号从1到3的所有学生
select * from student as s where s.sid between 1 and 3;

4. IN

#查询编号为1和2的2个学生
select * from student as s where s.sid IN (1,2);

逻辑运算符

1. and

#查询名字为zs，年龄为18的学生
select * from student as s where s.name = 'zs' and age = 18;

2. or

#查询名字为zs或者年龄为20的学生
select * from student as s where s.name = 'zs' or age = 20;

3. not

#取反查询，查询不是年龄大于等于20的
select * from student as s where not s.age >= 20;

模糊查询

#查询某s的学生
select * from student as s where s.name like '_s';

#查询l什么的学生
select * from student as s where s.name like 'l%';

空值判断

一般用于连表操作清除空数据

#查询名字为null的学生
select * from student as s where s.name is NULL;

由于表中没有名字为空的数据，所有什么都查不出来

补充：
where s.name = NULL 和 where s.name is NULL 不等价

#查询名字不为null的学生
select * from student as s where s.name is not NULL;

排序与分页

排序

单字段

升序操作

#排序，让成绩进行升序
select * from course as c order by c.score ASC

降序操作

#排序，让成绩进行降序
select * from course as c order by c.score desc

多字段

#排序，先按成绩进行降序，如果成绩一样按编号进行升序
select * from course as c order by c.score desc ,c.cid asc

分页

#分页，查前3条数据
select * from course as c limit 3

#分页，从2开始看后面2条数据
select * from course as c limit 2,2

聚合与分组

聚合与分组是 MySQL 中用于数据统计分析的核心功能，通过 GROUP BY 对数据按指定维度分组，再结合聚合函数（如统计数量、求和、平均值等）提炼关键信息

聚合函数：对一组数据进行计算，返回单个结果（如 “总人数”“平均年龄”）
分组（GROUP BY）：将表中的数据按指定字段（如 “部门”“性别”）划分为多个小组，每组内部进行聚合计算

聚合函数

统计

COUNT（计数）、SUM（求和）、AVG（平均）、MAX（最大）、MIN（最小）

#不同的年龄有几种
select count(distinct age) from student;

#查询学生最大年龄
select max(age) from student;

字符串处理

GROUP_CONCAT（分组拼接）

#将所有学生名字组合成一个，按年龄升序,间隔为 -
select GROUP_CONCAT(s.name ORDER BY s.age asc SEPARATOR '-') as '字符串拼接' from student as s;

分组查询

#查询数学最高分
select max(c.score) as '数学最高分' from course as c GROUP BY c.name having c.name = 'math';

根据课程名字分组，只要数学那一组，找最大值

多表连接查询

CREATE TABLE users (
  user_id INT PRIMARY KEY,  -- 用户ID（主键）
  username VARCHAR(50) NOT NULL,  -- 用户名
  age INT  -- 年龄
);

-- 插入数据（包含“有订单”和“无订单”的用户）
INSERT INTO users VALUES
(1, '张三', 25),
(2, '李四', 30),
(3, '王五', 28);  -- 王五还没有下单


CREATE TABLE orders (
  order_id INT PRIMARY KEY,  -- 订单ID（主键）
  user_id INT,  -- 关联用户表的user_id（外键）
  amount DECIMAL(10,2)  -- 订单金额
);

-- 插入数据（张三、李四各有订单，王五无订单）
INSERT INTO orders VALUES
(101, 1, 200.00),  -- 张三的订单1
(102, 1, 300.00),  -- 张三的订单2
(103, 2, 150.00);  -- 李四的订单1

多表连接用以上数据

内连接（INNER JOIN，取两表交集）

select * from users as u INNER JOIN orders as o on u.user_id = o.user_id;

左连接（LEFT JOIN，保留左表所有行）

select * from users as u left JOIN orders as o on u.user_id = o.user_id;

右连接（RIGHT JOIN，保留右表所有行）

select * from users as u right JOIN orders as o on u.user_id = o.user_id;

全连接（通过UNION间接实现，取两表并集）

使用条件

1. 每个 SELECT 语句必须具有相同数量的列。

2. 对应列的数据类型必须相似。

3. 每个 SELECT 语句中的列顺序必须一致。

#将查询出来的2个表数据合并
select * from users as u where u.user_id = 1
union
select * from users as u where u.user_id = 2;

子查询

标量子查询（返回单个值）
结果是单个值，可用于=、>等比较运算符后

列子查询（返回单列多行）
结果是一列多个值，常用IN、NOT IN、ANY、ALL

表子查询（返回多行多列）
结果是一张临时表，可作为FROM后的数据源（需起别名）

相关子查询（依赖外部查询）
子查询的条件依赖外部查询的字段，逐行执行

例子：

select * from student as s WHERE s.name = (select 'zs')

(select ‘zs’) 是一个子查询语句

#查年龄大于平均年龄的学生
SELECT * FROM student WHERE age > (SELECT AVG(age) FROM student); 

函数

日期函数（处理时间）

函数	语法	作用	示例（结果）
NOW()	NOW()	返回当前日期时间（YYYY-MM-DD HH:MM:SS）	NOW() → “2023-10-01 15:30:20”
CURDATE()	CURDATE()	返回当前日期（YYYY-MM-DD）	CURDATE() → “2023-10-01”
CURTIME()	CURTIME()	返回当前时间（HH:MM:SS）	CURTIME() → “15:30:20”
DATE_FORMAT()	DATE_FORMAT(日期, 格式)	按指定格式转换日期为字符串	DATE_FORMAT(‘2023-10-01’, ‘%Y年%m月%d日’) → “2023 年 10 月 01 日”
STR_TO_DATE()	STR_TO_DATE(字符串, 格式)	将字符串按格式转换为日期	STR_TO_DATE(‘2023年10月01日’, ‘%Y年%m月%d日’) → “2023-10-01”
YEAR()/MONTH()	YEAR(日期)/MONTH(日期)	提取年份 / 月份（1-12）	YEAR(‘2023-10-01’) → 2023；MONTH(…) → 10
DAY()	DAY(日期)	提取日（1-31）	DAY(‘2023-10-01’) → 1
DATEDIFF()	DATEDIFF(日期1, 日期2)	计算两个日期的天数差（日期 1 - 日期 2）	DATEDIFF(‘2023-10-05’, ‘2023-10-01’) → 4
DATE_ADD()	DATE_ADD(日期, INTERVAL 数量 单位)	日期加指定时间（单位：DAY/HOUR/MONTH 等）	DATE_ADD(‘2023-10-01’, INTERVAL 3 DAY) → “2023-10-04”
DATE_SUB()	DATE_SUB(日期, INTERVAL 数量 单位)	日期减指定时间	DATE_SUB(‘2023-10-01’, INTERVAL 1 MONTH) → “2023-09-01”

字符串函数（处理文本）

函数	语法	作用	示例（结果）
CONCAT()	CONCAT(str1, str2, …)	拼接多个字符串（任何参数为NULL则返回NULL）	CONCAT(‘用户：’, username) → “用户：张三”
CONCAT_WS()	CONCAT_WS(分隔符, str1, …)	用指定分隔符拼接字符串（忽略NULL参数）	CONCAT_WS(‘-’, ‘2023’, ‘10’, ‘01’) → “2023-10-01”
SUBSTRING()	SUBSTRING(str, 起始位置, 长度)	截取子串（起始位置从 1 开始，长度可选）	SUBSTRING(‘abcdef’, 2, 3) → “bcd”
LENGTH()	LENGTH(str)	返回字符串的字节数（注意：中文在 UTF8 下占 3 字节）	LENGTH(‘张三’) → 6（2 个中文 ×3 字节）
CHAR_LENGTH()	CHAR_LENGTH(str)	返回字符串的字符数（与编码无关）	CHAR_LENGTH(‘张三’) → 2
UPPER()/LOWER()	UPPER(str)/LOWER(str)	转换为大写 / 小写	UPPER(‘abc’) → “ABC”
TRIM()	TRIM([BOTH/LEADING/TRAILING] 字符 FROM str)	去除字符串首尾指定字符（默认去空格）	TRIM(’ abc ') → “abc”；TRIM(‘x’ FROM ‘xxabcxx’) → “abc”
REPLACE()	REPLACE(str, 旧子串, 新子串)	替换字符串中的子串	REPLACE(‘abc123’, ‘123’, ‘456’) → “abc456”

数值函数（处理数字）

函数	语法	作用	示例（结果）
ABS()	ABS(num)	取绝对值	ABS(-10) → 10
ROUND()	ROUND(num, 小数位数)	四舍五入（小数位数默认 0，负数表示整数位）	ROUND(3.1415, 2) → 3.14；ROUND(123.45, -1) → 120
FLOOR()	FLOOR(num)	向下取整（返回小于等于 num 的最大整数）	FLOOR(3.9) → 3；FLOOR(-3.1) → -4
CEIL()	CEIL(num)	向上取整（返回大于等于 num 的最小整数）	CEIL(3.1) → 4；CEIL(-3.9) → -3
MOD()	MOD(num1, num2)	取余数（同 num1 % num2）	MOD(10, 3) → 1
RAND()	RAND()	生成 0~1 之间的随机数（每次调用不同）	RAND() → 0.7832（示例）
FORMAT()	FORMAT(num, 小数位数)	按千分位格式化数字（返回字符串）	FORMAT(12345.67, 2) → “12,345.67”