1.4 MariaDB 服务端命令（DML-INSERT基本用法）

 

语法格式：
==================

{INSERT | REPLACE}  [INTO]  tbl_name  {VALUES | VALUE}  (var1,…), (var2,…),…

此语法特性：不指定字段，插入数据时必须给出所有字段的值；
INSERT：插入数据；
REPLACE：插入数据，插入的数据对应的某字段或某几个字段如果有主键特性，则要么新插入数据，要么替换数据；
VALUE：插入单行数据；
VALUES：插入多行数据，多行数据用小括号括起来，并使用逗号分隔多个数据集；

{INSERT  | REPLACE}  [INTO]  tbl_name(col_name,…)  {VALUES | VALUE}  (var1,…),(var2,…),…

此语法特性：
指定字段，插入数据时必须给出与其对应的值；
指定的字段的属性必须是非空类型的，空类型的字段可有可无；
如果字段属性是数值自动增长类型，则可不指定；

！！！注意：字符型需要用单引号；数值型不能用任何引号；！！！

举例：
=================

创建表：

MariaDB [firstdb]> desc shediao;
+-----------+------------------+------+-----+---------+----------------+
| Field     | Type             | Null | Key | Default | Extra          |
+-----------+------------------+------+-----+---------+----------------+
| jianghuid | int(12) unsigned | NO   | PRI | NULL    | auto_increment |
| name      | varchar(60)      | NO   | MUL | NULL    |                |
| gender    | enum('F','M')    | NO   |     | NULL    |                |
| area      | varchar(50)      | YES  |     | NULL    |                |
| arena     | varchar(120)     | YES  |     | NULL    |                |
| title     | varchar(110)     | YES  |     | NULL    |                |
+-----------+------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

插入一行数据：

MariaDB [firstdb]> insert into shediao value (1,'huang yaoshi','M','tao hua dao','tao hua dao','dong xie');
Query OK, 1 row affected (0.00 sec)

插入多行数据（指定所有字段，所有字段的值都必须给出）：

MariaDB [firstdb]> INSERT INTO shediao(jianghuid,name,gender,area,arena,title) VALUES (2,'ou yang feng','M','xi yu','bai tuo shan','xi du'),(3,'duan zhi xin','M','da li','di huang','nan di'),(4,'hong qi','M','zhong yuan','gai bang','bei gai'),(5,'wang chong yang','M','zhong yuan','quan zheng jiao','zhong sheng tong');
Query OK, 4 rows affected (0.10 sec)
Records: 4  Duplicates: 0  Warnings: 0

查看表的具体数据：

MariaDB [firstdb]> select * from shediao;
+-----------+-----------------+--------+-------------+-----------------+------------------+
| jianghuid | name            | gender | area        | arena           | title            |
+-----------+-----------------+--------+-------------+-----------------+------------------+
|         1 | huang yaoshi    | M      | tao hua dao | tao hua dao     | dong xie         |
|         2 | ou yang feng    | M      | xi yu       | bai tuo shan    | xi du            |
|         3 | duan zhi xin    | M      | da li       | di huang        | nan di           |
|         4 | hong qi         | M      | zhong yuan  | gai bang        | bei gai          |
|         5 | wang chong yang | M      | zhong yuan  | quan zheng jiao | zhong sheng tong |
+-----------+-----------------+--------+-------------+-----------------+------------------+
5 rows in set (0.00 sec)

插入多行数据（指定若干非空要求的字段，除了可以自动填充的字段值外，所给出的字段必须手动给出数据内容）：

MariaDB [firstdb]> INSERT INTO shediao(name,gender) values ('Guo Jing','M'),('Huang Rong','F');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [firstdb]> SELECT * FROM shediao;
+-----------+-----------------+--------+-------------+-----------------+------------------+
| jianghuid | name            | gender | area        | arena           | title            |
+-----------+-----------------+--------+-------------+-----------------+------------------+
|         1 | huang yaoshi    | M      | tao hua dao | tao hua dao     | dong xie         |
|         2 | ou yang feng    | M      | xi yu       | bai tuo shan    | xi du            |
|         3 | duan zhi xin    | M      | da li       | di huang        | nan di           |
|         4 | hong qi         | M      | zhong yuan  | gai bang        | bei gai          |
|         5 | wang chong yang | M      | zhong yuan  | quan zheng jiao | zhong sheng tong |
|         6 | Guo Jing        | M      | NULL        | NULL            | NULL             |
|         7 | Huang Rong      | F      | NULL        | NULL            | NULL             |
+-----------+-----------------+--------+-------------+-----------------+------------------+
7 rows in set (0.00 sec)

REPLACE举例，如果插入的数据已存在且有主键存在的话，则更新数据；

MariaDB [firstdb]> REPLACE INTO shediao VALUE (1,'Huang YaoSHi','F','Tao Hua Dao','Tao Hua Dao','Dong Xue');
Query OK, 2 rows affected (0.00 sec)

MariaDB [firstdb]> SELECT * FROM shediao;
+-----------+-----------------+--------+-------------+-----------------+------------------+
| jianghuid | name            | gender | area        | arena           | title            |
+-----------+-----------------+--------+-------------+-----------------+------------------+
|         1 | Huang YaoSHi    | M      | Tao Hua Dao | Tao Hua Dao     | Dong Xue         |
|         2 | ou yang feng    | M      | xi yu       | bai tuo shan    | xi du            |
|         3 | duan zhi xin    | M      | da li       | di huang        | nan di           |
|         4 | hong qi         | M      | zhong yuan  | gai bang        | bei gai          |
|         5 | wang chong yang | M      | zhong yuan  | quan zheng jiao | zhong sheng tong |
|         6 | Guo Jing        | M      | NULL        | NULL            | NULL             |
|         7 | Huang Rong      | F      | NULL        | NULL            | NULL             |
+-----------+-----------------+--------+-------------+-----------------+------------------+
7 rows in set (0.00 sec)