之前写的MySQL中也有提到复杂查询,只是写了些理论知识,光说不练假把式,所以我把复杂查询的具体操作写一下,加深印象。
1、聚合查询
1)通用的count
2)数值类的max/min/avg/sum
话不多说,我们开始操作👇👇👇
--先创建一张表
create table person(
name varchar(20),
height double,
weight double
);
--插入数据
insert into person (name,height,weight) values
("盲僧",175,60),
("提莫",100,30),
("泰坦",200,300),
("盖伦",185,80),
("安妮",150,45),
("锤石",190,65);
count是统计查询出来结果的行数
我在上面插入了6行数据,要统计数据的行数可以这么操作:
select count(*) from person;
那么这条命令呢?结果是多少?
select count(1) from person;
答案是1吗???
NO!
显然,结果还是6,那为什么是6呢?
我们知道,count是统计查询结果行数的,想知道为什么count(1)结果是6,那把count去掉就好
这样就好理解了吧,因为去掉count后,查询结果还是6行(数据内容变了,但我们不管)
我们再插入一条数据吧,插入“海涛”,但这个人比较神秘,身高和体重都没有(null)
那么执行下面操作结果是多少?
select count(*) from person; --7
select count(name) from person; --7
select count(height) from person; --6
select count(weight) from person; --6
由此我们可以知道,使用count时,null不计算在内
总结
计算count结果方法?
去掉count后得出的结果中,再次去掉所有的null,剩下多少行,count的结果就是多少。
至于其他的聚合函数,都比较好理解,我这里一起举例
select sum(height) from person;--统计所有身高的和
select max(height) from person;--统计最大身高
select min(height) from person;--统计身高最小值
select avg(height) from person;--统计身高平均值
注意:sum,max,min,avg是用来查询数字的,不是数字没有意义(是没有意义,不是查不出来,我们可以试着对name查最大值,结果是“锤石”)。
上面写的比较简单,现在来试试分组聚合
1、聚合函数
2、分组
3、聚合后再过滤
这里还是先创建一张新表class_person,我这里已经创建好了,表结构如下👇
插入数据👇
问题来了!针对刚创建的class_person表
1、统计每个班分别有多少人?
2、每个班的最高身高?
3、每个班的平均体重?
select class_name,count(*) from class_person group by class_name;--统计每个班人数
select class_name,max(height) from class_person group by class_name;--统计每个班最高身高
select class_name,avg(weight) from class_person group by class_name;--统计每个班的平均体重
这里我有个问题,统计每个班最高身高,加上name不是更好吗,还能看到谁最高,
例如:select class_name,name,max(height) from class_person group by class_name;
这样行吗?
答案是不行,可以自己执行一下,会报错。这里有一个重要的知识点:
group by 的select后边跟的内容有限制,只能出现两类:
1、聚合函数
2、group by 后的分组凭证
是不是感觉挺简单的,我们再加深一下,来实现多次分组
老规矩,我们再建一张表jobs,并往里面插入数据👇
那就先简单练习一下刚才的分组聚合,查询每个公司有多少人?每个部门有多少人
select company_name,count(*) from jobs group by company_name;--每个公司有多少人
select group_name,count(*) from jobs group by group_name;
查询一下每个公司各部门的人数?
这里变化不大,只是多一次分组
select company_name,group_name,count(*) from jobs group by company_name,group_name;
来查询一下哪些部门总薪资大于30000
其实也不难理解,看下面这两条命令
select company_name,group_name,sum(salary) from jobs group by company_name,group_name;
select company_name,group_name,sum(salary) from jobs group by company_name,group_name having sum(salary)>30000;
没错,只要在聚合后加上条件来筛选就可以。
注意:
order by vs group by
排序 分组
where vs having
聚合前 聚合后 过滤
2、联合查询(联表查询)
联合查询分为内连接和外连接,其中外连接包括左连接和右连接
具体操作这篇文章里有Mysql总结
这里再写一下自连接的操作👇
还是先建表
create table pystudents(
id int,
name varchar(200),
master_id int
);
--插入数据
insert into pystudents(id, name, master_id) values
(1,"刘备",1),
(2,"关羽",1),
(3,"张飞",1),
(4,"曹操",4),
(5,"典韦",4),
(6,"夏侯惇",4),
(7,"宋江",7),
(8,"李逵",7),
(9,"林冲",7);
查询每个人的姓名+上级的姓名(提示:一张表两个别名,当作两张表使用)
select s.id sid,s.name sname, m.id mid,m.name mname from pystudents s,pystudents m where s.master_id=m.id;
3、子查询
未完待续!!