数据插入
INSERT
用来将行插入(或添加)到数据库表;插入有几种方式:
- 插入完整的行
- 插入行的一部分
- 插入某些查询的结果
- 插入多行
插入及系统安全:
使用 INSERT
语句可能需要客户端 / 服务器 DBMS 中的特定安全权限,所以在使用 INSERT
前,应该保证自己有足够的安全权限
插入完整的行
把数据插入表中的最简单的方式是使用基本的 INSERT
语法,它要求指定表名和插入到新行中的值;例如:
INSERT INTO Customers
VALUES('10006','Toy Land',
'123 Any Street', 'New York',
'NY', '11111', 'USA', NULL, NULL);
INSERT
语句一般不会产生输出
这个例子将一个新顾客插入到 Customers 表中
存储到表中每一列的数据在 VALUES
子句中给出,必须给每一列提供一个值
如果某列没有值,如上面的 cust_contact 和 cust_email 列,则应该使用 NULL
值(假定表允许对该列指定空值
)
各列必须以它们在表定义中出现的次序填充
虽然上述例子的代码语法简单,但是并不安全,应该尽量避免使用
上面的 SQL 语句高度依赖于表中列的定义顺序,还依赖于其容易获得的次序信息
即使可以得到这样的次序信息,也不能保证各列在下一次表结构变动后保持完全相同的次序
编写 INSERT
语句的更安全(也更繁琐)的方法如下:
INSERT INTO Customers( cust_id, cust_name, cust_address,
cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES('10007', 'Charlie', '123 Any Street',
'New York', 'NY', '11111', 'USA', NULL, NULL);
这个例子与前一个 INSERT
语句的作用一样,但是在表名后的括号中明确给出了列名
在插入行时,MySQL 将用 VALUES
列表中的相应值填入列表中的对应项
VALUES
中的第一个值对应于第一个指定列名,第二个值对应于第二个列名,等等
因为提供了列名,VALUES
必须以其指定的次序匹配指定的列名,不一定按各列出现在表中的实际次序
优点是:即使表的结构改变,这条
INSERT
语句仍然是可以正确工作的
总是使用列的列表:
不要使用没有明确给出列的INSERT
语句;
给出列能使 SQL 代码继续发挥作用,即使表的结构发生了变化
小心使用
VALUES
:
不管使用哪种INSERT
语法,VALUES
的数目都必须正确
如果不提供列名,则必须给每个表列提供一个值
如果提供列名,则必须给列出的每个列一个值
否则,将产生一个错误信息,相应的行不能插入成功
插入部分行
使用 INSERT
的推荐方法是明确给出表的列名,使用这种方法还可以省略列
也就是,可以只给某些列提供值,给其他列不提供值
mysql> INSERT INTO Customers(cust_id, cust_name,
-> cust_address, cust_city, cust_state,
-> cust_zip, cust_country)
-> VALUES('10008', 'Mike',
-> '123 Any Street', 'New York', 'NY',
-> '11111', 'USA');
在这个例子中,没有给出 cust_contact 和 cust_email 这两列提供值,这表示没必要在 INSERT
语句中包含它们
因此,这里的 INSERT
语句省略了这两列及其对应的值
省略列:
如果表的定义允许,则可以在INSERT
操作中省略某些列,省略的列必须满足以下某个条件:
- 该列定义为允许 NULL 值(无值或空值)
- 在表定义中给出默认值;即,如果不给出值,将使用默认值
如果表中不允许有
NULL
值或者默认值,这时却省略了表中的值,DBMS 就会产生错误信息,相应的行将不能成功插入
提高整体性能 :
数据库经常被多个客户访问,对处理什么请求以及用什么次序处理进行管理是 MySQL 的任务
INSERT
操作可能很耗时(特别是有很多索引需要更新时),而且它可能降低等待处理的SELECT
语句的性能
如果数据检索是最重要的(通常情况),则可以通过在INSERT
和INTO
之间添加关键字LOW_PRIORITY
,指示 MySQL 降低INSERT
语句的优先级
INSERT LOW_PRIORITY INTO
同样适用于UPDATE 和 DELETE
插入多个行
一个 INSERT
语句可以插入一行到一个表中,如果想要插入多个行,则可以使用多条 INSERT
语句,甚至一次提交,每个语句使用一个分号结束
INSERT INTO customers(cust_name, cust_address,
-> cust_city, cust_state, cust_zip, cust_country)
-> VALUES('Pep E. LaPew', '100 Main Street',
-> 'Los Angeles', 'CA', '90046', 'USA');
INSERT INTO customers(cust_name, cust_address,
-> cust_city, cust_state, cust_zip, cust_country)
-> VALUES('Pepw', '123 Main Street',
-> 'Los Angeles', 'CA', '90896', 'USA');
或者,只要每条 INSERT
语句中的列名(和次序相同),可以如下组合各语句:
INSERT INTO customers(cust_name, cust_address,
-> cust_city, cust_state, cust_zip, cust_country)
-> VALUES('Pep E. LaPew', '100 Main Street',
-> 'Los Angeles', 'CA', '90046', 'USA'),
-> ('Pepw', '123 Main Street',
-> 'Los Angeles', 'CA', '90896', 'USA');
其中单条
INSERT
语句有多组值,每组值用一对圆括号括起来,用逗号分隔
此技术可以提高数据库处理的性能,因为 MySQL 用单条INSERT
语句处理多个插入比使用多条INSERT
语句快
插入检索出的数据
INSERT
一般用来给表插入具有指定列值的行
INSERT
还存在另一种形式,可以利用它将 SELECT
语句的结果插入表中;即 INSERT SELECT
顾名思义,INSERT SELECT
是由一条 INSERT
语句和一条 SELECT
语句组成的
假如想把另一个表中的顾客列合并到 Customers 表中;不需要每次读取一行再将它用 INSERT
插入,可以如下进行:
mysql> 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;
上述代码从一个名为 CustNew 的表中读出数据并插入到 Customers 表
为了实现上述代码,应该首先创建和填充 CustNew 表,在填充 CustNew 时,不应该使用已经在 Customers 中使用过的 cust_id 值(如果主键值重复后续的 INSERT
操作将会失败)
使用 INSERT SELECT
从 CustNew 中将所有数据导入 Customers
SELECT
语句从 CustNew 检索出要插入的值,而不是列出它们;SELECT
中列出的每一列对应于 Customers 表名后所跟的每一列
这条语句插入多少行,取决于 CustNew 表中有多少行;如果表中为空,则没有行被插入(不产生错误,因为操作仍然是合法的);如果表中确实有数据,则所有的数据将被插入到 Customers
INSERT SELECT
中的列名:
为了简单起见,上述代码中在INSERT
和SELECT
语句中使用了相同的列名
但是,不一定要求列名匹配!
事实上,MySQL 一点也不关心SELECT
返回的列名,它使用的是列的位置,因此SELECT
中的第一列(不管其列名)将用来填充表列中指定的第一列,第二列将用来填充表列中指定的第二列,等等
INSERT SELECT
中 SELECT
语句可以包含 WHERE
子句,以过滤插入的数据
插入多行:
INSERT
通常只插入一行;如果要插入多行,则必须执行多个 INSERT
语句
INSERT SELECT
是个例外,它可以用一条 INSERT
插入多行,不管 SELECT
语句返回多少行,都将被 INSERT
插入
从一个表复制到另一个表
有一种数据插入不使用 INSERT
语句
要将一个表的内容复制到一个全新的表(运行中创建的表),可以使用 SELECT INTO
语句
与 INSERT SELECT
将数据添加到一个已经存在的表不同,SELECT INTO
将数据复制到一个新表(有的 DBMS 会覆盖已经存在的表)
INSERT SELECT
与SELECT INTO
:
两者最大的差别在于,前者导出数据,后者导入数据
如下代码,创建一个名为 CustCopy 的新表,并把 Customers 表的整个内容复制到新表中;这里使用的是 SELECT *
, 所以将在 CustCopy 表中创建并填充与 Customers 表的每一列相同的列;如果只想复制部分的列,可以明确给出列名,而不是使用 *
通配符
在 MySQL 中 SELECT INTO
对应的语法:
CREATE TABLE CustCopy AS
SELECT * FROM Customers;
使用
SELECT INTO
时,需要知道:
- 任何
SELECT
选项和子句都可以使用,包括WHERE
和GROUP BY
- 可利用联结从多个表插入数据
- 不管从多少个表中检索数据,数据都只能插入到一个表中
进行表的复制:
SELECT INTO
是试验新 SQL 语句前进行表复制的很好的工具;先进行复制,可在复制的数据上测试 SQL 代码,而不会影响实际的数据