插入
INSERT
用来将行插入到数据库表中。插入有几种方式:
- 插入完整的行。
- 插入行的一部分。
- 插入某些查询的结果。
INSERT
语法要求指定表名和插入到新行中的值。例如,我们将一个新顾客插入到 customers 表中。
INSERT INTO Customers VALUES('1000000006', 'Toy Land', '123 Any Street', 'New York', 'NY', '11111', 'USA', 'Jordan', 'Jordan@gmail.com');
插入到新行中的值由 VALUES
子句给出,以这种方式插入数据时,我们必须为每一列提供一个值。而且各列必须以他们在表定义中出现的次序填充。虽然这种插入数据的语法很简单,但并不安全,应该尽量避免使用。因为上面的 SQL 语句高度依赖于表中列的定义次序。比较安全的写法,如下:
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', 'Jordan', 'Jordan@gmail.com');
在上述写法中,我们在表名后的括号里明确指定了列名,VALUES
中的第一个值对应于第一个指定列名,第二个值对应于第二个列名,依次类推。因为提供了列名,只要 VALUES
中的值和指定的列名能匹配上就行,不一定按照各列出现在表中的实际次序。这样做的好处是,即使表结构发生改变,这条 INSERT 语句仍然能正确工作。
由于明确指定了列名,所以我们可以随意指定列的顺序。例如,我们也可以如下插入数据:
INSERT INTO Customers(cust_id, cust_contact, cust_email, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country)
VALUES('1000000006', 'Jordan', 'Jordan@gmail.com', 'Toy Land', '123 Any Street', 'New York', 'NY', '11111',
'USA');
注意事项:
- 不要使用没有明确给出列的
INSERT
语句。给出列能使 SQL 代码继续发挥作用,即使表结构发生了变化。 - 不管使用哪种
INSERT
语法,VALUES
的数目都必须正确。如果不提供列名,则必须给每个表列提供一个值;如果提供列名,则必须给列出的每个列一个值。否则,就会产生一条错误消息,相应的行不能成功插入。
由于明确列出了列名,我们在进行插入数据时,可以省略某些列,这表示可以只给某些列提供值,给其他列不提供值。例如,我们可以如下插入数据:
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country)
VALUES('1000000006', 'Toy Land', '123 Any Street', 'New York', 'NY', '11111', 'USA');
在这个例子中,我们省略了 cust_contact
和 cust_email
这两列。
如果表的定义允许,则可以在 INSERT
操作中省略某些列。省略的列必须满足以下某个条件:
- 该列定义为允许
NULL
值。 - 在表定义中给出默认值。这表示如果不给出值,将使用默认值。
如果表中不允许有NULL
值或者默认值,这时却省略了表中的值, DBMS 就会产生错误消息,相应的行不能成功插入。
INSERT
还可以用来将查询出的数据插入到表中。就是所谓的 INSERT SELECT
。它由一条 INSERT
语句和一条 SELECT
语句组成。例如,我们从 customersnew 表中查询数据插入到 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 customersnew;
在这个例子中,INSERT
和 SELECT
语句中使用了相同的列名。但是,不一定要求列名匹配。事实上,DBMS 一点儿也不关心 SELECT
返回的列名。它使用的是列的位置,因此 SELECT
中的第一列(不管其列名)将用来填充表列中指定的第一列,第二列将用来填充表列中指定的第二列,如此等等。
INSERT SELECT
中 SELECT
语句可以包含 WHERE 子句,以过滤插入的数据。
另外,有时候,我们需要把一个表的内容复制到另外一张表。这时候我们可以使用:
CREATE TABLE custcopy AS
SELECT * FROM customers;
更新
下面我们来讲下如何更新数据:
更新表中的数据可以使用 UPDATE
语句,有两种使用 UPDATE
的方式:
- 更新表中的特定行。
- 更新表中的所有行。
使用 UPDATE
语句非常容易,基本的 UPDATE
语句由三部分组成:
- 要更新的表。
- 列名和它们的新值。
- 确定要更新哪些行的过滤条件。
举个简单的例子,例如我们要更新客户 1000000005 的电子邮箱地址,语句如下:
UPDATE customers
SET cust_email = 'kim@thetoystore.com'
WHERE cust_id = '1000000005';
UPDATE
语句以 WHERE
子句结束,它告诉 DBMS 更新哪一行。没有 WHERE
子句,DBMS 将会用这个电子邮件地址更新 Customers 表中的所有行, 这不是我们希望的。
更新多列的语法稍微不同,例如:
UPDATE Customers
SET cust_contact = 'Sam Roberts',
cust_email = 'sam@toyland.com'
WHERE cust_id = '1000000006';
在更新多个列时,只需要使用一条 SET 命令,每个“列=值”对之间用逗号分隔(最后一列之后不用逗号)。在此例子中,更新顾客 1000000006 的 cust_contact 和 cust_email 列。
要删除某个列的值,可设置它为 NULL
。例如:
UPDATE Customers
SET cust_email = NULL
WHERE cust_id = '1000000005';
在使用 UPDATE
语句时,不要省略 WHERE
子句,因为稍不注意,就会更新表中所有的行。
删除
下面来讲下如何删除数据:
从一个表中删除数据,使用 DELETE
语句。有两种使用 DELETE
的方式:
- 从表中删除特定的行;
- 从表中删除所有行。
例如:从 Customers 表中删除一行:
DELETE FROM Customers
WHERE cust_id = '1000000006';
DELETE
不需要列名或通配符。DELETE
删除整行而不是删除列。要删除指定的列,请使用 UPDATE
语句。
如果想从表中删除所有行,不要使用 DELETE
。可使用 TRUNCATE TABLE
语句,它完成相同的工作,而速度更快(因为不记录数据的变动)。
使用 UPDATE
和 DELETE
时所遵循的重要原则:
- 除非确实打算更新和删除每一行,否则绝对不要使用不带
WHERE
子句的UPDATE
或DELETE
语句。 - 在
UPDATE
或DELETE
语句使用WHERE
子句前,应该先用SELECT
进行测试,保证它过滤的是正确的记录,以防编写的WHERE
子句不正确。
备注:示例中用到的表和数据可以通过链接(https://github.com/songw/sql)下载。
本系列目录:零基础入门 SQL 系列之(一)查询数据
零基础入门 SQL 系列之(二)排序
零基础入门 SQL 系列之(三)过滤数据
零基础入门 SQL 系列之(四)内置函数
零基础入门 SQL 系列之(五)数据汇总
零基础入门 SQL 系列之(六)表连接
零基础入门 SQL 系列之(七)组合
零基础入门 SQL 系列之(八)插入、更新、删除
零基础入门 SQL 系列之(九)创建和操作表
零基础入门 SQL 系列之(十)视图