SQL 必知必会(第 4 版)(2013)
第十三课 创建高级联结
-
使用表别名
- SQL 除了可以对列名和计算字段使用别名,还允许给表名起别名。
SELECT cust_name, cust_contact FROM Customers AS C, Orders AS O, OrderItems AS OI WHERE C.cust_id = O.cust_id AND OI.order_num = O.order_num AND prod_id = 'RGAN01';
- 不仅能用于 WHERE 子句,还可以用于 SELECT 的列表、ORDER BY 子句以及其他语句部分。
- 需要注意,表别名只在查询执行中使用。与列别名不一样,表别名不返回到客户端。
-
使用不同类型的联结
- 三种其他联结:自联结(self-join)、自然联结(natural join)和外联结(outer join)。
- 自联结
- 自联结通常作为外部语句,用来替代从相同表中检索数据的使用子查询语句。
- 自然联结
- 无论何时对表进行联结,应该至少有一列不止出现在一个表中(被联结的列)。标准的联结(前一课中介绍的内联结)返回所有数据,相同的列甚至多次出现。自然联结排除多次出现,使每一列只返回一次。
- 迄今为止建立的每个内联结都是自然联结,很可能永远都不会用到不是自然联结的内联结。
- 外联结
SELECT Customers.cust_id, Orders.order_num FROM Customers LEFT OUTER JOIN Orders ON Customers.cust_id = Orders.cust_id;
- 在使用 OUTER JOIN 语法时,必须使用 RIGHT 或 LEFT 关键字指定包括其所有行的表。
- 调整 FROM 或 WHERE 子句中表的顺序,左外联结可以转换为右外联结。
-
使用带聚集函数的联结
- 检索所有顾客及每个顾客所下的订单数:
SELECT Customers.cust_id, COUNT(Orders.order_num) AS num_ord FROM Customers INNER JOIN Orders ON Customers.cust_id = Orders.cust_id GROUP BY Customers.cust_id;
-
使用联结和联结条件
- 联结及其使用的要点
- 注意所使用的联结类型。一般我们使用内联结,但使用外联结也有效。
- 关于确切的联结语法,应该查看具体的文档,看相应的 DBMS 支持何种语法。
- 保证使用正确的联结条件。
- 应该总是提供联结条件,否则会得出笛卡儿积。
- 在一个联结中可以包含多个表,甚至可以对每个联结采用不同的联结类型。
- 联结及其使用的要点
第十四课 组合查询
- 组合查询
- SQL 也允许执行多个查询(多条SELECT语句),并将结果作为一个查询结果集返回。这些组合查询通常称为并(union)或复合查询(compound query)。
- 主要有两种情况需要使用组合查询:
- 在一个查询中从不同的表返回结构数据;
- 对一个表执行多个查询,按一个查询返回数据。
- 创建组合查询
-
可用 UNION 操作符来组合数条 SQL 查询。利用 UNION,可给出多条 SELECT 语句,将它们的结果组合成一个结果集。
-
使用 UNION
SELECT cust_name, cust_contact, cust_email FROM Customers WHERE cust_state IN ('IL', 'IN', 'MI') UNION SELECT cust_name, cust_contact, cust_email FROM Customers WHERE cust_name = 'Fun4All';
- 语句由前面的两条 SELECT 语句组成,之间用 UNION 关键字分隔。UNION 指示 DBMS 执行这两条 SELECT 语句,并把输出组合成一个查询结果集。
- 使用 UNION 组合 SELECT 语句的数目,SQL 没有标准限制。但是,最好是参考一下具体的 DBMS 文档。
-
UNION 规则
- 进行组合时需要注意几条规则。
- UNION 必须由两条或两条以上的 SELECT 语句组成,语句之间用关键字 UNION 分隔。
- UNION 中的每个查询必须包含相同的列、表达式或聚集函数。
- 列数据类型必须兼容:类型不必完全相同,但必须是 DBMS 可以隐含转换的类型。
-
包含或取消重复的行
- UNION 从查询结果集中自动去除了重复的行。
- 如果想返回所有的匹配行,可使用 UNION ALL 而不是 UNION。
-
对组合查询结果排序
- 在用 UNION 组合查询时,只能使用一条 ORDER BY 子句,它必须位于最后一条 SELECT 语句之后。
-
第十五课 插入数据
- 数据插入
-
INSERT用来将行插入(或添加)到数据库表。插入有几种方式:
- 插入完整的行;
- 插入行的一部分;
- 插入某些查询的结果。
-
插入完整的行
INSERT INTO Customers VALUES('1000000006', 'Toy Land');
- 编写 INSERT 语句的更安全(不过更烦琐)的方法如下:
INSERT INTO Customers(cust_id, cust_name) VALUES('1000000006', 'Toy Land');
- 不要使用没有明确给出列的 INSERT 语句。给出列能使SQL代码继续发挥作用,即使表结构发生了变化。
- 不管使用哪种 INSERT 语法,VALUES 的数目都必须正确。
-
插入部分行
- 如果表的定义允许,则可以在 INSERT 操作中省略某些列。
-
插入检索出的数据
- INSERT 还存在另一种形式,可以利用它将 SELECT 语句的结果插入表中,这就是所谓的 INSERT SELECT。
- INSERT SELECT 是个例外,它可以用一条 INSERT 插入多行,不管 SELECT 语句返回多少行,都将被 INSERT 插入。
-
- 从一个表复制到另一个表
- 要将一个表的内容复制到一个全新的表(运行中创建的表),可以使用 SELECT INTO 语句。
- INSERT SELECT 与 SELECT INTO 它们之间的一个重要差别是前者插入数据,而后者导出数据。
- 使用 SELECT INTO 时,需要知道一些事情:
- 任何 SELECT 选项和子句都可以使用,包括 WHERE 和 GROUP BY;
- 可利用联结从多个表插入数据;
- 不管从多少个表中检索数据,数据都只能插入到一个表中。
第十六课 更新和删除数据
-
更新数据
- 更新(修改)表中的数据,可以使用 UPDATE 语句。有两种使用 UPDATE 的方式:
- 更新表中的特定行;
- 更新表中的所有行。
- 在使用UPDATE时一定要细心。因为稍不注意,就会更新表中的所有行。
UPDATE Customers SET cust_email = 'kim@thetoystore.com' WHERE cust_id = '1000000005';
- UPDATE 语句中可以使用子查询,使得能用 SELECT 语句检索出的数据更新列数据。
- 要删除某个列的值,可设置它为 NULL(假如表定义允许 NULL 值)。
UPDATE Customers SET cust_email = NULL WHERE cust_id = '1000000005';
- 更新(修改)表中的数据,可以使用 UPDATE 语句。有两种使用 UPDATE 的方式:
-
删除数据
- 从一个表中删除(去掉)数据,使用 DELETE 语句。有两种使用 DELETE 的方式:
- 从表中删除特定的行;
- 从表中删除所有行。
- 不要省略 WHERE 子句,在使用 DELETE 时一定要细心。因为稍不注意,就会错误地删除表中所有行。
DELETE FROM Customers WHERE cust_id = '1000000006';
。
- DELETE 不需要列名或通配符。DELETE 删除整行而不是删除列。要删除指定的列,请使用 UPDATE 语句。
- DELETE 语句从表中删除行,甚至是删除表中所有行。但是,DELETE 不删除表本身。
- 更快的删除用 TRUNCATE TABLE 语句
- 从一个表中删除(去掉)数据,使用 DELETE 语句。有两种使用 DELETE 的方式:
-
更新和删除的指导原则
- 除非确实打算更新和删除每一行,否则绝对不要使用不带 WHERE 子句的 UPDATE 或 DELETE 语句。
- 保证每个表都有主键,尽可能像 WHERE 子句那样使用它(可以指定各主键、多个值或值的范围)。
- 在 UPDATE 或 DELETE 语句使用 WHERE 子句前,应该先用 SELECT 进行测试,保证它过滤的是正确的记录,以防编写的 WHERE 子句不正确。
- 使用强制实施引用完整性的数据库,这样 DBMS 将不允许删除其数据与其他表相关联的行。
- 有的 DBMS 允许数据库管理员施加约束,防止执行不带 WHERE 子句的 UPDATE 或 DELETE 语句。如果所采用的 DBMS 支持这个特性,应该使用它。
第十七课 创建和操纵表
- 创建表
-
一般有两种创建表的方法:
- 多数 DBMS 都具有交互式创建和管理数据库表的工具;
- 表也可以直接用 SQL 语句操纵。
-
表创建基础
- 利用 CREATE TABLE 创建表,必须给出下列信息:
- 新表的名字,在关键字 CREATE TABLE 之后给出;
- 表列的名字和定义,用逗号分隔;
- 有的 DBMS 还要求指定表的位置。
CREATE TABLE Products ( prod_id CHAR(10) NOT NULL, vend_id CHAR(10) NOT NULL, prod_name CHAR(254) NOT NULL, prod_price DECIMAL(8,2) NOT NULL, prod_desc VARCHAR(1000) NULL );
- 利用 CREATE TABLE 创建表,必须给出下列信息:
-
使用 NULL 值
- 每个表列要么是 NULL 列,要么是 NOT NULL 列,这种状态在创建时由表的定义规定。
CREATE TABLE Orders ( order_num INTEGER NOT NULL, order_date DATETIME NOT NULL, cust_id CHAR(10) NOT NULL );
- 创建混合了 NULL 和 NOT NULL 列的表
CREATE TABLE Vendors ( vend_id CHAR(10) NOT NULL, vend_name CHAR(50) NOT NULL, vend_address CHAR(50) , vend_city CHAR(50) , vend_state CHAR(5) , vend_zip CHAR(10) , vend_country CHAR(50) );
- NULL 为默认设置,如果不指定 NOT NULL,就认为指定的是 NULL。
- 不要把 NULL 值与空字符串相混淆。NULL 值是没有值,不是空字符串。
-
指定默认值
- SQL 允许指定默认值,在插入行时如果不给出值,DBMS 将自动采用默认值。在 CREATE TABLE 语句的列定义中用关键字 DEFAULT 指定。
CREATE TABLE OrderItems ( order_num INTEGER NOT NULL, order_item INTEGER NOT NULL, prod_id CHAR(10) NOT NULL, quantity INTEGER NOT NULL DEFAULT 1, item_price DECIMAL(8,2) NOT NULL );
- 默认值经常用于日期或时间戳列。通过指定引用系统日期的函数或变量,将系统日期用作默认日期。
- 使用 DEFAULT 而不是 NULL 值。
-
- 更新表
-
更新表定义,可以使用 ALTER TABLE 语句。
ALTER TABLE Vendors ADD vend_phone CHAR(20);
-
更改或删除列、增加约束或增加键,这些操作也使用类似的语法
-
使用 ALTER TABLE 要极为小心,应该在进行改动前做完整的备份(表结构和数据的备份)。
-
- 删除表
- 删除表(删除整个表而不是其内容)非常简单,使用 DROP TABLE 语句即可:
DROP TABLE CustCopy;
。
- 删除表(删除整个表而不是其内容)非常简单,使用 DROP TABLE 语句即可:
- 重命名表
- DB2、MariaDB、MySQL、Oracle 和 PostgreSQL 用户使用 RENAME 语句。
- 所有重命名操作的基本语法都要求指定旧表名和新表名。