MySQL表的增删查改

create

向表中新增数据：

在 MySQL 中，insert 语句用于向表中插入新的记录，insert 的 SQL 语句如下：

INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);

说明：

• column1，column2，… ：要插入数据的表的列名。
• value1，value2，… ： 要插入的值。

使用这种形式可以插入指定列名的值，如果不知道的话，则会插入所有列的值。注意，要插入的值必须与列的数据类型匹配，否则会出现错误。

示例：为了便于演示，下面创建一张学生表，表中包含：自增长主键id，学号，姓名，QQ号。如下所示：

单行数据 + 全列插入

使用 insert 语句向 students 表中插入记录，向表中插入记录且不指定 column 列，表示按照表中默认的列顺序进行全列插入，value 数量必须和定义表的列的数量及顺序一致，如下所示：

多行数据 + 指定列插入

使用 insert 语句可以一次向表中插入多条记录，插入记录时按照指定的列顺序进行插入，多条记录之间使用逗号分隔，如下：

说明：在插入多行数据并指定列时，需要检查列名和值的对应关系，确保数据类型和约束条件的匹配，同时注意引号的使用和数据的唯一性。在插入记录时，只有允许为空的列或自增长字段可以不指定值插入，不允许为空的列必须指定值插入，否则插入出错。

插入否则更新

由于 主键 或者 唯一键 对应的值已经存在而导致插入失败，如下：

这时可以选择性的进行同步更新操作，在 SQL 中 使用 ON DUPLICATE UPDATE 语句将新纪录插入到表中，或者更新已经存在的记录。该语句的语法如下：

INSERT INTO table_name (column1, column2, ...) 
VALUES (value1, value2, ...) 
ON DUPLICATE KEY UPDATE column1 = value1 [, column2 = value2, ...];

示例一：插入的数据在原本的表里面没有找到，则直接插入新的记录。

示例二：插入的数据在原有的表中存在，则将原有记录的数据进行更新。

执行该操作后，可以通过受影响的数据行数来判断本次插入数据的情况：

• o row affected : 表中没有冲突数据，但冲突数据的值和 update 的值相等。
• 1 row affected : 表中没有冲突数据，数据直接被插入。
• 2 rows affected : 表中有冲突数据，并且数据已经被更新。

执行此语句时，如果表中已经存在具有相同键的记录，则指定的列将使用新值更新。如果没有具有相同键的记录，则将插入具有指定值的记录到表中。

替换

在 MySQL 中，replace 语句用于向表中插入新行或基于唯一键更新现有行。replace 语句的语法如下：

REPLACE INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

说明：

• 主键 或者 唯一键 没有冲突，则直接插入。
• 主键 或者 唯一键 如果冲突，则删除后再插入。

示例：

replace 语句执行之后，可通过受影响的数据行数来判断本次数据插入的情况：

• 1 row affected : 表中没有冲突数据，数据被插入。
• 2 row affected : 表中有冲突数据，删除后重新插入。

Retrieve

select语句

在 MySQL 中，select 语句用于从一个或多个表中检索数据。select 语句的语法如下：

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

为下列进行演示，下面创建一个成绩表，表中包含 id,name,chinese,math,english ，如下：

向表中插入一批数据，便于后续测试：

select列

全列查询

使用 select * 可以方便地查询表的所有列，如下：

通常情况下不建议使用 * 进行全列查询，查询的列越多，意味着需要传输的数据量越大，可能会影响到索引的使用。

指定列查询

查询数据时可以对一个表中的指定列进行查询，且指定列的顺序不需要按照定义表的顺序来：

查询字段为表达式

可以通过在 select 语句中使用表达式来查询计算字段，表达式是一种由列、运算符和函数组成的语法结构，可以用于计算、转换和组合列数据。

查询数据时，column 列表中除了能将表中所有的列显示，也可以将表达式罗列到 column 中：

column 列表中的表达式可以包含表中已有字段，可以对它们进行计算：

为查询结果指定列名

可以使用 AS 关键字为查询结果中的列指定别名。别名是一个用于表示查询结果中列的名称。可以是任何合法的标识符。

语法：

SELECT column [AS] alias_name [...] FROM table_name;

示例：SELECT column1 + column2 AS sum, column3 * 2 AS doubled FROM table_name;

column1 + column2 和 column3 * 2 是计算字段的表达式，AS 关键字用于为这些计算字段指定别名，如第一个计算字段别名为 sum，第二个字段别名为 doubled。

如下所示，为 chinese+math+english 的结果指定别名为总分：

使用 AS 关键字可以为查询结果指定列名，提高查询结果的可读性和易用性。需要注意的是，别名只在查询结果中起作用，不会对表结构或数据产生任何影响。

结果去重

可以使用 DISTINCT 关键字来去除查询结果中的重复行。distinct 关键字将会从查询结果中删除所有重复的行，只保留一行。

示例：查询表中所有同学的数学成绩，从数据中可看出数学成绩有重复的分数，如下：

接下来，我们将数学分数相同的进行去重查询，去重查询结果如下：

若使用如下语句，表示 name 和 math 都相同时才进行去重：

where条件

在 MySQL 中，使用 where 条件是为了限制查询的结果，只返回符合特定条件的数据行。使用 where 条件可以帮助我们更准确地获取或操作数据，提高数据处理的效率。

where 子句用于指定一个或多个筛选条件，这些条件用于过滤数据。多个筛选条件之间可以使用逻辑运算符 OR 或者 AND 进行关联，以便于更加精准的筛选数据。

常见的比较运算符如下：

这些运算符可以用于构建条件，以便更加精细的筛选数据。

常见的逻辑运算符如下：

逻辑运算符可以将多个筛选条件组合在一起，以便更精准的选择数据。通过在 where 子句中使用这些运算符，可以轻松的执行各种复杂的数据筛选工作。

英语不及格的同学姓名及英语成绩

在 where 子句中指明筛选条件为 english < 60，然后分别列出这些同学的姓名及英语成绩：

语文成绩在 [70,80] 分的同学的姓名及语文成绩

语文成绩是 98 或 87 或 55 或 75 分的同学的姓名和语文成绩

在 where 子句中使用逻辑运算符 or 来进行连接，如下：

这里，可以使用 where 子句中的 in 运算符在一组值中筛选数据。in 运算符可以指定多个值，如下：

姓钟的同学及钟某同学

在 where 子句中通过模糊匹配来筛选当前姓钟的同学，其中 like 用于匹配以 ''钟" 开头的姓名，% 表示任意字符的通配符，如下：

如果需要匹配一个严格的字符串，如查找钟某同学，可以在 where 子句中通过模糊查询来判断当前同学是否钟某（需要通过 _ 来严格匹配单个字符串），如下：

数学成绩好于英语成绩的同学

where 条件中比较运算符两侧都是字段。

总分在200分以下的同学

在 where 条件中使用表达式，在这里别名不能使用在 where 条件中，因为在查询数据时是现根据 where 子句筛选出符合条件的记录，然后再将符合条件的记录作为数据来执行 select 语句。

语文成绩 > 80 且不姓钟的同学

AND 与 NOT 的使用，where 子句中筛选条件为语文成绩>80 ，且通过模糊匹配和逻辑运算符 not 来确保筛选出来的数据中没有姓钟的同学：

钟某同学，否则要求总成绩 > 200 且 语文成绩 < 数学成绩 并且 英语成绩 > 80

NULL的查询

使用这张数据表进行查询：

查询英语成绩不为空的同学：

查询英语成绩为NULL的同学：

在比较某个值是否为 NULL 时，需要使用 <=> 运算符，不能使用 = 来对 NULL 进行判断：

结果排序

可以使用 order by 子句对查询结果进行排序。order by 子句可以指定一个或多个列名，以便对查询结果按照指定的列进行升序或降序排序。SQL 语句如下：

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

说明：

• ASC（ascending）为升序（从小到大）。
• DESC（descending）为降序（从大到小）。
• 不指定 ASC 或 DESC 时，默认为 ASC。

注意：没有 order by 子句的查询，返回的顺序是未定义的，永远不要依赖这个顺序。

查询同学及其数学成绩，按数学成绩升序显示

在 select 的 column 列表中指定要查询的列，使用 order by 子句进行排序，然后指定以升序或降序进行排序。当然，这里要求是按照升序显示，所以 SQL 子句中 asc 可以省略不写：

查询同学英语成绩，按英语成绩排序显示

首先按照英语成绩升序排序，如下：

按照降序对英语成绩进行排序：

说明：NULL 视为比任何值都要小，因此升序出现在最上面，降序出现在最下面。

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

如下，数学按照降序排序，在数学相同的情况下，英语按照升序排序，在数学和英语成绩都相同的情况下，最后才会按照语文升序：

在 order by 子句中，可以指定通过多个字段进行排序。每个字段可以指定按照升序或降序排序，并且这些字段之间需要使用逗号分隔。排序优先级与字段书写顺序相同。

查询同学及其总分，按照总分由高到低排序

order by 中可以使用表达式：

在 order by 子句中可以使用 select 中指定的别名：

• 查询数据时先根据 where 子句筛选出符合条件的记录。
• 然后将符合条件的记录作为数据源依次执行 select 子句。
• 最后通过 order by 子句对 select 语句筛选出来的数据进行排序。

因此，可以在 order by 子句中使用 select 语句中指定的别名：

查询姓鲁的同学或者姓百的同学数学成绩，结果按数学成绩由高到低显示

where 和 order by 子句的综合查询：

筛选分页结果

在 MySQL 中，可以使用 limit 来筛选查询结果并进行分页。limit 关键字需要指定两个参数，第一个此参数用于指定返回结果的起始位置（即偏移量），第二个参数用于指定返回结果的数量。

语句：

-- 起始下标为 0
-- 从 0 开始，筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n;

-- 从 s 开始，筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s, n;

-- 从 s 开始，筛选 n 条结果，比第二种用法更明确，建议使用
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET s;

说明：

• 查询 SQL 中各语句执行的顺序：where、select、order by、limit。
• 使用 limit 筛选数据时，若不指定起始位置，则默认从 0 开始。
• limit 和 offset 语句的顺序很重要。一般情况下，应该先指定 offset，在指定 limit，否则查询结果可能会出错。

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

按id进行分页，每页4条记录，分别显示第1、2、3页

接下来使用这张表进行演示：

在进行查询的时侯，使用 limit 指定起始位置和查询的记录数量，如下所示：

说明：若表中的数据不足 n 个，则有多少个就显示多少。

Update

在 MySQL 中，可以使用 update 语句来更新数据库表中的数据。update 语句的基本语法如下：

UPDATE table_name SET column1=expr1 [, column2=expr2] ... [WHERE ...] [ORDER BY ...] [LIMIT ...];

将曜的数学成绩更改为80分

查看原始数据：

使用 update 将曜的数学成绩改为 80 分，并进行查看更改是否正确：

将安琪拉的数学成绩更新为 60 分，语文成绩更新为 70 分

查看安琪拉同学原本的数学和语文成绩：

更新安琪拉同学的数学和语文成绩，并查看更新之后的数据：

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

查看原数据的总分倒数前 3 名同学：

使用 update 将筛选出来的同学数学成绩加上 30 分，并且查看修改数据是否成功：

在 MySQL 中，不支持 数据+= 这中复合运算符。此外，该题中更新了数学成绩后，再次查看总分倒数前 3 的同学，可能已经发生变化了。

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

更新之前，先查看以下所有同学的语文成绩：

使用 update 将所有同学的语文成绩变为原来的 2 倍，更改后并查看：

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

使用 update 语句更新数据时，需要特别小心谨慎，尤其是在没有备份的情况下。因为 update 会直接修改表中的数据，如果操作不当，可能会导致数据丢失或出错。因此，在执行 update 语句之前，应该对数据库进行备份，并谨慎的操作数据库。

Delete

删除数据

在 MySQL 中，可以使用 delete 语句来删除数据库表中的数据。delete 语句可以一次删除一条或多条记录，也可以删除表中的所有记录。delete 的 SQL 语句如下：

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

删除曜同学的考试成绩

删除前先看查看曜同学的相关数据，delete 数据之后再次查看是否删除成功：

删除整张表的数据

给一张测试表如下，删除这张表的全部数据：

使用 delete 删除数据时，不通过筛选条件（where、order by 、limit 等）筛选数据时，会将整张表的数据都删除：

向删除数据的表中插入一条记录，发现表中的自增长字段 id 是按照删除表之前的基础上进行增长的：

因为在表结构中存在 AUTO_INCREMENT=n 字段（用于记录当前表中自增长字段的下一个值），删除表中数据时，该字段保存的数据不会被删除:

注意：慎用该操作！！！

截断表

在 MySQL 中，可以使用 truncate 语句来删除数据库中的所有数据，这个操作和 delete 删除所有数据的效果相同，但是 truncate 的执行速度更快。

语句：

TRUNCATE [TABLE] table_name;

说明：

• 只能对整表进行操作，不能像 delete 一样针对部分数据操作。
• 实际上 truncate 不对数据操作，所以比 delete 快，但 truncate 在删除数据的时候，并不经过真正的事务，所以无法回滚。
• truncate 会重置 AUTO INCREMENT 项。

准备一张测试表：

向表中插入一批数据用于演示 truncate：

在 truncate 语句中指明要删除的数据库表名，截断整表数据，但由于 truncate 实际上不对数据进行操作，因此执行 truncate 操作后影响行数是 0 。

再次向表中插入数据，插入数据时自增长字段不指定，发现插入数据的自增长 id 值是重新从 1 开始增长：

插入查询结果

插入查询结果的 SQL 如下：

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

删除表中重复的记录，重复的数据只保留一份

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

向表中插入测试数据，数据中包含重复数据：

现在我们需要对上表进行去重操作，操作步骤如下：

step1： 创建一张临时表，该表的结构与测试表的结构完全相同。

创建的临时表的结构与原表结构相同，因此使用 like 来进行新表创建，如下：

step2： 用去重的方式查询表中的数据，并将查询结果插入到新建的临时表中去。

step3： 将测试表重命名备份到其它地方，再将临时表重命名为测试表的名称，实现原子去重操作。

聚合函数

在 MySQL 中，可以使用聚合函数来对表中的数据进行统计和汇总。常见的聚合函数如下：

这些聚合函数通常与 group by 子句一起使用，以对表中的数据进行分组统计。

统计班级中共有多少同学

下面我们用之前的成绩表进行演示：

使用 * 进行统计，不受 NULL 的影响：

使用表达式进行统计。如下所示，数据表中的每条记录都会执行一次函数，因此可以统计出数据表中一共有多少条记录：

统计班级里参加数学考试的有多少名同学

NULL 不会计入结果：

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

使用 count(math) 统计的是全部成绩：

使用 count(distinct math) 统计的是去重后的数学成绩数量：

统计数学成绩的总分

如下，NULL 不参与计算：

统计数学成绩<60 的总分，没有结果，则返回 NULL：

统计平均总分

注意：成绩为空的 曜 同学没有参与计算。且某一位同学的某一项数值为空，则不参与运算。

返回英语最高分

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

group by 子句的使用

在 MySQL 中，group by 子句用于对表中的数据进行分组，并对每个分组进行统计和汇总。group by 子句的基本语法如下：

SELECT column1, column2, ..., aggregate_function(column)
FROM table_name
WHERE condition
GROUP BY column1, column2, ...;

使用 group by 时需要注意以下几点：

• group by 子句必须放在 where 子句之后，select 子句之前。
• group by 子句的列名必须在 select 子句中出现，或者是聚合函数的参数。
• 可以使用多个列名进行分组，如：group by column1 column2 。
• 分组时可以使用表达式和别名。
• 如果没有指定分组条件，则将所有行视为一组。

示例：

首先我们需要创建一个雇员信息表（来自 Oracle 9i 的经典测试表）。

其中包含三张表，分别是员工表（emp）、部门表（dept）、工资登记表（salgrade）。

员工表（emp）：

部门表（dept）：

工资登记表（salgrade）：

雇员信息表的 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 语句保存在一个文件中，然后在命令行中执行 source 进行数据库导入，如下所示：

执行完该操作后，查看数据库查看是否导入成功：

如何显示每个部分的平均工资和最高工资

使用 group by 子句按照指定部门进行分组，然后在 select 语句中使用函数计算部分的平均工资和最高工资，如下：

上述 SQL 会先将表中的数据按照部分进行分组，然后再各自使用聚合函数进行计算。

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

select deptno,job,avg(sal),min(sal) from emp group by deptno,job;

按照部门编号和职位对员工表进行分组，并计算每组的平均薪资和最低薪资，如下：

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

HAVING的使用

在 MySQL 中，HAVING 子句用于过滤 GROUP BY 查询的结果。HAVING 子句类似于 WHERE 子句，但是 WHERE 子句在分组之前过滤，而 HAVING 子在分组后过滤。

语法：

SELECT column1, column2, ...
FROM table_name
GROUP BY column1, column2, ...
HAVING condition;

说明：

• column1、column2 是要检索的列。
• condition 是过滤条件。
• having 之后可以指定一个或多个条件。

having 子句和 where 子句之间的区别：

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

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

使用聚合函数和 group by 将各个部门的平均工资计算出来，然后使用 having 过滤条件符合的数据：

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

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