SQL 基础知识点汇总

(1)"NULL"值匹配问题

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

总结：null值不参与匹配！

(2)SQL（像多数语言一样）在处理OR操作符前，优先处理AND操作符，圆括号具有比AND或OR操作符更高的优先级。

(3)"IN"操作符

IN操作符一般比一组OR操作符执行得更快。

最大优点:可以包含其他SELECT语句，能够更动态地建立WHERE子句。

(4)"NOT"操作符

例：SELECT prod_name FROM Products WHERE NOT vend_id = 'DLL01'

这里的NOT否定跟在其后的条件，因此，DBMS不是匹配vend_id为DLL01，而是匹配非DLL01之外的所有东西，效果等同于"<>"（仍然不会返回值为NULL的数据）

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

(5)"LIKE"操作符

通配符"%"：代表搜索模式中给定位置的0个、1个或多个字符。
当"%"位于搜索模式的中间时(如：LIKE 'F%y')要注意：如果某列有50个字符，而存储的文本为Fish bean bag toy(17个字符)，则为填满该列需要在文本后附加33个空格，这个时候要在搜索模式再增加一个%号：'F%y%'来匹配y之后的字符(或空格)。

通配符"%"看起来像是可以匹配任何东西，但有个例外，这就是NULL。子句WHERE prod_name LIKE '%'不会匹配产品名称为NULL的行。

通配符"_"的用途与%一样，但它只匹配单个字符，不能多也不能少。通配符相对于其他搜索比较耗时，而且把通配符置于开始处，搜索起来是最慢的。

(6)DBMS函数的差异

因此使用函数降低了代码的可移植性

(7)聚合函数

AVG()函数忽略列值为NULL的行。

COUNT()函数有两种使用方式：

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

MIN(),MAX(),SUM()均忽略列值为NULL的行。

(8)GROUP BY分组

GROUP BY子句中列出的每一列都必须是检索列或有效的表达式（但不能是聚集函数）。

有效表达式如：select to_char(g.create_datetime,'yyyy'),count(*) from sys_config g group by to_char(g.create_datetime,'yyyy');

如果在SELECT中使用表达式，则必须在GROUP BY子句中指定相同的表达式。不能使用别名。

不能使用别名如：select to_char(g.create_datetime,'yyyy') as yearForConfig,count(*) from sys_config g group by yearForConfig;

大多数SQL???实现不允许GROUP BY列带有长度可变的数据类型（如文本或备注型字段）。

如果分组列中包含具有NULL值的行，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组

Microsoft SQL Server等有些SQL实现在GROUP BY中支持可选的ALL子句???。这个子句可用来返回所有分组，即使是没有匹配行的分组也返回（在此情况下，聚集将返回NULL）。

过滤分组HAVING

SELECT vend_id,COUNT(*) AS num_prods FROM Products WHERE prod_price >= 4 GROUP BY vend_id HAVING COUNT(*) >= 2;

HAVING和WHERE的差别:

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

（9）组合查询

union 与 union all

使用UNION时，重复的行会被自动取消。

用UNION组合查询时，只能使用一条ORDER BY子句，它必须位于最后一条SELECT语句之后。对于结果集，不存在用一种方式排序一部分，而又用另一种方式排序另一部分的情况，因此不允许使用多条ORDER BY子句。

其他类型的union

EXCEPT（有时称为MINUS）可用来检索只在第一个表中存在而在第二个表中不存在的行；
INTERSECT可用来检索两个表中都存在的行。
实际上，这些UNION很少使用，因为相同的结果可利用联结得到。

（10）INSERT SELECT：插入检索出的数据

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

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 CustNew;

为简单起见，这个例子在INSERT和SELECT语句中使用了相同的列名。但是，不一定要求列名匹配。事实上，DBMS一点儿也不关心SELECT返回的列名。它使用的是列的位置，因此SELECT中的第一列（不管其列名）将用来填充表列中指定的第一列，第二列将用来填充表列中指定的第二列，如此等等。

(11)SELECT INTO

DB2不支持这里描述的SELECT INTO。

与INSERT SELECT将数据添加到一个已经存在的表不同，SELECT INTO将数据复制到一个新表（有的DBMS可以覆盖已经存在的表，这依赖于所使用的具体DBMS）。

INSERT SELECT与SELECT INTO之间的一个重要差别是前者导出数据，而后者导入数据。

使用SELECT INTO时，需要知道一些事情：

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

(12)删除数据

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

(13)创建表

指定默认值

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
);

（14）视图

使用视图的好处

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

创建视图

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;

视图极大地简化了复杂SQL语句的使用。

注：覆盖（或更新）视图，必须先删除它，然后再重新创建。

（15）存储过程（较为复杂，多去理解和使用）