第3章语句优秀PPT.ppt

《第3章语句优秀PPT.ppt》由会员分享，可在线阅读，更多相关《第3章语句优秀PPT.ppt（79页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、第3章语句现在学习的是第1页，共79页3.1 SQL语言概况nSQL简介q结构化查询语言SQL（StructuredQueryLanguage）是一种介于关系代数与关系演算之间的语言，其功能包括查询、操纵、定义和控制四个方面，是一个通用的、功能极强的关系数据库语言。目前已成为关系数据库的标准语言。qSQL语言的版本包括：SQL-89，SQL-92，SQL3。nSQL特点qSQL语言之所以能够为用户和业界所接受，成为国际标准，是因为它是一个综合的、通用的、功能极强同时又简洁易学的语言。qSQL语言集数据查询（dataquery）、数据操纵（datamanipulation）、数据定义（datad

2、efinition）和数据控制（datacontrol）功能于一体，充分体现了关系数据语言的特点和优点。现在学习的是第2页，共79页3.1 SQL语言概况nSQL的基本概念SQL语言支持关系数据库三级模式结构。其中外模式对应于视图（View），模式对应于基本表，内模式对应于存储文件。q基本表是本身独立存在的表，在SQL中一个关系就对应一个表。一些基本表对应一个存储文件，一个表可以带若干索引，索引也存放在存储文件中。q存储文件的逻辑结构组成了关系数据库的内模式。存储文件的物理文件结构是任意的。q视图是从基本表或其他视图中导出的表，它本身不独立存储在数据库中，也就是说数据库中只存放视图的定义而不存

3、放视图对应的数据，这些数据仍存放在导出视图的基本表中，因此视图是一个虚表。用户可以用SQL语言对视图和基本表进行查询。在用户眼中，视图和基本表都是关系，而存储文件对用户是透明的。现在学习的是第3页，共79页3.1 SQL语言概况nSQL的基本功能q（1）数据定义功能n 基本表的建立、取消与更改。n 索引的建立与取消。n 视图的创建与取消。q（2）数据查询功能（包括数据表和视图）q（3）数据更新功能n数据插入、删除、修改功能。q（4）数据控制功能n数据库保护功能（安全性和完整性保护）。n事务管理功能（数据库故障恢复和并发事务处理）。现在学习的是第4页，共79页3.2 SQL数据定义语言n模式（数

4、据库）定义模式（数据库）定义n模式是表示“基本表”、“视图”等的集合，定义SQL模式也就是定义了一个存储空间，在此空间中对象全体构成了对应的SQL数据库n例如,教学数据库的SQL模式定义如下：CREATEDATABASEJIAOXUEn一般格式如下：CREATEDATABASE现在学习的是第5页，共79页3.2 SQL数据定义语言n模式定义模式定义n当一个SQL模式不需要时，可以用DROP语句撤销：nDROPDATABASE例：撤销SQL模式“JIAOXUE”DROPDATABASEJIAOXUE现在学习的是第6页，共79页3.2 SQL数据定义语言n定义基本表n一般格式如下：CREATETA

5、BLE(列级完整性约列级完整性约束条件束条件，列级完整性约束条件列级完整性约束条件.),;其中是所要定义的基本表的名字，它可以由一个或多个属性（列）组成。建表的同时通常还可以定义与该表有关的完整性约束条件，这些完整性约束条件被存入系统的数据字典中，当用户操作表中数据时由DBMS自动检查该操作是否违背这些完整性约束条件。如果完整性约束条件涉及到该表的多个属性列，则必须定义在表级上，否则既可以定义在列级也可以定义在表级。现在学习的是第7页，共79页3.2 SQL数据定义语言定义基本表(续)例1：在数据库JIAOXUE中建立一个“学生”表S，它由学号S、姓名Sn、性别Se、年龄Sa、所在系Sd五个属

6、性组成，其中学号属性不能为空，并且其值是唯一的，姓名属性不能为空，年龄要在1525范围，性别默认为男性，主键为学号。USE JIAOXUECREATE TABLE S(S#CHAR(5)NOT NULL UNIQUE,SN CHAR(20)NOT NULL,SE CHAR(2)DEFAULT M,SA SMALLINT,SD CHAR(20),PRIMARY KEY(S#),CHECK(SA BETWEEN 15 AND 25);现在学习的是第8页，共79页3.2 SQL数据定义语言定义基本表(续)例2：建立一个“选课”表C，它由课程号（C）、课程名（Cn）、先修课号（P）三个属性组成，其中课

7、程号和课程名属性不能为空，主键为课程号。CREATE TABLE C(C#CHAR(4)NOT NULL,CN CHAR(10)NOT NULL,P#CHAR(4),PRIMARY KEY(C#);现在学习的是第9页，共79页3.2 SQL数据定义语言定义基本表(续)例3：建立一个“学生选课”表SC，它由学生号（S），课程号（C）、成绩（G）三个属性组成，其中学生号和课程号属性不能为空，主键为学生号、课程号，学生号是表S的外键，课程号是表C的外建。CREATE TABLE SC(S#CHAR(4)NOT NULL,C#CHAR(4)NOT NULL,G SMALLINT,PRIMARY KEY

8、(S#,C#),FOREIGN KEY(S#)REFERENCES S(S#)ON DELETE CASCADE,FOREIGN KEY(C#)REFERENCES C(C#)ON DELETE NO ACTION);n选用NOACTION选项：表明被基本表所引用的主属性不得删除n选用CASCADE选项：表明若主表中删除被引用的主属性，则基本表中引用该外建的对应行随之被删除。现在学习的是第10页，共79页3.2 SQL数据定义语言定义基本表(续)定义表的各个属性时需要指明其数据类型及长度。不同的数据库系统支持的数据类型不完全相同，例如IBMDB2SQL主要支持以下数据类型：SMALLINT半字

9、长二进制整数。INTEGER或INT全字长二进制整数。DECIMAL(p,q)压缩十进制数，共p位，其中小数点后有q位。0qp15，q=0时可以省略。FLOAT双字长浮点数。CHARTER(n)或CHAR(n)长度为n的定长字符串。VARCHAR(n)最大长度为n的变长字符串。TXET存储数量巨大的变长字符串数据DATE日期型，格式为YYYY-MM-DD。TIME时间型，格式为HH.MM.SS。DATETIME日期加时间。现在学习的是第11页，共79页3.2 SQL数据定义语言基本表更新对基本表结构进行更新，包括增加新属性列、删除原有属性列、修改数据类型、补充定义主键和删除主键等。n（1）增加

10、属性列n一般格式为：ALTERTABLEADD完整性约束条件完整性约束条件n例：在基本表例：在基本表S中添加一个新的地址属性中添加一个新的地址属性ADDRESSALTERTABLESADDADDRESSVARCHAR(50);n基本表在增加一列后，原有元组在新增加的列上的值都定义为空值（NULL），因而新添加属性列时不允许出现NOTNULL。现在学习的是第12页，共79页3.2 SQL数据定义语言（2）删除属性列一般格式为：ALTERTABLEDROPCOLUMNn例：在基本表例：在基本表S删除属性列删除属性列SAALTERTABLESDROPCOLOMNSA现在学习的是第13页，共79页3.

11、2 SQL数据定义语言（3）修改属性列一般格式为：ALTERTABLEALTERCOLUMNn例：在基本表例：在基本表S中将中将ADDRESS的长度修改为的长度修改为40ALTERTABLESALTERCOLUMNADDRESSCHAR(40);现在学习的是第14页，共79页3.2 SQL数据定义语言（4）查看所有约束一般格式为：EXECSP_HELPCONSTRAINTn例：在查看例：在查看S表所有约束表所有约束nEXEC SP_HELPCONSTRAINT Sn约束类型有六种：主键，约束类型有六种：主键，NOTNULL，DEFAULT,CHECK,UNIQUE,外键。外键。现在学习的是第1

12、5页，共79页3.2 SQL数据定义语言（5）补充定义主键一般格式为：ALTERTABLECONSTRAINTADDPRIMARYKEY()定义的主键属性应当是非空和满足唯一性要求定义的主键属性应当是非空和满足唯一性要求n例：全体男生表例：全体男生表Smale，其结构与，其结构与S表相同，补充定义表相同，补充定义Smale的主键的主键ALTERTABLESmalenADDPRIMARYKEY(S#);现在学习的是第16页，共79页3.2 SQL数据定义语言（6）删除主键一般格式为：ALTERTABLEDROPCONSTRAINT例：删除例：删除S表中主键表中主键SALTER TABLE S D

13、ROP CONSTRAINT PK_S_0BC6C43E现在学习的是第17页，共79页3.2 SQL数据定义语言（7）删除其他约束一般格式为：ALTERTABLEDROPCONSTRAINTn例：删除例：删除S表中键表中键SA的年龄约束条件的年龄约束条件ALTERTABLESDROPCONSTRAINTCK_S_SA_0425A276现在学习的是第18页，共79页3.2 SQL数据定义语言（8）增加其它约束条件一般格式为：ALTERTABLEADDCONSTRAINT约束类型约束类型例：在例：在S表中地址表中地址ADDRESS加上唯一约束加上唯一约束ALTER TABLE S ADD CONS

14、TRAINT PK_ADDRESS_UNIQUE UNIQUE(address);例：在例：在S表中地址表中地址ADDRESS加上默认值为广东省广州市加上默认值为广东省广州市ALTER TABLE SADDCONSTRAINT PK_ADDRESS_DEFAUTL DEFAULT 广东省广州市 FOR ADDRESS现在学习的是第19页，共79页3.2 SQL数据定义语言例：在例：在SC表中把表中把C#的外键约束删除后，再添加的外键约束删除后，再添加C#外键约束ALTER TABLE SC ADD CONSTRAINT PK_C#_FOREIGN_UNIQUE FOREIGN KEY(C#)R

15、EFERENCES C(C#)例：在例：在S表中地址表中地址SE加上检查约束，要求加上检查约束，要求SE只能为只能为F或或MALTER TABLE SADD CONSTRAINT PK_SE_CHECKCHECK(SE IN(M,N)现在学习的是第20页，共79页3.2 SQL数据定义语言删除基本表一般格式为：DROPTABLE例:删除SC表DROPTABLESC现在学习的是第21页，共79页3.2 SQL数据定义语言索引是为了快速查找数据用的。索引相当于排序，但与排序不同的是，排序是将原数据重新排列，改变了原数据的排列顺序。而索引只是建立一个顺序表，由这个顺序表指出数据的顺序，所以索引不改变

16、原数据的排列顺序。索引被用来快速找出在一个列上用一特定值的行。没有索引，不得不首先以第一条记录开始并然后读完整个表直到它找出相关的行。表越大，花费时间越多。如果表对于查询的列有一个索引，能快速到达一个位置去搜寻到数据文件的中间，没有必要考虑所有数据。SQL新标准都不主张使用索引，而是在创建基本表时以主键取而代之了。现在学习的是第22页，共79页3.2 SQL数据定义语言建立索引一般格式为：CREATEUNIQUECLUSTER|NONCLUSTERINDEXON(ASC|DESC,ASC|DESC.);其中，指定要建索引的基本表的名字。索引可以建在该表的一列或多列上，各列名之间用逗号分隔。每个

17、后面还可以用指定索引值的排列次序，包括ASC（升序）和DESC（降序）两种，缺省值为ASC。UNIQUE表明此索引的每一个索引值只对应唯一的一个元组现在学习的是第23页，共79页默认主键为簇索引，所以一般建立的都为非簇索引。默认主键为簇索引，所以一般建立的都为非簇索引。现在学习的是第24页，共79页3.2 SQL数据定义语言例为学生-课程数据库中的S、C、SC三个表建立索引。其中S按学号升序建唯一索引，C表按课程号升序建唯一索引，SC表按学号升序和课程号降序建唯一索引。CREATEUNIQUENONCLUSTEREDINDEXSnoONS(S#);CREATEUNIQUENONCLUSTERE

18、DINDEXConoONC(C#);CREATEUNIQUENONCLUSTEREDINDEXSCnoONSC(S#ASC,C#DESC);现在学习的是第25页，共79页3.2 SQL数据定义语言查看索引：EXECSP_HELPINDEXS删除索引：DROPINDEXS.SNO现在学习的是第26页，共79页3.3 SQL数据查询语言SQL数据查询功能是一种基于关系元素的操作形式。查询常用的关系运算是：其中：查询的目标属性r1,r2,rn查询所涉及的关系R1,R2,Rn查询的逻辑条件Fn对应SQL查询语句为：nSELECT子句。表示查询的目标属性，指定做投影运算。nFROM子句。表示查询所涉及的

19、关系，指定多个关系做连接运算。nWHERE子句。表示查询的逻辑条件，指定做选择运算现在学习的是第27页，共79页SELECT语句一般格式为：SELECTALL|DISTINCT,.FROM,WHEREGROUPBYHAVINGORDERBYASC|DESC;整个SELECT语句的含义是n首先，根据WHERE子句的条件表达式n其次，从FROM子句指定的基本表或视图中找出满足条件的元组n最后按照SELECT子句中的目标列表达式，选出元组中的属性值形成结果关系表。值得注意的是：nSELECT子句中输出可以是列名，目标列的表达式或聚集函数(AVG、COUNT、MAX、MIN和SUM），DISTINCT

20、选项用以保证查询的结果中不存在重复的元组。现在学习的是第28页，共79页3.3 SQL数据查询语言(1)（单表查询）(1)不带条件的列查询不带条件的列查询例1:查询S所有列的情况SELECT*FROMS;说明：中各个列的先后顺序可以与表中的顺序不一致。也就是说，用户在查询时可以根据应用的需要改变列的显示顺序，例如：nSELECT Sd,S#,Sn,SanFROM S现在学习的是第29页，共79页例2:查询所有选修了课程的学生的学号 SELECT S#FROM SC;说明：可能有一个学生选择了多门不同的课程，上述查询结果可能重号，可以这样去掉：SELECT DISTINCT S#FROM SC;

21、现在学习的是第30页，共79页3.3 SQL数据查询语言(1)单表查询(续)查询经过计算的值:SELECT子句的不仅可以是表中的属性列，也可以是有关表达式，即可以将查询出来的属性列经过一定的计算后列出结果。n例3:查全体学生的姓名及其出生年份nSELECTSn,2010-SaFROMS;n说明：本例中，中第二项不是通常的列名，而是一个计算表达式，是用当前的年份（假设为2010年）减去学生的年龄，这样，所得的即是学生的出生年份。n不仅可以是算术表达式，还可以是字符串常量、函数等n如果要给新的列起名字：nSELECT Sn,bir=2010-SanFROM S现在学习的是第31页，共79页3.3

22、SQL数据查询语言(1)单表查询(续)(2)查询满足条件的元组查询满足指定条件的元组可以通过WHERE子句实现。WHERE子句常用的查询条件如表3-3所示。表3-3常用的查询条件查询条件谓词-比较上述比较运算符确定范围BETWEENAND,NOTBETWEENAND确定集合IN,NOTIN字符匹配LIKE,NOTLIKE空值ISNULL,ISNOTNULL多重条件NOT,AND,OR现在学习的是第32页，共79页3.3 SQL数据查询语言(1)单表查询(续)(a)比较例4:查计算机系（CS）全体学生姓名与年龄例5:查所有年龄在24岁以下的学生姓名及其年龄nSELECTSn,SaFROMSWHE

23、RESd=CS;SELECT Sn,Sa FROM S WHERE Sa 24;现在学习的是第33页，共79页3.3 SQL数据查询语言(1)单表查询(续)例6:查考试成绩有不及格的学生的学号SELECT DISTINCT S#FROM SC WHERE G60;这里使用了DISTINCT短语，当一个学生有多门课程不及格，他的学号也只列一次。现在学习的是第34页，共79页3.3 SQL数据查询语言(1)单表查询(续)(b)确定范围例7:查询年龄在21至23岁之间的学生的姓名、系别、和年龄例8:查询年龄不在21至23岁之间的学生姓名、系别和年龄。SELECT Sn,Sd,SaFROM S WHE

24、RE Sa BETWEEN 21 AND 23;n与BETWEEN.AND.相对的谓词是NOTBETWEEN.AND.。SELECTSn,Sd,SaFROMSWHERESaNOTBETWEEN21AND23;现在学习的是第35页，共79页3.3 SQL数据查询语言(1)单表查询(续)(c)确定集合例8:查信息系（IS）、数学系（MA）和计算机科学系（CS）的学生的姓名和性别例9:查既不是信息系、数学系，也不是计算机科学系的学生的姓名和性别SELECT Sn,SeFROM SWHERE Sd IN(IS,MA,CS)与IN相对的谓词是NOTIN，用于查找属性值不属于指定集合的元组。SELECT

25、Sn,SeFROM SWHERE Sd NOT IN(IS,MA,CS)现在学习的是第36页，共79页3.3 SQL数据查询语言(1)单表查询(续)(d)字符匹配谓词LIKE可以用来进行字符串的匹配。其一般语法格式如下：NOTLIKEESCAPE例10:查询姓名以马开头，且第3个字为花的学生的姓名与系别。SELECT Sn,SdFROM S WHERE Sn LIKE 马_花%;其含义是查找指定的属性列值与相匹配的元组。可以是一个完整的字符串，也可以含有通配符%和_。%(百分号)代表任意长度（长度可以为0）的字符串，如a%b表示以a开头，以b结尾的任意长度的字符串。_(下横线)代表任意单个字符

26、，如a_b表示以a开头，以b结尾的长度为3的任意字符串。现在学习的是第37页，共79页3.3 SQL数据查询语言(1)单表查询(续)例11:查所不姓刘的学生姓名SELECT SnFROM S WHERE Sn NOT LIKE 刘;现在学习的是第38页，共79页3.3 SQL数据查询语言(1)单表查询(续)如果用户要查询的匹配字符串本身就含有%或_，比如要查名字DB_Design的课程的学分，应如何实现呢？这时就要使用ESCAPE短语对通配符进行转义了。例12:查DB_Design课程的课程号SELECT C#FROM C WHERE Cn LIKE DB_Design ESCAPE;n说明：

27、ESCAPE短语表示为换码字符，这样匹配串中紧跟在后面的字符”_”不再具有通配符的含义，而是取其本身含义，被转义为普通的”_”字符。现在学习的是第39页，共79页3.3 SQL数据查询语言(1)单表查询(续)(e)涉及空值的查询例14:某些学生选修某门课程后没有参加考试，所以有选课记录，但没有考试成绩，下面我们来查一下缺少成绩的学生的学号和相应的课程号例15:查所有有成绩的记录的学生学号和课程号SELECT S#,C#FROM SCWHERE G IS NULL;注意这里的IS不能用等号（=）代替SELECT S#,C#FROM SCWHERE G IS NOT NULL;现在学习的是第40页

28、，共79页3.3 SQL数据查询语言(1)单表查询(续)(f)多重条件查询逻辑运算符AND和OR可用来联结多个查询条件。如果这两个运算符同时出现在同一个WHERE条件子句中，则AND的优先级高于OR，但用户可以用括号改变优先级。n例16:查CS系年龄在23岁以下的学生姓名SELECT Sn FROM S WHERE Sd=CS AND Sa23;n例8中的IN谓词实际上是多个OR运算符的缩写，因此例12中的查询也可以用OR运算符写成如下等价形式：SELECT Sn,SeFROM SWHERE Sd=IS OR Sd=MA OR Sd=CS;现在学习的是第41页，共79页3.3 SQL数据查询语

29、言(1)单表查询(续)(3).对查询结果排序n如果没有指定查询结果的显示顺序，DBMS将按其最方便的顺序（通常是元组在表中的先后顺序）输出查询结果。用户也可以用ORDERBY子句指定按照一个或多个属性列的升序（ASC）或降序（DESC）重新排列查询结果，其中升序ASC为缺省值。例17:查询选修了003号课程的学生的学号及其成绩，查询结果按分数的降序排列SELECT S#,GFROM SCWHERE C#=003 ORDER BY G DESC;n用ORDERBY子句对查询结果按成绩排序时，若按升序排，成绩为空值的元组将最后显示，若按降序排，成绩为空值的元组将最先显示。默认为升序(ORDERBY

30、G)。现在学习的是第42页，共79页3.3 SQL数据查询语言(1)单表查询(续)例18:查询全体学生情况，查询结果按所在系升序排列，对同一系中的学生按年龄降序排列SELECT*FROM S ORDER BY Sd,Sa DESC;现在学习的是第43页，共79页3.3 SQL数据查询语言（2）多表查询连接查询n一个数据库中的多个表之间一般都存在某种内在联系，它们共同提供有用的信息。前面的查询都是针对一个表进行的。若一个查询同时涉及两个以上的表，则称之为连接查询。连接查询主要包括等值连接、非等值连接查询、自身连接查询（连接二、自身连接）、外连接查询（连接三、外连接）和复合条件连接查询（连接四、复

31、合条件连接）。（1）等值与非等值连接查询n用来连接两个表的条件称为连接条件或连接谓词，其一般格式为：.其中比较运算符主要有：=、=、=、!=、!=现在学习的是第44页，共79页3.3 SQL数据查询语言（2）多表查询例19：查询学习课程号为001课程的所有学生学号和姓名 SELECT S.S#,SnFROM S,SC WHERE S.S#=SC.S#AND C#=001这个语句执行时，先对FROM后的基本表S和SC做笛卡儿乘积操作，然后进行等值连接（S.S#=SC.S#)、选择（C#001）等操作。例20：查询修读课程名为JAVA的所有学生的姓名。SELECT SnFROM S,SC,CWHE

32、RE S.S#=SC.S#AND SC.C#=C.C#AND C.Cn=JAVA现在学习的是第45页，共79页3.3 SQL数据查询语言（2）多表查询(2)自连接n连接操作不仅可以在两个表之间进行，也可以是一个表与其自己进行连接，这种连接称为表的自连接。例21:查询至少修读学号00005所修读的一门课的学生学号SELECT SC1.S#FROM SC AS SC1,SC AS SC2 WHERE SC1.C#=SC2.C#AND SC2.S#=00008;例22:查询至少选修了两名课程的学生学号SELECT DISTINCT SC1.S#FROM SC SC1,SC SC2 WHERE SC1

33、.S#=SC2.S#AND SC1.C#!=SC2.C#;现在学习的是第46页，共79页3.3 SQL数据查询语言（2）多表查询(3)外连接n在通常的连接操作中，只有满足连接条件的元组才能作为结果输出，如在例19中，如果学号为00005和00006的同学没有选修课程001，由于在表SC中没有相应元组，查询结果中就不会出现他们学号。但是某些情况下，需要以S表为主体，列出每个学生的基本情况和选课情况，如某个学生没有选课，则只输出其基本情况信息，其选课信息为空值即可，这时就需要使用外连接（OuterJoin）。例23：查询所有学生的基本情况和选课情况。SELECT S.S#,Sn,Sa,Sd,C#,

34、GFROM S LEFT OUTER JOIN SC ON S.S#=SC.S#说明：外连接可以看作是为连接中某个表（例如例23中SC）添加一个通用的行，这个行全部由空值组成，他能够和另一个表（例如例23中S）中所有不满足条件的元组进行连接。现在学习的是第47页，共79页3.3 SQL数据查询语言（2）多表查询nLEFTJOIN或LEFTOUTERJOIN左向外联接的结果集包括LEFTOUTER子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。语句格式:FROM表名1LEFTJOIN表名2ON表名1.字段

35、A关系运算符表名2.字段BnRIGHTJOIN或RIGHTOUTERJOIN。右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。nFULLJOIN或FULLOUTERJOIN。完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。现在学习的是第48页，共79页3.3 SQL数据查询语言（2）多表查询nINNERJION内连接，又叫等值连接，只返回两个表中连接字段相等的行SELECT*FROM SINNER JOIN SC ON S.S#=

36、SC.S#n等同于以下SQL句:SELECT*FROMS,SCWHERES.S#=SC.S#现在学习的是第49页，共79页3.3 SQL数据查询语言（3）嵌套查询n嵌套查询概述q一个SELECT-FROM-WHERE语句称为一个查询块q将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询现在学习的是第50页，共79页3.3 SQL数据查询语言（3）嵌套查询（1）元素x与集合S之间的关系主要表现为：（2）集合S1和集合S2之间的关系主要表现为：qS1与S2之间包含或相等qS1与S2之间的代数运算关系q基本原理：基本特征是一个查询语句中WHERE子句的逻辑条件

37、含有另一个查询语句。但查询语句的结果是一张表，表就是元组的集合，因而可以将WHERE子句内的查询语句看作是一个集合。在这种观点之下，WHERE子句所涉及到的逻辑条件就可以转化为“元素x与集合S”或者“集合S1与集合S2”之间的关系表示。现在学习的是第52页，共79页一、带有IN谓词的子查询例24查询修读课程号为001的所有学生的姓名。SELECT S.SnFROM SWHERE S.S#IN (SELECT SC.S#FROM SC WHERE SC.C#=001)例：查询不修读课程号为001的所有学生的姓名。SELECT S.SnFROM SWHERE S.S#NOT IN (SELECT

38、SC.S#FROM SC WHERE SC.C#=001)现在学习的是第53页，共79页带有IN谓词的子查询（续）例25查询修读课程名为JAVA的所有学生的姓名。SELECT SnFROM SWHERE S.S#IN (SELECT SC.S#FROM SC WHERE SC.C#IN (SELECT C.C#FROM C WHERE C.Cn=JAVA);现在学习的是第54页，共79页二、带有EXISTS谓词的子查询1.EXISTS谓词存在量词格式：exists(subsquery)带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。若内层查询结果

39、非空，则返回真值若内层查询结果为空，则返回假值由EXISTS引出的子查询，其目标列表达式通常都用*，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义2.NOT EXISTS谓词现在学习的是第55页，共79页带有EXISTS谓词的子查询(续）例26 查询所有选修001课程的学生姓名。SELECT Sn FROM S WHERE EXISTS (SELECT*FROM SC /*相关子查询*/WHERE SC.S#=S.S#AND SC.C#=001)；查询过程如下：（1）取外层查询中表S的第一个元组，根据它与内层查询相关的属性值（S#)处理内层，如果WHERE子句返回值为真，就取此

40、元组放入结果表。（2）然后再取表S的下一个元组，重复这一过程，直到外层S表全部检查完毕为止。现在学习的是第56页，共79页带有EXISTS谓词的子查询(续）例27 查询没有选修001号课程的学生姓名。SELECT Sn FROM S WHERE NOT EXISTS (SELECT*FROM SC WHERE S.S#=SC.S#AND C#=001)现在学习的是第57页，共79页带有EXISTS谓词的子查询(续）3.用EXISTS/NOT EXISTS实现全称量词(难点)SQL语言中没有全称量词（For all）对应关系代数中的除法可以把带有全称量词的谓词转换为等价的带有存在量词的谓词：(x

41、)P (x(P)现在学习的是第58页，共79页带有EXISTS谓词的子查询(续）例28 查询选修了全部课程的学生姓名。SELECT Sn FROM S WHERE NOT EXISTS (SELECT*FROM C WHERE NOT EXISTS (SELECT*FROM SC WHERE SC.S#=S.S#AND SC.C#=C.C#)现在学习的是第59页，共79页带有EXISTS谓词的子查询(续）例29 查询所学课程包含学号为00003的学生所学课程的所有学号。解题思路：n用逻辑蕴函表达：查询学号为x的学生，对所有的课程y，只要S3学生选修了课程y，则x也选修了y。n形式化表示：用P表

42、示谓词“学生00003选修了课程y”用q表示谓词“学生x选修了课程y”则上述查询为:(y)p q 现在学习的是第60页，共79页带有EXISTS谓词的子查询(续）等价变换：(y)pq(y(pq)(y(pq)y(pq)变换后语义：不存在这样的课程y，学生00003选修了y，而学生x没有选。现在学习的是第61页，共79页带有EXISTS谓词的子查询(续）例29 查询所学课程包含学号为00003的学生所学课程的所有学号。SELECT DISTINCT S#FROM SC AS XWHERE NOT EXISTS (SELECT*FROM SC AS Y WHERE Y.S#=00003 AND NO

43、T EXISTS (SELECT*FROM SC AS Z WHERE Z.S#=X.S#AND Z.C#=Y.C#);现在学习的是第62页，共79页三、带有ANY或ALL谓词的子查询谓词语义qSOME/ANY：任意一个值qALL：所有值现在学习的是第63页，共79页带有ANY或ALL谓词的子查询（续）例：查询其他系中比信息系任意一个学生年龄小的学生姓名和年龄SELECT Sn,SaFROM SWHERE SaANY(SELECT Sa FROM S WHERE Sd=IS)AND Sd IS;现在学习的是第64页，共79页带有ANY或ALL谓词的子查询（续）例：查询其他系中比信息系所有学生年

44、龄都小的学生姓名及年龄。SELECT Sn,SaFROM SWHERE SaALL(SELECT Sa FROM S WHERE Sd=IS)AND Sd IS;现在学习的是第65页，共79页3.3.4 查询中函数与表达式一、使用集函数n为了进一步方便用户，增强检索功能，SQL提供了许多集函数，主要包括：n如果指定DISTINCT短语，则表示在计算时先要取消指定列中的重复值。如果不指定DISTINCT短语或指定ALL短语（ALL为缺省值），则表示不取消重复值。现在学习的是第66页，共79页3.3.4 查询中函数与表达式（续）例33:查询学生总人数SELECT COUNT(*)FROM S;例3

45、4:查询选修了课程的学生人数nSELECTCOUNT(DISTINCTS#)FROMSC;n说明：学生每选修一门课，在SC中都有一条相应的记录，而一个学生一般都要选修多门课程，为避免重复计算学生人数，必须在COUNT函数中用DISTINCT短语。现在学习的是第67页，共79页3.3.4 查询中函数与表达式（续）例35:给出学生00001修读的课程数SELECT COUNT(*)FROM SCWHERE S#=00001;例36:给出学生00001所修读课程的平均成绩SELECT AVG(G)FROM SC WHERE S#=00001;现在学习的是第68页，共79页3.3.4 查询中函数与表达

46、式（续）例37:查询选修001课程的学生最高分数SELECT MAX(G)FROM SC WHERE C#=001;nFROM注意：集合函数只能用在注意：集合函数只能用在SELECT子句和子句和HAVING子句子句中中nSELECTS#FROMSCWHERESC.G=MAX(SC.G);语法错误，应写为：nSELECTS#FROMSCWHERESC.G=(SELECTMAX(SC.G)FROMSC);现在学习的是第69页，共79页n如何处理nullqnull是一个常量，仅在数值和字符串类型的列中有意义，代表的是没有意义或者是不确定的值。例如，学生选了课程，当成绩没有出来时G字段的值应该为空；或

47、者工资表中一个行政人员在课时补贴一栏的值为null，因为它不可能有课时补贴的收入3.3.4 查询中函数与表达式（续）现在学习的是第70页，共79页q例一select*fromscwhereG=60(不会选取所有记录，那些G的空值不会选取出来）q例二：有两个关系R,S如下图select*fromR,SwhereR.B=S.D的结果如下图3.3.4 查询中函数与表达式（续）现在学习的是第71页，共79页对于集合运算COUNT(列名)NULL值不会计算进去SUM 不影响AVG=SUM/COUNT 所以NULL值不会计算进去MAX NULL值不考虑进去 MIN NULL值不考虑进去Examplesel

48、ect count(*)from customers;5/6select count(cid)from customers;/*null values not counted*/5/6select count(distinct city)from customers;3/3现在学习的是第72页，共79页3.3.4 查询中函数与表达式（续）二、四则表达式与纯量函数nSQL查询输出结果中可以有基本算术运算表达式，也可以是纯量函数。常用的纯量函数主要有:函数名用途函数名用途INTEGER取整SQRT平方根SIN、COS正弦、余弦SUBSTRING字符串UPPER大写字符MONTHS_BETWEEN日

49、期现在学习的是第73页，共79页3.3.4 查询中函数与表达式（续）例38:给出修读课程为001的所有学生的学生分级（即学分数*3）SELECT S#,C#,G*3FROM SCWHERE C#=001 例39:给出计算机系下个年度学生的年龄SELECT Sn,Sa+1FROM SWHERE Sd=CS;现在学习的是第74页，共79页3.3.4 查询中函数与表达式（续）例40:给定一个关系表T(A,B)，表中属性值均为整数类型，则SELECTA,B,A*B,SQRT(B)FROMT的查询结果为:A、B以及A、B的乘积与B的平方根现在学习的是第75页，共79页3.3.4 查询中函数与表达式（续）

50、三、分组与筛选表达式nGROUPBY子句可以将查询结果表的各行按一列或多列取值相等的原则进行分组。对查询结果分组的目的是为了细化集函数的作用对象。如果未对查询结果分组，集函数将作用于整个查询结果，即整个查询结果只有一个函数值。否则，集函数将作用于每一个组，即每一组都有一个函数值。例41：给出每个学生的平均成绩。SELECTS#,AVG(G)FROMSCGROUPBYS#语句执行步骤为，先将课程按照相同的S#进行分组，再将每组中的成绩G作用与聚集函数AVG，从而得到所求的每个S#的平均分数。现在学习的是第76页，共79页3.3.4 查询中函数与表达式（续）例42:给出每个学生修读课程的门数nSE