- 插入数据
INSERT使用方式：
- 插入完整的行；
- 插入行的一部分；
- 插入某些查询的结果
1.插入完整的行（它要求指定 表名和插入到新行中的值）
mysql> INSERT INTO Customers VALUES(‘10006‘,‘Toy Land‘,‘123 Any Street‘,‘New York‘,‘NY‘,‘11111‘,‘USA‘,NULL,NULL); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM Customers; +---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+ | cust_id | cust_name | cust_address | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email | +---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+ | 10001 | Coyote Inc. | 200 Maple Lane | Detroit | MI | 44444 | USA | Y Lee | ylee@coyote.com | | 10002 | Mouse House | 333 Fromage Lane | Columbus | OH | 43333 | USA | Jerry Mouse | NULL | | 10003 | Wascals | 1 Sunny Place | Muncie | IN | 42222 | USA | Jim Jones | rabbit@wascally.com | | 10004 | Yosemite Place | 829 Riverside Drive | Phoenix | AZ | 88888 | USA | Y Sam | sam@yosemite.com | | 10005 | E Fudd | 4545 53rd Street | Chicago | IL | 54545 | USA | E Fudd | NULL | | 10006 | Toy Land | 123 Any Street | New York | NY | 11111 | USA | NULL | NULL | +---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+ 6 rows in set (0.00 sec) mysql>
然而这样并不安全，插入的时候应当明确给出列名，如下：
mysql> INSERT INTO Customers(cust_id,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country,cust_contact,cust_email) VALUES(‘10007‘,‘Toy Sky‘,‘123 Any Street‘,‘New York‘,‘NY‘,‘11111‘,‘USA‘,NULL,NULL); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM Customers; +---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+ | cust_id | cust_name | cust_address | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email | +---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+ | 10001 | Coyote Inc. | 200 Maple Lane | Detroit | MI | 44444 | USA | Y Lee | ylee@coyote.com | | 10002 | Mouse House | 333 Fromage Lane | Columbus | OH | 43333 | USA | Jerry Mouse | NULL | | 10003 | Wascals | 1 Sunny Place | Muncie | IN | 42222 | USA | Jim Jones | rabbit@wascally.com | | 10004 | Yosemite Place | 829 Riverside Drive | Phoenix | AZ | 88888 | USA | Y Sam | sam@yosemite.com | | 10005 | E Fudd | 4545 53rd Street | Chicago | IL | 54545 | USA | E Fudd | NULL | | 10006 | Toy Land | 123 Any Street | New York | NY | 11111 | USA | NULL | NULL | | 10007 | Toy Sky | 123 Any Street | New York | NY | 11111 | USA | NULL | NULL | +---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+ 7 rows in set (0.00 sec)
因为提供了列名，VALUES 必须以其指定的次序匹配指定的列名，不一定 按各列出现在表中的实际次序。其优点是，即使表的结构改变，这条 INSERT 语句仍然能正确工作
2.插入部分行
如果表的定义允许，则可以在 INSERT 操作中省略某些列。省略的列 必须满足以下某个条件。
- 该列定义为允许 NULL 值（无值或空值）
- 在表定义中给出默认值。这表示如果不给出值，将使用默认值
mysql> INSERT INTO Customers(cust_id,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country) VALUES(‘10008‘,‘Dels Sky‘,‘123 Habe Street‘,‘New York‘,‘NY‘,‘22222‘,‘USA‘); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM Customers; +---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+ | cust_id | cust_name | cust_address | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email | +---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+ | 10001 | Coyote Inc. | 200 Maple Lane | Detroit | MI | 44444 | USA | Y Lee | ylee@coyote.com | | 10002 | Mouse House | 333 Fromage Lane | Columbus | OH | 43333 | USA | Jerry Mouse | NULL | | 10003 | Wascals | 1 Sunny Place | Muncie | IN | 42222 | USA | Jim Jones | rabbit@wascally.com | | 10004 | Yosemite Place | 829 Riverside Drive | Phoenix | AZ | 88888 | USA | Y Sam | sam@yosemite.com | | 10005 | E Fudd | 4545 53rd Street | Chicago | IL | 54545 | USA | E Fudd | NULL | | 10006 | Toy Land | 123 Any Street | New York | NY | 11111 | USA | NULL | NULL | | 10007 | Toy Sky | 123 Any Street | New York | NY | 11111 | USA | NULL | NULL | | 10008 | Dels Sky | 123 Habe Street | New York | NY | 22222 | USA | NULL | NULL | +---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+ 8 rows in set (0.00 sec)
3.插入检索出的数据
将从CustNew检索的数据假如到Customers。
INSERT INTO Customers(cust_id, cust_contact,cust_email,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country) SELECT cust_id,cust_contact,cust_email,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country FROM CustNew;
INSERT 通常只插入一行。要插入多行，必须执行多个 INSERT 语句。 INSERT SELECT是个例外，它可以用一条INSERT插入多行，不管SELECT 语句返回多少行，都将被 INSERT 插入。
4.从一个表复制到另一个表
用SELECT INTO 或者 CREATE TABLE XX AS SELECT * FROM
mysql> CREATE TABLE CustCopy AS SELECT * FROM Customers; Query OK, 8 rows affected (0.04 sec) Records: 8 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM CustCopy; +---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+ | cust_id | cust_name | cust_address | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email | +---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+ | 10001 | Coyote Inc. | 200 Maple Lane | Detroit | MI | 44444 | USA | Y Lee | ylee@coyote.com | | 10002 | Mouse House | 333 Fromage Lane | Columbus | OH | 43333 | USA | Jerry Mouse | NULL | | 10003 | Wascals | 1 Sunny Place | Muncie | IN | 42222 | USA | Jim Jones | rabbit@wascally.com | | 10004 | Yosemite Place | 829 Riverside Drive | Phoenix | AZ | 88888 | USA | Y Sam | sam@yosemite.com | | 10005 | E Fudd | 4545 53rd Street | Chicago | IL | 54545 | USA | E Fudd | NULL | | 10006 | Toy Land | 123 Any Street | New York | NY | 11111 | USA | NULL | NULL | | 10007 | Toy Sky | 123 Any Street | New York | NY | 11111 | USA | NULL | NULL | | 10008 | Dels Sky | 123 Habe Street | New York | NY | 22222 | USA | NULL | NULL | +---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+ 8 rows in set (0.00 sec) mysql>