数据库DBC(摘自《SQL必知必会 第四版》

1、通过过滤选择不包含指定值的所有行时，你可能希望返回含NULL值的行。但是这做不到。因为未知（unknown）有特殊的含义，数据库不知道它们是否匹配，所以在进行匹配过滤或非匹配过滤时，不会返回这些结果。过滤数据时一定要验证被过滤列中含有NULL的行确实出现在返回的数据中。

2、在指定一条ORDER BY子句时，应该保证它是SELECT语句中最后一条子句。如果它不是最后的子句，将会出现错误消息。

3、and or操作符存在优先级，and高于or，适当的时候需要加括号 ()。任何时候使用具有AND和OR操作符的WHERE子句，都应该使用圆括号明确地分组操作符。不要过分依赖默认求值顺序，即使它确实如你希望的那样。使用圆括号没有什么坏处，他能消除歧义。

#下面选择的意思是，选择vend_id=1005 and prod_price>=10的行以及vend_id=1003的所有行

select prod_name,prod_price
from products
where vend_id=1003 or vend_id=1005 and prod_price>=10;

 #下面选择的意思是，选择vend_id=1003 or vend_id=1005中prod_price>=10的行

select prod_name,prod_price
from products
where (vend_id=1003 or vend_id=1005) and prod_price>=10;

4、NOT操作符在简单的WHERE子句中，确实没有什么优势，但在更复杂的子句中，NOT是非常有用的，例如，在与IN操作符联合使用时，NOT可以非常简单地找出与条件列表不匹配的行。

5、通配符，

• %：表示任何字符出现任意次数。看起来通配符%可以匹配任何东西，但有个例外，这就是NULL。子句WHERE prod_name LIKE '%'不会匹配产品名称为NULL的行。
• _：表示只匹配单个字符，不能多也不能少，若想匹配两个字符，就用两个下划线__。
• []：用来指定一个字符集，它必须匹配指定位置（通配符的位置）的一个字符，只匹配出现在字符集中的单个字符。例如，[JM]该位置只匹配 J 或 M。

通配符搜索一般比前面讨论的其他搜索要耗费更长的处理时间。

• 所以不要过度使用通配符。如果其他操作能达到相同的目的，应该使用其他操作符。
• 在确实需要使用通配符时，也尽量不要把它们用在搜索模式的开始处。把通配符置于开始处，搜索起来是最慢的。
• 仔细注意通配符的位置。如果放错地方，可能不会返回想要的数据。

6、AVG()只能用来确定特定数值列的平均值，而且列名必须作为函数参数给出。为了获得多个列的平均值，必须使用多个AVG()函数。AVG()函数忽略列值为NULL的行。

7、COUNT()函数进行计数。可利用COUNT()确定表中行的数目或符合特定条件的行的数目。

• 使用COUNT(*)对表中行的数目进行计数，不管表列中包含的是空值（NULL）还是非空值。
• 使用COUNT(column)对特定列中具有值的行进行计数，忽略NULL值。

8、该select语句指定了两个列：vend_id包含产品供应商的ID，num_prods为计算字段（用COUNT(*)函数建立）。GROUP BY子句指示DBMS按vend_id排序并分组数据。这就会对每个vend_id而不是整个表计算num_prods一次。

因为使用了GROUP BY，就不必指定要计算和估值的每个组了。系统会自动完成。GUOUP BY子句指示DBMS分组数据，然后对每个组而不是整个结果进行聚集。

select vend_id, count(*) as num_prods
from products
group by vend_id;

在使用GROUP BY子句前，需要之道一些重要的规定：

• GROUP BY子句可以包含任意数目的列，因而可以对分组进行嵌套，更细致地进行数据分组。
• 如果在GROUP BY子句中嵌套了分组，数据将在最后指定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算（所以不能从个别的列取回数据）。
• GROUP BY子句中列出的每一列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在SELECT中使用表达式，则必须在GROUP BY子句中指定相同的表达式。不能使用别名。
• 大多数SQL实现不允许GROUP BY列带有长度可变的数据类型（如文本或备注型字段）。除聚集计算语句外，SELECT语句中的每一列都必须在GROUP BY子句中给出。
• 如果分组列中包含具有NULL值的行，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组。
• GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。

9、除了能用GROUP BY分组数据外，SQL还允许过滤分组，规定包括哪些分组，排除哪些分组。为此，必须基于完整的分组而不是个别的行进行过滤。

WHERE具有过滤的功能，但是它过滤的是行，而不是分组。事实上，WHERE没有分组的概念。SQL为此提供了另一个子句，就是HAVING子句。

select vend_id, count(*) as num_prods
from products
where prod_price >= 4
group by vend_id
having count(*) >= 2

WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤。这是一个重要的区别，WHERE排除的行不包括在分组中。这可能会改变计算值，从而影响HAVING子句中基于这些值过滤掉的分组。

10、select子句的顺序

子句	说明	是否必须使用
SELECT	要返回的列或表达式	是
FROM	从中检索数据的表	仅在从表中选择数据时使用
WHERE	行级过滤	否
GROUP BY	分组说明	仅在按组计算聚集时使用
HAVING	组级过滤，对行分组，但输出可能不是分组的顺序	否
ORDER BY	输出排序顺序，对产生的输出排序	否

11、在WHERE子句中使用子查询能够编写功能很强且灵活的SQL语句。作为子查询的SELECT语句只能查询单个列，企图检索多个列将返回错误。子查询常用于WHERE子句的IN操作符中，以及用来填充计算列。

12、

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，可以把多条查询的结果作为一条组合查询返回，不管结果中有无重复。使用union可极大地简化复杂的where子句，简化从多个表中检索数据的工作。

13、insert一般用来给表插入具有指定列值的行。insert还存在另一种形式，可以利用它将select语句的结果插入表中，这就是所谓的insert select。顾名思义，它是由一条insert语句和一条select语句组成的。

insert通常只插入一行。要插入多行，必须执行多个insert语句。insert select是个例外，它可以用一条insert插入多行，不管select语句返回多少行，都将被insert插入。

14、复制一个表 create table custcopy select * from customers;

• 任何select选项和子句都可以使用，包括where和group by；
• 可以利用联结从多个表插入数据；
• 不管从多少个表中检索数据，数据都只能插入到一个表中。

15、更新（修改）表中的数据，可以使用update语句。有两种使用update的方式：

• 更新表中的特定行；
• 更新表中的所有行。

update customers
set cus_email = 'kim@thetoystore.com'
where cus_id = '1000000005'

update customers
set cus_email = 'kim@thetoystore.com',
    cus_contact = 'Sam Roberts'
where cus_id = '1000000005'

update语句以where子句结束，它告诉DBMS更新哪一行。没有where子句，DBMS将会更新表中的所有行，这不是我们希望的。

从一个表中删除数据，使用delete语句。有两种使用delete的方式：

• 从表中删除特定的行；
• 从表中删除所有行。

delete from customers
where cus_id = '100000006'

delete不需要列名或通配符。delete删除整行而不是删除列。要删除指定的列，请使用update语句。delete语句从表中删除行，甚至是删除表中所有行。但是，delete不删除表本身。

如果想从表中删除所有行，不要使用delete。可使用truncate table语句，它完成相同的工作，而速度更快（因为不记录数据的变动）。

16、下面是使用update或delete时所遵循的重要原则：

• 除非确实打算更新和删除每一行，否则绝对不要使用不带where子句的update或delete语句。
• 保证每个表都有主键，尽可能像where子句那样使用它（可以指定各主键、多个值或值的范围）。
• 在update或delete语句使用where子句前，应该先用select进行测试，保证它过滤的是正确的记录，以防编写的where子句不正确。
• 使用强制实施引用完整型的数据库，这样DBMS将不允许删除其数据与其他表相关联的行。
• 有的DBMS允许数据库管理员施加约束，防止执行不带where子句的update或delete语句。如果所有的DBMS支持这个特性，应该使用它

17、视图是虚拟的表。与包含数据的表不一样，试图只包含使用时动态检索数据的查询。视图用create view语句来创建。与create table一样，create view只能用于创建不存在的视图。删除视图，可以使用drop语句，其语法为drop view viewname;

select cust_name, cust_contact
from customers, orders, orderitems
where customers.cust_id = orders.cust_id
    and orderitems.order_num = orders.order_num
    and prod_id = 'FB';

此查询用来检索订购了某种产品的顾客，任何需要这个数据的人都必须理解相关表的结构，知道如何创建查询和对表进行联结。检索其他产品（或多个产品）的相同数据，必须修改最后的where子句。

假如可以把整个查询包装成一个名为productcustomers的虚拟表，则可以如下轻松地检索出相同的数据：

select cust_name, cust_contact
from productcustomers
where pro_id = 'RGAN01';

这就是视图的作用，productcustomers是一个视图，作为视图，它不包含任何列或数据，包含的是一个查询。

视图的常见应用：

• 重用sql语句。
• 简化复杂的sql操作。在编写查询后，可以方便地重用它而不必知道其基本查询细节。
• 使用表的一部分而不是整个表。
• 保护数据。可以授权用户访问表的特定部分的权限，而不是整个表的访问权限。
• 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。

创建视图之后，可以用与表基本相同的方式使用它们，可以对视图执行select操作，过滤和排序数据，将视图联结到其他视图或表，甚至添加和更新数据。重要的是，要知道视图仅仅是用来查看存储在别处数据的一种设施。视图本身不包含数据，因此返回的数据是从其他表中检索出来的。在添加或更改这些表中的数据时，视图将返回改变过的数据。

视图创建和使用的一些最常见的规则和限制：

• 与表一样，视图必须唯一命名（不能给视图取与别的视图或表相同的名字）。
• 对于可以创建的视图数目没有限制。
• 创建视图，必须具有足够的访问权限。这些权限通常由数据库管理人员授予。
• 视图可以嵌套，即可以利用从其他视图中检索数据的查询来构造视图。所允许的嵌套层数在不同的DBMS中有所不同（嵌套视图可能会严重降低查询的性能，因此在产品环境中使用之前，应该对其进行全面测试）。
• 许多DBMS禁止在视图查询中使用ORDER BY子句。
• 有些DBMS要求对返回的所有列进行命名，如果列是计算字段，则需要使用别名。
• 视图不能索引，也不能有关联的触发器或默认值。
• 有些DBMS把视图作为只读的查询，这表示可以从视图检索数据，但不能将数据写回底层表。详情请参阅具体的DBMS文档。
• 有些DBMS允许创建这样的视图，它不能进行导致行不再属于视图的插入或更新。例如有一个视图，只检索带有电子邮件地址的顾客。如果更新某个顾客，删除他的电子邮件地址，将使该顾客不再属于视图。这是默认行为，而且是允许的，但有的DBMS可能会防止这种情况发生。

create view ProductCustomers as
select cust_name, cust_contact, prod_id
from Customers, Orders, OrderItems
where Customers.cust_id = Orders.cust_id
	and OrderItems.order_num = Orders.order_num;

select * from ProductCustomers;

select cust_name, cust_contact
from ProductCustomers
where prod_id = 'anv01';

可以看出，视图极大地简化了复杂sql语句的使用。利用视图，可一次性编写基础的sql，然后根据需要多次使用。视图为虚拟的表，它们包含的不是数据而是根据需要检索数据的查询。视图提供了一种封装select语句的层次，可用来简化数据处理，重新格式化或保护基础数据。

18、为什么要使用存储过程？

• 通过把处理封装在一个易用的单元中，可以简化复杂的操作。
• 由于不要求反复建立一系列处理步骤，因而保证了数据的一致性。如果所有开发人员和应用程序都使用同一存储过程，则所使用的代码都是相同的。这一点的延伸就是防止错误。需要执行的步骤越多，出错的可能性就越大。防止错误保证了数据的一致性。
• 简化对变动的管理。如果表名、列名或业务逻辑（或别的内容）有变化，那么只需要更改存储过程的代码。使用它的人员甚至不需要知道这些变化。这一点的延伸就是安全性。通过存储过程限制对基础数据的访问，减少了数据讹误（无意识的或别的原因所导致的数据讹误）的机会。
• 因为存储过程通常以编译过的形式存储，所以DBMS处理命令的工作较少，提高了性能。
• 存在一些只能用在单个请求中的SQL元素和特性，存储过程可以使用它们来编写功能更强更灵活的代码。

换句话说，使用存储过程有三个主要的好处，即简单、安全、高性能。显然，它们都很重要。不过，在将SQL代码转换为存储过程前，也必须知道它的一些缺陷。

• 不同DBMS中的存储过程语法有所不同。事实上，编写真正的可移植存储过程几乎是不可能的。不过，存储过程的自我调用（名字以及数据如何传递）可以相对保持可移植。因此，如果需要移植到别的DBMS，至少客户端应用代码不需要变动。
• 一般来说，编写存储过程比编写基本SQL语句复杂，需要更高的技能，更丰富的经验。因此，许多数据库管理员把限制存储过程的创建作为安全措施（主要受上一条缺陷的影响）。

19、使用事物处理（transaction processing），通过确保成批的sql操作要么完全执行，要么完全不执行，来维护数据库的完整性。

事物处理是一种机制，用来管理必须成批执行的SQL操作，保证数据库不包含不完整的操作结果。利用事务处理，可以保证一组操作不会中途停止，它们要么完全执行，要么完全不执行（除非明确指示）。如果没有错误发生，整组语句提交给（写到）数据库表；如果发生错误，则进行回退（撤销），将数据库恢复到某个已知且安全的状态。

常用的几个术语：

• 事务（transaction）指一组SQL语句；
• 回退（rollback）指撤销指定SQL语句的过程；
• 提交（commit）指将未存储的SQL语句结果写入数据库表；
• 保留点（savepoint）指事务处理中设置的临时占位符（placeholder），可以对它发布回退（与回退整个事务处理不同）。

事务处理用来管理INSERT、UPDA TE和DELETE语句。不能回退SELECT语句（回退SELECT语句也没有必要），也不能回退CREA TE或DROP操作。事务处理中可以使用这些语句，但进行回退时，这些操作也不撤销。

20、常见的约束：主键、外键、唯一约束、检查约束

主键是一种特殊的约束，用来保证一列（或一组列）中的值是唯一的，而且永不改动。换句话说，表中的一列（或多个列）的值唯一标识表中的每一行。这方便了直接或交互地处理表中的行。没有主键，要安全地UPDA TE或DELETE特定行而不影响其他行会非常困难。

表中任意列只要满足以下条件，都可以用于主键：

• 任意两行的主键值都不相同。
• 每行都具有一个主键值（即列中不允许NULL值）。
• 包含主键值的列从不修改或更新。（大多数DBMS不允许这么做，但如果你使用的DBMS允许这样做，好吧，千万别！）
• 主键值不能重用。如果从表中删除某一行，其主键值不分配给新行。

CREATE TABLE Vendors(
    vend_id CHAR(10) NOT NULL PRIMARY KEY,
    vend_name CHAR(50) NOT NULL,
    vend_address CHAR(50) NULL,
    vend_city CHAR(50) NULL,
    vend_state CHAR(5) NULL,
    vend_zip CHAR(10) NULL,
    vend_country CHAR(50) NULL
);

ALTER TABLE Vendors
ADD CONSTRAINT PRIMARY KEY (vend_id);

外键是表中的一列，其值必须列在另一表的主键中。外键是保证引用完整性的极其重要部分。我们举个例子来理解外键。

CREATE TABLE Orders(
    order_num INTEGER NOT NULL PRIMARY KEY,
    order_date DATETIME NOT NULL,
    cust_id CHAR(10) NOT NULL REFERENCES Customers(cust_id)
);

其中的表定义使用了REFERENCES关键字，它表示cust_id中的任何值都必须是Customers表的cust_id中的值。相同的工作也可以在A LTER TA BLE语句中用CONSTRA INT语法来完成：

ALTER TABLE Orders
ADD CONSTRAINT
FOREIGN KEY (cust_id) REFERENCES Customers (cust_id)

 Orders表将录入到系统的每个订单作为一行包含其中。顾客信息存储在Customers表中。Orders表中的订单通过顾客ID与Customers表中的特定行相关联。顾客ID为Customers表的主键，每个顾客都有唯一的ID。订单号为Orders表的主键，每个订单都有唯一的订单号。

Orders表中顾客ID列的值不一定是唯一的。如果某个顾客有多个订单，则有多个行具有相同的顾客ID（虽然每个订单都有不同的订单号）。同时，Orders表中顾客ID列的合法值为Customers表中顾客的ID。

这就是外键的作用。在这个例子中，在Orders的顾客ID列上定义了一个外键，因此该列只能接受Customers表的主键值。

在定义外键后，DBMS不允许删除在另一个表中具有关联行的行。例如，不能删除关联订单的顾客。删除该顾客的唯一方法是首先删除相关的订单（这表示还要删除相关的订单项）。由于需要一系列的删除，因而利用外键可以防止意外删除数据。

唯一约束用来保证一列（或一组列）中的数据是唯一的。它们类似于主键，但存在以下重要区别。

• 表可包含多个唯一约束，但每个表只允许一个主键。
• 唯一约束列可包含NULL值。
• 唯一约束列可修改或更新。
• 唯一约束列的值可重复使用。
• 与主键不一样，唯一约束不能用来定义外键。

employees表是一个使用约束的例子。每个雇员都有唯一的社会安全号，但我们并不想用它作主键，因为它太长（而且我们也不想使该信息容易利用）。因此，每个雇员除了其社会安全号外还有唯一的雇员ID（主键）。雇员ID是主键，可以确定它是唯一的。你可能还想使DBMS保证每个社会安全号也是唯一的（保证输入错误不会导致使用他人号码）。可以通过在社会安全号列上定义UNIQUE约束做到。

唯一约束的语法类似于其他约束的语法。唯一约束既可以用UNIQUE关键字在表定义中定义，也可以用单独的CONSTRA INT定义。

检查约束用来保证一列（或一组列）中的数据满足一组指定的条件。检查约束的常见用途有以下几点。

• 检查最小或最大值。例如，防止0个物品的订单（即使0是合法的数）。
• 指定范围。例如，保证发货日期大于等于今天的日期，但不超过今天起一年后的日期。
• 只允许特定的值。例如，在性别字段中只允许M或F。

CREATE TABLE OrderItems(
    order_num INTEGER NOT NULL,
    order_item INTEGER NOT NULL,
    prod_id CHAR(10) NOT NULL,
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    item_price MONEY NOT NULL
);

利用这个约束，任何插入（或更新）的行都会被检查，保证quantity大于0。检查名为gender的列只包含M或F，可编写如下的A LTER TA BLE语句：

ADD CONSTRAINT CHECK (gender LIKE '[MF]')

21、主键数据总是排序的，这是DBMS的工作。因此，按主键检索特定行总是一种快速有效的操作。但是，搜索其他列中的值通常效率不高。例如，如果想搜索住在某个州的客户，怎么办？因为表数据并未按州排序，DBMS必须读出表中所有行（从第一行开始），看其是否匹配。这就像要从没有索引的书中找出词汇一样。

解决方法是使用索引。可以在一个或多个列上定义索引，使DBMS保存其内容的一个排过序的列表。在定义了索引后，DBMS以使用书的索引类似的方法使用它。DBMS搜索排过序的索引，找出匹配的位置，然后检索这些行。

在开始创建索引前，应该记住以下内容：

• 索引改善检索操作的性能，但降低了数据插入、修改和删除的性能。在执行这些操作时，DBMS必须动态地更新索引。
• 索引数据可能要占用大量的存储空间。
• 并非所有数据都适合做索引。取值不多的数据（如州）不如具有更多可能值的数据（如姓或名），能通过索引得到那么多的好处。
• 索引用于数据过滤和数据排序。如果你经常以某种特定的顺序排序数据，则该数据可能适合做索引。
• 可以在索引中定义多个列（例如，州加上城市）。这样的索引仅在以州加城市的顺序排序时有用。如果想按城市排序，则这种索引没有用处。

没有严格的规则要求什么应该索引，何时索引。大多数DBMS提供了可用来确定索引效率的实用程序，应该经常使用这些实用程序。

索引用CREA TE INDEX语句创建（不同DBMS创建索引的语句变化很大）。下面的语句在Products表的产品名列上创建一个简单的索引：

CREATE INDEX prod_name_ind
ON PRODUCTS (prod_name);