第十五课 插入数据
#利用SQL的INSERT语句将数据插入表中
#将行插入到数据库表中
#使用INSERT语句明确使用列名的原因
#用INSERT SELECT从其他表中导入行
#用SELECT INTO将行导出到一个新表
一、数据插入
#INSERT
用来将行插入(或添加)到数据库表中
插入方式:
- 插入完整的行
- 插入行的一部分
- 插入某些查询的结果
注意1:
插入及系统安全件:
#使用 INSERT语句可能需要客户端/服务器DBMS中的特定安全权限
#使用INSERT前,应该保证自己有足够的安全权限
1.1 插入完整的行:
#把数据插入表中最简单的方法是使用基本的INSERT语法
#基本的INSERT语法要求指定表名和插入到新行中的值
Customers表
【1】INSERT INTO customers VALUES('1000000006', 'Toy Land', '123 Any Street', 'New York', 'NY', '11111', 'USA', NULL, NULL);
#将一个新顾客插入到Customers表中
#存储到表中每一列的数据在 VALUES子句中给出,必须给每一列提供一个值
#如果某列没有值,则应该使用NULL值(假定表允许对该列指定空值),如cust_contact和cust_email列
#各列必须以它们在表定义中出现的次序填充
#虽然此语句语法简单,单并不安全,应尽量避免使用
#上述SQL语句高度依赖表中列的定义次序,还依赖与容易获得的次序信息
#不能保证各列在下一次表结构变动后保持完全相同的次序
注意2:
INTO关键字:
#在某些SQL实现中,跟在INSERT之后的INTO关键字是可选的
#即使不一定需要,最好提供这个关键字,保证SQL代码在DBMS之间的可移植性
#编写依赖于特定次序的SQL语句是很不安全的,迟早出问题
【2】INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email) VALUES('1000000007', 'Toy Landa', '123 Any Streeta', 'New Yorka', 'NYa', '11112', 'USA', NULL, NULL);
#编写INSERT语句更安全的方法(也更繁琐)
#与前一个SQL语句的工作完全相同,但表名后的括号里明确给出了列名
#在插入行时,DBMS用列表中的相应值填入列表中的对应项
#VALUES中的第一个值对应于第一个指定列名,第二个值对应于第二个列名
#因提供列名,VALUES必须以其指定的次序匹配指定的列名,不一定按各列出现在表中的实际次序
#优点在于,即使表的结构改变,这条INSERT语句仍能正确工作
【3】INSERT INTO customers(cust_id, cust_contact, cust_email, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country ) VALUES('1000000008', NULL, NULL, 'Toy Landaw', '123 Any Streetb', 'New Yorkb', 'NYb', '11113', 'USA');
#INSERT语句填充所有列,但以不同的次序填充
#因给出列名,插入结果仍然正确
注意3:
总是使用列的列表:
#不要使用没有明确给出列的INSERT语句
#给出列能使SQL代码继续发挥作用,即使表的结构发生变化
注意4:
小心使用VALUES:
#不管使用哪种INSERT语法,VALUES的数目都必须正确
#如果不提供列名,则必须给每个表列提供一个值
#如果提供列名,则必须给列出的每个列一个值,否则会报错,行不能插入成功
1.2 插入部分行:
#使用INSERT的推荐方法是明确给出表的列名
#使用这种语法,还可以省略列
#即可以给某些列提供值,给其他列不提供值
【4】INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country ) VALUES('1000000009', 'Toy Landc', '123 Any Streetc', 'New Yorkc', 'NYc', '11114', 'USA');
#本例没有给cust_contact和cust_email这两列提供值
#INSERT语句省略了这两列及其对应的值
注意5:
省略列:
#如果表的定义允许,则可以在INSERT操作中省略某些列
#省略列必须满足以下某个条件:
[1]该列定义为允许NULL值(无值或空值)
[2]在表定义中给出默认值。即表示如果不给出值,将使用默认值
#如果表中不允许有NULL值或默认值,此时省略表中的值,DBMS就会产生错误信息,相应行不能成功插入
1.3 插入检索出的数据:
#INSERT一般用来给表插入具有指定列值的行
#INSERT也可将SELECT语句的结果插入表中,即所谓的INSERT SELECT语句,由一条INSERT语句和一条SELECT语句组成
创建新custnew表:
【5】CREATE TABLE custnew ( cust_id char(10) NOT NULL , cust_name char(50) NOT NULL , cust_address char(50) NULL , cust_city char(50) NULL , cust_state char(5) NULL , cust_zip char(10) NULL , cust_country char(50) NULL , cust_contact char(50) NULL , cust_email char(255) NULL ); ALTER TABLE custnew ADD PRIMARY KEY (cust_id); INSERT INTO custnew(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email) VALUES('1000000010', 'Village Toyb', '200 Maple Lane', 'Detroit', 'MI', '44444', 'USA', 'John Smith', 'sales@villagetoys.com'); INSERT INTO custnew(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email) VALUES('1000000011', 'llage Toyb', '200 Maple Lane', 'Detroit', 'MI', '44444', 'USA', 'John Smith', 'sales@villagetoys.com'); INSERT INTO custnew(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email) VALUES('1000000012', 'illage Toyb', '200 Maple Lane', 'Detroit', 'MI', '44444', 'USA', 'John Smith', 'sales@villagetoys.com'); INSERT INTO custnew(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email) VALUES('1000000013', 'lage Toyb', '200 Maple Lane', 'Detroit', 'MI', '44444', 'USA', 'John Smith', 'sales@villagetoys.com');
#将另一个表custnew中的顾客列合并到customers表中
#不需要每次读取一行再将它用INSERT插入
【6】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 SELECT从custnew表中将所有数据导入customers
#SELECT语句从custnew检索出要插入的值
#SELECT中列出的每一列对应于customers表名后所跟的每一列
#插入多少行依赖于custnew表有多少行
#若custnew表为空,则没有行被插入(不产生错误,此操作合法)
#若custnew表有数据,则所有数据将被插入到customers表
注意6:
INSERT SELECT中的列名:
#为简单起见,上例在INSERT和SELECT语句中使用了相同的列名
#但上例不一定要求列名匹配
#DBMS不关心SELECT返回的列名。 它使用的是列的位置
#SELECT中的第一列(不管其列名)将用来填充表列中指定的第一列,第二列将用来填充表列中指定的第二列
注意7:
#INSERT SELECT中SELECT语句可以包含WHERE子句,以过滤插入的数据
注意8:
插入多行:
#INSERT通常只插入一行,要插入多行,必须执行多个INSERT语句
#INSERT SELECT是个例外,可用一条INSERT语句插入多行,不管SELECT语句返回多少行,都将被INSERT插入
二、从一个表复制到另一个表
#有一种数据插入不使用INSERT语句
#将一个表的内容复制到一个全新的表(运行中创建的表),可以使用SELECT INTO语句
#与INSERT SELECT将数据添加到一个已经存在的表不同,SELECT INTO将数据复制到一个新表(有的DBMS可以覆盖已经存在的表,这依赖于所使用的DBMS)
注意9:
- DB2不支持SELECT INTO语句
- INSERT SELECT与SELECT INTO的一个重要差别是前者插入数据,后者导出数据
【7】SELECT * INTO custcopy from customers;
#Oracle,MySQL和SQLite不支持此种语法
#此SELECT语句创建一个名为custcopy的新表
#把custcomers表的整个内容复制到新表custcopy中
#使用SELECT * 将在custcopy表中创建并填充与customers表的每一列相同的列
#要想只复制部分列,可以明确给出列名,而不是使用 * 通配符
【8】CREATE TABLE custcopy AS SELECT * from customers;
#上例MySQL中使用的语法
注意10:
使用SELECT INTO注意:
- 任何SELECT选项和子句都可以使用,包括WHERE和GROUP BY
- 可以利用联结从多个表插入数据
- 不管从多少个表中检索数据,数据都只能插入到一个表中
进行表的复制:
- SELECT INTO是试验新SQL语句前进行表复制的很好工具
- 先进行表复制,在复制的数据上测试SQL代码,不会影响实际数据
程序代码(DBMS:MySQL):
INSERT INTO customers
VALUES('1000000006',
'Toy Land',
'123 Any Street',
'New York',
'NY',
'11111',
'USA',
NULL,
NULL);
INSERT INTO customers(cust_id,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country,
cust_contact,
cust_email)
VALUES('1000000007',
'Toy Landa',
'123 Any Streeta',
'New Yorka',
'NYa',
'11112',
'USA',
NULL,
NULL);
INSERT INTO customers(cust_id,
cust_contact,
cust_email,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country
)
VALUES('1000000008',
NULL,
NULL,
'Toy Landaw',
'123 Any Streetb',
'New Yorkb',
'NYb',
'11113',
'USA');
INSERT INTO customers(cust_id,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country
)
VALUES('1000000009',
'Toy Landc',
'123 Any Streetc',
'New Yorkc',
'NYc',
'11114',
'USA');
CREATE TABLE custnew
(
cust_id char(10) NOT NULL ,
cust_name char(50) NOT NULL ,
cust_address char(50) NULL ,
cust_city char(50) NULL ,
cust_state char(5) NULL ,
cust_zip char(10) NULL ,
cust_country char(50) NULL ,
cust_contact char(50) NULL ,
cust_email char(255) NULL
);
ALTER TABLE custnew ADD PRIMARY KEY (cust_id);
INSERT INTO custnew(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES('1000000010', 'Village Toyb', '200 Maple Lane', 'Detroit', 'MI', '44444', 'USA', 'John Smith', 'sales@villagetoys.com');
INSERT INTO custnew(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES('1000000011', 'llage Toyb', '200 Maple Lane', 'Detroit', 'MI', '44444', 'USA', 'John Smith', 'sales@villagetoys.com');
INSERT INTO custnew(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES('1000000012', 'illage Toyb', '200 Maple Lane', 'Detroit', 'MI', '44444', 'USA', 'John Smith', 'sales@villagetoys.com');
INSERT INTO custnew(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES('1000000013', 'lage Toyb', '200 Maple Lane', 'Detroit', 'MI', '44444', 'USA', 'John Smith', 'sales@villagetoys.com');
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;
CREATE TABLE custcopy AS
SELECT * from customers;
需要完整代码的小伙伴可关注微信公众号:菜田里守望者
打开微信扫一扫关注吧,你们的支持就是我的动力
参考文献:
【1】《SQL必知必会》第四版 人民邮电出版社 [美] Ben Forta 著 钟鸣 刘晓霞 译