数据库原理第3章关系数据库标准语言SQL(1).ppt

首都师范大学信息工程学院首都师范大学信息工程学院数据库系统原理数据库系统原理Principles of Database System第三章第三章 关系数据库标准语言关系数据库标准语言SQLSQLAnIntroductiontoDatabaseSystem第三章第三章 关系数据库标准语言关系数据库标准语言SQL3.1SQL概述3.2数据定义3.3查询3.4数据更新3.5视图3.6数据控制3.7嵌入式SQL3.8小结AnIntroductiontoDatabaseSystem3.1 SQL概述概述nSQL的特点n1.综合统一n2.高度非过程化n3.面向集合的操作方式n4.以同一种语法结构提供两种使用方法n5.语言简洁，易学易用AnIntroductiontoDatabaseSystem5.语言简捷，易学易用语言简捷，易学易用AnIntroductiontoDatabaseSystemSQL的基本概念的基本概念SQL视图视图2视图视图1基本表基本表2基本表基本表1基本表基本表3基本表基本表4存储文件存储文件2存储文件存储文件1外模式外模式模模 式式内模式内模式SQL支持关系数据库三级模式结构AnIntroductiontoDatabaseSystemSQL的基本概念（续）的基本概念（续）n基本表n本身独立存在的表nSQL中一个关系就对应一个基本表n一个(或多个)基本表对应一个存储文件n一个表可以带若干索引n存储文件n逻辑结构组成了关系数据库的内模式n物理结构是任意的，对用户透明n视图n从一个或几个基本表导出的表n数据库中只存放视图的定义而不存放视图对应的数据n视图是一个虚表n用户可以在视图上再定义视图AnIntroductiontoDatabaseSystem第三章第三章 关系数据库标准语言关系数据库标准语言SQL3.1SQL概述3.2数据定义3.3查询3.4数据更新3.5视图3.6数据控制3.7嵌入式SQL3.8小结AnIntroductiontoDatabaseSystem3.2 数数 据据 定定 义义 AnIntroductiontoDatabaseSystem3.2.1 定义定义语句格式语句格式CREATETABLE（，）；n：所要定义的基本表的名字n：组成该表的各个属性（列）n：涉及相应属性列的完整性约束条件n：涉及一个或多个属性列的完整性约束条件AnIntroductiontoDatabaseSystem例题例题 例1建立一个“学生”表Student，它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成。其中学号不能为空，值是唯一的，并且姓名取值也唯一。CREATETABLEStudent(SnoCHAR(5)NOTNULLUNIQUE，SnameCHAR(20)UNIQUE，SsexCHAR(1)，SageINT，SdeptCHAR(15)；AnIntroductiontoDatabaseSystem定义基本表（续）定义基本表（续）n常用完整性约束n主码约束：PRIMARYKEYn唯一性约束：UNIQUEn非空值约束：NOTNULLn参照完整性约束AnIntroductiontoDatabaseSystem例题例题（续）（续）n例2建立一个“学生选课”表SC，它由学号Sno、课程号Cno，修课成绩Grade组成，其中(Sno,Cno)为主码。nCREATETABLESCn(SnoCHAR(5),nCnoCHAR(3),nGradeint,nPrimarykey(Sno,Cno);AnIntroductiontoDatabaseSystem例题例题（续）（续）AnIntroductiontoDatabaseSystem三、删除基本表三、删除基本表 DROPTABLE;基本表删除数据，表上的索引都删除表上的视图往往仍然保留，但无法引用删除基本表时，系统会从数据字典中删去有关该基本表及其索引的描述（标准中没有，认为表建立后就永久存在）AnIntroductiontoDatabaseSystem例题例题例5删除Student表DROPTABLEStudent;AnIntroductiontoDatabaseSystem二、修改基本表二、修改基本表ALTERTABLEADD完整性约束DROPMODIFY；n：要修改的基本表nADD子句：增加新列和新的完整性约束条件nDROP子句：删除指定的完整性约束条件nMODIFY子句：用于修改列名和数据类型AnIntroductiontoDatabaseSystem例题例题 例2向Student表增加“入学时间”列，其数据类型为日期型。ALTERTABLEStudentADDScomeDATE；n不论基本表中原来是否已有数据，新增加的列一律为空值。AnIntroductiontoDatabaseSystem语句格式（续）语句格式（续）n删除属性列直接/间接删除n把表中要保留的列及其内容复制到一个新表中n删除原表n再将新表重命名为原表名直接删除属性列:(新)例：ALTERTABLEStudentDropScome；AnIntroductiontoDatabaseSystem 例3将年龄的数据类型改为半字长整数。ALTERTABLEStudentMODIFYSageSMALLINT；n注：修改原有的列定义有可能会破坏已有数据AnIntroductiontoDatabaseSystem例题例题 例4删除学生姓名必须取唯一值的约束。ALTERTABLEStudentDROPUNIQUE(Sname)；AnIntroductiontoDatabaseSystem3.2.2 建立与删除索引建立与删除索引 n建立索引是加快查询速度的有效手段n建立索引nDBA或表的属主（即建立表的人）根据需要建立n维护索引nDBMS自动完成n使用索引nDBMS自动选择是否使用索引以及使用哪些索引AnIntroductiontoDatabaseSystem一、建立索引一、建立索引 n语句格式CREATEUNIQUECLUSTERINDEXON(,)；n用指定要建索引的基本表名字n索引可以建立在该表的一列或多列上，各列名之间用逗号分隔n用指定索引值的排列次序，升序：ASC，降序：DESC。缺省值：ASCnUNIQUE表明此索引的每一个索引值只对应唯一的数据记录nCLUSTER表示要建立的索引是聚簇索引AnIntroductiontoDatabaseSystem例题例题 例6为学生-课程数据库中的Student，Course，SC三个表建立索引。其中Student表按学号升序建唯一索引，Course表按课程号升序建唯一索引，SC表按学号升序和课程号降序建唯一索引。CREATEUNIQUEINDEXStusnoONStudent(Sno)；CREATEUNIQUEINDEXCoucnoONCourse(Cno)；CREATEUNIQUEINDEXSCnoONSC(SnoASC，CnoDESC)；AnIntroductiontoDatabaseSystem建立索引建立索引（续（续）n聚簇索引n建建立立聚聚簇簇索索引引后后，基基表表中中数数据据也也需需要要按按指指定定的的聚聚簇簇属属性性值值的的升升序序或或降降序序存存放放。也也即即聚聚簇簇索索引引的的索索引引项项顺顺序序与与表表中中记记录录的的物物理理顺序一致顺序一致例：CREATECLUSTERINDEXStusnameONStudent(Sname)；在在Student表的表的Sname（姓名）列上建立一个聚簇索引，而姓名）列上建立一个聚簇索引，而且且Student表中的记录表中的记录将按照将按照Sname值的升序存放值的升序存放AnIntroductiontoDatabaseSystem建立索引建立索引（续）（续）n在一个基本表上最多只能建立一个聚簇索引n聚簇索引的用途：对于某些类型的查询，可以提高查询效率n聚簇索引的适用范围n很少对基表进行增删操作n很少对其中的索引列进行修改操作AnIntroductiontoDatabaseSystem二、删除索引二、删除索引 DROPINDEX；n删除索引时，系统会从数据字典中删去有关该索引的描述。例7删除Student表的Stusname索引。DROPINDEXStusname；AnIntroductiontoDatabaseSystem3.3 查查 询询 3.3.1概述3.3.2单表查询3.3.3连接查询3.3.4嵌套查询3.3.5集合查询3.3.6小结AnIntroductiontoDatabaseSystem3.3.1 概述概述 n语句格式SELECTALL|DISTINCT，FROM，WHEREGROUPBYHAVINGORDERBYASC|DESC；AnIntroductiontoDatabaseSystem语句格式语句格式nSELECT子句：指定要显示的属性列nFROM子句：指定查询对象(基本表或视图)nWHERE子句：指定查询条件nGROUPBY子句：对查询结果按指定列的值分组，该属性列值相等的元组为一个组。通常会在每组中作用集函数。nHAVING短语：筛选出只有满足指定条件的组nORDERBY子句：对查询结果表按指定列值的升序或降序排序AnIntroductiontoDatabaseSystem示例数据库示例数据库 学生-课程数据库n学生表：Student(Sno，Sname，Ssex，Sage，Sdept)n课程表：Course(Cno，Cname，Cpno，Ccredit)n学生选课表：SC(Sno，Cno，Grade)AnIntroductiontoDatabaseSystem3.3 查查 询询 3.3.1概述3.3.2单表查询3.3.3连接查询3.3.4嵌套查询3.3.5集合查询3.3.6小结AnIntroductiontoDatabaseSystem3.3.2 单表查询单表查询 查询仅涉及一个表，是一种最简单的查询操作一、选择表中的若干列二、选择表中的若干元组三、对查询结果排序四、使用集函数五、对查询结果分组AnIntroductiontoDatabaseSystem1.查询指定列查询指定列例1查询全体学生的学号与姓名。SELECTSno，SnameFROMStudent；例2查询全体学生的姓名、学号、所在系。SELECTSname，Sno，SdeptFROMStudent；AnIntroductiontoDatabaseSystem2.查询全部列查询全部列例3查询全体学生的详细记录。SELECTSno，Sname，Ssex，Sage，SdeptFROMStudent；或SELECT*FROMStudent；AnIntroductiontoDatabaseSystem3.查询经过计算的值查询经过计算的值 SELECT子句的为表达式n算术表达式n字符串常量n函数n列别名AnIntroductiontoDatabaseSystem3.查询经过计算的值查询经过计算的值例4查全体学生的姓名及其出生年份。SELECTSname，2013-SageFROMStudent；输出结果：Sname2013-Sage-李勇1976刘晨1977王名1978张立1978AnIntroductiontoDatabaseSystem3.查询经过计算的值查询经过计算的值例5查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名。SELECTSname，YearofBirth:，2013-Sage，LOWER(Sdept)FROMStudent；AnIntroductiontoDatabaseSystem例题（续）例题（续）输出结果：SnameYearofBirth:2013-SageLOWER(Sdept)-李勇YearofBirth:1976cs刘晨YearofBirth:1977is王名YearofBirth:1978ma张立YearofBirth:1977isAnIntroductiontoDatabaseSystem例例5.1 使用列使用列别名别名改变查询结果的列标题改变查询结果的列标题SELECTSnameNAME，YearofBirth:BIRTH，2013-SageBIRTHDAY，LOWER(Sdept)DEPARTMENTFROMStudent；输出结果：NAMEBIRTHBIRTHDAYDEPARTMENT-李勇YearofBirth:1976cs刘晨YearofBirth:1977is王名YearofBirth:1978ma张立YearofBirth:1977isAnIntroductiontoDatabaseSystem二、选择表中的若干元组二、选择表中的若干元组 n消除取值重复的行n查询满足条件的元组AnIntroductiontoDatabaseSystem1.消除取值重复的行消除取值重复的行n在SELECT子句中使用DISTINCT短语假设SC表中有下列数据SnoCnoGrade-9500119295001285950013889500229095002380AnIntroductiontoDatabaseSystemALL 与与 DISTINCT 例6查询选修了课程的学生学号。(1)SELECTSnoFROMSC;或(默认ALL)SELECTALLSnoFROMSC;结果：Sno-9500195001950019500295002AnIntroductiontoDatabaseSystem例题（续）例题（续）(2)SELECTDISTINCTSnoFROMSC;结果：Sno-9500195002AnIntroductiontoDatabaseSystem例题（续）例题（续）n注意DISTINCT短语的作用范围是所有目标列例：查询选修课程的各种成绩错误的写法SELECTDISTINCTCno，DISTINCTGradeFROMSC;正确的写法SELECTDISTINCTCno，GradeFROMSC;AnIntroductiontoDatabaseSystem2.查询满足条件的元组查询满足条件的元组WHERE子句常用的查询条件子句常用的查询条件AnIntroductiontoDatabaseSystem(1)比较大小比较大小在WHERE子句的中使用比较运算符n=，=，=，!=或，!，!，n逻辑运算符NOT+比较运算符例8查询所有年龄在20岁以下的学生姓名及其年龄。SELECTSname，SageFROMStudentWHERESage=20；AnIntroductiontoDatabaseSystem(2)确定范围确定范围n使用谓词BETWEENANDNOTBETWEENAND例10查询年龄在2023岁（包括20岁和23岁）之间的学生的姓名、系别和年龄。SELECTSname，Sdept，SageFROMStudentWHERESageBETWEEN20AND23；AnIntroductiontoDatabaseSystem例题（续例题（续）例11查询年龄不在2023岁之间的学生姓名、系别和年龄。SELECTSname，Sdept，SageFROMStudentWHERESageNOTBETWEEN20AND23；AnIntroductiontoDatabaseSystem(3)确定集合确定集合使用谓词IN,NOTIN：用逗号分隔的一组取值例12查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别。SELECTSname，SsexFROMStudentWHERESdeptIN(IS，MA，CS);AnIntroductiontoDatabaseSystem(3)确定集合确定集合例13查询既不是信息系、数学系，也不是计算机科学系的学生的姓名和性别。SELECTSname，SsexFROMStudentWHERESdeptNOTIN(IS，MA，CS);AnIntroductiontoDatabaseSystem(4)字符串匹配字符串匹配nNOTLIKEESCAPE：指定匹配模板匹配模板：固定字符串或含通配符的字符串当匹配模板为固定字符串时，可以用=运算符取代LIKE谓词用!=或运算符取代NOTLIKE谓词AnIntroductiontoDatabaseSystem通配符通配符w%(百分号)代表任意长度（长度可以为0）的字符串n例：a%b表示以a开头，以b结尾的任意长度的字符串。如acb，addgb，ab等都满足该匹配串_(下横线)代表任意单个字符n例：a_b表示以a开头，以b结尾的长度为3的任意字符串。如acb，afb等都满足该匹配串AnIntroductiontoDatabaseSystemESCAPE 短语：短语：n当用户要查询的字符串本身就含有%或_时，要使用ESCAPE短语对通配符进行转义。AnIntroductiontoDatabaseSystem例题例题1)匹配模板为固定字符串例14查询学号为95001的学生的详细情况。SELECT*FROMStudentWHERESnoLIKE95001；等价于：SELECT*FROMStudentWHERESno=95001；AnIntroductiontoDatabaseSystem例题（续）例题（续）2)匹配模板为含通配符的字符串例15查询所有姓刘学生的姓名、学号和性别。SELECTSname，Sno，SsexFROMStudentWHERESnameLIKE刘%；AnIntroductiontoDatabaseSystem例题（续）例题（续）匹配模板为含通配符的字符串（续）例16查询姓欧阳且全名为三个汉字的学生的姓名。SELECTSnameFROMStudentWHERESnameLIKE欧阳_；AnIntroductiontoDatabaseSystem例题（续）例题（续）匹配模板为含通配符的字符串（续）例17查询名字中第2个字为阳字的学生的姓名和学号。SELECTSname，SnoFROMStudentWHERESnameLIKE_阳%；AnIntroductiontoDatabaseSystem例题（续）例题（续）匹配模板为含通配符的字符串（续）例18查询所有不姓刘的学生姓名。SELECTSname，Sno，SsexFROMStudentWHERESnameNOTLIKE刘%；AnIntroductiontoDatabaseSystem例题（续）例题（续）3)使用换码字符将通配符转义为普通字符例19查询DB_Design课程的课程号和学分。SELECTCno，CcreditFROMCourseWHERECnameLIKEDB_DesignESCAPEAnIntroductiontoDatabaseSystem例题（续）例题（续）使用换码字符将通配符转义为普通字符(续)例20查询以DB_开头，且倒数第3个字符为i的课程的详细情况。SELECT*FROMCourseWHERECnameLIKEDB_%i_ESCAPE；AnIntroductiontoDatabaseSystem(5)涉及空值的查询涉及空值的查询n使用谓词ISNULL或ISNOTNULLn“ISNULL”不能用“=NULL”代替例21某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。SELECTSno，CnoFROMSCWHEREGradeISNULL；AnIntroductiontoDatabaseSystem例题例题(续续)例22查所有有成绩的学生学号和课程号。SELECTSno，CnoFROMSCWHEREGradeISNOTNULL；AnIntroductiontoDatabaseSystem(6)多重条件查询多重条件查询用逻辑运算符AND和OR来联结多个查询条件nAND的优先级高于ORn可以用括号改变优先级可用来实现多种其他谓词nNOTINnNOTBETWEENANDAnIntroductiontoDatabaseSystem例题例题例23查询计算机系年龄在20岁以下的学生姓名。SELECTSnameFROMStudentWHERESdept=CSANDSage=20ANDSage=23；AnIntroductiontoDatabaseSystem三、对查询结果排序三、对查询结果排序 使用ORDERBY子句n可以按一个或多个属性列排序n升序：ASC；降序：DESC；缺省值为升序当排序列含空值时nASC：排序列为空值的元组最后显示nDESC：排序列为空值的元组最先显示AnIntroductiontoDatabaseSystem对查询结果排序（续）对查询结果排序（续）例24查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。SELECTSno，GradeFROMSCWHERECno=3ORDERBYGradeDESC；AnIntroductiontoDatabaseSystem查询结果查询结果SnoGrade-95024950079295003829501082950097595014619500255AnIntroductiontoDatabaseSystem对查询结果排序（续）对查询结果排序（续）例25查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。SELECT*FROMStudentORDERBYSdept，SageDESC；AnIntroductiontoDatabaseSystem四、使用集函数四、使用集函数 5类主要集函数n计数COUNT（DISTINCT|ALL*）COUNT（DISTINCT|ALL）n计算总和SUM（DISTINCT|ALL）n计算平均值AVG（DISTINCT|ALL）AnIntroductiontoDatabaseSystem使用集函数（续）使用集函数（续）求最大值MAX（DISTINCT|ALL）求最小值MIN（DISTINCT|ALL）DISTINCT短语：在计算时要取消指定列中的重复值ALL短语：不取消重复值ALL为缺省值AnIntroductiontoDatabaseSystem使用集函数使用集函数（续）（续）例26查询学生总人数。SELECTCOUNT(*)FROMStudent；例27查询选修了课程的学生人数。SELECTCOUNT(DISTINCTSno)FROMSC；注：用DISTINCT以避免重复计算学生人数AnIntroductiontoDatabaseSystem使用集函数使用集函数（续）（续）例28计算1号课程的学生平均成绩。SELECTAVG(Grade)FROMSCWHERECno=1；例29查询选修1号课程的学生最高分数。SELECTMAX(Grade)FROMSCWHERCno=1；AnIntroductiontoDatabaseSystem五、对查询结果分组五、对查询结果分组 使用GROUPBY子句分组细化集函数的作用对象n未对查询结果分组，集函数将作用于整个查询结果n对查询结果分组后，集函数将分别作用于每个组AnIntroductiontoDatabaseSystem使用使用GROUP BY子句分组子句分组例30求各个课程号及相应的选课人数。SELECTCno，COUNT(Sno)FROMSCGROUPBYCno；结果CnoCOUNT(Sno)122234344433548AnIntroductiontoDatabaseSystem对查询结果分组对查询结果分组（续）（续）nGROUPBY子句的作用对象是查询的中间结果表n分组方法：按指定的一列或多列值分组，值相等的为一组n使用GROUPBY子句后，SELECT子句的列名列表中只能出现分组属性和集函数AnIntroductiontoDatabaseSystem使用使用HAVING短语筛选最终输出结果短语筛选最终输出结果例31查询选修了3门以上课程的学生学号。SELECTSnoFROMSCGROUPBYSnoHAVINGCOUNT(*)3；AnIntroductiontoDatabaseSystem例题例题例32查询有3门以上课程是90分以上的学生的学号及（90分以上的）课程数SELECTSno,COUNT(*)FROMSCWHEREGrade=90GROUPBYSnoHAVINGCOUNT(*)=3;AnIntroductiontoDatabaseSystem使用使用HAVING短语筛选最终输出结果短语筛选最终输出结果n只有满足HAVING短语指定条件的组才输出nHAVING短语与WHERE子句的区别：作用对象不同nWHERE子句作用于基表或视图，从中选择满足条件的元组。nHAVING短语作用于组，从中选择满足条件的组。AnIntroductiontoDatabaseSystem下课了。下课了。休息一会儿。休息一会儿。AnIntroductiontoDatabaseSystem