MySQL常用语句复习

1、分组查询：
MySQL语句执行过程：from->where->group by->select->having->order by(asc/desc)->limit

注：having是过滤 分组数据(每种分组仅显示1条数据) 的（分组数据，非分组内的数据），通常用于group by后

(1)  # 查询 课程ID为80且实付金额大于1的 所有订单的id、实付金额？
select id,real_price from t_pay_order_202012 where season_id=80 && real_price > 1;(&&用and也可以)
(2)  # 查询 所有不同的课程id？（即去重查询，有2种方法）
select distinct season_id from t_pay_order_202012;
或select season_id from t_pay_order_202012 group by season_id;
延伸：查询 课程数？(课程门数、课程总数)
select count(distinct season_id) from t_pay_order_202012;
或 嵌套查询：select count(*) from (select distinct season_id from t_pay_order_202012) as m;
★PS：select season_id,count(season_id) from t_pay_order_202012 group by season_id;统计的是分组后，每门课程的id、每门课程id的数据量。不是 课程门数
(3)  # 查询 所有不同的课程id，及 每种课程的 最大实付金额、最小实付金额？
select season_id,max(real_price),min(real_price) from t_pay_order_202012 group by season_id;
(4)  # 查询 最小实付金额大于等于xx的 所有不同的课程id 及 这些类型课程的 最大实付金额、最小实付金额？
select season_id,max(real_price),min(real_price) from t_pay_order_202012 group by season_id having min(real_price) >= xx;（select中必须也有 min(real_price)）
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
示例：
test_1表：

test_2表：

2、左外连接(以左表为基准进行连接，右表没有数据的字段都为NULL)：（连接方式用 on！！！）
select * from test_1 a left outer join test_2 b on a.id = b.id where xxx;  # 查询具体字段可用a.xx和b.xx。outer一般省略

3、右外连接(以右表为基准进行连接，左表没有数据的字段都为NULL)：（连接方式用 on！！！）
select * from test_1 a right outer join test_2 b on a.id = b.id where xxx;  # 查询具体字段可用a.xx和b.xx。outer一般省略

4、内连接（★只取2张表的 交集数据！！！）
显示内连接：（连接方式用 on！！！）
select * from test_1 as a inner join test_2 as b on a.id = b.id where xxx;   # as和inner可省略。查询具体字段可用a.xx和b.xx
隐式内连接（自然连接）：（连接方式用 where，和上一种不同！！！）
select * from test_1 a,test_2 b where a.id = b.id;   # 查询具体字段可用a.xx和b.xx
二者结果一样：

注意：
（1）left/right/inner join整合成一张大表，所以后面都可以加“where、group by having、order by、limit”等进行详细筛选
（2）left/right/inner join整合成一张大表后，用where很容易出错，尽量不要用。因为where通常是用来 直接(从磁盘文件中)过滤数据的(整合成的大表数据 是内存中的数据(？暂不确定))（参考：Mao_yafeng/article/details/126040157）
（3）“on a.xx = b.xx”中，2张表的字段xx的值应都是唯一的，否则查询结果会是笛卡尔积(隐式内连接中的where a.xx = b.xx也一样)（也可以注意一下 2张表的字段xx的值的数据类型是否一致）

5、子查询：mysql子查询的五种方式 - 老白网络
select column1 from table1 where column2 in (select column3 from table2 where column4=xxx);
select column1 from table1 where column2=(select max(column2) from table2 where column3=xxx);（标量子查询，用于查询单个值，所以括号里语句的查询结果 必须只有1条）
select column1 from table1 where column2 not in (select column3 from table2 where column4=xxx);

6、全连接：
MySql不支持全连接FUll JOIN，不过可以通过联合UNION来模拟：
left join UNION right join

7、case when then else end
select *,(case when task_id < 60 then '不及格'
                when task_id > 60 and task_id < 80 then '及格'
                when task_id >= 80 then '优秀'
                # when task_id is null then '缺席考试'
                else '--------刚好及格--------'
                end) 'level'
from test_2

8、其他常用的
distinct去重、聚合函数sum()、max()、min()、avg()、count() 等。

其中，avg()通常用于求某一列的 平均值，如：(https://blog.csdn.net/go_down_/article/details/108552937)查询学生成绩表中各科成绩平均值？SELECT subject,ROUND(AVG(score),2) FROM study_score GROUP BY subject;（ROUND()函数也可不用）

但也可以用于 求占比值，如：
查询 表中女生占比值？SELECT AVG(sex = 1) as proportion FROM study_score;
查询 成绩大于90分的数据的占比值？SELECT AVG(score > 90) as proportion FROM study_score;
查询 各科目中，成绩大于80分的数据的占比值？(保留2位小数)SELECT subject,ROUND(AVG(score > 80),2) FROM study_score GROUP BY subject;

9、2023.7.18记录：
表A：Id:1，2，3，4，5   xxNo：001，002，001，002，003

查询 类型重复的xxNo？(与1、(4)相比，其实就是换了种说法而已)select xxNo,count(xxNo) from A group by xxNo having count(xxNo)>1;（count(*)和count(xxNo)的区别：后者不统计xxNo字段值为null的数据）
查询 类型不重复的xxNo？select xxNo,count(xxNo) from A group by xxNo having count(xxNo)=1;
查询 所有不同的xxNo？（即去重查询，有2种方法）见 1、(2)

★删除 类型重复的xxNo(所在行的数据)？delete from A where xxNo in (select xxNo from A group by xxNo having count(xxNo)>1);（括号里会查询出 所有 类型有重复的xxNo，in 这些xxNo，会删除 类型有重复的xxNo所在行的数据，只剩 xxNo为003这种 xxNo类型不重复的数据）（这个语句不正确，后续再研究。因为括号里的语句中，select后必须有count(xxNo)：having从内存中获取结果进行过滤，所以select后必须有count(xxNo)。但是有了count(xxNo)的话，查询出的结果就不只有xxNo了，还有count(xxNo)，就in不了了，所以语句不正确）

10、2023.8.16记录：
Mysql查询 从第11行到第20行的记录：SELECT * FROM users LIMIT 10, 10;（LIMIT offset, count。http://www.qince.net/mysql5xdbo.html）（where id in (1,2,3)还是[1,2,3]？MySQL LIKE 子句 | 菜鸟教程）

https://baijiahao.baidu.com/s?id=1709966309120511971、mysql条件查询and or使用实例及优先级介绍 - 码农教程、https://blog.csdn.net/asz321/article/details/102723845
select count(*) from (select distinct name from A) as m;效果等于select count(distinct name) from A;（后者统计结果中没有值为null的数据，前者得看select distinct name from A的结果里有没有值为null的数据。PS：COUNT(*) 的统计结果中，会包含值为NULL的行数。COUNT(expr) ，返回SELECT语句检索的行中expr的值不为NULL的数量）