【MySQL】表的增删改查

表的增删改查

表的增删查改简称CRUD：Create（新增），Retrieve（查找），Update（修改），Delete（删除）。
CRUD的操作对象是对表当中的数据，是典型的DML语句（Data Manipulation Language 数据操作语言）。

一、Create

语法：

INSERT [INTO] table_name 
[(column [, column]...)]
VALUES (value_list)[, (value_list)]...

// 解释： value_list: value, [, value]...

说明：

SQL中大写的表示关键字，[ ]中代表的是可选项。
SQL中的每个value_list都表示插入的一条记录，每个value_list都由若干待插入的列值组成。
SQL中的column列表，用于指定每个value_list中的各个列值应该插入到表中的哪一列。

为了方便进行演示，下面创建一个学生表，表当中包含自增长的主键id、学号、姓名和QQ号。如下：

create table students(id int unsigned primary key auto_increment,
    sn int unsigned not null unique comment '学号',
    name varchar(20) not null comment '姓名',
    qq varchar(15) unique comment 'qq号码');

desc students;

在这里插入图片描述

单行数据 + 全列插入

当我们在使用

insert

语句向学生表中插入记录，每次向表中插入一条记录，并且插入记录时不指定

column

列表，表示按照表中默认的列顺序进行全列插入，因此插入的每条记录中的列值需要按表列顺序依次列出。如下：

insert into students values(100, 1000, '唐玄奘', null);
insert into students values(101, 1023, '孙悟空', null);select * from students;

在这里插入图片描述

多行数据 + 指定列插入

使用

insert

语句也可以一次向表中插入多条记录，插入的多条记录之间使用逗号隔开，并且插入记录时可以只指定某些列进行插入。如下：

insert into students(sn, name) values (123, '曹操'), (187,'刘备');select * from students;

在这里插入图片描述

插入否则更新

有时候我们在向表中插入新记录时，由于主键或者 唯一键 对应的值已经存在而导致我们插入失败。

在这里插入图片描述

这时如果我们还想插入我们的数据就可以选择性的进行同步更新操作：

插入否则更新的SQL语法如下：

INSERT ... ON DUPLICATE KEY UPDATE column1=value1 [, column2=value2]...;

作用：

如果表中没有冲突数据，则直接插入数据。
如果表中有冲突数据，则将表中的数据进行更新。

说明：

SQL中大写的表示关键字，[ ]中代表的是可选项。
SQL中INSERT之后语法与之前的INSERT语句相同。
UPDATE后面的column=value，表示当插入记录出现冲突时需要更新的列值。

例如：这次我们继续向学生表中刚才的插入记录。如下：

insert into students (id,sn,name) values(100, 1000, '唐僧') on duplicate key update name='唐僧', sn=1010;

在这里插入图片描述

可以看到在冲突的情况下我们也确实更新了数据。

说明：执行插入否则更新的SQL后，可以通过受影响的数据行数来判断本次数据的插入情况：

0 rows affected：表中有冲突数据，但冲突数据的值和指定更新的值相同。
1 row affected：表中没有冲突数据，数据直接被插入。
2 rows affected：表中有冲突数据，并且数据已经被更新。

替换数据

如果表中没有冲突数据，则直接插入数据。
如果表中有冲突数据，则先将表中的冲突数据删除，然后再插入数据。

语法：

只需要在插入数据时将SQL语句中的INSERT改为REPLACE即可

replace into students(sn, name) values(1023, '孙行者');

在这里插入图片描述

执行替换数据的SQL后，也可以通过受影响的数据行数来判断本次数据的插入情况：

1 row affected：表中没有冲突数据，数据直接被插入。
2 rows affected：表中有冲突数据，冲突数据被删除后重新插入。

二、Retrieve

查找其实是我们使用

mysql

最多的操作，其语法如下：

SELECT [DISTINCT]{* |{column1 [, column2]...}} FROM table_name [WHERE ...][ORDER BY ...][LIMIT ...];

说明：

SQL中大写的表示关键字，[ ]中代表的是可选项。
{ }中的 | 代表可以选择左侧的语句或右侧的语句。

为了方便我们进行演示，下面创建一个成绩表，表当中包含：自增长的主键id、姓名、以及该同学的语文成绩、数学成绩和英语成绩。如下：

CREATE TABLE exam_result (id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) NOT NULL COMMENT '同学姓名',
chinese float DEFAULT 0.0 COMMENT '语文成绩',
math float DEFAULT 0.0 COMMENT '数学成绩',
english float DEFAULT 0.0 COMMENT '英语成绩');

在这里插入图片描述

接下来向表中插入几条测试记录，以供我们进行查找。如下：

INSERT INTO exam_result (name, chinese, math, english) VALUES
('唐三藏', 67, 98, 56),
('孙悟空', 87, 78, 77),
('猪悟能', 88, 98, 90),
('曹孟德', 82, 84, 67),
('刘玄德', 55, 85, 45),
('孙权', 70, 73, 78),
('宋公明', 75, 65, 30);

在这里插入图片描述

1、SELECT 列

1、全列查询

通过全列查询我们能够拿到所有列的数据，在查询数据时直接用

代替

column

列表，表示进行全列查询。如下：

select * from exam_result;

在这里插入图片描述

通常情况下不建议使用

进行全列查询

查询的列越多，意味着需要传输的数据量越大（MySQL采用的CS模式）；
可能会影响到索引的使用。

2、指定列查询

在查询数据时也可以只对指定的列进行查询，这时将需要查询的列在

column

列表列出即可。

例如我们现在要看一下班级中的数学成绩，如下：

select name, math from exam_result;

在这里插入图片描述

3、结果去重

当然，我们也可以在

select

后面跟上

distinct

，表示对结果去重，下面我们尝试对上面的7个数学成绩进行去重。

select distinct name, math from exam_result;

在这里插入图片描述

我们发现好像没有达到我们想象的效果，还是有两个相同的数据，这时为什么呢？

因为

distinct

是对结果进行去重，而我们结果中不仅有数学成绩还有姓名，这两个组合起来没有重复所以也就不会被去重了。

因此我们在对数学成绩进行去重时不应该加上姓名，使用下面的SQL进行去重：

select distinct math from exam_result;

可以看到我们的结果变成了6行（以前是7行），而且没有重复数据

在这里插入图片描述

4、查询字段为表达式

select

语句是一个非常特殊的语句，其在查询数据时，column列表中除了能罗列表中存在的列名外，我们也可以将表达式罗列到column列表中。如下：

select name, math, 1+1 from exam_result;

在这里插入图片描述

因此

select

不仅能够用来查询数据，还可以用来计算某些表达式或执行某些函数。如下：

在这里插入图片描述

如果我们将表达式罗列到

column

列表中：

那么每当一条记录被筛选出来时就会执行这个表达式，然后将表达式的计算结果作为这条记录的一个列值进行显示。
column列表中的表达式可以包含表中已有的字段，这时每当一条记录被筛选出来时，就会将记录中对应的列值提供给表达式进行计算。

例如：我们将每个同学的数学成绩都加10分。

select name, math, math+10 from exam_result;

在这里插入图片描述

又或者我们可以求一下同学的总分：

select name, math+chinese+english from exam_result;

在这里插入图片描述

5、为查询结果指定别名

为查询结果指定别名的SQL语法如下：

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

说明：

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

比如刚才查询成绩表中的数据时，求总分后的名字为

math+chinese+english

，有一点丑陋，我们使用别名进行解决。如下：

select name, math+chinese+english as total from exam_result;

在这里插入图片描述

2、WHERE 条件

通过刚才的学习我们对一列的数据能够进行很好的操作了，但是有些时候，某一列的数据特别多，我们想要快速的找到我们想要的行，于是就需要有

where

条件了！

where

子句添加与否的区别：

如果在查询数据时没有指定where子句，那么会直接将表中所有的记录作为数据源来依次执行select语句。
如果在查询数据时指定了where子句，那么在查询数据时会先根据where子句筛选出符合条件的记录，然后将符合条件的记录作为数据源来依次执行select语句。(非常重要！！！)

在这里插入图片描述

where

子句中可以指明一个或多个筛选条件，各个筛选条件之间用逻辑运算符

AND

或

OR

进行关联，下面给出了

where

子句中常用的比较运算符和逻辑运算符。

注意MySQL中的的NULL不能参与任何运算

比较运算符：
运算符说明>、>=、<、<=大于、大于等于、小于、小于等于=等于。NULL不安全，例如NULL=NULL的结果是NULL而不是TRUE(1)<=>等于。NULL安全，例如NULL<=>NULL的结果就是TRUE(1)!=、<>不等于，NULL不安全BETWEEN a0 AND a1范围匹配。如果a0<=value<=a1，则返回TRUE(1)IN(option1, option2, …)如果是IN中的任意一个option，则返回TRUE(1)IS NULL如果是NULL，则返回TRUE(1)IS NOT NULL如果不是NULL，则返回TRUE(1)LIKE模糊匹配。%表示任意多个字符（包括0个），_表示任意一个字符
逻辑运算符：
运算符说明AND多个条件同时为TRUE(1)，则结果为TRUE(1)，否则为FALSE(0)OR任意一个条件为TRUE(1)，则结果为TRUE(1)，否则为FALSE(0)NOT条件为TRUE(1)，则结果为FALSE(0)；条件为FALSE(0)，则结果为TRUE(1)

下面我们来做一些案例帮助我们理解并掌握这些运算符：

1、查询英语不及格的同学及其英语成绩

在

where

子句中指明筛选条件为英语成绩小于60，我们直接使用

运算符即可。如下：

select name, english from exam_result where english <60;

在这里插入图片描述

2、查询语文成绩在80到90分的同学及其语文成绩

在

where

子句中指明筛选条件为语文成绩，然后根据分数直接

>=80 ，<= 90

即可，然后根据其并列关系条件我们选择

and

逻辑运算符进行连接。如下：

select name, chinese from exam_result where chinese >=80 and chinese <=90;

在这里插入图片描述

此外，这里也可以使用

BETWEEN a0 AND a1

来指明语文成绩的的所在区间。如下：

select name, chinese from exam_result where chinese between 80 and 90;

在这里插入图片描述

3、数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩

在

where

子句中指明筛选条件为数学成绩等于

或

，在

select

的

column

列表中指明要查询的列为姓名和数学成绩。如下：

select name ,math from exam_result where math=58 or math=59 or math=98 or math=99;

在这里插入图片描述

此外，这里也可以通过

IN(58, 59, 98, 99)

的方式来判断数学成绩是否符合筛选要求。如下：

select name, math from exam_reault where math in(58,59,98,99);

在这里插入图片描述

4、姓孙的同学及孙某同学

查询姓孙的同学

这里给出的查询条件是模糊条件，所以在

where

子句中通过模糊匹配来判断当前同学是否姓孙（需要用到

来匹配多个字符），在

select

的

column

列表中指明要查询的列为姓名。如下：

select name from exam_result where name like '孙%';

在这里插入图片描述

查询孙某同学

这里给出的查询条件是同样是模糊条件，在

where

子句中通过模糊匹配来判断当前同学是否为孙某（需要用到

来严格匹配单个字符），在

select

的

column

列表中指明要查询的列为姓名。如下：

select name from exam_result where name like '孙_';

在这里插入图片描述

5、查询语文成绩好于英语成绩的同学

在

where

子句中指明筛选条件为：语文成绩

英语成绩，在

select

的

column

列表中指明要查询的列为姓名、语文成绩和英语成绩。如下：

select name chinese, english from exam_result where chinese > english;

在这里插入图片描述

6、查询总成绩在200分以下的同学

在

select

的

column

列表中添加表达式查询，查询的表达式为语文、数学和英语成绩之和，为了方便观察可以将表达式对应的列指定别名为“总分”，在

where

子句中指明筛选条件为三科成绩之和小于200。如下：

select name, chinese, math, english,  chinese+math+english 总分 from exam_result where 总分 <200;

在这里插入图片描述

结果出现了错误，为什么呢？

因为前面我们说过

where

子句的执行顺序是在

select

之前的，而我们是在

select

时才进行了取别名，所以

where

子句中的

总分

字段找不到，因此报错。

在这里插入图片描述

而且我们的

where

子句中也不支持起别名。所以我们只能完整写出：

select name, chinese, math, english , chinese+math+english 总分 from exam_result where chinese+math+english <200;

在这里插入图片描述

注意点：在where子句中不能使用select中指定的别名：

查询数据时是先根据where子句筛选出符合条件的记录。
然后再将符合条件的记录作为数据源来依次执行select语句。

7、语文成绩 > 80 并且不姓孙的同学

在where子句中指明筛选条件为语文成绩大于80，并且

and

连接下一个条件，再通过模糊匹配和

not

否定来保证该同学不姓孙，在

select

的

column

列表中指明要查询的列为姓名和语文成绩。如下：

select name, chinese from exam_result where chinese >80 and name not like '孙%';

在这里插入图片描述

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

很显然该题目是一个使用

or

逻辑运算符的多选题目，要求是查询成绩，被查询的人要么是孙某，要么总成绩大于200分并且语文成绩小于数学成绩并且英语成绩大于80分，查询时需要用到模糊匹配、表达式查询和逻辑运算符。如下：

select name chinese,math,english,  chinese + math + english as 总分 from exam_result 
where name like '孙_' or (chinese+math+english>200 andd chinese < math and english >80);

在这里插入图片描述

9、NULL的查询

在前面我们讲表的创建时我们已经创建了一张学生表，学生表中的内容如下：

select * from students;

在这里插入图片描述
这里我们将数据进行一下更新，给孙行者添加一下qq号。

update students setqq='1111111'  where name='孙行者';

然后我们再进行查看表格

在这里插入图片描述

9.1、 查询 qq 号已知的同学姓名

qq号已知即qq号不为空，所以我们需要在where子句中指明筛选条件为QQ号不为NULL，在select的column列表中指明要查询的列为姓名和QQ号。如下：

select name, qq from students where qq is not null;

在这里插入图片描述

9.2、 查询QQ号未知的同学

qq号未知即qq号为空，所以我们需要在where子句中指明筛选条件为QQ号为NULL，在select的column列表中指明要查询的列为姓名和QQ号。如下：

select name, qq from students where qq is null;

在这里插入图片描述

当然这里也可以使用

<=>

来进行判断，但需要注意的是，在与NULL值作比较的时候应该使用

<=>

运算符，使用

运算符无法得到正确的查询结果。如下：

select name, qq from students where qq <=> null;select name, qq from students where qq <> null;

在这里插入图片描述

3、结果排序

有些时候我们对我们查找到的数据可能要进行一下排序，于是这里我们就要学习一下MySQL怎么对结果进行排序了。

结果排序的SQL如下：

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

解释：

SQL中大写的表示关键字，[ ]中代表的是可选项。
ASC和DESC分别代表的是排升序和排降序，默认为ASC。

注意：如果查询SQL中没有order by子句，那么返回的顺序是未定义的。

下面我们来进行一些简单的练习：

1、 同学及数学成绩，按数学成绩升序显示

在select的column列表中指明要查询的列为姓名和数学成绩，在order by子句中指明按照数学成绩进行升序排序。如下：

select name, math from exam_result order by math asc;

在这里插入图片描述

当然我们也还可以省略

order by

最后的

asc

，默认就是升序，但是这样的写法还是不太建议。

在这里插入图片描述

2、查询同学及其QQ号，按QQ号排序显示

升序

这个需求也很简单，我们只需要对

qq

进行

order by asc

就行了，但是别忘了我们的

students

表中的

qq

有的是

null

，那么结果会是什么呢？

select name, qq from students order by qq asc;

在这里插入图片描述

可以看出在MySQL中默认null是比任何数据都要小的！

降序

select name, qq from students order by qq desc;

在这里插入图片描述

结果符合我们的预期！

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

由于排序中可能存在相同值，于是我们就要再进行比较其他列来确定最后的相对排序了。

排序这里我们不需要使用

or

来连接多个条件，在

order by

子句中指明依次按照数学成绩排降序、英语成绩排升序和语文成绩排升序。

在

select

的

column

列表中指明要查询的列为姓名、数学成绩、英语成绩和语文成绩，如下：

在这里插入图片描述
可以看到显示结果是按照数学成绩进行降序排序的，而相同的数学成绩之间则是按照英语成绩进行升序排序的。

说明：

order by子句中可以指明按照多个字段进行排序，每个字段都可以指明按照升序或降序进行排序，各个字段之间使用逗号隔开，排序优先级与书写顺序相同。
例如上述SQL中，当两条记录的数学成绩相同时就会按照英语成绩进行排序，如果这两条记录的英语成绩也相同就会继续按照语文成绩进行排序，以此类推。

4、查询同学及总分，由高到低

在select的column列表中指明要查询的列为姓名和总分，在order by子句中指明按照总分进行降序排序。如下：

select name, chinese+ math + english 总分 from exam_result  order by 总分 desc;

在这里插入图片描述

那么这里的别名为什么我们

order by

中能够使用呢？

因为只有有了数据才能对数据进行排序，所以

order by

子句的执行的优先级是非常低的！所以

order by

能够使用

select

中的别名。

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

也就是说，order by子句的执行是在select语句之后的，所以在order by子句中可以使用别名。

5、查询姓孙的同学或姓曹的同学及其数学成绩，按数学成绩降序显示

由于这里要排序的不是所有数据，所以这里面我们要结合

where

子句和

order by

子句。

这里我们先通过

where

子句模糊匹配找到人：

select name, math from exam_result where name like '孙%' or name like '曹%';

在这里插入图片描述

当查询到目标数据后再在查询SQL后添加

order by

子句，在

order by

子句中指明按照数学成绩进行降序排序。如下：

select name, math from exam_result where name like '孙%' or name like '曹%' order by math desc;

在这里插入图片描述

4、筛选分页结果

有时候我们即使经过了数据的筛选，但是数据依然很多，如果我们直接查看的话是不利于我们进行操作的，所以有时候我们还要对我们的数据进行分页显示。

筛选分页结果的语法如下：

从第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中大写的表示关键字，[ ]中代表的是可选项。
查询SQL中各语句的执行顺序为：where、select、order by、limit。
limit子句在筛选记录时，记录的下标从0开始。

建议：对未知表进行查询时最好在查询SQL后加上

limit 1

，避免在查询全表数据时因为表中数据过大而导致数据库卡死。

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

我们先来看一看所有的数据：

select * from exam_result;

在这里插入图片描述

查询第1页记录时在查询全表数据的SQL后，加上

limit 3

子句表示从第0条向后筛选出3条记录。如下：

select * from exam_result limit 3;

在这里插入图片描述

现在我们完成了第一页的显示，但是我们要进行第二页的显示时，我们就要指明起始位置为

了，于是我们的

limit

子句就要变成了

limit 3,3

。

select * from exam_result limit 3,3;

在这里插入图片描述

最后一页，同理我们可以使用

limit 6,3

，也可以使用

limit 3 offset 6

，表明从第6条记录开始，向后筛选出3条记录。如下：

select * from exam_result limit 3 offset 6;

在这里插入图片描述

因为从表中筛选出的记录不足3个，所以筛选出几个就显示几个。

三、Update

修改数据的SQL如下：

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

说明：

SQL中大写的表示关键字，[ ]中代表的是可选项。
SQL中的column=expr，表示将记录中列名为column的值修改为expr。
在修改数据之前需要先找到待修改的记录，update语句中的where、order by和limit就是用来定位数据的。

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

在修改数据之前，我们先查看孙悟空同学当前的数学成绩。如下：

select name, math from exam_result where name='孙悟空';

在这里插入图片描述

在

update

语句中指明要将筛选出来的记录的数学成绩改为80分，并在修改后再次查看数据确保数据成功被修改。如下：

update exam_result setmath=80 where name='孙悟空';

在这里插入图片描述

2、将曹孟德同学的数学成绩修改为60分，语文成绩修改为70分

同理，在修改数据之前，先查看曹孟德同学当前的数学成绩和语文成绩。如下：

select * from exam_result where name='曹孟德';

在这里插入图片描述

在

update

语句中指明要将筛选出来的记录的数学成绩改为60分，语文成绩改为70分，并在修改后再次查看数据确保数据成功被修改。如下：

select * from exam_result where name='曹孟德';

在这里插入图片描述

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

在修改数据之前，先查看总成绩倒数前三的3位同学的数学成绩。如下：

select name, chinese+math+english 总分 from exam_result order by 总分 asc;select name, chinese+math+english 总分 from exam_result order by 总分 asc limit 3;

在这里插入图片描述

在

update

语句中指明要将筛选出来的记录的数学成绩加上30分，如下：

update exam_result set math = math + 30 order by chinese+math+english asc limit 3;

在这里插入图片描述

并在修改后再次查看数据确保数据成功被修改。

在这里插入图片描述

需要注意的是，MySQL中不支持

+=

这种复合赋值运算符，此外，这里在查看更新后的数据时不能查看总成绩倒数前三的3位同学，因为之前总成绩倒数前三的3位同学，数学成绩加上30分后可能就不再是倒数前三了。

4、将所有同学的语文成绩修改为原来的2倍

在修改数据之前，先查看所有同学的语文成绩。如下：

在这里插入图片描述

在

update

语句中指明要将筛选出来的记录的语文成绩变为原来的2倍，因为要修改的的所有同学所以我们不需要使用

where

子句，如下：

update exam_result set chinese = chinese * 2;

在这里插入图片描述

查看修改后的数据：

在这里插入图片描述

注意：这里没有

where

子句，会更新全表，更新全表的语句要慎用！

四、Delete

删除数据的SQL如下：

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

说明：

SQL中大写的表示关键字，[ ]中代表的是可选项。
在删除数据之前需要先找到待删除的记录，delete语句中的where、order by和limit就是用来定位数据的。

1、删除数据

删除表中的某个条目

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

在删除数据之前，先查看孙悟空同学的相关信息。

select * from exam_result where name='孙悟空';

在这里插入图片描述

然后在delete语句中指明删除孙悟空对应的记录，并在删除后再次查看数据确保数据成功被删除。如下：

delete from exam_result where name='孙悟空';

在这里插入图片描述

删除整张表数据

删除整表的操作很简单，我们只要不在

delete

后面加

where

子句，这样我们就能够删除整个表了

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

我们先来准备一张测试表，表中包含一个自增长的主键id和姓名。如下：

create table test(id int primary key auto_increment, name varchar(10));

desc test;

在这里插入图片描述

插入数据，并查看数据

insert into test(name) values ('A'),('B'),('C');select * from test;

在这里插入图片描述

这里我们顺便看一下

test

表的主键值的相关信息，方便我们解释下面的截断表问题：

在这里插入图片描述

现在我们在

delete

语句中只指明要删除数据的表名，而不通过

where

、

order by

和

limit

指明筛选条件，这时将会删除整张表的数据。如下：

在这里插入图片描述

这里我们再次查看一下

test

表的主键值的相关信息：

show create table test\G

在这里插入图片描述

我们在插入一些数据进行验证：

insert into test(name) values ('D'), ('E'), ('F');select * from test;

在这里插入图片描述

再次查看主键值的相关信息：

show create table test\G

在这里插入图片描述

注意：当通过delete语句删除整表数据时，不会重置AUTO_INCREMENT=n字段!

如果我们想要清除AUTO_INCREMENT=n字段，可以使用截断表！

2、截断表

截断表的SQL语法如下：

TRUNCATE [TABLE] table_name;

说明：

SQL中大写的表示关键字，[ ]中代表的是可选项。
truncate只能对整表操作，不能像delete一样针对部分数据操作。
truncate实际上不对数据操作，所以比delete更快。
truncate在删除数据时不经过真正的事务，所以无法回滚。
truncate会重置AUTO_INCREMENT=n字段。

为了演示

truncate

的效果，我们创建一张测试和原来一样表，表中包含一个自增长的主键id和姓名。如下：

create table for_truncate(id int primary key auto_increment,
    name varchar(10));

insert into for_truncate(name) values ('A'),('B'),('C');select * from for_truncate;

在这里插入图片描述

我们查看一下表

for_truncate

主键值的相关信息：

show create table for_truncate \G

在这里插入图片描述

然后我们就要通过

truncate

删除这张表了，在

truncate

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

在这里插入图片描述

再次查看表

for_truncate

主键值的相关信息

在这里插入图片描述

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

insert into for_truncate(name) values ('D'), ('E'), ('F');select * from for_truncate;

在这里插入图片描述

再次查看表

for_truncate

主键值的相关信息

show create table for_truncate \G

在这里插入图片描述

注意：

truncate

在删除数据时不经过真正的事务，无法回滚，所以截断表操作要慎用！

六、插入查询结果

经过上面的学习，我们对于表的增删查改基本上都没有问题了，但是在这里我们还要学习一下如果将这些SQL组合起来使用。

例如有些时候我们要插入的数据是：某个查询的结果，这时我们就要将SQL组合在一起了。

插入查询结果的SQL语法：

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

说明：

SQL中大写的表示关键字，[ ]中代表的是可选项。
此条SQL的作用是将筛选出来的记录插入到指定的表当中。
SQL中的column，表示将筛选出的记录的各个列插入到表中的哪一列。

为了更好的学习这个SQL语法，我们来看一下面的这个案例：

现在有一张表，表中的数据是有重复的，要求如下：

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

我们先来伪造一下题目的数据：

创建原数据表

CREATE TABLE duplicate_table (id int, name varchar(20));

插入测试数据

INSERT INTO duplicate_table VALUES
(100, 'aaa'),
(100, 'aaa'),
(200, 'bbb'),
(200, 'bbb'),
(200, 'bbb'),
(300, 'ccc');

现在我们查看一下表中的数据：

select * from duplicate_table;

在这里插入图片描述

那我们怎么完成题目的要求呢？

思路：

创建一张空表 no_duplicate_table，其结构和 duplicate_table 一样的。
对 duplicate_table表进行查询去重。
将去重的结果插入到新表no_duplicate_table

这里我们就会发现：第二步和第三步的SQL必须要合在一起才能完成我们想要的插入

将表duplicate_table进行重命名为old_duplicate_table
将表no_duplicate_table进行重命名为 duplicate_table。

操作5和操作6的重命名是为了保证底层数据表的改变不影响上层应用的使用

由于创建的空表 no_duplicate_table的表的结构与duplicate_table 相同，因此在创建临时表的时候可以借助like进行创建。如下：

create table no_duplicate_table like duplictae_table;
desc no_duplicate_table;

在这里插入图片描述

对 duplicate_table表进行查询去重。

select distinct * from duplicate_table;

在这里插入图片描述

由于表 no_duplicate_table和表duplicate_table的结构相同，所以可以通过插入查询语句将去重查询后的结果插入到临时表中。并且我们可以对select进行全列查询，因此在插入时不用在表名后指明column列表。如下：

insert into no_duplicate_table select distinct * from duplicate_table;select * from no_duplicate_table;

在这里插入图片描述
4. 将表

duplicate_table

重命名为

old_duplicate_table

要修改表名，我们使用

RENAME TABLE

语句如下：

RENAME TABLE old_table_name TO new_table_name;

旧表( old_table_name)必须存在，而新表( new_table_name)一定不存在，如果新表 new_table_name 确实存在，该语句将失败。

rename table duplicate_table to old_duplicate_table;
show tables;

在这里插入图片描述

将表no_duplicate_table进行重命名为 duplicate_table。

rename table no_duplicate_table to duplicate_table;
show tables;

在这里插入图片描述

最后我们在进行一下检查duplicate_table表是否已经经过了去重。

在这里插入图片描述

标签： mysql 数据库

本文转载自: https://blog.csdn.net/qq_65207641/article/details/134301797
版权归原作者 看到我请叫我滚去学习Orz 所有，如有侵权，请联系我们删除。

【MySQL】表的增删改查

表的增删改查

一、Create

二、Retrieve

1、SELECT 列

2、WHERE 条件

3、结果排序

4、筛选分页结果

三、Update

四、Delete

1、删除数据

2、截断表

六、插入查询结果

发表评论

“【MySQL】表的增删改查”的评论:

关于作者

overfit同步小助手

相关阅读

文章导航