插入MySQL数据

插入数据的关键字是  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)