目录
表的增删改查(二)
一、聚合函数
聚合函数对一组值执行计算并返回单一的值,常用的聚合函数如下:
函数 | 说明 |
---|---|
COUNT([DISTINCT] expr)
| 返回查询到的数据的数量 |
SUM([DISTINCT] expr)
| 返回查询到的数据的总和,不是数字没有意义 |
AVG([DISTINCT] expr)
| 返回查询到的数据的平均值,不是数字没有意义 |
MAX([DISTINCT] expr)
| 返回查询到的数据的最大值,不是数字没有意义 |
MIN([DISTINCT] expr)
| 返回查询到的数据的最小值,不是数字没有意义 |
聚合函数可以在select语句中使用,此时select每处理一条记录时都会将对应的参数传递给这些聚合函数。
我们这里使用之前创建过的students表,来进行测试:
1. 统计班级有多少学生
使用
*
做统计
在select语句中使用count函数,并将*
作为参数传递给count函数,这时便能统计出表中的记录条数。如下:
使用表达式做统计
2. 统计班级收集的qq号有多少
3. 统计本次考试的数学成绩分数个数(去重)
4. 统计数学成绩总分
统计不及格的数学成绩总分
5. 统计平均总分
6. 返回英语最高分
7. 返回 >70 分以上的数学最低分
二、分组查询(group by子句)
语法如下:
SELECT column1 [, column2], ... FROM table_name [WHERE ...]
GROUP BY column [, ...] [order by ...] [LIMIT ...];
说明一下:
- 查询SQL中各语句的执行顺序为:where、group by、select、order by、limit。
- group by后面的列名,表示按照指定列进行分组查询。
1. 创建一个雇员信息表(准备工作)
雇员信息表中包含三张表,分别是员工表(emp)、部门表(dept)和工资等级表(salgrade)。
员工表(emp)中包含如下字段:
- 雇员编号(empno)。
- 雇员姓名(ename)。
- 雇员职位(job)。
- 雇员领导编号(mgr)。
- 雇佣时间(hiredate)。
- 工资月薪(sal)。
- 奖金(comm)。
- 部门编号(deptno)。
部门表(dept)中包含如下字段:
- 部门编号(deptno)。
- 部门名称(dname)。
- 部门所在地点(loc)。
工资等级表(salgrade)中包含如下字段:
- 等级(grade)。
- 此等级最低工资(losal)。
- 此等级最高工资(hisal)。
雇员信息表的SQL如下:
DROP database IF EXISTS `scott`;
CREATE database IF NOT EXISTS `scott` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `scott`;
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
`deptno` int(2) unsigned zerofill NOT NULL COMMENT '部门编号',
`dname` varchar(14) DEFAULT NULL COMMENT '部门名称',
`loc` varchar(13) DEFAULT NULL COMMENT '部门所在地点'
);
DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
`empno` int(6) unsigned zerofill NOT NULL COMMENT '雇员编号',
`ename` varchar(10) DEFAULT NULL COMMENT '雇员姓名',
`job` varchar(9) DEFAULT NULL COMMENT '雇员职位',
`mgr` int(4) unsigned zerofill DEFAULT NULL COMMENT '雇员领导编号',
`hiredate` datetime DEFAULT NULL COMMENT '雇佣时间',
`sal` decimal(7,2) DEFAULT NULL COMMENT '工资月薪',
`comm` decimal(7,2) DEFAULT NULL COMMENT '奖金',
`deptno` int(2) unsigned zerofill DEFAULT NULL COMMENT '部门编号'
);
DROP TABLE IF EXISTS `salgrade`;
CREATE TABLE `salgrade` (
`grade` int(11) DEFAULT NULL COMMENT '等级',
`losal` int(11) DEFAULT NULL COMMENT '此等级最低工资',
`hisal` int(11) DEFAULT NULL COMMENT '此等级最高工资'
);
insert into dept (deptno, dname, loc)
values (10, 'ACCOUNTING', 'NEW YORK');
insert into dept (deptno, dname, loc)
values (20, 'RESEARCH', 'DALLAS');
insert into dept (deptno, dname, loc)
values (30, 'SALES', 'CHICAGO');
insert into dept (deptno, dname, loc)
values (40, 'OPERATIONS', 'BOSTON');
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, null, 20);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, null, 20);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, null, 30);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, null, 10);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, null, 20);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7839, 'KING', 'PRESIDENT', null, '1981-11-17', 5000, null, 10);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7844, 'TURNER', 'SALESMAN', 7698,'1981-09-08', 1500, 0, 30);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, null, 20);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, null, 30);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, null, 20);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, null, 10);
insert into salgrade (grade, losal, hisal) values (1, 700, 1200);
insert into salgrade (grade, losal, hisal) values (2, 1201, 1400);
insert into salgrade (grade, losal, hisal) values (3, 1401, 2000);
insert into salgrade (grade, losal, hisal) values (4, 2001, 3000);
insert into salgrade (grade, losal, hisal) values (5, 3001, 9999);
上述SQL中创建了一个名为scott的数据库,在该数据库中分别创建了部门表(dept)、员工表(emp)和工资等级表(salgrade),并分别向三张表中插入了一些数据用于查询。
将上述SQL保存到文件中,然后在MySQL中使用source命令依次执行文件中的SQL。如下:
执行完文件中的SQL后查看数据库,就能看到多了一个名为scott的数据库。如下:
进入该数据库,在该数据库中就可以看到雇员信息表中的三张表。如下:
部门表(dept)的表结构和表中的内容如下:
员工表(emp)的表结构和表中的内容如下:
工资等级表(salgrade)的表结构和表中的内容如下:
2. 显示每个部门的平均工资和最高工资
在group by子句中指明按照部门号进行分组,在select语句中使用avg函数和max函数,分别查询每个部门的平均工资和最高工资。如下:
上述SQL会先将表中的数据按照部门号进行分组,然后各自在组内做聚合查询得到每个组的平均工资和最高工资。
3. 显示每个部门的每种岗位的平均工资和最低工资
在group by子句中指明依次按照部门号和岗位进行分组,在select语句中使用avg函数和min函数,分别查询每个部门的每种岗位的平均工资和最低工资。如下:
说明一下:
- group by子句中可以指明按照多个字段进行分组,各个字段之间使用逗号隔开,分组优先级与书写顺序相同。
- 比如上述SQL中,当两条记录的部门号相同时,将会继续按照岗位进行分组。
4. HAVING条件
语法如下:
SELECT ... FROM table_name [WHERE ...] [GROUP BY ...]
[HAVING ...] [order by ...] [LIMIT ...];
说明一下:
- SQL中各语句的执行顺序为:where、group by、select、having、order by、limit。
- having子句中可以指明一个或多个筛选条件。
having子句和where子句的区别:
- where子句放在表名后面,而having子句必须搭配group by子句使用,放在group by子句的后面。
- where子句是对整表的数据进行筛选,having子句是对分组后的数据进行筛选。
- where子句中不能使用聚合函数和别名,而having子句中可以使用聚合函数和别名。
查询数据时,SQL中各语句的执行顺序如下:
- 根据where子句筛选出符合条件的记录。
- 根据group by子句对数据进行分组。
- 将分组后的数据依次执行select语句。
- 根据having子句对分组后的数据进行进一步筛选。
- 根据order by子句对数据进行排序。
- 根据limit子句筛选若干条记录进行显示。
1. 显示平均工资低于2000的部门和它的平均工资
把问题化繁为简:
- 显示部分和它的平均工资。
- 以上面的为基础在进行筛选出,平均工资低于200的部门。
以上的内容就是MySQL的基本的增删查改操作(这里是基于单表的操作,后续会有复合查询,也就是多表操作了),我们可以发现SQL语句从刚开始到现在,越来越难一些,这里我给大家一些编写SQL操作建议:
- 根据题目(要求),我们要知道从哪一张表拿数据,也就是from table_name;
- 看清楚需要查询的字段有哪些,需不需要起别名、需不需要聚合;
- 理清筛选条件;
对于上面的3个步骤还是可以进行细分的,你可以将题目化繁为简,先查询全部,再一步一步的进行添加筛选条件,最后达到效果;