关系数据库标准语言SQL.ppt

第三章关系数据库标准语言SQL,3.1SQL概述3.2学生-课程数据库3.3数据定义3.4数据查询3.5数据更新3.6视图3.7小结,3.1SQL概述,1970年，美国IBM研究中心的E.F.Codd连续发表多篇论文，提出关系模型。1972年，IBM公司开始研制实验型关系数据库管理系统SYSTEMR，配制的查询语言称为SQUARE语言，在语言中使用了较多的数学符号。1974年，Boyce和Chamberlin把SQUARE修改为SQL语言(StructuredQueryLanguage)，即“结构式查询语言”，SQL的发音仍为“sequel”。,1986年10月美国国家标准局（ANSI）数据库委员会批准了SQL作为关系数据库语言的美国标准。同年公布了SQL标准文本（简称SQL-86）。1987年6月，国际标准化组织（ISO）将其采纳为国际标准。在此之后，对SQL进行修改和扩充的标准化工作不断进行着，相继出现了SQL-89、SQL2（1992）和SQL3（1999）、SQL2003。,3.1SQL概述,SQL的特点1.综合统一2.高度非过程化3.面向集合的操作方式4.以同一种语法结构提供两种使用方法5.语言简洁，易学易用,SQL数据库的体系结构,从图中可以看出，模式与基本表相对应，外模式与视图相对应，内模式对应于存储文件。基本表和视图都是关系。,1基本表（BaseTable）基本表是模式的基本内容。每个基本表都是一个实际存在的关系。2视图（View)视图是外模式的基本单位，用户通过视图使用数据库中基于基本表的数据（基本表也可作为外模式使用）。一个视图虽然也是一个关系，但是它与基本表有着本质的区别。任何一个视图都是从已有的若干关系导出的关系，它只是逻辑上的定义，实际并不存在。在导出时，给出一个视图的定义（从哪几个关系中，根据什么标准选取数据，组成一个什么名称的关系等），,视图与基本表,此定义存放在数据库（数据字典）中，但没有真正执行此定义（并未真正生成此关系）。当使用某一视图查询时，将实时从数据字典中调出此视图的定义；根据此定义以及现场查询条件，从规定的若干关系中取出数据，组织成查询结果，展现给用户。因此，视图是虚表，实际并不存在，只有定义存放在数据字典中。当然，用户可在视图上再定义视图，就像在基本表上定义视图一样，因为视图也是关系。,视图与基本表（续）,3存储文件存储文件是内模式的基本单位。每一个存储文件存储一个或多个基本表的内容。一个基本表可有若干索引，索引也存储在存储文件中。存储文件的存储结构对用户是透明的。下面将介绍SQL的基本语句。各厂商的RDBMS实际使用的SQL语言，与标准SQL语言都有所差异及扩充。因此，具体使用时，应参阅实际系统的有关手册。,3.2学生-课程数据库,本章后续实例都以该数据库为操作对象参见教材P82,3.3数据定义,3.3.1模式的定义与删除,一、定义模式定义语句createschemaatuhorization如果没有指定模式名，那么一用户名为模式名该命令的执行必须要求用户具有DBA权限,例1定义学生-课程模式S-Tcreateschema“s-t”authorizationwang;例2createschemaauthorizationwang定义模式实际上是定义了一个命名空间，在这个空间中可以进一步定义该模式包含的数据库对象，如基本表、视图、索引等。,Createschemaauthorization|例3createschemaTESTauthorizationzhangcreatetableTAB1（COL1SMALLINT，COL2INT，COL3CHAR（20），COL4MUMERIC（10，3）COL5DECIMAL（5，2）；,二删除模式DROPSCHEMACascade表示级联删除模式中的数据库对象Restrict表示当模式中存在数据库对象是限制删除模式例4dropschemazhangcascade；,3.3.2基本表的定义删除与修改,CREATETABLE（，）；：所要定义的基本表的名字：组成该表的各个属性（列）：涉及相应属性列的完整性约束条件：涉及一个或多个属性列的完整性约束条件,例题,例1建立一个“学生”表Student，它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成。其中学号不能为空，值是唯一的，并且姓名取值也唯一。CREATETABLEStudent(SnoCHAR(9)primarykey，SnameCHAR(20)UNIQUE，SsexCHAR(2)，SageSMALLINT，SdeptCHAR(20)；,例题（续）,常用完整性约束主码约束：PRIMARYKEY唯一性约束：UNIQUE非空值约束：NOTNULL参照完整性约束PRIMARYKEY与UNIQUE的区别？,例题（续）,例2建立一个“学生选课”表SC，它由学号Sno、课程号Cno，修课成绩Grade组成，其中(Sno,Cno)为主码。CREATETABLESC(SnoCHAR(9),CnoCHAR(4),Gradeint,Primarykey(Sno,Cno),FOREIGNKEY(Sno)REFERENCESStudent(sno),FOREIGNKEY(Cno)REFERENCESCourse(cno);,例3建立“课程”表Coursecreatetablecourse(cnochar(4)primarykey,cnamechar(40),cpnochar(4),Ccreditsmallint,foreignkey(cpno)referencescourse(cno);,数据类型,SQL中域的概念用类型来实现。不同的RDBMS中支持的数据类型不完全相同SQL提供的主要数据类型P86表3.3,模式与表,每个基本表都属于某个模式，一个模式包含多个基本表。定义基本表示定义所属模式的方法1、在表名中明确给出模式名createtable“S-T”.Student();2、在创建模式语句中同时创建表3、设置所属的模式，这样在创建表示不必给出模式名,当用户创建基本表示若没有指定模式，系统根据搜索路径来确定该对象所属的模式使用showsearch_path语句显示当前搜索路径使用setsearch_pathto来设置搜索路径,修改基本表,ALTERTABLEADD完整性约束DROPALTERCOLUMN；：要修改的基本表ADD子句：增加新列和新的完整性约束条件DROP子句：删除指定的列或完整性约束条件ALTERCOLUMN子句：用于修改列名和数据类型,例题,例1向Student表增加“入学时间”列，其数据类型为日期型。ALTERTABLEStudentADDS_entranceDATE；不论基本表中原来是否已有数据，新增加的列一律为空值。,例2增加课程名称必须取唯一值得约束条件altertablecourseaddunique(cname),删除属性列直接/间接删除把表中要保留的列及其内容复制到一个新表中删除原表再将新表重命名为原表名直接删除属性列:(新)例：ALTERTABLEStudentDropS_entrance；,三、删除基本表,DROPTABLErestrict|cascade;基本表删除数据、表上的索引都删除表上的视图往往仍然保留，但无法引用删除基本表时，系统会从数据字典中删去有关该基本表及其索引的描述,Restrict:表示受限删除。欲删除的表不能被其他表的约束引用，不能有视图，不能有触发器，不能有存储过程或函数。Cascade:表示级联删除。表的删除要慎重,例题,例5删除Student表DROPTABLEStudent;,3.2.3建立与删除索引,建立索引是加快查询速度的有效手段建立索引DBA或表的属主（即建立表的人）根据需要建立有些DBMS自动建立以下列上的索引PRIMARYKEYUNIQUE维护索引DBMS自动完成使用索引DBMS自动选择是否使用索引以及使用哪些索引,一、建立索引,语句格式CREATEUNIQUECLUSTERINDEXON(,)；用指定要建索引的基本表名字索引可以建立在该表的一列或多列上，各列名之间用逗号分隔用指定索引值的排列次序，升序：ASC，降序：DESC。缺省值：ASCUNIQUE表明此索引的每一个索引值只对应唯一的数据记录CLUSTER表示要建立的索引是聚簇索引,用户可以在最经常查询的列上进行聚簇索引，而对于经常更新的列则不宜建立这种索引。一个基本表只能建立一个聚簇索引。,例题,例6为学生-课程数据库中的Student，Course，SC三个表建立索引。其中Student表按学号升序建唯一索引，Course表按课程号升序建唯一索引，SC表按学号升序和课程号降序建唯一索引。CREATEUNIQUEINDEXStusnoONStudent(Sno)；CREATEUNIQUEINDEXCoucnoONCourse(Cno)；CREATEUNIQUEINDEXSCnoONSC(SnoASC，CnoDESC)；,建立索引（续）,唯一值索引对于已含重复值的属性列不能建UNIQUE索引对某个列建立UNIQUE索引后，插入新记录时DBMS会自动检查新记录在该列上是否取了重复值。这相当于增加了一个UNIQUE约束,建立索引（续）,聚簇索引建立聚簇索引后，基表中数据也需要按指定的聚簇属性值的升序或降序存放。也即聚簇索引的索引项顺序与表中记录的物理顺序一致例：CREATECLUSTERINDEXStusnameONStudent(Sname)；在Student表的Sname（姓名）列上建立一个聚簇索引，而且Student表中的记录将按照Sname值的升序存放,建立索引（续）,在一个基本表上最多只能建立一个聚簇索引聚簇索引的用途：对于某些类型的查询，可以提高查询效率聚簇索引的适用范围很少对基表进行增删操作很少对其中的变长列进行修改操作,思考,建立索引的时机？,二、删除索引,DROPINDEX；删除索引时，系统会从数据字典中删去有关该索引的描述。例7删除Student表的Stusname索引。DROPINDEXStusname；,3.4查询,3.4.1概述3.4.2单表查询3.4.3连接查询3.4.4嵌套查询3.4.5集合查询3.4.6小结,3.4.1概述,语句格式SELECTALL|DISTINCT，FROM，WHEREGROUPBYHAVINGORDERBYASC|DESC；,一,二/1,二/2,三,五,语句格式,SELECT子句：指定要显示的属性列FROM子句：指定查询对象(基本表或视图)WHERE子句：指定查询条件GROUPBY子句：对查询结果按指定列的值分组，该属性列值相等的元组为一个组。通常会在每组中作用集函数。HAVING短语：筛选出只有满足指定条件的组ORDERBY子句：对查询结果表按指定列值的升序或降序排序,示例数据库,学生-课程数据库学生表：Student(Sno，Sname，Ssex，Sage，Sdept)课程表：Course(Cno，Cname，Cpno，Ccredit)学生选课表：SC(Sno，Cno，Grade),3.4.2单表查询,查询仅涉及一个表，是一种最简单的查询操作一、选择表中的若干列二、选择表中的若干元组三、对查询结果排序四、使用聚集函数五、对查询结果分组,查询指定列,例1查询全体学生的学号与姓名。SELECTSno，SnameFROMStudent；例2查询全体学生的姓名、学号、所在系。SELECTSname，Sno，SdeptFROMStudent；,查询全部列,例3查询全体学生的详细记录。SELECTSno，Sname，Ssex，Sage，SdeptFROMStudent；或SELECT*FROMStudent；,3.查询经过计算的值,SELECT子句的为表达式算术表达式字符串常量函数列别名等,3.查询经过计算的值,例4查全体学生的姓名及其出生年份。SELECTSname，2000-SageFROMStudent；输出结果：Sname2000-Sage-李勇1976刘晨1977王名1978张立1978,3.查询经过计算的值,例5查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名。SELECTSname，YearofBirth:，2000-Sage，ISLOWER(Sdept)FROMStudent；,例题（续）,输出结果：SnameYearofBirth:2000-SageISLOWER(Sdept)-李勇YearofBirth:1976cs刘晨YearofBirth:1977is王名YearofBirth:1978ma张立YearofBirth:1977is,例5.1使用列别名改变查询结果的列标题,SELECTSnameNAME，YearofBirth:BIRTH，2000-SageBIRTHDAY，ISLOWER(Sdept)DEPARTMENTFROMStudent；输出结果：NAMEBIRTHBIRTHDAYDEPARTMENT-李勇YearofBirth:1976cs刘晨YearofBirth:1977is王名YearofBirth:1978ma张立YearofBirth:1977is,二、选择表中的若干元组,消除取值重复的行查询满足条件的元组,1.消除取值重复的行,在SELECT子句中使用DISTINCT短语假设SC表中有下列数据SnoCnoGrade-9500119295001285950013889500229095002380,ALL与DISTINCT,例6查询选修了课程的学生学号。(1)SELECTSnoFROMSC;或(默认ALL)SELECTALLSnoFROMSC;结果：Sno-9500195001950019500295002,例题（续）,(2)SELECTDISTINCTSnoFROMSC;结果：Sno-9500195002,例题（续）,注意DISTINCT短语的作用范围是所有目标列例：查询选修课程的各种成绩错误的写法SELECTDISTINCTCno，DISTINCTGradeFROMSC;正确的写法SELECTDISTINCTCno，GradeFROMSC;,2.查询满足条件的元组,WHERE子句常用的查询条件,(1)比较大小,在WHERE子句的中使用比较运算符=，=，!，!=20；,(2)确定范围,使用谓词BETWEENANDNOTBETWEENAND例10查询年龄在2023岁（包括20岁和23岁）之间的学生的姓名、系别和年龄。SELECTSname，Sdept，SageFROMStudentWHERESageBETWEEN20AND23；,例题（续）,例11查询年龄不在2023岁之间的学生姓名、系别和年龄。SELECTSname，Sdept，SageFROMStudentWHERESageNOTBETWEEN20AND23；,(3)确定集合,使用谓词IN,NOTIN：用逗号分隔的一组取值例12查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别。SELECTSname，SsexFROMStudentWHERESdeptIN(IS，MA，CS);,(3)确定集合,例13查询既不是信息系、数学系，也不是计算机科学系的学生的姓名和性别。SELECTSname，SsexFROMStudentWHERESdeptNOTIN(IS，MA，CS);,(4)字符串匹配,NOTLIKEESCAPE：指定匹配模板匹配模板：固定字符串或含通配符的字符串当匹配模板为固定字符串时，可以用=运算符取代LIKE谓词,用!=或运算符取代NOTLIKE谓词,通配符,%(百分号)代表任意长度（长度可以为0）的字符串例：a%b表示以a开头，以b结尾的任意长度的字符串。如acb，addgb，ab等都满足该匹配串_(下横线)代表任意单个字符例：a_b表示以a开头，以b结尾的长度为3的任意字符串。如acb，afb等都满足该匹配串,ESCAPE短语：,当用户要查询的字符串本身就含有%或_时，要使用ESCAPE短语对通配符进行转义。,例题,1)匹配模板为固定字符串例14查询学号为95001的学生的详细情况。SELECT*FROMStudentWHERESnoLIKE95001；等价于：SELECT*FROMStudentWHERESno=95001；,例题（续）,2)匹配模板为含通配符的字符串例15查询所有姓刘学生的姓名、学号和性别。SELECTSname，Sno，SsexFROMStudentWHERESnameLIKE刘%；,例题（续）,匹配模板为含通配符的字符串（续）例16查询姓欧阳且全名为三个汉字的学生的姓名。SELECTSnameFROMStudentWHERESnameLIKE欧阳_；,例题（续）,匹配模板为含通配符的字符串（续）例17查询名字中第2个字为阳字的学生的姓名和学号。SELECTSname，SnoFROMStudentWHERESnameLIKE_阳%；,例题（续）,匹配模板为含通配符的字符串（续）例18查询所有不姓刘的学生姓名。SELECTSname，Sno，SsexFROMStudentWHERESnameNOTLIKE刘%；,例题（续）,3)使