《数据库武大版3章关系数据库标准语言SQL1ppt课件》由会员分享，可在线阅读，更多相关《数据库武大版3章关系数据库标准语言SQL1ppt课件（102页珍藏版）》请在金锄头文库上搜索。
1、计算机科学与技术学院计算机科学与技术学院数据库系统概论数据库系统概论第三章第三章 关系数据库标准语言关系数据库标准语言SQLSQL1第三章第三章 关系数据库标准语言关系数据库标准语言SQL3.1SQL概述3.2数据定义3.3查询3.4数据更新3.5视图3.6数据控制3.7嵌入式SQL3.8小结23.1 SQL概述概述nSQL的特点n1.综合统一n2.高度非过程化n3.面向集合的操作方式n4.以同一种语法结构提供两种使用方法n5.语言简洁，易学易用35. 语言简捷，易学易用语言简捷，易学易用43.1.1 SQL：产生与发展：产生与发展n1970年，美国IBM研究中心的E.F.Codd连续发表多篇
2、论文，提出关系模型。n1972年，IBM公司开始研制实验型关系数据库管理系统SYSTEM R，配制的查询语言称为SQUARE (Specifying Queries As Relational Expression )语言，在语言中使用了较多的数学符号。n1974年，Boyce和Chamberlain把SQUARE修改为SEQUEL (Structured English QUEry Language )语言。n后来SEQUEL简称为SQL (Structured Query Language )，即“结构式查询语言”，SQL的发音仍为“sequel”。nSQL已经成为一个标准 ，不具有具体缩
3、写含义。53.1.1 SQL：数据库语言的标准：数据库语言的标准n1986.10ANSI:“DBLanguageSQL”n1987.6ISO:SQL86n1989.10ANSI:SQL89n1992.8ISO:SQL92，“SQL2”，600页n1999ISO:SQL99，“SQL3”，1000页63.1.1 SQL：标准的实现：标准的实现n70年代末起，成为事实的标准；n早期支持SQL的主要产品：Oracle，DB2，Sybase；nSQL成为标准后，数据库世界；nSQL向其他领域渗透；SE，AI；n具体厂商的SQL实现不同：nMSSQLServer：Transact-SQL，T-SQL；n
4、Oracle：PL/SQL；73.1.1 SQL：语言的标准：语言的标准n语言组成：n词汇n语言：n自然语言：汉语，英语，法语，鸟语，狗语，n程序设计语言：n语言作用：n交流的载体；n有交流对象，交流内容n可写，可读n语法n语义n机器语言n汇编语言n高级语言n超高级语言83.1.2 SQL数据库的体系结构数据库的体系结构SQL用户用户视图视图基本表基本表存储文件存储文件外模式外模式概念模式概念模式内模式内模式用户用户1用户用户2用户用户3用户用户4视图视图1视图视图1基本表基本表1基本表基本表2基本表基本表3基本表基本表4存储文件存储文件1存储文件存储文件2存储文件存储文件3存储文件存储文件4
5、93.1.2 SQL数据库的体系结构数据库的体系结构n1、关系模型与SQL标准的术语比较：n数据库模式：SQL模式(Schema)，数据库(Database)；n关系模式：基本表；n外/子模式：视图；n元组：行(Row)；n属性：列(Column)；103.1.2 SQL数据库的体系结构数据库的体系结构n2、SQL模式：已命名的数据组；构成：表，用户授权，规则，约束等；113.1.2 SQL数据库的体系结构数据库的体系结构n3、有三种类型的“二维表”：基本表，视图，导出表；n基本表：实际存储在数据库中的表，实表；n视图：由基本表或其他视图选择、投影得到的虚表，该结构的定义长期在数据库中存放；n
6、导出表：执行查询后产生的临时表；n4、用户可以用SQL语句对基本表和视图进行查询；在用户看来，无区别。n5、一个基本表可以跨越多个存储文件；一个存储文件也可以存放一个或多个基本表；123.1.2 SQL数据库的体系结构数据库的体系结构n6、SQL语句可以嵌套在宿主语言中使用；也可以在交互环境下使用；即，用户可以是应用程序，也可以是终端用户。133.1.3 SQL的组成的组成n核心SQL主要有四个部分:(1) 数据定义语言，即SQL DDL，用于定义SQL模式、基本表、视图、索引等结构。(2) 数据操纵语言，即SQL DML。数据操纵分成数据查询和数据更新两类。其中数据更新又分成插入、删除和修改
7、三种操作。(3) 嵌入式SQL语言的使用规定。这一部分内容涉及到SQL语句嵌入在宿主语言程序中的规则。(4) 数据控制语言，即SQL DCL，这一部分包括对基本表和视图的授权、完整性规则的描述、事务控制等内容。14第三章第三章 关系数据库标准语言关系数据库标准语言SQL3.1SQL概述3.2数据定义3.3查询3.4数据更新3.5视图3.6数据控制3.7嵌入式SQL3.8小结153.2 数数 据据 定定 义义关系数据库系统支持三级模式结构，其模式、外模式和内模式中的基本对象有表、视图和索引。因此SQL的数据定义包括定义表、定义视图和定义索引。163.2 数数 据据 定定 义义 173.2.1 定
8、义、删除、修改基本表定义、删除、修改基本表定义基本表语句一般格式：CREATETABLE（，）；n：所要定义的基本表的名字n：组成该表的各个属性（列）n：涉及相应属性列的完整性约束条件n：涉及一个或多个属性列的完整性约束条件18一、定义基本表一、定义基本表例1建立一个“学生”表Student，它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成。其中学号不能为空，值是唯一的，并且姓名取值也唯一。CREATETABLEStudent(SnoCHAR(5)NOTNULLUNIQUE，SnameCHAR(20)UNIQUE，SsexCHAR(2)，SageINT，
9、SdeptCHAR(15)；19定义基本表（续）定义基本表（续）n常用完整性约束n主码约束：PRIMARYKEYn唯一性约束：UNIQUEn非空值约束：NOTNULLn参照完整性约束20定义基本表（续）定义基本表（续）21定义基本表（续）定义基本表（续）22例题例题 （续）（续）n例2建立一个“学生选课”表SC，它由学号Sno、课程号Cno，修课成绩Grade组成，其中(Sno,Cno)为主码。nCREATETABLESC(nSnoCHAR(5),nCnoCHAR(3),nGradeint,nPrimarykey(Sno,Cno);23二、修改基本表二、修改基本表ALTERTABLEADD完整
10、性约束DROPMODIFY；n：要修改的基本表nADD子句：增加新列和新的完整性约束条件nDROP子句：删除指定的完整性约束条件nMODIFY子句：用于修改列名和数据类型24例题例题 例2向Student表增加“入学时间”列，其数据类型为日期型。ALTERTABLEStudentADDScomeDATE；n不论基本表中原来是否已有数据，新增加的列一律为空值。25语句格式（续）语句格式（续）n删除属性列直接/间接删除n把表中要保留的列及其内容复制到一个新表中n删除原表n再将新表重命名为原表名直接删除属性列:(新)例：ALTERTABLEStudentDropScome；26 例3将年龄的数据类型
11、改为半字长整数。ALTERTABLEStudentMODIFYSageSMALLINT；n注：修改原有的列定义有可能会破坏已有数据27例题例题 例4删除学生姓名必须取唯一值的约束。ALTERTABLEStudentDROPUNIQUE(Sname)；28三、删除基本表三、删除基本表 DROPTABLE;基本表定义一旦删除，表中的数据、此表上建立的索引和视图都将自动删除。29例题例题例5删除Student表DROPTABLEStudent;303.2.2 建立与删除索引建立与删除索引 n建立索引是加快查询速度的有效手段n建立索引nDBA或表的属主（即建立表的人）根据需要建立n有些DBMS自动建立
12、以下列上的索引nPRIMARYKEYnUNIQUEn维护索引nDBMS自动完成n使用索引nDBMS自动选择是否使用索引以及使用哪些索引31一、建立索引一、建立索引 n语句格式CREATEUNIQUECLUSTERINDEXON(,)；n用指定要建索引的基本表名字n索引可以建立在该表的一列或多列上，各列名之间用逗号分隔n用指定索引值的排列次序，升序：ASC，降序：DESC。缺省值：ASCnUNIQUE表明此索引的每一个索引值只对应唯一的数据记录nCLUSTER表示要建立的索引是聚簇索引。32例题例题 例6为学生-课程数据库中的Student，Course，SC三个表建立索引。其中Student表
13、按学号升序建唯一索引，Course表按课程号升序建唯一索引，SC表按学号升序和课程号降序建唯一索引。CREATEUNIQUEINDEXStusnoONStudent(Sno)；CREATEUNIQUEINDEXCoucnoONCourse(Cno)；CREATEUNIQUEINDEXSCnoONSC(SnoASC，CnoDESC)；33建立索引建立索引 （续）（续）n唯一值索引n对于已含重复值的属性列不能建UNIQUE索引n对某个列建立UNIQUE索引后，插入新记录时DBMS会自动检查新记录在该列上是否取了重复值。这相当于增加了一个UNIQUE约束34建立索引建立索引 （续（续）n聚簇索引n建
14、建立立聚聚簇簇索索引引后后，基基表表中中数数据据也也需需要要按按指指定定的的聚聚簇簇属属性性值值的的升升序序或或降降序序存存放放。也也即即聚聚簇簇索索引引的的索索引引项项顺顺序序与与表表中中记记录录的的物物理理顺序一致顺序一致例：CREATECLUSTERINDEXStusnameONStudent(Sname)；在在Student表的表的Sname（姓名）列上建立一个聚簇索引，而姓名）列上建立一个聚簇索引，而且且Student表中的记录表中的记录将按照将按照Sname值的升序存放值的升序存放35建立索引建立索引 （续）（续）n在一个基本表上最多只能建立一个聚簇索引n聚簇索引的用途：对于某些类
15、型的查询，可以提高查询效率n聚簇索引的适用范围n很少对基表进行增删操作n很少对其中的变长列进行修改操作36二、删除索引二、删除索引 DROPINDEX；n删除索引时，系统会从数据字典中删去有关该索引的描述。例7删除Student表的Stusname索引。DROPINDEXStusname；37第三章第三章 关系数据库标准语言关系数据库标准语言SQL3.1SQL概述3.2数据定义3.3查询3.4数据更新3.5视图3.6数据控制3.7嵌入式SQL3.8小结383.3 查查 询询 3.3.1概述3.3.2单表查询3.3.3连接查询3.3.4嵌套查询3.3.5集合查询3.3.6小结393.3.1 概述
16、概述nSQL语言中最主要、最核心的部分是它的查询功能。语言中最主要、最核心的部分是它的查询功能。n在关系数据库中，查询的含义是用来描述如何从数据库中在关系数据库中，查询的含义是用来描述如何从数据库中获取所需要的数据。获取所需要的数据。n关于查询的含义要明确以下关于查询的含义要明确以下3点：点：n任何时候使用任何时候使用SQL语句与语句与SQL Server中的数据进行交互时，都是中的数据进行交互时，都是在执行一个查询。在执行一个查询。n查询可以一次性地进行，也可以保存下来，以便以后多次执行。查询可以一次性地进行，也可以保存下来，以便以后多次执行。n查询是查询是DBA与与SQL Server数据
17、库进行交互的重要手段。数据库进行交互的重要手段。403.3.1 概述概述 语句格式SELECTALL|DISTINCT，FROM，WHEREGROUPBYHAVINGORDERBYASC|DESC；41语句格式语句格式nSELECT子句：指定要显示的属性列nFROM子句：指定查询对象(基本表或视图)nWHERE子句：指定查询条件nGROUPBY子句：对查询结果按指定列的值分组，该属性列值相等的元组为一个组。nHAVING短语：筛选出只有满足指定条件的组nORDERBY子句：对查询结果表按指定列值的升序或降序排序42语句格式语句格式整个SELECT语句的含义是：根据WHERE子句的条件表达式，从
18、FROM子句指定的基本表或视图中找出满足条件的元组，再按SELECT子句中的目标列表达式，选出元组中的属性值形成结果表。如果有GROUP子句，则将结果按的值进行分组，该属性列值相等的元组为一个组。如果有ORDER子句，则结果表还要按的值的升序或降序排列。43示例数据库示例数据库 学生-课程数据库n学生表：Student(Sno，Sname，Ssex，Sage，Sdept)n课程表：Course(Cno，Cname，Cpno，Ccredit)n学生选课表：SC(Sno，Cno，Grade)443.3 查查 询询 3.3.1概述3.3.2单表查询3.3.3连接查询3.3.4嵌套查询3.3.5集合查
19、询3.3.6小结453.3.2 单表查询单表查询 查询仅涉及一个表，是一种最简单的查询操作一、选择表中的若干列二、选择表中的若干元组三、对查询结果排序四、使用集函数五、对查询结果分组46查询指定列查询指定列例1查询全体学生的学号与姓名。SELECTSno，SnameFROMStudent；47查询指定列查询指定列例2查询全体学生的姓名、学号、所在系。SELECTSname，Sno，SdeptFROMStudent；48查询全部列查询全部列例3查询全体学生的详细记录。SELECTSno，Sname，Ssex，Sage，SdeptFROMStudent；或SELECT*FROMStudent；49
20、3. 查询经过计算的值查询经过计算的值 SELECT子句的为表达式n算术表达式n字符串常量n函数n列别名n等503. 查询经过计算的值查询经过计算的值例4查全体学生的姓名及其出生年份。SELECTSname，2006-SageFROMStudent；513. 查询经过计算的值查询经过计算的值例5查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名。SELECTSname，YearofBirth:，2000-Sage，ISLOWER(Sdept)FROMStudent；52例题（续）例题（续）输出结果：SnameYearofBirth:2000-SageISLOWER(Sdept)-
21、李勇YearofBirth:1976cs刘晨YearofBirth:1977is王名YearofBirth:1978ma张立YearofBirth:1977is53例例5.1 使用列使用列别名别名改变查询结果的列标题改变查询结果的列标题SELECTSnameNAME，YearofBirth:BIRTH，2000-SageBIRTHDAY，ISLOWER(Sdept)DEPARTMENTFROMStudent；输出结果：NAMEBIRTHBIRTHDAYDEPARTMENT-李勇YearofBirth:1976cs刘晨YearofBirth:1977is王名YearofBirth:1978ma张
22、立YearofBirth:1977is54二、选择表中的若干元组二、选择表中的若干元组 n消除取值重复的行n查询满足条件的元组551. 消除取值重复的行消除取值重复的行n在SELECT子句中使用DISTINCT短语假设SC表中有下列数据SnoCnoGrade-950011929500128595001388950022909500238056ALL 与与 DISTINCT 例6查询选修了课程的学生学号。(1)SELECTSnoFROMSC;或(默认ALL)SELECTALLSnoFROMSC;结果：Sno-950019500195001950029500257例题（续）例题（续）(2)SELE
23、CTDISTINCTSnoFROMSC;结果：Sno-950019500258例题（续）例题（续）n注意DISTINCT短语的作用范围是所有目标列例：查询选修课程的各种成绩错误的写法SELECTDISTINCTCno，DISTINCTGradeFROMSC;正确的写法SELECTDISTINCTCno，GradeFROMSC;592.查询满足条件的元组查询满足条件的元组WHERE子句常用的查询条件子句常用的查询条件60(1) 比较大小比较大小在WHERE子句的中使用比较运算符n=，=，=，!=或，!，!，n逻辑运算符NOT+比较运算符例8查询所有年龄在20岁以下的学生姓名及其年龄。SELECT
24、Sname，SageFROMStudentWHERESage=20；61(1) 比较大小比较大小62(2) 确定范围确定范围n使用谓词BETWEENANDNOTBETWEENAND例10查询年龄在2023岁（包括20岁和23岁）之间的学生的姓名、系别和年龄。SELECTSname，Sdept，SageFROMStudentWHERESageBETWEEN20AND23；63(2) 确定范围确定范围64例题（续）例题（续）例11查询年龄不在2023岁之间的学生姓名、系别和年龄。SELECT Sname， Sdept，SageFROMStudentWHERESageNOTBETWEEN20AND2
25、3；65(3) 确定集合确定集合使用谓词IN,NOTIN：用逗号分隔的一组取值66(3) 确定集合确定集合例12查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别。SELECTSname，SsexFROMStudentWHERESdeptIN(IS，MA，CS);67(3) 确定集合确定集合例13查询既不是信息系、数学系，也不是计算机科学系的学生的姓名和性别。SELECTSname，SsexFROMStudentWHERE Sdept NOT IN (IS，MA，CS);68(4) 字符串匹配字符串匹配谓词LIKE可以用来进行字符串的匹配。其一般语法格式如下：NOTLIK
26、EESCAPE其含义是查找指定的属性列值与相匹配的元组。：指定匹配模板匹配模板：固定字符串或含通配符（%,_）的字符串当匹配模板为固定字符串时，可以用=运算符取代LIKE谓词用!=或运算符取代NOTLIKE谓词69通配符通配符w%(百分号)代表任意长度（长度可以为0）的字符串n例：a%b表示以a开头，以b结尾的任意长度的字符串。如acb，addgb，ab等都满足该匹配串_(下横线)代表任意单个字符n例：a_b表示以a开头，以b结尾的长度为3的任意字符串。如acb，afb等都满足该匹配串70ESCAPE 短语：短语：n当用户要查询的字符串本身就含有%或_时，要使用ESCAPE短语对通配符进行转义
27、。71例题例题1)匹配模板为固定字符串例14查询学号为95001的学生的详细情况。SELECT*FROMStudentWHERESnoLIKE95001；等价于：SELECT*FROMStudentWHERESno=95001；7273例题（续）例题（续）2)匹配模板为含通配符的字符串例15 查询所有姓刘学生的姓名、学号和性别。 SELECT Sname，Sno，Ssex FROM Student WHERE Sname LIKE 刘%；74例题（续）例题（续）匹配模板为含通配符的字符串（续）例16查询姓欧阳且全名为三个汉字的学生的姓名。SELECTSnameFROMStudentWHERES
28、nameLIKE欧阳_；75例题（续）例题（续）匹配模板为含通配符的字符串（续）例17查询名字中第2个字为阳字的学生的姓名和学号。SELECTSname，SnoFROMStudentWHERESnameLIKE_阳%；76例题（续）例题（续）匹配模板为含通配符的字符串（续）例18查询所有不姓刘的学生姓名。SELECTSname，Sno，SsexFROMStudentWHERESnameNOTLIKE刘%；77例题（续）例题（续）3)使用换码字符将通配符转义为普通字符例19查询DB_Design课程的课程号和学分。SELECTCno，CcreditFROMCourseWHERECnameLIKE
29、DB_DesignESCAPE78例题（续）例题（续）使用换码字符将通配符转义为普通字符(续)例20查询以DB_开头，且倒数第3个字符为i的课程的详细情况。SELECT*FROMCourseWHERECnameLIKEDB_%i_ESCAPE；79(5) 涉及空值的查询涉及空值的查询n使用谓词ISNULL或ISNOTNULLn“ISNULL”不能用“=NULL”代替80(5) 涉及空值的查询涉及空值的查询例21某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。SELECTSno，CnoFROMSCWHEREGradeISNULL；81例题例
30、题(续续) 例22查所有有成绩的学生学号和课程号。SELECTSno，CnoFROMSCWHEREGradeISNOTNULL；82(6) 多重条件查询多重条件查询用逻辑运算符AND和OR来联结多个查询条件nAND的优先级高于ORn可以用括号改变优先级可用来实现多种其他谓词nNOTINnNOTBETWEENAND83例题例题例23查询计算机系年龄在20岁以下的学生姓名。SELECTSnameFROMStudentWHERESdept=CSANDSage=20ANDSage=23；86三、对查询结果排序三、对查询结果排序 使用ORDERBY子句n可以按一个或多个属性列排序n升序：ASC；降序：D
31、ESC；缺省值为升序当排序列含空值时nASC：排序列为空值的元组最后显示nDESC：排序列为空值的元组最先显示87对查询结果排序（续）对查询结果排序（续） 例24查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。SELECTSno，GradeFROMSCWHERECno=3ORDER BY GradeDESC；88对查询结果排序（续）对查询结果排序（续） 例25查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。SELECT*FROMStudentORDER BY Sdept，SageDESC；89四、使用集函数四、使用集函数 5类主要集函数n计数CO
32、UNT（DISTINCT|ALL*）COUNT（DISTINCT|ALL）n计算总和SUM（DISTINCT|ALL）n计算平均值AVG（DISTINCT|ALL）90使用集函数（续）使用集函数（续） 求一列中最大值MAX（DISTINCT|ALL）求最小值MIN（DISTINCT|ALL）DISTINCT短语：在计算时要取消指定列中的重复值ALL短语：不取消重复值ALL为缺省值91使用集函数使用集函数 （续）（续）例26查询学生总人数。SELECTCOUNT(*)FROMStudent；例27查询选修了课程的学生人数。SELECTCOUNT(DISTINCTSno)FROMSC；注：用DIS
33、TINCT以避免重复计算学生人数9293使用集函数使用集函数 （续）（续）例28计算1号课程的学生平均成绩。SELECTAVG(Grade)FROMSCWHERECno=1；例29查询选修1号课程的学生最高分数。SELECTMAX(Grade)FROMSCWHERCno=1；9495五、对查询结果分组五、对查询结果分组 使用GROUPBY子句分组细化集函数的作用对象n未对查询结果分组，集函数将作用于整个查询结果n对查询结果分组后，集函数将分别作用于每个组96使用使用GROUP BY子句分组子句分组例30求各个课程号及相应的选课人数。SELECTCno，COUNT(Sno)FROMSCGROUP
34、BYCno；9798对查询结果分组对查询结果分组 （续）（续）nGROUPBY子句的作用对象是查询的中间结果表n分组方法：按指定的一列或多列值分组，值相等的为一组n使用GROUPBY子句后，SELECT子句的列名列表中只能出现分组属性和集函数99使用使用HAVING短语筛选最终输出结果短语筛选最终输出结果例31查询选修了3门以上课程的学生学号。SELECTSnoFROMSCGROUPBYSnoHAVINGCOUNT(*)3；100例题例题例32查询有3门以上课程是90分以上的学生的学号及（90分以上的）课程数SELECTSno,COUNT(*)FROMSCWHEREGrade=90GROUPBYSnoHAVINGCOUNT(*)=3;101使用使用HAVING短语筛选最终输出结果短语筛选最终输出结果n只有满足HAVING短语指定条件的组才输出nHAVING短语与WHERE子句的区别：作用对象不同nWHERE子句作用于基表或视图，从中选择满足条件的元组。nHAVING短语作用于组，从中选择满足条件的组。102
