13.2.8 DML语句语法 1.insert into语句

发表于 更新于 分类于 ， ， Waline： 本文字数： 9.4k 阅读时长 ≈ 9 分钟

13.2.8 DML语句语法

与DDL操作数据库对象不同，DML主要操作数据表里的数据，使用DML可以完成如下三个任务。

  • 插入新数据(insert into)
  • 修改已有数据(update)
  • 删除不需要的数据(delete from)

1.insert into语句

insert into用于向指定数据表中插入记录。对于标准的SQL语句而言，每次只能插入一条记录。insert into语句的语法格式如下：

1
2
insert into 表名 [(属性1名[,属性2名,...])]
values (属性1值 [,属性2值,...])

执行插入操作时，表名后可以用括号列出所有需要插入值的列名，而values后用括号列出对应需要插入的值。

如果省略了表名后面的括号及括号里的列名列表，默认将为所有列都插入值，则需要为每一列都指定一个值。如果既不想在表名后列出列名，又不想为所有列都指定值，则可以为那些无法确定值的列分配null。下面的SQL语句示范了如何向数据表中插入记录。

注意

要先创建表然后才可以向数据表中插入记录。下面的SQL语句以前面介绍外键约束时所创建的teacher_table2和student_table2为例来介绍数据插入操作。

1
2
3
4
5
CREATE TABLE `teacher_table2` (
`teacher_id` int(11) NOT NULL AUTO_INCREMENT,
`teacher_name` varchar(255) DEFAULT NULL,
 PRIMARY KEY (`teacher_id`)
)

插入指定列

在表名后使用括号列出所有需要插入值的列：

1
2
insert into teacher_table2(teacher_name)
values('xyz');

这种方式关键在于可以插入部分列的值,其他列使用默认值.

插入所有列

如果不想在表后用括号列出所有列，则需要为所有列指定值；如果某列的值不能确定，则为该列分配一个null值。

1
2
insert into teacher_table2
values(null,'abx');

使用这种方式,必须给所有列赋值,也就是说下面的写法是错误的:

1
2
insert into teacher_table2
values('abx');

虽然第一列是MySQL自己生成的,虽然我们传入的null这个值没有用到,但是这个null相当于一个占位符,不过既然是占位符,那把null换成其他的也是可以的,例如这里不使用null占位,而是使用空字符串占位也是可以的,如下所示:

1
insert into teacher_table2 values('','haha');

插入效果:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
mysql> select * from teacher_table2;
+------------+--------------+
| teacher_id | teacher_name |
+------------+--------------+
|          1 | xyz          |
|          2 | abx          |
|          3 | abx          |
|          4 | abx          |
+------------+--------------+
4 rows in set

mysql> insert into teacher_table2 values('','haha');
Query OK, 1 row affected

mysql> select * from teacher_table2;
+------------+--------------+
| teacher_id | teacher_name |
+------------+--------------+
|          1 | xyz          |
|          2 | abx          |
|          3 | abx          |
|          4 | abx          |
|          5 | haha         |
+------------+--------------+
5 rows in set
mysql>

实例

插入主表

经过两条插入语句后，可以看到teacher_table2表中的数据如下所示.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> insert into teacher_table2(teacher_name)
values('xyz');
Query OK, 1 row affected

mysql> insert into teacher_table2
values(null,'abx');
Query OK, 1 row affected

mysql> select *from teacher_table2;
+------------+--------------+
| teacher_id | teacher_name |
+------------+--------------+
|          1 | xyz          |
|          2 | abx          |
+------------+--------------+
2 rows in set
mysql>

可以看到abc记录的主键列的值是2，而不是SQL语句插入的null，因为该主键列是自增长的，系统会自动为该列分配值。

自动增长列不需要赋值

自动增长列不需要赋值,就算你赋值了,也是不起作用的,例如,下面再插入一条记录:

1
2
insert into teacher_table2
values('aaa','abx');

然后再次显示表:

1
2
3
4
5
6
7
8
9
mysql> select *from teacher_table2;
+------------+--------------+
| teacher_id | teacher_name |
+------------+--------------+
|          1 | xyz          |
|          2 | abx          |
|          3 | abx          |
+------------+--------------+
3 rows in set

可以看到第三行的第一列的值是3,而不是我们填入的'aaa'这个字符串.因此没必要给自动增长列赋值。

向从表插入数据

从表:定义外键的表

1
2
3
4
5
6
7
8
9
10
CREATE TABLE `student_table2` (
  `student_id` int(11) NOT NULL AUTO_INCREMENT,
  `student_name` varchar(255) DEFAULT NULL,
  `java_teacher` int(11) DEFAULT NULL,
  PRIMARY KEY (`student_id`),
  KEY `student_teacher_fk` (`java_teacher`),
  CONSTRAINT `student_teacher_fk`
    FOREIGN KEY (`java_teacher`)
        REFERENCES `teacher_table2` (`teacher_id`)
)

根据前面介绍的外键约束规则：外键列里的值必须是被参照列里已有的值，**所以向从表中插入记录之前，通常应该先向主表中插入记录，否则从表记录的外键列只能为null**。现在主表teacher_table2中已有了2条记录，现在可以向从表student_table2中插入记录了，如下SQL语句所示：

1
2
insert into student_table2
values(null,'张三',2);

需要注意的是第三列java_teacher的值,这个值必须在teacher_table2表中的teacher_id列中存在.如果不存在则插入错误,现在插入一个错误的数据如下:

1
2
insert into student_table2
values(null,'张三',7);

运行结果:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> select * from teacher_table2;
+------------+--------------+
| teacher_id | teacher_name |
+------------+--------------+
|          1 | xyz          |
|          2 | abx          |
|          3 | abx          |
+------------+--------------+
3 rows in set

mysql> insert into student_table2
values(null,'张三',7);
1452 - Cannot add or update a child row: a foreign key constraint fails (`test_engine`.`student_table2`, CONSTRAINT `student_teacher_fk` FOREIGN KEY (`java_teacher`) REFERENCES `teacher_table2` (`teacher_id`))
mysql>

设置外键不能插入null

外键约束保证被参照的记录必须存在，但并不保证必须有被参照记录，即外键列可以为null。如果想保证每条从表记录必须存在对应的主表记录，则应使用非空、外键两个约束。

带子查询的插入语句

在一些特别的情况下，可以使用带子查询的插入语句，带子查询的插入语句可以一次插入多条记录，如下SQL语句所示：

1
2
inser into student_table2(student_name)
select teacher_name from teacher_table2;

运行结果如下所示:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
mysql> select * from student_table2;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            2 |
+------------+--------------+--------------+
1 row in set
mysql> select * from teacher_table2;
+------------+--------------+
| teacher_id | teacher_name |
+------------+--------------+
|          1 | xyz          |
|          2 | abx          |
|          3 | abx          |
+------------+--------------+
3 rows in set

mysql> insert into student_table2(student_name)
select teacher_name from teacher_table2;
Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from student_table2;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            2 |
|          9 | xyz          | NULL         |
|         10 | abx          | NULL         |
|         11 | abx          | NULL         |
+------------+--------------+--------------+
4 rows in set
mysql>

这里的自动增长列没有从2开始,是因为我有好几条insert语句插入失败了,但是自动增长列可不管你插入是否成功,它只知道你使用一次insert语句,它就加一

子查询插入要求

正如上面的SQL语句所示，带子查询的插入语句甚至不要求查询数据的源表和插入数据的目的表是同一个表，它只要求子查询选择出来的数据列和插入目的表的数据列个数相等、数据类型匹配即可。

插入失败自动增长列也会增长

虽然insert语句插入可能会失败,但是自动增长列依旧会增长。
可以通过显示表创建语句来查看自动增长列的值:

1
show create table student_table2;

显示效果:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> show create table student_table2;
+----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table          | Create Table                                                                                                                                                                                                                                                                                                                                                                                            |
+----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student_table2 | CREATE TABLE `student_table2` (
  `student_id` int(11) NOT NULL AUTO_INCREMENT,
  `student_name` varchar(255) DEFAULT NULL,
  `java_teacher` int(11) DEFAULT NULL,
  PRIMARY KEY (`student_id`),
  KEY `student_teacher_fk` (`java_teacher`),
  CONSTRAINT `student_teacher_fk`
    FOREIGN KEY (`java_teacher`)
        REFERENCES `teacher_table2` (`teacher_id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8 |
+----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

可以看到此时自动增长列的值是12,这个值表示下次插入语句时MySQL填给自动增长列的值.
现在故意输错一个insert语句,然后再次查看自动增长列的值,如下所示:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> insert into student_table2 values(null,'插不进',8);
1452 - Cannot add or update a child row: a foreign key constraint fails (`test_engine`.`student_table2`, CONSTRAINT `student_teacher_fk` FOREIGN KEY (`java_teacher`) REFERENCES `teacher_table2` (`teacher_id`))
mysql> show create table student_table2;
+----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table          | Create Table                                                                                                                                                                                                                                                                                                                                                                                            |
+----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student_table2 | CREATE TABLE `student_table2` (
  `student_id` int(11) NOT NULL AUTO_INCREMENT,
  `student_name` varchar(255) DEFAULT NULL,
  `java_teacher` int(11) DEFAULT NULL,
  PRIMARY KEY (`student_id`),
  KEY `student_teacher_fk` (`java_teacher`),
  CONSTRAINT `student_teacher_fk`
    FOREIGN KEY (`java_teacher`)
        REFERENCES `teacher_table2` (`teacher_id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8 |
+----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

可以看到虽然插入失败了,但是自动增长列还是自动加一,此时下一个自动增长列的值默认为13,如下所示:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
mysql> select * from student_table2;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            2 |
|          9 | xyz          | NULL         |
|         10 | abx          | NULL         |
|         11 | abx          | NULL         |
+------------+--------------+--------------+
4 rows in set

mysql> insert into student_table2 value(null,'可以插入',null);
Query OK, 1 row affected

mysql> select * from student_table2;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            2 |
|          9 | xyz          | NULL         |
|         10 | abx          | NULL         |
|         11 | abx          | NULL         |
|         13 | 可以插入     | NULL         |
+------------+--------------+--------------+
5 rows in set