关系数据库标准语言SQL

第四章SQL语言第四章SQL4.1SQL概述4.2示例数据库4.3SQL数据定义功能4.4SQL数据查询功能4.5SQL数据修改功能4.6SQL数据控制功能4.7嵌入式SQL及存储过程4.8ODBC编程4.1SQL概述历史1974年，由Boyce和Chamber提出。1975-1979年，在SystemR上实现，由IBM的SanJose研究室研制，称为Sequel，现在称为SQL(StruceuredQueryLanguang),结构化查询语言，是关系数据库的标准语言;SQL是一个通用的、功能极强的关系数据库语言。4.1SQL概述有关标准SQL-86：“数据库语言SQL”，1986.10SQL-89：“具有完整性增强的数据库语言SQL”，增加了对完整性约束的支持。1989年SQL-92：“数据库语言SQL”，是SQL-89的超集，增加了许多新特性，如新的数据类型，更丰富的数据操作，更强的完整性、安全性支持等。1992年SQL-3：又称SQL-99，增加对面向对象模型的支持。1999年SQL20032003年4.1SQL概述SQL语言的特点1.综合统一集数据定义语言（DDL），数据操纵语言（DML），数据控制语言（DCL）功能于一体。可以独立完成数据库生命周期中的全部活动：定义关系模式，插入数据，建立数据库；对数据库中的数据进行查询和更新；数据库重构和维护数据库安全性、完整性控制等用户数据库投入运行后，可根据需要随时逐步修改模式，不影响数据的运行。单一的结构----关系，数据操作符统一4.1SQL概述2.高度非过程化非关系数据模型的数据操纵语言“面向过程”，必须制定存取路径用户只需提出“做什么”，无须告诉“怎么做”，不必了解存取路径。存取路径的选择以及SQL的操作过程由系统自动完成。4.1SQL概述3.面向集合的操作方式非关系数据模型采用面向记录的操作方式，操作对象是一条记录SQL采用集合操作方式操作对象、查找结果可以是元组的集合一次插入、删除、更新操作的对象可以是元组的集合4.1SQL概述4.以同一种语法结构提供多种使用方式SQL是独立的语言

能够独立地用于联机交互的使用方式SQL又是嵌入式语言

SQL能够嵌入到高级语言（例如C，C++，Java）程序中，供程序员设计程序时使用4.1SQL概述5.语言简洁，易学易用

SQL功能极强，完成核心功能只用了9个动词。4.1SQL概述SQL语言的基本概念1.SQL支持关系数据库三级模式结构。SQL视图2视图1基本表2基本表1基本表3基本表4存储文件2存储文件1外模式模式内模式4.1SQL概述2.基本表本身独立存在的表SQL中一个关系就对应一个基本表一个(或多个)基本表对应一个存储文件一个表可以带若干索引3.存储文件逻辑结构组成了关系数据库的内模式物理结构是任意的，对用户透明4.视图从一个或几个基本表导出的表数据库中只存放视图的定义而不存放视图对应的数据视图是一个虚表用户可以在视图上再定义视图4.2示例数据库DEPT(DNO,DNAME,DEAN)—系表S(SNO,SNAME,SEX,AGE,DNO,ADRESS)—学生表COURSE(CNO,CN,PCNO,CREDIT,GRADE)–-课程表SC(SNO,CNO,SCORE)–-学生选课表PROF(PNO,PNAME,AGE,DNO,SAL)–-教师表PC(PNO,CNO)–-教师授课表学生成绩及教师任课数据库4.3SQL数据定义功能4.3.1数据库的建立与撤消4.3.2域定义4.3.3基本表的定义4.3.4索引的定义4.3.5数据库的建立与撤消4.3.6SQL数据定义特点4.3SQL数据定义功能SQL的数据定义功能:域定义、表定义、视图和索引的定义。4.3.1数据库的建立与撤消有的数据库系统支持多库。建立一个新数据库

createdatabase

数据库名示例:createdatabasestudent指定当前数据库

database

数据库名关闭当前数据库

closedatabase

数据库名撤消一个数据库

dropdatabase

数据库名示例:dropdatabasestudent4.3.2域定义域类型（SQL-92）

SQL中域的概念用数据类型来实现，定义表的属性时需要指明其数据类型及长度。char（n）：固定长度的字符串。varchar（n）：可变长字符串。int：整数。smallint：小整数类型。numeric（p，d）：定点数，小数点左边p位，右边q位。real：浮点数，取决于机器精度。doubleprecision：双精度浮点数，取决于机器精度。date：日期（年、月、日）。time：时间（小时、分、秒）。interval：两个date或time类型数据之间的差。4.3.2域定义域定义格式

createdomain

域名数据类型示例createdomain

person-namechar（20）

类似C语言中：

typedef

ADDRESS_LIST{ charname[10]; chartelephone[20]; charlocation[20] charemail[20]};

ADDRESS_LISTtom;4.3.3基本表的定义基本表的定义（CREATE）createtable

表名（列名数据类型[default

缺省值][notnull][，列名数据类型[default缺省值][notnull]]…… [，primarykey（列名[，列名]…）] [，foreignkey（列名[，列名]…）

references

表名（列名[，列名]…）] [，check（条件）]）注：如果完整性约束条件涉及到该表的多个属性列，则必须定义在表级上，否则既可以定义在列级也可以定义在表级。4.3.3基本表的定义示例1建立“学生”表S，学号是主码，姓名取值唯一。

CREATETABLES (SnoCHAR(9)PRIMARYKEY，/*列级完整性约束条件*/

SnameCHAR(20)UNIQUE，/*Sname取唯一值*/

sexCHAR(2)，ageSMALLINT，

DNOCHAR(20))；4.3.3基本表的定义示例2

createdomainperson_namechar(20)

createtablePROF (PNOchar(10), PNAMEperson_namenotnull, SAL int, AGEint,

DNOchar(10),

primarykey

(PNO),

foreignkey

(DNO)references

DEPT(DNO),

check(SAL>0))4.3.3基本表的定义示例3建立一个“学生选课”表SC

CREATETABLESC (SnoCHAR(9)，

CnoCHAR(4)，

SCORESMALLINT，

PRIMARYKEY(Sno，Cno)，/*主码由两个属性构成，必须作为表级完整性进行定义*/

FOREIGNKEY(Sno)REFERENCESS(Sno)，/*表级完整性约束条件，Sno是外码，被参照表是S*/

FOREIGNKEY(Cno)REFERENCESCourse(Cno)/*表级完整性约束条件，Cno是外码，被参照表是Course*/ );4.3.3基本表的定义修改基本表定义（ALTER）格式：

ALTERTABLE<表名>[ADD<新列名><数据类型>[完整性约束]]--增加新列[DROP<完整性约束名>]--删除列[ALTERCOLUMN<列名><数据类型>]；--修改列定义

4.3.3基本表的定义示例1向prof表增加“location”列，字符类型为字符型。

altertablePROF

add

LOCATIONchar(30)

不论基本表中原来是否已有数据，新增加的列的值一律为空值。示例2将年龄的数据类型由字符型（假设原来的数据类型是字符型）改为整数。

ALTERTABLESALTERCOLUMNageINT；示例3增加课程名称必须取唯一值的约束条件。

ALTERTABLECourseADDUNIQUE(Cname);4.3.3基本表的定义撤消基本表定义（drop）格式

droptable

表名［RESTRICT|CASCADE］；RESTRICT：删除表是有限制的。欲删除的基本表不能被其他表的约束所引用如果存在依赖该表的对象，则此表不能被删除CASCADE：删除该表没有限制。在删除基本表的同时，相关的依赖对象一起删除

危险

撤消基本表后，基本表的定义、表中数据、索引、以及由此表导出的视图的定义都被删除。4.3.3基本表的定义示例1删除S表

DROPTABLEStudentCASCADE

;基本表定义被删除，数据被删除表上建立的索引、视图、触发器等一般也将被删除

示例2若表上建有视图，选择RESTRICT时表不能删除

CREATEVIEWIS_Student AS SELECTSno，Sname，Sage FROMS WHERESdept='IS'； DROPTABLEStudentRESTRICT;--ERROR:cannotdroptableStudentbecauseotherobjectsdependonit4.3.3基本表的定义示例3如果选择CASCADE时可以删除表，视图也自动被删除

DROPTABLESCASCADE;

--NOTICE:dropcascadestoviewIS_StudentSELECT*FROMIS_Student;--ERROR:relation"IS_Student"doesnotexist4.3.3基本表的定义DROPTABLE时，SQL99与3个RDBMS的处理策略比较序号标准及主流数据库的处理方式依赖基本表的对象SQL99KingbaseESORACLE9iMSSQLSERVER2000RCRCC1.索引无规定√√√√√2.视图×√×√√保留√保留√保留3.DEFAULT，PRIMARYKEY，CHECK（只含该表的列）NOTNULL等约束√√√√√√√4.ForeignKey×√×√×√×5.TRIGGER×√×√√√√6.函数或存储过程×√√保留√保留√保留√保留√保留R表示RESTRICT,C表示CASCADE'×'表示不能删除基本表，'√'表示能删除基本表，‘保留’表示删除基本表后，还保留依赖对象4.3.4索引的定义建立索引的目的：加快查询速度谁可以建立索引DBA或表的属主（即建立表的人）DBMS一般会自动建立以下列上的索引

PRIMARYKEYUNIQUE谁维护索引

DBMS自动完成

使用索引

DBMS自动选择是否使用索引以及使用哪些索引RDBMS中索引一般采用B+树、HASH索引来实现B+树索引具有动态平衡的优点HASH索引具有查找速度快的特点采用B+树，还是HASH索引则由具体的RDBMS来决定索引是关系数据库的内部实现技术，属于内模式的范畴CREATEINDEX语句定义索引时，可以定义索引是唯一、非唯一或聚簇索引

4.3.4索引的定义索引的定义格式

create[unique|distinct][cluster]index

索引名

on

表名(列名[asc|desc][,列名[asc|desc]]…)

unique|distinct：唯一性索引，不允许表中不同的行在索引列上取相同值。若已有相同值存在，则系统给出相关信息，不建此索引。系统并拒绝违背唯一性的插入、更新。

cluster：聚集索引，表中元组按索引项的值排序并物理地聚集在一起。一个基本表上只能建一个聚集索引。

asc|desc：索引表中索引值的排序次序，缺省为asc。示例：

createclusterindexs-indexonS（S#）4.3.4索引的定义示例1:

CREATECLUSTERINDEXStusnameONS(Sname)；--在S表的Sname（姓名）列上建立一个聚簇索引示例2为学生-课程数据库中的S，Course，SC三个表建立索引。CREATEUNIQUEINDEXStusnoONS(Sno)；CREATEUNIQUEINDEXCoucnoONCourse(Cno)；CREATEUNIQUEINDEXSCnoONSC(SnoASC，CnoDESC)；

--S表按学号升序建唯一索引--Course表按课程号升序建唯一索引--SC表按学号升序和课程号降序建唯一索引4.3.4索引的定义索引的删除格式：

dropindex

索引名删除索引时，系统会从数据字典中删去有关该索引的描述。示例1：删除S表的Stusname索引

DROPINDEXStusname；4.3.4索引的定义索引的有关说明可以动态地定义索引，即可以随时建立和删除索引。不允许用户在数据操作中引用索引。索引如何使用完全由系统决定，这支持了数据的物理独立性。在最经常查询的列上建立聚簇索引以提高查询效率。经常更新的列不宜建立聚簇索引。应该在使用频率高的、经常用于连接的列上建索引。一个表上可建多个索引。索引可以提高查询效率，但索引过多耗费空间，且降低了插入、删除、更新的效率。4.3.5SQL数据定义特点

SQL中，任何时候都可以执行一个数据定义语句，随时修改数据库结构。数据库定义不断增长（不必一开始就定义完整）。数据库定义随时修改（不必一开始就完全合理）。可进行增加索引、撤消索引的实验，检验其对效率的影响。4.4SQL数据查询功能4.4.1SQL数据查询基本结构4.4.2单表查询4.4.3连接查询4.4.4嵌套查询4.4.5集合查询4.4.6Select语句的一般形式4.4SQL数据查询功能语句格式

SELECT[ALL|DISTINCT]<目标列表达式>[，<目标列表达式>]…FROM

<表名或视图名>[，<表名或视图名>]…[WHERE<条件表达式>][GROUPBY<列名1>[HAVING<条件表达式>]][ORDERBY<列名2>[ASC|DESC]]；4.4.1SQL数据查询基本结构基本结构

select

A1,A2,…,An

from

r1,

r2,…

,rm

where

P

∏A1,A2,…,An(p(r1r2…

rm))示例给出所有老师的姓名。select PNAMEfrom PROF?4.4.2单表查询查询仅涉及一个表：一、选择表中的若干列二、From子句三、选择表中的若干元组四、ORDERBY子句五、聚集函数六、GROUPBY子句七、更名运算4.4.2单表查询--选择表中的若干列目标列形式：

可以为指定列名，所有属性列(*)，目标列表达式。查询指定列[例1]查询全体学生的学号与姓名。

SELECTSno，Sname

FROMS；

[例2]查询全体学生的姓名、学号、所在系代码。

SELECTSname，Sno，Dno

FROMS；4.4.2单表查询--选择表中的若干列查询所有属性列：在SELECT关键字后面列出所有列名将<目标列表达式>指定为*，表示“所有的属性”[例3]查询全体学生的详细记录。

SELECTSno，Sname，sex，age，Dno

FROMS；

或

SELECT*FROMS；两者完全等价。4.4.2单表查询--选择表中的若干列[例1]查询全体学生的姓名、出生年份和所有地址，要求用小写字母表示所有地址SELECTSname，‘YearofBirth:‘，2007-age，LOWER(adress)FROMS；--LOWER():为SQLSERVER函数输出结果：Sname无列名无列名无列名李勇YearofBirth:1984 abc刘晨YearofBirth:1985 bcv王敏YearofBirth:1986 mmm张立YearofBirth:1985 kkk目标列表达式可以为：算术表达式(带，，，)字符串常量函数列别名

使用更名运算old_nameasnew_name为关系和属性重新命名，可出现在select和from子句中。As可省略。SELECTSname，‘YearofBirth:‘asBIRTH，2007–ageBIRTHDAY，LOWER(adress)AdressFROMS；--LOWER():为SQLSERVER函数输出结果：SnameBIRTHBIRTHDAY

Adress李勇YearofBirth:1984 abc刘晨YearofBirth:1985 bcv王敏YearofBirth:1986 mmm张立YearofBirth:1985 kkk4.4.2单表查询查询仅涉及一个表：一、选择表中的若干列二、From子句三、选择表中的若干元组四、ORDERBY子句五、聚集函数六、GROUPBY子句七、更名运算4.4.2单表查询—from子句说明

from子句列出查询的对象表。当目标列取自多个表时，在不混淆的情况下可以不用显式指明来自哪个关系。示例例：找出工资低于500的职工的姓名、工资、系别。

select PNAME,SAL,DNAME

from PROF,DEPT

whereSAL<500

andPROF.DNO=DEPT.DNO4.4.2单表查询--选择表中的若干元组1.重复元组的处理

如果没有指定Distinct或Unique关键词，则缺省为ALL。[例1]查询选修了课程的学生学号。

SELECTSnoFROMSC；

等价于：

SELECTALLSnoFROMSC；

执行上面的SELECT语句后，结果为：

Sno200215121200215121200215121200215122200215122指定DISTINCT关键词，去掉表中重复的行。

SELECTDISTINCTSno

FROMSC；

执行结果：

Sno

2002151212002151224.4.2单表查询查询仅涉及一个表：一、选择表中的若干列二、From子句三、选择表中的若干元组四、ORDERBY子句五、聚集函数六、GROUPBY子句七、更名运算4.4.2单表查询--选择表中的若干元组2.查询满足条件的元组---where子句表4.4常用的查询条件查

询

条

件谓

词比

较=，>，<，>=，<=，!=，<>，!>，!<；NOT+上述比较运算符确定范围BETWEENAND，NOTBETWEENAND确定集合IN，NOTIN字符匹配LIKE，NOTLIKE空

值ISNULL，ISNOTNULL多重条件（逻辑运算）AND，OR，NOT4.4.2单表查询--选择表中的若干元组比较大小［例1］查询计算机科学系全体学生的名单。

SELECTSname

FROMSWHEREDno=‘CS’；

[例2]查询所有年龄在20岁以下的学生姓名及其年龄。

SELECTSname，ageFROMSWHEREage<20；［例3］查询考试成绩有不及格的学生的学号。

SELECTDISTINCTSno

FROMSCWHEREGrade<60；4.4.2单表查询--选择表中的若干元组（2）确定范围谓词:

BETWEEN…AND…NOTBETWEEN…AND…[例1]查询年龄在20~23岁(包括20岁和23岁)之间的学生的姓名、系别和年龄

SELECTSname，Dno，ageFROMSWHEREageBETWEEN20AND23；[例2]查询年龄不在20~23岁之间的学生姓名、系别和年龄

SELECTSname，Dno，age FROMS WHEREageNOTBETWEEN20AND23；SELECTSname，Dno，ageFROMSWHEREage>=20ANDage<=23?4.4.2单表查询--选择表中的若干元组(3)确定集合谓词：IN<值表>,NOTIN<值表>

[例1]查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别。SELECTSname，sexFROMSWHEREDnoIN('IS'，'MA'，'CS');[例2]查询既不是信息系、数学系，也不是计算机科学系的学生的姓名和性别。SELECTSname，SsexFROMStudent

WHERESdeptNOTIN('IS'，'MA'，'CS')SELECTSname，sexFROMSWHEREDno='IS‘orDno='MA‘OrDno='CS'?4.4.2单表查询--字符匹配谓词：[NOT]LIKE‘<匹配串>’[ESCAPE‘<换码字符>’]匹配串为固定字符串[例1]查询学号为200215121的学生的详细情况。

SELECT*FROMSWHERESno

LIKE‘200215121'；等价于：

SELECT*FROMStudentWHERESno='200215121'；4.4.2单表查询--字符匹配2)匹配串为含通配符的字符串匹配规则：“%”：匹配零个或多个字符。“＿”：匹配任意单个字符。

[例2]查询所有姓刘学生的姓名、学号和性别。

SELECTSname，Sno，sexFROMSWHERESname

LIKE‘刘%’；[例3]查询姓“欧阳”且全名为三个汉字的学生的姓名。

SELECTSname

FROMSWHERESname

LIKE‘欧阳__’[例4]查询名字中第2个字为“阳”字的学生的姓名和学号SELECTSname，SnoFROMSWHERESname

LIKE‘__阳%’；[例5]查询所有不姓刘的学生姓名。SELECTSname，Sno，sexFROMSWHERESname

NOTLIKE'刘%'；4.4.2单表查询--字符匹配3)使用换码字符将通配符转义为普通字符

定义转义字符，以去掉特殊字符的特定含义，使其被作为普通字符看待。如escape“\”，是定义了\作为转义字符，则可用\%去匹配%，用\＿去匹配＿，用\\去匹配\。

ESCAPE‘＼’表示“＼”为换码字符

[例6]查询DB_Design课程的课程号和学分。

SELECTCno，creditFROMCourseWHERECnameLIKE'DB\

Design'ESCAPE'\‘；[例7]查询以"DB_"开头，且倒数第3个字符为i的课程的详细情况。

SELECT*FROMCourseWHERECnameLIKE'DB\

%I

'ESCAPE'\‘；4.4.2单表查询--涉及空值的查询谓词：

ISNULL

或ISNOTNULL

测试指定列的值是否为空值。

“IS”不能用“=”代替[例1]某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。

SELECTSno，Cno

FROMSCWHEREGradeISNULL[例2]查所有有成绩的学生学号和课程号。

SELECTSno，Cno

FROMSCWHEREGradeISNOTNULL；4.4.2单表查询--涉及空值的查询注意事项除is[not]null之外，空值不满足任何查找条件。如果null参与算术运算，则该算术表达式的值为null。如果null参与比较运算，则结果可视为false。在SQL-92中可看成unknown。如果null参与聚集运算，则除count(*)之外其它聚集函数都忽略null。

例：select

sum(SAL)

fromPROF

例：select

count(*)

fromPROF4.4.2单表查询--多重条件查询逻辑运算符：AND和OR来联结多个查询条件

AND的优先级高于OR

可以用括号改变优先级可用来实现多种其他谓词[NOT]IN[NOT]BETWEEN…AND…[例1]查询计算机系年龄在20岁以下的学生姓名。

SELECTSname

FROMStudentWHEREdno='CS'ANDSage<20；4.4.2单表查询--多重条件查询[例2]查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别。SELECTSname，sexFROMSWHEREdnoIN('IS'，'MA'，'CS')可改写为：SELECTSname，sexFROMSWHEREdno='IS'ORdno='MA'ORdno='CS'；4.4.2单表查询查询仅涉及一个表：一、选择表中的若干列二、From子句三、选择表中的若干元组四、ORDERBY子句五、聚集函数六、GROUPBY子句七、更名运算4.4.2单表查询--ORDERBY子句

格式：

orderby

列名[asc|

desc]ORDERBY子句可以按一个或多个属性列排序升序：ASC；降序：DESC；缺省值为升序当排序列含空值时（一般来说）ASC：排序列为空值的元组最后显示DESC：排序列为空值的元组最先显示[例1]查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。

SELECTSno，GradeFROMSCWHERECno='3'ORDERBYGradeDESC；不同的DBMS的规则有所不一样，如MSSQLSERVER的规则刚好相反。4.4.2单表查询--ORDERBY子句［例2］查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。

SELECT*FROMSORDERBYdno，SageDESC；4.4.2单表查询查询仅涉及一个表：一、选择表中的若干列二、From子句三、选择表中的若干元组四、ORDERBY子句五、聚集函数六、GROUPBY子句七、更名运算4.4.2单表查询--聚集函数聚集函数：计数COUNT（[DISTINCT|ALL]*）COUNT（[DISTINCT|ALL]<列名>）计算总和SUM（[DISTINCT|ALL]<列名>）

计算平均值AVG（[DISTINCT|ALL]<列名>）最大最小值

MAX（[DISTINCT|ALL]<列名>）

MIN（[DISTINCT|ALL]<列名>）4.4.2单表查询--聚集函数[例1]查询学生总人数。

SELECTCOUNT(*)FROMS；

[例2]查询选修了课程的学生人数。

SELECTCOUNT(DISTINCTSno)FROMSC；[例3]计算1号课程的学生平均成绩。

SELECTAVG(Grade)FROMSCWHERECno='1'；4.4.2单表查询--聚集函数[例4]查询选修1号课程的学生最高分数。

SELECTMAX(Grade)FROMSCWHERCno=‘1’；[例5］查询学生200215012选修课程的总学分数。

SELECTSUM(credit)FROMSC，CourseWHERSno='200215012'ANDSC.Cno=Course.Cno;

4.4.2单表查询查询仅涉及一个表：一、选择表中的若干列二、From子句三、选择表中的若干元组四、ORDERBY子句五、聚集函数六、GROUPBY子句七、更名运算4.4.2单表查询--GROUPBY子句

分组命令groupby

列名[having

条件表达式]

groupby将表中的元组按指定一列或多列上的值相等的原则分组，然后在每一分组上使用聚集函数，得到单一值。having则对分组进行选择，只将聚集函数作用到满足条件的分组上。未对查询结果分组，聚集函数将作用于整个查询结果对查询结果分组后，聚集函数将分别作用于每个组作用对象是查询的中间结果表按指定的一列或多列值分组，值相等的为一组having子句对groupby子句所确定的行组进行控制,having子句条件中只允许涉及常量,聚组函数或groupby子句中的列.4.4.2单表查询--GROUPBY子句[例1]求各个课程号及相应的选课人数。

SELECTCno，COUNT(Sno)FROMSCGROUPBYCno；

查询结果：

CnoCOUNT(Sno)

122

234 344 433 5484.4.2单表查询--GROUPBY子句[例2]列出所有老师的最高、最低、平均工资。

selectmax(SAL)，min(SAL)，avg(SAL)fromPROF[例3]列出各系的老师的最高、最低、平均工资。

selectDNO，max(SAL)，min(SAL)，avg(SAL)fromPROFgroupbyDNO4.4.2单表查询--GROUPBY子句[例4]查询选修了3门以上课程的学生学号。

SELECTSno

FROMSCGROUPBYSno

HAVINGCOUNT(*)>3；[例5]列出所有课程均及格的学生的平均成绩。

selectSNO，avg(SCORE)

fromSCgroupbySNO

having

min(SCORE)>=60HAVING短语与WHERE子句的区别：作用对象不同WHERE子句作用于基表或视图，从中选择满足条件的元组HAVING短语作用于组，从中选择满足条件的组。

[例6]列出所有同学的及格课程的平均成绩。selectSNO,avg(grade)fromSCwheregrade>=60groupbySNO4.4.2单表查询查询仅涉及一个表：一、选择表中的若干列二、From子句三、选择表中的若干元组四、ORDERBY子句五、聚集函数六、GROUPBY子句七、更名运算4.4.2单表查询--更名运算格式old_nameas

new_name为关系和属性重新命名，可出现在select和from子句中。示例属性更名例：给出所有老师的姓名、所纳税额及税后工资额。

selectPNAME，SAL0.05astaxi，SAL*0.95asincoming

fromPROF4.4.2单表查询--更名运算关系更名找出工资比所在系主任工资高的老师姓名及工资。

selectP1.PNAME，P1.SAL

fromPROFasP1，PROFasP2，DEPT

whereP1.DNO=DEPT.DNO

andDEPT.DEAN=P2.PNO

andP1.SAL>P2.SAL

注：as可选。4.4.3连接查询一、等值与非等值连接查询

二、自身连接三、外连接四、复合条件连接4.4.3连接查询连接查询：同时涉及多个表的查询连接条件或连接谓词：用来连接两个表的条件一般格式：[<表名1>.]<列名1><比较运算符>[<表名2>.]<列名2>[<表名1>.]<列名1>BETWEEN[<表名2>.]<列名2>AND[<表名2>.]<列名3>连接字段：连接谓词中的列名称连接条件中的各连接字段类型必须是可比的，但名字不必是相同的4.4.3连接查询连接操作的执行过程嵌套循环法(NESTED-LOOP)首先在表1中找到第一个元组，然后从头开始扫描表2，逐一查找满足连接件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。表2全部查找完后，再找表1中第二个元组，然后再从头开始扫描表2，逐一查找满足连接条件的元组，找到后就将表1中的第二个元组与该元组拼接起来，形成结果表中一个元组。重复上述操作，直到表1中的全部元组都处理完毕4.4.3连接查询排序合并法(SORT-MERGE）--常用于=连接首先按连接属性对表1和表2排序对表1的第一个元组，从头开始扫描表2，顺序查找满足连接条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。当遇到表2中第一条大于表1连接字段值的元组时，对表2的查询不再继续找到表1的第二条元组，然后从刚才的中断点处继续顺序扫描表2，查找满足连接条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。直接遇到表2中大于表1连接字段值的元组时，对表2的查询不再继续重复上述操作，直到表1或表2中的全部元组都处理完毕为止4.4.3连接查询索引连接(INDEX-JOIN）对表2按连接字段建立索引对表1中的每个元组，依次根据其连接字段值查询表2的索引，从中找到满足条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组4.4.3连接查询一、等值与非等值连接查询

二、自身连接三、外连接四、复合条件连接4.4.3连接查询--等值与非等值连接查询

集合操作（Ⅰ）命令集合并：union集合交：intersect集合差：

except示例求选修了001或002号课程的学生号。

（selectSNO

fromSC

whereCNO=001） unionall

（selectSNO

fromSC

whereCNO=002）集合操作（Ⅱ）求选修了001和002号而没有选003号课程的学生号。

（selectSNO

fromSC

whereCNO=001orCNO=002） except

（selectSNO

fromSC

whereCNO=003）提示集合操作自动去除重复元组，如果要保留重复元组的话，必须用all关键词指明。分组和聚集函数（Ⅰ）分组命令groupby

列名[having

条件表达式]

groupby将表中的元组按指定列上的值相等的原则分组，然后在每一分组上使用聚集函数，得到单一值。having则对分组进行选择，只将聚集函数作用到满足条件的分组上。聚集函数平均值：avg最小值：min最大值：max总和：sum记数：count分组和聚集函数（Ⅱ）示例列出各系的老师的最高、最低、平均工资。

selectDNO，max(SAL)，min(SAL)，avg(SAL)

fromPROFgroupbyDNO列出及格的学生的平均成绩。

selectSNO，avg(SCORE)

fromSCgroupbySNO

having

min(SCORE)>=60分组和聚集函数（Ⅲ）?①求选修了课程的学生人数。

select

count(SNO)

fromSC②selectPNAME，max(SAL)

fromPROF③selectDNO，avg(SAL)

fromPROF

groupbyDNO

whereAGE>60空值（Ⅰ）空值测试is

[not]

null测试指定列的值是否为空值。示例找出年龄值为空的老师姓名。

selectPNAME

fromPROF

whereAGEisnull不可写为whereAGE=null空值（Ⅱ）注意事项除is[not]null之外，空值不满足任何查找条件。如果null参与算术运算，则该算术表达式的值为null。如果null参与比较运算，则结果可视为false。在SQL-92中可看成unknown。如果null参与聚集运算，则除count(*)之外其它聚集函数都忽略null。

例：select

sum(SAL)

fromPROF

例：select

count(*)

fromPROF嵌套子查询集合成员资格集合之间的比较集合基数的测试测试集合是否为空测试集合是否存在重复元组集合成员资格（Ⅰ）in子查询表达式[not]in（子查询）判断表达式的值是否在子查询的结果中。示例选修了001号课程的学生的学号及姓名。

selectSNO，SNAME

fromSwhereSNOin (selectSNO

fromSC

whereCNO=001）集合成员资格（Ⅱ）列出选修了001号和002号课程的学生的学号。

selectSNO

fromSC

whereSC.CNO=001

andSNOin

（selectSNO

fromSC

whereCNO=002）列出张军和王红同学的所有信息。

select*

fromS

whereSNAMEin

（“张军”，“王红”）集合之间的比较（Ⅰ）some/all子查询表达式比较运算符

some（子查询）表达式的值至少与子查询结果中的一个值相比满足比较运算符。表达式比较运算符

all（子查询）表达式的值与子查询结果中的所有的值相比都满足比较运算符。集合之间的比较（Ⅱ）示例找出平均成绩最高的学生号。

selectSNO

fromSC

groupbySNO

having

avg(SCORE)>=all

（select

avg(SCORE)

fromSC

groupbySNO）集合基数的测试（Ⅰ）测试集合是否为空[not]exists（子查询）判断子查询的结果集合中是否有任何元组存在。列出选修了01号课程的学生的学号及姓名。

selectSNO，SNAME

fromS

whereexists

（select*

fromSC

where

CNO=01

andSNO=S.SNO）集合基数的测试（Ⅱ）列出选修了001号和002号课程的学生的学号。

selectSNO

fromSCSC1

whereSC1.CNO=001

and

exists

（selectSNO

from

SCSC2

whereSC2.CNO=002

andSC2.SNO=SC1.SNO）注：in后的子查询与外层查询无关，每个子查询执行一次，而exists后的子查询与外层查询有关，需要执行多次，称之为相关子查询。列出至少选修了001号学生选修的所有课程的学生名。

selectSNAME

fromS

where

notexists

（selectCNO

fromCOURSE

where

exists

（select*

fromSC

whereSC.CNO=COURSE.CNO

andSC.SNO=001）

andnotexists

（select*

fromSC

whereSC.CNO=COURSE.CNO

andSC.SNO=S.SNO）任意课程，001号学生选之，所求学生选之。不存在任何一门课程，001号学生选之，所求学生没有选之。集合基数的测试（Ⅲ）测试集合是否存在重复元组unique(子查询）如果子查询结果中没有重复元组，则返回true。示例找出所有只教授一门课程的老师姓名。

selectPNAME fromPROF

where

unique

（select

PNO

from

PC

wherePC.PNO=PROF.PNO)集合基数的测试（Ⅳ）找出至少选修了两门课程的学生姓名。

selectSNAME fromS where

not

unique

（selectSNO

fromSC

whereSC.SNO=S.SNO)思考

toTRUEornottoTRUE,thatisthequestion.

unique{(a,b,null),(a,b,null)}?派生关系（Ⅰ）命令（子查询）as

关系名（列名，列名，…）

SQL-92中，允许在from子句中使用子查询表达式，这时可将该子查询的结果命名为一个临时关系加以引用。示例找出平均成绩及格的学生。

先求出每个学生的平均成绩，再从中找出及格的学生

select

SNAME，avg（SCORE）

from

S，SC

whereSC.SNO=S.SNO

groupbySC.SNO派生关系（Ⅱ）selectSNAME，AVG_SCOREfrom

(selectSNAME，avg（SCORE）

fromS，SC

whereSC.SNO=S.SNO

groupbySC.SNO)

as

result(SNAME，AVG_SCORE)whereAVG_SCORE>=60派生关系Vs

视图？视图（Ⅰ）定义视图

createviewview_name[(列名[，列名]…)]

as（查询表达式）[withcheckoption]

视图的属性名缺省为子查询结果中的属性名，也可以显式指明。

withcheckoption指明当对视图进行insert，update时，要检查是否满足视图定义中的条件。撤消视图

dropviewview_name视图（Ⅱ）示例createviewCOMPUTER_PROF

as(selectPNO,PNAME,SAL

fromPROF，DEPT

wherePROF.PNO=DEPT.PNO

andDEPT.DNAME=“计算机系”）createviewDEPTSAL(DNO,LOW,HIGH,AVERAGE,TOTAL)

as(selectDNO,min(SAL),max(SAL),

avg(SAL),sum(SAL)

fromPROF

groupbyDNO)视图（Ⅲ）给出计算机系工资超过800的老师姓名。

selectPNAME fromCOMPUTER_PROF whereSAL>800给出计算机系老师的最低、最高、平均工资以及工资总额。

selectLOW,HIGH,AVERAGE,TOTAL fromDEPTSAL,DEPT whereDEPTSAL.DNO=DEPT.DNO

andDEPT.DNAME=“计算机系”关系的连接（Ⅰ）基本分类连接成分包括两个输入关系、连接条件、连接类型。连接条件：决定两个关系中哪些元组相互匹配，以及连接结果中出现哪些属性。连接类型：决定如何处理与连接条件不匹配的元组。连接类型连接条件innerjoinleftouterjoinrightouterjoinfullouterjoinnatureon<谓词>using(A1,A2,…,An)关系的连接（Ⅱ）自然连接：出现在结果关系中的两个连接关系的元组在公共属性上取值相等，且公共属性只出现一次。on<谓词P>：出现在结果关系中的两个连接关系的元组在公共属性上取值满足谓词条件P，且公共属性出现两次。using(A1,A2,…,An)：(A1,A2,…,An)是两个连接关系的公共属性的子集，元组在(A1,A2,…,An)上取值相等，且(A1,A2,…,An)只出现一次。内连接：舍弃不匹配的元组。左外连接：内连接+左边关系中失配的元组（缺少的右边关系属性值用null表示）。关系的连接（Ⅲ）右外连接：内连接+右边关系中失配的元组（缺少的左边关系属性值用null表示）。全外连接：内连接+左边关系中失配的元组（缺少的右边关系属性值用null表示）+右边关系中失配的元组（缺少的左边关系属性值用null表示）。crossjoin：两个关系的笛卡儿积。unionjoin：左边关系中失配的元组+右边关系中失配的元组。对于外连接，连接条件是必须的； 对于内连接，连接条件是可选的，没有连接条件等价于两个关系的笛卡儿积。关系的连接（Ⅳ）列出老师的教工号、姓名、工资、所教课程号。

selectPNO，PNAME，SAL，CNO from（PROFnatureleftouterjoinPC）

RinnerjoinSonR.C=S.CRS关系的连接（Ⅴ）RleftouterjoinSonR.C=S.CRnaturerightouterjoinS关系的连接（Ⅵ）RfullouterjoinSonR.C=S.CSQL的数据修改功能插入删除修改视图更新插入操作（Ⅰ）命令insertinto

表名[（列名[，列名]…]values（值[，值]…）插入一条指定好值的元组insertinto

表名[（列名[，列名]…]（子查询）插入子查询结果中的若干条元组示例insertinto

PROFvalues(P123,“王明”,35,D08,498)insertinto

PROF

(PNO,PNAME,DNO)

values(P123,“王明”,D08)

思考：SAL取何值？如何防止插入带有空值的元组？插入操作（Ⅱ）将平均成绩大于90的学生加入到EXCELLENT中。

insertintoEXCELLENT(SNO,GRADE)

selectSNO,avg(SCORE)

fromSC

groupby(SNO)

having

avg(SCORE)>90FORBIDDEN(

INFORMIX) insertintoPROF

select*

fromPROF若支持，则完成查询后，再执行修改操作不支持修改在子查询中出现的表删除操作（Ⅰ）命令deletefrom

表名[where

条件表达式]从表中删除符合条件的元组，如果没有where语句，则删除所有元组。示例清除所有选课记录

deletefromSC删除王明老师所有的任课记录。

deletefromPC

wherePNOin(selectPNO

fromPROF

wherePNAME=“王明”)

删除操作（Ⅱ）删除低于平均工资的老师记录。

deletefromPROF

whereSAL<(select

avg(SAL)

fromPROF)

思考：是先找到所有符合条件的元组，一并删除，还是找到一个删除一个？

更新操作（Ⅰ）命令

update表名

set

列名=表达式|子查询 列名=[，表达式|子查询]… [where

条件表达式]指定对哪些列进行更新，以及更新后的值是什么。示例老师工资上调5%。

updatePROF

setSAL=SAL*1.05更新操作（Ⅱ）将D01系系主任的工资改为该系的平均工资

updatePROF

setSAL= (select

avg(SAL)

fromPROF

whereDNO=D01)

wherePNO=(select

DEAN

fromDEPT

whereDNO=D01)更新操作（Ⅲ）工资超过2000的缴纳10%所得税，其余的缴纳5%所得税。 ①updatePROF

setSAL=SAL*0.9

whereSAL>2000

②updatePROF

setSAL=SAL*0.95

whereSAL<=2000

执行顺序是①，②，还是②，①？视图更新（Ⅰ）示例createviewP_SAL

as(selectPNO,PNAME,SAL

fromPROF)insertintoP_SAL

values(P08,