MySQL全面瓦解5：数据操作-DML

smart哥

已于 2024-07-12 19:20:12 修改

阅读量535

点赞数 30

分类专栏： mysql专题文章标签： mysql

于 2024-04-29 15:14:55 首次发布

本文链接：https://blog.csdn.net/smart_an/article/details/138314073

版权

mysql专题专栏收录该内容

30 篇文章 0 订阅

订阅专栏

作者简介：大家好，我是smart哥，前中兴通讯、美团架构师，现某互联网公司CTO

联系qq：184480602，加我进群，大家一起学习，一起进步，一起对抗互联网寒冬

码哥源码部分

码哥讲源码-原理源码篇【2024年最新大厂关于线程池使用的场景题】

码哥讲源码【炸雷啦！炸雷啦！黄光头他终于跑路啦！】

码哥讲源码-【jvm课程前置知识及c/c++调试环境搭建】

码哥讲源码-原理源码篇【揭秘join方法的唤醒本质上决定于jvm的底层析构函数】

码哥源码-原理源码篇【Doug Lea为什么要将成员变量赋值给局部变量后再操作？】

码哥讲源码【你水不是你的错,但是你胡说八道就是你不对了！】

码哥讲源码【谁再说Spring不支持多线程事务，你给我抽他！】

终结B站没人能讲清楚红黑树的历史，不服等你来踢馆！

打脸系列【020-3小时讲解MESI协议和volatile之间的关系，那些将x86下的验证结果当作最终结果的水货们请闭嘴】

说明

DML(Data Manipulation Language)数据操作语言，是指对数据库进行增删改的操作指令，主要有INSERT、UPDATE、DELETE三种，代表插入、更新与删除，这是学习MySQL必要掌握的基本知识。

与之前的章节一致，下方语法中 [] 中内容可以省略。

INSERT操作

逐行插入

语法格式如下：

    1 insert into t_name[(column_name1,columnname_2,...)] values (val1,val2);
    2 或者
    3 insert into t_name set column_name1 = val1,column_name2 = val2;

1、字段名称和值需要保证数量一直，类型一直，位置一一对应，否则可能导致异常。

2、not null的字段需要保证有插入的值，否则会报非空的异常信息。允许null的字段如果不想输入数据，字段和值都不出现，或者value用null代替。

3、数值类型，值不需要用单引号括起来，其他的如字符型或日期类型，值需要用单引号括起来；

4、如果表名后面的column_name 省略不写，则代表覆盖该表的所有字段。值的顺序和表中字段顺序须保持一致。

5、上述第二种语法的写法更繁琐，现在比较少使用。

测试一下：

     1 mysql> desc `user1`;
     2 +---------+--------------+------+-----+---------+----------------+
     3 | Field   | Type         | Null | Key | Default | Extra          |
     4 +---------+--------------+------+-----+---------+----------------+
     5 | id      | bigint(20)   | NO   | PRI | NULL    | auto_increment |
     6 | name    | varchar(20)  | NO   |     | NULL    |                |
     7 | age     | int(11)      | NO   |     | 0       |                |
     8 | address | varchar(255) | YES  |     | NULL    |                |
     9 +---------+--------------+------+-----+---------+----------------+
    10 4 rows in set
    11 
    12 mysql> insert into `user1`(name,age,address) values('brand',20,'fuzhou');
    13 Query OK, 1 row affected
    14 
    15 mysql> insert into `user1`(age,address) values(20,'fuzhou');
    16 1364 - Field 'name' doesn't have a default value
    17 
    18 mysql> insert into `user1` values('sol',21,'xiamen');
    19 1136 - Column count doesn't match value count at row 1
    20 
    21 mysql> insert into `user1` values(null,'sol',21,'xiamen');
    22 Query OK, 1 row affected
    23 
    24 mysql> select * from `user1`;
    25 +----+-------+-----+---------+
    26 | id | name  | age | address |
    27 +----+-------+-----+---------+
    28 |  3 | brand |  20 | fuzhou  |
    29 |  4 | sol   |  21 | xiamen  |
    30 +----+-------+-----+---------+
    31 2 rows in set

批量插入

语法格式如下：

    1 insert into t_name [(column_name1,column_name2)] values (val1_1,val1_2),(val2_1,val2_2)...);
    2 或者
    3 insert into t_name [(column_name1,column_name2)] select  o_name1,o_name2  from o_t_name [where  condition];

1、上述第一个语法，values 后面的值个数需要同等配对 column的数量，可以设置多个，逗号隔开，提高数据插入效率。

2、第二个语法，select查询的字段和插入数据的字段数量、顺序、类型需要一致。 insert的字段可以省略，代表插入t_name表所有字段。条件可选。

测试一下：

     1 mysql> insert into `user1`(name,age,address) values('brand',20,'fuzhou'),('sol',21,'xiamen');
     2 Query OK, 2 rows affected
     3 Records: 2  Duplicates: 0  Warnings: 0
     4 
     5 mysql> select * from `user1`;
     6 +----+-------+-----+---------+
     7 | id | name  | age | address |
     8 +----+-------+-----+---------+
     9 |  5 | brand |  20 | fuzhou  |
    10 |  6 | sol   |  21 | xiamen  |
    11 +----+-------+-----+---------+
    12 2 rows in set

     1 mysql> desc `user2`;
     2 +---------+--------------+------+-----+---------+----------------+
     3 | Field   | Type         | Null | Key | Default | Extra          |
     4 +---------+--------------+------+-----+---------+----------------+
     5 | id      | bigint(20)   | NO   | PRI | NULL    | auto_increment |
     6 | name    | varchar(20)  | NO   |     | NULL    |                |
     7 | age     | int(11)      | NO   |     | 0       |                |
     8 | address | varchar(255) | YES  |     | NULL    |                |
     9 | sex     | int(11)      | NO   |     | 1       |                |
    10 +---------+--------------+------+-----+---------+----------------+
    11 5 rows in set
    12 
    13 mysql> insert into `user2` (name,age,address,sex) select name,age,address,null from `user1`;
    14 Query OK, 2 rows affected
    15 Records: 2  Duplicates: 0  Warnings: 0
    16 
    17 mysql> select * from `user2`;
    18 +----+-------+-----+---------+------+
    19 | id | name  | age | address | sex  |
    20 +----+-------+-----+---------+------+
    21 |  7 | brand |  20 | fuzhou  | 1    |
    22 |  8 | sol   |  21 | xiamen  | 1    |
    23 +----+-------+-----+---------+------+
    24 2 rows in set

UPDATE操作

数据更新

语法格式如下：

    1 update t_name [[as] alias] set [ alias.]column_name1 = val1,[alias.]column_name2 = val2 [where condition];

1、alias 是别名的意思，别名越简单识别性越强越好，容易辨认，方便操作，没有别名情况下，表名就是别名

2、as alias 中as也是可选的，where 条件也是可选的，所以用户可以选择需要的，符合特定条件的部分数据进行更新。

测试一下：

     1 mysql> select * from `user2`;
     2 +----+-------+-----+---------+------+
     3 | id | name  | age | address | sex  |
     4 +----+-------+-----+---------+------+
     5 |  7 | brand |  20 | fuzhou  | NULL |
     6 |  8 | sol   |  21 | xiamen  | NULL |
     7 +----+-------+-----+---------+------+
     8 2 rows in set
     9 
    10 mysql> update `user2` as u2 set u2.name = 'hero',u2.age=23,u2.sex=1 where id=7;
    11 Query OK, 1 row affected
    12 Rows matched: 1  Changed: 1  Warnings: 0
    13 
    14 mysql> select * from `user2`;
    15 +----+------+-----+---------+------+
    16 | id | name | age | address | sex  |
    17 +----+------+-----+---------+------+
    18 |  7 | hero |  23 | fuzhou  |    1 |
    19 |  8 | sol  |  21 | xiamen  | NULL |
    20 +----+------+-----+---------+------+
    21 2 rows in set

还有一种方式是同时更新多个表，使用不同的别名以及一些条件去限制，不过不建议这么做，操作易错，并且不好维护。

DELETE操作

delete方式删除

语法格式如下：

    1 delete [alias] from t_name [[as] alias] [where condition];

1、跟上面一样，alias代表别名，没有别名情况下，表名就是别名

2、如果表设置了别名，则delete后面必须跟上别名，否则数据库会报异常。

测试一下：

    mysql> select * from `user2`;
    +----+------+-----+---------+------+
    | id | name | age | address | sex  |
    +----+------+-----+---------+------+
    |  7 | hero |  23 | fuzhou  |    1 |
    |  8 | sol  |  21 | xiamen  | NULL |
    +----+------+-----+---------+------+
    2 rows in set
    
    mysql>  delete from `user2` as alias where sex=1;
    1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as alias where sex=1' at line 1
    
    mysql>  delete alias from `user2` as alias where sex=1;
    Query OK, 1 row affected
    
    mysql> select * from `user2`;
    +----+------+-----+---------+------+
    | id | name | age | address | sex  |
    +----+------+-----+---------+------+
    |  8 | sol  |  21 | xiamen  | NULL |
    +----+------+-----+---------+------+
    1 row in set

3、如果删除表中所有的数据，则后面不带上where条件即可，不过要谨慎使用哟。

     1 mysql> select * from `user2`;
     2 +----+-------+-----+----------+-----+
     3 | id | name  | age | address  | sex |
     4 +----+-------+-----+----------+-----+
     5 |  8 | sol   |  21 | xiamen   |   0 |
     6 | 10 | brand |  21 | fuzhou   |   1 |
     7 | 11 | helen |  20 | quanzhou |   0 |
     8 +----+-------+-----+----------+-----+
     9 3 rows in set
    10 
    11 mysql> delete from `user2`;
    12 Query OK, 3 rows affected
    13 
    14 mysql> select * from `user2`;
    15 Empty set

truncate方式删除

语法格式如下：

    1 truncate t_name;

     1 mysql> select * from `user2`;
     2 +----+-------+-----+----------+-----+
     3 | id | name  | age | address  | sex |
     4 +----+-------+-----+----------+-----+
     5 | 12 | brand |  21 | fuzhou   |   1 |
     6 | 13 | helen |  20 | quanzhou |   0 |
     7 | 14 | sol   |  21 | xiamen   |   0 |
     8 +----+-------+-----+----------+-----+
     9 3 rows in set
    10 
    11 mysql> truncate `user2`;
    12 Query OK, 0 rows affected
    13 
    14 mysql> select * from `user2`;
    15 Empty set

看起来跟delete很像，但是重新插入数据会发现，他的自增主键会重新从1开始，但是delete的是直接在原来的所以自增值之后往上加。看下面id字段。

     1 mysql> insert into `user2` (name,age,address,sex) values('brand',21,'fuzhou',1),('helen',20,'quanzhou',0),('sol',21,'xiamen',0);
     2 Query OK, 3 rows affected
     3 Records: 3  Duplicates: 0  Warnings: 0
     4 
     5 mysql> select * from `user2`;
     6 +----+-------+-----+----------+-----+
     7 |  id  | name  | age | address  | sex |
     8 +----+-------+-----+----------+-----+
     9 |  1 | brand |  21 | fuzhou   |   1 |
    10 |  2 | helen |  20 | quanzhou |   0 |
    11 |  3 | sol   |  21 | xiamen   |   0 |
    12 +----+-------+-----+----------+-----+
    13 3 rows in set

那 truncate 和 delete有什么区别呢？我们来梳理下。

truncate和delete的比较

1、truncate 指的是清空表的数据、释放表的空间，但不删除表的架构定义(表结构)。因为不包含Where条件，所以不是删除具体行，而是将整个表清空了。

2、而delete 语句是删除表中的数据行，可以在后面带上条件控制删除的维度、范围，它每次从表中删除一行，会同时将该行的删除操作作为事务保存在日志中，用于进行可能的回滚操作。

3、truncate 和 delete 一样的地方是：只是删除数据，涉及到的表结构及其列、约束、索引等均不会变。

4、如果被外键 foreign key 约束，不能使用truncate ，只能使用不带where子句的delete语句。

5、truncate 操作会记录在日志中，delete操作会放到 rollback segement 中，执行时要等事务被commit才会生效；所以delete 会触发删除触发器（如果有的话），truncate 不会。

6、 如果像上面我们测试的那样，包含自增字段，truncate方式清空之后，自增列的值会被初始化从1开始。

delete方式要分情况判断（如果数据全部delete，数据库未被重启，则按照之前max+1；数据库重启了，则一样会重新开始计算自增列的初始值）。

7、前面章节我们还学过drop，drop语句会删除表包括结构、数据、依赖该表的约束（constrain），触发器（trigger）索引（index）等。

smart哥

关注

30
点赞
踩
8

收藏

觉得还不错? 一键收藏
0
评论
MySQL全面瓦解5：数据操作-DML

作者简介：大家好，我是哥，前中兴通讯、美团架构师，现某互联网公司联系qq：184480602，加我进群，大家一起学习，一起进步，一起对抗互联网寒冬学习必须往深处挖，挖的越深，基础越扎实！
复制链接

扫一扫