MySQL的查询
Mysql的查询
- 一、基础查询（单表查询）
- 二、条件查询
- 1. 比较查询
- 2. 逻辑查询
- 3. 范围查询
- 4. 模糊查询
- 5. 空值查询
- 三、排序查询
- 四、聚合查询
- 五、分组查询
- 六、常用函数
- 1. 聚合函数
- 2. 字符串函数
- 长度计算
- 字符串连接
- 数字格式化
- 大小写转换
- 字符串截取
- 字符串替换
- 3. 数值函数
- 4. 日期时间函数
- 七、子查询
- 八、连接查询（多表查询）
- 1. 交叉连接
- 2. 内连接
- 3. 左连接
- 4. 右连接
- 5. 全连接
- 6. 自连接
- 九、高阶查询
- 1. 条件聚合
- 2. 窗口函数
- 排名函数
- 聚合运算
- 总结
一、基础查询（单表查询）
select [distinct] <列名/*> from <表名> [where <条件>] [order by <列名>] [group by 列名]
select 查询
distinct: 去重
from 从哪个表查询
where 条件
order by 排序
group by 分组
- 表product的字段有pid,pname,price,cid
+-----+-----------------+-------+------+
| pid | pname | price | cid |
+-----+-----------------+-------+------+
| 1 | 联想 | 5000 | c001 |
| 2 | 海尔 | 3000 | c001 |
| 3 | 雷神 | 5000 | c001 |
| 4 | 杰克琼斯 | 800 | c002 |
| 5 | 真维斯 | 200 | c002 |
| 6 | 花花公子 | 440 | c002 |
| 7 | 劲霸 | 2000 | c002 |
| 8 | 香奈儿 | 800 | c003 |
| 9 | 相宜本草 | 200 | c003 |
| 10 | 面霸 | 5 | c003 |
| 11 | 好想你枣 | 56 | c004 |
| 12 | 香飘飘奶茶 | 1 | c005 |
| 13 | 海澜之家 | 1 | c002 |
+-----+-----------------+-------+------+
查看所有的商品信息
select * fromproduct;
查看所有商品的商品名和价格
selectpname,pricefromproduct;
查看所有商品的商品名和价格，列名显示为中文 （取别名）
select pname
AS商品名,priceAS商品价格 from product;
select pname 商品名,price 商品价格 from product;
查看所有商品的商品名和价格*10
select pname 商品名,price*10 10倍价格 from product;
查看商品有哪些类别（去重）
select
distinctcid from product;
限制返回数量 limit
limit n => 返回n条数据
select * from product limit 1;
limit n,m => 跳过前n条，输出m条数据
select * from product limit 3,3;
+-----+--------------+-------+------+
| pid | pname | price | cid |
+-----+--------------+-------+------+
| 4 | 杰克琼斯 | 800 | c002 |
| 5 | 真维斯 | 200 | c002 |
| 6 | 花花公子 | 440 | c002 |
+-----+--------------+-------+------+
二、条件查询
通过 WHERE 子句指定条件，从表中提取符合条件的记录
1. 比较查询
<,>,<=,>=,!=/<>, =
select * from product where pname=“真维斯”;
+-----+-----------+-------+------+
| pid | pname | price | cid |
+-----+-----------+-------+------+
| 5 | 真维斯 | 200 | c002 |
+-----+-----------+-------+------+
名字不等于花花公子的数据
select * from product where pname <> “花花公子”;
单价大于1000的商品
select * from product where price > 1000;
2. 逻辑查询
and or not
单价大于等于200，且小于等于1000
select * from product where price >=200 and price <=1000;
pid小于3或大于10
select * from product where pid<3 or pid >10;
查询分类不等于c004的商品信息
select * from product where not (cid=‘c004’);
3. 范围查询
in 、 between and
单价大于等于200，且小于等于1000
select * from product where price between 200 and 1000;
cid=c001或c003
select * from product where cid in (‘c001’, ‘c003’);
select * from product where cid = ‘c001’ or cid=‘c003’;
not in、not between...and
select * from product where cid not in (‘c001’, ‘c003’);
select * from product where price not between 200 and 1000;
4. 模糊查询
Like (字符串)
% 匹配任意个字符
_ 匹配单个任意字符
select * from product where pname like ‘%家%’;
_ 匹配一个字符
select * from product where pname like ‘海_家’;
- 正则匹配
regexp 或 rlike
默认只要包含即可
select * from product where pname regexp ‘家’;
^ => 以…开头
select * from product where pname rlike ‘^海’;
$ => 以…结尾
select * from product where pname rlike ‘家$’;
5. 空值查询
select * from articles
where id is NULL;
select * from articleswhere id is not NULL;
三、排序查询
order by
默认升序
select * from product order by price;
降序排列
select * from product
order bypricedesc;
多字段排序
select * from product order by cid desc, price desc;
筛选再排序
select * from product where pid between 1 and 10 order by cid desc, price desc;
四、聚合查询
将多条记录聚合到一起计算出一条计算
- 计数、求和、求平均
count(字段名)： 统计值不为NULL的数量
select count(*) from product;
select count(pid) from product;
sum(字段)： 计算数字的和
select sum(price) from product;
max(字段)，max: 求最大值,最小值
select max(price),min(price) from product;
avg(字段)：求平均值
select avg(price) from product;
五、分组查询
…where 条件 group by 列名 [having 条件表达式（having对分组的结果再操作)]
查看每个不同类别的平均值
select list 只能包含group by的字段或聚合函数
select cid,avg(price) from product group by cid;
+------+-------------------+
| cid | avg(price) |
+------+-------------------+
| c001 | 4333.333333333333 |
| c002 | 688.2 |
| c003 | 335 |
| c004 | 56 |
| c005 | 1 |
+------+-------------------+
select cid,price,avg(price) from product group by cid,price;
select cid,price,count(pid) from product group by cid,price;
查找商品类别数量大于等于3的分类
select cid,count(pid) cid_count from product
group bycidhavingcid_count >=3;
+------+-----------+
| cid | cid_count |
+------+-----------+
| c001 | 3 |
| c002 | 5 |
| c003 | 3 |
+------+-----------+
六、常用函数
1. 聚合函数
将多条数据聚合到一起计算
- count 不计NULL
- sum
- max
- min
- avg
2. 字符串函数
长度计算
length => 字符存储消耗的空间
char_length => 统计字符串长度，字符个数
select pname, length(pname), char_length(pname) from product order by pname desc limit 3;
+-----------------+---------------+--------------------+
| pname | length(pname) | char_length(pname) |
+-----------------+---------------+--------------------+
| 香飘飘奶茶 | 15 | 5 |
| 香奈儿 | 9 | 3 |
| 面霸 | 6 | 2 |
+-----------------+---------------+--------------------+
字符串连接
CONCAT：连接
CONCAT_WS：使用指定的分隔符连接（每个字段）
select concat(cid,‘-’ ,pname) from product;
select concat_ws(‘-’,cid,pname,‘a’,‘b’,‘c’) from product;
数字格式化
FORMAT
root@test 17: 13>select format(3.1415, 3);
+-------------------+
| format(3.1415, 3) |
+-------------------+
| 3.142 |
+-------------------+
大小写转换
LOWER: 转小写
UPPER转大小
select lower(‘Hello’), upper(‘Hello’);
+----------------+----------------+
| lower('Hello') | upper('Hello') |
+----------------+----------------+
| hello | HELLO |
+----------------+----------------+
字符串截取
LEFT: 从左取字符串
RIGHT：从右取字符串
select pname,
left(pname,2), right(pname,2) from product;
SUBSTRING: 取子串
substring(string, 起始位置，长度)
select substring(pname,2,1) from product;
LTRIM: 删除前导空格（左侧）
RTRIM: 删除后续空格（右侧）
TRIM: 删除指定前导符或后续符
root@test 17: 17>select length(’ Mysql ‘),length(
ltrim(’ Mysql ‘)), length(rtrim(’ Mysql '));
+--------------------+---------------------------+---------------------------+
| length(' Mysql ') | length(ltrim(' Mysql ')) | length(rtrim(' Mysql ')) |
+--------------------+---------------------------+---------------------------+
| 8 | 6 | 7 |
+--------------------+---------------------------+---------------------------+
删除左边指定符号
root@test 17: 18>select ‘–Mysql–’,
trim(LEADING '-' FROM‘–Mysql–’);
+-----------+------------------------------------+
| --Mysql-- | trim(LEADING '-' FROM '--Mysql--') |
+-----------+------------------------------------+
| --Mysql-- | Mysql-- |
+-----------+------------------------------------+
1 row in set (0.00 sec)
删除右边指定符号
root@test 17: 21>select ‘–Mysql–’,
trim(TRAILING '-' FROM‘–Mysql–’);
删除两边指定符号
root@test 17: 21>select ‘–Mysql–’,
trim(BOTH '-' FROM‘–Mysql–’);
字符串替换
REPLACE
select
replace(pname, '花','hua')from product;
3. 数值函数
CEIL: 向上取整
FLOOR: 向下取整
SELECT 3.1415, CEIL(3.1415), FLOOR(3.1415);
+--------+--------------+---------------+
| 3.1415 | CEIL(3.1415) | FLOOR(3.1415) |
+--------+--------------+---------------+
| 3.1415 | 4 | 3 |
+--------+--------------+---------------+
DIV : 取整除
MOD: 取余
root@test 17: 29>SELECT 5/2, 5 DIV 2, 5 MOD 2;
+--------+---------+---------+
| 5/2 | 5 DIV 2 | 5 MOD 2 |
+--------+---------+---------+
| 2.5000 | 2 | 1 |
+--------+---------+---------+
ROUND: 四舍五入
POWER: 冪运算
root@test 17: 30>SELECT POWER(2,10),ROUND(3.1425, 3);
+-------------+------------------+
| POWER(2,10) | ROUND(3.1425, 3) |
+-------------+------------------+
| 1024 | 3.143 |
+-------------+------------------+
4. 日期时间函数
NOW： 获取当前日期时间
CRUDATE： 获取当前日期
CURTIME ：获取当前时间
select now(), curdate(), curtime();
+---------------------+------------+-----------+
| now() | curdate() | curtime() |
+---------------------+------------+-----------+
| 2025-07-04 17:34:18 | 2025-07-04 | 17:34:18 |
+---------------------+------------+-----------+
YEAR(date)：提取年份
MONTH(date)：提取月份
DAY(date)：提取日
select YEAR(now()),month(now()),day(now());
+-------------+--------------+------------+
| YEAR(now()) | month(now()) | day(now()) |
+-------------+--------------+------------+
| 2025 | 7 | 4 |
+-------------+--------------+------------+
DATE_ADD： 日期操作（计算）
root@test 17: 35>select date_add(curdate(), interval 10 day);
+--------------------------------------+
| date_add(curdate(), interval 10 day) |
+--------------------------------------+
| 2025-07-14 |
+--------------------------------------+
1 row in set (0.00 sec)
root@test 17: 35>select date_add(curdate(), interval -10 day);
root@test 17: 36>select date_add(curdate(), interval 2 year);
root@test 17: 36>select date_add(curdate(), interval 1 week);
DATEDIFF 日期差值
select datediff(‘2025-1-1’, ‘2026-3-4’);
DATE_FORMATE 日期格式化
select date_format(now(), ‘%Y/%m/%d’);
七、子查询
一条select查询语句的结果作为另一条select语句的一部分
select * from (select * from table)
select * from table where id in (select id from table)
子查询的特点：
- 子查询必须放在小括号中
- 子查询是可以独立存在的语句
- 子查询一般有两个位置，充当数据源(表)，或充当条件
创建商品表
CREATE TABLE product
(
pid INT PRIMARY KEY,
pname VARCHAR(20) NOT NULL,
price DOUBLE,
cid CHAR(4)
);
创建商品信息表
CREATE TABLE category
(
cid CHAR(4) PRIMARY KEY,
cname VARCHAR(10)
);
表的数据，后续操作基于此表
root@test 22: 39>select * from product;
+-----+-----------------+-------+------+
| pid | pname | price | cid |
+-----+-----------------+-------+------+
| 1 | 联想 | 5000 | c001 |
| 2 | 海尔 | 3000 | c001 |
| 3 | 雷神 | 5000 | c001 |
| 4 | 杰克琼斯 | 800 | c002 |
| 5 | 真维斯 | 200 | c002 |
| 6 | 花花公子 | 440 | c002 |
| 7 | 劲霸 | 2000 | c002 |
| 8 | 香奈儿 | 800 | c003 |
| 9 | 相宜本草 | 200 | c003 |
| 10 | 面霸 | 5 | c003 |
| 11 | 好想你枣 | 56 | c004 |
| 12 | 香飘飘奶茶 | 1 | c005 |
| 13 | 海澜之家 | 1 | c002 |
+-----+-----------------+-------+------+root@test 22: 40>select * from category;
+------+-----------+
| cid | cname |
+------+-----------+
| c001 | 家电 |
| c002 | 鞋服 |
| c003 | 化妆品 |
| c004 | 箱包 |
| c005 | 食品 |
+------+-----------+
1. 通过子查询的方式，查询出价格最高的商品信息
a. 找出最高的单价
select max(price) from product;
b. 找到价格最高的商品信息
select * from product where price = (select max(price) from product);
2. 查询`化妆品`分类下的商品名称、商品价格
a. 找到`化妆品`的cid
select cid from category where cname=‘化妆品’;
b. 根据cid查询商品信息
select pname,price from product where cid = (select cid from category where cname=‘化妆品’);
3. 查询小于平均价格的商品信息
a. 找到平均价格
select avg(price) from product;
b. 找到商品
select * from product where price < (select avg(price) from product);
4. 查询价格小于2000的商品来自哪些分类（分类名称（化妆品…））
分类名 -> 分类cid -> product
a. 找到小于2000的商品的cid
select distinct cid from product where price <2000;
b. 根据查询的cid找到对应的分类名
select cname from category where cid in (select distinct cid from product where price <2000);
5. 查询`家电`和`鞋服`下的全部商品信息
a. 查询`家电`和`鞋服`的cid
select cid from category where cname=‘家电’ or cname =‘鞋服’;
b. 查询对应cid的商品
select * from product where cid in (select cid from category where cname=‘家电’ or cname =‘鞋服’);
将子查询的结果作为一张表
select 查询字段 from (子查询) as 别名 where 条件
1. 查询商品类别均价大于200的所有商品信息
a. 查询商品类别均价，找到均价大于200的分类
select cid, avg(price) p_avg from product group by cid having p_avg >200;
b. cid列表
select cid from (select cid, avg(price) p_avg from product group by cid having p_avg >200) t1;
c. 从product表中找到对应分类的商品信息
select * from product where cid in (select cid from (select cid, avg(price) p_avg from product group by cid having p_avg >200) t1);
等效写法
select * from product where cid in (select cid from product group by cid having avg(price)>200);
八、连接查询（多表查询）
多表查询需要表与表之间有紧密关联的
创建学生表
root@test 14: 45>select * from student;
+----+--------+------+----------+
| id | name | age | class_id |
+----+--------+------+----------+
| 1 | 张三 | 18 | 1 |
| 2 | 李四 | 19 | 2 |
| 3 | 王五 | 20 | 1 |
| 4 | 赵六 | 18 | 2 |
| 5 | 马七 | 20 | 4 |
+----+--------+------+----------+
创建班级表
root@test 14: 45>select * from class;
+----+--------+-----------+
| id | name | teacher |
+----+--------+-----------+
| 1 | 一班 | 张老师 |
| 2 | 二班 | 李老师 |
| 3 | 三班 | 刘老师 |
+----+--------+-----------+
1. 交叉连接
select 列名 from 表1, 表2 where ....
select 列名 from 表1 cross join 表2
会生成所有可能的行组合，也被称为笛卡尔积
root@test 14: 45>select * from class,student;
+----+--------+-----------+----+--------+------+----------+
| id | name | teacher | id | name | age | class_id |
+----+--------+-----------+----+--------+------+----------+
| 3 | 三班 | 刘老师 | 1 | 张三 | 18 | 1 |
| 2 | 二班 | 李老师 | 1 | 张三 | 18 | 1 |
| 1 | 一班 | 张老师 | 1 | 张三 | 18 | 1 |
| 3 | 三班 | 刘老师 | 2 | 李四 | 19 | 2 |
| 2 | 二班 | 李老师 | 2 | 李四 | 19 | 2 |
| 1 | 一班 | 张老师 | 2 | 李四 | 19 | 2 |
| 3 | 三班 | 刘老师 | 3 | 王五 | 20 | 1 |
| 2 | 二班 | 李老师 | 3 | 王五 | 20 | 1 |
| 1 | 一班 | 张老师 | 3 | 王五 | 20 | 1 |
| 3 | 三班 | 刘老师 | 4 | 赵六 | 18 | 2 |
| 2 | 二班 | 李老师 | 4 | 赵六 | 18 | 2 |
| 1 | 一班 | 张老师 | 4 | 赵六 | 18 | 2 |
| 3 | 三班 | 刘老师 | 5 | 马七 | 20 | 4 |
| 2 | 二班 | 李老师 | 5 | 马七 | 20 | 4 |
| 1 | 一班 | 张老师 | 5 | 马七 | 20 | 4 |
+----+--------+-----------+----+--------+------+----------+
root@test 14: 51>select * from class,student where class.id=student.class_id;
+----+--------+-----------+----+--------+------+----------+
| id | name | teacher | id | name | age | class_id |
+----+--------+-----------+----+--------+------+----------+
| 1 | 一班 | 张老师 | 1 | 张三 | 18 | 1 |
| 2 | 二班 | 李老师 | 2 | 李四 | 19 | 2 |
| 1 | 一班 | 张老师 | 3 | 王五 | 20 | 1 |
| 2 | 二班 | 李老师 | 4 | 赵六 | 18 | 2 |
+----+--------+-----------+----+--------+------+----------+
2. 内连接
INNER JOIN
返回两个表中满足条件的行的组合
select 列名 from 表1 inner join 表2 on 连接条件;
root@test 14: 55>select * from student inner join class on student.class_id=class.id;
+----+--------+------+----------+----+--------+-----------+
| id | name | age | class_id | id | name | teacher |
+----+--------+------+----------+----+--------+-----------+
| 1 | 张三 | 18 | 1 | 1 | 一班 | 张老师 |
| 2 | 李四 | 19 | 2 | 2 | 二班 | 李老师 |
| 3 | 王五 | 20 | 1 | 1 | 一班 | 张老师 |
| 4 | 赵六 | 18 | 2 | 2 | 二班 | 李老师 |
+----+--------+------+----------+----+--------+-----------+
隐式内连接
root@test 14: 58>select * from class,student where student.class_id=class.id;
3. 左连接
LEFT JOIN
将左表的数据全部查询出来
select * from student left join class on student.class_id = class.id;
+----+--------+------+----------+------+--------+-----------+
| id | name | age | class_id | id | name | teacher |
+----+--------+------+----------+------+--------+-----------+
| 1 | 张三 | 18 | 1 | 1 | 一班 | 张老师 |
| 2 | 李四 | 19 | 2 | 2 | 二班 | 李老师 |
| 3 | 王五 | 20 | 1 | 1 | 一班 | 张老师 |
| 4 | 赵六 | 18 | 2 | 2 | 二班 | 李老师 |
| 5 | 马七 | 20 | 4 | NULL | NULL | NULL |
+----+--------+------+----------+------+--------+-----------+
4. 右连接
RIGHT JOIN
将右表的数据全部查询出来
select * from student right join class on student.class_id = class.id;
+------+--------+------+----------+----+--------+-----------+
| id | name | age | class_id | id | name | teacher |
+------+--------+------+----------+----+--------+-----------+
| 3 | 王五 | 20 | 1 | 1 | 一班 | 张老师 |
| 1 | 张三 | 18 | 1 | 1 | 一班 | 张老师 |
| 4 | 赵六 | 18 | 2 | 2 | 二班 | 李老师 |
| 2 | 李四 | 19 | 2 | 2 | 二班 | 李老师 |
| NULL | NULL | NULL | NULL | 3 | 三班 | 刘老师 |
+------+--------+------+----------+----+--------+-----------+
5. 全连接
UNION
左表和右表中的数据全部展示
root@test 15: 14>select * from student left join class on student.class_id = class.id
-> union
-> select * from student right join class on student.class_id = class.id;
+------+--------+------+----------+------+--------+-----------+
| id | name | age | class_id | id | name | teacher |
+------+--------+------+----------+------+--------+-----------+
| 1 | 张三 | 18 | 1 | 1 | 一班 | 张老师 |
| 2 | 李四 | 19 | 2 | 2 | 二班 | 李老师 |
| 3 | 王五 | 20 | 1 | 1 | 一班 | 张老师 |
| 4 | 赵六 | 18 | 2 | 2 | 二班 | 李老师 |
| 5 | 马七 | 20 | 4 | NULL | NULL | NULL |
| NULL | NULL | NULL | NULL | 3 | 三班 | 刘老师 |
+------+--------+------+----------+------+--------+-----------+
6. 自连接
将一个表与它自身进行连接
创建菜单表
root@test 14: 45>select * from menu;
+----+-----------+--------+
| id | label | parent |
+----+-----------+--------+
| 1 | 主页 | 0 |
| 2 | 服务 | 0 |
| 3 | 关于 | 0 |
| 4 | 查单词 | 2 |
| 5 | 查成绩 | 2 |
| 6 | 测试 | 3 |
+----+-----------+--------+
root@test 15: 13>select * from
menu as a,menu as bwhere a.id=b.parent;
+----+--------+--------+----+-----------+--------+
| id | label | parent | id | label | parent |
+----+--------+--------+----+-----------+--------+
| 2 | 服务 | 0 | 4 | 查单词 | 2 |
| 2 | 服务 | 0 | 5 | 查成绩 | 2 |
| 3 | 关于 | 0 | 6 | 测试 | 3 |
+----+--------+--------+----+-----------+--------+
九、高阶查询
1. 条件聚合
case when -> 加标签/字段
是一个多分支的函数，可以根据条件列表返回多个值
case 表达式
when 表达式1 then 结果表达式
when 表达式2 then 结果表达式
when 表达式3 then 结果表达式
[else 结果表达式]
end as 新字段名
从上到下测试比较，返回第一个匹配的结果，如果没有匹配上返回NULL
创建成绩表
root@test 15: 58>select * from scores;
+--------+--------+--------+--------+--------+--------------+-----------+--------+
| 学号 | 姓名 | 性别 | 班号 | 系号 | 班主任号 | 课程号 | 成绩 |
+--------+--------+--------+--------+--------+--------------+-----------+--------+
| s01 | 张三 | 男 | 1 | 1 | 1 | M01F013 | 80 |
| s02 | 李四 | 女 | 1 | 3 | 1 | M01F011 | 60 |
| s03 | 王五 | 男 | 2 | 2 | 1 | M01F012 | 50 |
| s04 | 马六 | 男 | 1 | 1 | 1 | M01F011 | 80 |
| s05 | 刘七 | 女 | 1 | 2 | 1 | M01F011 | 80 |
| s06 | 朱八 | 男 | 2 | 3 | 1 | M01F012 | 80 |
| s07 | 赵一 | 女 | 2 | 1 | 1 | M01F011 | 70 |
| s08 | 钱二 | 男 | 2 | 3 | 1 | M01F011 | 80 |
| s09 | 孙小 | 女 | 1 | 2 | 1 | M01F012 | 90 |
| s10 | 冯十 | 男 | 2 | 1 | 1 | M01F013 | 80 |
+--------+--------+--------+--------+--------+--------------+-----------+--------+
10 rows in set (0.01 sec)
查询scores信息，将系号变成字段(学号，姓名，性别，系号)
1 => 计算机
2 => 软件工程
3 => 物联网
root@test 15: 59>select 学号,姓名,性别,
-> case 系号
-> when 1 then “计算机”
-> when 2 then “软件工程”
-> when 3 then “物联网”
-> else “其他”
-> end 系号 from scores;
+--------+--------+--------+--------------+
| 学号 | 姓名 | 性别 | 系号 |
+--------+--------+--------+--------------+
| s01 | 张三 | 男 | 计算机 |
| s02 | 李四 | 女 | 物联网 |
| s03 | 王五 | 男 | 软件工程 |
| s04 | 马六 | 男 | 计算机 |
| s05 | 刘七 | 女 | 软件工程 |
| s06 | 朱八 | 男 | 物联网 |
| s07 | 赵一 | 女 | 计算机 |
| s08 | 钱二 | 男 | 物联网 |
| s09 | 孙小 | 女 | 软件工程 |
| s10 | 冯十 | 男 | 计算机 |
+--------+--------+--------+--------------+
定义成绩等级
>=90 => 优
80-89 => 良
70-79 => 中
60-69 => 及格
<60 不及格
root@test 16: 12>select 学号,姓名,课程号,
-> case
-> when 成绩 >=90 then “优”
-> when 成绩 between 80 and 89 then “良”
-> when 成绩 between 70 and 79 then “中”
-> when 成绩 between 60 and 69 then “及格”
-> when 成绩<60 then “不及格”
-> end 等级
-> from scores;
+--------+--------+-----------+-----------+
| 学号 | 姓名 | 课程号 | 等级 |
+--------+--------+-----------+-----------+
| s01 | 张三 | M01F013 | 良 |
| s02 | 李四 | M01F011 | 及格 |
| s03 | 王五 | M01F012 | 不及格 |
| s04 | 马六 | M01F011 | 良 |
| s05 | 刘七 | M01F011 | 良 |
| s06 | 朱八 | M01F012 | 良 |
| s07 | 赵一 | M01F011 | 中 |
| s08 | 钱二 | M01F011 | 良 |
| s09 | 孙小 | M01F012 | 优 |
| s10 | 冯十 | M01F013 | 良 |
+--------+--------+-----------+-----------+
将成绩用等级制表示,统计每个等级人数
root@test 16: 23>select
-> case
-> when 成绩 >=90 then “优”
-> when 成绩 between 80 and 89 then “良”
-> when 成绩 between 70 and 79 then “中”
-> when 成绩 between 60 and 69 then “及格”
-> when 成绩<60 then “不及格”
-> end 等级,count(*) 人数
-> from scores
-> group by 等级;
+-----------+--------+
| 等级 | 人数 |
+-----------+--------+
| 良 | 6 |
| 及格 | 1 |
| 不及格 | 1 |
| 中 | 1 |
| 优 | 1 |
+-----------+--------+
统计每班男生、女生数量
root@test 16: 33>select 班号, count(case when 性别=“男” then “xxx” end) 男生数, count(case when 性别=“女” then “xxx” end) 女生数 from scores group by 班号;
+--------+-----------+-----------+
| 班号 | 男生数 | 女生数 |
+--------+-----------+-----------+
| 1 | 2 | 3 |
| 2 | 4 | 1 |
+--------+-----------+-----------+
2. 窗口函数
窗口函数是一种在查询结果集上进行计算的一个函数
可以不改变查询结果，为每行添加（排名或聚合信息）
<窗口函数> over (partition by <分组列> order by <排序列>) as 别名
partition by和order by都是可选项
尽量使用别名，直接使用会导致结果列名冗长且难以理解
排名函数
rank(): 排名，如果有值相同，会出现排名空缺 (1,1,3)
dense_rank(): 排名，如果有值相同，不会出现排名空缺 (1,1,2)
row_number(): 排名，如果有值相同，数值连续（1,2,3）
将学生成绩进行排名
- rank()
root@test 17: 13>select 班号,学号,姓名,成绩, rank() over (partition by 班号 order by 成绩 desc) 排名 from scores;
+--------+--------+--------+--------+--------+
| 班号 | 学号 | 姓名 | 成绩 | 排名 |
+--------+--------+--------+--------+--------+
| 1 | s09 | 孙小 | 90 | 1 |
| 1 | s01 | 张三 | 80 | 2 |
| 1 | s04 | 马六 | 80 | 2 |
| 1 | s05 | 刘七 | 80 | 2 |
| 1 | s02 | 李四 | 60 | 5 |
| 2 | s06 | 朱八 | 80 | 1 |
| 2 | s08 | 钱二 | 80 | 1 |
| 2 | s10 | 冯十 | 80 | 1 |
| 2 | s07 | 赵一 | 70 | 4 |
| 2 | s03 | 王五 | 50 | 5 |
+--------+--------+--------+--------+--------+
- dense_rank()
select 学号,姓名,成绩, dense_rank() over (order by 成绩 desc) 排名 from scores;
- row_number()
select 学号,姓名,成绩, row_number() over (order by 成绩 desc) 排名 from scores;
聚合运算
AVG()
SUM()
MAX()
MIN()
展示所有学生信息并展示每班平均分
root@test 17: 21>select 班号,学号,姓名,成绩, avg(成绩) over (partition by 班号) as 班级平均分 from scores;
+--------+--------+--------+--------+-----------------+
| 班号 | 学号 | 姓名 | 成绩 | 班级平均分 |
+--------+--------+--------+--------+-----------------+
| 1 | s01 | 张三 | 80 | 78 |
| 1 | s02 | 李四 | 60 | 78 |
| 1 | s04 | 马六 | 80 | 78 |
| 1 | s05 | 刘七 | 80 | 78 |
| 1 | s09 | 孙小 | 90 | 78 |
| 2 | s03 | 王五 | 50 | 72 |
| 2 | s06 | 朱八 | 80 | 72 |
| 2 | s07 | 赵一 | 70 | 72 |
| 2 | s08 | 钱二 | 80 | 72 |
| 2 | s10 | 冯十 | 80 | 72 |
+--------+--------+--------+--------+-----------------+
总结
MySQL 查询的核心作用是从数据库中高效、精准地获取、处理、分析数据，既是数据交互的基础工具，也是支撑业务逻辑、决策分析的关键手段。
其灵活性（支持简单到复杂的各类场景）和高效性（通过索引等优化）使其成为数据库操作中不可或缺的部分