mysql insert的几种情况

一 普通单条插入：

像如下表中插入数据：

id是自动增长的主键。

INSERT INTO student_score (student_name, score, SUBJECT) VALUES ('张三', 90, '语文');

结果如下：

mysql> SELECT * FROM student_score;
+----+--------------+-------+---------+
| id | student_name | score | subject |
+----+--------------+-------+---------+
|  1 | 张三         |    90 | 语文    |
+----+--------------+-------+---------+
1 row in set

二 批量插入：

INSERT INTO student_score (student_name, score, SUBJECT)
VALUES
    ('李四', 95, '语文'),
    ('王五', 98, '语文');

结果如下：

mysql> SELECT * FROM student_score;
+----+--------------+-------+---------+
| id | student_name | score | subject |
+----+--------------+-------+---------+
|  1 | 张三         |    90 | 语文    |
|  2 | 李四         |    95 | 语文    |
|  3 | 王五         |    98 | 语文    |
+----+--------------+-------+---------+
3 rows in set

三 将数据从一个表插入另一个表

首先清空student_score表，student_score_record的数据如下：

mysql> SELECT * FROM student_score_record;
+----+--------------+-------+---------+------------+
| id | student_name | score | subject | test_date  |
+----+--------------+-------+---------+------------+
|  1 | 张三         |     1 | 语文    | 2020-12-10 |
|  2 | 李四         |     2 | 语文    | 2020-12-11 |
|  3 | 王五         |     3 | 语文    | 2020-12-11 |
+----+--------------+-------+---------+------------+
3 rows in set

现需要将student_score_record的相关数据插入到student_score中：

INSERT INTO student_score (student_name, score, SUBJECT) SELECT
    student_name,
    score,
    SUBJECT
FROM
    student_score_record;

结果如下：

mysql> SELECT * FROM student_score;
+----+--------------+-------+---------+
| id | student_name | score | subject |
+----+--------------+-------+---------+
|  7 | 张三         |     1 | 语文    |
|  8 | 李四         |     2 | 语文    |
|  9 | 王五         |     3 | 语文    |
+----+--------------+-------+---------+
3 rows in set

四 将数据从多个关联表插入一个表

首先清空student_score表，student表的数据如下：

mysql> SELECT * FROM `student`;
+----+--------------+------------+
| id | student_name | student_id |
+----+--------------+------------+
|  1 | 张三         |          1 |
|  2 | 李四         |          2 |
|  3 | 王五         |          3 |
+----+--------------+------------+
3 rows in set

student_id_score_record表的数据如下：

mysql> SELECT * FROM `student_id_score_record`;
+----+------------+-------+---------+
| id | student_id | score | subject |
+----+------------+-------+---------+
| 10 |          1 |    11 | 语文    |
| 11 |          2 |    12 | 语文    |
| 12 |          3 |    13 | 语文    |
+----+------------+-------+---------+
3 rows in set

现在将student和student_id_score_record的关联信息插入到student_score表中：

INSERT INTO student_score (student_name, score, SUBJECT) SELECT
    student.student_name,
    student_id_score_record.score,
    student_id_score_record.`subject`
FROM
    student
INNER JOIN student_id_score_record ON student.id = student_id_score_record.student_id;

结果如下：

mysql> SELECT * FROM student_score;
+----+--------------+-------+---------+
| id | student_name | score | subject |
+----+--------------+-------+---------+
| 10 | 张三         |    11 | 语文    |
| 11 | 李四         |    12 | 语文    |
| 12 | 王五         |    13 | 语文    |
+----+--------------+-------+---------+
3 rows in set

五 将数据从其他表插入一个表

还是四中的例子，不过这次换种写法（此写法主要目的是测试将几个独立表的不同字段插入同一个表时如何处理）：

INSERT INTO student_score (student_name, score, SUBJECT)
VALUES
    (
        (
            SELECT
                student_name
            FROM
                student
            WHERE
                student_id = 1
        ),
        (
            SELECT
                score
            FROM
                student_id_score_record
            WHERE
                student_id = 1
        ),
        '语文'
    );

结果如下：

mysql> SELECT * FROM student_score;
+----+--------------+-------+---------+
| id | student_name | score | subject |
+----+--------------+-------+---------+
| 14 | 张三         |    11 | 语文    |
+----+--------------+-------+---------+
1 row in set

最新2020整理收集的一些高频面试题（都整理成文档），有很多干货，包含mysql，netty，spring，线程，spring cloud、jvm、源码、算法等详细讲解，也有详细的学习规划图，面试题整理等，需要获取这些内容的朋友请加Q君样：909038429
/./*欢迎加入java交流Q君样：909038429一起吹水聊天