目录
1、where、聚合函数、having 在from后面的执行顺序:
2、若须引入聚合函数来对group by 结果进行过滤 则只能用having
3、having语句通常与group by语句联合使用,用来过滤由group by语句返回的记录集。 having语句的存在弥补了where关键字不能与聚合函数联合使用的不足。
一、聚合函数
1、max()
2、min()
3、avg()
4、sum()
5、count()
count
函数有几种形式:
count(*)
,count(expression)
和count(distinct expression)
expression——必需,一个字段或者一个字符串值
-
count(*)
函数返回由SELECT语句返回的结果集中的行数。-
count(*)
函数计算包含NULL
和非NULL
值的行,即:所有行。
-
-
count(expression)
返回不包含NULL
值的行数。 -
count(distinct expression)
返回不包含NULL
值的唯一行数。
年龄最大的是多少
select max(age) from person;
年龄最小的是多少
select min(age) from person;
年龄平均
select avg(age) from person;
年龄和
select sum(age) from person;
一共有多少人
select count(*) from person;
❕这里如果age是空 就不会计数 如果用* 会对空的行会计数
select count(age) from person;
二、group by 分组汇总
一般聚合函数配合着group by(分组)语句进行使用
把一组的数据放到一起,再配合聚合函数进行使用
⚠️group by查询的内容 除了聚合函数外的其他内容 必须都写在group by 的后面
使用了group by 后,要求Select出的结果字段都是可汇总的,否则就会出错。
‼️‼️‘每个’后面是什么,‘group by’ 后面就是什么
计算每个班级的平均年龄
select class,avg(age) from person group by class;
-- 查询每个班级的最大年龄
select class,max(age) from person group by class;
-- ‼️坑,下面这样查id会报错:SELECT list is not in GROUP BY...
-- 因为查出来的最大值对应的id不一定是唯一的,且“id class”没有被汇总...
select id,class,max(age) from person group by class;
-- 报错:⚠️
SELECT * from person group by name,age -- 报错:⚠️
SELECT MAX(学号),MAX(姓名),MAX(性别),MAX(年龄),sum(成绩)
FROM 学生表
GROUP BY 学号
是对的,汇总出每一同学号学生的总成绩。注意的是,只要学号相同,别的如果有不同,取它们值最大的一条作为显示输出。
SELECT 学号,姓名,性别,年龄,sum(成绩)
FROM 学生表
GROUP BY 学号,姓名,性别,年龄
这样写也是对的,但注意的是,学号,姓名,性别,年龄中,只要有一个不同,就会当成另一条记录来汇总。
三、having
having语句 做筛选的
-
where 是在原始数据的基础上进行筛选 ,⚠️where后面不能使用“聚合函数”
-
having是在分组查询之后和聚合函数计算之后的结果中进行的筛选
查询每个班级有多少人
select class,count(*) from person group by class;
查询 班级人数超过2人的班级 ‼️在查询出班级人数的基础上再进行查询 用having
select class,count(*) from person group by class having count(*) > 2
四、where和having的作用以及区别:
select sid,avg(score) from SC GROUP BY sid HAVING avg(score) > 60
select sid,avg(score) from SC where avg(score) > 60 ——————报错
-
WHERE是一个约束声明,在查询数据库的结果返回之前对数据库中的查询条件进行约束,即在结果返回之前起作用;
-
where 子句的作用是在对查询结果进行分组前,将不符合where条件的行去掉,即在分组之前过滤数据,条件中不能包含聚组函数,使用where条件显示特定的行。
-
且where后面不能使用“聚合函数”,因为where的执行顺序在聚合函数之前。
-
HAVING是一个过滤声明,过滤是在查询数据库的结果返回之后进行过滤,即在结果返回之后起作用,且having后面可以使用“聚合函数”。
-
having 子句的作用是筛选满足条件的组,即在分组之后过滤数据,条件中经常包含聚组函数,使用having 条件显示特定的组,也可以使用多个分组标准进行分组。
-
注意:having是对查出来的结果进行过滤,那么对没有查出来的值就不能使用having。
五、where、having、聚合函数之间的区别和用法
1、where、聚合函数、having 在from后面的执行顺序:
where > 聚合函数(sum,min,max,avg,count) > having
2、若须引入聚合函数来对group by 结果进行过滤 则只能用having
3、having语句通常与group by语句联合使用,用来过滤由group by语句返回的记录集。 having语句的存在弥补了where关键字不能与聚合函数联合使用的不足。
4、演示:
select sum(score) from student
where sex='man'
group by name
having sum(score)>210
六、order by 排序
在sql语句的结尾 添加一个order by的语句
order by 查询的结果的列 asc|desc
-
asc 升序——⚠️不写默认asc
-
desc 降序
当age一样,按照phone降序
select * from person order by age asc,phone desc
select name,age from person order by age
-- 以下有as,order by后用年龄/age都可:
select name,age as 年龄 from person order by 年龄/age
七、AS:设置别名
1、为表指定别名 <表名> AS <别名>
-
<表名>
:数据库中存储的数据表的名称。 -
<别名>
:查询时指定的表的新名称。 -
AS
关键字可以省略,省略后需要将表名和别名用空格隔开 -
表的别名不能与该数据库的其它表同名
-
表别名只在执行查询时使用,并不在返回结果中显示。
2、为字段指定别名 <字段名> AS <别名>
-
<字段名>
:为数据表中字段定义的名称。 -
<字段别名>
:字段新的名称。 -
AS
关键字可以省略,省略后需要将字段名和别名用空格隔开。 -
在条件表达式中不能使用字段的别名,否则会出现“ERROR 1054 (42S22): Unknown column”这样的错误提示信息。
-
字段的别名不能与该表的其它字段同名。
-
字段定义别名之后,会返回给客户端显示,显示的字段为字段的别名。
八、函数/关键字
1、distinct
select distinct columns from 表 where 条件
-
distinct和null值
-
如果列具有null值,并且对该列使用distinct子句,mysql将保留一个
null
值,并删除其它的null
值,因为distinct子句将所有null
值视为相同的值。
-
-
distinct在多列上的使用
-
select distinct state, city from——获取
city
和state
的唯一组合
-
-
distinct和聚合函数
-
使用具有聚合函数的
distinct
子句中,在mysql将聚合函数应用于结果集之前删除重复的行 -
select count(distinct state) from...
-
2、limit子句
-
select column1,column2,... from table LIMIT offset , count
offset
参数指定要返回的第一行的偏移量。第一行的偏移量为0
,而不是1
。count
指定要返回的最大行数。 -
举例说明
例如下面两条语句: SELECT * FROM table limit 2 offset 1; SELECT * FROM table limit 2,1; 前者表示跳过一条数据,读取两条数据 后者表示跳过两条数据,读取一条数据 比如有三条数据,id 分别为 0,1,2 则前者读取的数据 id 为 1 和 2 后者读取的数据为 2 总结: LIMIT 2 OFFSET 1 :OFFSET 表示跳过,LIMIT 表示读取 LIMIT 2, 1 :前面一个数字为跳过,后面的为读取
3、union
union 语句:用于将不同表中相同列中查询的数据展示出来;(不包括重复数据)
union all 语句:用于将不同表中相同列中查询的数据展示出来;(包括重复数据)
SELECT 列名称 FROM 表名称 UNION SELECT 列名称 FROM 表名称 ORDER BY 列名称; SELECT 列名称 FROM 表名称 UNION ALL SELECT 列名称 FROM 表名称 ORDER BY 列名称;
九、示例
现在有一个表person:
id,name,age
1,jay,18
2,jay,18
3,waw,20
4,zzq,25
思考:
1、通过sql语句查询出重复的数据有哪些
SELECT name,age from person group by name,age having count(*) >= 2
2、删除重复的数据
删除重复数据 那么保留的是什么?——如果数据重复 保留最小的id的数据,如果数据不重复,保留该数据
-- ⚠️步骤1:查询要保留的数据
-- 只要根据名字和年龄做分组,查询分组内最小的id值 就是要保留的数据
SELECT min(id) from person group by name,age
-- ⚠️步骤2:删除重复数据
-- ⚠️如下:‼️查询某个表的数据的同时去修改/删除该表的数据,直接这样操作不行,mysql不允许
❌delete from person where id not in (SELECT min(id) from person group by name,age)
-- ⚠️可以把查询的结果单独的放在一个结果集里t,通过查询这个结果集,删除表里的数据
1、select min(id) from person group by name,age ——-放到临时表t里面
2、select id from t
3、delete from person where id not in (select id from t)
delete from person where id not in (select id from (select min(id) as id from person group by name,age)t)