Update

语法：

UPDATE table_name SET column = expr [, column = expr ...]
[WHERE ...] [ORDER BY ...] [LIMIT ...]

将孙悟空同学的数学成绩修改为80分

将曹孟德同学的数学成绩变更为 60 分，语文成绩变更为 70 分

将总成绩倒数前三的 3 位同学的数学成绩加上 30 分

将所有同学的语文成绩更新为原来的 2 倍

注意：更新全表的语句慎用！

Delete

删除数据

语法：

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

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

删除整张表数据

注意：删除整表操作要慎用！

创建一个用来测试删除的表

插入一些数据

查看插入的数据

执行删除表中数据SQL、并查看删除后的表

再插入一条数据，我们发现自增 id 在原值上增长

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

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

截断表

语法：

TRUNCATE [TABLE] table_name

• 只能对整表操作，不能像 DELETE 一样针对部分数据操作；
• 实际上 TRUNCATE 不对数据操作，所以比 DELETE 更快，但是TRUNCATE在删除数据的时候，并不经过真正的事务，所以无法回滚
• 会重置 AUTO_INCREMENT 项

创建一个用来测试截断的表

插入测试数据

查看插入的数据

截断整表数据、并查看表中数据

再插入2条数据，自增 id 在重新增长

插入查询结果

语法：

INSERT INTO table_name [(column [, column ...])] SELECT ...

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

创建一张测试表

插入一些重复测试数据

删除测试表中重复的数据：思路

1. 创建一张空表no_duplicate_table，结构和duplicate_table一样
   

2. 将 duplicate_table的去重数据插入到 no_duplicate_table
   

3. 通过重命名表，实现原子的去重操作
   

查看原表数据

聚合函数

函数	说明
COUNT([DISTINCT] expr)	返回查询到的数据 数量
SUM([DISTINCT] expr)	返回查询到的数据的 总和，不是数字没有意义
AVG([DISTINCT] expr)	返回查询到的数据的 平均值，不是数字没有意义
MAX([DISTINCT] expr)	返回查询到的数据的 最大值，不是数字没有意义
MIN([DISTINCT] expr)	返回查询到的数据的 最小值，不是数字没有意义

统计班级共有多少同学

统计班级收集的 qq 号有多少

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

统计的是去重成绩数量

统计数学成绩总分

不及格 < 60 的总分，没有结果，返回 NULL

统计数学成绩平均分

返回英语最高分

返回 > 70 分以上的数学最低分

group by子句的使用

在select中使用group by 子句可以对指定列进行分组查询

select column1, column2, .. from table group by column;

创建一个雇员信息表

• EMP员工表
• DEPT部门表
• SALGRADE工资等级表

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

scott数据库下有三张表，部门表（dept）、员工表（emp）和工资等级表（salgrade）

• 部门表（dept）
  

• 员工表（emp）
  

• 工资等级表（salgrade）
  

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

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

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

• 统计各个部门的平均工资，先聚合结果
  

• 再对聚合的结果进行判断having和group by配合使用，对group by结果进行过滤

having

• having是对聚合后的统计数据，条件筛选；

having和where的区别

• where子句放在表名后面，而having子句必须搭配group by子句使用，放在group by子句的后面
• where子句是对整表的数据进行筛选，having子句是对分组后的数据进行筛选
• where子句中不能使用聚合函数和别名，而having子句中可以使用聚合函数和别名

SQL查询中各个关键字的执行先后顺序

from > on> join > where > group by > with > having > select > distinct > order by > limit

• 根据where子句筛选出符合条件的记录。
• 根据group by子句对数据进行分组。
• 将分组后的数据依次执行select语句。
• 根据having子句对分组后的数据进行进一步筛选。
• 根据order by子句对数据进行排序。
• 根据limit子句筛选若干条记录进行显示。