mysql插入ㄖ_MySQL插入命令_INSERT INTO

MySQL允许将一个或多个元组插入已存在的table中。

格式：INSERT INTO  表名 (属性名1,属性名2,属性名3) VALUES (value1,value2,value3);

INSERT INTO  表名 (属性名1,属性名2,属性名3) VALUES (value-a1,value-a2,value-a3),(value-b1,value-b2,value-b3);

说明：若value数值是字符型，则需要加上双引号""

1.逐个元组插入

1)首先创建一个TABLE

mysql>use test

Database changed

mysql> create table courses

-> (num int AUTO_INCREMENT PRIMARY KEY,

-> name char(20) NOT NULL,

-> submission_date char(10)

-> )ENGINE=InnoDB DEFAULT CHARSET=utf8;

Query OK, 0 rows affected (0.07 sec)

2)然后插入元组

mysql> insert into courses (name,submission_date) values("数学",NOW());

Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> insert into courses (name,submission_date) values("语文",NOW());

Query OK, 1 row affected, 1 warning (0.03 sec)

mysql> insert into courses (name,submission_date) values("英语",NOW());

Query OK, 1 row affected, 1 warning (0.06 sec)

mysql>insert into courses (name,submission_date) values("化学",NOW());

Query OK, 1 row affected, 1 warning (0.03 sec)

mysql> insert into courses (name,submission_date) values("历史",NOW());

Query OK, 1 row affected, 1 warning (0.01 sec)

其中，属性num的值没有设置，因为它的值是自动递增的。

3)元组查看

mysql> select * from courses;

+-----+--------+-----------------+

| num | name | submission_date |

+-----+--------+-----------------+

| 1 | 数学 | 2018-01-30 |

| 2 | 语文 | 2018-01-30 |

| 3 | 英语 | 2018-01-30 |

| 4 | 化学 | 2018-01-30 |

| 5 | 历史 | 2018-01-30 |

+-----+--------+-----------------+

5 rows in set (0.00 sec)

2.多个元组插入

mysql> insert into courses (name,submission_date) values("生物",NOW()),

-> ("政治",NOW()),("体育",NOW());

Query OK, 3 rows affected, 3 warnings (0.03 sec)

Records: 3 Duplicates: 0 Warnings: 3

mysql> select * from courses;

+-----+--------+-----------------+

| num | name | submission_date |

+-----+--------+-----------------+

| 1 | 数学 | 2018-01-30 |

| 2 | 语文 | 2018-01-30 |

| 3 | 英语 | 2018-01-30 |

| 4 | 化学 | 2018-01-30 |

| 5 | 历史 | 2018-01-30 |

| 6 | 生物 | 2018-01-30 |

| 7 | 政治 | 2018-01-30 |

| 8 | 体育 | 2018-01-30 |

+-----+--------+-----------------+

8 rows in set (0.00 sec)