一、SQL语法——10-分组和组函数

10-分组和组函数

1.组函数即多行函数，组函数将一组记录作为整体，每组记录返回一个结果，而不是每条记录返回一个结果。

2.组函数简单示例：

--创建表并添加记录
create table teacher_table(
    teacher_id int auto_increment,
    teacher_name varchar(255),
    primary key(teacher_id)
);
create table student_table(
    student_id int auto_increment primary key,
    student_name varchar(255),
    java_teacher int,
    foreign key (java_teacher) references teacher_table(teacher_id)
);

--为表中添加数据
insert into teacher_table
values
(null,'teacher_1'),
(null,'teacher_2'),
(null,'teacher_3');

insert into student_table
values
(null,'studnet_1',1),
(null,'studnet_2',1),
(null,'studnet_3',1),
(null,'studnet_4',2),
(null,'studnet_5',2),
(null,null,2),
(null,'studnet_6',null);



--计算student_table表中的记录条数
select count(*)
from student_table;

--计算java_teacher列共有多少个值
select count(distinct java_teacher)
from student_table;

--统计所有student_id的总和
select sum(student_id)
from student_table;

--计算结果是20*记录的行数
select sum(20)
from student_table;

--选出student_table中student_id最大的值
select max(student_id)
from student_table;

--选出teacher_table中id最小的值
select min(teacher_id)
from teacher_table;

/*
因为sum中的expr是常量34，所以每行的值都相同
使用distinct强制不计算重复值，所以下面的结果为34
*/
select sum(distinct 34)
from student_table;

--使用count统计记录行数时，null不会被计算在内
select count(student_name)
from student_table;


--对于可能出现null的列可以使用ifnull来处理null
--计算java_teacher列的平均值
select avg(ifnull(java_teacher,0))
from student_table;

--注意：distinct和*不能够同时使用

 

3.group by子句

（1）默认情况下，组函数会把所有记录当做是一组，为了对记录进行显式分组，可以在select后使用group by子句,group by后通常跟一个或多个列的列名，表明查询结果根据一列或多列进行分组——当一列或多列组合的值完全相同时，系统会把这些记录当做是一组：

（2）示例：

--count(*)将会对每组得到一个结果
select count(*)
from student_table
--将java_teacher列值相同的当做一组
group by java_teacher;

--如果要对多列进行分组，只有当多列的值完全相同才会被当成一组
select count(*)
from student_table
group by java_teacher,student_name;

（3）如果需要对分组进行过滤，则应该使用having,having子句后边也是一个表达式，只有满足该表达式的分组才会被选出来；

（4）having和where的区别：

1）不能在where子句中过滤组，where子句仅仅用于过滤行，过滤组需要使用having；

2）不能在where子句中使用组函数，having子句中才能使用组函数。

（5）having的使用示例：

select * from student_table
group by java_teacher
--对组进行过滤
having count(*)>2;

 

 

 

 

转载于:https://www.cnblogs.com/ljiwej/p/7280180.html