数据库单表查询
SELECT [DISTINCT] 列1,列2,列3……(*) FROM 表名 WHERE 条件 ORDER BY 排序列 GROUP BY 分组[列名]
基本查询
# 设置别名展示在查询结果的列值
SELECT pname 商品名,price 商品价格, price*0.9 九折价格 FROM product ORDER BY price;
比较查询
<, >, =, >=，<=, !=
SELECT * FROM product WHERE price>300;
逻辑查询
AND OR NOT
# 查询某满足多个要求的数据 --AND
SELECT * FROM product WHERE price > 300 AND price < 800;
# 查询只满足其中一个要求即可
SELECT * FROM product WHERE cid = 'c001' OR cid = 'C003';
范围查询
# 数字范围查询，在这个区间内--BETWEEN XX AND XX
BETWEEN 100 AND 300;
# 字符是否所属该区间查询--IN() 是否在自定义区间内
SELECT * FROM product WHERE cid in ('c001', 'c003');
# 限制返回数量，进行分页查询 -- LIMIT N:返回前n条数据 LIMIT N,M:返回从n开始后的m条数据（数据从0开始索引）
SELECT * FROM product WHERE cid in ('c001', 'c003') LIMIT 2;
模糊查询
# _匹配任意一个字符
SELECT * FROM product WHERE pname LIKE "__公子";
# %匹配任意多个字符
SELECT * FROM product WHERE pname LIKE "%公子";
空值查询
查询某个键的内容为空的数据
# 使用关键字 IS NULL
SELECT * FROM product WHERE cid IS NULL;
查询结果排序
# ORDER BY 对查询结果进行排序
# 默认是升序，降序ORDER BY 字段名 DESC
# 可以根据多个字段名进行排序
SELECT * FROM product ORDER BY price DESC,cid;
聚合查询
-
COUNT() 计数（统计所有非NULL的数据数量）
-
SUM() 求和
-
AVG() 求平均
-
MIN() 求最小值
-
MAX() 求最大值
SELECT COUNT(pid) FROM product; # 一般使用count（*）或者主键统计数据条数
分组查询 — GROUP BY
被作为分组的列，同一个值的数据只会显示一条，一般分组的时候会进行一些聚合查询
GROUP BY 列名1，列名2 HAVING [条件];
-
后接条件筛选 —— HAVING [条件]
WHERE是对原始数据做条件筛选，HAVING是对查询结果做条件筛选
# 将产品表中同一类产品数量大于3的筛选出来 SELECT cid, AVG(price),COUNT(*) FROM product GROUP BY cid HAVING COUNT(*)>3;
CASE语句
简单 CASE 表达式用于将一个表达式与一组简单的常量值进行比较，根据比较结果返回相应的值。其基本语法如下：
简单CASE表达式
# 表达式
CASE expression
WHEN value1 THEN result1
WHEN value2 THEN result2
...
ELSE default_result
END
应用示例
SELECT 姓名,性别,班号,CASE 系号
WHEN 1 THEN "软件工程"
WHEN 2 THEN "计算机系"
WHEN 3 THEN "物联网系"
END AS "系号名", 班主任号,成绩
FROM student_3_1;
# 在表达式的后面添加AS别名，不然查询结果的列名会很长。
+--------+--------+--------+--------------+--------------+--------+
| 姓名 | 性别 | 班号 | 系号名 | 班主任号 | 成绩 |
+--------+--------+--------+--------------+--------------+--------+
| 张三 | 男 | 1 | 软件工程 | 1 | 80 |
| 李四 | 女 | 1 | 物联网系 | 1 | 60 |
| 王五 | 男 | 2 | 计算机系 | 1 | 50 |
| 马六 | 男 | 1 | 软件工程 | 1 | 80 |
| 刘七 | 女 | 1 | 计算机系 | 1 | 80 |
| 朱八 | 男 | 2 | 物联网系 | 1 | 80 |
| 赵一 | 女 | 2 | 软件工程 | 1 | 70 |
| 钱二 | 男 | 2 | 物联网系 | 1 | 80 |
| 孙小 | 女 | 1 | 计算机系 | 1 | 90 |
| 冯十 | 男 | 2 | 软件工程 | 1 | 80 |
+--------+--------+--------+--------------+--------------+--------+
10 rows in set (0.001 sec)
搜索CASE表达式
搜索 CASE 表达式用于根据一组布尔表达式的结果返回相应的值，它可以处理更复杂的条件判断。其基本语法如下：
# 相比于简单CASE表达式，CASE后面不需要添加表达式
# 在WHEN的后面可以添加布尔表达式
# 并且提供ELSE关键字
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
ELSE default_result
END
应用示例
SELECT 姓名,性别,班号,系号,班主任号,成绩,
CASE
WHEN 成绩 BETWEEN 90 AND 100 THEN "优"
WHEN 成绩 BETWEEN 80 AND 90 THEN "良"
WHEN 成绩 BETWEEN 70 AND 80 THEN "好"
WHEN 成绩 BETWEEN 60 AND 70 THEN "及格"
ELSE "不及格"
END AS "成绩等级"
FROM student_3_1;
+--------+--------+--------+--------+--------------+--------+--------------+
| 姓名 | 性别 | 班号 | 系号 | 班主任号 | 成绩 | 成绩等级 |
+--------+--------+--------+--------+--------------+--------+--------------+
| 张三 | 男 | 1 | 1 | 1 | 80 | 良 |
| 李四 | 女 | 1 | 3 | 1 | 60 | 及格 |
| 王五 | 男 | 2 | 2 | 1 | 50 | 不及格 |
| 马六 | 男 | 1 | 1 | 1 | 80 | 良 |
| 刘七 | 女 | 1 | 2 | 1 | 80 | 良 |
| 朱八 | 男 | 2 | 3 | 1 | 80 | 良 |
| 赵一 | 女 | 2 | 1 | 1 | 70 | 好 |
| 钱二 | 男 | 2 | 3 | 1 | 80 | 良 |
| 孙小 | 女 | 1 | 2 | 1 | 90 | 优 |
| 冯十 | 男 | 2 | 1 | 1 | 80 | 良 |
+--------+--------+--------+--------+--------------+--------+--------------+
10 rows in set (0.001 sec)
# 将一定范围内的行数据进行分组
窗口函数
窗口函数（Window Functions）是一种强大的工具，它可以在查询结果的特定 “窗口”（一组行）上执行计算。与传统的聚合函数不同，窗口函数不会将查询结果合并成一行，而是为每一行返回一个计算结果。
# 规范格式
function_name(expression) OVER (
[PARTITION BY partition_expression1, partition_expression2, ...]
[ORDER BY sort_expression1 [ASC|DESC], sort_expression2 [ASC|DESC], ...]
[frame_clause]
)
排序的窗口函数
ROW_NUMBER()：为结果集中的每一行分配一个唯一的行号，从 1 开始，按照ORDER BY子句指定的顺序依次递增。1，2，2，4RANK()：为结果集中的每一行分配一个排名，排名相同的行具有相同的排名，下一个排名会跳过相应的数量。1，2，2，3DENSE_RANK()：与RANK()函数类似，但排名相同的行具有相同的排名，下一个排名不会跳过。1，2，3，4
窗口函数中的聚合函数
SUM()：计算分区内指定列的总和。AVG()：计算分区内指定列的平均值。
示例代码
SELECT *,AVG(price) over(partition by cid ORDER by price desc) as avg_price from product;
+------+-----------------+-------+------+-------------------+
| pid | pname | price | cid | avg_price |
+------+-----------------+-------+------+-------------------+
| 14 | 小米 | 3500 | NULL | 3500 |
| 15 | 华为 | 3500 | NULL | 3500 |
| 1 | 联想 | 5000 | c001 | 5000 |
| 3 | 雷神 | 5000 | c001 | 5000 |
| 2 | 海尔 | 3000 | c001 | 4333.333333333333 |
| 7 | 劲霸 | 2000 | c002 | 2000 |
| 4 | 杰克琼斯 | 800 | c002 | 1400 |
| 6 | 花花公子 | 440 | c002 | 1080 |
| 5 | 真维斯 | 200 | c002 | 860 |
| 13 | 海澜之家 | 1 | c002 | 688.2 |
| 8 | 香奈儿 | 800 | c003 | 800 |
| 9 | 相宜本草 | 200 | c003 | 500 |
| 10 | 面霸 | 5 | c003 | 335 |
| 11 | 好想你枣 | 56 | c004 | 56 |
| 12 | 香飘飘奶茶 | 1 | c005 | 1 |
+------+-----------------+-------+------+-------------------+
相比于普通的聚合函数，将多列数据仍然保留下来。
分析类窗口函数(偏移函数)
LAG()：返回分区内当前行之前某一行的值。
select 课程号,学号,姓名,成绩,成绩-LAG(成绩) over(partition by 课程号 order by 成绩) as 与上一名同学的差距 from student_3_1;
+-----------+--------+--------+--------+-----------------------------+
| 课程号 | 学号 | 姓名 | 成绩 | 与上一名同学的差距 |
+-----------+--------+--------+--------+-----------------------------+
| M01F011 | s02 | 李四 | 60 | NULL |
| M01F011 | s07 | 赵一 | 70 | 10 |
| M01F011 | s08 | 钱二 | 80 | 10 |
| M01F011 | s05 | 刘七 | 80 | 0 |
| M01F011 | s04 | 马六 | 80 | 0 |
| M01F012 | s03 | 王五 | 50 | NULL |
| M01F012 | s06 | 朱八 | 80 | 30 |
| M01F012 | s09 | 孙小 | 90 | 10 |
| M01F013 | s10 | 冯十 | 80 | NULL |
| M01F013 | s01 | 张三 | 80 | 0 |
+-----------+--------+--------+--------+-----------------------------+
10 rows in set (0.001 sec)
LEAD()：返回分区内当前行之后某一行的值。
select 课程号,学号,姓名,成绩,LEAD(成绩) over(partition by 课程号 order by 成绩)-成绩 as 与下一名同学的差距 from student_3_1;
+-----------+--------+--------+--------+-----------------------------+
| 课程号 | 学号 | 姓名 | 成绩 | 与下一名同学的差距 |
+-----------+--------+--------+--------+-----------------------------+
| M01F011 | s02 | 李四 | 60 | 10 |
| M01F011 | s07 | 赵一 | 70 | 10 |
| M01F011 | s08 | 钱二 | 80 | 0 |
| M01F011 | s05 | 刘七 | 80 | 0 |
| M01F011 | s04 | 马六 | 80 | NULL |
| M01F012 | s03 | 王五 | 50 | 30 |
| M01F012 | s06 | 朱八 | 80 | 10 |
| M01F012 | s09 | 孙小 | 90 | NULL |
| M01F013 | s10 | 冯十 | 80 | 0 |
| M01F013 | s01 | 张三 | 80 | NULL |
+-----------+--------+--------+--------+-----------------------------+
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
