MySQL常用SQL
MySQL常用SQL
订单表：orders（id、no、user_id、shop_id、total_fee、phone、paid_at、status、created_at、updated_at）
订单明细表：order_items（id、order_id、shop_id、product_id、category_id、price、quantity、total_fee、created_at、updated_at、coupon_id）
菜单表：categories（id、name、remark、created_at、updated_at）
商品表：products（id、name、description、category_id、support_takeaway、created_at、updated_at）
SKU表：skus（id、product_id、name、price、sort、created_at、updated_at）
1、消除取值重复的行（distinct）
select distinct name as '商品名称 ' from products;
2、分组统计（group by）
查询各商品分类下有多少商品
select c.id,c.name as "分类名称", count(*) as "商品数量" from products p left join categories c on p.category_id=c.id group by p.category_id;
查询每个的分类id和商品名称及分类下的商品的平均价格
select c.id,c.name as "分类名称", avg(s.price) as "分类下商品平均价格" from products p left join categories c on p.category_id=c.id LEFT JOIN skus s ON s.product_id=p.id group by p.category_id;
3、聚合函数的条件筛选
having 关键字后面直接跟聚集函数，在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与合计函数一起使用。
【例】查询分类下的商品数量大于30的分类ID号和分类下商品数量
SELECT p.category_id AS "分类", count(p.id) AS "分类下商品数量" FROM products p GROUP BY category_id HAVING count(p.id)>30;
【例】查询每个订单内包含的商品分类大于等于5的订单号和订单包含的商品分类数量
SELECT o.id,o.`no` AS "订单号",count(oi.id) AS "订单内商品种类数量" FROM orders o LEFT JOIN order_items oi on o.id=oi.order_id GROUP BY oi.order_id HAVING count(oi.id)>=5;
4、CASE WHEN 语句的用法
第一种用法：CASE后面跟列名，when后面跟对应值
CASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list] ...
[ELSE statement_list]
END
【例】查询订单是否使用了优先券(orders的prior字段：0 未使用，其他为已使用)
SELECT id,`no`,
CASE prior
WHEN 0 THEN "未使用优先券"
ELSE "使用了优先券"
END "是否使用优先券"
FROM orders
ORDER BY
id ASC
LIMIT 10;
当sex值为0时当前列显示“男”，否则显示“女”，sql写法如下：
SELECT
( CASE sex WHEN 0 THEN '男' ELSE '女' END ) AS 性别,
avg( score ) AS 平均分
FROM
grade
GROUP BY
sex;
第二种用法：case后面空白，when后面跟着判断条件
CASE
WHEN search_condition THEN statement_list
[WHEN search_condition THEN statement_list]
...
[ELSE statement_list]
END
针对于这种写法，我们考虑这样一种需求，学生成绩是有评分的，大于等于90分的学生是A，小于90分大于等于60分的学生是B， 其余的学生是C，现在要查询评分为A、B、C的学生成绩的平均分分别是多少，因为成绩评分并不是单独的一列，所以不能简单的 使用 group by 来分组实现了，但是可以利用 case when 语句实现，写起来也很简单，看看下面的sql语句就知道了！
select
(case
when score >= 90 then 'A'
when score < 60 then 'C'
else 'B'
end) as '等级',
avg(score) as 平均分
from
grade
group by
'等级';
【例】
CREATE TABLE `result` (
`Time` date NOT NULL,
`is_result` varchar(5) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
插入数据：
INSERT INTO `test`.`result`(`Time`, `is_result`) VALUES ('2020-05-09', '胜');
INSERT INTO `test`.`result`(`Time`, `is_result`) VALUES ('2020-05-09', '胜');
INSERT INTO `test`.`result`(`Time`, `is_result`) VALUES ('2020-05-09', '胜');
INSERT INTO `test`.`result`(`Time`, `is_result`) VALUES ('2020-05-09', '胜');
INSERT INTO `test`.`result`(`Time`, `is_result`) VALUES ('2020-05-10', '胜');
INSERT INTO `test`.`result`(`Time`, `is_result`) VALUES ('2020-05-10', '胜');
INSERT INTO `test`.`result`(`Time`, `is_result`) VALUES ('2020-05-10', '胜');
思路：
1.查询出胜的次数，按时间分组；
2.查询出负的次数，按时间分组
3.联结查询，根据时间相等。
查询胜利的次数
select Time ,count(1) from tbl where is_result = '胜' GROUP BY time
查询失败的次数
select Time ,count(1) from tbl where is_result = '负' GROUP BY time
一次查出胜的次数和负的次数，按时间分组 结果得出
SELECT Time AS "时间",
COUNT(CASE is_result WHEN "胜" THEN "胜" END) AS "胜",
COUNT(CASE is_result WHEN "负" THEN "负" END) AS "负"
FROM result GROUP BY Time;
总结
case when 语句共有两种写法，使用时要区别两种用法的差异。
使用 case when 语句可以实现修改数值的对应关系，还可以按照复杂的条件进行分组。
关于 case when 语句的详细用法，有兴趣的同学可以参考一下官方文档：13.6.5.1 CASE Syntax
