数据库系统管理课件(王岚主编)第四章.ppt

数据库系统管理课件(王岚主编)第四章.ppt

《数据库系统管理课件(王岚主编)第四章.ppt》由会员分享，可在线阅读，更多相关《数据库系统管理课件(王岚主编)第四章.ppt（157页珍藏版）》请在冰点文库上搜索。

数据库基础,第四章关系数据库标准语言SQL,数据库基础,【本章要点】,通过本章的学习，可以掌握关系数据库标准语言SQL的数据定义、数据查询、数据更新等操作。

数据库基础,第四章关系数据库标准语言SQL,4.1SQL概述4.2SQL数据定义4.3SQL数据查询4.4SQL数据更新,数据库基础,4.1SQL概述,SQL（结构化查询语言）是StructuredQueryLanguage的缩写。

由于它具有功能丰富、使用灵活、语言简洁等特点，深受计算机用户的欢迎，许多数据库生产厂家推出各自支持SQL的软件。

它是在1974年由Boyce和Chambertin提出来的，1986年被美国国家标准局（ANSI）的数据库委员会批准为关系数据库语言的美国标准，1989年被国际标准化组织（ISO）定为国际标准，使SQL语言成为标准关系数据库语言，1990年我国也颁布了信息处理系统数据库语言SQL，并将其定为国家标准。

数据库基础,4.1SQL概述,SQL是一种介于关系代数和关系演算之间的一种结构化查询语言，它的主要功能包括数据定义、数据操纵及数据控制等方面，数据操纵又可以分为数据检索（查询）和数据更新两个方面。

它是一种综合的、通用的、功能极强的关系数据库语言。

数据库基础,4.1SQL概述,SQL（StructuredQueryLanguage）是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。

SQL同时也是数据库脚本文件的扩展名。

数据库基础,4.1SQL概述,SQL是高级的非过程化编程语言，允许用户在高层数据结构上工作。

它不要求用户指定对数据的存放方法，也不需要用户了解具体的数据存放方式，所以具有完全不同底层结构的不同数据库系统可以使用相同的SQL语言作为数据输入与管理的接口。

它以记录集合作为操作对象，所有SQL语句接受集合作为输入，返回集合作为输出，这种集合特性允许一条SQL语句的输出作为另一条SQL语句的输入，所以SQL语句可以嵌套，这使他具有极大的灵活性和强大的功能，在多数情况下，在其他语言中需要一大段程序实现的功能只需要一个SQL语句就可以达到目的，这也意味着用SQL语言可以写出非常复杂的语句。

数据库基础,4.1SQL概述,结构化查询语言（StructuredQueryLanguage）最早是IBM的圣约瑟研究实验室为其关系数据库管理系统SYSTEMR开发的一种查询语言，它的前身是SQUARE语言。

SQL语言结构简洁，功能强大，简单易学，所以自从IBM公司1981年推出以来，SQL语言得到了广泛的应用。

如今无论是像Oracle、Sybase、Informix、SQLServer这些大型的数据库管理系统，还是像VisualFoxporo、PowerBuilder这些PC上常用的数据库开发系统，都支持SQL语言作为查询语言。

数据库基础,4.1SQL概述,美国国家标准局（ANSI）与国际标准化组织（ISO）已经制定了SQL标准。

ANSI是一个美国工业和商业集团组织，负责开发美国的商务和通讯标准。

ANSI同时也是ISO和InternationalElectrotechnicalCommission（IEC）的成员之一。

ANSI发布与国际标准组织相应的美国标准。

1992年，ISO和IEC发布了SQL国际标准，称为SQL-92。

ANSI随之发布的相应标准是ANSISQL-92。

ANSISQL-92有时被称为ANSISQL。

尽管不同的关系数据库使用的SQL版本有一些差异，但大多数都遵循ANSISQL标准。

SQLServer使用ANSISQL-92的扩展集，称为T-SQL，其遵循ANSI制定的SQL-92标准。

数据库基础,4.1SQL概述,SQL语言包含3个部分：

数据定义语言（DDL），例如：

CREATE、DROP、ALTER等语句。

数据操作语言（DML），例如：

SELECT、INSERT、UPDATE、DELETE语句。

数据控制语言（DCL），例如：

GRANT、REVOKE、COMMIT、ROLLBACK等语句。

数据库基础,4.1SQL概述,SQL语言包括三种主要程序设计语言类别的陈述式：

数据定义语言（DDL），数据操作语言（DML）及数据控制语言（DCL）。

其中数据操作语言（DML）又分为数据查询（SELECT）和数据更新（INSERT、UPDATE、DELETE）。

数据库基础,4.1SQL概述,在SQLServer2005中，可以使用界面方式进行上述操作（详见第三章SQLServer2005的界面操作），也可以使用SQL语句完成。

见图4.1，在ManagementStudio界面中，使用数据库引擎查询工具，可以出现图中的书写SQL语句的窗口，书写后点击执行按钮，即可在下面的消息框中看到执行的结果。

注意：

默认状态下，SQL语言不区分大小写。

数据库基础,4.1SQL概述,图4.1ManagementStudio界面,数据库基础,第四章关系数据库标准语言SQL,4.1SQL概述4.2SQL数据定义4.3SQL数据查询4.4SQL数据更新,数据库基础,4.2SQL数据定义,4.2.1SQL数据库的体系结构4.2.2基本表的定义与删改,数据库基础,4.2.1SQL数据库的体系结构,在具体介绍数据定义功能之前，首先需要了解SQL语言支持的关系数据库三级模式结构。

SQL语言可以对两种基本数据结构进行操作，一种是“表”，另一种是“视图（View）”。

视图是由不同的数据库中满足一定条件约束的数据所组成，用户可以像基本表一样对视图进行操作。

当对视图操作时，由系统转换成对基本表的操作。

视图可以作为某个用户的专用数据部分，这样便于用户使用，提高了数据的独立性，有利于数据的安全保密。

数据库基础,4.2.1SQL数据库的体系结构,SQL语言支持关系数据库三级模式结构，如图4.2所示。

用户可以用SQL语言对视图和基本表进行查询等操作，在用户观点里，视图和基本表都是关系。

视图是从一个或几个基本表导出的表，它本身不独立存储在数据库中，即数据库中只存储视图的定义而不存储对应的数据，因此视图是一个虚表。

视图在概念上与基本表等同，用户可在视图上再定义新的视图。

基本表是本身独立存在的表，一个（或多个）基本表对应一个存储文件，一个表可以带若干索引，索引也存放在存储文件中。

存储文件的逻辑结构组成了关系数据库的内模式。

数据库基础,4.2.1SQL数据库的体系结构,在SQL中，关系模式称为基本表，存储模式称为存储文件，子模式称为视图，元组称为行，属性称为列。

数据库基础,4.2.1SQL数据库的体系结构,图4.2SQL支持的数据库体系结构,数据库基础,4.2SQL数据定义,4.2.1SQL数据库的体系结构4.2.2基本表的定义与删改,数据库基础,4.2.2基本表的定义与删改,SQL的数据定义功能包括3部分：

定义基本表、定义视图和定义索引。

我们以一个简单的图书管理关系数据模型为基础，通过示例来介绍SQL的使用方法。

设图书管理关系数据模型包括以下3个关系模式：

图书（图书编号，书名，作者，出版社，单价）读者（借书证号，姓名，性别，单位，职称，地址）借阅（借书证号，图书编号，借阅日期，备注）,数据库基础,4.2.2基本表的定义与删改,1定义基本表2修改基本表3删除基本表,数据库基础,1定义基本表,定义一个基本表相当于建立一个新的关系模式，但尚未输入数据，只是一个空的关系框架。

系统将一个基本表的数据描述存入数据字典中，供系统或用户查阅。

定义基本表就是创建一个基本表，对表名（关系名）和它所包括的各个属性及其数据类型做出具体规定。

不同的系统支持的数据类型有区别，SQLServer2005支持的数据类型请见3.2.1。

数据库基础,1定义基本表,定义基本表使用SQL语言数据定义功能中的CREATETable语句实现，其一般格式为：

CREATETABLE（列级完整性约束定义,列级完整性约束定义,表级完整性约束定义）,数据库基础,1定义基本表,注意：

上述语法中用到了一些特殊的符号，这些符号是语法描述的常用符号，而不是SQL语句的组成部分。

我们先介绍一下这些符号，后面的语句语法介绍中也会用到。

方括号（）中的内容是可选的（即可出现0次或1次）。

花括号（）与省略号（）一起，表示其中的内容也是可选的（即可出现0次或1次）。

另外常用的还有竖杠（|）表示在多个短语中选择一个，例如term1|term2|term3，表示在这三个选项中任选一项，它将在后面的语法中用到。

数据库基础,1定义基本表,其中：

是所定义的基本表的名字。

是表中所包含的列的名字。

一个表可以有多个列。

是指列的数据类型。

完整性约束定义,数据库基础,1定义基本表,在定义表的同时还可以定义与表有关的完整行约束条件，这些完整性约束条件都存储在系统的数据字典中。

如果完整性约束只涉及表中的一个列，则这些约束条件可以在列级完整性约束定义处定义，也可以在表级完整性约束定义处定义，但如果完整性约束条件涉及表中多个列，则必须在表级完整性约束定义处定义。

在列级完整性约束定义处可以定义以下约束：

数据库基础,1定义基本表,NOTNULL：

限制列取值非空。

DEFAULT：

给定列的默认值。

UNIQUE：

限制列取值不重。

CHECK：

限制列的取值范围。

PRIMARYKEY：

指定本列为主码。

FOREIGNKEY：

定义本列为引用其他表的外码。

使用形式为：

FOREIGNKEY（）REFERENCES（）,数据库基础,1定义基本表,上述约束中，除了NOTNULL和DEFAULT不能在表级完整性约束定义处定义外，其他约束均可在表级完整性约束定义处定义。

但要注意以下几点：

第一，如果CHECK约束是定义多列之间的取值约束，则只能在表级完整性约束处定义；第二，如果表的主码由多个列组成，则也只能在表级完整性约束处定义，并将主码列用括号括起来，即：

PRIMARYKEY（列1，列2）；第三，如果在表级完整性约束处定义外码，则“FOREIGNKEY（）”部分不能省。

关于完整性详见第七章。

数据库基础,1定义基本表,例4.1创建图书、读者和借阅三个二维表。

CREATETABLE图书（图书编号CHAR（8）PRIMARYKEY,书名CHAR（30）,作者CHAR（8），出版社CHAR（30），单价DECIMAL（10，2）CREATETABLE读者（借书证号CHAR（8）PRIMARYKEY,数据库基础,1定义基本表,姓名CHAR（8）,性别CHAR

（2）,CHECK性别IN（男,女）单位CHAR（30），职称CHAR（10），地址CHAR（30）CREATETABLE借阅（借书证号CHAR（8），,数据库基础,1定义基本表,图书编号CHAR（8），借阅日期DATATIME，备注CHAR（30）,PRIMARYKEY（借书证号,图书编号），FOREIGNKEY借书证号REFERENCES读者（借书证号），FOREIGNKEY图书编号REFERENCES图书（图书编号）用NOTNULL指出该属性在输入数据时不允许有空值。

在一般情况下不允许主关键字为空值，而其他属性可以暂时不填写，或是未知的值。

数据库基础,2修改基本表,修改基本表是指对已经定义的基本表增加、删除或修改某一列（属性）。

修改基本表使用SQL语言数据定义功能中的ALTERTABLE语句实现，其一般格式为：

数据库基础,2修改基本表,ALTERTABLEALTERCOLUMN|ADDCOLUMN约束|DROPCOLUMN|ADDPRIMARYKEY（列名,n）|ADDFOREIGNKEY（列名）REFERNECES表名列名）,数据库基础,2修改基本表,例4.2在图书表中增加作者年龄和出版社电话两个列。

ALTERTABLE图书ADD（年龄INT，出版社电话CHAR（12）新增加的属性处于表的最后一列。

如果被修改的基本表原来已经有了数据，各个记录中新增加的属性全部是空值，有待以后用更新语句修改。

在命令中可以用ALTERCOLUMN修改某一列，DROP删除某一列。

数据库基础,3删除基本表,删除基本表是把表的定义、表中的数据、相应的索引以及以该基本表为基础所建立的所有视图全部删除，并释放所占用的存储空间。

删除基本表使用SQL语言数据定义功能中的DROPTABLE语句实现，其一般格式为：

DROPTABLE,例4.3删除图书表。

DROPTABLE图书；,数据库基础,第四章关系数据库标准语言SQL,4.1SQL概述4.2SQL数据定义4.3SQL数据查询4.4SQL数据更新,数据库基础,4.3SQL数据查询,SQL的查询语句也称SELECT命令，其基本形式是SELECT-FROM-WHERE查询块。

多个查询块可以逐层嵌套执行。

SELECT命令是SQL结构化查询语言最具特色的核心语句，使用方便，查询速度块。

数据库基础,4.3SQL数据查询,SELECT语句是SQL的核心，在你的SQL语句中可能用的最多的就是SELECT语句了。

SELECT语句用于查询数据库并检索匹配你指定条件的选择数据。

SELECT语句有五个主要的子句子你可以选择，而FROM是唯一必须的子句。

每一个子句有大量的选择项和参数等。

数据库基础,4.3SQL数据查询,以下是SELECT语句的一般格式：

SELECTINTOFROMWHEREGROUPBYHAVINGORDERBYASC|DESC,数据库基础,4.3SQL数据查询,在上述结构中，SELECT子句用于指定输出的字段。

INTO子句用于将查询结果存于新的二维表中。

FROM用于指定数据的来源。

WHERE子句用于指定单条记录的选择条件。

GROUPBY子句用于对检索到的记录进行分组。

HAVING子句用于指定组的选择条件。

ORDERBY子句用于对查询结果进行排序。

在这些子句中，SELECT和FROM子句是必需的，其他子句都是可选的。

数据库基础,4.3SQL数据查询,4.3.1简单查询4.3.2连接查询4.3.3子查询4.3.4使用库函数查询4.3.5集合运算查询,数据库基础,4.3.1简单查询,这里所指的简单查询是指只涉及一个二维表的查询。

数据库基础,4.3.1简单查询,例4.4查找所有读者的全部情况。

SELECT*FROM读者;注意：

SELECT子句中的星号*表示选择了关系的全部属性，由于查询中无条件限制，所以省略了WHERE子句。

数据库基础,4.3.1简单查询,例4.5列出图书馆中所有藏书的书名和出版社。

SELECT出版社,书名FROM图书注意：

目标列的选择顺序可以与表中定义的列的顺序不一致。

数据库基础,4.3.1简单查询,例4.6查找读者黄刚所在的单位。

SELECT姓名,单位FROM读者WHERE姓名=黄刚,数据库基础,4.3.1简单查询,例4.7查找所有作者的姓名及其出生年份（以例4.2增加年龄后的二维表为基础）。

SELECT作者,2009-年龄AS出生年份FROM图书注意：

目标列中可以对列进行表达式运算。

如果没有AS保留字，经过计算的列的显示结果没有标题，加上AS保留字，后面就是列的标题，它是前面表达式的别名。

数据库基础,4.3.1简单查询,例4.8查找年龄小于40的作者所写的图书的情况（以例4.2增加年龄后的二维表为基础）。

SELECT*FROM图书WHERE年龄、=、（或!

=）、NOT+前述比较运算符。

数据库基础,4.3.1简单查询,例4.9查找2008年1月1日后借阅图书的借书证号。

SELECTDISTINCT借书证号FROM借阅WHERE借阅日期2008-1-1注意：

因同一个读者在2008年1月1日后可以借阅多本书，借书证号可能会重复输出，加上DISTINCT保留字可以去掉重复。

数据库基础,4.3.1简单查询,例4.10查找价格在10元和20元之间的图书。

SELECT书名,作者,单价,出版社FROM图书WHERE单价BETWEEN10AND20注意：

WHERE单价BETWEEN10AND20子句相当于WHERE单价=10AND单价=20。

BETWEEN下限值AND上限值表示如果列或表达式在下限值和上限值之间（包括边界值），则结果为TRUE，表明此记录符合查询条件。

NOTBETWEEN下限值AND上限值正好相反。

数据库基础,4.3.1简单查询,例4.11查找价格不在10元和20元之间的图书。

SELECT书名,作者,单价,出版社FROM图书WHERE单价NOTBETWEEN10AND20,数据库基础,4.3.1简单查询,例4.12查找清华大学出版社和北京大学出版社的所有图书及作者。

SELECT书名,作者,出版社FROM图书WHERE出版社IN（清华大学出版社,北京大学出版社）;注意：

谓词IN表示属于，即什么在某集合中。

它可以用一个或几个OR来代替，如：

WHERE出版社IN（清华大学出版社，北京大学出版社）可以表示成WHERE出版社=清华大学出版社OR出版社=北京大学出版社。

用NOTIN表示否定，计不在摸个集合中。

数据库基础,4.3.1简单查询,例4.13查找书名以“数据库”开头的所有图书及作者。

SELECT书名,作者FROM图书WHERE书名LIKE数据库%;注意：

谓词LIKE后面必须是字符串常量，其中可以使用两个通配符。

下划线_代表任意一个字符，百分号%代表任意多个字符。

例如：

WHERE书名LIKE%数据库%：

表示包含数据库的书名。

WHERE书名LIKE%数据库：

表示以数据库结尾的书名。

WHERE作者LIKE%强_：

表示作者姓名至少4个字符（两个汉字）且倒数第2个汉字必须是强字。

数据库基础,4.3.1简单查询,例4.14查找备注为空的借阅情况。

SELECT*FROM图书WHERE备注ISNULL注意：

空值（Null）在数据库中的有特殊的含义，它表示不确定的值。

它是一种状态，不能用普通的比较运算符（=，！

=）。

判断不空的语句格式是“列名ISNOTNULL”,数据库基础,4.3.1简单查询,例4.15查找清华大学出版的单价高于10元的图书的情况。

SELECT*FROM图书WHERE出版社=清华大学出版社and单价10注意：

逻辑运算符and和or可用来连接多个查询条件。

And的优先级高于or，但用户可以用括号改变优先级。

数据库基础,4.3.1简单查询,例4.16查找鲁迅书的情况，并先按出版社升序排列，同一出版社的图书再按图书单价的降序排列。

SELECT*FROM图书WHERE作者=鲁迅ORDERBY出版社，单价DESC,数据库基础,4.3.1简单查询,注意：

用户可以用ORDERBY子句对查询结果按照一个或多个属性列的升序（ASC，此项为默认，可以省略）或降序（DESC）排序。

对于空值，若按升序，含空值的元组在最后显示。

若按降序，含空值的元组将最先显示。

在该例中，先按出版社升序排列，ASC被省略，同一出版社的图书再按图书单价的降序排列。

数据库基础,4.3.1简单查询,GROUPBY子句将查询结果按某一列或多列的值分组，值相等的为一组。

对查询结果分组的目的是为了细化聚集函数的作用对象。

分组后聚集函数将作用与每一个组，即每一个组都有一个函数值。

数据库基础,4.3.1简单查询,例4.17查找各个出版社出版的图书册数。

SELECT出版社，COUNT（*）AS图书册数FROM图书GROUPBY出版社注意：

该语句对查询结果按出版社的值分组，所有具有相同出版社的元组为一组，然后对每一组作用聚集函数COUNT，以求得该组的元组个数。

数据库基础,4.3.1简单查询,例4.18查找借阅了3本以上图书的借书证号。

SELECT借书证号FROM借阅GROUPBY借书证号HAVINGCOUNT（*）3注意：

HAVING短语是用来指定筛选组的条件。

该例中先用GROUPBY子句按出版社分组，再用聚集函数COUNT对每一组计数。

HAVING短语选取计数结果大于3的组输出。

下面我们给出一个综合实例说明简单查询的方法，该例题包含了SELECT语句的所有子句。

数据库基础,4.3.1简单查询,例4.19假设有一个关系描述了供应商与零件之间的关系，其中S表是供应商关系，s#代表供应商编号,sname代表供应商姓名,status代表供应商状态,city代表供应商所在城市。

P表是零件关系，p#代表零件编号,pname代表零件名字,color代表零件颜色,weight代表零件重量,city代表零件的产地。

SP表是供应关系，s#代表供应商编号,p#代表零件编号,qty代表供应的数量。

要求对于供应总量大于300的所有零件（总量中，不包括每项供应量200的零件供应）取出零件号码和供应这种零件的最大数量，并将结果按最大供应量递增的顺序排列，对最大量相同的零件，按零件号递减顺序排列。

数据库基础,4.3.1简单查询,我们分析一下三个二维表，发现本查询只涉及到SP一个表。

假设表4-1是SP表，则表4-2是该程序的运行结果。

S（s#,sname,status,city）P（p#,pname,color,weight,city）SP（s#,p#,qty）Selectp#,max（qty）ASshuliangFromspWhereqty200Groupbyp#Havingsum（qty）300Orderbymax（qty）,p#desc,数据库基础,4.3.1简单查询,表4-1SP表,表4-2运行结果表,数据库基础,4.3SQL数据查询,4.3.1简单查询4.3.2连接查询4.3.3子查询4.3.4使用库函数查询4.3.5集合运算查询,数据库基础,4.3.2连接查询,前面介绍的查询都是针对一个表进行的，但有时我们需要从多个表中获取信息，这样就会涉及多张表。

若一个查询涉及两个或两个以上的表，则称之为连接查询。

连接查询是关系数据库中最主要的查询，主要包括内连接、外连接和交叉连接等类型。

我们这里只介绍内连接和外连接，交叉连接很少使用，其结果也没有太大的意义，这里就不介绍了。

数据库基础,4.3.2连接查询,如果查询某查询要涉及两个或多个关系，往往要进行连接运算。

由于SQL是高度非过程化的，用户只要在FROM子句中指出关系名称，在WHERE子句写明连接条件即可，连接运算由系统去完成并实现优化。

以下几个例子的写法是国际标准写法。

数据库基础,4.3.2连接查询,例4.20查找所有借阅了图书的读者姓名及所在单位。

SELECTDISTINCT姓名,单位FROM读者,借阅WHERE读者.借书证号=借阅.借书证号;注意如果不同关系中有相同的属性名，为了避免混淆，应当在前面冠以关系名，并用.分开，用DISTINCT表示无论一位读者借几本书，在输出结果中只出现一次。

数据库基础,4.3.2连接查询,例4.21找出李晶所借的所有图书的书名及借阅日期。

SELECT李晶所借的图书：

书名，借阅日期FROM图书,借阅,读者WHERE读者.借书证号=借阅.借书证号AND借阅.图书编号图书.图书编号AND姓名=李晶;该查询涉及到3个关系之间的自然连接，用户只需用外关键字指出连接条件。

SELECT子句中允许有字符串常量，例中“李晶所借的图书：

”是提醒用户，使查询结果易于阅读。

数据库基础,4.3.2连接查询,例4.22查找价格在20元以上已借出的图书，结果按单价降序排列。

SELECT*FROM图书,借阅WHERE图书.图书编号=借阅.图书编