第三章关系数据库语言SQL

第三章关系数据库语言SQL3.1SQL语言概述3.2SQL数据定义语言3.3SQL查询语言3.4SQL数据操纵语言3.5SQL中的视图3.6嵌入式SQL3.7动态SQL3.8SQL的存储过程3.1SQL语言概述DBMS所提供的语言一般局限于对数据库的操作，称为数据库语言。数据库语言：非完备语言以关系代数为基础的数据库语言是过程语言。以关系演算为基础的数据库语言是非过程语言。过程化/非过程化关系数据库都配有非过程关系数据库语言，其中最成功、应用最广的首推SQL语言。SQL(StructuredQueryLanguage)•1974：IBM－Boyce,Chamberlin提出SEQUEL(StructuredEnglishQueryLanguage)•1981：IBM关系数据库SQL/DS•1986：ANSI美国标准；ISO：SQL－86•1989：SQL－89•1992：SQL－92，SQL2•1999：SQL99SQL支持ANSI/X3/SPARCDBMS三层模式结构外模式对应于视图和部分基本表，模式对应于基本表，内模式对应于存储文件。•SQL组成：数据定义语言（DataDefinitionLanguage）DDL查询语言（QueryLanguage）QL数据操纵语言（DataManipulationLanguage）DML数据控制语言（DataControlLanguage）DCL•SQL的特点：–综合统一–高度非过程化–面向集合的操作方式–以同一种语法结构提供两种使用方式（自含式语言、嵌入式语言）–语言简洁，易学易用（核心功能9个动词：SELECT、CREATE、DROP、ALTER、INSERT、UPDATE、DELETE、GRANT、REVOKE）3.2数据定义语言3.2.1基本数据类型•关系又称表，是关系数据库的基本组成单位。在SQL中，表分为两种：一种叫基表，其数据显式地存储在数据库中；另一种叫视图。视图是一种虚表，它仅有逻辑定义，可以根据其定义从其它表（包括视图）导出。•引入视图的优点：有利于数据库的安全提高数据的逻辑独立性•基本数据类型INT，DEC，FLOAT，CHAR（VAR），BIT（VAR），DATE，TIME…3.2数据定义语言3.2.2基表的定义•一般格式：CREATETABLE表名(列名数据类型[列级完整性约束条件][，列名数据类型[列级完整性约束条件]]···[，表级完整性约束条件])；•语法：P61图例：P61例3－1•定义表的各个属性时需要指明其数据类型及长度。不同的数据库系统支持的数据类型不完全相同。3.2.3基表的修改3.2数据定义语言3.2数据定义语言3.2.3基表的修改1.修改基本表ALTERTABLE表名[ADD新列名数据类型[完整性约束]][DROP完整件约束名][MODIFY列名数据类型];•其中表名，是要修改的基本表，ADD子句用于增加新列和新的完整性约束条件，DROP子句用于删除指定的完整性约束条件，MODIFY子句用于修改原有的列定义，包括修改列名和数据类型。3.2数据定义语言例2:向STUDENT表增加“入学时间”(SCOME)列，其数据类型为日期型ALTERTABLESTUDENTADDSCOMEDATE;不论基本表中原来是否已有数据，新增加的列一律为空值。例3:将学生姓名SNAME的长度增加到30ALTERTABLESTUDENTMODIFYSNAMEVARCHAR(30);修改原有的列定义有可能会破坏已有数据。例4：删除关于学号为主键的约束ALTERTABLESTUDENTDROPUNIQUE(Sname);;3.2数据定义语言2.删除基本表一般格式为：DROPTABLE表名例5：删除STUDENT表DROPTABLESTUDENT基本表定义一旦删除，表中的数据、在此表上建立的索引都将自动被删除掉，因此执行删除操作一定要格外小心。3.2数据定义语言3.增加主键ALTERTABLE表名ADDPRIMARYKEY(列名)列的限制：满足NOTNULL和惟一性条件4.撤销（全部）主键ALTERTABLE表名DROPPRIMARYKEY5.补充定义外键ALTERTABLE表名ADDFOREIGNKEY[外键名]（列名）REFERENCE表名[ONDELETE{RESTRICT|CASCADE|SETNULL}]3.2数据定义语言6.撤销外键定义ALTERTABLE表名DROP外键名7.定义、撤销别名别名（alias）的作用有二：一是用简写的别名代替全名，书写和输入都比较方便。二是因为各个用户对同一数据对象可能有不同的命名。定义和撤消别名的语句格式如下：CREATESYNONYM标识符FOR表创建者.{表名|视图名};DROPSYNONYM标识符;3.2数据定义语言3.2.4索引的建立和删除CREATE[UNIQUE][CLUSTERED]INDEX索引名ON表名(列名[次序][，列名[次序]]···);DROPINDEX索引名;例6为学生．课程数据库中的Student，Course，SC三个表建立索引。其中Stuent表按学号升序建唯一索引，Course表按课程号升序建唯一索引，SC表按学号升序和课程号降序建唯一索引。CREATEUNIQUEINDEXStusnoONStudent(Sno)；CREATEUNIQUEINDEXCoucnoONCourse(Cno)；CREATEUNIQUEINDEXScnoONSC(SnoASC，CnoDESC)；例7删除Student表的Stusname索引DROPINDEXStusname；3.3查询语言数据库查询是数据库的核心操作。SQL语言提供了SELECT语句进行数据库的查询，该语句具有灵活的使用方式和丰富的功能。一般格式：SELECT[ALL|DISTINCT]目标列表达式[,目标列表达式]...FROM表名或视图名[,表名或视图名]...[WHERE条件表达式][GROUPBY列名1[HAVING条件表达式]][ORDERBY列名2[ASC|DESC]];语法图P65图3－3整个SELECT语句的含义是，根据WHERE子句的条件表达式，从FROM子句指定的基本表或视图中找出满足条件的元组，再按SELECT子句中的目标列表达式，选出元组中的属性值形成结果表。如果有GROUP子句，则将结果按列名1的值进行分组，该属性列值相等的元组为一个组，每个组产生结果表中的一条记录。通常会在每组中作用集函数。如果GROUP子句带HAVING短语，则只有满足指定条件的组才予输出。如果有ORDER子句，则结果表还要按列名2的值的升序或降序排序。3.3查询语言关系代数→SelectFromWhere学生—课程数据库个包括三个表。●学生表：Student(Sno，Sname,Ssex，Sage，Sdept)Student由学号(Sno)、姓名(Sname)、性别(Ssex)、年龄(Sage)、所在系(sdept)五个属性组成，其中sno为主键。●课程表：Course(Cno，Cname，Cpno，Credit)Course由课程号(Cno)、课程名(Cname)、先修课号(Cpno)、学分(Credit)四个属性组成，其中Cno为主键。●学生选课表：SC(Sno，Cno，Grade)由学号(Sno)、课程号(Cno)、成绩(Grade)三个属性组成，主键为(sno，cno)。3.3查询语言3.3.1单表查询单表查询指仅涉及一个表的查询。一、选择表中的若干列选择表中的全部列或部分列，这就是投影运算。1、查询指定列例1查询全体学生的学号与姓名。SELECTSno，SnameFROMStudent；例2查询全体学生的姓名、学号、所在系。SELECTSname，Sno，SdeptFROMStudent；3.3查询语言2.查询全部列两种方法：一种方法就是在SELECT关键字后面列出所有列名。另一种方法，如果列的显示顺序与其在基表中的顺序相同，也可以简单地将目标表达式指定为*。例3查询全体学生的详细记录。SELECT*FROMStudent;等价于SELECTSno,Sname,Ssex,Sage,SdeptFROMStudent;3.3查询语言3.查询经过计算的值SELECT子句的目标列表达式不仅可以是表中的属性列，也可以是表达式。例4查全体学生的姓名及其出生年份SELECTSname,2006-Sage，FROMStudent;例5查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名。SELECTSname,’YearofBirth:’,2006-Sage,ISLOWER(Sdept)FROMStudent;3.3查询语言用户可以通过指定别名来改变查询结果的列标题。对于上例，定义如下的列名：SELECTSname,’YearofBirth:’BIRTH,1996-SageBIRTHDAY,ISLOWER(Sdept)DEPARTMENTFROMStudent;3.3查询语言(2)选择表中的若干元组1).消除取值重复的行例7:查所有选修过课的学生的学号SELECTSnoFROMSC;假设SC表中有下列数据SnoCnoGrade---------------------95001192950012859500138895002290950023803.3查询语言例7(续)：执行上面的SELECT语句后，结果为：Sno-------9500195001950019500295002该查询结果里包含了许多重复的行。如果想去掉结果表中的重复行，必须指定DISTINCT短语：SELECTDISTINCT(Sno)FROMSC;执行结果为：Sno-------95001950023.3查询语言二、选择表中的若干元组2.查询满足条件的元组查询满足指定条件的元组可以通过WHERE子句实现。WHERE子句常用的查询条件如表所示。常用的查询条件查询条件谓词------------------------------------------------------------------------------比较＝，，，=，=，!=或等确定范围BETWEENAND,NOTBETWEENAND确定集合IN,NOTIN字符匹配LIKE,NOTLIKE空值ISNULL,ISNOTNULL多重条件AND,OR3.3查询语言2、查询满足条件的元组（1）比较大小用来进行比较的运算符一般包括：＝（等于），（大于），（小于），=（大于等于），=（小于等于），!=或者（不等于）。有些产品中还包括：!（不大于），!（不小于）。逻辑运算符NOT可与比较运算符同用，对条件求非。例8:查计算机系全体学生的名单SELECTSnameFROMStudentWHERESdept='CS';3.3查询语言例9:查所有年龄在20岁以下的学生姓名及其年龄SELECTSname,SageFROMStudentWHERESage20;或SELECTSname,SageFROMStudentWHERENOTSage=20;例10:查考试成绩有不及格的学生的学号SELECTDISTINCTSnoFROMSCWHEREGrade60;•这里使用了DISTINCT短语，当一个学生有多门课程不及格，他的学号也只列一次。3.3查询语言2、查询满足条件的元组（2）确定范围谓词BETWEEN…AND…和NOTBETWEEN…AND…可以用来查找属性值在(或不在)指定范围内的元组，其中BETWEEN后是范围的下限(即低值)，AND后是范围的上限(即高值)。例10查询年龄在20~23岁(包括20岁和23岁)之间的学生的姓名系别和年龄。SELECTSname，Sdept，SageF