第3章关系数据库语言SQL课件

第3章关系数据库语言SQL

第3章关系数据库语言SQL

本章重要概念

（1）SQL数据库的体系结构，SQL的组成。（2）SQL的数据定义：SQL模式、基本表和索引的创建和撤销。（3）SQL的数据查询；SELECT语句的句法，SELECT语句的三种形式及各种限定，基本表的联接操作，SQL3中的递归查询。（4）SQL的数据更新：插入、删除和修改语句。（5）视图的创建和撤销，对视图更新操作的限制。本章重要概念（1）SQL数据库的体系结构，SQL的组成本章概述SQL是关系数据库的标准语言，对关系模型的发展和商用DBMS的研制起着重要的作用。SQL语言是介乎于关系代数和元组演算之间的一种语言。本章详细介绍SQL的核心部分内容：数据定义、数据查询、数据更新和嵌入式SQL。

本章概述SQL是关系数据库的标准语言，对关系模型的发展和商用关系数据库语言SQL3.1SQL的体系结构

3.2SQL的数据定义

3.3SQL的数据查询3.4数据更新3.5视图的定义和对视图的操作3.6嵌入式SQL

关系数据库语言SQL3.1SQL的体系结构3.1SQL的体系结构

3.1.1SQL的产生与发展

3.1.2SQL数据库的体系结构

3.1.3SQL的组成

返回3.1SQL的体系结构3.1.1SQL的产生与发展SQL的产生与发展

1970年，美国IBM研究中心的E.F.Codd连续发表多篇论文，提出关系模型。1972年，IBM公司开始研制实验型关系数据库管理系统SYSTEMR，配制的查询语言称为SQUARE(SpecifyingQueriesAsRelationalExpression)语言，在语言中使用了较多的数学符号。1974年，Boyce和Chamberlin把SQUARE修改为SEQUEL(StructuredEnglishQUEryLanguage)语言。后来SEQUEL简称为SQL(StructuredQueryLanguage)，即“结构式查询语言”，SQL的发音仍为“sequel”。现在SQL已经成为一个标准。SQL的产生与发展1970年，美国IBM研究中心的E.F.SQL数据库的体系结构

用户1用户2用户3用户4视图1视图1基本表1基本表2基本表3基本表4存储文件1存储文件2存储文件3存储文件4图3.1SQL数据库的体系结构SQL用户ViewBasetableStoredfileSQL数据库的体系结构用户1用户2用户3用户4视图1视图1SQL的组成

核心SQL主要有四个部分: (1)数据定义语言，即SQLDDL，用于定义SQL模式、基本表、视图、索引等结构。

(2)数据操纵语言，即SQLDML。数据操纵分成数据查询和数据更新两类。其中数据更新又分成插入、删除和修改三种操作。

(3)嵌入式SQL语言的使用规定。这一部分内容涉及到SQL语句嵌入在宿主语言程序中的规则。

(4)数据控制语言，即SQLDCL，这一部分包括对基本表和视图的授权、完整性规则的描述、事务控制等内容。返回SQL的组成核心SQL主要有四个部分:返回3.2SQL的数据定义

3.2.1SQL模式的创建和撤销

3.2.2基本数据类型

3.2.3基本表的创建、修改和撤销

3.2.4索引的创建和撤销

返回3.2SQL的数据定义3.2.1SQL模式的创建和撤SQL模式的创建和撤销

SQL模式的创建可用CREATESCHEMA语句定义，其基本句法如下：

CREATESCHEMA〈模式名〉AUTHORIZATION〈用户名〉DROP语句的句法如下：

DROPSCHEMA〈模式名〉［CASCADE│RESTRICT］其方式有两种：

CASCADE(级联式)方式。

RESTRICT(约束式)方式。

SQL模式的创建和撤销SQL模式的创建可用CREATESQLServer2008的schemaSchma也称为架构，是形成单个命名空间的数据库实体的集合。命名空间是一个集合，其中每个元素的名称都是唯一的。以往SQLServer内的对象命名是“服务器.数据库.用户名.对象”，但新版的对象命名改为“服务器.数据库.Schema.对象”。完全限定的对象名称现在包含四部分：server.database.schema.objectSQLServer2005以后，架构独立于创建它们的数据库用户而存在。可以在不更改架构名称的情况下转让架构的所有权SQLServer2008的schemaSchma也称为基本数据类型

SQL提供的主要数据类型(也称为“域类型”)有：（1）数值型（2）字符串型（3）位串型（4）时间型返回基本数据类型SQL提供的主要数据类型(也称为“域类型”)有AnIntroductiontoDatabaseSystemSQLServer支持的数据类型数据类型含义CHAR(n)长度为n的定长字符串VARCHAR(n)最大长度为n的变长字符串INT整数型。精度为10，长度为4字节。SMALLINT短整数。精度为5，长度为2字节。NUMERIC(p[，s])精确整数型，由p位数字（不包括符号、小数点）组成，小数后面有s位数字,长度为5-17。REAL，FLOAT(n)浮点型。使用科学计数法表示数据。如：5.6432E20DATETIME,SMALLDATETIME日期型，包含年、月、日，长度分别为8字节，4字节，分别精确到毫秒和分钟。bit位型。存储0和1，长度为1字节。Binary,varbinary二进制型。存储二进制位数据流。存储字节个数0～8000text文本型，存储大量的字符数据。请参见SQLServer实用教程表2.3（P38），或参见联机帮助。AnIntroductiontoDatabaseSy基本表的创建、修改和撤销

(1)

基本表的创建

例3.1

基本表SS（S#，SNAME，AGE，SEX）可用下列语句创建：

CREATETABLES (S# CHAR(4)NOTNULL，

SNAMECHAR(8)NOTNULL，

AGE CHAR(1)，

SEX CHAR(1), PRIMARYKEY(S#))；

基本表的创建、修改和撤销(1)基本表的创建基本表的创建、修改和撤销(2)基本表结构的修改增加新的列用“ALTER…ADD…”语句，其句法如下：

ALTERTABLE<基本表名>ADD<列名><类型>删除原有的列用“ALTER…DROP…”语句，句法如下：

ALTERTABLE<基本表名>DROP<列名>［CASCADE│RESTRICT］此处CASCADE方式表示：在基本表中删除某列时，所有引用到该列的视图和约束也要一起自动地被删除。而RESTRICT方式表示在没有视图或约束引用该属性时，才能在基本表中删除该列，否则拒绝删除操作。

基本表的创建、修改和撤销(2)基本表结构的修改基本表的创建、修改和撤销(3)例3.2在基本表S中增加一个地址（ADDRESS）列，可用下列语句：

ALTERTABLESADDADDRESSVARCHAR（30）； 应注意，新增加的列不能定义为“NOTNULL”。基本表在增加一列后，原有元组在新增加的列上的值都被定义为空值（NULL）。例3.3在基本表S中删除年龄（AGE）列，并且把引用该列的所有视图和约束也一起删除，可用下列语句：

ALTERTABLESDROPAGECASCADE；例3.4在基本表S中S#的长度修改为6，可用下列语句：

ALTERTABLESMODIFYS#CHAR（6）；

基本表的创建、修改和撤销(3)例3.2在基本表S中增加一基本表的创建、修改和撤销(4)基本表的撤销撤销语句的句法如下：

DROPTABLE<基本表名>［CASCADE│RESTRICT］此处的CASCADE、RESTRICT的语义同前面句法中的语义一样。

返回基本表的创建、修改和撤销(4)基本表的撤销返回索引的创建和撤销

索引的创建 创建索引可用“CREATEINDEX”语句实现。句法如下：

CREATE［UNIQUE］INDEX<索引名>ON<基本表名>（列名表）索引的撤销 当索引不需要时，可以用“DROPINDEX”语句撤销，其句法如下：

DROPINDEX<索引名>返回索引的创建和撤销索引的创建返回3.3SQL的数据查询

3.3.1

SELECT查询语句

3.3.2

SELECT语句完整的句法

3.3.3

SELECT语句中的限定

3.3.4

基本表的联接操作

3.3.5

SQL3中的递归查询

返回3.3SQL的数据查询3.3.1SELECT查询语句SELECT查询语句

(1)SELECT—FROM—WHERE句型 在关系代数中最常用的式子是下列表达式：

πA1,…,An(σF(R1×…×Rm))

这里R1、…、Rm为关系，F是公式，A1、…、An为属性。 针对上述表达式，SQL为此设计了SELECT—FROM—WHERE句型：

SELECTA1，…，An FROMR1，…，Rm WHEREF

这个句型是从关系代数表达式演变来的，但WHERE子句中的条件表达式F要比关系代数中公式更灵活。SELECT查询语句(1)SELECT—FROM—WHESELECT语句完整的句法

(1)SELECT语句完整的句法如下：

SELECT目标表的列名或列表达式序列

FROM基本表名和（或）视图序列

[WHERE行条件表达式][GROUPBY列名序列

[HAVING组条件表达式]][ORDERBY列名[ASC|DESC]，…]SELECT语句完整的句法(1)SELECT语句完整的句法SELECT语句完整的句法(2)整个语句的执行过程如下：（1）读取FROM子句中基本表、视图的数据，执行笛卡尔积操作。（2）选取满足WHERE子句中给出的条件表达式的元组。（3）按GROUP子句中指定列的值分组，同时提取满足HAVING子句中组条件表达式的那些组。（4）按SELECT子句中给出的列名或列表达式求值输出。（5）ORDER子句对输出的目标表进行排序，按附加说明ASC升序排列，或按DESC降序排列。SELECT语句完整的句法(2)整个语句的执行过程如下：SELECT查询语句

(2)SELECT句型使用实例 例3.8教学数据中有三个基本表（关系）：

S（S#，SNAME，AGE，SEX）

SC（S#，C#，GRADE）

C（C#，CNAME，TEACHER） 下面用SELECT查询语句表达每个查询。 ①检索学习课程号为C2的学生学号与成绩。 ②检索学习课程号为C2的学生学号与姓名。 ③检索选修课程名为MATHS的学生学号与姓名。④检索选修课程号为C2或C4的学生学号。⑤检索不学Ｃ２课程的学生学号。（６）检索至少选修课程号为C2和C4的学生学号。（７）检索学习了全部课程的学生姓名。SELECT查询语句(2)SELECT句型使用实例SELECT查询语句

(4)

聚合函数

COUNT（*）计算元组的个数COUNT（列名）对一列中的值计算个数SUM（列名）求某一列值的总和（此列的值必须是数值型）AVG（列名）求某一列值的平均值（此列的值必须是数值型）MAX（列名）求某一列值的最大值MIN（列名）求某一列值的最小值返回SELECT查询语句(4)

聚合函数返回SELECT语句完整的句法

(3)例3.11对教学数据库的基本表S、SC、C中数据进行查询和计算。①统计每一年龄选修课程的学生人数

SELECTAGE，COUNT（DISTINCTS.S#）

FROMS，SCWHERES.S#=SC.S#GROUPBYAGE；由于要统计每一个年龄的学生人数，因此要把满足WHERE子句中条件的查询结果按年龄分组，在每一组中的学生年龄相同。此时的SELECT子句应对每一组分开进行操作，在每一组中，年龄只有一个值，统计的人数是这一组中的学生人数。SELECT语句完整的句法(3)例3.11对教学数据库SELECT语句完整的句法

(4)②求基本表S中男同学的每一年龄组（超过50人）有多少人？要求查询结果按人数升序排列，人数相同按年龄降序排列。

SELECTAGE，COUNT（S#）

FROMS WHERESEX='M' GROUPBYAGE HAVINGCOUNT（*）>50 ORDERBY2，AGEDESC；返回SELECT语句完整的句法(4)②求基本表S中男同学的每一SELECT语句中的限定

(1)

SELECT子句中的规定

SELECT子句描述查询输出的表格结构，即输出值的列名或表达式。其形式如下：

SELECT[ALL|DISTINCT]<列名或列表达式序列>|*

条件表达式中的算术比较操作条件表达式中可出现算术比较运算符（＜，＜＝，＞，＞＝，＝，!＝），也可以用“BETWEEN…AND…”比较运算符限定一个值的范围。列和基本表的改名操作SELECT语句中的限定(1)SELECT子句中的规定SELECT语句中的限定

(2)字符串的匹配操作 条件表达式中字符串匹配操作符是“LIKE”。在表达式中可使用两个通配符： 百分号（%）：与零个或多个字符组成的字符串匹配。 下划线（_）：与单个字符匹配。集合的并、交、差操作当两个子查询结果的结构完全一致时，可以让这两个子查询执行并、交、差操作。并、交、差的运算符为UNION、INTERSECT和EXCEPT。空值的比较操作

SQL中允许列值为空，空值用保留字NULL表示。

SELECT语句中的限定(2)字符串的匹配操作SELECT语句中的限定

(3)集合的比较操作

SQL提供SELECT语句的嵌套子查询机制。子查询是嵌套在另一个查询中的SELECT语句。（1）集合成员资格的比较（2）集合成员的算术比较（3）空关系的测试（4）重复元组的测试导出表的使用

SQL2允许在FROM子句中使用子查询。如果在FROM子句中使用了子查询，那么要给子查询的结果起个表名和相应的列名。

返回SELECT语句中的限定(3)集合的比较操作返回基本表的联接操作

基本表的联接操作基本表的联接操作

(例)基本表的联接操作(例)3.4数据更新

3.4.1数据插入

3.4.2

数据删除

3.4.3数据修改

返回3.4数据更新3.4.1数据插入返回数据插入

（1）单元组的插入

INSERTINTO基本表名[（列名表）]VALUES（元组值）（2）多元组的插入

INSERTINTO基本表名[（列名表）] VALUES（元组值），（元组值），……，（元组值）（3）查询结果的插入

INSERTINTO基本表名[(列名表)]＜SELECT查询语句＞这个语句可把一个SELECT语句的查询结果插到某个基本表中。（4）表的插入

INSERTINTO基本表名1[（列名表）]TABLE基本表名2

这个语句可把基本表2的值插入到基本表1中。数据插入（1）单元组的插入数据删除

SQL的删除操作是指从基本表中删除元组，其句法如下：

DELETEFROM基本表名

[WHERE条件表达式]

其语义是从基本表中删除满足条件表达式的元组。删除语句实际上是“SELECT*FROM基本表名[WHERE条件表达式]”和DELETE操作的结合，每找到一个元组，就把它删去。应该注意，DELETE语句只能从一个基本表中删除元组。WHERE子句中条件可以嵌套，也可以是来自几个基本表的复合条件。数据删除SQL的删除操作是指从基本表中删除元组，其句法如下数据修改

当需要修改基本表中元组的某些列值时，可以用UPDATE语句实现，其句法如下：

UPDATE基本表名

SET列名=值表达式[，列名=值表达式…]│ROW=（元组）

[WHERE条件表达式]

其语义是：修改基本表中满足条件表达式的那些元组中的列值，需修改的列值在SET子句中指出。返回数据修改当需要修改基本表中元组的某些列值时，可以用UPDA3.5视图的定义和对视图的操作

3.5.1视图的创建和撤销

3.5.2

对视图的更新操作

3.5.3视图的优点

返回3.5视图的定义和对视图的操作3.5.1视图的创建和视图的创建和撤销

(1)视图的创建创建视图可用“CREATEVIEW”语句实现。其句法如下：

CREATEVIEW<视图名>（列表名）

AS<SELECT查询语句>

例3.27对于教学数据库中基本表S、SC、C，用户经常要用到S#、SNAME、CNAME和GRADE等列的数据，那么可用下列语句建立视图：

CREATEVIEWSTUDENT_GRADE(S#，SNAME，CNAME，GRADE) ASSELECTS.S#，SNAME，CNAME，GRADE FROMS，SC，C WHERES.S#=SC.S#ANDSC.C#=C.C#；视图的创建和撤销(1)视图的创建视图的创建和撤销

(2)视图的撤销在视图不需要时，可以用“DROPVIEW”语句把其从系统中撤销。其句法如下：

DROPVIEW视图名例3.28撤销STUDENT_GRADE视图，可用下列语句实现：

DROPVIEWSTUDENT_GRADE；

视图的创建和撤销(2)视图的撤销对视图的更新操作

(1)定义3.1如果视图是从单个基本表只使用选择、投影操作导出的，并且包含了基本表的主键，那么这样的视图称为“行列子集视图”，并且可以被执行更新操作。对视图的更新操作(1)定义3.1如果视图是从单个基本表对视图的更新操作(2)例3.29下面讨论对视图更新的几个例子。①设有一个视图定义

CREATEVIEWSUDENT_GRADE ASSELECTS.S#,SNAME，CNAME，GRADE FROMS，SC，C WHERES.S#=SC.S#ANDSC.C#=C.C#；这个视图定义了学生选修的课程名和成绩，是由三个基本表联接而成的。 如果用户要在视图中执行插入操作: INSERTINTOSTUDENT_GRADE VALUES('S24','WANG','MATHS',80)；若在基本表C中，课程名为MATHS的课程号有多个，则往基本表SC插入元组时，课程号C#就不定了。因此，对这个视图的更新是不允许的。

对视图的更新操作(2)例3.29下面讨论对视图更新的几个例对视图的更新操作(3)②设有一个视图定义，包含每个学生选修课程（成绩为非空）的门数和平均成绩：

CREATEVIEWS_GRADE（S#，C_NUM，AVG_GRADE）

ASSELECTS#，COUNT（C#），AVG（GRADE）

FROMSC WHEREGRADEISNOTNULL

视图S_GRADE虽然是从单个基本表导出，但导出时使用了分组和聚集操作，因此也是不能更新的。譬如，在未更改基本表SC中值时，要在视图S_GRADE中更改学生的平均成绩，显然是不切实际的。对视图的更新操作(3)②设有一个视图定义，包含每个学生选修对视图的更新操作(4) ③如果定义了一个有关男学生的视图：

CREATEVIEWS_MALE ASSELECTS#,SNAME,AGE FROMS WHERESEX='M'；这个视图是从单个关系只使用选择和投影导出的，并且包含键S#，因此是可更新的。例如，执行插入操作：

INSERTINTOS_MALEVALUES（'S28','WU',18）；系统自动会把它转变成下列语句：

INSERTINTOS VALUES('S28','WU',18,'M')；

返回对视图的更新操作(4) ③如果定义了一个有关男学生的视图：视图的优点

视图是用户一级的数据观点，由于有了视图，使数据库系统具有下列优点：（1）视图提供了逻辑数据独立性。

（2）简化了用户观点。数据库的全部结构是复杂的,并有多种联系。

（3）数据的安全保护功能。

返回视图的优点视图是用户一级的数据观点，由于有了视图，使数据数据库应用编程存储过程ADO.net数据库应用编程存储过程小结

SQL是关系数据库的标准语言，已广泛应用在商用系统中。SQL的数据定义部分包括对SQL模式、基本表、视图、索引的创建和撤销。SQL的数据操纵分成数据查询和数据更新两部分。SQL的数据查询是用SELECT语句实现，兼有关系代数和元组演算的特点。SQL的数据更新包括插入、删除和修改等三种操作，在视图中只有行列子集视图是可以更新的。嵌入式SQL涉及到SQL语句的宿主语言程序中的使用规定，以解决两种语言的不一致和相互联系的问题。同时还介绍了动态SQL语句。

小结SQL是关系数据库的标准语言，已广泛应用在商用系统

本章的重点篇幅

（1）教材中P97的例3.8（SELECT语句）。（2）教材中P124的例3.31和的例3.32（嵌入式SQL）。

本章的重点篇幅（1）教材中P97的例3.8（SELEC重要内容分析（一）SELECT语句是SQL的核心内容，对于该语句考生应掌握下列内容。1．SELECT语句的来历在关系代数中最常用的式子是下列表达式：

πA1,…,An(σF(R1×…×Rm))

这里R1、…、Rm为关系，F是公式，A1、…、A