结构化查询语言SQL

第三章结构化查询语言SQL

（关系数据标准语言）3.1SQL概述3.2数据定义3.3查询3.4数据更新3.5视图3.6数据控制3.7嵌入式SQL3.8ACCESS应用组件3.1SQL概述3.1.1SQL的特点3.1.2SQL语言的基本概念3.1SQL概述

SQL语言是:1974年由Boyce和Chamberlin提出的。1975年至1979年IBM公司sanJoseResearchLaboratory研制的关系数据库管理系统原形系统SystemR实现了这种语言。3.1SQL概述第一个SQL标准是1986年10月由美国国家标准局（AmericanNationalStandardinstitute,简称ANSI）公布的，所以也称该标准为SQL-----86。1989年国际标准化组织（InternationalOrganizationforStandardization,简称ISO）标准（SQL－89）1992年又公布了SQL－92标准。目前新的SQL标准：SQL33.1SQL概述结构化查询语言（structuredquerylanguage,简称SQL）是一种介于关系代数与关系演算之间的语言，其功能包括查询、操纵、定义和控制4个方面，是一个通用、功能极强的关系数据库语言。目前已成为关系数据库的标准语言大多数数据库均用SQL作为共同的数据存取语言和标准接口，使不同数据库系统之间的互操作有了共同的基础。3.1.1SQL的特点1.综合统一数据库的主要功能是通过数据库支持的数据语言来实现的。3.1.1SQL的特点非关系模型的数据语言一般都分为：*模式数据定义语言（schemadatadefinitionlanguage:DDL）：用于定义模式、外模式、内模式*数据存储有关的描述语言（datastroragedescriptionlanguage,简称DSDL）：进行数据的存取*数据操纵语言（datamanipulationlanguage,简称DML）进行数据的处置当用户数据库投入运行后，如果需要修改模式，必须停止现有数据库的运行，转储数据，修改模式并编译后再重装数据库，因此很麻烦。3.1.1SQL的特点

于一体，风格一致SQL语言集可以独立完成数据库生命周期中的全部活动，包括定义关系模式、录入数据以建立数据库、查询、更新、维护、数据库重构、数据库安全控制等一系列操作的要求，这就是为数据库应用系统开发提供了良好的环境。

3.1.1SQL的特点2.高度非过程化

只提“做什么”，不必指明“怎么做”——用户无需了解存取路径及物理地址——减轻用户负担，提高效率。

3.面向集合的操作方式

不仅查找结果可以是集合，插入，删除，更新也可以是元组，比非关系DBMS优越。3.1.1SQL的特点4.以同一种语法结构提供两种使用方式5.语言简洁，易学易用SQL功能动词数据查询SELECT数据定义CREATE,DROP,ALTER数据操纵INSERT,UPDATE,DELETE数据控制GRANT,REVOTE3.1.2SQL语言的基本概念SQL语言支持关系数据库三级模式结构，如图所示。其中外模式对于视图（view）和部分基本表（basetable），模式对应于基本表，内模式对应用存储文件。SQL视图1视图2基本表1基本表2基本表3基本表4外模式模式存储模式存储文件1存储文件23.1.2SQL语言的基本概念1.基本表是本身独立存在的表,即实际存在DB中的表，而不是从其它表导出的。3.1.2SQL语言的基本概念特性：在关系数据库中一个关系对应一个表基本表可以有若干个索引一个基本表可以跨一个或若干个存储文件一个存储文件可以存储一个或多个基本表一个存储文件与外存储器上的一个物理文件相对应存储文件和相关索引组成了关系的内模式（存储模式）基本表的集合组成关系模型，即全局概念模型。3.1.2SQL语言的基本概念2.视图（View）:从一个或几个基本表或其它视图导出来的表。特性：

视图本身并不独立存储数据,系统只保存视图的定义从用户的观点出发，基本表和视图都是关系，用SQL一样访问,只是对视图的修改受到一定限制

视图可以当作用户按照应用需要定义的外模式，即用户的局部数据—逻辑结构。

3.2数据定义3.2.1定义、删除与修改基本表3.2.2建立与删除索引

3.2.3ACCESS进行数据定义3.2数据定义

关系数据库的基本对象是表、视图和索引。SQL的数据定义功能包括定义表、定义视图和定义索引。索引是依附于基本表的，因此SQL通常不提供修改视图定义和修改索引定义的操作。用户如果想修改视图定义或索引定义，只能先将它们删除掉，然后再重建。不过有些关系数据库产品如ORACLE允许直接修改视图定义。3.2数据定义操作对象操作方式创建删除修改表CREATETABLEDROPTABLEALTERTABLE视图CTEATEVIEWDROPVIEW

索引CREATEINDEXDROPINDEX

表.SQL的数据定义语句

3.2.1定义、删除与修改基本表

1.定义基本表SQL语言定义基本表格式如下：CREATETABLE<表名>

（<列名><数据类型>[列级完整性约束条件][,<列名><数据类型>[列级完整性约束条件]…][,<表级完整性约束条件>]）;3.2.1定义、删除与修改基本表例1.建立一个“学生”表student,它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成。其中学号不能为空，并且其值也唯一。student（Sno,Sname,Ssex,Sage,Sdept)CREATETABLEStudent(SnoCHAR(5)NOTNULLUNIQUE,/*列级完整性约束条件，Sno取值唯一，不取空值*/SnameCHAR(20),SsexCHAR(1),SageINT,SdeptCHAR(15));

3.2.1定义、删除与修改基本表不同数据库系统支持的数据类型不同。如IBMDB2支持的数据类型（常用）：CHAR(n)字符INTEGER全字长整数SMALLINT半字长整数DECIMAL(x,y)十进制数FLOAT(x,y)双字长浮点数Date日期3.2.1定义、删除与修改基本表ACCESS数据类型：

设置数据类型大小文本(Text)（默认值）文本或文本和数字的组合，或不需要计算的数字，例如电话号码。最多为255个字符或长度小于FieldSize属性的设置值。MicrosoftAccess不会为文本字段中未使用的部分保留空间。备注(Memo)长文本或文本和数字的组合。最多为65,535个字符（如果Memo字段是通过DAO来操作并且只有文本和数字[非二进制数据]保存在其中，则Memo字段的大小受数据库大小的限制）。3.2.1定义、删除与修改基本表数字(Number)用于数学计算的数值数据。有关如何设置特定Number类型的详细内容，请参阅FieldSize属性主题。1、2、4或8个字节（如果FieldSize属性设置为ReplicationID，则为16个字节）。日期/时间(Date)从100到9999年的日期与时间值8个字节货币(Current)货币值或用于数学计算的数值数据，这里的数学计算的对象是带有1到4位小数的数据。精确到小数点左边15位和小数点右边4位。8个字节3.2.1定义、删除与修改基本表自动编号(AutoNumber)当向表中添加一条新记录时，由MicrosoftAccess指定的一个唯一的顺序号（每次加1）或随机数。AutoNumber字段不能更新。有关详细内容，请参阅NewValues属性主题。4个字节（如果FieldSize属性设置为ReplicationID则为16个字节）。是/否(Boolean)Yes和No值，以及只包含两者之一的字段（Yes/No、True/False或On/Off）。1位OLE对象(OLEObject)MicrosoftAccess表中链接或嵌入的对象（例如MicrosoftExcel电子表格、MicrosoftWord文档、图形、声音或其他二进制数据）。最多为1G字节（受可用磁盘空间限制）。3.2.1定义、删除与修改基本表以下列形式在ACCESS中定义表可以CREATETABLEStu(SnoCHAR(5)NOTNULLUNIQUEPRIMARYKEY,SnameCHAR(20)UNIQUE,SsexCHAR(1),SageINT,SdeptCHAR(15));3.2.1定义、删除与修改基本表以下列形式在ACCESS中定义表也可以CREATETABLEStu(SnoTEXT(5)NOTNULLUNIQUEPRIMARYKEY,SnameTEXT(20)UNIQUE,SsexTEXT(1),SageNUMBER,SdeptTEXT(15));3.2.1定义、删除与修改基本表2.修改基本表

随着应用环境和用需求的变化，有时需要修改已建立好的基本表，包括增加新列、增加新的完整性约束条件、修改原有的列定义或删除已有的完整性约束条件等。3.2.1定义、删除与修改基本表SQL语言用ALTERTABLE语句修改基本表，其一般格式为：

ALTERTABLE<表名>[ADD<新列名><数据类型>[完整性约束]][DROP<完整性约束名>][MODIFY<列名><数据类型>]；3.2.1定义、删除与修改基本表例2.向stuedent表增加“入学时间”(日期型)列。ALTERTABLEstuedentADDScomeDATE;如果原表中已有数据，这些数据的新增列值均为空。3.2.1定义、删除与修改基本表2.修改基本表例3.将年龄的数据类型改为半字长型。ALTERTABLEstuedentMODIFYSageSMALLINT;修改原有列定义可能破坏已有数据。3.2.1定义、删除与修改基本表2.修改基本表例4.删除学生姓名必须取唯一值的约束。ALTERTABLEstudentDROPUNIQUE(Sname);SQL没提供删除列语句，用户间接完成：建新表，删旧表。3.2.1定义、删除与修改基本表3.取消基本表

把表的定义，表中数据，其上索引以及以该基本表为基础所建立的所有视图全部删除，并释放所占用的存储空间。例5删除Student表DROPTABLEstudent

3.2.2建立及删除索引

建立索引是加快表的查询速度的有效手段索引在概念上可当作一个对照表，提供一种根据索引码的值快速检索数据记录的方法。数据库管理员或表的属主负责建立及删除索引。系统在存取数据时自动选择合适的索引作存取路径，用户不必也不能选择索引。3.2.2建立及删除索引1.索引类型1）

唯一索引（UNIQUE）：要求所有数据行中任意两行中的被索引列不能存在重复值（包括NULL）SQLSever实现方法3.2.2建立及删除索引1）

唯一索引系统自动维护

3.2.2建立及删除索引2）

簇索引（CLUSTER）在簇索引中，行的物理存储顺序与索引顺序完全相同每个表只允许建立一个簇索引，由于在建立簇索引时要改变表中数据行的物理顺序，所以应在其它非簇索引建立之前建立簇索引，以免引起SQLServer重新构造非簇索引。

使用簇索引比非簇索引检索要快

默认情况SQLServer为PrimaryKey建立的是簇索引3.2.2建立及删除索引3）

非簇索引非簇索引不改变行的物理存储顺序。用索引和指针指明。

4）

复合索引建两列或多列的组合索引（最大16列）

3.2.2建立及删除索引2.建立索引

Create[UNIQUE][CLUSTER|NONCLUSTER]INDEX<索引名>ON<表名>(<列名>[<次序>][,<列名>[<次序>]]……);次序：索引值的排列次序，可选ASC或DESCUNIQUE：唯一索引CLUSTER:簇索引NONCLUSTER：非簇索引3.2.2建立及删除索引2.建立索引例6.在student表中按学号升序建唯一索引。CREATEUNIQUEINDEXStusnoONStudent(Sno);在student表中按学号升序和年龄降序建唯一索引。CREATEUNIQUEINDEXSt1ONStudent(SnoASC,SageDESC);3.2.2建立及删除索引3.删除索引

建立索引是为了减少查询时间，但如果数据增加删改频繁，会增加系统维护时间。可删除不必要的索引DROPINDEX<索引名>例7.删除student表中St1索引DROPINDEXSt13.2.3ACCESS进行数据定义1.进入数据定义1)新建查询选设计视图关闭显示表菜单查询SQL特定查询数据定义

3.2.3ACCESS进行数据定义3.2.3ACCESS进行数据定义2）菜单插入查询设计视图菜单视图SQL视图

3.2.3ACCESS进行数据定义1.输入SQL定义建立表将例1-例5分别输入例6、例7同2.运行只能成功运行一次！如有错误，看说明！3.2.3ACCESS进行数据定义4.检查结果1）检查建表选择对象“表”，检查是否有所建表，选中表名，单击右键，选设计视图，检查表结构。2）检查索引UNIQUE索引在设计视图中体现为“关键字”，所以从视图中观察检索引前后关键字标识3.3查询3.3.1单表查询3.3.2连接查询3.3.3嵌套查询3.3.4集合查询3.3.5小结

3.3查询数据库查询是数据库的核心操作。SQL语言提供了SELECT语句进行数据库的查询，该语句具有灵活的使用方式和丰富的功能。其一般格式为：

SELECT[ALL/DISTINCT]<目标列表表达式>[,<目标列表达式>]…FROM<表名或视图名>[,<表名或视图名>]…[WHERE<条件表达式>][GROUPBY<列名1>[HAING<条件表达式>]][ORDERBY<列名2>[ASC/DESC]]3.3查询Select查询目标属性名From查询目标表名及where子句的条件中涉及的所有关系名Where查询目标必须满足的条件

3.3查询SQL基本查询模块：SELECT<表达式1>,<表达式2>,…<表达式n>FROM<关系1>,<关系2>,…，<关系m>WHERE〈条件表达式〉；3.3查询例子数据库：“学生－课程”数据库中包括三个表1.“学生”表Student由学号（Sno）、姓名（Sname）、性别（Ssex）年龄（Sage）、所在系（Sdept）5个属性组成，可记为：Student(Sno,Snome,Ssex,Sage,Sdept)其中Sno为主码3.3查询2.“课程”表Course由课程号（Cno）、课程名（Cname）、先修课号（Cpno）、学分（Ccredit）4个属性组成，可记为：Course(Cno,Cname,Cpno,Ccredit)其中Cno为主码。3.3查询3.“学生选课”表SC由学号（Sno）课程号（Cno）、成绩（Grade）3个属性组成，可记为：SC(Sno,Cno,Grade)其中（Sno,Cno）为主码。3.3.1单表查询(1.选择表中的若干列)单表查询是指仅涉及一个数据库表的查询，比如选择一个表中的某些列值、选择一个表中的某些特定行等。单表查询是一种最简单的查询操作。1.选择表中的若干列选择表中的全部列或部分列，这类运算又称为投影。其变化方式主要表现在SELECT子句的<目标表达式>上。33.3.1单表查询(1.选择表中的若干列)1）查询指定列通过在SELECT子句的<目标列表达式>中指定要查询的属性，有选择的列出感兴趣的列。

例1查询全体学生的学号与姓名SELECTSno，SnameFROMStudent

<目标列表达式>中各个列的先后顺序可以与表中的顺序不一致。也就是说，用户在查询时可以根据应用的需要改变列的显示顺序。3.3.1单表查询(1.选择表中的若干列)例2查询全体学生的姓名、学号、所在系。SELECTSname,Sno,SdeptFROMStudent

这时结果表中的列的顺序与基本表中不同，是按查询要求，先列车姓名属性，然后再列学号属性和所在系属性。3.3.1单表查询(1.选择表中的若干列)2）查询全部列将表中的所有属性列都选出来，可以有两种方法。一种方法就是在SELECT关键字后面列出所有列名。如果列的显示顺序与其表中的顺序相同，也可以简单地将<列目标表达式>指定为*。

例3查询全体学生地详细记录SELECT*FROMStudent;该SELECT语句实际上是无条件地把Student表全部信息都查询出来，所以也称为全表查询，这是最简单的一种查询。3.3.1单表查询(1.选择表中的若干列)3）查询经过计算的值<目标列表达式>可以是有关表达式，即可以将查询出来的属性列经过一定的计算后列出结果。例4查询全体学生的姓名及其出生年份SELECTSname,1996-SageFROMStudent3.3.1单表查询(1.选择表中的若干列)输出的结果为：

Sname1996-Sage李勇1976刘晨1977王名1978张立1978

<目标列表达式>不仅可以式算术表达式，还可以是字符串常量、函数等。3.3.1单表查询(1.选择表中的若干列)例5查询全体学生的姓名、出生年份和所有系，要求用小些字母表示所有系名。SELECTSname，‘YearofBirth:’,1996-Sage,ISLOWER(Sdept)FROMStudent;结果为：

Sname‘YearofBirth:’1996-SageISLOWER(Sdept)

李勇YearofBirth:1976cs刘晨YearofBirth:1977if王名YearofBirth:1978ma张立YearofBirth:1978if3.3.1单表查询(1.选择表中的若干列)用户可以通过指定别名来改变查询结果的列标题，例如，对于上例，可以如下定义列别名

SELECTSnameNAME,‘YearofBirth:’BIRTH,1996-SageBIRTHDAY，ISLWER(Sdept)DEPARTMENTFROMStudent;

结果为：NAMEBIRTHBIRTHDAYDEPARTMENT

李勇YearofBirth:1976cs刘晨YearofBirth:1977if王名YearofBirth:1978ma张立YearofBirth:1978if3.3.1单表查询(2.选择表中的若干元组)2.选择表中的若干元组根据实际需要，从一个指定的表中选择出所有元组的全部或部分列。如果只想选择部分元组的全部或部分列，则还需要指定DISTINCT短语或指定WHERE子句

1）

消除取值重复的行两个本来并不相同的元组，投影到指定的某列上后，可能完全变成相同的行了。3.3.1单表查询(2.选择表中的若干元组)例6查询所有选修过课的学生的学号SELECTSnoFROMSC;假设SC表中有下列数据SnoCnoGrade95001192950012859500138895002290950023803.3.1单表查询(2.选择表中的若干元组)执行上面的SELECT语句后，结果为：Sno9500195001950019500295002该查询结果里包含了许多重复的行。3.3.1单表查询(2.选择表中的若干元组)如果想去掉结果表中的重复行，必须指定DISTINCT语句：

SELECTDISTINCTSnoFROMSC;执行结果为：

Sno9500195002如果没有指定DISTINCT短语，则缺省为ALL，即要求结果表中保留取值重复的行3.3.1单表查询(2.选择表中的若干元组)2）查询满足条件的元组通过WHERE子句实现。WHERE子句常用的查询条件如下表。查询条件谓词比较确定范围确定集合字符匹配空值多重条件=，>,<,>=,<=,!=,<>,!>,!<,NOT+上述比较运算符BETWEENAND,NOTBETWEENANDIN,NOTLIKELIKE,NOTLIKEISNULL,ISNOTNULLAND,OR3.3.1单表查询(2.选择表中的若干元组)比较大小（=,<,>,<=,>=,!=,<>)例7

查计算机系全体学生的名单SELECTSnameFROMStudentWHERESdept=‘CS’3.3.1单表查询(2.选择表中的若干元组)

例8

查所有年龄在20岁以下的学生姓名和及其年龄

SELECTSname,SageFROMStudentWHERESage<20；或SELECTSname,SageFROMStudentWHERENOTSage>＝203.3.1单表查询(2.选择表中的若干元组)例9

查考试成绩有不及格的学生的学号。

SELECTDISTINCTSnoFROMSCWHEREGrade<60

3.3.1单表查询(2.选择表中的若干元组)确定范围

例10查询年龄在20至23之间的学生的姓名、系别和年龄。

SELECTSname,Sdept,SageFROMStudentWHERESageBETWEEN20AND23

与BETWEEN…AND…相对的谓词是NOTBETWEEN..AND…。

3.3.1单表查询(2.选择表中的若干元组)例11查询年龄不在20至23之间的学生的姓名、系别和年龄。SELECTSname,Sdept,SageFROMStudentWHERESageNOTBETWEEN20AND233.3.1单表查询(2.选择表中的若干元组)确定集合谓词IN可以用来查找属性值属于指定集合的元组。

例12查信息系（IS）、数学系（MA）和计算机科学系（CS）的学生的姓名和性别。SELECTSname,SsexFROMStudentWHERESdeptIN(‘IS’,’MA’,‘CS’)3.3.1单表查询(2.选择表中的若干元组)例13查既不是信息系（IS）、数学系（MA）和计算机科学系（CS）的学生的姓名和性别。SELECTSname,SsexFROMStudentWHERESdeptNOTIN(‘IS’,’MA’,‘CS’)3.3.1单表查询(2.选择表中的若干元组)字符匹配谓词LIKE可以用来进行字符串的匹配。其一般语法格式如下：[NOT]LIKE‘<匹配串>’[ESCAPE‘<换码字符>’]其含义是查找指定的属性列值与<匹配符>相匹配的元组。<匹配串>可以是一个完整的字符串，也可以含有通配符％和_。3.3.1单表查询(2.选择表中的若干元组)

％（百分号）:代表任意长度（长度可以为0）的字符串。例如a%b表示以a开头，以b结尾的任意长度的字符串。acb,addgb,ab等都满足该匹配串。

_（下横线）:代表任意单个字符例如a_b表示以a开头，以b结尾的长度为3的任意字符串。Acb,afd等满足该匹配串。3.3.1单表查询(2.选择表中的若干元组)

例14查询学号为95001的学生的详细情况。

SELECT*FROMStudentWHERESnoLIKE‘95001’

该语句实际上与下面的语句完全等价：

SELECT*FROMStudentWHERESno=‘95001’如果LIKE后面的匹配串中不含通配符，则可以用=（等于）运算符取代LIKE谓词，用！＝

或<>（不等于）运算符取代NOTLIKE谓词3.3.1单表查询(2.选择表中的若干元组)例15查所有姓刘的学生、学号和性别

SELECTSname,Sno,SsexFROMStudentWHERESnameLIKE‘刘％’3.3.1单表查询(2.选择表中的若干元组)例16查姓“欧阳”且全名为3个汉字的学生的姓名。

SELECTSnameFROMStudentWHERESnameLIKE‘欧阳--’

注意，由于一个汉字占两个字符的位置，所以匹配串欧阳后面需要跟2个－。3.3.1单表查询(2.选择表中的若干元组)例17查名字中第二个为“阳”字的学生的姓名和学号

SELECTSname,SnoFROMStudentWHERESnameLIKE‘--阳％’；

例18查所有不姓刘的学生姓名SELECTSname,Sno,SsexFROMStudentWHERESnameNOTLIKE‘刘％’3.3.1单表查询(2.选择表中的若干元组)如果用户要查询的匹配字符串本身就含有％或－，比如要查名字为DB-Design的课程的学分，应如何实现呢？这就是要使用ESCAPE‘<换码字符>’短语对通配符进行转义了。3.3.1单表查询(2.选择表中的若干元组)

例19查DB-Design课程的课程号和学分SELECTCno,CcreditFROMCourseWHERECnameLIKE‘DB\-Design’ESCAPE‘\’ESCAPE‘\’短语表示\为换码字符，这样匹配串中紧跟在\后面的字符“－”不再具有通配符的含义，而是取其本身含义，被转义为普通的“－”字符。3.3.1单表查询(2.选择表中的若干元组)例20查以“DB-”开头，且倒数第三个字符为i的课程的详细情况

SELECT*FROMCourseWHERECnameLIKE‘DB\-%I--’ESCAPE’\’注意这里的匹配字符串‘DB\-%i-‘.第一个－前面有换码字符\，所以它被转义为普通的－字符。而％、第2个－和第3个－前面均没有换码字符\，所以它们仍作为通配符。3.3.1单表查询(2.选择表中的若干元组)其执行结果为：

CNOCNAMECCREDIT

8DB-DESIGN410DB-Programing213DB-DBMSDesign43.3.1单表查询(2.选择表中的若干元组)涉及空值的查询谓词ISNULL和ISNOTNULL可用来查询空值和非空值

例21某些学生选修某门课程后没有参加考试，所以有选课记录，但没有考试成绩，下面来查一下缺少成绩的学生的学号和相应的课程号。

SELECTSno,CnoFROMSCWHEREGradeISNULL3.3.1单表查询(2.选择表中的若干元组)例22查所有有成绩的记录的学生和课程号

SELECTSno,CnoFROMSCWHEREGradeISNOTNULL3.3.1单表查询(2.选择表中的若干元组)多重条件查询逻辑运算符AND和OR可用来联结多个查询条件。如果这两个运算同时出现在同一个WHERE条件子句中，则AND的优先级高于OR，但用户可以用括号改变优先级。

3.3.1单表查询(2.选择表中的若干元组)例23查CS系年龄在20岁以下的学生姓名。

SELECTSnameFROMStudentWHERESdept=’CS’ANDSage<20;例12中的IN谓词实际上是多个OR运算符的缩写，因此，例12中的查询也可以用OR运算符写成如下等价形式：

SELECTSnameFROMStudentWHERESdept=’IS’ORSdept=‘MA’ORSdept=’CS’3.3.1单表查询(3.对查询结果排序)3.对查询结果排序

如果没有指定查询结果的显示顺序，DBMS将按其最方便的顺序（通常是元组在表中的先后顺序）输出结果。用户也可以用ORDERBY子句指定按照一个或多个属性列的升序（ASC）或降序（DESC）重新排列查询结果，其中升序ASC为缺省值。

3.3.1单表查询(3.对查询结果排序)例24查询选修了3号课程的学生的学号及其成绩，查询结果按分数的降序排列。SELECTSno,GradeFROMSCWHERECno=’1’ORDERBYGradeDESC3.3.1单表查询(3.对查询结果排序)例25查询全体学生情况，查询结果按所在系升序排列，对同一系中的学生按年龄降序排列。

SELECT*FROMStudentORDERBYSdept,StageDESC;3.3.1单表查询(4.使用集函数)4.使用集函数COUNT([DISTINCT/ALL]*)统计元组个数COUNT([DISTINCT/ALL]<列名>)统计一列中值的个数SUM([DISTINCT/ALL]<列名>)计算一列值的总和（此列必须是数值型)AVG([DISTINCT/ALL]<列名>)计算一列值的平均值（数值型)MAX([DISTINCT/ALL]<列名>)求一列值中最大值MIN([DISTINCT/ALL]<列名>)求一列值中最小值3.3.1单表查询(4.使用集函数)如果指定DISTINCT短语，则表示在计算时要取消指定列中重复值。如果不指定DISTINCT短语或指定ALL短语，则表示不取消重复值。例26查询学生总人数SELECTCOUNT(*)FROMStudent

例27查询选修了课程的学生人数SELECTCOUNT(DISTINCTSno)FROMSC;3.3.1单表查询(4.使用集函数)例28计算1号课程的学生平均成绩。SELECTAVG(Grade)FROMSCWHERECno=’1’;

例29查询学习1号课程的学生最高分数SELECTMAX(Grade)FROMSCWHERECno=‘1’3.3.1单表查询(5.对查询结果分组)5.对查询结果分组GROUPBY子句可以将查询结果表的各行按一列或多列取值相等的原则进行分组。对查询结果分组的目的是为了细化集函数的作用对象。如果未对查询结果分组，集函数将作用于整个查询结果，即整个查询结果只有一个函数值，如上面的例26，例27，例28，例29。否则，集函数将作用于每一个组，即每一个组都有一个函数值。

3.3.1单表查询(5.对查询结果分组)例30查询各个课程号与相应的选课人数。（查询选修每门课的人数）SELECTCno,COUNT(Sno)FROMSCGROUPBYCno;

该SELECT语句对SC表按Cno的取值进行分组，所有具有相同Cno值的元组为一组，然后对每一组作用集函数COUNT以求的该组的学生人数。3.3.1单表查询(5.对查询结果分组)查询结果为：CnoCOUNT(Sno)1222

343

444335483.3.1单表查询(5.对查询结果分组)如果分组后还要求按一定的条件对这些组进行筛选，最终只输出满足指定条件的组，则可以使用HAVING短语指定筛选条件。例31查询选修了3门以上课程的学生的学号SELECTSnoFROMSCGROUPBYSnoHAVINGCOUNT(*)>33.3.1单表查询(5.对查询结果分组)例31查询信息系选修了3门以上课程的学生的学号（为简单起见，这里假设SC表中有一列Dept）SELECTSnoFROMSC WHEREDept＝’IS’GROUPBYSnoHAVINGCOUNT(*)>33.3.1单表查询(5.对查询结果分组)WHERE子句与HAVING短句的根本区别在于作用对象不同。WHERE子句作用于基本表或视图，从中选择满足条件的元组。HAVING短句作用于组，从中选择满足条件的组。

HAVING短句常跟在GROUPBY之后，条件中必须包含库函数，否则条件可直接放到WHERE中。3.3.1单表查询(5.对查询结果分组)WHERE一定在HAVING之前执行例如：查询信息系选修了3门以上课程的学生的学号（假设SC表中有一列DEPT）SELECTSnoFROMSCWHEREDEPT=‘IS’GROUPBYSnoHAVINGCOUNT(*)>33.3.1单表查询(5.对查询结果分组)执行顺序：1.先选择适合WHERE条件的元组2.对选出的元组进行分组3.去除不适合HAVING条件的分组注意！在有分组的查询中，只有分组的项和内置函数可以出现在select后面3.3.2连接查询(1.等值与非等值)连接查询：若一个查询同时涉及两个以上的表，则称之为连接查询。连接查询实际上是关系数据库中最主要的查询连接查询包括：等值连接查询、非等值连接查询、自身连接查询、外连接查询和复合条件连接查询。1.等值与非等值连接查询连接条件或连接谓词：用来连接两个表的条件称为连接条件或连接谓词。

3.3.2连接查询(1.等值与非等值)连接条件或连接谓词的一般格式为：

[<表名1>.]<列名1><比较运算符>[<表名2>.]<列名2>其中比较运算符主要有：＝、>、<、>=、<=、！＝此外，连接谓词词还可以使用下面形式：[<表名1>.<列名1>BETWEEN[<表名2>.]<列名2>AND[<表名2>.]<列名3>

当连接运算符为＝时，称为等值连接。使用其它运算符称为非等值连接。3.3.2连接查询(1.等值与非等值)连接字段:连接谓词中的列名称为连接字段。连接条件中的各连接字段类型必须是可比的，但不必是相同的。例如，可以都是字符型，或都是日期型；也可以一个是整型，另一个是实型，整型和实型都是数值型，因此是可比的。但若一个是字符型，另一个是整数型就不允许了，因为它们是不可比的类型。3.3.2连接查询(1.等值与非等值)DBMS执行连接操作的过程是:首先在表1中找到第一个元组，然后从头开始顺序扫描或按索引扫描表2，查找满足连接条件的元组，每找到一个元组，就将表1中的第一个元组于该元组拼接起来，形成结果表中的一个元组。表2全部扫描完毕后，再到表1中找第二个元组，然后再从头开始顺序扫描或按索引扫描表2，查找满足连接条件的元组，每找到一个元组，就将表1中的第二个元组于该元组拼接起来，形成结果表中的一个元组。重复上述操作，直到表1全部元组都处理完毕为止。3.3.2连接查询(1.等值与非等值)例32查询每个学生及其选修课程的情况3.3.2连接查询(1.等值与非等值)例32解：SELECTStudent.*,sc.*FROMStudent,SCWHEREStudent.sno=sc.sno3.3.2连接查询(1.等值与非等值)

SnoSnameSsexSageSdept95001李勇男20CS95002刘晨女19IS95003王名女18MA95004张立男18ISSnoCnoGrade9500119295001285950013889500229095002380Student.SnoSnameSsexSageSdeptSC.SnoCnoGrade95001李勇男20CS9500119295001李勇男20CS9500128595001李勇男20CS9500138895002刘晨女19IS9500229095002刘晨女19IS95002380Student表

SC表

查询的执行结果为:3.3.2连接查询(1.等值与非等值)SELECT要查询的目标列名FROM目标列涉及的表+条件列涉及的表WHER显式条件+隐式条件隐式条件即表间关联条件若无关联条件则连接结果为表的笛卡尔积连接查询(1.等值与非等值)表CUSTOMER和JOB现有值：连接查询(1.等值与非等值)SELECTCUSTOMER.*,JOB.*FROMCUSTOMER,JOB连接查询(1.等值与非等值)SELECTCUSTOMER.*,JOB.*FROMCUSTOMER.*,JOBWHERECUSTOMER.CUSTOMER_ID=JOB.CUSTOMER_ID3.3.2连接查询(1.等值与非等值)自然连接：如果是按照两个表中的相同属性进行等值连接。且目标中去掉了重复的属性列，但保留了所有不重复的属性列，则称之为自然连接。

例33自然连接Student和SC表

3.3.2连接查询(1.等值与非等值)例33解：SELECTStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,GradeFROMStudent,SCWHEREStudent.Sno=SC.Sno;3.3.2连接查询(1.等值与非等值)Student.SnoSnameSsexSageSdeptSC.SnoCnoGrade95001李勇男20CS9500119295001李勇男20CS9500128595001李勇男20CS9500138895002刘晨女19IS9500229095002刘晨女19IS95002380查询的执行结果为

3.3.2连接查询(4.复合连接

)最常用的就是复合条件连接查询（所以提前讲）多表连接查询的WHERE语句中，有两种类型的条件，一为显式条件，一为隐式（或叫做表间关联条件），如例35中，SC.Cno=‘2’ANDSC.Grade>90为显式条件Student.Sno=SC.Sno为隐式（或叫做表间关联条件）条件3.3.2连接查询(4.复合连接

)4.复合条件连接复合条件连接：WHERE子句中有多个条件的连接操作，称为复合条件连接。

例35查询选修2号课程且成绩在90分以上的所有学生姓名。3.3.2连接查询(4.复合连接

)例35解SELECTStudent.Sno,SnameFROMStudent,SCWHEREStudent.Sno=SC.SnoANDSC.Cno=‘2’ANDSC.Grade>903.3.2连接查询(4.复合连接

)例36查询每个学生选修的课程名及其成绩。3.3.2连接查询(4.复合连接

)例36解：SELECTStudent.Sno,Sname,Course.Cname,SC.GradeFROMStudent,SC,CourseWHEREStudent.Sno=SC.SnoANDSC.Cno=Course.Cno3.3.2连接查询(4.复合连接

)考虑数据库三个表：图书管理关系数据模型：图书（总编号，分类号，书名，作者，出版单位，单价）读者（借书证号，姓名，性别，单位，职称，地址）借阅（借书证号，总编号，借阅日期，备注）

3.3.2连接查询(4.复合连接

)由于SQL是高度非过程化的，用户只要在FROM子句中指出关系名称，在WHERE子句中写明连接条件即可，联接运算由系统去完成并实现优化。例J1：查找所有借阅了图书的读者姓名及所在单位。3.3.2连接查询(4.复合连接

)J1解：SELECTDISTINCT姓名，单位FROM读者，借阅WHERE读者.借书证号=借阅.借书证号*读者，借阅二表均有借书证号域，为避免混淆，用表名.域名表示域名；*本例无显式条件，只有表关联条件3.3.2连接查询(4.复合连接

)例J2：查找李晶所借的所有图书的书名及借阅日期。3.3.2连接查询(4.复合连接

)J2解：SELECT姓名,“所借图书：”，书名，借阅日期FROM图书，读者，借阅WHERE姓名=‘李晶’AND读者.借书证号=借阅.借书证号AND借阅.总编号=图书.总编号

*查询涉及到三个关系的自然连接，用户只需用外关键字指出连接条件，WHERE后两行既是；第一行是显式条件3.3.2连接查询(4.复合连接

)例J3：查询价格在20元以上的已借出的图书，结果按单价降序排列。3.3.2连接查询(4.复合连接

)J3解：SELECT*FROM图书，借阅WHERE借阅.总编号=图书.总编号AND单价>=20ORDERBY单价DESC3.3.2连接查询(4.复合连接

)例J4.分别找出各个单位当前借阅图书的读者人次。3.3.2连接查询(4.复合连接

)例J4解：SELECT单位，‘借书人数：’，COUNT（借书证号）FROM借阅，读者WHERE读者.借书证号=借阅.借书证号GROUPBY单位3.3.2连接查询(4.复合连接

)例J5.找出藏书中各出版单位的册数、价值总额，并按总价降序，总价相同者按册数排序。3.3.2连接查询(4.复合连接

)例J5解：SELECT出版单位，‘册数：’，COUNT（*），‘总价：’，SUM（单价）FROM图书GROUPBY出版单位ORDERBYSUM（单价），COUNT（*）DESC3.3.2连接查询(4.复合连接

)例J6.找出当前至少借阅了5本图书的读者及所在单位。3.3.2连接查询(4.复合连接

)例J6解（CUO）：SELECT姓名，单位FROM读者，借阅WHERE读者.借书证号=借阅.借书证号GROUPBY借书证号HAVINGCOUNT（*）>=53.3.2连接查询(4.复合连接

)例J6解：SELECT姓名，单位FROM读者WHERE借书证号IN（SELECT借书证号FROM借阅GROUPBY借书证号HAVINGCOUNT（*）>=5）3.3.2连接查询(4.复合连接

)例J7.分别找出借书人数超过10个人的单位及人数。3.3.2连接查询(4.复合连接

)例J7解：SELECT单位，‘借书人数：’，COUNT（DISTINCT借书证号）FROM借阅，读者WHERE读者.借书证号=借阅.借书证号GROUPBY单位HAVINGCOUNT（DISTINCT借书证号>103.3.2连接查询(2.自身连接

)2.自身连接

连接操作不仅可以在两个表之间进行，也可以是一个表与其自己进行连接，这种连接称为表的自身连接。例：查询与“李小波”年龄相等的学生姓名。（如果查询年龄等于18的学生姓名，会查，18是固定条件，现在条件是查询结果，并且是出于同一个表的查询，这样需对student表查两遍，一遍是查出“李小波”的年龄，一遍是用“李小波”的年龄作条件，两次对同一表的查询是有关联的--自身连接）3.3.2连接查询(2.自身连接

)SELECTs1.SnameFROMStudents1,Students2

s1作为最后s2作为查条件表，即结果出处表查出李小波年龄WHEREs1.Sage=s2.SageANDs2.Sname=‘李小波’3.3.2连接查询(2.自身连接

)例：查询同时选修了c1和c2两门课的学生学号。（分析：选修了c1和c2两门课程的学生在SC表中有两条记录，可以分两次查出，这两条记录必须满足姓名相等才符合题要求）3.3.2连接查询(2.自身连接

)解答1：SELECTSnoFROMSCWHERECno=‘c1’ORCno=‘c2’解答2：SELECTsc1.SnoFROMSCsc1,SCsc2WHEREsc1.Cno=‘c1’ANDsc2.Cno=‘c2’ANDsc1.Sno=sc2.Sno?哪对哪错？S1、s2叫作SC的别名3.3.2连接查询(2.自身连接

)例34查询每一门课的间接先修课（即先修课的先修课）

分析：CnoCpno1557现在已知1，要找7，即对所有的Cno去查Cpno的Cpno，第二个条件以Cpno作Cno3.3.2连接查询(2.自身连接

)为清楚起见，我们可以把Course表想成是两个完全一样的表，一个是FIRST表，另一个是SECOND表。

CnoCnameCpnoCcredit1数据库542数学23信息系统144操作系统635数据结构746数据处理27PASCAL语言64FIRST表（Course表）

SECOND表（Course表）

CnoCnameCpnoCcredit1数据库542数学23信息系统144操作系统635数据结构746数据处理27PASCAL语言643.3.2连接查询(2.自身连接

)解答：SELECTFIRST.Cno,SECOND.CpnoFROMCourseFIRST,CourseSECONDWHEREFIRST.Cpno=SECOND.Cno;

结果表示如下：CnoCpno1

73

5563.3.2连接查询(3.外连接

)3.外连接

在通常的联接操作中，只有满足联接条件的元组才能作为结果输出例如：查询每个学生及其选修课程的情况SelectStudent.Sno,Sname,Cno,GradeFromStudent,SCWhereStudent.Sno=SC.Sno3.3.2连接查询(3.外连接

)SnoSnameSsexSageSdept95001李勇男20CS95002刘晨女19IS95003王名女18MA95004张立男18ISSnoCnoGrade9500119295001285950013889500229095002380StudentSCSnoSnameCnoGrade95001李勇19295001李勇28595001李勇38895002刘晨29095002刘晨380结果

注意！在此查询结果中没有关于95003和95004两个学生的信息，原因在于他们没有选课，在ＳＣ表中没有相应的元组。

3.3.2连接查询(3.外连接

)有时我们想以Student表为主体列出每个学生的基本情况及其选课情况，若某个学生没有选课，则只输出其基本情况，其选课信息为空值即可——使用外联接（OuterJoin）

外联接运算符通常为“＊”，也用“＋”

例：查询学生选课情况

SelectSno,Sname,Cno,GradeFromStudent.SCWhereStudent.Sno=SC.Sno(*)3.3.2连接查询(3.外连接

)外联接就好象是为＊号指定的表增加一个“万能”的行，这个行全部由空值组成，它可以和另一个表中（Student）所有不能与SC表其他行联接的元组进行联接，即与Student表中95003和95004元组联接，由于这个“万能”行的各列全部是空值，因此在联接结果中，95003和95004两行中来自SC表中的属性值全部是空值。SnoSnameCnoGrade95001李勇19295001李勇28595001李勇38895002刘昆29095002刘昆38095003王名95004张立结果

3.3.2连接查询(3.外连接

)Student.SnoSnameSsexSageSdeptSC.SnoCnoGrade95001李勇男20CS 9500119295001 李勇男20CS 9500128595001 李勇男20CS 9500138895002 刘晨女19IS 9500229095002 刘晨女19IS 9500238095003 王名女18MA95004 张立男18IS如果可以改写例33：

SELECTStudent.SnoSname,Ssex,Sage,Sdept,Cno,GradeFROMStudent,SCWHEREStudent.Sno=SC.Sno(*)外连接符*出现在连接运算符的右边，所以也称其为右外连接。相应地，如果外连接符出项在连接运算符地左边，则称为左外连接。

3.3.3嵌套查询在SQL语言中，一个SELECT-FROM-WHERE语句称为一个查询块。将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询或子查询。例如

SELECTSnameFROMStudentWHERESnoIN(SELECTSnoFROMSCWHERECno=‘2’);3.3.3嵌套查询（1.带有IN谓词）1.带有IN谓词的子查询

带有IN谓词的子查询是指父查询与子查询之间用IN进行连接，判断某个属性列值是否在子查询的结果中。例37查询与“刘晨”在同一个系学习的学生。（自身连接也可完成）3.3.3嵌套查询（1.带有IN谓词）分析：1）确定“刘晨”所在系名（子查询）

2）查找所有在IS系学习的学生

SELECTSno,Sname,SdeptFROMStudentWHERESdeptIN(SELECTSdeptFROMStudentWHERESname=“刘晨”）；

3.3.3嵌套