【数据库】聚合函数｜group by分组｜having｜where｜排序｜函数 关键字的使用

目录

一、聚合函数

1、max()

2、min()

3、avg()

4、sum()

5、count()

 二、group by 分组汇总

一般聚合函数配合着group by(分组)语句进行使用

把一组的数据放到一起，再配合聚合函数进行使用

 三、having

having语句 做筛选的

四、where和having的作用以及区别：

五、where、having、聚合函数之间的区别和用法 

1、where、聚合函数、having 在from后面的执行顺序：

2、若须引入聚合函数来对group by 结果进行过滤 则只能用having

3、having语句通常与group by语句联合使用，用来过滤由group by语句返回的记录集。 having语句的存在弥补了where关键字不能与聚合函数联合使用的不足。

4、演示：

六、order by 排序

七、AS：设置别名

1、为表指定别名  <表名> AS <别名>

2、为字段指定别名 <字段名> AS <别名>

八、函数/关键字

1、distinct

distinct和null值

distinct在多列上的使用

distinct和聚合函数

2、limit子句

3、union

九、示例

---

一、聚合函数

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)