MySQL全面瓦解5：数据操作-DML
说明
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）等。
