数据库系统原理及应用教程-数据库课件5

AnIntroductiontoDatabaseSystem第五章关系数据库标准语言SQL5.1SQL概述5.2数据定义5.3查询5.4数据更新5.5视图5.6数据控制5.7嵌入式SQLAnIntroductiontoDatabaseSystem5.1SQL概述SQL的特点1.以同一种语法结构提供两种使用方法2.语言简洁，易学易用3.三级数据模式结构P116AnIntroductiontoDatabaseSystem2.语言简捷，易学易用表5.1SQL语言的动词SQL功能动词数据定义CREATE，DROP，ALTER数据查询SELECT数据操纵INSERT，UPDATEDELETE数据控制GRANT，REVOKEAnIntroductiontoDatabaseSystem5.2数据定义表5.2SQL的数据定义语句操作方式操作对象创建删除修改表CREATETABLEDROPTABLEALTERTABLE视图CREATEVIEWDROPVIEW索引CREATEINDEXDROPINDEXAnIntroductiontoDatabaseSystem5.2.1定义语句格式CREATETABLE表名（列名数据类型[列级完整性约束条件][，列名数据类型[列级完整性约束条件]]…[，表级完整性约束条件]）；表名：所要定义的基本表的名字列名：组成该表的各个属性（列）列级完整性约束条件：涉及相应属性列的完整性约束条件表级完整性约束条件：涉及一个或多个属性列的完整性约束条件AnIntroductiontoDatabaseSystem例题[例1]建立一个“学生”表Student，它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成。其中学号不能为空，值是唯一的，并且姓名取值也唯一。CREATETABLEStudent(SnoCHAR(5)NOTNULLUNIQUE,SnameCHAR(20)UNIQUE,SsexCHAR(1),SageINT,SdeptCHAR(15));AnIntroductiontoDatabaseSystem例题（续）SnoSnameSsexSageSdept　↑　　　↑　　　↑　　　↑　　　↑字符型　字符型　字符型　整数　字符型长度为5长度为20长度为1　长度为15不能为空值AnIntroductiontoDatabaseSystem定义基本表（续）常用完整性约束主码约束：PRIMARYKEY唯一性约束：UNIQUE非空值约束：NOTNULL参照完整性约束PRIMARYKEY与UNIQUE的区别？AnIntroductiontoDatabaseSystem例题（续）[例2]建立一个“学生选课”表SC，它由学号Sno、课程号Cno，修课成绩Grade组成，其中(Sno,Cno)为主码。CREATETABLESC(SnoCHAR(5),CnoCHAR(3),Gradeint,Primarykey(Sno,Cno));AnIntroductiontoDatabaseSystem二、修改基本表ALTERTABLE表名[ADD新列名数据类型[完整性约束]][DROP完整性约束名][MODIFY列名数据类型]；表名：要修改的基本表ADD子句：增加新列和新的完整性约束条件DROP子句：删除指定的完整性约束条件MODIFY子句：用于修改列名和数据类型AnIntroductiontoDatabaseSystem例题[例2]向Student表增加“入学时间”列，其数据类型为日期时间型。ALTERTABLEStudentADDScomeDATETIME；不论基本表中原来是否已有数据，新增加的列一律为空值。AnIntroductiontoDatabaseSystem语句格式（续）删除属性列直接/间接删除把表中要保留的列及其内容复制到一个新表中删除原表再将新表重命名为原表名直接删除属性列:例：ALTERTABLEStudentDropScome;AnIntroductiontoDatabaseSystem[例3]将年龄的数据类型改为半字长整数。ALTERTABLEStudentMODIFYSageSMALLINT;注：修改原有的列定义有可能会破坏已有数据AnIntroductiontoDatabaseSystem例题[例4]删除学生姓名必须取唯一值的约束。ALTERTABLEStudentDROPUNIQUE(Sname);AnIntroductiontoDatabaseSystem三、删除基本表DROPTABLE表名;基本表删除数据、表上的索引都删除表上的视图往往仍然保留，但无法引用删除基本表时，系统会从数据字典中删去有关该基本表及其索引的描述AnIntroductiontoDatabaseSystem例题[例5]删除Student表DROPTABLEStudent;AnIntroductiontoDatabaseSystem5.2.2建立与删除索引建立索引是加快查询速度的有效手段建立索引DBA或表的属主（即建立表的人）根据需要建立有些DBMS自动建立以下列上的索引PRIMARYKEYUNIQUE维护索引DBMS自动完成使用索引DBMS自动选择是否使用索引以及使用哪些索引AnIntroductiontoDatabaseSystem一、建立索引语句格式CREATE[UNIQUE][CLUSTER]INDEX索引名ON表名(列名[次序][,列名[次序]]…)；用表名指定要建索引的基本表名字索引可以建立在该表的一列或多列上，各列名之间用逗号分隔用次序指定索引值的排列次序，升序：ASC，降序：DESC。缺省值：ASCUNIQUE表明此索引的每一个索引值只对应唯一的数据记录CLUSTER表示要建立的索引是聚簇索引AnIntroductiontoDatabaseSystem例题[例6]为学生-课程数据库中的Student，Course，SC三个表建立索引。其中Student表按学号升序建唯一索引，Course表按课程号升序建唯一索引，SC表按学号升序和课程号降序建唯一索引。CREATEUNIQUEINDEXStusnoONStudent(Sno);CREATEUNIQUEINDEXCoucnoONCourse(Cno);CREATEUNIQUEINDEXSCnoONSC(SnoASC，CnoDESC);AnIntroductiontoDatabaseSystem建立索引（续）唯一值索引对于已含重复值的属性列不能建UNIQUE索引对某个列建立UNIQUE索引后，插入新记录时DBMS会自动检查新记录在该列上是否取了重复值。这相当于增加了一个UNIQUE约束AnIntroductiontoDatabaseSystem建立索引（续）聚簇索引建立聚簇索引后，基表中数据也需要按指定的聚簇属性值的升序或降序存放。也即聚簇索引的索引项顺序与表中记录的物理顺序一致例：CREATECLUSTERINDEXStusnameONStudent(Sname);在Student表的Sname（姓名）列上建立一个聚簇索引，而且Student表中的记录将按照Sname值的升序存放AnIntroductiontoDatabaseSystem建立索引（续）在一个基本表上最多只能建立一个聚簇索引聚簇索引的用途：对于某些类型的查询，可以提高查询效率聚簇索引的适用范围很少对基表进行增删操作很少对其中的变长列进行修改操作AnIntroductiontoDatabaseSystem二、删除索引DROPINDEX索引名；删除索引时，系统会从数据字典中删去有关该索引的描述。[例7]删除Student表的Stusname索引。DROPINDEXStusname;AnIntroductiontoDatabaseSystem5.3查询5.3.1概述5.3.2单表查询5.3.3连接查询5.3.4嵌套查询5.3.5集合查询AnIntroductiontoDatabaseSystem5.3.1概述语句格式SELECT[ALL|DISTINCT]目标列表达式[，目标列表达式]…FROM表名或视图名[，表名或视图名]…[WHERE条件表达式][GROUPBY列名1[HAVING条件表达式]][ORDERBY列名2[ASC|DESC]]；AnIntroductiontoDatabaseSystem语句格式SELECT子句：指定要显示的属性列FROM子句：指定查询对象(基本表或视图)WHERE子句：指定查询条件GROUPBY子句：对查询结果按指定列的值分组，该属性列值相等的元组为一个组。通常会在每组中作用集函数。HAVING短语：筛选出只有满足指定条件的组ORDERBY子句：对查询结果表按指定列值的升序或降序排序AnIntroductiontoDatabaseSystem示例数据库学生-课程数据库学生表：Student(Sno，Sname，Ssex，Sage，Sdept)课程表：Course(Cno，Cname，Cpno，Ccredit)学生选课表：SC(Sno，Cno，Grade)AnIntroductiontoDatabaseSystem5.3.2单表查询查询仅涉及一个表，是一种最简单的查询操作一、选择表中的若干列二、选择表中的若干元组三、对查询结果排序四、使用集函数五、对查询结果分组AnIntroductiontoDatabaseSystem查询指定列[例1]查询全体学生的学号与姓名。SELECTSno,SnameFROMStudent;[例2]查询全体学生的姓名、学号、所在系。SELECTSname,Sno,SdeptFROMStudent;AnIntroductiontoDatabaseSystem查询全部列[例3]查询全体学生的详细记录。SELECTSno,Sname,Ssex,Sage,SdeptFROMStudent;或SELECT*FROMStudent;AnIntroductiontoDatabaseSystem3.查询经过计算的值SELECT子句的目标列表达式为表达式算术表达式字符串常量函数列别名等AnIntroductiontoDatabaseSystem3.查询经过计算的值[例4]查全体学生的姓名及其出生年份。SELECTSname,2006-SageFROMStudent;输出结果：Sname2006-Sage----------------------李勇1976刘晨1977王名1978张立1978AnIntroductiontoDatabaseSystem3.查询经过计算的值[例5]查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名。SELECTSname,'YearofBirth:',2000-Sage,ISLOWER(Sdept)FROMStudent;AnIntroductiontoDatabaseSystem例题（续）输出结果：Sname'YearofBirth:'2000-SageISLOWER(Sdept)----------------------------------------------李勇YearofBirth:1976cs刘晨YearofBirth:1977is王名YearofBirth:1978ma张立YearofBirth:1977isAnIntroductiontoDatabaseSystem[例5.1]使用列别名改变查询结果的列标题SELECTSnameNAME,'YearofBirth:’BIRTH,2000-SageBIRTHDAY,ISLOWER(Sdept)DEPARTMENTFROMStudent