VALUES('张三风',22,'北京',1563.51,'男',2);

INSERT employee(username,age,addr,salary,sex,depid)

VALUES('章子怡',14,'云南',6454.14,'女',1);

INSERT employee(username,age,addr,salary,sex,depid)

VALUES('丽丽',41,'广州',1201.45,'男',2);

INSERT employee(username,age,addr,salary,sex,depid)

VALUES('李成',52,'深圳',6914.86,'男',2);

INSERT employee(username,age,addr,salary,sex,depid)

VALUES('程程',25,'成都',5631.12,'女',3);

INSERT employee(username,age,addr,salary,sex,depid)

VALUES('白静',65,'南阳',4823.45,'男',4);

INSERT employee(username,age,addr,salary,sex,depid)

VALUES('程秀碧',48,'广汉',5523.36,'女',4);

按性别分组

SELECT * FROM employee GROUP BY sex;

按地址分组

SELECT * FROM employee GROUP BY addr;

按部门编号

SELECT * FROM employee GROUP BY depid;

根据性别和地址分组

SELECT * FROM employee GROUP BY sex,addr;

SELECT * FROM employee GROUP BY addr,sex;

查询结果跟顺序有关

--分组查询配合GROUP_CONCAT()

--按性别分组，得到每个组中人员的名称

SELECT * ,GROUP_CONCAT(username) FROM employee GROUP BY sex;

SELECT * ,GROUP_CONCAT(username) AS usernames FROM employee GROUP BY sex;

--配合聚合函数使用

COUNT():统计记录数目

SUM():求字段和

AVG():求字段平均值

MAX():

MIN():

--统计员工表中员工数目，以及薪水总和、最大最小值

SELECT id AS '编号',username AS '用户名',COUNT(*) AS 'totalNUMS',SUM(salary) AS '总薪水'

,AVG(salary) AS '平均薪水',MAX(salary) AS '最高薪水',MIN(salary) AS '最低薪水'FROM employee;

SELECT id AS '编号',username AS '用户名',COUNT(*) AS 'totalNUMS',SUM(salary) AS '总薪水'

,AVG(salary) AS '平均薪水',MAX(salary) AS '最高薪水',MIN(salary) AS '最低薪水'FROM employee\G;

COUNT(*),COUNT(字段名称):

*无论字段值是否为NULL都统计

字段名称：不统计NULL记录。

INSERT employee(username,age,addr,salary,sex,depid)

VALUES('程秀碧',NULL,'广汉',5523.36,'女',4);

SELECT COUNT(*) FROM employee;

SELECT COUNT(age) FROM employee;

--按性别分组，统计出每个组找那个年龄最大最小，最高薪水，每个组人数，以及平均薪水

SELECT id,sex,MAX(age) AS max_age,MIN(age) AS min_age,MAX(salary) AS max_salary,COUNT(*) AS total,AVG(salary) AS avg_salary FROM employee GROUP BY sex;

SELECT id,sex,MAX(age) AS max_age,MIN(age) AS min_age,MAX(salary) AS max_salary,COUNT(*) AS total,AVG(salary) AS avg_salary,GROUP_CONCAT(username) AS users FROM employee GROUP BY sex;

3》HAVING子句二次筛选

--按照性别分组

SELECT id,sex,COUNT(*) AS total FROM employee GROUP BY sex;

分组后组中人数大于3的有

SELECT id,sex,COUNT(*) AS total FROM employee GROUP BY sex HAVING COUNT(*)>6;

--按地址分组

SELECT id,addr,MIN(age) AS min_age FROM employee GROUP BY addr;

分组后要求组中最小人员年龄大于18；

SELECT id,addr,MIN(age) AS min_age FROM employee GROUP BY addr HAVING MIN(age)>25;

SELECT exp FROM tb_name

[WHERE 条件]

[GROUP BY 分组[HAVING对分组结果进行二次筛选]]

[ORDER BY 字段名称]

[LIMIT 限制显示条数 ]

4》对分组结果排序

ORDER BY 字段名称|位置 ASC|DESC;

--按照ID由小到大排序

SELECT * FROM department ORDER BY id ASC;

SELECT * FROM department ORDER BY id ;

默认升序，所以查询结果一样

SELECT * FROM department ORDER BY id DESC;

--按位置

SELECT * FROM department ORDER BY 1 DESC;

SELECT * FROM employee ORDER BY age DESC;

SELECT * FROM employee ORDER BY 3 DESC;

当有相同年龄时，按出现顺序进行排序

--按多个字段排序

ORDER BY 字段名称1，字段名称2

SELECT * FROM employee ORDER BY age DESC,id DESC;

SELECT * FROM employee ORDER BY id DESC,age DESC;

查询结果与字段名称的顺序有关

5》LIMIT

LIMIT 值：

代表显示前几条记录

LIMIT 偏移量，显示记录条数

实现分页的核心是通过LIMIT语句

--显示前5条

SELECT * FROM employee LIMIT 5;

SELECT * FROM employee ORDER BY id DESC LIMIT 5;

第一条记录偏移量为0

SELECT * FROM employee LIMIT 0, 5;

--下一页，显示后五条

SELECT * FROM employee LIMIT 5, 5;

SELECT * FROM employee LIMIT 10, 5;

--所有条件