标准语言SQL.ppt
- 文档编号:17688547
- 上传时间:2023-08-02
- 格式:PPT
- 页数:105
- 大小:1.20MB
标准语言SQL.ppt
《标准语言SQL.ppt》由会员分享,可在线阅读,更多相关《标准语言SQL.ppt(105页珍藏版)》请在冰点文库上搜索。
1,3.1SQL概述(了解)3.2SQL数据定义(掌握)3.3SQL数据查询(掌握重点)3.4SQL数据更新(掌握)3.5SQL视图(掌握),第三章关系数据库标准语言SQL,2,一、SQL的发展SQL是StructuredQueryLanguage的缩写,即结构化查询语言,是关系数据库的标准语言。
1974年Boyce&Chambarlin提出,在IBM的SystemR上首先实现,1979年Oracle1982年IBM的DB21984年Sybase,采用SQL作为数据库语言,3.1SQL概述,3,1986年10月成为美国国家标准1987年国际标准化组织(ISO)采纳为国际标准1989年ISO推出SQL891992年ISO推出SQL2目前正制定SQL3标准,3.1SQL概述,4,
(1)综合统一
(2)高度非过程化(3)面向集合的操作方式(4)以同一种语法结构提供两种使用方法(5)语言简洁,易学易用,二、SQL的主要特点,3.1SQL概述,5,
(1)SQL综合统一,非关系模型实现模型的三要素以及三级模式结构需要:
数据定义语言DDL;数据操作语言DML;外模式定义语言SDDL;数据存储语言DSDL等不同语言。
关系模式中只需要SQL语言就可以了,而且并没有增加SQL语言的复杂度,反而很简单。
6,
(2)高度非过程化,非关系数据模型采用的是“面向过程”的语言,用户必须指明做什么、如何做等。
关系模型中的语言是非过程化的语言,只需用户提出“做什么”就可以了,无须指明“怎么做”。
系统会根据用户的需求自动选择数据操作方法。
7,(3)面向集合的操作方式,非关系模型如层次模型、网状模型中采用的是面向记录的操作方式,即实体是用数据结构中的结点来描述的,定义数据、操作数据都是面向结点的。
关系模型中数据定义和操作都是关系;关系的运算对象和运算结果也都是关系。
也就是说运算是面向集合的。
8,(4)以同一种语法结构提供两种使用方法,独立语言:
独立的运行于联机终端,用户可以在终端直接敲入SQL语言提交执行。
嵌入式语言:
SQL语句可以嵌入到高级语言(如C、Java、Pascal)程序中。
这两种结构下,SQL的语法基本上是一致的。
9,(5)语言简捷,易学易用,10,SQL支持关系数据库三级模式结构,11,说明:
基本表是独立存在的表。
一个关系对应一个表。
一个(或多个)表对应一个存储文件,每个表可有若干索引,这些索引也可放在存储文件中。
对内模式,只需定义索引,其余的一切均由DBMS自动完成。
视图是从一个或几个基本表中导出的表,概念上同基本表。
但它并不真正存储数据,也不独立存在,它依赖于导出它的基本表,数据也存放在原来的基本表中。
12,说明:
视图是从基本表导出的虚表,索引依赖于基本表,SQL没有修改视图和索引的操作,可通过先删除,再创建达此目的。
3.2SQL数据定义,13,一、基本表的定义和修改1、定义基本表CREATETABLE表名(列名1类型列级完整性约束,列名2类型列级完整性约束),);,:
所要定义的基本表的名字:
组成该表的各个属性(列):
涉及相应属性列的完整性约束条件:
涉及一个或多个属性列的完整性约束条件,14,例:
建立一个“学生”表Student,它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成。
其中学号不能为空,值是唯一的,并且姓名取值也唯一。
CREATETABLEStudent(SnoCHAR(5)NOTNULLUNIQUE,SnameCHAR(20)UNIQUE,SsexCHAR
(1),SageINT,SdeptCHAR(15);,15,2、修改基本表
(1)增加列:
ALTERTABLE表名ADD列名类型完整性约束;
(2)修改列类型:
ALTERTABLE表名ALTERCOLUMN列名类型;(3)删除完整性约束:
ALTERTABLE表名DROP完整性约束名;,如ALTERTABLESALTERCOLUMNSDCHAR(20),如ALTERTABLESADDSDINT/UNIQUE(SN);,在定义基本表时要考虑充分,16,3、删除基本表DROPTABLE表名CASCADE|RESTRICT;,注意:
删除基本表时,表中的数据、建立在表上的索引和视图将一并被删除,因此应格外小心。
二、索引的建立和删除由DBA或表的属主进行,存取数据时由系统自动选取合适的索引作为存取路径,用户不必也不能选择索引。
17,1、建立索引CREATEUNIQUECLUSTERINDEX索引名ON表名(列名,2、删除索引DROPINDEX索引名;,,列名,);,18,例:
为学生-课程数据库中的Student,Course,SC三个表建立索引。
其中Student表按学号升序建唯一索引,Course表按课程号升序建唯一索引,SC表按学号升序和课程号降序建唯一索引。
例:
在Student表的Sname(姓名)列上建立一个聚簇索引,而且Student表中的记录将按照Sname值的升序存放。
CREATEUNIQUEINDEXStusnoONStudent(Sno);CREATEUNIQUEINDEXCoucnoONCourse(Cno);CREATEUNIQUEINDEXSCnoONSC(SnoASC,CnoDESC);,CREATECLUSTERINDEXStusnameONStudent(Sname);,19,查询是数据库的核心操作。
SQL仅提供了唯一的语句SELECT,其使用方式灵活,功能非常丰富。
3.3SQL数据查询,单表查询连接查询嵌套查询集合查询,20,一般格式,SELECTALL|DISTINCT*|目标列FROM基本表(或视图)WHERE条件表达式GROUPBY列名1HAVING内部函数表达式ORDERBY列名2;,3.3SQL数据查询,21,SELECT子句:
指定要显示的属性列FROM子句:
指定查询对象(基本表或视图)WHERE子句:
指定查询条件GROUPBY子句:
对查询结果按指定列的值分组,该属性列值相等的元组为一个组。
通常会在每组中作用集函数。
HAVING短语:
筛选出只有满足指定条件的组ORDERBY子句:
对查询结果表按指定列值的升序或降序排序,22,单表查询仅涉及一个表,是一种最简单的查询操作,有以下五种形式:
1、选择表中的若干列(相当于投影运算)2、选择表中的若干元组(相当于选择运算)3、对查询结果排序(运算结果的处理)4、使用集函数(运算结果的处理)5、对查询结果分组(运算结果的处理),一、单表查询,23,1、选择表中的若干列,查询单列查询多列、全部列查询经过计算的值,24,查询指定列(投影),例:
查询全体学生的姓名、学号、所在系。
SELECTSname,Sno,SdeptFROMStudent;,查询全部列,例:
查询全体学生的详细记录。
SELECTSno,Sname,Ssex,Sage,SdeptFROMStudent;或SELECT*FROMStudent;,25,查询经过计算的值,SELECT子句的可以为算术表达式、字符串常量、函数、列别名、其他,例:
查全体学生的姓名及其出生年份。
SELECTSname,2012-SageFROMStudent;,26,消除取值重复的行,相当于投影运算查询满足条件的元组,相当于选择运算,2、选择表中的若干元组,27,消除取值重复的行,例:
假设SC表中有下列数据SnoCnoGrade-9500119295001285950013889500229095002380,
(1)SELECTSnoFROMSC;结果:
Sno-9500195001950019500295002,
(2)SELECTDISTINCTSnoFROMSC;结果:
Sno-9500195002,在SELECT子句中使用DISTINCT短语,28,查询满足条件的元组,WHERE子句常用的查询条件,常用的查询条件,29,
(1)比较大小,在WHERE子句的中使用比较运算符=,=,!
,!
=20;,30,
(2)确定范围,使用谓词BETWEENANDNOTBETWEENAND例:
查询年龄在2023岁(包括20岁和23岁)之间的学生的姓名、系别和年龄。
SELECTSname,Sdept,SageFROMStudentWHERESageBETWEEN20AND23;,31,(3)确定集合,使用谓词IN,NOTIN:
用逗号分隔的一组取值例12查询信息系(IS)、数学系(MA)和计算机科学系(CS)学生的姓名和性别。
SELECTSname,SsexFROMStudentWHERESdeptIN(IS,MA,CS);,32,(4)字符串匹配,使用谓词NOTLIKEESCAPE:
指定匹配模板匹配模板:
固定字符串或含通配符的字符串当匹配模板为固定字符串时:
可以用=运算符取代LIKE谓词,用!
=或运算符取代NOTLIKE谓词。
33,通配符,%(百分号)代表任意长度(长度可以为0)的字符串例:
a%b表示以a开头,以b结尾的任意长度的字符串。
如acb,addgb,ab等都满足该匹配串。
_(下横线)代表任意单个字符例:
a_b表示以a开头,以b结尾的长度为3的任意字符串。
如acb,afb等都满足该匹配串,34,ESCAPE短语:
当用户要查询的字符串本身就含有%或_时,要使用ESCAPE短语对通配符进行转义。
35,例:
查询所有姓刘学生的姓名、学号和性别。
SELECTSname,Sno,SsexFROMStudentWHERESnameLIKE刘%;,例:
查询姓欧阳且全名为三个汉字的学生的姓名。
SELECTSnameFROMStudentWHERESnameLIKE欧阳_;,36,例:
查询DB_Design课程的课程号和学分。
SELECTCno,CcreditFROMCourseWHERECnameLIKEDB_DesignESCAPE;,37,(5)涉及空值的查询,使用谓词ISNULL或ISNOTNULL“ISNULL”不能用“=NULL”代替例:
某些学生选修课程后没有参加考试,所以有选课记录,但没有考试成绩。
查询缺少成绩的学生的学号和相应的课程号。
SELECTSno,CnoFROMSCWHEREGradeISNULL;,38,(6)多重条件查询,用逻辑运算符AND和OR来联结多个查询条件AND的优先级高于OR可以用括号改变优先级可用来实现多种其他谓词NOTINNOTBETWEENAND,39,例:
查询计算机系年龄在20岁以下的学生姓名。
SELECTSnameFROMStudentWHERESdept=CSANDSage20;,例:
查询年龄在2023岁(包括20岁和23岁)之间的学生的姓名、系别和年龄。
SELECTSname,Sdept,SageFROMStudentWHERESageBETWEEN20AND23;,40,3、对查询结果排序,使用ORDERBY子句可以按一个或多个属性列排序升序:
ASC;降序:
DESC;缺省值为升序当排序列含空值时ASC:
排序列为空值的元组最后显示DESC:
排序列为空值的元组最先显示,41,例:
查询选修了3号课程的学生的学号及其成绩,查询结果按分数降序排列。
SELECTSno,GradeFROMSCWHERECno=3ORDERBYGradeDESC;,结果:
SnoGrade-9501095024950079295003829501082950097595014619500255,42,例:
查询全体学生情况,查询结果按所在系的系号升序排列,同一系中的学生按年龄降序排列。
SELECT*FROMStudentORDERBYSdept,SageDESC;,43,4、使用集函数,六类主要集函数,COUNT统计一列中的(NOTNULL)值的个数COUNT(*)统计元组个数SUM对一列求和(数值型)AVG对一列求平均值(数值型)MAX对一列求最大值MIN对一列求最小值,44,例:
查询学生总人数。
SELECTCOUNT(*)FROMStudent;例:
查询选修了课程的学生人数。
SELECTCOUNT(DISTINCTSno)FROMSC;注:
用DISTINCT以避免重复计算学生人数。
45,例:
计算1号课程的学生平均成绩。
SELECTAVG(Grade)FROMSCWHERECno=1;例:
查询选修1号课程的学生最高分数。
SELECTMAX(Grade)FROMSCWHERCno=1;,46,5、对查询结果分组,使用GROUPBY子句分组细化集函数的作用对象未对查询结果分组,集函数将作用于整个查询结果对查询结果分组后,集函数将分别作用于每个组,47,例:
求各个课程号及相应的选课人数。
SELECTCno,COUNT(Sno)FROMSCGROUPBYCno;结果可能为:
CnoCOUNT(Sno)122234344433548,48,说明:
GROUPBY子句的作用对象是查询的中间结果表分组方法:
按指定的一列或多列值分组,值相等的为一组使用GROUPBY子句后,SELECT子句的列名列表中只能出现分组属性和集函数,49,使用HAVING短语筛选最终输出结果,例:
查询选修了3门以上课程的学生学号。
SELECTSnoFROMSCGROUPBYSnoHAVINGCOUNT(*)3;,对于SC中每个Sno的统计值,50,例:
查询有3门以上课程是90分以上的学生的学号及(90分以上的)课程数SELECTSno,COUNT(*)FROMSCWHEREGrade=90GROUPBYSnoHAVINGCOUNT(*)=3;,作用范围是整个SC表,作用范围是分组后的统计值,51,只有满足HAVING短语指定条件的组才输出HAVING是选择分组的条件且必须和GROUPBY一起使用集函数只能作用于HAVING和目标列,而不能用于WHERE。
HAVING短语与WHERE子句的区别:
作用对象不同WHERE子句作用于基本表或视图,从中选择满足条件的元组。
HAVING短语作用于组,从中选择满足条件的组。
52,二、连接查询,连接查询是一种多表查询,有以下四种查询方式:
1、等值与非等值查询2、自身连接3、外连接4、复合条件连接,53,SQL中没有专门的JOIN命令,而是靠SELECT语句中的WHERE子句来达到连接运算的目的,因此更加灵活、简便。
用来连接两个表的条件称为连接条件或连接谓词。
连接条件的一般格式为:
表名1.列名1比较运算符表名2.列名2表名1.列名1BETWEEN表名2.列名2AND表名2.列名3比较运算符主要有:
=、=、=、!
=。
等值连接:
运算符为“=”时。
非等值连接:
运算符不是“=”时。
自然连接:
等值连接且目标列不含重复属性。
1、等值与非等值查询,54,SELECTS.S#,SN,GFROMS,SCWHERES.S#=SC.S#ANDSC.C#=C1;,01AMA2002BCS1903CIS2104DMA1905EMA20,01C1A01C2A02C2B02C3C03C3B04C1B04C4A,S:
S#SNSDSA,SC:
S#C#G,例:
求选修C1课程的学生学号、姓名和成绩。
1、等值与非等值查询,55,例:
求选修C1课程的学生学号、姓名和成绩。
SELECTS.S#,SN,GFROMS,SCWHERES.S#=SC.S#ANDSC.C#=C1;,01AMA2002BCS1903CIS2104DMA1905EMA20,S:
S#SNSDSA,SC:
S#C#G,查询结果:
S#SNG,01A,A,01C1A01C2A02C2B02C3C03C3B04C1B04C4A,条件满足,条件不满足,条件不满足,04D,B,1、等值与非等值查询,56,2、自身连接,SELECTTAB1.FROMTAB1T1,TAB1T2WHERET1.=T2.,需要给表起别名以示区别,由于所有属性名都是同名属性,因此必须使用别名前缀,查询的多个数据表都是自身。
57,例:
求每一门课程的间接先修课。
直观的查找过程,FIRST,SECOND,SELECTFIRST.C#,SECOND.PC#FROMCFIRST,CSECONDWHEREFIRST.PC#=SECOND.C#;,查询结果为:
C#PC#,SQL语句为:
C4C1,C5C2,C3,C2,2、自身连接,58,3、外连接,普通连接只输出满足连接条件的元组外连接以指定表为连接主体,将主体表中不满足连接条件的元组一起输出。
留住左表中的信息被称为左连接,留住右表中的数据被称为右连接。
59,SQL实现,左外连接(LeftOuterJoin)Select*FromStudentLeftOuterJoinSConStudent.sno=SC.sno;右外连接(RightOuterJoin)Select*FromStudentRightOuterJoinSConStudent.sno=SC.sno;,60,4、复合条件连接,前面三种查询中WHERE中的条件都是单条件,如果是多个连接条件就是复合条件连接。
如S,SC,C表的等值连接可以写为:
SELECTS.*,SC.*,C.*FROMS,SC,CWHERES.Sno=SC.SnoANDSC.Cno=C.Cno,61,三、嵌套查询,一个SELECT-FROM-WHERE语句称为一个查询块。
将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询。
SELECTSname外层查询/父查询FROMStudentWHERESnoIN(SELECTSno内层查询/子查询FROMSCWHERECno=2);,62,嵌套查询种类,1、带有IN谓词的子查询2、带有比较运算符的子查询3、带有ANY或ALL谓词的子查询4、带有EXISTS谓词的子查询,63,例:
求选修了课程名为J的学生学号和姓名。
S:
S#SNSDSA,C:
C#CNPC#,在C中找课程J的编号,在SC中找选修该课的学号,在S中找选修该课的学生姓名,S1ACS20S2BCS21S3CMA19S4DCI19S5EMA20S6FCS22,C1GC2HC1C3IC1C4JC2C5KC4,S1C1AS1C2AS1C3AS1C5BS2C1BS2C2CS2C4CS3C2BS3C3CS3C4BS4C3BS4C5DS5C2CS5C3BS5C5BS6C4AS6C5A,1、带有IN谓词的子查询,分析:
64,S:
S#SNSDSA,C:
C#CNPC#,在C中找课程J的编号,在SC中找选修该课的学号,在S中找选修该课的学生姓名,S1ACS20S2BCS21S3CMA19S4DCI19S5EMA20S6FCS22,C1GC2HC1C3IC1C4JC2C5KC4,S1C1AS1C2AS1C3AS1C5BS2C1BS2C2CS2C4CS3C2BS3C3CS3C4BS4C3BS4C5DS5C2CS5C3BS5C5BS6C4AS6C5A,SELECTC#FROMCWHERECN=J;,SELECTS#FROMSCWHEREC#IN(C4);,SELECTS#,SNFROMSWHERES#IN(S2,S3,S6);,65,SELECTS#FROMSCWHEREC#IN(C4);,SELECTS#,SNFROMSWHERES#IN(S2,S3,S6);,最后的查询语句:
SELECTC#FROMCWHERECN=J,(,),(,);,在C中找课程J的编号,在SC中找选修该课的学号,在S中找选修该课的学生姓名,SELECTC#FROMCWHERECN=J;,SELECTS#FROMSCWHEREC#IN(C4);,SELECTS#,SNFROMSWHERES#IN(S2,S3,S6);,66,最后的查询语句:
说明:
(1)嵌套查询由内向外处理
(2)SQL允许多层嵌套(3)嵌套查询中最常用的谓词是IN(4)嵌套查询层次分明、容易理解,67,当用户确切知道内层查询的结果是单值(只有一个元组,且该元组只有一个字段)时,可将外层查询的某字段与内层查询的结果用、=、=、!
=等比较运算符进行比较。
例:
求张三选修的课程名称及成绩(设没有同名学生)SELECTC.CN,SC.GFROMC,SCWHEREC.C#=SC.C#ANDSC.S#=(SELECTS#FROMSWHERESN=张三);,S(S#,SN,SD,SA)C(C#,CN,PC#)SC(S#,C#,G),子查询必须出现在比较符之后。
2、带有比较运算符的子查询,68,3、带有ANY或ALL谓词的子查询,子查询返回多值时,配合比较运算符使用ANY:
任意一个值,或者说某一个值ALL:
所有值ANY大于子查询结果中的某个值ALL大于子查询结果中的所有值=ANY大于等于子查询结果中的某个值=ALL大于等于子查询结果中的所有值)ANY不等于子查询结果中的某个值!
=(或)ALL不等于子查询结果中的任何一个值,69,例:
查询比信息系任意一个学生年龄小的学生姓名和年龄。
SELECTSname,SageFROMStudentWHERESageALL(SELECTSageFROMStudentWHERESdept=IS),WHERESageSELECTMIN(Sage)FROMStudentWHERESdept=IS),70,ANY和ALL谓词有时可以用集函数实现ANY与ALL与集函数的对应关系,用集函数实现子查询通常比直接用ANY或ALL查询效率要高,71,EXISTS谓词存在量词带有EXISTS谓词的子查询不返回任何数据,只产生逻辑真值“true”或逻辑假值“false”。
若内层查询结果非空,则返回真值若内层查询结果为空,则返回假值由EXISTS引出的子查询,其目标列表达式通常都用*,因为带EXISTS的子查询只返回真值或假值,给出列名无实际意义,4、带有EXISTS谓词的子查询,72,相当于一个变量,根据它的值处理内层查询,S中有多少个学号,内层查询就进行多少次,称为相关子查询:
查询条件依赖于外层查询中某个值,等价于:
SELECTS.SNFROMS,SCWHERES.S#=SC.S#ANDSC.G=D;,SELECTSNFROMSWHEREEXISTS(SELECT*FROMSCWHERES#=S.S#ANDG=D);,例:
求至少一门不及格的学生姓名。
73,四、集合查询,标准SQL直接支持的集合操作种类并操作(UNION)一般商用数据库支持的集合操作种类并操作(UNION)交操作(INTERSECT)差操作(MINUS),74,并操作,形式UNION
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 标准 语言 SQL