目录
3.1 前置知识—笛卡尔积 https://www.cnblogs.com/Toolo/p/3634563.html
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 不会进行去重,会进行简单地叠加。