四、Delete

4.1 删除数据

DELETE FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...];

SQL中大写的表示关键字，[ ]中代表的是可选项

在删除数据之前需先找到待删除的记录，delete语句中的where、order by和limit用来定位数据

删除孙悟空同学的考试成绩

在删除数据之前，先查看孙悟空同学相关信息，然后在delete语句中指明删除孙悟空对应的记录，并在删除后再次查看确保数据被删除

删除整张表数据

创建一张测试表，表中包含一个自增长的主键id和姓名

向表中插入一些测试数据用于删除

delete语句中只指明要删除数据的表名，而不通过where、order by和limit指明筛选条件，这时将会删除整张表的数据

再向表中插入一些数据，在插入数据时不指明自增长字段的值，这时会发现插入数据对应的自增长id值是在之前的基础上继续增长的

查看创建表时的相关信息，存在AUTO_INCREMENT=n的字段，该字段表示下一次插入数据时自增长字段的值应该为n

通过delete语句删除整表数据，不会重置AUTO_INCREMENT=n字段，因此删除整表数据后插入数据，对应的自增长id值会在原来的基础上继续增长

4.2 截断表

TRUNCATE [TABLE] table_name;

SQL中大写的表示关键字，[ ]中代表的是可选项

truncate只能对整表操作，不能像delete一样针对部分数据操作

truncate实际上不对数据操作，所以比delete更快

truncate在删除数据时不经过真正的事务，所以无法回滚

truncate会重置AUTO_INCREMENT=n字段

创建一张测试表，表中包含一个自增长的主键id和姓名

向表中插入一些测试数据用于删除

在truncate语句中只指明要删除数据的表名，此时会删除整张表数据，但由于truncate不对数据操作，因此执行truncate语句后看到影响行数为0

再向表中插入一些数据，在插入数据时不指明自增长字段的值，这时会发现插入数据对应的自增长id值是重新从1开始增长的

查看创建表时的相关信息时也可以看到，有一个AUTO_INCREMENT=n字段，该字段表示下一次插入数据时自增长字段的值应该为n

使用truncate语句删除整表数据时，会重置AUTO_INCREMENT字段，因此截断表后插入数据对应的自增长id值会重新从1开始增长

五、插入查询结果

INSERT [INTO] table_name [(column1 [, column2] ...)] SELECT ... [WHERE ...] [ORDER BY ...] [LIMIT ...];

SQL中大写的表示关键字，[ ]中代表的是可选项

SQL的作用是将筛选出来的记录插入到指定的表中

SQL中的column，表示将筛选出的记录的各个列插入到表中的哪一列

删除表中重复的记录，重复的数据只能有一份

创建一张测试表，表中包含id和姓名

向测试表中插入一些测试数据，数据中存在重复的记录

现在要求删除测试表中重复的数据，思路如下：

创建一张临时表，该表的结构与测试表的结构相同

以去重的方式查询测试表中的数据，并将查询结果插入到临时表中

将测试表重命名为其他名字，再将临时表重命名为测试表的名字，实现原子去重操作

由于临时表的结构与测试表相同，因此在创建临时表的时候可以借助like进行创建

通过插入查询语句将去重查询后的结果插入到临时表中，由于临时表和测试表的结构相同，并且select进行的是全列查询，因此在插入时不用在表名后指明column列表

将测试表重命名为其他名字（相当于对去重前的数据进行备份，若不需要可直接删除），将临时表重命名为测试表的名字，这时便完成了表中数据的去重操作

六、聚合函数

聚合函数对一列值执行计算并返回单一的值

统计班级共有多少同学

使用之前的学生表进行演示，表中数据如下：

在select语句中使用count函数，并将*作为参数传递给count函数，这时便能统计出表中的记录条数

在select语句中使用count函数，并将表达式作为参数传递给count函数，这时也可以统计出表中的记录条数

相当于在查询表中数据时，自行新增了一列列名为特定表达式的列，用count函数统计该列中有多少个数据，等价于统计表中有多少条记录

统计班级收集的QQ号有多少个

在select语句中使用count函数统计qq列中数据的个数，这时便能统计出表中QQ号的个数

若count函数的参数是一个确定的列名，那么count函数将会忽略该列中的NULL值

统计本次考试数学成绩的分数个数

使用之前的成绩表来进行演示

在select语句中使用count函数统计math列中数据的个数，这时便能统计出表中的数学成绩的个数

统计本次考试数学成绩的分数个数（去重）

在使用count函数时（包括其他聚合函数），在传递的参数之前加上distinct，这时便能统计出表中数学成绩去重后的个数

统计数学成绩总分

在select语句中使用sum函数统计math列中数据的总和，这时便能统计出表中的数学成绩的总和

统计不及格的数学成绩总分

在where子句中指明筛选条件为数学成绩小于60分，在select语句中使用sum函数统计math列中数据的总和

由于当前没有数学不及格的同学，因此求和结果为NULL

统计平均总分

在select语句中使用avg函数计算总分的平均值

返回英语最高分

在select语句中使用max函数查询英语成绩最高分

返回70分以上的英语最低分

在where子句中指明筛选条件为英语成绩大于70分，在select语句中使用min函数查询英语成绩最低分

七、分组查询

SELECT column1 [, column2], ... FROM table_name [WHERE ...] GROUP BY column [, ...] [order by ...] [LIMIT ...];

SQL中大写的表示关键字，[ ]中代表的是可选项

查询SQL中各语句的执行顺序为：where、group by、select、order by、limit

group by后面的列名，表示按照指定列进行分组查询

7.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);

显示每个部门的平均工资和最高工资

在group by子句中指明按照部门号进行分组，在select语句中使用avg函数和max函数，分别查询每个部门的平均工资和最高工资

上述SQL会先将表中的数据按照部门号进行分组，然后各自在组内做聚合查询得到每个组的平均工资和最高工资

显示每个部门的每种岗位的平均工资和最低工资

在group by子句中指明依次按照部门号和岗位进行分组，在select语句中使用avg函数和min函数，分别查询每个部门的每种岗位的平均工资和最低工资

group by子句中可以指明按照多个字段进行分组，各个字段之间使用逗号隔开，分组优先级与书写顺序相同

如上述SQL中，当两条记录的部门号相同时，将会继续按照岗位进行分组

7.2 HAVING条件

SELECT ... FROM table_name [WHERE ...] [GROUP BY ...] [HAVING ...] [order by ...] [LIMIT ...];

SQL中大写的表示关键字，[ ]中代表的是可选项

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子句筛选若干条记录进行显示

显示平均工资低于2000的部门和它的平均工资

先统计每个部门的平均工资

然后通过having子句筛选出平均工资低于2000的部门

MySQL表CRUD（三）

四、Delete

4.1 删除数据

4.2 截断表

五、插入查询结果

六、聚合函数

七、分组查询

7.1 分组查询测试表 —— 雇员信息表

7.2 HAVING条件

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像