一、 count、distinct、group by的用法：
1.count()
函数是用来统计表中记录的一个函数，返回匹配条件的行数，不去重。
2.count()
语法：
（1
）count(*)—包括所有列，返回表中的记录数，相当于统计表的行数，在统计结果的时候，
包含
NULL
的记录，且
不去重
。
（2
）count(1)—第一列，1表示一个固定值，也可以用count(2)、count(3)代替，在统计结果的时候，
包含
NULL
的记录。
（3
）count(列名)—只包括列名指定列，返回指定列的
非空
的记录数，在统计结果的时候，不包含为NULL
的记录。
（4
）count(distinct 列名)—只包括列名指定列，返回指定列的不同值的记录数，即
会去重
，且不包含列值为NULL
的记录。
3. SELECT count(*) FROM 表名 WHERE 条件 // 这样查出来的是总记录条
SELECT id count(*)FROM 表名 WHERE 条件 GROUP BY id //这样统计的会是每组的记录条数，统计出来的是相同id值的计数情况。
SELECT id count(*) FROM 表名 WHERE 条件.
是错误的写法。
一般来说，一条
select语句
的执行顺序如下：from子句→where→group by（having）→select→order by→limit，因为
聚集函数
是在select中的，所以是在执行完group by之后再执行count（）函数，
转载地址：https://blog.csdn.net/wuyou1336/article/details/53822745
本文将探讨以下问题
1.count(*)
、
count(n)
、
count(null)
与
count(fieldName)
2.distinct
与
count
连用
3.group by (
多个字段
)
与
count
实现分组计数
准备表以及数据
[sql]
view plain
copy
— 创建表
CREATE TABLE `tb_student` (
`id` int(11) NOT NULL,
`stu_name` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT ‘学生姓名’,
`tea_name` varchar(255) DEFAULT NULL COMMENT ‘教师姓名’,
`stu_class` varchar(255) DEFAULT NULL COMMENT ‘所在班级名称’,
`stu_sex` varchar(255) DEFAULT NULL COMMENT ‘学生性别’,
`stu_sex_int` tinyint(4) DEFAULT NULL,
PRIMARY KEY (`id`)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
- — 插入数据
INSERT INTO `tb_student` (`id`, `stu_name`, `tea_name`, `stu_class`, `stu_sex`) VALUES (‘0’, ‘小明’, ‘老张’, ‘一班’, ‘男’,0);
INSERT INTO `tb_student` (`id`, `stu_name`, `tea_name`, `stu_class`, `stu_sex`) VALUES (‘1’, ‘小红’, ‘老张’, ‘一班’, ‘女’,0);
INSERT INTO `tb_student` (`id`, `stu_name`, `tea_name`, `stu_class`, `stu_sex`) VALUES (‘2’, ‘小刚’, ‘老王’, ‘一班’, ‘男’,0);
INSERT INTO `tb_student` (`id`, `stu_name`, `tea_name`, `stu_class`, `stu_sex`) VALUES (‘3’, ‘小兰’, ‘老王’, ‘一班’, ‘女’,0);
INSERT INTO `tb_student` (`id`, `stu_name`, `tea_name`, `stu_class`, `stu_sex`) VALUES (‘4’, ‘小军’, ‘老张’, ‘二班’, ‘男’,0);
INSERT INTO `tb_student` (`id`, `stu_name`, `tea_name`, `stu_class`, `stu_sex`) VALUES (‘5’, ‘小芳’, ‘老张’, ‘二班’, ‘女’,0);
INSERT INTO `tb_student` (`id`, `stu_name`, `tea_name`, `stu_class`, `stu_sex`) VALUES (‘6’, ‘小强’, ‘老王’, ‘二班’, ‘男’,0);
INSERT INTO `tb_student` (`id`, `stu_name`, `tea_name`, `stu_class`, `stu_sex`) VALUES (‘7’, ‘小娜’, ‘老王’, ‘二班’, ‘女’,0);
INSERT INTO `tb_student` (`id`, `stu_name`, `tea_name`, `stu_class`, `stu_sex`) VALUES (‘8’, null, null, null, null,null);
问题一：
count(*)
、
count(n)
、
count(null)
与
count(fieldName)
我们看一下执行过程
:
EXPLAIN SELECT count(0)from tb_student
EXPLAIN SELECT count(*)from tb_student
两次执行结果相同，结果为
:
(select_type, table, type,possible_keys, key, key_len, ref, rows, Extra) VALUES ('1', 'SIMPLE','tb_student', 'index', NULL, 'stu_sex_int', '2', NULL, '8', 'Using index');
此次查询使用了索引
stu_sex_int
。我们知道某个字段建立索引之后数据库引擎会对该字段排序并把排序结果作为索引数据存储。查询时候对索引进行
二分查找提高命中率。在使用聚集函数同样也会使用索引。数据库引擎在处理
count
时
,
会
直接从索引数据
(
排序结果中
)
中求排序结果的
id
最大值
。这样一来会大大提高
count
的速度。主键也有索引此处为什么没有使用主键的索引？因为
stu_sex_int
字段长度更短
为
tinyint
类型，查找速度更快。
由此可见
count(*)
和
count(n)
n>=0
的效果相同。
当
count
的表达式为
NULL
时
不会计数
,
所以
count(fieldName)
当
fieldName
为
null
时
不会计数
。比如
·
select count(stu_name) as count from tb_student;
结果: count=8
·
select count(id) as count from tb_student;
结果: count=9
·
select count(null) as count from tb_student ;
结果: count= 0
问题二
: distinct
与
count
连用
distinct
的作用是对
查询结果去重
。
distinctfieldA
那么在查询结果中
fieldA
的值不会重复。当
count
内的表达式是
distinct
时候
,
所表达的意思就是对被
distinct
的字段取值类型计。例如
:
select distinct stu_class from tb_student;
执行结果
:
|
|
|
|
|
|
select count(distinct stu_class) as count fromtb_student;
执行结果
:
|
|
|
|
问题三：
group by (
多个字段
)
与
count
实现分组计数
group by fieldA
是表示根据
fieldA
的不同取值对
查询结果进行分组
。比如对于
tb_student
表，根据
stu_sex
的不同取值
(
男
,
女
)
可把查询结果分成两组。
fieldA
有
n
个不同的取值
,
查询结果就会被分成
n
组。
当分组字段有多个时候
group by fieldA,fieldB
会对
fieldA
和
fieldB
进行排列组合。每个排列组合的
,
结果作为查询一个的一个分组。如果
fileA
的取值有
n
个
fieldB
的取值有
m
个，那么查询结果
将会被分称
m*n
组。
当
count
与
group by
连用时，
count
是对
group by
结果的各个分组进行计数
。
单个分组条件
:
SELECT stu_sex ,COUNT(*) ascount from tb_student GROUP BY stu_sex ;
结果为
:
|
|
|
|
|
|
|
|
|
|
|
|
多个分组条件
:
SELECT stu_sex, stu_class,COUNT(*)AScount
FROM tb_student GROUPBY stu_sex, stu_class
结果为
:
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
COUNT(*)
通常是对
主键进行索引扫描
，而
COUNT(COL)
就不一定了，另外前者是统计表中的所有符合的纪录总数，而后者是计算表中所有符合的
COL
的纪录数。还有有区别的。
COUNT
的时候，如果没有
WHERE
限制的话，
MySQL
直接返回保存有总的行数，而在有
WHERE
限制的情况下，总是需要对
MySQL
进行全表遍历。
具有GROUP BY的MySQL COUNT
我们经常将
COUNT
函数与
GROUP BY
子句结合使用来统计不同分组中的数据。请参见以下
products
表的结构
mysql>descproducts;
+--------------------+---------------+------+-----+---------+------------------+
|Field|Type|Null|Key|Default|Extra|
+--------------------+---------------+------+-----+---------+------------------+
|productCode|varchar(15)|NO|PRI|||
|productName|varchar(70)|NO|MUL|NULL||
|productLine|varchar(50)|NO|MUL|NULL||
|productScale|varchar(10)|NO||NULL||
|productVendor|varchar(50)|NO||NULL||
|productDescription|text|NO||NULL||
|quantityInStock|smallint(6)|NO||NULL||
|buyPrice|decimal(10,2)|NO||NULL||
|MSRP|decimal(10,2)|NO||NULL||
|stockValue|double|YES||NULL|STORED GENERATED|
+--------------------+---------------+------+-----+---------+------------------+
10rowsinset
SQL
例如，要查找每个产品系列中的产品数量，可以使用
COUNT
函数与
GROUP BY
子句，如下查询所示：
SELECTproductline,count(*)FROMproductsGROUPBYproductline;
SQL
执行上面代码，得到以下结果 –
mysql> SELECT productline, count(*) FROM products GROUP BY productline;+------------------+----------+| productline | count(*) |+------------------+----------+| Classic Cars | 38 || Motorcycles | 13 || Planes | 12 || Ships | 9 || Trains | 3 || Trucks and Buses | 11 || Vintage Cars | 24 |+------------------+----------+7 rows in setShell
要查找供应商提供的产品数量，请使用以下查询：
SELECTproductvendor,count(*)FROMproductsGROUPBYproductvendor;
SQL
执行上面代码，得到以下结果 –
mysql> SELECT productvendor, count(*) FROM products GROUP BY productvendor;+---------------------------+----------+| productvendor | count(*) |+---------------------------+----------+| Autoart Studio Design | 8 || Carousel DieCast Legends | 9 || Classic Metal Creations | 10 || Exoto Designs | 9 || Gearbox Collectibles | 9 || Highway 66 Mini Classics | 9 || Min Lin Diecast | 8 || Motor City Art Classics | 9 || Red Start Diecast | 7 || Second Gear Diecast | 8 || Studio M Art Models | 8 || Unimax Art Galleries | 8 || Welly Diecast Productions | 8 |+---------------------------+----------+13 rows in setShell
要查找哪个供应商提供至少
9
个产品，您可以
使用
HAVING
子句在
COUNT
函数中，如以下查询语句所示：
SELECTproductvendor,count(*)FROMproductsGROUPBYproductvendor
HAVINGcount(*)>=9;
SQL
执行上面代码，得到以下结果 –
mysql> SELECT productvendor, count(*) FROM products GROUP BY productvendorHAVING count(*) >= 9;+--------------------------+----------+| productvendor | count(*) |+--------------------------+----------+| Carousel DieCast Legends | 9 || Classic Metal Creations | 10 || Exoto Designs | 9 || Gearbox Collectibles | 9 || Highway 66 Mini Classics | 9 || Motor City Art Classics | 9 |+--------------------------+----------+6rows in set
实例练习：
1、主人的数量：
select count(owner) from pet；
错误的写法：SELECT owner, COUNT(*) FROM pet;
2、每个主人拥有的宠物总量：
select owner count(*) from pet group by owner;
3、每种动物的数量：
select species count(*) from pet group by species;
4、每种性别的动物数：
select sex count(*) from petgroup by sex
5、按种类和性别组合的动物数
select species,sex,count(*) from pet group by species,sex;
6、只对狗和猫进行查询统计时：
Select species,sex,count(*) from pet
where species=’dog’ or species=’cat’
group by species,sex;
7、统计已知性别的动物的数量
Select sex count(sex) from pet
Where sex is not null
Group by sex;
8, Distinct的用法
① 去重，比如：selectcount(distinct id) from pet
是计算不同id的宠物id的个数
② 要返回不同id的具体值的时候：
Select distinct idfrom pet;
返回不同id的宠物的具体值
4、
去重：SELECT DISTINCT 列名称 FROM 表名称
select distinct salary from salaries
where salary.to_date=’9999-01-01′
order by salaries.salary desc;
或者：group by
select salary from salarieswhere to_date=’9999-01-01′
group by salary
order by salary desc;
二、 group by 与order by
group by:从英文上理解就是分组的意思。需要和聚合函数（例如：max（），count（），avg（）等）配合使用，使用时至少有一个分组标识字段（例如某一列的列名）。
order by :从英文上理解就是排序。一般后面跟上某一列的列名，然后查询到的数据会以该列的大小顺序进行排序显示，默认的是ASC（从小到大排列），desc（大到小排列）
where: 对查询到的内容进行筛选，where后面跟上限定条件,where使用在分组和排序之后。
having:作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函数，使用having 条件显示特定的组，也可以使用多个分组标准进行分组。
group by按照查询结果集中的某一列（或多列），进行分组，值相等的为一组。
1）细化集函数（count，sum，avg，max，min）的作用对象：
未对查询结果分组，集函数将作用于整个查询结果。
对查询结果分组后，集函数将分别作用于每个组。
例如：
Selsct deptmax(salary) as maxmum
from staff
Group by dept;
统计结果为每个部门的薪水最高值。
2）按时间顺序排列：
select * from staff
Order by hiredate;
3）只展示开发部的信息
Select * from staff
Where dept = ‘开发部’
4）筛选出分组后的max大于3000的一组
Select dept, max(salary) as maxmum
from staff
Group by dept
Having max(salary)>3000;
5）
倒序显示日期大于 2010-10-03中，每个部门中工资最高且大于2000的数据
Select deptmax(salary) as maxmum from satff
Wherehiredate>’2010-10-03’
Group by dept having max(salary)>2000
Order by maxmum desc;
6) 查询公司2010年入职的各个部门每个级别里的最高薪水
Select dept,level,max(salary) as maxmum from staff
Where hiredate>=2010-01-01
Group by dept,level
7) 查找雇员数超过2个的部门的最高和最低薪水：
Select dept,count(*),max(salary) as maxmum,min(salary) as minmum
from staff
Group by dept
Having count(*)>2
8) 查找雇员平均工资大于3000的部门的最高薪水和最低薪水：
Select dept,max(salary) as maxmum,min(salary) as minmum
From staff
Group by dept
having avg(salary)>3000