读书笔记--SQL必知必会15--插入数据
来源:互联网 发布:yy挂机软件免费下载 编辑:程序博客网 时间:2024/06/03 19:57
15.1 数据插入
使用INSERT语句将行插入(或添加)到数据库表。可能需要特定的安全权限。
- 插入完整的行
- 插入行的一部分
- 插入某些查询的结果
15.1.1 插入完整的行
要求指定表名和插入到新行中的值。
存储到表中每一列的数据在VALUES子句中给出,必须给每一列提供一个值。如果某列没有值,则应该使用NULL值(假定表允许对该列指定空值)。
各列必须以它们在表定义中的次序填充。
在某些SQL实现中,跟在INSERT之后的INTO关键字是可选的,但建议提供这个关键字,保证移植性。
MariaDB [sqlbzbh]> SELECT * FROM Customers;+------------+---------------+----------------------+-----------+------------+----------+--------------+--------------------+-----------------------+| cust_id | cust_name | cust_address | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email |+------------+---------------+----------------------+-----------+------------+----------+--------------+--------------------+-----------------------+| 1000000001 | Village Toys | 200 Maple Lane | Detroit | MI | 44444 | USA | John Smith | sales@villagetoys.com || 1000000002 | Kids Place | 333 South Lake Drive | Columbus | OH | 43333 | USA | Michelle Green | NULL || 1000000003 | Fun4All | 1 Sunny Place | Muncie | IN | 42222 | USA | Jim Jones | jjones@fun4all.com || 1000000004 | Fun4All | 829 Riverside Drive | Phoenix | AZ | 88888 | USA | Denise L. Stephens | dstephens@fun4all.com || 1000000005 | The Toy Store | 4545 53rd Street | Chicago | IL | 54545 | USA | Kim Howard | NULL |+------------+---------------+----------------------+-----------+------------+----------+--------------+--------------------+-----------------------+5 rows in set (0.00 sec)MariaDB [sqlbzbh]> MariaDB [sqlbzbh]> INSERT INTO Customers VALUES('1000000006','Toy Land','123 Any Street','New York','NY','11111','USA',NULL,NULL);Query OK, 1 row affected (0.00 sec)MariaDB [sqlbzbh]> MariaDB [sqlbzbh]> SELECT * FROM Customers;+------------+---------------+----------------------+-----------+------------+----------+--------------+--------------------+-----------------------+| cust_id | cust_name | cust_address | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email |+------------+---------------+----------------------+-----------+------------+----------+--------------+--------------------+-----------------------+| 1000000001 | Village Toys | 200 Maple Lane | Detroit | MI | 44444 | USA | John Smith | sales@villagetoys.com || 1000000002 | Kids Place | 333 South Lake Drive | Columbus | OH | 43333 | USA | Michelle Green | NULL || 1000000003 | Fun4All | 1 Sunny Place | Muncie | IN | 42222 | USA | Jim Jones | jjones@fun4all.com || 1000000004 | Fun4All | 829 Riverside Drive | Phoenix | AZ | 88888 | USA | Denise L. Stephens | dstephens@fun4all.com || 1000000005 | The Toy Store | 4545 53rd Street | Chicago | IL | 54545 | USA | Kim Howard | NULL || 1000000006 | Toy Land | 123 Any Street | New York | NY | 11111 | USA | NULL | NULL |+------------+---------------+----------------------+-----------+------------+----------+--------------+--------------------+-----------------------+6 rows in set (0.00 sec)MariaDB [sqlbzbh]>
表的结构有可能变动,因此编写特定次序的SQL语句,后期可能无法使用。
可以在表名后的括号里明确相应的列名,这样相应的值就会填入列表中的对应项,即使表的结构改变,INSERT语句仍然能够正确工作。
推荐明确给出表的列名,不要使用没有明确给出列的INSERT语句。
MariaDB [sqlbzbh]> INSERT INTO Customers(cust_id,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country,cust_contact,cust_email) -> VALUES('1000000006','Toy Land','123 Any Street','New York','NY','11111','USA',NULL,NULL);Query OK, 1 row affected (0.00 sec)MariaDB [sqlbzbh]>
因为给出了列名,并且和相应值对应,插入数据就可以不按各列出现在表中的实际次序。
MariaDB [sqlbzbh]> INSERT INTO Customers(cust_id,cust_contact,cust_email,cust_name,cust_address,cust_city,cust_state,cust_zip) -> VALUES('1000000006',NULL,NULL,'Toy Land','123 Any Street','New York','NY','11111');Query OK, 1 row affected (0.01 sec)MariaDB [sqlbzbh]>
15.1.2 插入部分行
如果表的定义允许有NULL值或者默认值,可以只明确给出表的某些列名,并只给这些列提供值,其他列不提供,也就是省略了某些列。
省略的列必须满足以下某个条件:
- 该列定义为允许NULL值。
- 在表定义中给出默认值(如果不给出值,将使用默认值)。
15.1.3 插入检索出的数据
可以利用INSERT语句将SELECT语句的结果插入表中。
所谓的INSERT SELECT,顾名思义,就是由一条INSERT语句和一条SELECT语句组成的。
INSERT和SELECT语句是使用列的位置,因此列名不一定匹配。
INSERT SELECT中的SELECT语句可以包含WHERE子句。
INSERT通常只插入一行,而INSERT SELECT插入的行数取决于SELECT语句返回多少行。
MariaDB [sqlbzbh]> SELECT * FROM Customers;+------------+---------------+----------------------+-----------+------------+----------+--------------+--------------------+-----------------------+| cust_id | cust_name | cust_address | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email |+------------+---------------+----------------------+-----------+------------+----------+--------------+--------------------+-----------------------+| 1000000001 | Village Toys | 200 Maple Lane | Detroit | MI | 44444 | USA | John Smith | sales@villagetoys.com || 1000000002 | Kids Place | 333 South Lake Drive | Columbus | OH | 43333 | USA | Michelle Green | NULL || 1000000003 | Fun4All | 1 Sunny Place | Muncie | IN | 42222 | USA | Jim Jones | jjones@fun4all.com || 1000000004 | Fun4All | 829 Riverside Drive | Phoenix | AZ | 88888 | USA | Denise L. Stephens | dstephens@fun4all.com || 1000000005 | The Toy Store | 4545 53rd Street | Chicago | IL | 54545 | USA | Kim Howard | NULL |+------------+---------------+----------------------+-----------+------------+----------+--------------+--------------------+-----------------------+5 rows in set (0.00 sec)MariaDB [sqlbzbh]> MariaDB [sqlbzbh]> SELECT * FROM CustNew;+------------+-----------+----------------+-----------+------------+----------+--------------+--------------+------------+| cust_id | cust_name | cust_address | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email |+------------+-----------+----------------+-----------+------------+----------+--------------+--------------+------------+| 1000000006 | Toy Land | 123 Any Street | New York | NY | 11111 | USA | NULL | NULL |+------------+-----------+----------------+-----------+------------+----------+--------------+--------------+------------+1 row in set (0.00 sec)MariaDB [sqlbzbh]> MariaDB [sqlbzbh]> MariaDB [sqlbzbh]> INSERT INTO Customers(cust_id,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country,cust_contact,cust_email) -> SELECT cust_id,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country,cust_contact,cust_email -> FROM CustNew;Query OK, 1 row affected (0.01 sec)Records: 1 Duplicates: 0 Warnings: 0MariaDB [sqlbzbh]> MariaDB [sqlbzbh]> SELECT * FROM Customers;+------------+---------------+----------------------+-----------+------------+----------+--------------+--------------------+-----------------------+| cust_id | cust_name | cust_address | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email |+------------+---------------+----------------------+-----------+------------+----------+--------------+--------------------+-----------------------+| 1000000001 | Village Toys | 200 Maple Lane | Detroit | MI | 44444 | USA | John Smith | sales@villagetoys.com || 1000000002 | Kids Place | 333 South Lake Drive | Columbus | OH | 43333 | USA | Michelle Green | NULL || 1000000003 | Fun4All | 1 Sunny Place | Muncie | IN | 42222 | USA | Jim Jones | jjones@fun4all.com || 1000000004 | Fun4All | 829 Riverside Drive | Phoenix | AZ | 88888 | USA | Denise L. Stephens | dstephens@fun4all.com || 1000000005 | The Toy Store | 4545 53rd Street | Chicago | IL | 54545 | USA | Kim Howard | NULL || 1000000006 | Toy Land | 123 Any Street | New York | NY | 11111 | USA | NULL | NULL |+------------+---------------+----------------------+-----------+------------+----------+--------------+--------------------+-----------------------+6 rows in set (0.00 sec)MariaDB [sqlbzbh]>
15.2 从一个表复制到另一个表
SELECT INTO语句可以将一个表的内容复制到一个全新的表(运行中创建的表)。
- 任何SELECT选项和子句都可以使用
- 可利用联结从多个表插入数据
- 数据都只能一次插入到一个表中
在MySQL和MariaDB中使用的语法不同于SELECT INTO语句。
MariaDB [sqlbzbh]> SHOW TABLES;+-------------------+| Tables_in_sqlbzbh |+-------------------+| CustNew || Customers || OrderItems || Orders || Products || Vendors |+-------------------+6 rows in set (0.00 sec)MariaDB [sqlbzbh]> MariaDB [sqlbzbh]> CREATE TABLE CustCopy AS SELECT * FROM CustNew;Query OK, 1 row affected (0.01 sec)Records: 1 Duplicates: 0 Warnings: 0MariaDB [sqlbzbh]> MariaDB [sqlbzbh]> SHOW TABLES;+-------------------+| Tables_in_sqlbzbh |+-------------------+| CustCopy || CustNew || Customers || OrderItems || Orders || Products || Vendors |+-------------------+7 rows in set (0.00 sec)MariaDB [sqlbzbh]> MariaDB [sqlbzbh]> SELECT * FROM CustCopy -> ;+------------+-----------+----------------+-----------+------------+----------+--------------+--------------+------------+| cust_id | cust_name | cust_address | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email |+------------+-----------+----------------+-----------+------------+----------+--------------+--------------+------------+| 1000000006 | Toy Land | 123 Any Street | New York | NY | 11111 | USA | NULL | NULL |+------------+-----------+----------------+-----------+------------+----------+--------------+--------------+------------+1 row in set (0.00 sec)MariaDB [sqlbzbh]>
- 读书笔记--SQL必知必会15--插入数据
- SQL读书笔记(十二) 插入数据
- Database - 读书笔记--SQL必知必会04--过滤数据
- 读书笔记--SQL必知必会02--检索数据
- 读书笔记--SQL必知必会03--排序检索数据
- 读书笔记--SQL必知必会04--过滤数据
- 读书笔记--SQL必知必会05--高级数据过滤
- 读书笔记--SQL必知必会09--汇总数据
- 读书笔记--SQL必知必会10--分组数据
- SQL必知必会 笔记 第十五章 插入数据
- sql 循环插入数据
- sql 循环插入数据
- sql 循环插入数据
- SQL 批量插入数据
- sql批量插入数据
- sql插入二进制数据
- sql 插入多个表数据
- SQL-INSERT-插入数据
- 读书笔记--SQL必知必会13--创建高级联结
- Find The Multiple
- 读书笔记--SQL必知必会14--组合查询
- Tools
- Tools
- 读书笔记--SQL必知必会15--插入数据
- 读书笔记--SQL必知必会16--更新和删除数据
- VMware网络配制说明
- 读书笔记--SQL必知必会17--创建和操纵表
- 读书笔记--SQL必知必会18--视图
- 读书笔记--SQL必知必会19--存储过程
- hdu-1242 Rescue
- 读书笔记--SQL必知必会20--管理事务处理
- 读书笔记--SQL必知必会21--使用游标