mysql 根据商品价格区间 分组

文章目录

• 前言
• 简单查询
• 模糊查询
• 聚合函数
• 排序查询
• 分组查询
• 分页查询
• NULL查询

---

前言

DQL（Data Query Language），数据查询语言，主要是用来查询数据的，这也是SQL中最重要的部分！

简单查询

以fruits表为例子进行查询操作：

-- 查询所有
SELECT * FROM fruits;

-- 按照指定字段查询
SELECT f_name,f_price FROM fruits;

-- 按照指定条件查询 查询名称为 'apple' 的水果名称及价格
SELECT f_name,f_price FROM fruits WHERE f_name='apple';

-- 多条件查询 查询价格在 5元 和 10元 之间的 水果名称 和 价格
SELECT f_name,f_price FROM fruits WHERE f_price<='10' AND f_price>='5';
SELECT f_name,f_price FROM fruits WHERE f_price<='10' && f_price>='5';
SELECT f_name,f_price FROM fruits WHERE f_price BETWEEN 5 and 10;

-- 查询供应商编号 s_id 为 101 和 102 的水果
SELECT f_name,f_price FROM fruits WHERE s_id=101 or s_id=102;

-- 查询价格在 5.2元 和 10.2元 之间 供应商编号 s_id 为 101 和 102 的水果 
SELECT f_name,f_price FROM fruits WHERE (s_id=101 or s_id=102) AND (f_price BETWEEN 5.2 AND 10.2);

-- in查询
-- 查询供应商编号 s_id 为 101 和 102 的水果
SELECT s_id,f_name,f_price FROM fruits WHERE s_id IN(101,102);

-- 查询供应商编号 s_id 不是 101 和 102 的水果
SELECT s_id,f_name,f_price FROM fruits WHERE s_id != 101 AND s_id != 102;
SELECT s_id,f_name,f_price FROM fruits WHERE s_id NOT IN(101,102);

模糊查询

以fruits表为例子进行查询操作：

-- 模糊查询
-- 查询名字为 b 开头的水果 一个' % ' 代表零个或多个字符
SELECT * FROM fruits WHERE f_name like 'b%';

-- 查询名字中有 g 的水果 
SELECT * FROM fruits WHERE f_name like '%g%';

-- 查询名字中有 g 供应商编号 s_id 为 102 价格在 10 与 20 之间的 水果
SELECT * FROM fruits WHERE (f_name like '%g%') AND (s_id=102) AND (f_price BETWEEN 10 AND 20);

-- 查询名字为 b 开头的 y 结尾的水果
SELECT * FROM fruits WHERE f_name like 'b%y';

-- 查询结尾是 y 前面有 5 个字符的水果 一个' _ '	仅替代一个字符
SELECT * FROM fruits WHERE f_name like '_____y' ;

聚合函数

以fruits表为例子进行查询操作：

-- 聚合函数
-- 种类总数
SELECT COUNT(*) AS'总数量' FROM fruits;

-- 最大值
SELECT MAX(f_price) AS'最大值' FROM fruits;

-- 最小值
SELECT MIN(f_price) AS'最小值' FROM fruits;

-- 求和
SELECT SUM(f_price) AS'求和' FROM fruits;

-- 平均值
SELECT SUM(f_price)/COUNT(*) AS'平均值' FROM fruits;
SELECT AVG(f_price) AS'平均值' FROM fruits;

排序查询

以fruits表为例子进行查询操作：

-- 排序
-- 按照水果价格升序  默认是升序（ASC）
SELECT * FROM fruits ORDER BY f_price;
SELECT * FROM fruits ORDER BY f_price ASC;

-- 按照水果价格降序 DESC 
SELECT * FROM fruits ORDER BY f_price DESC;

-- 按照供应商 s_id 升序 如果供应商相同的情况下按照价格降序
SELECT * FROM fruits ORDER BY s_id ASC,f_price DESC;

分组查询

以fruits表为例子进行查询操作：

-- 分组查询
-- 每个供应商供应水果种类数量
SELECT s_id,COUNT(*) FROM fruits GROUP BY s_id;

-- 每个供应商供应水果种类数量 按照供应水果种类数量降序
SELECT s_id,COUNT(*) AS 'total' FROM fruits GROUP BY s_id ORDER BY total DESC;

-- 每个供应商供应水果种类数量 按照供应水果种类数量降序 如果供应水果种类数量相同则按照供应商编号降序
SELECT s_id,COUNT(*) AS 'total' FROM fruits GROUP BY s_id ORDER BY total DESC,s_id DESC;

小练习：类似这种的统计清单

以orderitems表为例进行操作：

-- 练习
-- 查询订单详情总表
SELECT * FROM orderitems;

-- 查询单个编号订单信息
SELECT * FROM orderitems WHERE o_num='30001';

-- 计算单个编号订单的金额小计
SELECT SUM(quantity*item_price) AS'total（总计）' FROM orderitems WHERE o_num='30001';

-- 分组计算出所有编号订单的总金额
SELECT o_num,GROUP_CONCAT(quantity*item_price) AS'sub_total（小计）',SUM(quantity*item_price) AS'total（总计）' FROM orderitems GROUP BY o_num;

-- 分组计算出所有编号订单的总金额 并降序
SELECT o_num,GROUP_CONCAT(quantity*item_price) AS'sub_total（小计）',SUM(quantity*item_price) AS'total（总计）' FROM orderitems GROUP BY o_num
ORDER BY SUM(quantity*item_price) DESC;

-- 分组计算出所有编号订单的总金额大于 200 的 并降序
SELECT o_num,GROUP_CONCAT(quantity*item_price) AS'sub_total（小计）',SUM(quantity*item_price) AS 'total（总计）' FROM orderitems GROUP BY o_num
HAVING total（总计）>200 ORDER BY SUM(quantity*item_price) DESC;

分页查询

以fruits表为例子进行查询操作：

-- 分页查询 LIMIT
-- LIMIT 起始位置 , 查询数量
-- (查询页面号 - 1) * 每页记录数 = 起始位置 
-- 总页数 = if(总记录数 % 每页记录数 == 0) ? 总记录数 / 每页记录数 : (总记录数 / 每页记录数) + 1

-- 第一页
SELECT * FROM fruits LIMIT 0,5;

-- 第二页
SELECT * FROM fruits LIMIT 5,5;

-- 第三页
SELECT * FROM fruits LIMIT 10,5;

-- 第四页
SELECT * FROM fruits LIMIT 15,5;

NULL查询

以customers表为例子进行查询操作：

-- NULL查询
SELECT * FROM customers;

-- 查询没有绑定邮箱的消费者
SELECT * FROM customers WHERE c_email IS NULL;

-- 查询绑定邮箱的消费者
SELECT * FROM customers WHERE c_email IS NOT NULL;

-- 统计消费者人数 
SELECT COUNT(*) FROM customers;
SELECT COUNT(c_id) FROM customers;
-- 因为以c_email为条件查询 值为null的不计数
SELECT COUNT(c_email) FROM customers;

-- 查询绑定邮箱的人数
SELECT COUNT(c_email) AS '人数' FROM customers;

-- 查询没有绑定邮箱的人数
SELECT COUNT(*) AS '人数' FROM customers WHERE c_email IS NULL;