MySQL必知必会 -- INSERT 插入数据

最新推荐文章于 2022-07-09 14:30:09 发布

young_dump_broke

最新推荐文章于 2022-07-09 14:30:09 发布

阅读量3.3k

点赞数

分类专栏： mysql必知必会文章标签： mysql 数据库 mariadb sql

本文链接：https://blog.csdn.net/thermal_life/article/details/108217666

版权

mysql必知必会专栏收录该内容

27 篇文章 0 订阅

订阅专栏

插入完整的行


MariaDB [test]> insert into linux value('user8','888','girl','19',null,null,2);\c
Query OK, 1 row affected (0.05 sec)

MariaDB [test]> select * from linux;
+-------+--------+------+------+-------+------------+-------+
| user  | passwd | sex  | age  | price | math_price | class |
+-------+--------+------+------+-------+------------+-------+
| user2 | 222    | girl | 23   | 155   | 36         |     1 |
| user3 | 333    | boy  | 12   | 78    | 66         |     1 |
| user4 | 444    | boy  | 22   | NULL  | NULL       |     2 |
| user5 | 555    | boy  | 30   | NULL  | 43         |     2 |
| user1 | 111    | boy  | 18   | 35    | 88         |     1 |
| user6 | 666    | girl | 16   | 35    | 43         |     2 |
| user7 | 777    | boy  | NULL | NULL  | NULL       |  NULL |
| user8 | 888    | girl | 19   | NULL  | NULL       |     2 |
+-------+--------+------+------+-------+------------+-------+
8 rows in set (0.00 sec)

数据在VALUES子句中给出，对每个列必须按顺序提供一个值。如果某个列没有值（如上面的cust_contact和cust_email列），应该使用NULL值（假定表允许对该列指定空值）。

这种是不安全的，因为表中的列可能是变动的，所以应该在 VALUE 前列出表中的列，这样即使表的列顺序变动了，也能正确插入

在以下情况我们可以省略一些列：

该列定义为允许NULL值（无值或空值）。
在表定义中给出默认值。这表示如果不给出值，将使用默认值。

通常数据检索是最重要的，我们可以通过在INSERT和INTO之间添加关键字LOW_PRIORITY，指示MySQL降低INSERT语句的优先级，如 INSERT LOW_PRIORITY INTO

插入多个行

MariaDB [test]> insert into linux 
value('user9','999','boy','24','27','46',2),
('user10','000','boy','30','28','39',1);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [test]> select * from linux;
+--------+--------+------+------+-------+------------+-------+
| user   | passwd | sex  | age  | price | math_price | class |
+--------+--------+------+------+-------+------------+-------+
| user2  | 222    | girl | 23   | 155   | 36         |     1 |
| user3  | 333    | boy  | 12   | 78    | 66         |     1 |
| user4  | 444    | boy  | 22   | NULL  | NULL       |     2 |
| user5  | 555    | boy  | 30   | NULL  | 43         |     2 |
| user1  | 111    | boy  | 18   | 35    | 88         |     1 |
| user6  | 666    | girl | 16   | 35    | 43         |     2 |
| user7  | 777    | boy  | NULL | NULL  | NULL       |  NULL |
| user8  | 888    | girl | 19   | NULL  | NULL       |     2 |
| user9  | 999    | boy  | 24   | 27    | 46         |     2 |
| user10 | 000    | boy  | 30   | 28    | 39         |     1 |
+--------+--------+------+------+-------+------------+-------+
10 rows in set (0.00 sec)

插入检索出的数据

MariaDB [test]> select * from linux;
+--------+--------+------+------+-------+------------+-------+
| user   | passwd | sex  | age  | price | math_price | class |
+--------+--------+------+------+-------+------------+-------+
| user2  | 222    | girl | 23   | 155   | 36         |     1 |
| user3  | 333    | boy  | 12   | 78    | 66         |     1 |
| user4  | 444    | boy  | 22   | NULL  | NULL       |     2 |
| user5  | 555    | boy  | 30   | NULL  | 43         |     2 |
| user1  | 111    | boy  | 18   | 35    | 88         |     1 |
| user6  | 666    | girl | 16   | 35    | 43         |     2 |
| user7  | 777    | boy  | NULL | NULL  | NULL       |  NULL |
| user8  | 888    | girl | 19   | NULL  | NULL       |     2 |
| user9  | 999    | boy  | 24   | 27    | 46         |     2 |
| user10 | 000    | boy  | 30   | 28    | 39         |     1 |
+--------+--------+------+------+-------+------------+-------+
10 rows in set (0.00 sec)

MariaDB [test]> select * from hour;
+--------+--------+------+------+------+----------+-------+
| user   | passwd | sex  | age  | num  | math_num | class |
+--------+--------+------+------+------+----------+-------+
| user11 | NULL   | NULL | NULL | NULL | NULL     |  NULL |
+--------+--------+------+------+------+----------+-------+
1 row in set (0.00 sec)

MariaDB [test]> insert into linux(user,passwd,sex,age,price,math_price,class)
    -> select user,passwd,sex,age,num,math_num,class
    -> from hour;
Query OK, 1 row affected, 2 warnings (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 2

MariaDB [test]> select * from linux;
+--------+--------+------+------+-------+------------+-------+
| user   | passwd | sex  | age  | price | math_price | class |
+--------+--------+------+------+-------+------------+-------+
| user2  | 222    | girl | 23   | 155   | 36         |     1 |
| user3  | 333    | boy  | 12   | 78    | 66         |     1 |
| user4  | 444    | boy  | 22   | NULL  | NULL       |     2 |
| user5  | 555    | boy  | 30   | NULL  | 43         |     2 |
| user1  | 111    | boy  | 18   | 35    | 88         |     1 |
| user6  | 666    | girl | 16   | 35    | 43         |     2 |
| user7  | 777    | boy  | NULL | NULL  | NULL       |  NULL |
| user8  | 888    | girl | 19   | NULL  | NULL       |     2 |
| user9  | 999    | boy  | 24   | 27    | 46         |     2 |
| user10 | 000    | boy  | 30   | 28    | 39         |     1 |
| user11 |        |      | NULL | NULL  | NULL       |  NULL |
+--------+--------+------+------+-------+------------+-------+
11 rows in set (0.00 sec)

用INSERT SELECT从hour中将所有数据导入 linux表，他们的列名不一定是需要匹配的，知识将第一列的值放入要 insert 的表的第一列。

还可以加 where 子句：

MariaDB [test]> insert into linux(user,passwd,sex,age,price,math_price,class) select user,passwd,sex,age,num,math_num,class from hour where passwd is not null;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

MariaDB [test]> select * from linux;
+--------+--------+------+------+-------+------------+-------+
| user   | passwd | sex  | age  | price | math_price | class |
+--------+--------+------+------+-------+------------+-------+
| user2  | 222    | girl | 23   | 155   | 36         |     1 |
| user3  | 333    | boy  | 12   | 78    | 66         |     1 |
| user4  | 444    | boy  | 22   | NULL  | NULL       |     2 |
| user5  | 555    | boy  | 30   | NULL  | 43         |     2 |
| user1  | 111    | boy  | 18   | 35    | 88         |     1 |
| user6  | 666    | girl | 16   | 35    | 43         |     2 |
| user7  | 777    | boy  | NULL | NULL  | NULL       |  NULL |
| user8  | 888    | girl | 19   | NULL  | NULL       |     2 |
| user9  | 999    | boy  | 24   | 27    | 46         |     2 |
| user10 | 000    | boy  | 30   | 28    | 39         |     1 |
| user11 |        |      | NULL | NULL  | NULL       |  NULL |
| user12 | 222    | boy  | 27   | NULL  | NULL       |  NULL |
+--------+--------+------+------+-------+------------+-------+
12 rows in set (0.00 sec)