SQL doc

//先列出条目，然后整理。结合面试题给出的知识点。

//考试前的复习才能出成绩...

1.使用SUBQUERY:

除了使用=, !=(<>)外，还是使用exist, any(some), all, in等

exist是在一个返回项目存在的前提下为真；

any(some)是将待测试项与subquery中的项目测试，与其中一个项目测试的结果为真；

all是将待测试项与subquery中的项目测试，与其中所有项目测试的结果为真；

in是待测试项存在于subquery中的项目列表为真。

子查询原理上并不负载，但是要相当熟练的使用子查询还需要多加思考，下面是一道练习题：

CREATE TABLE passengers (
   name VARCHAR(15), 
   compartment INT);
 
INSERT INTO passengers VALUES ('smith',20);
INSERT INTO passengers VALUES ('jones',25);
 
CREATE TABLE cars (
   compartment INT,
   class VARCHAR(10));
 
INSERT INTO cars VALUES (20,'first');

编写SQL语句查找每一个乘客都乘坐的汽车。

SELECT * FROM cars c1 WHERE NOT EXISTS 

(SELECT * FROM passengers p1 WHERE NOT EXISTS 
(SELECT * FROM cars c2
WHERE c2.compartment = p1.compartment
AND c2.compartment = c1.compartment));

一开始很难明白这个语句的作用，三重嵌套，并且名字是跨嵌套的。

现在我们可以用逻辑语句分析:

我们选择的是：

一辆车，它不存在着：

一个乘客，它不存在着：

乘客坐的车就是那一辆车。

再解释通顺一下：

一辆车，它不存在着：

一个乘客坐的车不是那一辆车。

如果按照以下逻辑：

select * from cars c1 where exists (select * from passengers p where exists(select * from cars c2 where p.compartment = c2.compartment and c2.compartment = c1.compartment));;

我们选择的是：

一辆车，它存在着：

一个乘客，它坐的车就是那一辆车。

这样只要有一个乘客座这辆车，就为真。所以是错误的。

NOT EXIST的使用就是以排除的形式来确定任何。

2.多表联查：

多表联查事实上相当于join table，虽然在细节上有所差别。但是结果都是：

生成表1和表2的全可能的虚拟表（表1的每一项都和表2的每一项两两组成一个条目）；

然后由select语句选择符合条件的条目返回。

所以join table在性能上可能存在问题。

3.使用GROUP BY：

select count(title_text) from title group by stock;

select title_text from title group by stock;

第二句没有使用aggregate functions（统计函数）出错的原因是

“you have only one row per group when you use GROUP BY.“

记住这个原理就能理解group by的限制。

select count(stock) from title group by stock;

count作用于group by的字段，和作用于其它字段是一样的。它统计字段出现的频率。

group by可以使用多个字段作为聚合。

我们注意到统计函数（或者group 函数）的作用域就是每一个group。

表如下：

+------+-------+-------+---------+-----------------+
| clno | fname | lname | job | account_balance |
+------+-------+-------+---------+-----------------+
| 10 | sam | smith | auditor |5525.75 | 
| 20 | james | jones | manager | 8960.25 | 
+------+-------+-------+---------+-----------------+

使用：

select * from clients group by account_balance having account_balance = max(account_balance);

结果：

+------+-------+-------+---------+-----------------+
| clno | fname | lname | job | account_balance |
+------+-------+-------+---------+-----------------+
| 10 | sam | smith | auditor | 5525.75 | 
| 20 | james | jones | manager | 8960.25 | 
+------+-------+-------+---------+-----------------+

使用：

select max(account_balance) from clients group by account_balance;

结果：

+----------------------+
| max(account_balance) |
+----------------------+
| 5525.75 | 
| 8960.25 | 
+----------------------+

考虑到上面所说的group by的原理，应该是统计函数发生在每一group后作为其中的一列，组成代表每一group的唯一一行被提交给了数据库的数据缓存中。

另外，统计函数不能用于where语句中。

4.使用函数：

5.复制表（只复制结构）：

select into B from A where 1=0;

  拷贝表（包括数据）：

select into B from A;

6.创建临时表：

7.当你需要关于某字段的统计信息的时候，无疑是使用GROUP BY和统计函数来实现查询，并且利用having来限定条件。

8.创建索引（index)

9.创建快照（view)

10.POSTGRESQL中使用serial类型自动增加ID

11.sql-case:

很难确定它的主题，但是它非常有用。

http://www.blogjava.net/hhhaaawwwkkk/archive/2008/11/13/240328.html

注意：case expression when NULL...是不能工作的，因为用的是expression=NULL,而应该使用第一种形式：case when expression is NULL。（见14）

所列举的三种使用方式：

1.用于统计函数的删选数据。

2.用于select确定要选择的列。

3.用于改变值的表达方式。

12.join tables：

http://www.cnblogs.com/ajaxworld/archive/2007/07/06/808854.html

重要的就是：

inner join 创建内部链接，创建的表只包含满足on测试的条件。

left join 和 right join创建的是outer join

A left　join B

A是左表，B是右表，以A为基础，即全部显示A后，B中的记录会填充到相应的条目上。

A right join B

A仍然是左表，刚好相反，以B为基础，全部显示B后，A再填充。

可以在 JOIN 语句中链接多个 ON 子句，请使用如下语法：

SELECT fields
FROM table1 INNER JOIN table2
ON table1.field1 compopr table2.field1 AND
table1.field2 compopr table2.field2 OR
table1.field3 compopr table2.field3;

也可以通过如下语法嵌套 JOIN 语句：

(为了强调这一个观点：A join B on ...产生的结果是一个表。我们使用括号。）

SELECT fields from 

(table1 inner join 

(table2 inner join table3 on ...)

on ...);

13.使用 select top num ...或者select ... limit num;

14.NULL这个值表示UNKNOWN(未知):它不表示“”(空字符串)。假设您的SQL Server数据库里有ANSI_NULLS，当然在默认情况下会有，对NULL这个值的任何比较都会生产一个NULL值。您不能把任何值与一个 UNKNOWN值进行比较，并在逻辑上希望获得一个答案。您必须使用IS NULL操作符。

15.练习题：