ppt数据库实用教程四1

1、讲授：麻淑芳 时间：2012年2月数据库实用教程第四章 结构化查询语言 4.1 SQL概述 4.2 SQL基本知识 4.3 SQL的数据定义 4.4 SQL的数据查询 4.5 SQL的数据更新 4.6 嵌入式SQL4.1 SQL概述结构化查询语言（Structured Query Language，SQL）是一种介于关系代数与关系演算之间的语言，其功能包括查询、操作、定义和控制四个方面，是一种通用的功能极强的关系数据库标准语言。SQL的发展历程 1974年由Boyce和Chamberlin提出来，最初在System R关系数据库中实现。 1986年成为美国国家标准。 1987年成为国际标准SQ

2、L86。 1989年,ISO颁布了增强完整性特征的SQL89标准。 1993年,ISO对SQL89标准进行了大量的修改和扩充,推出SQL2(SQL92)。SQL的发展历程SQL是一种通用的、功能极强的关系数据库语言。各数据库厂家都推出了支持SQL的软件或与SQL接口软件,使自己的数据库可以运行SQL.许多其他软件(例如图形处理、人工智能、软件工程工具、软件开发工具等)也利用SQL语言的数据查询功能,作为自身的一部分,使其数据查询功能更强大.许多程序设计语言中也都引入了SQL.SQL数据库的体系结构uSQLSQL数据库的体系结构也是三级模式结数据库的体系结构也是三级模式结构。构。 外模式对应于视

3、图View和部分基本表； 模式对应于全部基本表； 内模式对应于存储文件； 元组称为“行”； 属性称为“列”。SQL数据库的体系结构u SQLSQL数据库的体系结构具有如下特征：数据库的体系结构具有如下特征： 一个SQL模式是表和约束的集合。 一个表是行的集合，每行是列的序列，每列对应一个数据项。 一个表可以是一个基本表，也可以是一个视图。基本表基本表是实际存储在数据库中的表。视图视图是从基本表或其他视图中导出的表；它并不存放实际的数据,仅保存视图的定义；使用视图时,根据视图定义,从基本表中取数据；因此,视图实际上是一个虚表。SQL数据库的体系结构 一个基本表可以跨一个或多个存储文件，一个存储文

4、件也可存放一个或多个基本表。一个表可以带若干索引，索引也存放在存储文件中。存储文件与外部存储器上的一个物理文件对应。 用户可以用SQL语言对基本表和视图进行操作；从用户的观点看基本表和视图是一样的。 SQL用户可以是应用程序，也可以是终端用户。SQL语句可嵌入在宿主语言的程序中使用，也能作为独立的用户接口，供终端用户使用。SQL数据库的体系结构SQL的组成uSQLSQL主要分为四个部分：主要分为四个部分： 数据定义（SQL DDL） 数据操作(SQL DML) 数据控制(SQL DCL) 嵌入式SQL的使用SQL的组成u SQL具有很强的功能，但设计巧妙，使得语言十分简捷，其核心功能只用其核心

5、功能只用9 9个动词：个动词：数据定义：数据定义：CREATE(CREATE(创建表、索引、视图创建表、索引、视图) ) DROP( DROP(删除表、索引、视图删除表、索引、视图) ) ALTER( ALTER(修改表修改表) )数据查询：数据查询：SELECT(SELECT(查询查询) )数据更新：数据更新：INSERT(INSERT(插入插入) ) DELETE( DELETE(删除删除) ) UPDATE( UPDATE(修改修改) )数据控制：数据控制：GRANT(GRANT(授权授权) ) REVOKE(REVOKE(收回授权收回授权) )4.2 SQL基本知识 数据类型数据类型

6、语法规定与约定语法规定与约定 表达式与运算符表达式与运算符 函数函数 数据类型 数据类型数据类型用于给特定的列提供数据规则，它决定着数据在列中的存储方式和给列分配的数据长度 ，并且决定了此数据是字符、数字还是时间日期数据。 每一个具体的SQL的实施方案都有自己特有的数据类型，因此有必要使用与具体的实施方案相关的数据类型。 SQL提供一些基本数据类型基本数据类型，并允许用户自定义新的域。数据类型基本数据类型uSQLSQL提供的基本数据类型有：提供的基本数据类型有： 数值型 字符串型 位串型 日期、时间型 逻辑型 数据类型基本数据类型（1 1）数值型）数值型 INT/INTEGERINT/INTE

7、GER：长整数长整数 SMALLINTSMALLINT：短整数短整数 REALREAL：浮点数浮点数（取决于机器精度） DOUBLE PRECISIONDOUBLE PRECISION：双精度浮点数双精度浮点数（取决于机器精度） FLOAT(nFLOAT(n) )：浮点数浮点数，精度至少为n位数字 NUMERIC(p,dNUMERIC(p,d)：定点数定点数，有共p位数字，其中小数点后有d位。(也可写成DECIMAL(p,d) 或DEC(p,d) ) 数据类型基本数据类型（2 2）字符串型）字符串型 CHAR(nCHAR(n) )：长度为 n 的定长字符串定长字符串，n是字符串中字符的个数 V

8、ARCHAR(nVARCHAR(n) )：具有最大长度为 n 的变长字变长字符串符串 数据类型基本数据类型（3 3）位串型：）位串型： BIT(nBIT(n) )：长度为 n 的二进制位串二进制位串 BIT VARYING(nBIT VARYING(n) )：最大长度为 n 的变长二变长二进制位串进制位串数据类型基本数据类型（4 4）日期、时间型：）日期、时间型： DATEDATE：日期型：日期型 包含：年、月、日 格式为：YYYY-MM-DD TIME TIME：时间型：时间型 包含：一日的时、分、秒 格式为：HH:MM:SS 数据类型基本数据类型（5 5）逻辑型：）逻辑型： BOOLEAN

9、BOOLEAN：表示具有逻辑类型的值。 可能的值是：TRUETRUE、FALSEFALSE和和UNKNOWNUNKNOWN SQL支持空值空值(NULL)(NULL)的概念，空值是RDB中的一个重要概念，与空(或空白)字符串、数值0具有不同的含义，不能把它理解为任何意义的数据。数据类型基本数据类型 有的系统可能还会提供货币型、文本货币型、文本型、图像型型、图像型等类型。 SQL允许在上面列出的数据类型（值域）上执行比较操作，但算数操作只限于数数值型值型。数据类型用户定义类型uSQLSQL允许用户定义新的域，并在定以后允许用户定义新的域，并在定以后作为基本数据类型看待：作为基本数据类型看待：例如

10、：例如：CREATE DOMAINCREATE DOMAIN PERSON_NAME CHAR(10) PERSON_NAME CHAR(10)语法规定与约束1 1、 语句格式约定符号语句格式约定符号 ：其中的内容为必选项,它表示实际语义，不能为空； ：其中内容为任选项； 或或：必选其中之一项； ，nn：表示前面的项可以重复多次。语法规定与约束2 2、 语法规定语法规定 一般语法规定 SQL中数据项(列、表和视图)的分隔符为“ , ”； 字符串常数的定界符用单引号“ ”表示。 SQL特殊语法规定 SQL的关键词一般使用大写字母表示； SQL语句的结束符为“ ；”； SQL采用格式化书写方式。表

11、达式与运算符u 表达式：表达式：由一个或多个值、运算符和函数组合而成，可计算出一个值，其数据类型一般为它的成分的数据类型。u 运算符：运算符：是保留字或字符，主要用于在子句中执行比较和数学运算等操作。 在SQL语句中运算符用于确定条件或建立语句中多个条件之间的连接。表达式与运算符1.1. 比较运算符比较运算符2.2. 算术运算符算术运算符3.3. 谓词谓词4.4. 逻辑运算符逻辑运算符表达式与运算符比较运算符1.1. 比较运算符比较运算符 在SQL语句中用于测试单个的值。包括： 、!或、 用于测试数据相等、不等、小于、大于、小于等于和大于等于的各种情况。 当条件满足时，返回True，否则返回F

12、alse。 当被测试的值为NULL时，返回Unknown。表达式与运算符算术运算符2. 2. 算术运算符算术运算符 用于执行数学运算的功能用于执行数学运算的功能 通常有四种操作符：+ +、-、* 和 / 可单独使用，也可组合使用表达式与运算符谓词3. 3. 谓词谓词 SQLSQL语言中使用谓词来语言中使用谓词来构造条件构造条件，以便只处，以便只处理满足这些条件的那些理满足这些条件的那些行（元组）行（元组）。 IS NULL / IS NOT NULL BETWEENAND / NOT BETWEENAND LIKE / NOT LIKE IN / NOT IN ALL和ANY EXISTS /

13、 NOT EXISTS UNIQUE / NOT UNIQUE表达式与运算符谓词 IS NULL / IS NOT NULL IS NULL / IS NOT NULL 用于判断字段的值是否是用于判断字段的值是否是空值空值。例：查询缺少成绩（GRADE）的学生的学号。可在WHERE子句中使用的表达式为：GRADE IS NULL 注意：注意：不能用“”来查找NULL值。若使用“”，则表示要查找的是字符“NULL”，而不是NULL值。 例：查找不属于任何系的学生。错误的表达式为：SDEPTNULL 表达式与运算符谓词 BETWEENAND / NOT BETWEENAND BETWEENAND

14、/ NOT BETWEENAND 谓词谓词BETWEENBETWEEN用于判断值是否在给定的两个用于判断值是否在给定的两个值之间，注意值之间，注意包括边值包括边值。 例：查找成绩（GRADE）在90分到100分之间的学生。可在WHERE子句中使用表达式： GRADE BETWEEN 90 AND 100 也可使用表达式：GRADE90 AND GRADE100 显然在可读性上谓词BETWEEN更好一些。 BETWEEN前面加上NOT表示否定。 表达式与运算符谓词 LIKE / NOT LIKE LIKE / NOT LIKE 可以用来进行字符串的匹配。可以用来进行字符串的匹配。其一般语法格 式

15、如下： NOT LIKE NOT LIKE ESCAPEESCAPE 在指定的关系中查找指定属性列值与 相匹配的元组。表达式与运算符谓词 LIKE / NOT LIKE LIKE / NOT LIKE说明说明1 1： 中经常使用通配符通配符( (百分百分号号) )和和_(_(下划线下划线) )，且可组合使用。其中： ( (百分号百分号) )：代表任意长度(长度可能为0)的字符串； _(_(下划线下划线) )：代表任意单个字符；例： A%B 以A开头，以B结尾的任意长度的字符串。 A_B 以A开头，以B结尾长度为3的任意字符串。 表达式与运算符谓词 LIKE / NOT LIKE LIKE /

16、NOT LIKE说明说明2 2：如果LIKE后面的匹配符中不含通配符，则可以用“”运算符取代谓词LIKE，用或!运算符取代谓词NOT LIKE。说明说明3 3：若要查询的字符串本身就含有“%”或“_”，则需要使用由ESCAPE指定的换码字换码字符符来区分。表达式与运算符谓词 LIKE / NOT LIKE LIKE / NOT LIKE例：要查找“以COM_cn开始且最后一个字母为任意的字符串”，那么在WHERE子句中可用LIKE谓词表示为:LIKECOM_cn_ESCAPELIKECOM_cn_ESCAPE 其中，指定的换码字符为；所以匹配符COM_cn_中的第一个下划线前因为有换码字符，所

17、以将其解释为下划线本身，而不再是通配符；第二个下划线则仍是通配符。 表达式与运算符谓词 IN / NOT IN IN / NOT IN 用于判断值是否属于指定的集合，实现集合用于判断值是否属于指定的集合，实现集合成员的资格比较。成员的资格比较。其语法格式为：属性属性 IN/NOT ININ/NOT IN（集合）（集合） 这里的集合可以是属性值的集合，也可以是一个SELECT语句，但它们中元素应与前面属性具有一致的结构。例：查找学生所在系（SDEPT)是否为以下几个值中的一个：CS，MA，IS。可在WHERE子句 中使用表达式： SDEPT IN (CS,MA,IS) 表达式与运算符谓词 ALL

18、 ALL和和ANYANY（SOMESOME） 是是定量谓词定量谓词，用于将某个值同另一集，用于将某个值同另一集合中的每个值相比较，实现集合成员合中的每个值相比较，实现集合成员的算术比较。的算术比较。其语法格式为：表达式表达式 ALL/ANY (ALL/ANY (集合集合) ) 这里的集合可以是值的集合，也可以是一个SELECT语句，但它们中元素应与前面表达式的值具有一致的结构。表达式与运算符谓词 ALL ALL和和ANYANY（SOMESOME） 是算术比较运算符。“ANYANY”表示左边表达式的值与右边集合中至少一个元素满足运算；“ALLALL”表示左边的表达式的值与右边集合中每一个元素满足

19、运算。 SQL中规定ANY和SOME为同义，早期标准用ANY。 另外，=ANT=ANT与ININ等价；ALLALL与NOT INNOT IN等价。表达式与运算符谓词 EXISTS / NOT EXISTS EXISTS / NOT EXISTS 是存在量词，用于测试一个集合是否为是存在量词，用于测试一个集合是否为非空或空，实现空关系的测试。非空或空，实现空关系的测试。其语法格式为: EXISTS/NOT EXISTS EXISTS/NOT EXISTS（子查询）（子查询） 带有EXISTS谓词的子查询不返回任何数据。当子查询结果非空非空时，返回逻辑真值True；否则，返回逻辑假值False。

20、表达式与运算符谓词 UNIQUE / NOT UNIQUE UNIQUE / NOT UNIQUE 用于测试一个集合里是否有重复元组用于测试一个集合里是否有重复元组存在，实现重复元组的测试。存在，实现重复元组的测试。其语法格式为：UNIQUE/NOT UNIQUE(UNIQUE/NOT UNIQUE(集合集合) ) 对于UNIQUE操作,当集合中不存在重复元组时，其逻辑值为True，否则为False。表达式与运算符逻辑运算符3. 3. 逻辑运算符逻辑运算符 NOT AND OR 表达式与运算符逻辑运算符 NOT NOT 将它使用的逻辑运算符的意义反转。将它使用的逻辑运算符的意义反转。 如：NO

21、T BETWEEN、NOT IN、NOT EXISTS、NOT LIKE、NOT UNIQUE、 NOT 比较运算符 等。 表达式与运算符逻辑运算符 AND AND 连接多个查询条件，表示所有的查询条件都连接多个查询条件，表示所有的查询条件都 成立。成立。例：查找年龄(AGE)为25岁，并且家庭住址 (ADDRESS)住在大连的女学生(SEX表示性别)。可使用表达式为：AGE25 AND ADDRESS LIKE%大连% AND SEX女表达式与运算符逻辑运算符 OR OR 连接多个查询条件，表示至少有一个查连接多个查询条件，表示至少有一个查询条件成立。询条件成立。例：查找年龄(AGE)为20

22、岁或25岁的学生。可使用表达式为： AGE20 OR AGE25 函数1. 1. 单行函数单行函数 2. 2. 聚合函数聚合函数 函数单行函数1.1. 单行函数单行函数单行函数主要分为数值函数、字符函数、日期函数、转换函数等；它对查询的表或视图的每一行返回一个结果行。 函数单行函数 数值函数：数值函数：用于接受数值输入，返回数值。 许多函数的返回值可精确到38位十进制数字 ，三角函数精确到36位十进制数字。 单行字符函数：单行字符函数：用于接受字符输入，可返回字符值或数值。如：ISLOWER()转换为小写字母。 日期函数：日期函数：是操作DATE数据类型的值，所有日期函数都返回一个DATE类型

23、的值。 转换函数：转换函数：是将一种数据类型的值转换成另一种数据类型的值。函数聚合函数 聚合函数聚合函数 又称统计函数，是用来累加、合计和显示数据极限的函数，它主要用于给SQL语句提供统计信息。 常用的统计函数有COUNT（计数）、 SUM（和）、AVG（平均值）、 MAX（最大值）、 MIN（最小值）等。注：聚合函数只能出现在查询语句中SELECT后的列表达式，或者GROUP BY下的HAVING子句中。不能用在WHERE子句中。4.3 SQL的数据定义SQL的数据定义部分包括对SQL模式（Schema）、基本表（关系,Table）、视图（View）、索引（Index）的创建和撤销操作。SQ

24、L模式的创建和撤销1.1.SQLSQL模式的创建模式的创建CREATE SCHEMACREATE SCHEMA AUTHORIZATION AUTHORIZATION CREATE DATABASECREATE DATABASE AUTHORIZATION AUTHORIZATION 2. SQL2. SQL模式的撤销模式的撤销DROP SCHEMADROP SCHEMACASCADE|RESTRICTCASCADE|RESTRICT CASCADECASCADE（连锁式）（连锁式）：把SQL模式及其下属的基本表、视图缩影等所有元素全部撤销 RESTRICTRESTRICT（约束式）（约束式）

25、：只有当SQL模式中没有任何下属元素是才能撤销SQL模式，否则拒绝执行基本表的创建、修改和撤消1.1.基本表的创建基本表的创建CREATE TABLE SQLCREATE TABLE SQL模式名模式名. .基本表名基本表名( (列名列名 类型类型 列级完整性约束条件列级完整性约束条件 ， 完整性约束，完整性约束，） 基本表名：基本表名：需指出他放在哪个模式中 列名：列名：属性名 类型：类型：属性的数据类型,可以是基本类型，也可以是用户定义的域名基本表的创建、修改和撤消1.1. 基本表的创建基本表的创建CREATE TABLE SQLCREATE TABLE SQL模式名模式名. .基本表名基

26、本表名( (列名列名 类型类型 列级完整性约束条件列级完整性约束条件 ， 完整性约束，完整性约束，）列级完整性约束条件：列级完整性约束条件：NULL/NOT NULL: 该项是否可以取空值UNIQUE: 该项是否具有唯一性 完整性约束：完整性约束：PRIMARY KEY: 设置主键CHECK: 检查子句，用户自定义完整性规则FOREIGN KEY: 设置外键基本表的创建、修改和撤消2. 2. 基本表结构的修改基本表结构的修改（1 1）增加新属性：）增加新属性：ALTER TABLE ALTER TABLE 基本表名基本表名 ADD ADD 新属性名新属性名 新新属性类型属性类型（2 2）删除原

27、有属性：）删除原有属性：ALTER TABLE ALTER TABLE 基本表名基本表名 DROP DROP 属性名属性名CASCADE|RESTRICTCASCADE|RESTRICT（3 3）撤销基本表：）撤销基本表：DROP TABLE DROP TABLE 基本表名基本表名 CASCADE|RESTRICTCASCADE|RESTRICT视图的创建和撤消1. 1. 视图的创建：视图的创建：CREATE VIEW CREATE VIEW 视图名（列名表）视图名（列名表）AS SELECT AS SELECT 查询语句查询语句2. 2. 视图的撤销：视图的撤销：DROP VIEW DROP

28、 VIEW 视图名视图名索引的创建和撤消1. 1. 索引的创建：索引的创建：CREATE UNIQUE INDEX CREATE UNIQUE INDEX 索引名索引名 ON ON 基本表基本表名（名（ ,） UNIQUEUNIQUE表示此索引的每一个索引值只对应唯一的表示此索引的每一个索引值只对应唯一的数据记录数据记录 包括包括ASCASC 和和DESCDESC ，默认为，默认为ASCASC2. 2. 索引的撤销：索引的撤销：DROP INDEX DROP INDEX 索引名索引名4.4 SQL的数据查询SQL的数据查询是关系运算理论在SQL语言中的主要体现。只有一条SELECT语句。在学习

29、时，应注意把SELECT语句和关系代数表达式联系起来考虑问题。SELECT语句格式1.1.SELECTSELECT语句的来历语句的来历 在关系代数中最常用的式子是下列表达式： A1,AnA1,An ( (F F(R1(R1RmRm) ) 这里R1、Rm为关系，F是条件，A1、An为属性。 针对上述表达式，SQL为此设计了SELECT-FROM-WHERE句型： SELECT A1, , An SELECT A1, , An FROM R1, , RmFROM R1, , Rm WHERE F ;WHERE F ; 这个句型是从关系代数表达式演变来的,但WHERE子句中的条件表达式F要比关系代数

30、公式更灵活。 SELECT语句格式1.1.SELECTSELECT语句的来历语句的来历在WHERE子句的条件表达式 F 中可以包含如下内容： 算术运算符 比较运算符 谓词 函数 F 中的运算对象还可以是另一个SELECT语句，即SELECT语句可以嵌套SELECT语句的查询结果之间还可以进行集合的并（ UNION ）、交（ INTERSECT ）、差（ EXCEPT ）操作。SELECT语句格式1.1.SELECTSELECT语句的句法语句的句法SELECTSELECT ALL|DISTINCT ALL|DISTINCT * *| 1 , 2 FROMFROM , 2 WHEREWHERE G

31、ROUP BYGROUP BY 1 HAVINGHAVING ORDER BYORDER BY 2 ASCASC|DESC,;|DESC,; SELECT语句格式 SELECTSELECT子句子句：指定要显示的属性列，表示查询。 其中：ALL ALL 为显示所有满足条件的元组；DISTINCT ISTINCT 为消除重复元组；* * 表示所有列；目标列表达式目标列表达式为查询结果, ,可以有函数运算。 FROMFROM子句子句：指定查询对象，表示从哪些表或视图中查询。 WHEREWHERE子句子句：指定查询条件。 GROUP BYGROUP BY子句子句：按指定的列分组，通常会在每组中使用聚合

32、函数。例如,查询每个学生成绩的平均值时, ,要按学号将每个学生分为一组。 HAVINGHAVING子句子句：对GROUP BY分组进行筛选的条件。 ORDER BYORDER BY子句子句：对输出的目标表按指定列排序。其中ASCASC为升序（缺省值），DESCDESC为降序。SELECT语句格式 另外，另外，SELECTSELECT子句，子句，FROMFROM子句是不可省略的部分，子句是不可省略的部分，整个语句的执行过程为：整个语句的执行过程为：首先，读取FROMFROM子句子句中的基本表、视图的数据，执行笛卡儿积操作；选取满足WHEREWHERE子句子句中行条件表达式的元组；按GROUP B

33、YGROUP BY子句子句中指定列的值分组，同时提取满足HAVINGHAVING子句子句中组条件表达式的那些组；最终，按SELECTSELECT子句子句中给出的列名或列表达式求值输出；按ORDER BYORDER BY子句子句对输出的目标表进行排序。 其中， WHEREWHERE子句子句称为“行条件子句行条件子句”，GROUP GROUP BYBY子句子句称为“分组子句分组子句”，HAVINGHAVING子句子句称为“组组条件子句条件子句”，ORDER BYORDER BY子句子句称为“排序子句排序子句”。单表查询单表查询仅涉及一个表。该查询中涉及单表查询仅涉及一个表。该查询中涉及到的内容：到

34、的内容：1. 选择表中的若干列2. 选择表中的若干元组3. 对查询结果排序 ORDER BY 子句4. 使用聚合函数5. 对查询结果分组 GROUP BY 子句 单表查询1.1.选择表中的若干列选择表中的若干列SELECTSELECT子句子句（1 1）查询指定列）查询指定列 SELECT 列名列名1 ,1 ,列名列名22 FROM 表名（2 2）查询全部列）查询全部列 SELECT * * FROM 表名单表查询1.1.选择表中的若干列选择表中的若干列SELECTSELECT子句子句（3 3）查询经过计算的列）查询经过计算的列SELECT FROM 表名 其中目标列表达式可以为：字符串常量，算

35、术表达式，函数，列别名字符串常量，算术表达式，函数，列别名注：以上内容所对应查询结果表中的列标题必须通过列别名才能标识，否则显示“无列名”。单表查询1.1.选择表中的若干列选择表中的若干列SELECTSELECT子句子句（3 3）查询经过计算的列）查询经过计算的列例：查询全体学生的姓名、出生年份和所属系， 要求用小写字母表示所有系名。并通过列别名改变查询结果的列标题 SELECT SnameSELECT Sname AS AS NAME,Year of Birth: NAME,Year of Birth: BIRTH , 2004-Sage BIRTHDAY , BIRTH , 2004-Sa

36、ge BIRTHDAY , ISLOWER(Sdept ISLOWER(Sdept) DEPARTMENT ) DEPARTMENT FROM S; FROM S;输出结果：NAME NAME BIRTH BIRTH BIRTHDAY BIRTHDAY DEPARTMENT DEPARTMENT- - - - - - -李勇李勇 Year of Birth: 1984 csYear of Birth: 1984 cs 单表查询2.2.选择表中的若干元组选择表中的若干元组（1 1）消除取值重复的行）消除取值重复的行 在SELECT子句中消除重复的行使用DISTINCTDISTINCT，保留重复的

37、行使用ALLALL，默认为ALL。 注意DISTINCT短语的作用范围是所有目标列。例：查询选修课程的各种成绩 错误的写法： SELECT DISTINCT Cno, DISTINCT Grade FROM SC; 正确的写法：SELECT DISTINCT Cno, Grade FROM SC; 单表查询2.2.选择表中的若干元组选择表中的若干元组（2 2）查询满足条件的元组）查询满足条件的元组属于选择运算，通过WHERE子句实现。WHERE常用的查询条件：单表查询3.3.对查询结果排序对查询结果排序ORDER BYORDER BY子句子句 可以按一个或多个属性列排序 升序为ASCASC；降

38、序为DESCDESC；缺省为升序 当排序列含空值空值时： ASC：排序列为空值的元组最后显示 DESC：排序列为空值的元组最先显示单表查询4. 4. 使用聚合函数使用聚合函数聚合函数聚合函数能对集合中的一组数据进行计算，返回单个计算结果，实现一定的统计功能。与其它函数的根本区别与其它函数的根本区别就是它们一般作用在多条记录上。SQL提供的聚合函数有： 计数：计数：COUNT(DISTINCT|ALL COUNT(DISTINCT|ALL * *|) ) 计算总和：计算总和：SUM(DISTINCT|ALLSUM(DISTINCT|ALL) 计算平均值：计算平均值：AVG(DISTINCT|AL

39、LAVG(DISTINCT|ALL) ) 求最大值：求最大值：MAX(DISTINCT|ALLMAX(DISTINCT|ALL) ) 求最小值：求最小值：MIN(DISTINCT|ALLMIN(DISTINCT|ALL) 单表查询4. 4. 使用聚合函数使用聚合函数 其中，DISTINCT短语表示在计算时要取消指定列中的重复值；ALL短语表示不取消重复值；ALL为缺省值。 除COUNT函数之外，聚合函数忽略空值。 另外，聚合函数通常和SELECT语句中的GROUP BY子句一起使用。单表查询4. 4. 使用聚合函数的范围：使用聚合函数的范围： 聚组函数不能不能出现在查询条件WHERE子句中或连

40、接条件ON之后。 聚合函数只能用在SELECT之后作为输出的列表达式； 而作为查询条件，聚合函数只能出现在GROUP BY下的HAVING子句，或选择列表所包含的子查询（子查询的SELECT）之后，并且要对其进行聚合的列必须是外部引用。单表查询4. 4. 使用聚合函数使用聚合函数 在SQL语句中同时包含WHERE子句，GROUP BY子句， HAVING子句以及聚集函数时的执行顺执行顺序序：（1）按WHERE筛选记录。（2）使用GROUP BY子句将WHERE筛选出的记录进行分组。（3）通过HAVING子句将分组后的结果根据聚合函数的运算（如果HAVING子句包含聚集函数）进行过滤。单表查询4

41、. 4. 使用聚合函数使用聚合函数例：查询有3门以上课程是90分以上的学生的学号及（90分以上的）课程数。SELECTSELECT Sno, COUNT(*) AS Count_CnoFROMFROM SC WHEREWHERE Grade=90 GROUPGROUP BYBY Sno HAVINGHAVING COUNT(*) =3; 当使用聚合函数时，在SELECT后面使用的列名必须包含在聚合函数中，或者包含在GROUP BY子句中。如此例中SELECT之后不能包含Cno。单表查询4. 4. 使用聚合函数使用聚合函数 在上例中，WHEREWHERE子句子句的作用是在对查询结果进行分组前，将

42、不符合WHERE条件的行去掉，即在分组之前过滤数据；而HAVINGHAVING子句子句的作用是筛选满足条件的组，即在分组之后过滤数据。 因此，WHERE条件中不能包含聚合函数，而HAVING条件中却经常包含聚合函数，以显示特定的组。单表查询4. 4. 使用聚合函数使用聚合函数 聚合函数也可出现在嵌套子查询SELECT后的列表达式中的例子:例：查询其它系中比信息系所有学生年龄都小的学生的姓名及年龄 SELECT Sname,Sage FROM SWHERE Sdept IS AND Sage =90 GROUP BY Sno HAVING COUNT(*) =3;单表查询5.5.对查询结果分组对

43、查询结果分组GROUP BYGROUP BY子句子句 例：显示每个地区的总人口数和总面积，且仅显示那些总面积超过1000000的地区。 SELECT Region,SUM(Population), SUM(Area)FROM bbcGROUP BY RegionHAVING SUM(Area)1000000 单表查询5.5.对查询结果分组对查询结果分组GROUP BYGROUP BY子句子句 使用使用HAVINGHAVING短语在分组后进行筛选短语在分组后进行筛选 只有满足HAVING短语指定条件的组才输出 HAVINGHAVING短语短语与WHEREWHERE子句子句的区别：作用对象不同 W

44、HEREWHERE子句子句的作用对象是基表或视图，从中选择满足条件的记录。其在分组之前过滤数据，不能包含聚合函数。HAVINGHAVING短语短语的作用对象是组，从中选择满足条件的组。其在分组之后过滤数据，经常包含聚合函数。多表查询多表查询实现来自多个关系的查询。多表查询实现来自多个关系的查询。如果要引用不同关系中的同名属性，则采用“关系名.属性名”的形式表示。该查询中涉及到的内容：该查询中涉及到的内容：1. 连接查询2. 嵌套查询多表查询连接查询SQLSQL中连接查询的主要类型：中连接查询的主要类型：（1）广义笛卡尔积查询（2）等值连接查询（3）自然连接查询（4）非等值连接查询（5）自身连接

45、查询（6）外连接查询（7）复合条件连接查询 多表查询连接查询（1 1）广义笛卡尔积查询）广义笛卡尔积查询不带连接谓词的连接 很少使用 例： SELECT S.* , SC.* FROM S, SC多表查询连接查询（2 2）等值连接查询）等值连接查询 连接运算符为连接运算符为“ “ = ”= ”的连接操作的连接操作 .=. 2 任何子句中引用表1和表2中同名属性时，都必须加表名前缀。引用唯一属性名时可以加也可以省略表名前缀。 例：查询每个学生及其选修课程的情况。 SELECT S.*, SC.* FROM S, SC WHERE S.Sno = SC.Sno; 多表查询连接查询（3 3）自然连接

46、查询）自然连接查询自然连接是等值连接的一种特殊情况，把目自然连接是等值连接的一种特殊情况，把目标列中标列中重复的属性列去掉重复的属性列去掉。例：查询每个学生及其选修课程的情况，用自然连接完成。 SELECT S.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade FROM S, SC WHERE S.Sno = SC.Sno; 多表查询连接查询（4 4）非等值连接查询）非等值连接查询 连接运算符连接运算符 不是不是“ “ = ”= ”的连接操作的连接操作.1.2 为为比较运算符比较运算符：、=、=、!=. BETWEEN BETWEEN . AND AND .

47、3 多表查询连接查询（5 5）自身连接查询）自身连接查询 一个表与其自己进行连接，称为表的自身连接一个表与其自己进行连接，称为表的自身连接需要给表起别名别名以示区别由于所有属性名都是同名属性，因此必须使用别名前缀 例：查询每一门课的间接先修课（即先修课的先修课） SELECT FIRST.Cno, SECOND.Cpno FROM C FIRST, C SECOND WHERE FIRST.Cpno = SECOND.Cno; 多表查询连接查询（6 6）外连接查询）外连接查询（Outer Join）外连接与普通连接的区别外连接与普通连接的区别 普通连接操作只输出满足连接条件的元组外连接操作以指

48、定表为连接主体连接主体，将主体表中不满足连接条件的元组一并输出外连接是对标准SQL的扩充,不同的DBMS对外连接的规定存在差异多表查询连接查询（6 6）外连接查询）外连接查询（Outer Join） 例：查询每个学生及其选修课程的情况（包括没有选修课程的学生）用外连接操作 SELECT SELECT S.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade FROM FROM S LEFT OUT JOIN SC ON S.Sno = SC.Sno; 多表查询连接查询（6 6）外连接查询）外连接查询（Outer Join）多表查询连接查询（6 6）外连接查询）外连

49、接查询（Outer Join） 非主体表非主体表有一“万能”的虚行，该行全部由空值组成。 虚行可以和主体表中所有不满足连接条件的元组进行连接。 由于虚行各列全部是空值，因此与虚行连接的结果中，来自非主体表的属性值全部是空值。多表查询连接查询（6 6）外连接查询）外连接查询（Outer Join） 左外连接：左外连接： LEFT OUTER JOIN / LEFT JOIN 右外连接：右外连接： RIGHT OUTER JOIN / RIGHT JOIN 完全外连接：完全外连接： FULL OUTER JOIN / FULL JOIN 多表查询连接查询（7 7）复合条件连接）复合条件连接 WHE

50、REWHERE子句中含子句中含多个连接条件多个连接条件时，称为时，称为复合条件复合条件连接连接例：查询选修C2号课程且成绩在90分以上的所有学生的学号、姓名 SELECTSELECT S.Sno, S.Sname FROMFROM S,SCWHEREWHERE S.Sno = SC.Sno ANDAND /*连接谓词*/ SC.Cno=C2ANDAND /*其他限定条件*/ SC.Grade 90; /*其他限定条件*/多表查询连接查询（7 7）复合条件连接）复合条件连接 WHEREWHERE子句中含子句中含多个连接条件多个连接条件时，称为时，称为复合条件复合条件连接连接例：查询每个学生的学号

51、、姓名、选修的课程名及成绩。 SELECT SELECT S.Sno, Sname, Cname, Grade FROM FROM S, SC, C WHEREWHERE S.Sno = SC.Sno AND SC.Cno = C.Cno; 多表查询嵌套查询 嵌套查询概述嵌套查询概述 嵌套查询分类嵌套查询分类 嵌套查询求解方法嵌套查询求解方法 引出子查询的谓词引出子查询的谓词 多表查询嵌套查询 嵌套查询概述嵌套查询概述 一个SELECT-FROM-WHERE语句称为一个查询块查询块 将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询嵌套查询，或子查询。多

52、表查询嵌套查询嵌套查询概述嵌套查询概述 子查询的限制：子查询的限制：子查询的SELECT语句中不能不能使用ORDER BY子句， ORDER BY子句永远只能对最终查询结果排序。 层层嵌套方式反映了SQL语言的结构化 有些嵌套查询可以用连接运算替代多表查询嵌套查询嵌套查询分类嵌套查询分类（1 1）不相关子查询：）不相关子查询：子查询的查询条件不依赖于父查询 （2 2）相关子查询：）相关子查询：子查询的查询条件依赖于父查询依赖 多表查询嵌套查询嵌套查询的求解方法嵌套查询的求解方法不相关子查询不相关子查询是由里向外逐层处理。即每个子查询在上一级查询处理之前求解，其的结果用于建立其父查询的查找条件。

53、相关子查询：相关子查询：首先，取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层查询，若WHERE子句返回值为真，则取此元组放入结果表；然后，再取外层表的下一个元组；重复这一过程，直至外层表全部检查完为止。 多表查询嵌套查询引出子查询的谓词引出子查询的谓词(1) 带有IN谓词的子查询(2) 带有比较运算符的子查询(3) 带有ANY或ALL谓词的子查询 (4) 带有EXISTS谓词的子查询多表查询嵌套查询(1)(1)带有带有ININ谓词的子查询谓词的子查询带有IN谓词的子查询是指父查询与子查询之间用IN进行连接，用于判断父查询的某个属性列值是否在子查询的结果中。 由于在嵌套查询中，

54、子查询的结果往往是一个集合，所以谓词IN是嵌套查询中最经常使用的谓词。多表查询嵌套查询(2) (2) 带有比较运算符的子查询带有比较运算符的子查询 当能确切知道内层查询返回单值单值时，可用比较运算符：，=，=， !=或。 可与ANY或ALL谓词配合使用 多表查询嵌套查询(3) (3) 带有带有ANYANY或或ALLALL谓词的子查询谓词的子查询 谓词语义 ：ANY：任意一个值 ALL：所有值 使用ANY或ALL时必须同时使用比较运算符多表查询嵌套查询(3) (3) 带有带有ANYANY或或ALLALL谓词的子查询谓词的子查询 需要配合使用比较运算符：需要配合使用比较运算符： ANY 大于子查询

55、结果中的某个值 ALL 大于子查询结果中的所有值 ANY 小于子查询结果中的某个值 = ANY 大于等于子查询结果中的某个值 = ALL 大于等于子查询结果中的所有值 = ANY 小于等于子查询结果中的某个值 = ALL 小于等于子查询结果中的所有值 = ANY 等于子查询结果中的某个值 = ALL 等于子查询结果中的所有值（通常没有实际意义 ） !=（或）ANY 不等于子查询结果中的某个值 !=（或）ALL 不等于子查询结果中的任何一个值多表查询嵌套查询(3) (3) 带有带有ANYANY或或ALLALL谓词的子查询谓词的子查询 ANY和ALL谓词有时可以用集函数实现ANY和ALL与集函数的

56、对应关系 多表查询嵌套查询(3) (3) 带有带有ANYANY或或ALLALL谓词的子查询谓词的子查询 例： 查询其他系中比信息系所有学生年龄都小的学生姓名及年龄。 方法一：用ALL谓词 SELECT Sname,Sage FROM SWHERE Sdept IS AND Sage ALL (SELECT Sage FROM S WHERE Sdept=IS) ；多表查询嵌套查询(3) (3) 带有带有ANYANY或或ALLALL谓词的子查询谓词的子查询 例： 查询其他系中比信息系所有学生年龄都小的学生姓名及年龄。 方法二：用集函数 SELECT Sname,Sage FROM SWHERE

57、Sdept IS AND Sage (SELECT MIN(Sage) FROM S WHERE Sdept=IS);多表查询嵌套查询(4) (4) 带有带有EXISTSEXISTS谓词的子查询谓词的子查询 EXISTS谓词为存在量词，带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“True”或逻辑假值“False”。 若内层查询结果非空，则返回真值True 若内层查询结果为空，则返回假值False 由EXISTS引出的子查询，其目标列表达式 通常都用 * ，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义。多表查询嵌套查询(4) (4) 带有带有EXISTSEXIS

58、TS谓词的子查询谓词的子查询 EXISTSEXISTS谓词相对应的是谓词相对应的是NOT EXISTSNOT EXISTS，使用存在量词NOT EXISTS： 若内层结果为空，则外层的WHERE条件返回真； 若内层结果为非空，则返回假 不同形式的查询间的替换不同形式的查询间的替换 ： 一些带EXISTS或NOT EXISTS谓词的子查询不能不能被其他形式的子查询等价替换。所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换。 集合查询 SELECT语句的结果是一个或一组元组 集合操作种类：集合操作种类： 并操作并操作(UNION) (UNION) 交操

59、作交操作(INTERSECT) (INTERSECT) 差操作差操作(EXCEPT)(EXCEPT) 当两个子查询结果的结构完全一致时，可以让这两个子查询执行并、交、差操作。 集合查询对集合操作结果的排序对集合操作结果的排序 ORDER BYORDER BY子句子句只能用于对最终查询结果排序，不能对中间结果排序 任何情况下，ORDER BY子句只能出现在最后 对集合操作结果排序时，ORDER BY子句中可以用数字指定排序属性 集合查询例：错误写法 SELECT * FROM SWHERE Sdept= CS ORDER BY Sno UNIONUNION SELECT * FROM SWHER

60、E Sage=19 ORDER BY Sno; 集合查询例：正确写法 SELECT * FROM SWHERE Sdept= CS UNION UNION SELECT * FROM SWHERE Sage=19 ORDER BY Sno ；小结SELECTSELECT语句的一般格式语句的一般格式SELECTSELECTALL|DISTINCT ALL|DISTINCT * *|1别名别名 ,2别名别名 FROMFROM 1别名别名 , ,2别名别名 WHEREWHERE GROUP BYGROUP BY 1 HAVINGHAVING ORDER BYORDER BY 2 ASCASC|DES