关系数据库标准语言-SQL课件

第3章

关系数据库标准语言－SQL9/20/20231周口师范学院计算机科学系第3章关系数据库标准语言－SQL8/6/20231周口师在数据库系统中，数据查询是最常用的操作。SQL是目前使用最广泛的数据库语言。利用SQL可以通过功能强大而又简捷的查询语言与数据库打交道，从数据库中得到想要的数据。SQL语句主要被用于进行数据库的查询、定义、操纵和控制几个方面，是一种功能齐全的数据库语言。SQL的数据定义是指对关系模式一级的定义。数据操纵是指对关系中的具体数据进行增、删、改等更新操作。数据控制是指对数据访问权限的授予与取消。9/20/20232周口师范学院计算机科学系在数据库系统中，数据查询是最常用的操作。SQL是目前使用最广3.1SQL语言基本知识3.1.1SQL的发展史1974年IBM圣约瑟实验室的Boyce和Chamberlin为关系数据库管理系统System-R设计的一种查询语言，当时称为SEQUEL语言（StructuredEnglishQueryLanguage），后简称为SQL。1981年IBM推出关系数据库系统SQL/DS后，SQL得到了广泛应用。1986年美国国家标准协会（ANSI）公布了第一个SQL标准－SQL86。1987年，ISO通过SQL86标准。9/20/20233周口师范学院计算机科学系3.1SQL语言基本知识8/6/20233周口师范学院1989年，ISO制定SQL89标准，SQL89标准在SQL86基础上增补了完整性描述。1990年，我国制定等同SQL89的国家标准。1992年，ISO制定SQL92标准，即SQL2。1999年，ANSI制定SQL3标准。SQL成为国际标准后，对数据库以外的领域产生了很大的影响，有不少软件产品将SQL语言的数据查询功能与图形功能、软件工程工具、软件开发工具、人工智能程序结合起来。SQL已成为数据库领域中一个主流语言。9/20/20234周口师范学院计算机科学系1989年，ISO制定SQL89标准，SQL89标准在SQL在许多软件产品中，软件厂商都对SQL的基本命令集进行了扩充，将其扩展成嵌入式SQL语言。SQLServer2000中使用Transact-SQL语言与数据库服务器打交道。

9/20/20235周口师范学院计算机科学系在许多软件产品中，软件厂商都对SQL的基本命令集进行了扩充，3.1.2SQL的特点SQL具有如下特点：1．综合统一。2．高度非过程化。3．面向集合的操作方式。4．以同一种语法结构提供两种使用方式5.语言简捷、易学易用。

SQL语言功能极强，但由于设计巧妙，语言十分简捷，完成核心功能只用了9个动词，如下表示，SQL语言接近英语口语，因此容易学习，容易使用。9/20/20236周口师范学院计算机科学系3.1.2SQL的特点8/6/20236周口师范学院计SQL的功能动词数据查询SELECT数据定义CREATE,DROP,ALTER数据操纵INSERT,UPDATE,DELETE数据控制GRANT,REVOKESQL语言的动词9/20/20237周口师范学院计算机科学系SQL的功能动词数据查询SELECT数据定义CREATE,SQL语言支持关系数据库三级模式结构，如下图示。其中外模式对应于视图和部分基本表，模式对应于基本表，内模式对应于存储文件。基本表是本身独立存在的表，在SQL中一个关系就对应一个表。一个（或多个）基本表对应一个存储文件。一个表可以带若干个索引，索引也存放在存储文件中。存储文件的逻辑结构组成了关系数据库的内模式，存储文件的物理结构是任意的，对用户是透明的。视图是从一个或几个基本表导出的表。它本身不独立存储在数据库中，是一个虚表。即数据库中只存放视图的定义而不存放视图对应的数据。9/20/20238周口师范学院计算机科学系SQL语言支持关系数据库三级模式结构，如下图示。其中外模式对外模式内模式模式9/20/20239周口师范学院计算机科学系外模式内模式模式8/6/20239周口师范学院计算机科学系3.1.3SQL的分类1、数据定义语言（DDL：DataDefinitionLanguage）创建、修改或删除数据库中各种对象，包括表、视图、索引等。2、查询语言（QL：QueryLanguage）按照指定的组合、条件表达式或排序检索已存在的数据库中的数据，但并不改变数据库中数据。3、数据操纵语言（DML：DataManipulationLanguage）对已经存在的数据库进行记录的插入、删除、修改等操作9/20/202310周口师范学院计算机科学系3.1.3SQL的分类8/6/202310周口师范学院4、数据控制语言（DCL：DataControlLanguage）用来授予或收回访问数据库的某种特权、控制数据操纵事务的发生时间及效果、对数据库进行监视注意：在书写各种SQL命令时，命令中所涉及的标点符号，如括号、逗号、分号、圆点（英文句号）等都应是英文半角，如果写成中文全角符号，则会在执行命令时出错。

9/20/202311周口师范学院计算机科学系4、数据控制语言（DCL：DataControlLang3.2数据定义数据定义命令用于建立数据库和建立、修改、删除基本表。表CREATETABLEDROPTABLEALTERTABLE视图CREATEVIEWDROPVIEW索引CREATEINDEXDROPINDEX操作对象操作方式创建删除修改SQL的数据定义语句9/20/202312周口师范学院计算机科学系3.2数据定义表CREATETABLEDROPTA3.2.1定义、删除与修改基本表一、定义基本表建立数据库最重要的一步就是定义一些基本表。SQL语言使用CREATETABLE语句定义基本表，其一般格式如下：CREATETABLE<表名>（<列名><数据类型>[列级完整性约束条件][<列名><数据类型>[列级完整性约束条件]]…[表级完整性约束条件]）;其中<表名>是所要定义的基本表的名字，它可以由一个或多个属性（列）组成。9/20/202313周口师范学院计算机科学系3.2.1定义、删除与修改基本表8/6/202313周建表的同时通常还可以定义与该表有关的完整性约束条件。这些完整性约束条件被存入系统的数据字典中，当用户操作表中数据时由DBMS自动检查该操作是否违背这些完整性约束条件。如果完整性约束条件涉及到该表的多个属性列，则必须定义在表级上，否则既可以定义在列级上也可以定义在表级上9/20/202314周口师范学院计算机科学系建表的同时通常还可以定义与该表有关的完整性约束条件。这些完整例1建立一个“学生”表Student,它由学号Sno,姓名Sname,性别Ssex,年龄Sdept、所在系Sdept五个属性组成。其中学号不能为空，值是唯一的，并且姓名取值也唯一。CREATETABLEStudent(SnoCHAR(5)NOTNULLUNIQUE,SnameCHAR(20)UNIQUE,SsexCHAR(1),SageINT,SdeptCHAR(15));其中：NOTNULL指的是该列的值不能为空值,UNIQUE指的是该列的值唯一。9/20/202315周口师范学院计算机科学系例1建立一个“学生”表Student,它由学号Sno,姓名SnoSnameSsexSageSdeptStudent9/20/202316周口师范学院计算机科学系SnoSnameSsexSageSdeptStudent8/例2：建立如下表所示学生基本情况表的命令是：createtablejbqk(numberchar(8)notnull,namechar(8)notnull,sexchar(2)notnull,Birthdaydatetime,departmentchar(12));numbernamesexBirthdaydepartmentjbqk9/20/202317周口师范学院计算机科学系例2：建立如下表所示学生基本情况表的命令是：numberna数据类型说明符备注整数INT字长32位短整数SMALLINT字长16位十进制数DEC(m,n)m为总十进制位数,n为小数位数浮点数FLOAT一般指双精度浮点数64位定长字符串CHAR(n)变长字符串VARCHAR(n)位串BIT(n)二进制位串,长度为n.变长位串BITARYINT(n)日期DATE日期加时间DATESTAMP9/20/202318周口师范学院计算机科学系数据类型说明符备注整数INT字长32位短整数SMALLINT建立课程表的命令是：createtablecourse(c_numberchar(4)notnull,c_namechar(20)notnull,perioddecimal(3,0),t_numberchar(4)notnull);建立教师表（teacher）的命令是:：createtableteacher(t_numberchar(4)notnull,t_namechar(8),titlechar(10));建立选课表（sle_course）的命令是:：Createtablesle_course(s_numberchar(8)notnull,c_numberchar(4),scoredecimal(3,0));9/20/202319周口师范学院计算机科学系建立课程表的命令是：8/6/202319周口师范学院计算机科二、修改基本表随着应用环境和应用需求的变化，有时需要修改已建立好的基本表，SQL语言使用ALTERTABLE语句定义基本表，其一般格式如下：ALTERTABLE<表名>［ADD<新列名><数据类型>[列级完整性约束条件]］[DROP<完整性约束名>][MODIFY<列名><数据类型>];9/20/202320周口师范学院计算机科学系二、修改基本表8/6/202320周口师范学院计算机科学系例1：向Student表中增加“入学时间”列，其数据类型为日期型。ALTERTABLEStudentADDScomeDATE;不论基本表中原来是否已有数据，新增加的列一律为空值。例2：将Student表中年龄的数据类型改为短整型ALTERTABLEStudentMODIFYSageSMALLINT;9/20/202321周口师范学院计算机科学系例1：向Student表中增加“入学时间”列，其数据类型为日例3将Student表中学生姓名必须取唯一值的约束删除ALTERTABLEStudentDROPUNIQUE(Sname);SQL没有提供删除属性列的语句，用户只能间接实现这一功能，即先把表中要保留的列及其内容复制到一个新表中，然后删除原表，再将新表重命名为原表名。

9/20/202322周口师范学院计算机科学系例3将Student表中学生姓名必须取唯一值的约束删除三、

数据表的删除DROPTABLE<表名>;例4：删除Student表，使用如下命令格式：DROPTABLEStudent;注意：

基本表定义一旦删除，表中的数据、此表上建立的索引和视图都将自动被删除掉。因此执行删除基本表的操作一定要格外小心。9/20/202323周口师范学院计算机科学系三、数据表的删除8/6/202323周口师范学院计算机3.2.2索引的建立与删除索引是对数据库表中一个或多个列的值进行排序的结构。可以利用索引快速访问数据库表中的特定信息。一、建立索引语句CREATE[UNIQUE][CLUSTER]INDEX<索引名>ON<基本表名>(列名[次序][,列名[次序]]…);说明：索引可以建立在一列和多列之上，索引顺序可以是ASC（升序）或DESC（降序），缺省值是ASC(升序)。UNIQUE表示每一个索引值对应唯一的数据记录。9/20/202324周口师范学院计算机科学系3.2.2索引的建立与删除8/6/202324周口师范UNIQUE表明此索引的每一个索引值只对应唯一的数据记录。CLUSTER表示要建立的索引是聚簇索引。所谓聚簇索引是指索引项的顺序与表中记录的物理顺序一致的索引组织。例如，执行下面的索引语句：CREATECLUSTERINDEXStusnameONStudent(Sname);将会在Student表的Sname列上建立一个聚簇索引，而且Student表中的记录将按照Sname值的升序存放。9/20/202325周口师范学院计算机科学系UNIQUE表明此索引的每一个索引值只对应唯一的数据记录。8用户可以在最常查询的列上建立聚簇索引以提高查询效率。显然在一个基本表上最多只能建立一个聚簇索引。建立聚簇索引后，更新索引列数据时，往往导致表中记录的物理顺序的变更，代价较大，因此对于经常更新的列不宜建立聚簇索引。9/20/202326周口师范学院计算机科学系用户可以在最常查询的列上建立聚簇索引以提高查询效率。显然在一例1：为学生数据库中的Student,Course,SC三个表建立索引，其中，Student表按学号升序建唯一索引，Course表按课程号升序建唯一索引，SC表按学号升序和课程号降序建唯一索引。CREATEUNIQUEINDEXStusnoONStudent(Sno);CREATEUNIQUEINDEXCoucnoONCourse(Cno);CREATEUNIQUEINDEXSCnoONSC(SnoASC,CnoDESC);9/20/202327周口师范学院计算机科学系例1：为学生数据库中的Student,Course,SC三个学号Sno姓名Sname性别Ssex年龄Sage系别Sdept95001李勇男20CS95002刘晨女19IS95003王敏女18MA95004张立男19ISStudent（a）设有一个学生-课程数据库，包括学生关系Student课程关系Course和选修关系SC。如下图示689/20/202328周口师范学院计算机科学系学号姓名性别年龄系别95001李勇男20CS95002刘晨女课程号Cno课程名Cname先行课Cpno学分Ccredit1数据库542数学23信息系统144操作系统635数据结构746数据处理27PASCAL语言64Course（b）9/20/202329周口师范学院计算机科学系课程号课程名先行课学分1数据库542数学23信息系统144操学号Sno课程号Cno成绩Grade9500119295001285950013889500229095002380(c)SC9/20/202330周口师范学院计算机科学系学号课程号成绩95001192950012859500138二、删除索引索引一经建立，就由系统使用和维护它，不需用户干预。建立索引是为了减少查询操作时间，但如果数据增、删、改频繁，系统会花费许多时间来维护索引，这时，可以删除一些不必要的索引DROPINDEX<索引名>;注意：该命令不能删除由CREATETABLE或者ALTERTABLE命令创建的主键和唯一性约束索引，也不能删除系统表中的索引。例2、删除例1中建立的索引StusnoDROPINDEXStusno;9/20/202331周口师范学院计算机科学系二、删除索引8/6/202331周口师范学院计算机科学系3.3数据查询语言数据库查询是数据库的核心操作。SQL语言提供了SELECT语句进行数据库的查询，该语句具有灵活的使用方式和丰富的功能。9/20/202332周口师范学院计算机科学系3.3数据查询语言数据库查询是数据库的核心操作。SQLSQL语言的查询语句一般格式是：SELECT[ALL|DISTINCT]<目标列表达式>[,<目标列表达式>]…FROM<基本表名或视图名>[,<基本表名或视图名>]…[WHERE<条件表达式>][GROUPBY<列名1>[HAVING<条件表达式>]][ORDERBY<列名2>[ASC|DESC]];下面对该命令进行一些说明：9/20/202333周口师范学院计算机科学系SQL语言的查询语句一般格式是：8/6/202333周口师范1．命令含义从FROM子句指定的基本表或视图中，根据WHERE子句的条件表达式查找出满足该条件的元组（记录），再按照SELECT子句指定的目标列表达式，选出元组中的属性值形成结果表。如果有GROUPBY子句，则将结果按“列名1”的值进行分组，该属性列值相等的元组为一个组；如果GROUPBY子句带有短语HAVING，则只有满足短语指定条件的分组才会输出。如果有ORDERBY子句，则结果表要按照<列名2>的值进行升序和降序排列。9/20/202334周口师范学院计算机科学系1．命令含义8/6/202334周口师范学院计算机科学系SELECT[ALL|DISTINCT]<目标列表达式>实现的是对表的投影操作，WHERE<条件表达式>中实现的是选择操作。FROM<基本表名或视图名>[,<基本表名或视图名>]…实现的是对指定表的广义笛卡尔积

9/20/202335周口师范学院计算机科学系SELECT[ALL|DISTINCT]<目标列表达式>实2．目标列表达式①列表达式可以是“列名1,列名2…”的形式；如果FROM子句指定了多个表，则列名应是“表名.列名”的形式。②列表达式可以使用SQL提供的库函数形成表达式，常用的函数如下：COUNT(*)：统计记录条数。COUNT(列名)：统计一列值得个数。SUM(列名):计算某一数值型列的值的总和。AVG(列名):计算某一数值型列的值的平均值MAX(列名):计算某一数值型列的值的最大值③DISTINCT参数：表示在结果集中，查询出的内容相同的记录只留下一条。

MIN(列名):计算某一数值型列的值的最小值9/20/202336周口师范学院计算机科学系2．目标列表达式8/6/202336周口师范学院计算机科学系3.3.1单表查询单表查询是指仅涉及一个表的查询。一、选择表中的若干列选择表中的全部列和部分列，就是投影运算。1、查询指定列在很多情况下，用户只对表中的一部分属性列感兴趣，这时可以通过在SELECT子句的<目标列表达式>中指定要查询的属性。9/20/202337周口师范学院计算机科学系3.3.1单表查询8/6/202337周口师范学院计算机例1：查询全体学生的学号和姓名。SELECTSno,SnameFROMStudent;例2：查询全体学生的姓名、学号、所在系。SELECTSname，Sno,SdeptFROMStudent;9/20/202338周口师范学院计算机科学系例1：查询全体学生的学号和姓名。8/6/202338周口师范2、查询全部列将表中所有属性列都选出来，可以有两种方法。一种方法是在SELECT关键字后列出所有的列名。如果列的显示顺序与其在基表中的顺序相同，也可以简单地将<目标列表达式>指定为*。例3：查询全体学生的详细记录。SELECT*SELECTSno,Sname，…FROMStudentFROMStudent9/20/202339周口师范学院计算机科学系2、查询全部列8/6/202339周口师范学院计算机科学系3、查询经过计算的值SELECT子句的<目标列表达式>不仅可以是表中的属性列，也可以是表达式。例4：查全体学生的姓名及其出生年份SELECTSname，2004-SageFROMStudent;输出的结果为：<目标列表达式>不仅可以是算术表达式，还可以是字符串常量、函数等。9/20/202340周口师范学院计算机科学系3、查询经过计算的值8/6/202340周口师范学院计算机科例5：查询全体学生的姓名，出生年份和所在系，要求用小写字母表示所在系名。SELECTSname，‘YearofBirth’,2004-Sage,ISLOWER(Sdept)FROMStudent;输出结果为：用户可以通过指定别名来改变查询结果的列标题。这对于含算术表达式、常量、函数名的目标列表达式尤为有用。例如，对上例，可定义如下：9/20/202341周口师范学院计算机科学系例5：查询全体学生的姓名，出生年份和所在系，要求用小写字母表SELECTSnameNAME，‘YearofBirth;’BIRTH,2004-SageBIRTHDAY,ISLOWER(Sdept)DEPARTMENTFROMStudent;结果为：NAMEBIRTHBIRTHDAYDEPARTMENT李勇YearofBirth：1976cs刘晨YearofBirth：1977is王敏YearofBirth：1978ma(c)9/20/202342周口师范学院计算机科学系SELECTSnameNAME，‘YearofBirSnameYearofBirth：2004-SageISLOWER(Sdept)李勇YearofBirth：1976cs刘晨YearofBirth：1977is王敏YearofBirth：1978maSname2004-Sage李勇1976刘晨1977王敏1978(a)(b)9/20/202343周口师范学院计算机科学系SnameYearofBirth：2004-SageIS二、选择表中的若干元组1、消除取值重复的行两个本来并不完全相同的元组，投影到指定的某些列上后，可能变成相同的行。如果想去掉表中的重复行，必须在SELECT子句中指定DISTINCET短语。如果没有指定DISTINCET短语，则缺省为ALL，即保留结果表中取值重复的行。例6：查询选修了课程的学生学号。SELECTSnoFROMSC;9/20/202344周口师范学院计算机科学系二、选择表中的若干元组8/6/202344周口师范学院计算机Sno9500195001950019500295002(a)该查询结果里包含了许多重复行如果想去掉表中的重复行，则：SELECTDISTINCTSnoFROMSC；执行结果为：Sno9500195002(b)9/20/202345周口师范学院计算机科学系Sno9500195001950019500295002(a2、查询满足条件的元组查询满足条件的元组可以通过WHERE子句来实现，WHERE子句常用的查询条件如下表示：9/20/202346周口师范学院计算机科学系2、查询满足条件的元组8/6/202346周口师范学院计算机查询条件运

算

符说

明比

较=，>，<，>=，<=，<>，!=，(!<,!>)NOT+字符串比较从左向右进行确定范围BETWEENAND，NOTBETWEENANDBETWEEN后是下限(低)，AND后是上限确定集合IN，NOTIN检查一个某属性值是否属于指定集合的元组字符匹配LIKE，NOTLIKE用于构造条件表达式中的字符匹配空

值ISNULL，ISNOTNULL当属性值内容为空时，要用此运算符逻辑运算AND，OR，NOT用于构造复合表达式常用的查询条件9/20/202347周口师范学院计算机科学系查询条件运算符说明比例7：查询考试成绩不及格学生的学号。SELECTDISTINCTSnoFROMSCWHEREGrade<60;例8：查询年龄在20～23之间的学生的姓名，系别和年龄。SELECTSname,Sdept,SageFROMStudentWHERESageBETWEEN20AND23例9：查询年龄不在20～23之间的学生的姓名，系别和年龄。SELECTSname,Sdept,SageFROMStudentWHERESageNOTBETWEEN20AND239/20/202348周口师范学院计算机科学系例7：查询考试成绩不及格学生的学号。8/6/202348周口例10：查询计算机系、信息系学生的姓名、系别和年龄。SELECTSname,Sdept,SageFROMStudentWHERESdeptIN(‘CS’,’IS’);例11：查询不是计算机系、也不是信息系的学生的姓名、系别和年龄。SELECTSname,Sdept,SageFROMStudentWHERESdeptNOT

IN(‘CS’,’IS’);9/20/202349周口师范学院计算机科学系例10：查询计算机系、信息系学生的姓名、系别和年龄。8/6/谓词LIKE可以用来进行字符串的匹配。其一般语法格式如下：[NOT]LIKE‘<匹配串>’[ESCAPE‘<换码字符>’]其含义是查找指定的属性列值与<匹配串>相匹配的元组。<匹配串>可以是一个完整的字符串，也可以含有通配符％和—。其中：％(百分号)代表任意长度(可以为０)的字符串，如：a%b表示以a开头，以b结尾的任意长度的字符串。_(下划线)代表任意单个字符。a_b表示以a开头，以b结尾长度为３的任意字符串。9/20/202350周口师范学院计算机科学系谓词LIKE可以用来进行字符串的匹配。其一般语法格式如下：8例12：查询所有姓“王”的学生信息。SELECT*FROMStudentWHERESnamelike‘王%’;例13：查询名字中第二个字为“阳”的学生姓名和学号。SELECTSname,SnoFROMStudentWHERESnamelike‘__阳%’;如果用户要查询的字符串本身就含有%或_就要使用ESCAPE‘<换码字符>’对通配符进行转义9/20/202351周口师范学院计算机科学系例12：查询所有姓“王”的学生信息。8/6/202351周口例14：查询DB_Design课程的课程号和学分SELECTCno,CcreditFROMCourseWHERECnamelike‘DB\_Design’ESCAPE‘\’;

其中：ESCAPE‘\’短语表示\为换码字符，这样匹配串中紧跟在\后面的字符“_”不再具有通配符的含义，转义为普通的“_”字符。例15：查询以“DB_”开头，且倒数第三个字符为i的课程的详细情况SELECT＊FROMCourseWHERECnamelike‘DB\_%i__’ESCAPE‘\’;9/20/202352周口师范学院计算机科学系例14：查询DB_Design课程的课程号和学分8/6/20例16某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。SELECTSno,CnoFROMSCWHEREGradeISNULL例17查询所有有成绩的学生的学号和相应的课程号。SELECTSno,CnoFROMSCWHEREGradeISNOTNULL9/20/202353周口师范学院计算机科学系例16某些学生选修课程后没有参加考试，所以有选课记录，但没多重条件查询逻辑运算符AND和OR可用来联结多个查询条件，AND优先级高于OR，但用户可用括号改变优先级。例18查询计算机系年龄在20岁以下的学生姓名SELECTSnameFROMStudentWHERESdept=‘CS’ANDSage<20;例19：查询计算机系、信息系学生的姓名、系别和年龄。SELECTSname,Sdept,SageFROMStudentWHERESdept=‘CS’ORSdept=’IS’;9/20/202354周口师范学院计算机科学系多重条件查询8/6/202354周口师范学院计算机科学系三、对查询结果排序用户可以用ORDERBY子句对查询结果按照一个或多个属性列的升序(ASC)或降序(DESC)排列，缺省值为升序。例19查询选修了３号课程的学号及其成绩，查询结果按分数的降序排列。SELECTSno,GradeFROMSCWHERECno=‘3’ORDERBYGradeDESC;

对于空值，若按升序，含空值的元组将最后显示，若按降序排，空值的元组将最先显示。9/20/202355周口师范学院计算机科学系三、对查询结果排序8/6/202355周口师范学院计算机科学例19查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。SELECT＊FROMStudentORDERBYSdept,SageDESC;9/20/202356周口师范学院计算机科学系例19查询全体学生情况，查询结果按所在系的系号升序排列，同四、查询中集函数的使用说明：DISTINCT：取消列中的重复值ALL：为默认状态，表示不取消重复值。

函数名称函数功能COUNT([DISTINCT|ALL]*)统计元组个数COUNT([DISTINCT|ALL]<列名>)统计一列重值的个数SUM([DISTINCT|ALL]<列名>)计算数值型一列值的总和AVG([DISTINCT|ALL]<列名>)求一列值的平均值MAX([DISTINCT|ALL]<列名>)求一列值的最大值MIN([DISTINCT|ALL]<列名>)求一列值的最小值9/20/202357周口师范学院计算机科学系四、查询中集函数的使用函数名称函数功能COUNT([DIST例20：查询学生总人数。SELECTCOUNT(*)FROMStudent;例21：计算95001学生的平均成绩。SELECTAVG(Grade)FROMSCWHERESno=’95001’;9/20/202358周口师范学院计算机科学系例20：查询学生总人数。8/6/202358周口师范学院计算5．查询结果的分组与排序GROUPBY子句将查询结果表按某一列或多列值分组，值相等的为一组。分组的目的是为了细化集函数的作用对象，如果未对查询结果分组，集函数将作用于整个查询结果。分组后集函数将作用在每一个组上，即每一组都有一个函数值。

9/20/202359周口师范学院计算机科学系5．查询结果的分组与排序8/6/202359周口师范学院例22：查询选修了2门以上课程的学生学号,并且查询结果按照升序排列。SELECTSnoFROMSCGROUPBYSnoHAVINGCOUNT(*)>2ORDERBYSnoASC;WHERE与HAVING的区别在于作用的对象不同。WHERE子句作用于基本表或视图，从中选择满足条件的记录；HAVING短语作用于分组，从中选出满足条件的组。

9/20/202360周口师范学院计算机科学系例22：查询选修了2门以上课程的学生学号,并且查询结果按照升3.3.2连接查询前面的查询都是针对一个表进行的。若一个查询同时涉及两个以上的表，则称之为连接查询，连接查询是关系数据库中最主要的查询，包括等值连接、自然连接、非等值连接查询、自身连接查询、外连接查询和复合条件连接查询。9/20/202361周口师范学院计算机科学系3.3.2连接查询8/6/202361周口师范学院计算机从概念上讲，DBMS执行连接操作的过程是：首先在表1中找到第一个元组，然后从头开始扫描表2，逐一查找满足连接条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。表2全部查找完后，再找表1中第2个元组，然后再从头开始扫描表2，逐一查找满足连接条件的元组，找到后就将表1中的第2个元组与该元组拼接起来，形成结果表中一个元组。重复上述操作，直到表1中的全部元组都处理完毕为止。9/20/202362周口师范学院计算机科学系从概念上讲，DBMS执行连接操作的过程是：首先在表1中找到第一、等值与非等值连接。连接查询中用来连接两个表的条件称为连接条件或连接谓词，其一般格式为：

[<表名1>].<列名1><比较运算符>[<表名2>].<列名2>其中<比较运算符>主要有：=，>，<，>=，<=，!=。当连接运算符为=时，称为等值连接，使用其它运算符称为非等值连接。连接谓词中的<列名>称为连接字段，连接条件中的各个连接字段类型必须是可比的，但不必是相同的。Eg.9/20/202363周口师范学院计算机科学系一、等值与非等值连接。8/6/202363周口师范学院计算机在本例中，SELECT子句与WHERE子句中的属性名前都加上了表名前缀，这是为了避免混淆。如果属性名在参加连接的各表中是唯一的，则可以省略表名前缀。连接运算中有两种特殊情况，一种为自然连接，另一种为广义笛卡尔积（连接）。广义笛卡尔积是不带连接谓词的连接。两个表的广义笛卡尔积即是两表中元组的交叉乘积，其连接的结果会产生一些没有意义的元组，所以这种运算实际很少使用。若在等值连接中把目标列中重复的属性列去掉则为自然连接。9/20/202364周口师范学院计算机科学系在本例中，SELECT子句与WHERE子句中的属性名前都加上二、自身连接连接操作不仅可以在两个表之间进行，也可以是一个表与其自己进行连接，称为表的自身连接。在通常的连接操作中，只有满足连接条件的元组才能作为结果输出。9/20/202365周口师范学院计算机科学系二、自身连接8/6/202365周口师范学院计算机科学系三、外连接外连接的表示方法为，在连接谓词的某一边加符号*（有的数据库系统中用+）。外连接就好象为符号*所在边的表增加一个“万能的”行，这个行全部由空值组成。它可以和另一边的表中所有不满足条件的元组进行连接（从而使得另一个表在连接的过程中不会有元组丢失）。如果外连接符出现在连接条件的右边，称其为右外连接，如果外连接符出现在连接条件的左边，则称其为左外连接。9/20/202366周口师范学院计算机科学系三、外连接8/6/202366周口师范学院计算机科学系四、复合条件的连接上面各个连接查询中，WHERE子句中只有一个条件，即连接谓词。WHERE子句中可以有多个连接条件，称为复合条件连接。连接操作除了可以是两表连接，一个表与其自身连接外，还可以是两个以上的表进行连接，后者称为多表连接。9/20/202367周口师范学院计算机科学系四、复合条件的连接8/6/202367周口师范学院计算机科学例23查询每个学生及其所选课程的情况SelectStudent.*,SC.*FromStudent,SC

WhereStudent.Sno=SC.Sno;例24查询每个学生及其所选课程的情况（用自然连接）SelectStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,GradeFromStudent,SC

WhereStudent.Sno=SC.Sno;例25查询所有比李勇年龄大的学生姓名、年龄SelectX.Sname,X.SageFromStudentX,StudentYWhereX.Sage>Y.SageAndY.Sname=‘李勇’结果结果9/20/202368周口师范学院计算机科学系例23查询每个学生及其所选课程的情况结果结果8/6/20Student.SnoSnameSsexSageSdept95001李勇男20CS95001李勇男20CS95001李勇男20CS95002刘晨女19IS95002刘晨女19ISSC.SnoCnoGrade9500119295001285950013889500229095002380该查询的执行结果：9/20/202369周口师范学院计算机科学系Student.SnoSnameSsexSageSdept9Student.SnoSnameSsexSageSdept95001李勇男20CS95001李勇男20CS95001李勇男20CS95002刘晨女19IS95002刘晨女19ISCnoGrade192285388290380该查询的执行结果：9/20/202370周口师范学院计算机科学系Student.SnoSnameSsexSageSdept9例26查询每一门课的间接先修课（即先修课的先修课）。SelectFIRST.Cno,SECOND.CpnoFromCourseFIRST,CourseSECOND

WhereFIRST.Cpno=SECOND.Cno;例27查询所有学生的学号、姓名、选修的课程和成绩SelectStudent.Sno,Sname,Cname,GradeFromStudent,SC,CourseWhereStudent.Sno=SC.Sno(+)AndSC.Cno=Course.Cno(+)9/20/202371周口师范学院计算机科学系例26查询每一门课的间接先修课（即先修课的先修课）。8/6例28查询选修2号课程且成绩在90分以上的所有学生。SELECTStudent.Sno,SnameFROMStudent,SCWHEREStudent.Sno=SC.SnoANDSC.Cno=‘2’ANDSC.Grade>90;9/20/202372周口师范学院计算机科学系例28查询选修2号课程且成绩在90分以上的所有学生。8/6例29查询每个学生的学号、姓名、选修的课程名及成绩。SELECTStudent.Sno,Sname，Cname,GradeFROMStudent,SC,CourseWHEREStudent.Sno=SC.SnoANDSC.Cno=Course.Cno;9/20/202373周口师范学院计算机科学系例29查询每个学生的学号、姓名、选修的课程名及成绩。8/6Student.SnoSnameSsexSageSdept95001李勇男20CS95001李勇男20CS95001李勇男20CS95002刘晨女19IS95002刘晨女19ISSC.SnoCnoGrade9500119295001285950013889500229095002380该查询的执行结果：95003王敏女18MA95004张立男19IS9/20/202374周口师范学院计算机科学系Student.SnoSnameSsexSageSdept99/20/202375周口师范学院计算机科学系8/6/202375周口师范学院计算机科学系3.3.3嵌套查询在SQL语言中，一个SELECT-FROM-WHERE称为一个查询块，将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询。上层的查询块称为外层查询或父查询，下层查询称为内层查询或子查询。外层查询利用子查询来获取检索条件的条件值，并根据这个条件值确定外层查询的结果集合。SQL语言允许多层嵌套查询，即一个子查询中还可以嵌套其他子查询，通过子查询，可以利用一系列简单查询来构造一个复杂查询。子查询中的SELECT语句中不能使用ORDERBY子句，ORDERBY子句只能对最终查询结果排序。9/20/202376周口师范学院计算机科学系3.3.3嵌套查询在SQL语言中，一个SELECT-FROM嵌套查询一般的求解方法是由里向外处理，即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。嵌套查询使我们可以用多个简单查询构成复杂的查询，从而增强SQL的查询能力。以层层嵌套的方式来构造程序正是SQL(StructuredQueryLanguage)中“结构化”的含义所在。9/20/202377周口师范学院计算机科学系嵌套查询一般的求解方法是由里向外处理，即每个子查询在上一级查一、带有IN谓词的子查询在嵌套查询中，子查询的结果往往是一个集合，所以谓词IN是嵌套查询中最经常使用的谓词。例30查询与刘晨在同一个系学习的学生。SelectSno,Sname,SdeptFromStudentWhereSdeptIN(SelectSdeptFromStudentWhereSname=‘刘晨’);9/20/202378周口师范学院计算机科学系一、带有IN谓词的子查询8/6/202378周口师范学院计算本例中的查询也可以用自身连接来完成：SELECTS1.Sno,S1.Sname,S1.SdeptFROMStudentS1,StudentS2WHERES1.Sdept=S2.SdeptANDS2.Sname=“刘晨”；本例中父查询和子查询均引用了Student表，可以像自身连接那样用别名将父查询中的Student表与子查询中的Student表区分开：9/20/202379周口师范学院计算机科学系本例中的查询也可以用自身连接来完成：8/6/202379周口SelectSno,Sname,SdeptFromStudentS1WhereS1.SdeptIN(SelectSdeptFromStudentS2WhereS2.Sname=‘刘晨’);9/20/202380周口师范学院计算机科学系SelectSno,Sname,Sdept8/6/2例31查询选修了课程名为‘信息系统’的学生的学号和姓名。SELECTSno,SnameFROMStudentWHERESnoIN(SELECTSnoFROMSCWHERECnoIN(SELECTCnoFROMCourseWHERECname=‘信息系统’));①首先在Course

关系中找出“信息系统”的课程号，结果为3号②然后在SC关系中找出选修了3号课程的学生学号③最后在Student关系中取出Sno和Sname9/20/202381周口师范学院计算机科学系例31查询选修了课程名为‘信息系统’的学生的学号和姓名。①同样本查询也可以用连接查询来实现：SELECTSno,SnameFROMStudent,SC,CourseWHEREStudent.Sno=SC.SnoANDSC.Cno=Course.CnoANDCourse.Cname=“信息系统”；9/20/202382周口师范学院计算机科学系同样本查询也可以用连接查询来实现：8/6/202382周口师从例30和例31可以看到，查询涉及多个关系时，用嵌套查询逐步求解，层次清楚，易于构造，具有结构化程序设计的优点。有些嵌套查询可以用连接运算替代，有些是不能替代的。到底采用哪种方法用户可以根据自己的习惯确定。例30和例31中的各个子查询都只执行一次，其结果用于父查询。子查询的查询条件不依赖于父查询，这类子查询称为不相关子查询。这种子查询是最简单的一类子查询。9/20/202383周口师范学院计算机科学系从例30和例31可以看到，查询涉及多个关系时，用嵌套查询逐步二、带有比较运算符的子查询带有比较运算符的子查询是指父查询与子查询之间用比较运算符进行连接。当用户能确切知道内层查询返回的是单值时，可以用>,<,=,>=,<=,!=或<>等比较运算符。例如在例30中，由于一个学生只可能在一个系学习，也就是说内查询的结果是一个值，因此可以用=代替IN，其SQL语句如下：9/20/202384周口师范学院计算机科学系二、带有比较运算符的子查询带有比较运算符的子查询是指父查询与SELECTSno,Sname,SdeptFROMStudentWHERESdept=(SELECTSdeptFROMStudentWHERESname=‘刘晨’);需要注意的是，子查询一定要跟在比较符之后。9/20/202385周口师范学院计算机科学系SELECTSno,Sname,Sdept8/6/2三、带有ANY或ALL谓词的子查询子查询返回单值时可以用比较运算符，而使用ANY或ALL谓词时则必须同时使用比较运算符。其语义为：>ANY大于子查询结果中的某个值>ALL大于子查询结果中的所有值<ANY小于子查询结果中的某个值<ALL小于子查询结果中的所有值>=ANY大于等于子查询结果中的某个值>=ALL大于等于子查询结果中的所有值<=ANY小于等于子查询结果中的某个值9/20/202386周口师范学院计算机科学系三、带有ANY或ALL谓词的子查询8/6/202386周口师<=ALL小于等于子查询结果中的所有值=ANY等于子查询结果中的某个值=ALL等于子查询结果中的所有值(通常没有实际意义)!=(或<>)ANY不等于子查询结果中的某个值!=(或<>)ALL不等于子查询结果中的任何一个值9/20/202387周口师范学院计算机科学系<=ALL小于等于子查询结果中的所有值8/6/例32查询其他系中比信息系中某一学生年龄小的学生姓名和年龄。SelectSname,SageFromStudentWhereSdept<>‘IS’ANDSage<Any(SelectSageFromStudentWhereSdept=‘IS’)；DBMS执行此查询时，首先处理子查询找出IS系中所有学生的年龄，构成一个集合(19,18)。然后处理父查询，找所有不是IS系且年龄小于19或18的学生。9/20/202388周口师范学院计算机科学系例32查询其他系中比信息系中某一学生年龄小的学生姓名和年本查询也可以用集函数来实现。首先用子查询找出IS系中最大年龄（19）,然后在父查询中查所有非IS系且年龄小于19岁的学生姓名及年龄。SQL语句如下：

SelectSname,SageFromStudentWhereSdept<>‘IS’ANDSage<(SelectMAX(Sage)FromStudentWhereSdept=‘IS’)；9/20/202389周口师范学院计算机科学系本查询也可以用集函数来实现。首先用子查询找出IS系中最大年龄例33查询其他系中比信息系中所有学生年龄都小的学生姓名和年龄。SelectSname,SageFromStudentWhereSdept<>‘IS’AndSage<All(SelectSageFromStudentWhereSdept=‘IS’)；本查询也可以用集函数实现。SQL语句？

9/20/202390周口师范学院计算机科学系例33查询其他系中比信息系中所有学生年龄都小的学生姓名和事实上，用集函数实现子查询通常比直接用ANY或ALL查询效率要高。ANY与ALL与集函数的对应关系如下表示：

ANY,ALL谓词与集函数及IN谓词的等价转换关系=<>或!=<<=>>=ANYIN--<MAX<=MAX>MIN>=MINALL--IN<MIN<=MIN>MAX>=MAX9/20/202391周口师范学院计算机科学系事实上，用集函数实现子查询通常比直接用ANY或ALL查询效率四、带有EXISTS谓词的子查询EXISTS代表存在量词。带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”.例34查询选择了1号课程的学生姓名。SELECTSnameFROMStudentWHEREEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno=‘1’)；9/20/202392周口师范学院计算机科学系四、带有EXISTS谓词的子查询8/6/202392周口师范使用存在量词EXISTS后，若内层查询结果非空，则外层的WHERE子句返回真值，否则返回假值。由EXISTS引出的子查询，其目标列表达式通常都用*，因为带EXISTS的子查询只返回真值或假值，给出列名没有实际意义。这类子查询的查询条件依赖于外层父查询的某个属性值，称这类查询为相关子查询。

9/20/202393周口师范学院计算机科学系使用存在量词EXISTS后，若内层查询结果非空，则外层的WH从概念上讲，相关子查询的一般处理过程是：首先取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层查询，若WHERE子句返回值为真，则取此元组放入结果表；然后再取表的下一个元组；重复这一过程，直至外层表全部检查完为止。与EXISTS谓词相对应的是NOTEXISTS谓词。使用存在量词NOTEXISTS后，若内层查询结果为空，则外层的WHERE子句返回真值，否则返回假值。9/20/202394周口师范学院计算机科学系从概念上讲，相关子查询的一般处理过程是：8/6/202394例35查询没有选择1号课程的学生姓名。SELECTSnameFROMStudentWHERENotEXISTS(SELECT*FROMSCWHERESno=Student.SnoAndCno=‘1’)；一些带有EXISTS或NOTEXISTS谓词的子查询不能被其它形式的子查询等价替换，但所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带有EXISTS或NOTEXISTS谓词的子查询等价替换。9/20/202395周口师范学院计算机科学系例35查询没有选择1号课程的学生姓名。8/6/20239查询与刘晨在同一个系学习的学生。SELECTSno,Sname,SdeptFROMStudentWHERESdeptIN(SELECTSdeptFROMStudentWHERESname=‘刘晨’);9/20/202396周口师范学院计算机科学系查询与刘晨在同一个系学习的学生。8/6/202396周口师范SELECTSno,Sname,SdeptFROMStudentS1WHEREEXISTS(SELECT*FROMStudentS2WHERES2.Sdept=S1.SdeptANDS2.Sname=‘刘晨’);9/20/202397周口师范学院计算机科学系SELECTSno,Sname,Sdept8/6由于带EXISTS词的相关子查询只关心内层查询是否有返回值，并不需要查具体值因此其效率并不一定低于不相关子查询，有时是高效的方法

9/20/202398周口师范学院计算机科学系由于带EXISTS词的相关子查询只关心内层查询是否有返回值，例36查询选修了全部课程的学生姓名SELECTSnameFROMStudentWHERENOTEXISTS(SELECT*FROMCourse

WHERENOTEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno=Course.Cno));某个课程没选9/20/202399周口师范学院计算机科学系例36查询选修了全部课程的学生姓名某个课程没选8/6/23.3.4集合查询SELECT语句的查询结果是元组的集合，所以多个SELECT语句的可进行集合操作。集合操作主要包括并操作UNION、交操作INTERSECT和差操作MINUS.例37查询选修了1号或2号课程的学生SELECTSnoFROMSCWHERECno=‘1’UNIONSELECTSnoFROMSCWHERECno=‘2’9/20/2023100周口师范学院计算机科学系3.3.4集合查询SELECT语句的查询结果是元组的集合，所标准SQL中没有直接提供集合交操作和集合差操作，但可以用其他方法来实现。例38查询选修了1号课程的学生和选修了2号课程的学生的交集SELECTSnoFROMSCWHERECno=‘1’ANDSnoIN(SELECTSnoFROMSCWHERECno=‘2’);9/20/2023101周口师范学院计算机科学系标准SQL中没有直接提供集合交操作和集合差操作，但可以用其他例39查询计算机系的学生与年龄不大于19岁的学生的差集。即查询计算机系中年龄大于19岁的学生。SELECT*FROMStudentWHERESdept=‘CS’ANDSage>19;9/20/2023102周口师范学院计算机科学系例39查询计算机系的学生与年龄不大于19岁的学生的差集。83.4数据更新SQL中数据更新包括插入、修改数据和删除数据三条语句：INSERT语句UPDATE语句DELETE语句9/20/2023103周口师范学院计算机科学系3.4数据更新SQL中数据更新包括插入、修改数据和删除数3.4.1Insert语句SQL的数据插入语句INSERT通常有两种形式。一种是插入一个元组，另一种是插入子查询结果。后者可以一次插入多个元组一、插入单个元组INSERTINTO<表名>[（<属性列1>[,<属性列2>…]）]VALUES(<常量1>[{,<常量2>}]…);其功能是将新元组插入指定表中。其中新记录属性列1的值为常量1，属性列2的值为常量2，…INTO子句中没有出现的属性列，新记录在这些列上将取空值。如果INTO子句中没有任何列名，则新插入的记录必须在每个属性列上均有值。9/20/2023104周口师范学院计算机科学系3.4.1Insert语句SQL的数据插入语句INSER例1将一个新学生记录（学号：95020;姓名：陈冬；性别：男；所在系：IS;年龄：18岁）插入到Student表中。INSERTINTOStudentValues(‘95020’,‘陈冬’,‘男’,‘IS’,18);例2插入一条选课记录（‘95020’,‘1’）。INSERTINTOSC(Sno,Cno)Values(‘95020’,‘1’);新插入的记录在Grade列上取空值。9/20/2023105周口师范学院计算机科学系例1将一个新学生记录（学号：95020;姓名：陈冬；性别：二、插入子查询结果子查询不仅可以嵌套在SELECT语句中，用以构造父查询的条件，也可以嵌套在INSERT语句中，用以生成要插入的批量数据。INSERTINTO<表名>[（<属性列1>[,<属性列2>…]）]<子查询>;9/20/2023106周口师范学院计算机科学系二、插入子查询结果8/6/2023106周口师范学院计算机科例3对每一个系，求学生的平均年龄，并把结果存入数据库。INSERTINTODept_Age(Sdept,Avgage)

SELECTSdept,AVG(Sage)FROMStudentGROUPBYSdept;9/20/2023107周口师范学院计算机科学系例3对每一个系，求学生的平均年龄，并把结果存入数据库。83.4.2Update语句修改操作语句的一般格式：UPDATE<表名>SET<列名1>=<表达式1>[,<列名2>=<表达式2>]…[WHERE<条件>];其功能是修改指定表中满足WHERE子句条件的元组。其中SET子句给出<表达式>的值用于取代相应的属性列值。如果省略WHERE子句，则表示要修改表中的所有元组。9/20/2023108周口师范学院计算机科学系3.4.2Update语句修改操作语句的一般格式：8/6一、修改某一个元组的值例4、将学生95001的年龄改为22岁UPDATEStudentSETSage=22WHERESno=‘95001’；二、修改