第3章 创建数据库 重点内容： 创建数据库和表 列约束 创建索引 修改数据库和表 删除数据库和表.

Presentation on theme: "第3章 创建数据库 重点内容： 创建数据库和表 列约束 创建索引 修改数据库和表 删除数据库和表."— Presentation transcript:

1 第3章 创建数据库 重点内容： 创建数据库和表 列约束 创建索引 修改数据库和表 删除数据库和表

2 一、子查询定义 子查询(subquery)就是嵌套在另一个查询语句中的SELECT语句，那个外部的查询语句称作为该子查询的外围查询，而这个子查询则称为被嵌套查询。子查询的结果将作为外围查询的参数，这种关系就好像我们的函数调用的嵌套，将一个函数调用作为另一个函数的输入参数。 子查询的外围查询可以是多种SQL语句：子查询可以嵌套在另一个SELECT语句的FROM子句、WHERE子句或者是HAVING子句中，它还可以嵌套在INSERT语句、UPDATE语句和DELETE语句中，甚至子查询的外围查询也可以是一个子查询，即嵌套子查询。 子查询具有两种不同处理方式的类型：子查询的两种不同处理方式的类型是：相关子查询(correlated subquery)和无关子查询(noncorrelated subquery)。无关子查询语句的运行和它的外围查询关系密切，而无关子查询运行是不需要和外围查询发生联系。 子查询有多种用途：随着子查询返回的数据类型的不同，它的用途也随着发生变化。

3 二、子查询和连接 在实际的SQL语句的编写中，需要在多个表的基础上运行一个查询语句的情况也是很常见的。在这两种情况下，可以在查询语句编写中使用表的自连接和连接，也可以使用子查询。一般来说，这两种结构的查询语句的结果是相同的，性能也差不多，选用哪种方式可以依据自己的习惯。 当需要使用聚集函数，并将它的值和其它值进行比较时，要使用子查询；当查询的结果要显示多个表的内容时，要使用连接。在其它的子查询和连接可以相互替换的情况下，由于性能相近，使用何种方式就依赖个人的风格习惯了。

4 三、子查询类型 1、无关子查询 无关子查询是这样的一种查询，它在外围查询之前执行，然后返回数据供外围查询使用，它和外围查询的联系仅此而已。在编写嵌套了子查询的SQL语句时，如果被嵌套的查询中不包含对于外围查询的任何引用，就可以使用无关子查询。编写无关子查询的方式有很多种，最常见的是在IN子句中嵌套。 SELECT select_list FROM table_name[, table_name, ...] WHERE column_name IN (SELECT [DISTINCT] column FROM table_name [WHERE condition])

5 三、子查询类型 2、相关子查询 相关子查询和无关子查询的定义是相对的。它是这样的一种查询，在该子查询执行时要使用到外围查询的数据。子查询执行结束后再将它的查询结果返回到它的外围查询中，供外围查询比较使用。 SELECT dno Department, name Manager FROM Employee e WHERE 0 <= ANY (SELECT MONTHS_BETWEEN(mgrstrdate, ’ ’) FROM Department WHERE e.id = mgrid)

6 四、子查询的规则 使用子查询的限制主要是指对子查询的SELECT列表和子查询中对函数指定的一些限制。
（1）由比较运算符或关键字IN引入的内层子查询的SELECT列表中只允许有一项内容，即只能是一个列名或表达式。而且在子查询的SELECT列表中命名的列必须能与在外围语句的WHERE子句中命名的列连接兼容。 （2）在使用关键字EXISTS引入的子查询的SELECT列表中不需要指定具体的列名，可以都由星号“*”组成，这是因为这种情况下只需要测试任何一个符合标准的行的存在。或者也可以在子查询的WHERE子句中加入行的限定语句。 （3）子查询不能包括ORDER BY子句，因为子查询不需要在内部处理它们的查询结果。但是使用关键字DISTINCT也可以有效地对查询结果进行排序，因为一些系统是通过首先给结果排序来消除复制的。 （4）由后面不跟关键字ANY或ALL的比较运算符所引入的子查询除非预先确定了它只能成组返回单个的值，否则不允许使用GROUP BY或HAVING子句。

7 五、返回多个值的子查询 1、IN的使用 在IN子句中，子查询和手动输入一组数据的区别就在于，我们手动输入时，一般都会输入两个或两个以上的数值，而使用子查询时，不能确定它返回的结果的数量。但是，即使子查询返回的结果为空，语句也能正常运行。  Results manager mgrid ———— ——— 林志千 陈广海 张宇 张峰 李志深 魏成 [6rows] SELECT name manager, id mgrid FROM Employee WHERE id IN (SELECT mgrid FROM Department)

8 五、返回多个值的子查询 2、NOT IN的使用
子查询还可以用在外围查询的NOT IN子句中，以产生NOT IN使用的清单。NOT IN的使用和IN的使用基本一样。如果外围查询中用来比较的记录值被检测出与子查询产生的结果集中的所有值都不匹配，那么NOT IN子句就返回“真”，然后将该记录指定列的值输入到最终的结果集中。 Results Name sex ———— ——— 李明 男 李大平 男 林国荣 男 庞文凯 男 刘燕 女 林志祥 男 吴忠彦 男 魏箐 女 王静 女 张敏燕 女 魏华翔 男 王亚鹏 男 林雪飞 女 姜艳成 女 梁朝阳 男 王力刚 男 余文惠 女 陈志强 男 [18rows] SELECT name, sex FROM Employee WHERE id NOT IN (SELECT mgrid FROM Department)

9 五、返回多个值的子查询 3、IN与相关子查询的联合使用 IN与相关子查询联合使用：
SELECT pno Project, name Manager, dno Department FROM Employee e WHERE ’2001’ IN (SELECT TO_CHAR(pstrdate, ’YYYY’) FROM Project WHERE e.id = pmgrid) ORDER BY pno Results Project manager Department ———— ————— ———— 李明 林志千 林志千 魏华翔 魏华翔 林志千 [6rows] 用户可以使用下面的连接或无关子查询来替换上面的IN与相关子查询联合使用的SQL语句，而且可能获得更好的性能 。 SELECT e.pno Project, e.name Manager, e.dno Department FROM Employee e, Project p WHERE TO_CHAR(p.pstrdate, ’YYYY’) = ’2001’ AND e.id = p.pmgrid) ORDER By e.pno SELECT pno Project, name Manager, dno Department FROM Employee WHERE id IN (SELECT pmgrid FROM Project WHERE TO_CHAR(pstrdate, ’YYYY’) = ’2001’) ORDER BY pno

10 五、返回多个值的子查询 4、交集和差集的查询：EXISTS
使用关键字EXISTS引入子查询可以将该子查询用作存在性测试，即测试是否存在满足子查询准则的数据。如果子查询返回的结果是空集，则判断为不存在，即EXISTS失败，NOT EXISTS成功。如果子查询返回至少一行的数据记录，则判断为存在，即EXISTS成功，NOT EXISTS失败。 关键字EXISTS一般直接跟在外围查询的WHERE关键字后面。它的前面没有列名、常量或者表达式； 关键字EXISTS引入的子查询的SELECT列表清单可以而且通常都是由星号“*”组成的。因为我们只是测试满足子查询的数据行的存在性，在子查询的SELECT列表清单中加入列名没有实际意义。

11 五、返回多个值的子查询 5、空集的查询：NOT EXISTS
因为使用了关键字EXISTS的子查询在检索到一个为真的结果后就可以停止查询，所以当满足条件的行数比较多时，用户可以使用EXISTS来代替一些有相同的结果但是性能不如EXISTS子查询的SQL语句。

12 五、返回多个值的子查询 7、ANY的使用 使用关键字ANY引入子查询时，外层查询使用指定的数据值，按照特定的比较运算符与子查询结果集中的每一个值进行比较。如果有一个比较为真，则和整个子查询结果集的比较就为真。 8、ALL的使用 关键字ALL与ANY的区别在于：使用关键字ALL引入子查询时，外层查询使用指定的数据值按照特定的比较运算符与子查询结果集中的每一个值进行比较。如果有一个比较为假，则和整个子查询结果集的比较就为假，只有所有的比较结果都为真，和整个子查询结果集的比较才为真。

13 六、返回单个值的子查询 1、比较运算符和相关子查询
在保证相关子查询每一次运行返回的结果都是单个值的情况下就可以使用标准比较运算符来直接引入该相关子查询。  Results name sex id ———— —— —— 张峰 男 张敏燕 女 魏华翔 男 王亚鹏 男 林雪飞 女 姜艳成 女 [6rows] SELECT name, sex, id FROM Employee e WHERE ’技术部’ = (SELECT dname FROM Department WHERE e.dno = dnumber) ORDER BY id

14 六、返回单个值的子查询 2、聚集函数和子查询
只要不使用GROUP BY子句，在SELECT清单中使用了聚集函数的子查询就总是会返回单个值。因此，我们就能使用标准的比较运算符来直接引入这种使用了聚集函数的子查询。 3、返回单个值的GROUP BY和HAVING子句 只有确定我们在子查询中使用的GROUP BY子句和HAVING子句返回的是单个的值，才能使用标准比较运算符来引入该子查询。

15 七、子查询的嵌套 1、嵌套子查询 子查询可以嵌套在一般的查询语句中一样，子查询本身也可以包含子查询，而且可以是任意多个，这就是嵌套子查询。对嵌套子查询来说，随着嵌套层数的增加，查询语句的性能会不断下降，这也是嵌套子查询的唯一限制。 2、子查询与连接结合 子查询和连接各有优缺点。使用子查询可以计算一个变化的聚集值并返回到外围查询进行比较，连接查询做不到这一点。而使用连接可以在查询语句的最后结果显示多个表中的数据，子查询做不到这一点。如果我们同时使用子查询和连接就可以利用它们的优势而避免它们的缺陷

16 七、子查询的嵌套 3、在HAVING子句中使用子查询 4、在UPDATE,DELETE和INSERT中使用子查询
在编写UPDATE和DELETE语句时，一般在它们的WHERE子句中使用子查询来代替连接，因为很多数据库都不支持在这两种语句中使用连接来对多个表进行操作。INSERT语句中没有WHERE子句，但是它也可以和子查询一起使用。 SELECT dno, AVG(salary) FROM Employee GROUP BY dno HAVING AVG(salary) > (SELECT AVG(salary) FROM Employee)  Results Dno AVG(salary) —— ———— [2rows]

17 七、子查询的嵌套 5、在FROM中使用子查询
ANSI标准允许在查询语句的FROM子句中使用子查询。在FROM子句中使用的子查询相当于一个虚拟表，和视图的作用差不多。 SELECT MAX(temp.avg_salary) FROM ( SELECT dno, AVG(salary) avg_salary FROM Employee GROUP BY dno) temp Results MAX(temp.avg_salary) ————— [1rows]

18 本章小结： 掌握子查询的定义和规则 了解子查询的类型 掌握各种子查询的使用方法