数据库系统原理SHUJUKUXITONGYUANLI中国计量学院信息学院SHUJUKUXITONGYUANLIChinaJiliangUniversity2011第3章关系数据库标准语言第三章本章重要概念(1)SQL语言简介:SQL发展历史与特点、组成结构(2)SQL的数据定义:SQL模式、基本表和索引的创建和撤销。(3)SQL的数据查询;SELECT语句的句法,SELECT语句的三种形式及各种限定,基本表的联接操作,SQL3中的递归查询。第三章本章重要概念(4)SQL的数据操纵:添加、更新和删除记录。(5)SQL数据控制:对数据库用户或角色授权、收权(6)视图管理:创建、修改和删除视图,对视图更新操作的限制。(6)嵌入式SQL:运行环境,使用规定,使用技术,卷游标,动态SQL语句。第三章本章概述SQL全称是“结构化查询语言(StructuredQueryLanguage)”,是一种通用的功能极强的关系数据库标准语言。SQL语言具有结构简洁,功能强大,简单易学,被确定为关系数据库系统的国际标准。本章详细介绍SQL的核心部分内容:数据定义、数据查询、数据更新和嵌入式SQL。第三章3.1SQL语言简介SQL发展历史与特点SQL语言特点第三章3.1.1SQL语言简介SQL语言是当前最为成功、应用最为广泛的关系数据库语言,其发展主要经历了以下几个阶段:1)1974年由CHAMBERLIN和BOYEE提出,当时称为SEQUEL(STUCTUREDENGLISHQUERYLANGUAGE);2)IBM公司对其进行了修改,并用于其SYSTEMR关系数据库系统中;第三章3)1981年IBM推出其商用关系关系数据库SQL/DS,并将其名字改为SQL,由于SQL语言功能强大,简洁易用,因此得到了广泛的使用;4)今天广泛应用于各种大型数据库,如SYBASE、MicrosoftSQLServer、ORACLE、DB2等,也用于各种小型数据库,如FOXPRO、ACCESS。第三章SQL语言特点非过程化语言SQL是一个非过程化的语言,因为它一次处理一个记录,对数据提供自动导航。SQL允许用户在高层的数据结构上工作,而不对单个记录进行操作,可操作记录集。统一的语法结构SQL为许多任务提供了命令,包括:●查询数据●在表中插入、修改和删除记录●建立、修改和删除数据对象●控制对数据和数据对象的存取●保证数据库一致性和完整性是所有关系数据库的公共语言第三章3.1.2SQL的组成结构SQL事实上是操作和检索关系型数据库的标准语言,它允许程序员和数据库管理员做如下的工作:更改数据库的结构;更改系统的安全设置;增加用户对数据库或表的许可权限;在数据库中检索需要的信息;对数据库的信息进行更新;第三章1.数据定义语言(DataDefinitionLanguage,简称DDL),DDL用于定义数据库的逻辑结构,是对关系模式一级的定义,包括基本表、视图及索引的定义。2.数据查询语言(DataQueryLanguage,简称DQL),DQL用于查询数据。SQL主要定义了以下四类语言:第三章3.数据操纵语言(DataManipulationLanguage,简称DML),DML用于对关系模式中的具体数据的增、删、改等操作。4.数据控制语言(DataControlLanguage,简称DCL),DCL用于数据访问权限的控制。SQL语言的组成对应的命令动词建表3.1第三章SQL的各部分对应的命令动词表SQL组成部分命令动词数据定义(DDL)CREATE,DROP,ALTER数据查询(DQL)SELECT数据操纵(DML)INSERT,UPDATE,DELETE数据控制(DCL)GRANT,REVOKESQL语言的组成对应的命令动词表3.1第三章3.2SQL数据定义SQL支持关系数据库的外模式、模式、内模式这三级模式结构,如图3.1所示。外模式:由视图和部分基本表构成。视图是为满足用户和应用程序的数据格式要求而定义的,当基本表不适合于用户直接查询的操作要求时,需要定义视图,以便用户的查询操作。模式:由全体基本表构成。基本表是本身独立的表,SQL中一个关系对应一个基本表。内模式:由数据库的存储文件和他们的索引文件构成。在SQL中,一个关系对应一个表;一个或多个表对应一个存储文件;一个表可以带若干个索引。存储文件的逻辑结构组成了关系数据库的内模式。第三章SQL基本表b3基本表b4存储文件f2存储文件f3视图v1视图v2基本表b1基本表b2存储文件f1外模式模式内模式图3.1数据库的三级模式结构第三章3.2.1数据库模式的定义1.数据库模式的创建对于SQL,数据库的创建用CREATE语句实现,典型的数据库创建语句如下:CREATEDATABASE数据库名【参数…】例3.1创建一个名称为“教务管理系统”的数据库,参数保持系统默认。解:CREATEDATABASE教务管理系统第三章原先所建数据库进行删除,此时对应的语句为DROP语句,具体语法如下:DROPDATABASE数据库名例3.2删除”教务管理系统”数据库.解:DROPDATABASE教务管理系统2.数据库的删除第三章1.创建基本表SQL语言创建基本表的一般格式为:CREATETABLE表名(列名数据类型[列级完整性约束],…[表级完整性约束]);3.2.2基本表的定义第三章例3.3创建一个学生表S,包括如下几个字段:Snum、Sname、Ssex、Sage、Dnum,其中Snum是学号,Sname是姓名,Ssex是性别,Sage是年龄,Dnum是学生所在的院系编号。Dnum是已经存在的院系表D的主键,是学生表S的外键。Snum是主键,不能为空值且是唯一。第三章解:CREATETABLES(SnumCHAR(4)NOTNULLUNIQUE,SnameCHAR(8),SsexCHAR(2),SageSMALLINT,Dnumchar(4),PRIMARYKEY(Snum),FOREIGNKEY(Dnum)REFERENCESD(Dnum));第三章这里定义的学生表有六个属性,其中NOTNULL和UNIQUE是列级完整性约束,作用在Snum上,NOTNULL要求Snum不能为空值,UNIQUE要求Snum取值唯一,而PRIMARYKEY(Snum)是表级完整性约束,FOREIGNKEY(Dnum)REFERENCESD(Dnum)则说明该学生表S的字段Dnum为外键,参照引用了院系表D的主键Dnum。第三章定义一个课程表C(Cnum,Cname,Cfreq)和一个选修表SC(Snum,Cnum,score),其中Cnum是课程号,Cname是课程名称,Cfreq是该门课程的学分,Snum是学号,Score是成绩。Cnum是C表的主键,(Snum,Cnum)是SC表的主键。要求学分Cfreq可以是空值或者是0到10之间的定点数。而SC表中的Cnum和Snum是外键,分别参照引用了课程表C和学生表S的主键。例3.4第三章CREATETABLEC(CnumCHAR(4),CnameCHAR(20),CfreqNUMERIC(2,1),PRIMARYKEY(Cnum),CHECK((CfreqISNULL)OR(CfreqBETWEEN0AND10)));解:第三章CREATETABLESC(SnumCHAR(4),CnumCHAR(4),scoreSMALLINT,PRIMARYKEY(Snum,Cnum),FOREIGNKEY(Snum)REFERENCESS(Snum),FOREIGNKEY(Cnum)REFERENCESC(Cnum));第三章ALTERTABLE语句的语法如下:ALTERTABLE表名[ADD[COLUMN]新列名数据类型[完整性约束]][ADD完整性约束[,完整性约束]][DROPCOLUMN列名[,列名…]][DROP完整性约束名[,完整性约束名…]][ALTER[COLUMN]列名数据类型];2.修改基本表1第三章2.修改基本表2ALTERTABLE语句可以实现如下功能:(1)加入一列到已经存在的表中;(2)修改已经存在的表中的某一列;(3)删除表中已经存在的一列;(4)加入新的约束到已经存在的表中;(5)删除表中已经存在的约束;第三章例3.6在已存在的学生表S中增加一个家庭地址Saddress的新的属性列:解:ALTERTABLESADDSaddressVARCHAR(50);要注意的是,新增加的属性列的值都是空值。(1)增加一列第三章例3.7修改学生表S中Sname的数据类型,变为CHAR类型。解:ALTERTABLESalterCOLUMNSnameCHAR(15);(2)修改列的属性第三章(3)删除列例3.8删除学生表S中的Sage字段。解:ALTERTABLESDROPCOLUMNSage;第三章例3.9在学生表S中限定年龄Sage小于100大于0.解:ALTERTABLESADDCONSTRAINTChk_SageCHECK(Sage0andSage100);(4)增加完整性约束第三章(5)删除完整性约束例3.10删除学生表S中完整性约束Uni_Snum和Chk_Sage.解:ALTERTABLESDROPUni_Snum,Chk_Sage;第三章SQL提供了一个可以从数据库彻底地移去某个表的命令DROPTABLE,它可以从数据库中删除一个指定的表以及与之相关联的索引和视图,需要注意的是,一旦这个命令发出以后就没有办法可以取消它。SQL语言删除基本表的一般格式为:DROPTABLE表名[CASCADE|RESTRICT];例3.11删除学生表S。DROPTABLES3.删除基本表第三章1.建立索引SQL语言建立索引的一般格式为:CREATE[UNIQUE][CLUSTER]INDEX索引名ON表名(列名[次序][,列名[次序]]…);3.2.3索引的定义第三章建立索引示例例3.12在学生表S的姓名列Sname上建索引CREATEINDEXidx_SnameONS(Sname);例3.13在选修表SC的课程号Cnum和成绩Score上建索引,要求课程号为主索引且为升序,课程号相同时成绩为降序。CREATEINDEXidx_sc_sONSC(Cnum,ScoreDESC);第三章SQL语言删除索引的一般格式为:DROPINDEX索引名;例3.13在学生表S上的索引idx_Sname。DROPINDEXidx_Sname;2.删除索引3.2.3索引的定义第三章3.3SQL数据查询SELECT命令的格式与基本使用SELECT语句是SQL语言中功能最强大、用途最广泛的数据库操纵语句,也是关系运算理论在SQL语言中的主要体现。一个完整的SELECT语句包括SELECT,FROM,WHERE,GROUPBY和ORDERBY共五个子句,前两个必不可少,后面三个可以省略。第三章SELECT语句的完整格式为:SELECT[DISTINCT]目标列组FROM基本表名或视图名序列[WHERE行条件表达式][GROUPBY分组列名[HAVING组条件表达式]][ORDERBY排序列名[ASC|DESC]]第三章该SELECT格式的含义是:根据WHERE子句的行条件表达式,从FROM子句指定的基本表或视图中找出满足条件的行,再按SELECT子句中的目标列表达式选出元组中的属性值形成结果表。第三章如果有GROUP子句,则将结果按分组列名的值进行分组,该属性列值相等的元组为一个组,每个组产生结果表中的一条记录。通常会在每组中进行聚合操作。如果GROUP子句带HAVING短语,则只有满足指定条件的组才予输出。如果有ORDER子句,则结果表还要按排序列名的值的升序或降序排序。