MySQL基础03-数据增删改（DML语句）

一、简介

DML（Data Manipulation Language）数据操作语言
用来对数据库中表的数据进行增删改。关键词：insert、delete、update等

二、插入数据

1、常规插入
语法：insert [into] 表名 [(列名1, 列名2, ...)] values (值1, 值2, ...);
多条数据：insert [into] 表名 [(列名1, 列名2, ...)] values (值1, 值2, ...), (值1, 值2, ...),...;

mysql> insert user values (1,'张三');
Query OK, 1 row affected (0.00 sec)

mysql> insert into user values
    -> (2,'大O'),
    -> (3,'小O');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from user;
+------+------+
| id   | name |
+------+------+
|    1 | 张三 |
|    2 | 大O  |
|    3 | 小O  |
+------+------+
3 rows in set (0.00 sec)

注意：
1）值与字段必须相对应，个数相同，类型相同
2）值的数据大小必须在字段的长度范围内
3）除了数值类型外，其它的字段类型值必须使用引号引起。
4）如果要插入空值，可以不写字段，或者插入null

2、蠕虫复制

在已有数据的基础上，将原来的数据进行复制，插入到对应的表中。

语法：insert [into] 表名 [(字段名,...)] select ...;

两表结构一样：insert into 表1 select * from 表2;
插入指定列值：insert into 表1(字段1,字段2,...) select 字段1,字段2,... from 表2;

mysql> insert into user1 select * from user;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from user1;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 张三   |
|    2 | 大O    |
|    3 | 小O    |
+------+--------+
3 rows in set (0.00 sec)

mysql> insert into user1(name) select name from user;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from user1;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 张三   |
|    2 | 大O    |
|    3 | 小O    |
| NULL | 张三   |
| NULL | 大O    |
| NULL | 小O    |
+------+--------+
6 rows in set (0.00 sec)

三、更改数据

语法：update 表名 set 字段名=新值 [where 更新条件];

mysql> update user1 set name='Nosee' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from user1;
+------+--------+
| id   | name   |
+------+--------+
|    1 | Nosee  |
|    2 | 大O    |
|    3 | 小O    |
| NULL | 张三   |
| NULL | 大O    |
| NULL | 小O    |
+------+--------+
6 rows in set (0.00 sec)

四、删除数据

1、条件删除

语法：delete from 表名 [where 删除条件];

如，删除指定字段值为null的记录：

mysql> delete from user1 where id is null;
Query OK, 3 rows affected (0.01 sec)

mysql> select * from user1;
+------+-------+
| id   | name  |
+------+-------+
|    1 | Nosee |
|    2 | 大O   |
|    3 | 小O   |
+------+-------+
3 rows in set (0.00 sec)

注：当没有WHERE条件，则删除表中的所有行

2、批量删：drop、truncate

清空一张(大)表更有效的方法是使用truncate语句，比delete快得多。
语法：truncate [table] 表名;
truncate 会将整个表摧毁，然后重新建一个和原表结构一模一样的新表（删除表数据，保留表结构）。

mysql> truncate user1;
Query OK, 0 rows affected (0.04 sec)

mysql> select * from user1;
Empty set (0.00 sec)

mysql> show create table user1 \G
*************************** 1. row ***************************
       Table: user1
Create Table: CREATE TABLE `user1` (
  `id` int DEFAULT NULL,
  `name` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk
1 row in set (0.00 sec)

摧毁表:drop table 表名
drop 会将整个表摧毁（包括表的结构和全部数据），就是这张表不存在了。

drop 与 delete 删除所有数据比较：
delete from 表名 是将表中的数据一条一条的删除，而truncate [table] 表名则是一次性整张表删除。

五、替代 REPLACE

replace 是 insert 语句的一个变种，当添加新行时：
①如果主键值重复，那么就覆盖表中已有的行
②如果没有主键值重复，则插入该行

语法：REPLACE [INTO] tbl_name [(col_name,...)] VALUES (expr,...),(...),...

mysql> select * from user;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 张三   |
|    2 | 大O    |
|    3 | 小O    |
+------+--------+
3 rows in set (0.01 sec)

mysql> insert into user values (3,'小四');
ERROR 1062 (23000): Duplicate entry '3' for key 'user.PRIMARY'

mysql> replace user values (3,'小四');
Query OK, 2 rows affected (0.00 sec)

mysql> replace user values (4,'小小');
Query OK, 1 row affected (0.01 sec)

mysql> select * from user;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张三   |
|  2 | 大O    |
|  3 | 小四   |
|  4 | 小小   |
+----+--------+
4 rows in set (0.00 sec)

六、参考

8.0官方文档：https://dev.mysql.com/doc/refman/8.0/en/