本期给大家带来的是MySQL下对表中数据的增删查改操作

一、对表插入数据

1.1 单行数据插入

1.2 多行数据插入

1.3 插入冲突时更新数据

1.4 替换式插入

1.5 插入查询结果

二、对表中数据进行查询

2.1 基本select

2.1.1 使用select查询表中数据

2.1.2 使用select进行计算

2.1.3 在select语句中使用as对列自定义命名

2.1.4 对查询结果进行去重

2.1.5 where条件

2.1.6 对查询结果进行排序

2.1.7 分页显示筛选结果

2.2 分组聚合查询

三、对表中的数据进行修改

四、删除表中的数据

五、截断表

六、聚合函数

一、对表插入数据

我们之前在表的操作中提了一下向表中插入数据，下面我们来详细讲解

1.1 单行数据插入

我们可以使用下面的语句对表做数据插入：

insert into 想要插入的表名(所要插入的列1,所要插入的列2...) values(数据1,数据2...);

上面语句在使用时into关键字可省略

在表名后面我们可以使用（）在里面指定要插入的列，下面用这张表来演示一下：

当然表名后面我们也可以省略（）进行全列插入：

1.2 多行数据插入

上面的方法每次只能插入一行数据，效率不高，我们可以在values关键字后面多加几个（）用来一次插入多行数据：

insert into 表名(列1,列2...) values(数据1,数据2...),(数据1,数据2...), ...;

上面语句在使用时into关键字可省略

下面我们来演示一下，同样还是刚才的表：

多行指定列插入：

多行全列插入：

1.3 插入冲突时更新数据

我们向表中插入数据时，有时会因为主键、唯一建之类的约束发生数据冲突：

这样子我们就不能将数据插入到表中，但是使用下列语法可以在插入数据发生冲突时，更新表中冲突数据所在行：

insert into 表名(列1,...) values (数据1,...) on duplicate key update 列名1=数据1 ,列名2=数据2 ,...;

我们来试试看：

我们可以看到上述语句在插入‘i’这个数据时因为id的主键冲突肯定会失败，但是后面的on duplicate key update语句则意味着插入冲突时，将冲突数据所在行的name列数据改为'i'、telephone列数据改为'12345689'

可以看到修改非常成功，下面我们来看看该语句如果插入时没有发生冲突，系统会干啥：

可以看到当插入语句没有发生冲突时，系统会直接插入数据，不会再执行后面的更新语句

下面我们试试看当发生插入冲突时，但是更新语句更新的数据跟冲突数据一样会怎么样：

我们发现：当发生插入冲突时，但是更新语句更新的数据跟冲突数据一样，系统会什么都不做

所以使用该语句时，我们可以根据系统执行的反馈来确定数据是否做了更改：

-- 0 row affected: 表中有冲突数据，但冲突数据的值和 update 的值相等

-- 1 row affected: 表中没有冲突数据，数据被插入

-- 2 row affected: 表中有冲突数据，并且数据已经被更新

1.4 替换式插入

replace into 表名 (列名1, 列名2, ...) values (数据1, 数据2, ...);

使用上述语句可以进行替换式插入：主键或唯一键没有冲突，则直接插入；如果冲突，则删除表中原冲突数据后再插入

举例演示：

可以看到插入的数据和表中数据没有冲突，进行了直接插入

下面要插入的数据和表中原数据的id列发生了主键冲突：

可以看到发生冲突的原数据直接全部被替换为插入的数据

1.5 插入查询结果

该内容涉及到下文的select语句，建议看完基本select后再回头来看该部分内容

insert不仅仅可以插入我们自己手动输入的数据，还可以拿取select语句的数据对表进行插入：

INSERT INTO 表名 [(列名1,列名2...)] SELECT ...

光说概念太生涩了，我们实际举例来说明：

对于上面这张表，现在的要求是对其数据内容进行去重

去重？使用distinct关键字不就行了吗？

distinct虽然可以对查询结果进行去重，但是不能修改表中原始数据，所以我们要重新创建一张空表，将去重结果存入空表中，再将空表重命名为原表名称（原表可以改为其他名或者删除），这样子就可以实现对表中数据进行去重了。

下面我们一步步来：

创建空表时，我们可以使用原始的create语句一个个来定义和原表一样的列，不过在这里推荐使用like直接创建结构跟原表一模一样的空表：

再使用select语句来拿到原表的去重数据：

接着将select语句的去重结果使用insert语句插入到空表中：

最后将两个表重命名一下就完成了：

二、对表中数据进行查询

2.1 基本select

我们对表中数据进行查询时少不了select语句，其基本语法为：

SELECT
[DISTINCT] [[*] [column , column] ...]
[FROM table_name]
[WHERE ...]
[ORDER BY column [ASC | DESC], ...]
[LIMIT ...]

可以看到select语句可以跟上很多条件，下面我们来一一举例介绍

我们先创建一个表，再向其插入些数据：

2.1.1 使用select查询表中数据

如果我们想要查询表中全部的数据可以使用下面的语句：

select * from 表名;

上面的*是通配符，表示全部

我们来试试看：

如果我们想要查询表中某几列的数据可以使用下面的语句：

select 列名1,列名2,... from 表名;

例如：

2.1.2 使用select进行计算

select语句不仅仅能查询，还能进行计算：

既然可以进行计算的话，我们拿表中的数据来试试看：

那这样我们就可以打印出每个学生的总分：

2.1.3 在select语句中使用as对列自定义命名

上面的总分列名太长了，我们可以使用as关键字来对打印的列名进行重命名：

当然as也可以省略：

2.1.4 对查询结果进行去重

我们可以在select后面加上distinct关键字来对查询结果去重

例如math列原数据：

有两个85相同数据，现在我们来进行去重：

2.1.5 where条件

我们可以在select后面加上where条件句来筛选符号条件的数据，where后面常用的运算符有：

比较运算符：

运算符	说明
>, >=, <, <=	大于，大于等于，小于，小于等于
=	等于，NULL 不安全，例如 NULL = NULL 的结果是 NULL
<=>	等于，NULL 安全，例如 NULL NULL 的结果是 TRUE(1)
!=, <>	不等于
BETWEEN n1 AND n1	范围匹配，[a0, a1]，如果 a0 <= value <= a1，返回 TRUE(1)
IN (option, ...)	如果是 option 中的任意一个，返回 TRUE(1)
IS NULL	是 NULL
IS NOT NULL	不是 NULL
LIKE	模糊匹配。% 表示任意多个（包括 0 个）任意字符；_ 表示任意一个字符

逻辑运算符：

运算符	说明
AND	多个条件必须都为 TRUE(1)，结果才是 TRUE(1)
OR	任意一个条件为 TRUE(1), 结果为 TRUE(1)
NOT	条件为 TRUE(1)，结果为 FALSE(0)

下面我们拿案例来细细分析这些运算符的使用：

（1）英语不及格的同学及英语成绩 ( < 60 )：

（2）语文成绩在 [80, 90] 分的同学及语文成绩：

或者使用between and条件句：

（3）数学成绩是 12 或者 65 或者 98 或者 100 分的同学及数学成绩：

上面使用or条件句过于复杂，我们可以换成in条件句：

（4）姓名以a开头的同学及a某同学：

为了演示该例子我们再插入两组数据：

在这里我们需要用到like进行模糊查找，在字符a后面加上%表示a字符后可以跟任意多个（包括 0 个）任意字符，这样子我们就可以招找到姓名以a开头的同学了：

在字符a后面加上_表示a字符后只能跟一个任意字符，这样我们就可以查到a某同学：

（5）语文成绩好于英语成绩的同学

（6）总分在 200 分以下的同学

当然我们对总分这一列重命名：

那既然重命名了总分这一列，where字句的后面直接用重命名的列不是更方便吗？

但是系统是不支持的，要知道执行这句指令时，先要执行计算where语句后面的条件，再拿到select语句中去筛选。所以在where执行时总分这一列还没有被重命名，系统会自动报错。

（7）语文成绩 > 80 并且姓名不以a开头的同学

（8）姓名以b开头的同学，否则要求总成绩 > 200 并且语文成绩 < 数学成绩并且英语成绩 > 80

当条件过多时，可以使用（）来将多个条件视为一个整体：

2.1.6 对查询结果进行排序

我们可以使用order by语句对select的查询结果进行排序：

SELECT ... FROM table_name [WHERE ...]
ORDER BY column [ASC|DESC], [...];

-- ASC 为升序（从小到大），DESC 为降序（从大到小），默认为 ASC

要注意NULL视为比任何值都小！

下面我们来进行演示：

（1）查询同学及数学成绩，按数学成绩升序显示

（2）查询同学及英语成绩，按英语成绩降序显示

（3）查询同学各门成绩，依次按数学降序，英语升序，语文升序的方式显示

即数学成绩升序排列，如果相等数学成绩相等按英语成绩升序，如果英语成绩相等按语文成绩升序：

（4）查询同学及总分，由高到低排序

我们可以看到order by语句可以使用select语句前面重命名的列名，和where语句不一样。

这是因为select在选择完数据后才会交给order by语句来排序，在选择完数据时已经对列进行了重命名，所以order by语句可以使用select语句前面重命名的列名。

2.1.7 分页显示筛选结果

我们可以使用limit关键字来对我们select的筛选结果进行部分显示，其语法有三种：

从第一行开始向下显示n行：

select ... limit n;

从第n1行（0为第一行）开始向下显示n2行：

select ... limit n1,n2;

从第n2行（0为第一行）开始向下显示n1行：

select ... limit n1 offset n2;

建议：对未知表进行查询时，最好加一条 LIMIT 1，避免因为表中数据过大，查询全表数据导致数据库卡死。

下面来演示一下：

从第一行开始，向下显示3行：

从第4行开始，向下显示2行：

如此一来就可以进行对表进行分页显示了，比如我们每一页显示该表的3行数据：

或者

2.2 分组聚合查询

该内容涉及到下文的聚合函数，建议看完聚合函数后再回头来看该部分内容

2.2.1 使用group by对数据进行分组

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

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

分组查询有什么用呢？别急，我们下面用实例一一说明：

下面我们创建一个雇员信息表（来自oracle 9i的经典测试表）（该表的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);

我们使用source语句将该表导入到MySQL中：

导入后我们查看一下该数据库：

下面我们正式开始进行分组查询：

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

我们可以使用聚合函数对所有员工的平均工资和最高工资进行统计：

但是我们的目的要将所有员工以所在部门为单位统计，所以在这里group by语句就要上场了：

这样子我们可以看到数据被分成了三份，但是每组数据具体属于哪个部门还看不出来，我们可以打印数据所在deptno列来看看：

从上述例子我们可以看出：分组，就是把一组数据按照条件拆成了多个组，进行各自组内的统计分组(“分表”)，就是把一张表按照条件在逻辑上拆成了多个子表，然后分别对各自的子表进行聚合统计！

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

我们可以看到该需求需要我们现将数据进行按部门分组，按部门分完组后再按岗位进行分组，最后将每组的数据做统计：

2.2.1 使用having对分组后的数据进行条件判断

下面我们有一个需求：

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

我们可以将这个需求分为两部分：

一：统计出来每一个部门的平均工资结果先聚合出来

二：再对聚合的结果进行判断，看其是否小于2000

我们先来实现第一步：

第二步我们要用到一个关键字having，可以对group by的结果进行条件判断（where无法对group by的分组结果进行条件判断）：

三、对表中的数据进行修改

对表中数据进行修改时我们会用的update：

update 表名 set 要修改的列名1=数据1,列名2=数据2,... [where ...] [order by ...] [limit ...];

下面我们开始举例说明：

（1）将a同学的数学成绩变更为 80 分

这里要注意了，如果不加后面的where语句默认对表math列的全部数据进行修改，这是很危险的！

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

四、删除表中的数据

对表中的数据做删会用到delete关键字：

DELETE FROM 表名 [WHERE ...] [ORDER BY ...] [LIMIT ...]

如果DELETE FROM 表名后面不接任何条件句回删除整张表中的数据！

下面我们来举例：

（1）删除a同学的考试成绩

（2）删除总分倒数第一同学的考试成绩

（2）删除表中全部数据

我们以下面这张表为列，删除其内部全部数据：

我们可以看到delete确实删除了表中所有数据，但是自增建的默认值没没有发生变化，所以使用delete删除表中所有数据并不会重置自增建！

五、截断表

我们可以使用下面的语句直接删除表中的所有数据（注意：这个操作慎用）：

TRUNCATE [TABLE] 表名;

要强调一下：

1. 只能对整表操作，不能像 DELETE 一样针对部分数据操作；

2. 实际上 MySQL 不对数据操作，所以比 DELETE 更快，但是TRUNCATE在删除数据的时候，并不经过真正的事物，所以无法回滚

3. 会重置 AUTO_INCREMENT

下面拿这张表来操作一下：

我们可以看到经过truncate之后，该表不仅仅所有数据都清空了，并且建表语句中AUTO_INCREMENT也没有了

我们再插入数据试试看：

我们可以看到AUTO_INCREMENT已经被重置了

六、聚合函数

MySQL内有一些函数可供我们调用，下面我们来提前讲一讲聚合函数为分组查询做准备：

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

下面我们继续举例：

（1）统计共有多少个学生