吃透MySQL高级查询这一篇就够了~

目录

1、聚合查询

1.1 count 查询

1.2 sum 查询

1.3 avg 查询

1.4 max/min 查询

1.5 if null 查询

2、分组查询

2.1 group by

​

 2.2 分组条件查询 having

3、联合查询（联表查询）

3.1 前置知识—笛卡尔积 https://www.cnblogs.com/Toolo/p/3634563.html

3.2 内连接

3.3 外连接

3.3 自查询

3.4 子查询(嵌套查询)

 3.5 合并查询

---

1、聚合查询

1.1 count 查询

查询条数

①

select count(*) from 表名;
-- 推荐使用，可查出所有数据条数，包括null和非null。

②

select count(0) from 表名;
--兼容性不好，仅限MySQL使用。

③

select count(列名) from 表名;
-- 统计非null的数据条数。

不推荐使用，如果业务需要，可以用count(*)结合 where 进行操作使用。

1.2 sum 查询

select sum(列名) from 表名;
-- 和统计，若有null或统计非整数值，那么它只会统计有效的数值（为null的默认为0);

※ 注意区分和表达式查询的差别：表达式查询的行数据的和，eg：chinese + english + math 若有一个为null 最后查询的值为null。

1.3 avg 查询

select avg(列名) from 表名;
--若有为null的值，则将数据忽略。

1.4 max/min 查询

select max(列名) from 表名;

select min(列名) from 表名;

--可以同时使用多个聚合函数：select max（math）,min(english) from 表名;

1.5 if null 查询

select ifnull(A,B) from 表名;
--判断是否为空 若A!=null 返回A，否则返回B;
--null 和 '' 是不同的。

2、分组查询

2.1 group by

select max(salry),min(salary),role from emp group by role;
--查询三个字段，按角色进行分组。

 2.2 分组条件查询 having

having：过滤group by 之后的数据，满足条件的留下来。

 ※注意：

① group by 之后不能再加 where 查询

 

② having 和 group by 都可以单独使用，单独使用having 与 where 类似，不过又有细微区别：having 中可以使用别名，而where不行，where中可以使用select 中未出现的列进行筛选，而having不行

③ having 和 group by 都可以使用别名（虽然二者执行在 select（定义别名阶段） 之前）

为什么MySQL group by 和 having 可以使用别名？

 这是因为mysql 5.7.5 之后做了一个特殊处理，允许在group by 和 having中使用别名，在之前按照select执行顺序来说，是不能的。

3、联合查询（联表查询）

3.1 前置知识—笛卡尔积 https://www.cnblogs.com/Toolo/p/3634563.html

3.2 内连接

关键字join

select * from 表1 join 表2 [on 过滤条件][where 过滤条件];

select * from 表1 inner join 表2 [on 过滤条件][where 过滤条件];

select * from 表1  cross join 表2 [on 过滤条件][where 过滤条件];
--on 从语法上来说可以省略，但省略之后查询的就是多张表的笛卡尔积，会存在很多无效数据。

select * from 表1,表2 [where 过滤条件];
--这种查询不能加on过滤条件

例题1 查询张三的成绩：

        ①进行内连接查询

        ②去掉无效数据 on

 

        ③ 查询张三成绩 where 过滤条件

 例题2 查询每个人的总成绩和个人信息

        ①联合学生表和成绩表进行查询

        ②排除笛卡尔积中无意义的数据

        ③使用sum 来得到成绩的总和

        ④根据学生id进行分组

 例题3 查询学生的成绩和科目以及个人信息，要求根据相同的科目显示在一起，并按照成绩降序排列，为null则为0。

 

3.3 外连接

①左（外）连接：查询出左表中的所有数据和右表中的关联数据

②右（外）连接：查询出右表中的所有数据和左表中的关联数据

 例题1 查询所有人的个人信息+课程名+分数

 ※ 联表查询中的 不同位置on的区别

        ①内连接on是可以省略的，而外连接on不能省略

        ②on 在内连接中的执行效果和外连接中的执行效果是不一样的

                内连接中on过滤全局信息（例如学生表和成绩表，如若学生没有成绩，那么进行内连接，将不会查出没有成绩的学生信息），而外连接不会过滤掉左表（主表）的信息（即便有学生没有参加考试，那么他的信息也会被查询出来）。

        ③在外连接中 on 和 where 又是不一样的

   

3.3 自查询

自查询顾名思义，自己和自己进行联表查询，用join(内连接的方式实现)

① 查询英语成绩 < 计算机成绩的数据

where实现

 join实现

select st.score'计算机成绩',st2.score'英语成绩' from score_table st join score_table st2 on st.student_id = st2.student_id and st.course_id =1 and st2.course_id=2 and st.score>st2.score;

3.4 子查询(嵌套查询)

将查询结果作为另一个查询的where选项

①查询张三的同班同学

        1、查询张三的班级id

        2、根据查出来的班级id查询出所有列表

②查询计算机或英语的成绩

        由于成绩表里面没有成绩名称，所以需要先去score表里查询科目id，再去成绩表查询成绩

 

 ※ 用 in 而不用 =

        = 查询  需要一个具体确定的值

        in 查询   可以是一个或者多个值，并且满足任意一个将返回true。

 3.5 合并查询

        - union

        - union all

① 查询课程id< 2 和名字为“英语”的课程

union：

union all：

 ※ union vs union all

        union 会将合并的结果集中的重复数据进行去重，而union all 不会进行去重，会进行简单地叠加。