插入数据的关键字是 VALUES
1、创建一张名为person的表
MariaDB [vincen]> CREATE TABLE person
-> (
-> id INT UNSIGNED NOT NULL AUTO_INCREMENT,
-> name CHAR(40) NOT NULL DEFAULT '',
-> age INT NOT NULL DEFAULT 0,
-> info CHAR(50) NULL,
-> PRIMARY KEY (id)
-> );
Query OK, 0 rows affected (0.02 sec)
2、为表中所有的字段插入数据
#在person表中,插入一条新记录,id值为1,name值为Green,age值为21,info值为Lawyer,
MariaDB [vincen]> INSERT INTO person (id ,name, age , info)
-> VALUES (1,'Green', 21, 'Lawyer');
MariaDB [vincen]> select * from person;
+----+-------+-----+--------+
| id | name | age | info |
+----+-------+-----+--------+
| 1 | Green | 21 | Lawyer |
+----+-------+-----+--------+
1 row in set (0.00 sec)
3、为表中指定的字段插入数据
#在person表中,插入一条新记录,name值为Willam,age值为20,info值为sports man
MariaDB [vincen]> INSERT INTO person (name, age,info)
-> VALUES('Willam', 20, 'sports man');
Query OK, 1 row affected (0.01 sec)
#查看插入数据后的person表
MariaDB [vincen]> select * from person;
+----+--------+-----+------------+
| id | name | age | info |
+----+--------+-----+------------+
| 1 | Green | 21 | Lawyer |
| 2 | Willam | 20 | sports man |
+----+--------+-----+------------+
2 rows in set (0.01 sec)
4、同时插入多条记录
#在person表中,在name、age和info字段指定插入值,同时插入3条新记录
MariaDB [vincen]> INSERT INTO person(name, age, info)
-> VALUES ('Evans',27, 'secretary'),
-> ('Dale',22, 'cook'),
-> ('Edison',28, 'singer');
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
#查看插入后的person表
MariaDB [vincen]> select * from person;
+----+--------+-----+------------+
| id | name | age | info |
+----+--------+-----+------------+
| 1 | Green | 21 | Lawyer |
| 2 | Willam | 20 | sports man |
| 3 | Evans | 27 | secretary |
| 4 | Dale | 22 | cook |
| 5 | Edison | 28 | singer |
+----+--------+-----+------------+
5 rows in set (0.01 sec)
5、将查询结果插入到表中
1)、先创建一个名为person_old的表
#创建一个名为person_old的数据表,其表结构与person结构相同
MariaDB [vincen]> CREATE TABLE person_old
-> (
-> id INT UNSIGNED NOT NULL AUTO_INCREMENT,
-> name CHAR(40) NOT NULL DEFAULT '',
-> age INT NOT NULL DEFAULT 0,
-> info CHAR(50) NULL,
-> PRIMARY KEY (id)
-> );
Query OK, 0 rows affected (0.05 sec)
2)、给person_old表插入记录
#向person_old表中添加两条记录
MariaDB [vincen]> INSERT INTO person_old
-> VALUES (11,'Harry',20, 'student'), (12,'Beckham',31, 'police');
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0
3)、将person_old表中所有的记录插入到person表中
#将person_old表中所有的记录插入person表中
MariaDB [vincen]> INSERT INTO person(id, name, age, info)
-> SELECT id, name, age, info FROM person_old;
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
4)、查看插入数据后的person表
#查看插入数据后的person表
MariaDB [vincen]> select * from person;
+----+---------+-----+------------+
| id | name | age | info |
+----+---------+-----+------------+
| 1 | Green | 21 | Lawyer |
| 2 | Willam | 20 | sports man |
| 3 | Evans | 27 | secretary |
| 4 | Dale | 22 | cook |
| 5 | Edison | 28 | singer |
| 11 | Harry | 20 | student |
| 12 | Beckham | 31 | police |
+----+---------+-----+------------+
7 rows in set (0.01 sec)