超过电子表格

如前一页所示,我们现在有一个名为 person 的空表。我们可以对这样的表做什么呢?就像使用一个容器一样!在其中存储数据,查看以检查数据的存在,修改其中的数据或将其删除。这是四个基本操作,涉及表中的数据:

  • INSERT:将一些数据放入表中
  • SELECT:从表中检索数据
  • UPDATE修改表中已存在的数据
  • DELETE:从表中移除数据

对于这四种操作中的每一种,都有一个对应的 SQL 命令。它们以一个关键字开始,并以分号结束。这个规则适用于所有 SQL 命令:它们由一个关键字引入,并以分号终止。中间可能包含更多的关键字以及对象名称和数值。

使用 INSERT 命令存储新数据

当在表的行中存储新数据时,我们必须命名所有受影响的对象和值:表名(数据库中可能有很多表)、列名和值。所有这些都嵌入在一些关键字中,以便 SQL 编译器能够识别标记及其含义。通常,简单的 INSERT 语法如下:

INSERT INTO <tablename> (<list_of_columnnames>)
VALUES                  (<list_of_values>);

以下是一个示例:

-- 插入一行
INSERT INTO person (id, firstname, lastname,    date_of_birth,     place_of_birth, ssn,           weight)
VALUES             (1,  'Larry',   'Goldstein', date'1970-11-20', 'Dallas',        '078-05-1120', 95);
-- 确认 INSERT 命令
COMMIT;

当 DBMS 识别到关键字 INSERT INTOVALUES 时,它知道该做什么:在表中创建一新行,并将给定的值放入命名的列中。在上述示例中,命令后面跟着第二个命令:COMMIT 确认了 INSERT 操作以及其他写入操作 UPDATEDELETE。(我们将在后面的章节中详细了解 COMMIT 及其对应的 ROLLBACK。)

关于 date_of_birth 值格式的简短说明:全球没有统一的日期格式。人们根据其文化习惯使用不同的格式。出于我们的目的,我们决定使用 ISO 8601 定义的分层格式来表示日期。可能您的本地数据库安装使用不同的格式,因此您被迫要么修改我们的示例,要么修改数据库安装的默认日期格式。

现在我们将向表中插入更多行。为此,我们使用上述语法的一个变体。如果值的列表与原始 CREATE TABLE 语句中使用的列的数量、顺序和数据类型完全对应,则可以省略列名列表。

提示:在实际应用中省略列名列表的做法不推荐!表结构会随着时间变化,例如,可能有人向表中添加新列。在这种情况下,应用程序中可能会出现意想不到的副作用。

-- 插入四行
INSERT INTO person VALUES (2,  'Tom',    'Burton',    date'1980-01-22', 'Birmingham',    '078-05-1121', 75);
INSERT INTO person VALUES (3,  'Lisa',   'Hamilton',  date'1975-12-30', 'Mumbai',        '078-05-1122', 56);
INSERT INTO person VALUES (4,  'Debora', 'Patterson', date'2011-06-01', 'Shanghai',      '078-05-1123', 11);
INSERT INTO person VALUES (5,  'James',  'de Winter', date'1975-12-23', 'San Francisco', '078-05-1124', 75);
COMMIT;

使用 SELECT 命令检索数据

现在我们的表应该包含五行。我们能确定这一点吗?我们如何检查所有操作是否顺利完成,行和数值是否确实存在?为此,我们需要一个命令来显示表的实际内容。这个命令就是 SELECT,其一般语法如下:

SELECT   <list_of_columnnames>
FROM     <tablename>
WHERE    <search_condition>
ORDER BY <order_by_clause>;

INSERT 命令一样,您可以省略某些部分。最简单的示例如下:

SELECT   *
FROM     person;

星号字符 * 表示“所有列”。在结果中,DBMS 应该返回所有五行,每行包含我们之前使用 INSERT 命令插入的七个值。

在以下示例中,我们依次添加了一般语法中目前缺失的子句。

添加部分或所有列名列表

SELECT   firstname, lastname
FROM     person;

DBMS 应该返回所有五行的 firstnamelastname 两列。

添加搜索条件

SELECT   id, firstname, lastname
FROM     person
WHERE    id > 2;

DBMS 应该返回满足条件的三行的 idfirstnamelastname 三列。

添加排序指令

SELECT   id, firstname, lastname, date_of_birth
FROM     person
WHERE    id > 2
ORDER BY date_of_birth;

DBMS 应该按照 date_of_birth 的升序返回满足条件的三行的 idfirstnamelastnamedate_of_birth 四列。

使用 UPDATE 命令修改数据

如果我们想更改表中某些行的某些列的值,可以使用 UPDATE 命令。简单 UPDATE 的一般语法如下:

UPDATE <tablename>
SET    <columnname> = <value>, 
       <columnname> = <value>,
               ...
WHERE  <search_condition>;

值被分配给命名的列。未提及的列保持不变。search_condition 的作用方式与 SELECT 命令中的相同。它将命令的作用范围限制在满足条件的行上。如果省略 WHERE 关键字和 search_condition,则表的所有行都会受到影响。可以指定不匹配任何行的 search_condition。在这种情况下,不会更新任何行——也不会发生错误或异常。


您可以根据需要复制/粘贴这些示例,以帮助您学习和实践SQL。

更改单行的某一列

UPDATE person
SET    firstname = 'James Walker' 
WHERE  id = 5;
COMMIT;

Mr. de Winter 的名字更改为 James Walker,而他的其他所有值保持不变。所有其他行也保持不变。请使用 SELECT 命令进行验证。


更改多行的某一列

UPDATE person
SET    firstname = 'Unknown' 
WHERE  date_of_birth < date'2000-01-01';
COMMIT;

<search_condition> 并不限于主键列。我们可以指定任何其他列。而且比较运算符也不限于等号。我们可以使用不同的运算符——它们只需与列的数据类型匹配。

在此示例中,我们使用一个命令更改了四行的 firstname。如果有一个包含数百万行的表,我们可以使用一个命令更改所有这些行。


更改单行的两列

-- 请注意额外的逗号
UPDATE person
SET    firstname = 'Jimmy Walker', 
       lastname  = 'de la Crux' 
WHERE  id = 5;
COMMIT;

使用一个命令更改了两个值。


使用 DELETE 命令删除数据

DELETE 命令从表中删除完整的行。由于行是整体删除的,因此不需要指定任何列名。<search_condition> 的语义与 SELECTUPDATE 相同。

DELETE
FROM   <tablename>
WHERE  <search_condition>;

删除一行

DELETE
FROM   person
WHERE  id = 5;
COMMIT;

James de Winter 的行被从表中移除。


删除多行

DELETE
FROM   person;
COMMIT;

由于省略了 <search_condition>,所有剩余的行都被删除。表为空,但仍然存在。


未影响任何行

DELETE
FROM   person
WHERE  id = 99;
COMMIT;

此命令不会删除任何行,因为没有 id 等于 99 的行。但语法和在 DBMS 中的执行仍然是完美的。不会抛出任何异常。命令在没有任何错误消息或错误代码的情况下终止。


总结

INSERTDELETE 命令会整体影响行。INSERT 将一个完整的新行放入表中(未提及的列保持为空),而 DELETE 则删除整个行。相比之下,SELECTUPDATE 仅影响命令中提到的列;未提及的列不受影响。

简单版本的 INSERT 命令没有 <search_condition>,因此只处理一行。其他三个命令根据其 <search_condition> 的评估结果,可能影响零行、一行或多行。


您可以根据需要复制/粘贴这些示例,以帮助您学习和实践SQL。

Last modified: Tuesday, 28 January 2025, 12:21 PM