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

1数据库原理及应用数据库原理及应用PrincipleandApplicationofDatabase第三章关系数据库标准语言SQL2数据库原理及应用学习目标了解SQL语言的功能和特点掌握SQL的数据定义掌握SQL的单表查询3数据库原理及应用SQL(StructuredQueryLanguage)语言是1974年由Boyce和Chamberlin提出的。1975年--1979年在IBM公司SanJoseResearchLaboratory研制的SystemR上实现了这种语言。由于它功能丰富、使用灵活、表达能力强、语言简洁易学，所以深受用户欢迎和计算机工业界的青睐。1986年10月美国国家标准局ANSI的数据库委员会X3H2批准SQL作为关系数据库的美国标准，同年公布了SQL标准文本(简称SQL-86)。1987年国际标准化组织ISO通过了这一标准。1989年ANSI公布了SQL-89标准。1992年ANSI公布了SQL-92(SQL2)标准。1999年ANSI公布了SQL-99(SQL3)标准。4数据库原理及应用3.1SQL概述SQL的功能：数据定义、数据操纵和数据控制。SQL的特点综合统一：SQL语言集数据定义语言DDL、数据操纵语言DML、数据控制语言DCL的功能于一体。高度非过程化：非关系数据模型的数据操纵语言是面向过程的语言，操作必须指明存取路径；而用SQL语言进行数据操作，只要提出“做什么”，无须指明“怎么做”，因此无需了解存取路径。面向集合的操作方式：非关系数据模型采用的是面向记录的操作方式，操作对象是一条记录；而SQL语言采用的集合操作方式，不仅操作对象、查询结果可以是元组的集合，而且一次插入、删除、修改操作的对象也可以是元组的集合。以同一种语法结构提供两种使用方法：SQL语言既是自含式语言，能独立地用于联机交互；又是嵌入式语言，能嵌入到高级语言中进行混合编程。5数据库原理及应用表3.1SQL语言的动词SQL功能动词数据定义CREATE，DROP，ALTER数据查询SELECT数据操纵INSERT，UPDATEDELETE数据控制GRANT，REVOKE•语言简洁，易学易用：完成核心功能只用9个动词：6数据库原理及应用3.2数据定义定义、修改与删除基本表定义基本表CREATETABLE表名(列名数据类型[列级完整性约束条件][,列名数据类型[列级完整性约束条件]]…[,表级完整性约束条件])；其中：表名为所要定义的基本表的名字，列名为组成该表的各个属性(列)，列级完整性约束条件为涉及相应属性列的完整性约束条件，表级完整性约束条件为涉及一个或多个属性列的完整性约束条件。常用的完整性约束有：主码约束PRIMARYKEY、唯一性约束UNIQUE、非空值约束NOTNULL、参照完整性约束FOREIGNKEYREFERENCES。7数据库原理及应用[例1]建立一个“学生”表Student，它由学号Sno、姓名Sname、性别Ssex、年龄Sage、系别Sdept五个属性组成。其中学号不能为空，值是唯一的，并且姓名取值也唯一。CREATETABLEStudent(SnoCHAR(5)NOTNULLUNIQUE，SnameCHAR(20)UNIQUE，SsexCHAR(1)，SageINT，SdeptCHAR(15))；SnoSnameSsexSageSdept字符型,长度为5,不能为空字符型,长度为20字符型长度为1整数字符型长度为158数据库原理及应用修改基本表ALTERTABLE表名[ADD新列名数据类型[完整性约束]][DROP完整性约束名][DROPcolumn列名][MODIFY列名数据类型]；其中：表名为要修改的基本表，ADD子句为增加新列和新的完整性约束条件，DROP子句为删除指定的完整性约束条件，MODIFY子句为用于修改列名和数据类型。[例2]向Student表增加“入学时间”列，其数据类型为日期型。ALTERTABLEStudentADDScomeDATE；不论基本表中原来是否已有数据，新增加的列一律为空值。9数据库原理及应用[例3]将年龄的数据类型改为半字长整数。ALTERTABLEStudentMODIFYSageSMALLINT；修改原有的列定义有可能会破坏已有数据。[例4]删除学生姓名必须取唯一值的约束。ALTERTABLEStudentDROPUNIQUE(Sname)；SQL没有提供删除属性列的语句，用户只能间接实现这一功能：先把表中要保留的列及其内容复制到一个新表中，然后删除原表，再将新表重命名为原表名。但有的DBMS提供直接删除属性列的语句，如：ALTERTABLEStudentDropScome；10数据库原理及应用删除基本表DROPTABLE表名;基本表定义一旦删除，表中的数据、表上建立的索引和视图都将自动删除。但Oracle中删除基本表后建立在此表上的视图仍保留在数据字典中，但用户引用时就出错。[例5]删除Student表。DROPTABLEStudent;建立与删除索引建立索引是加快查询速度的有效手段，建立与删除索引由DBA或表的属主负责完成，但有些DBMS自动在建立PRIMARYKEY或UNIQUE列上的索引。11数据库原理及应用建立索引CREATE[UNIQUE][CLUSTER]INDEX索引名ON表名(列名[次序][,列名[次序]]…)；用表名指定要建索引的基本表。索引可以建立在该表的一列或多列上，各列名之间用逗号分隔用次序指定索引值的排列次序，升序ASC，降序DESC。缺省ASC。UNIQUE表明此索引的每一个索引值只对应唯一的数据记录。CLUSTER表示要建立的索引是聚簇索引。[例6]为学生-课程数据库中Student,Course,SC三个表建立索引。其中Student表按学号升序建唯一索引，Course表按课程号升序建唯一索引，SC表按学号升序和课程号降序建唯一索引。CREATEUNIQUEINDEXStusnoONStudent(Sno)；CREATEUNIQUEINDEXCoucnoONCourse(Cno)；CREATEUNIQUEINDEXSCnoONSC(SnoASC，CnoDESC)；12数据库原理及应用唯一索引：对于已含重复值的属性列不能建UNIQUE索引，对某个列建立UNIQUE索引后，插入新记录时DBMS会自动检查新记录在该列上是否取了重复值，这相当于增加了一个UNIQUE约束。聚簇索引：建立聚簇索引后，基表中数据也需要按指定的聚簇属性值的升序或降序存放，即聚簇索引的索引项顺序与表中记录的物理顺序一致。例：CREATECLUSTERINDEXStusnameONStudent(Sname)；在Student表的Sname列上建立一个聚簇索引，而且Student表中的记录将按照Sname值的升序存放。在一个基本表上最多只能建立一个聚簇索引，聚簇索引对于某些类型的查询，可以提高查询效率，聚簇索引在很少对基表进行增删操作或很少对其中的变长列进行修改操作的场合下非常适用。13数据库原理及应用删除索引DROPINDEX索引名；删除索引时，系统会从数据字典中删去有关该索引的描述。[例7]删除Student表的Stusname索引。DROPINDEXStusname；14数据库原理及应用3.3查询概述查询语句格式：SELECT[ALL|DISTINCT]目标列表达式[，目标列表达式]…FROM表名或视图名[，表名或视图名]…[WHERE条件表达式][GROUPBY列名1[HAVING条件表达式]][ORDERBY列名2[ASC|DESC]]；其中：SELECT子句指定要显示的属性列；FROM子句指定查询对象(基本表或视图)；WHERE子句指定查询条件；GROUPBY子句对查询结果按指定列的值分组，该属性列值相等的元组为一个组，通常会在每组中作用集函数；HAVING短语筛选出只有满足指定条件的组；ORDERBY子句对查询结果表按指定列值的升序或降序排序。15数据库原理及应用StudentCnoCnameCpnoCcredit1数据库542数学23信息系统144操作系统635数据结构746数据处理27C语言64CourseScSnoCnoGrade950011929500128595001388950022909500238095001950049500395002Sno李勇张立王敏刘晨Sname男男女女Ssex20191819SageCSISMAISSdept示例数据库：学生-课程数据库16数据库原理及应用单表查询：查询仅涉及一个表，是一种最简单的查询操作。选择表中的若干列查询指定列或全部列[例1]查询全体学生的学号与姓名。SELECTSno，SnameFROMStudent；[例2]查询全体学生的姓名、学号、所在系。SELECTSname，Sno，SdeptFROMStudent；[例3]查询全体学生的详细记录。SELECTSno，Sname，Ssex，Sage，SdeptFROMStudent；或SELECT*FROMStudent；17数据库原理及应用查询经过计算的值：SELECT子句的目标列表达式为算术表达式、字符串常量、函数、列别名等。[例4]查全体学生的姓名及其出生年份。SELECTSname，2000-SageFROMStudent；输出结果：Sname2000-Sage----------------------李勇1976刘晨1977王名1978张立197818数据库原理及应用[例5]查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名。SELECTSname，'YearofBirth:'，2000-Sage，ISLOWER(Sdept)FROMStudent；输出结果：Sname'YearofBirth:'2000-SageISLOWER(Sdept)----------------------------------------------李勇YearofBirth:1976cs刘晨YearofBirth:1977is王名YearofBirth:1978ma张立YearofBirth:1977is19数据库原理及应用可以使用列别名改变查询结果的列标题SELECTSnameNAME，'YearofBirth:’BIRTH，2000-SageBIRTHDAY，ISLOWER(Sdept)DEPARTMENTFROMStudent；输出结果：NAMEBIRTHBIRTHDAYDEPARTMENT----------------------------------------------------李勇YearofBirth:1976cs刘晨YearofBirth:1977is王名YearofBirth:1978ma张立YearofBirth:1977is20数据库原理及应用选择表中的若干元组：在WHERE子句中设置查询条件。消除取值重复的行：在SELECT子句中使用DISTINCT短语。[例6]查询选修了课程的学生学号。SELECTSnoFROMSC;或SELECTALLSnoFROMSC;SELECTDISTINCTSnoFROMSC;ALL的结果：SnoDISTINCT的结果：Sno--------------9500195001950019500295001950029500221数据库原理及应用注意：DISTINCT短语的作用范围是所有目标列。例：查询选修课程的各种成绩。错误的写法SELECTDISTINCTCno，DISTINCTGradeFROMSC;正确的写法SELECTDISTINCTCno，GradeFROMSC;22数据库原理及应用表3-3常用的查询条件确定大小：在WHERE子句的比较条件中使用比较运算符。[例8]查询所有年龄在20岁以下的学生姓名及