SQL 除了可以对列名和计算字段使用别名,还允许给表名起别名。这样做有两个主要理由:
缩短 SQL 语句;
允许在一条 SELECT 语句中多次使用相同的表。
用自联结而不用子查询
自联结通常作为外部语句,用来替代从相同表中检索数据的使用子查询语句。虽然最终的结果是相同的,但许多 DBMS 处理联结远比处理子查询快得多。应该试一下两种方法,以确定哪一种的性能更好。
事实上,我们迄今为止建立的每个内联结都是自然联结,很可能永远都不会用到不是自然联结的内联结。
使用联结和联结条件
在总结讨论联结的这两课前,有必要汇总一下联结及其使用的要点。
注意所使用的联结类型。一般我们使用内联结,但使用外联结也有效。
关于确切的联结语法,应该查看具体的文档,看相应的 DBMS 支持何种语法(大多数 DBMS 使用这两课中描述的某种语法)。
保证使用正确的联结条件(不管采用哪种语法),否则会返回不正确的数据。
应该总是提供联结条件,否则会得出笛卡儿积。
在一个联结中可以包含多个表,甚至可以对每个联结采用不同的联结类型。虽然这样做是合法的,一般也很有用,但应该在一起测试它们前分别测试每个联结。这会使故障排除更为简单。
主要有两种情况需要使用组合查询:
在一个查询中从不同的表返回结构数据;
对一个表执行多个查询按一个查询返回数据
UNION规则
可以看到,UNION 非常容易使用,但在进行组合时需要注意几条规则。
UNION 必须由两条或两条以上的 SELECT 语句组成,语句之间用关键字UNION分隔(因此,如果组合四条SELECT语句,将要使用三个UNION关键字)
UNION 中的每个查询必须包含相同的列、表达式或聚集函数(不过,各个列不需要以相同的次序列出)。
列数据类型必须兼容:类型不必完全相同,但必须是 DBMS 可以隐含转换的类型(例如,不同的数值类型或不同的日期类型)。
UNION 从查询结果集中自动去除了重复的行;这是 UNION 的默认行为,如果愿意也可以改变它。事实上,如果想返回所有的匹配行,可使用 UNION ALL 而不是 UNION。
UNION 与 WHERE
这一课一开始我们说过,UNION 几乎总是完成与多个 WHERE 条件相同的工作。UNION ALL 为 UNION 的一种形式,它完成 WHERE 子句完成
不了的工作。如果确实需要每个条件的匹配行全部出现(包括重复行),就必须使用 UNION ALL,而不是 WHERE。
插入及系统安全
使用 INSERT 语句可能需要客户端/服务器 DBMS 中的特定安全权限。在你试图使用 INSERT 前,应该保证自己有足够的安全权限。
总是使用列的列表
不要使用没有明确给出列的 INSERT 语句。给出列能使 SQL 代码继续发挥作用,即使表结构发生了变化。
省略列
如果表的定义允许,则可以在 INSERT 操作中省略某些列。省略的列必须满足以下某个条件。
该列定义为允许 NULL 值(无值或空值)。
在表定义中给出默认值。这表示如果不给出值,将使用默认值
省略所需的值
如果表中不允许有 NULL 值或者默认值,这时却省略了表中的值,DBMS 就会产生错误消息,相应的行不能成功插入。
插入多行
INSERT 通常只插入一行。要插入多行,必须执行多个 INSERT 语句。INSERT SELECT是个例外,它可以用一条INSERT插入多行,不管SELECT语句返回多少行,都将被 INSERT 插入。
SELECT *
INTO CustCopy
FROM Customers;
在使用 SELECT INTO 时,需要知道一些事情:
任何 SELECT 选项和子句都可以使用,包括 WHERE 和 GROUP BY;
可利用联结从多个表插入数据
不管从多少个表中检索数据,数据都只能插入到一个表中。
进行表的复制
SELECT INTO 是试验新 SQL 语句前进行表复制的很好工具。先进行复制,可在复制的数据上测试 SQL 代码,而不会影响实际的数据。
下面是许多 SQL 程序员使用 UPDATE 或 DELETE 时所遵循的重要原则。
除非确实打算更新和删除每一行,否则绝对不要使用不带 WHERE 子句的 UPDATE 或 DELETE 语句。
保证每个表都有主键(如果忘记这个内容,请参阅第 12 课),尽可能像 WHERE 子句那样使用它(可以指定各主键、多个值或值的范围)
在 UPDATE 或 DELETE 语句使用 WHERE 子句前,应该先用 SELECT 进行测试,保证它过滤的是正确的记录,以防编写的 WHERE 子句不正确。
使用强制实施引用完整性的数据库(关于这个内容,请参阅第 12 课),这样 DBMS 将不允许删除其数据与其他表相关联的行。
有的 DBMS 允许数据库管理员施加约束,防止执行不带 WHERE 子句的 UPDATE 或 DELETE 语句。如果所采用的 DBMS 支持这个特性,应该使用它。
利用 CREATE TABLE 创建表,必须给出下列信息:
新表的名字,在关键字 CREATE TABLE 之后给出;
表列的名字和定义,用逗号分隔;
有的 DBMS 还要求指定表的位置。
SQL 允许指定默认值,在插入行时如果不给出值,DBMS 将自动采用默认值。默认值在 CREATE TABLE 语句的列定义中用关键字 DEFAULT 指定。
复杂的表结构更改一般需要手动删除过程,它涉及以下步骤:
(1) 用新的列布局创建一个新表;
(2) 使用 INSERT SELECT 语句,从旧表复制数据到新表。有必要的话,可以使用转换函数和计算字段;
(3) 检验包含所需数据的新表;
(4) 重命名旧表(如果确定,可以删除它);
(5) 用旧表原来的名字重命名新表;
(6) 根据需要,重新创建触发器、存储过程、索引和外键。
下面是视图的一些常见应用。
重用 SQL 语句。
简化复杂的 SQL 操作。在编写查询后,可以方便地重用它而不必知道其基本查询细节。
使用表的一部分而不是整个表。
保护数据。可以授予用户访问表的特定部分的权限,而不是整个表的访问权限。
更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。
性能问题
因为视图不包含数据,所以每次使用视图时,都必须处理查询执行时需要的所有检索。如果你用多个联结和过滤创建了复杂的视图或者嵌
套了视图,性能可能会下降得很厉害。因此,在部署使用了大量视图的应用前,应该进行测试。
下面是关于视图创建和使用的一些最常见的规则和限制。
与表一样,视图必须唯一命名(不能给视图取与别的视图或表相同的名字)。
对于可以创建的视图数目没有限制。
创建视图,必须具有足够的访问权限。这些权限通常由数据库管理人员授予。
视图可以嵌套,即可以利用从其他视图中检索数据的查询来构造视图。所允许的嵌套层数在不同的 DBMS 中有所不同(嵌套视图可能会严重降低查询的性能,因此在产品环境中使用之前,应该对其进行全面测试)。
许多 DBMS 禁止在视图查询中使用 ORDER BY 子句。
有些 DBMS 要求对返回的所有列进行命名,如果列是计算字段,则需要使用别名(关于列别名的更多信息,请参阅第 7 课)。
视图不能索引,也不能有关联的触发器或默认值。
有些 DBMS 把视图作为只读的查询,这表示可以从视图检索数据,但不能将数据写回底层表。详情请参阅具体的 DBMS 文档。
有些 DBMS 允许创建这样的视图,它不能进行导致行不再属于视图的插入或更新。例如有一个视图,只检索带有电子邮件地址的顾客。如果更新某个顾客,删除他的电子邮件地址,将使该顾客不再属于视图。这是默认行为,而且是允许的,但有的 DBMS 可能会防止这种情况发生。
视图极大地简化了复杂 SQL 语句的使用。利用视图,可一次性编写基础的 SQL,然后根据需要多次使用