1、概述
在MySQL的查询中,select主要有5中子句类型,主要包括
- where 条件查询
- group by 分组查询
- having 筛选
- order by 排序
- limit 分页
准备演示的表结构及示例数据
CREATE TABLE "department" (
"did" int(11) NOT NULL AUTO_INCREMENT,
"dname" varchar(100) NOT NULL,
"description" varchar(200) DEFAULT NULL,
"manager_id" int(11) DEFAULT NULL,
PRIMARY KEY ("did")
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE "employee" (
"eid" int(11) NOT NULL AUTO_INCREMENT,
"ename" varchar(100) NOT NULL,
"gender" char(1) NOT NULL DEFAULT '男',
"card_id" char(18) DEFAULT NULL,
"‘mid‘" int(11) DEFAULT NULL,
"dept_id" int(11) DEFAULT NULL,
PRIMARY KEY ("eid"),
UNIQUE KEY "card_id" ("card_id"),
KEY "dept_id" ("dept_id"),
CONSTRAINT "employee_ibfk_1" FOREIGN KEY ("dept_id") REFERENCES "department" ("did")
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE "salary" (
"eid" int(11) NOT NULL,
"basic_salary" decimal(10,2) DEFAULT NULL,
"performance_salary" decimal(10,2) DEFAULT NULL,
PRIMARY KEY ("eid"),
CONSTRAINT "salary_ibfk_1" FOREIGN KEY ("eid") REFERENCES "employee" ("eid")
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `department`(dname,description) VALUES ( '研发部', '业务平台研发');
INSERT INTO `department` (dname,description) VALUES ( '市场部', '市场推广');
INSERT INTO `department` (dname,description) VALUES ( '财务部', '财务管理');
INSERT INTO `employee` (ename,gender,card_id,‘mid‘,dept_id) VALUES ('张三', '男', '123456789012345678', null, '1');
INSERT INTO `employee` (ename,gender,card_id,‘mid‘,dept_id) VALUES ('李四', '女', '123456789012345665', null, '2');
INSERT INTO `employee` (ename,gender,card_id,‘mid‘,dept_id) VALUES ('赵柳', '男', '123456789012235678', '2', '2');
INSERT INTO `employee` (ename,gender,card_id,‘mid‘,dept_id) VALUES ('王五', '男', '123456789012115678', '1', '1');
INSERT INTO `employee` (ename,gender,card_id,‘mid‘,dept_id) VALUES ('谷雨', '男', '123456789012115978', '1', '1');
INSERT INTO `salary` VALUES ('1', '12000.00', '6000.00');
INSERT INTO `salary` VALUES ('2', '9000.00', '8000.00');
INSERT INTO `salary` VALUES ('3', '11000.00', '5500.00');
INSERT INTO `salary` VALUES ('4', '5800.00', '7800.00');
2、where 条件查询
where :从原表的记录中进行筛选
3、group by 分组查询
在实际的应用中,需要进行一些汇总操作,例如:统计整个公司的人数或者统计每一个部门的人数等。
常用的聚合函数
函数名称 | 函数说明 |
---|---|
AVG([DISTINCT] expr) | 返回expr的平均值 |
COUNT([DISTINCT] expr) | 返回expr的非NULL值的数目 |
MIN([DISTINCT] expr) | 返回expr的最小值 |
MAX([DISTINCT] expr) | 返回expr的最大值 |
SUM([DISTINCT] expr) | 返回expr的平均值 |
#聚合函数
#AVG(【DISTINCT】 expr) 返回 expr 的平均值
mysql> select avg(basic_salary) from salary;
+-------------------+
| avg(basic_salary) |
+-------------------+
| 9450.000000 |
+-------------------+
1 row in set
#COUNT(【DISTINCT】 expr)返回 expr 的非 NULL 值的数目
#统计员工总人数
#count(*)统计的是记录数
mysql> select count(*) from employee;
+----------+
| count(*) |
+----------+
| 5 |
+----------+
1 row in set
mysql>
#统计员工表的员工所在部门数
#统计的是非NULL值
mysql> select count(dept_id) from employee;
+----------------+
| count(dept_id) |
+----------------+
| 4 |
+----------------+
1 row in set
#统计的是非 NULL 值,并且去重
mysql> select count( distinct dept_id) from employee;
+--------------------------+
| count( distinct dept_id) |
+--------------------------+
| 2 |
+--------------------------+
1 row in set
#MIN(【DISTINCT】 expr)返回 expr 的最小值
#查询最低基本工资值
mysql> select min(basic_salary) from salary;
+-------------------+
| min(basic_salary) |
+-------------------+
| 5800 |
+-------------------+
1 row in set
#MAX(【DISTINCT】 expr)返回 expr 的最大值
#查询最高基本工资值
mysql> select max(basic_salary) from salary;
+-------------------+
| max(basic_salary) |
+-------------------+
| 12000 |
+-------------------+
1 row in set
#查询最高基本工资与最低基本工资的差值
mysql> select max(basic_salary)-min(basic_salary) from salary;
+-------------------------------------+
| max(basic_salary)-min(basic_salary) |
+-------------------------------------+
| 6200 |
+-------------------------------------+
1 row in set
#SUM(【DISTINCT】 expr)返回 expr 的总和
#查询基本工资总和
mysql> select sum(basic_salary) from salary;
+-------------------+
| sum(basic_salary) |
+-------------------+
| 37800.00 |
+-------------------+
1 row in set
group by + 聚合函数
#group by + 聚合函数
#统计每个部门的人数
mysql> select dept_id, count(*) from employee group by dept_id;
+---------+----------+
| dept_id | count(*) |
+---------+----------+
| 1 | 2 |
| 2 | 2 |
+---------+----------+
2 rows in set
#统计每个部门的平均基本工资
mysql> select emp.dept_id,avg(s.basic_salary) from employee emp,salary s where emp.eid=s.eid group by emp.dept_id;
+---------+---------------------+
| dept_id | avg(s.basic_salary) |
+---------+---------------------+
| 1 | 8900.000000 |
| 2 | 10000.000000 |
+---------+---------------------+
2 rows in set
#统计每个部门基本工资最高者
mysql> select emp.dept_id, max(s.basic_salary) from employee emp,salary s where emp.eid=s.eid group by emp.dept_id;
+---------+---------------------+
| dept_id | max(s.basic_salary) |
+---------+---------------------+
| 1 | 12000 |
| 2 | 11000 |
+---------+---------------------+
2 rows in set
#统计每个部门基本工资之和
mysql> select emp.dept_id, sum(s.basic_salary) from employee emp,salary s where emp.eid=s.eid group by emp.dept_id;
+---------+---------------------+
| dept_id | sum(s.basic_salary) |
+---------+---------------------+
| 1 | 17800.00 |
| 2 | 20000.00 |
+---------+---------------------+
2 rows in set
注意事项
1、用count(*)、count(1),那个好呢?
- 对于myisam引擎的表来说,没有区别,因为myisam引擎内部有一个计数器在维护着行数
- Innodb的表,用count(*) 直接读行数,效率很低,因为Innodb真的要去数一遍所有的数据。
2、关于MySQL的group by的特殊说明
在SELECT列表中所有未包含在组函数中的列都应该是包含在group by 子句中的,也就是说,SELECT列表中最好不要出现group by子句中没有的列。
4、having筛选
having与where类似,可以筛选数据
having与where的不同点
- where针对表中的列发挥作用,查询数据;having针对查询结果中的列发挥作用,筛选数据
- where后面不能写分组函数;having后面可以使用分组函数
- having只用于group by 分组统计语句
# 按照部门统计员工人数,仅显示部门人数少于3人的
mysql> SELECT dept_id,COUNT(*) AS c
FROM employee
WHERE dept_id IS NOT NULL
GROUP BY dept_id
HAVING c <3;
+---------+---+
| dept_id | c |
+---------+---+
| 2 | 2 |
+---------+---+
1 row in set
#查询每个部门的平均工资,并且仅显示平均工资高于8000
mysql> select emp.dept_id,avg(s.basic_salary) as avg_salary
-> from employee emp,salary s
-> where emp.eid=s.eid and dept_id is not null
-> group by emp.dept_id
-> having avg_salary>8000;
+---------+--------------+
| dept_id | avg_salary |
+---------+--------------+
| 1 | 8900.000000 |
| 2 | 10000.000000 |
+---------+--------------+
2 rows in set
mysql>
5、order by 排序
(1)按一个或多个字段对查询结果进行排序
用法:order by col1,col2,col3,……
说明:先按col1排序,如果col1相同就按照col2排序,以此类推
col1,col2,col3可以是select后面的字段也可以不是
(2)默认是升序,也可以在字段后面加asc显示说明是升序,desc为降序
如果两个字段排序不一样,例如
order by 字段1 asc, 字段2 desc
(3)order by 后面除了跟1个或多个字段,还可以写表达式、函数、别名等
#排序
# 查询员工基本工资,按照基本工资升序排序,如果工资相同,按照eid升序排序
mysql> select employee.eid,basic_salary from employee inner join salary on employee.eid=salary.eid order by basic_salary,eid;
+-----+--------------+
| eid | basic_salary |
+-----+--------------+
| 4 | 5800 |
| 2 | 9000 |
| 3 | 11000 |
| 1 | 12000 |
+-----+--------------+
4 rows in set
#查询员工基本工资,按照基本工资降序排序,如果工资相同,按照eid排列
mysql> select employee.eid,basic_salary from employee inner join salary on employee.eid=salary.eid order by basic_salary desc,eid asc;
+-----+--------------+
| eid | basic_salary |
+-----+--------------+
| 1 | 12000 |
| 3 | 11000 |
| 2 | 9000 |
| 4 | 5800 |
+-----+--------------+
4 rows in set
mysql>
#统计每个部门的平均基本工资,并按照平均工资降序排列
mysql> select emp.dept_id,avg(s.basic_salary) from employee emp,salary s where emp.eid=s.eid group by emp.dept_id order by avg(s.basic_salary) desc;
+---------+---------------------+
| dept_id | avg(s.basic_salary) |
+---------+---------------------+
| 2 | 10000.000000 |
| 1 | 8900.000000 |
+---------+---------------------+
2 rows in set
mysql>
6、limit 分页
limit m,n
- m表示从下标为m的记录开始查询,第一条记录下标为0
- n表示取出n条出来,如果从m开始不够n条记录,就有几条取几条
- m = (page-1)*n,其中,page是页码,n表示每页显示的条目
如果第一页:limit 0,n
如果第二页:limit n,n
依次类推,得出公式 limit (page-1)*n,n
#分页
#查询员工信息,每页显示 3 条,第二页
mysql> select * from employee limit 3,3;
+-----+-------+--------+--------------------+-------+---------+
| eid | ename | gender | card_id | ‘mid‘ | dept_id |
+-----+-------+--------+--------------------+-------+---------+
| 4 | 王五 | 男 | 123456789012115678 | 1 | 1 |
| 5 | 谷雨 | 男 | 123456789012115978 | 1 | 1 |
+-----+-------+--------+--------------------+-------+---------+
2 rows in set
#统计每个部门的平均基本工资,并显示前三名
mysql> select emp.dept_id,avg(s.basic_salary) from employee emp,salary s where emp.eid=s.eid group by emp.dept_id order by avg(s.basic_salary) desc limit 0,3;
+---------+---------------------+
| dept_id | avg(s.basic_salary) |
+---------+---------------------+
| 2 | 10000.000000 |
| 1 | 8900.000000 |
+---------+---------------------+
2 rows in set
mysql>