第五章 关系数据库标准语言SQL新.docx

第五章 关系数据库标准语言SQL新.docx

《第五章 关系数据库标准语言SQL新.docx》由会员分享，可在线阅读，更多相关《第五章 关系数据库标准语言SQL新.docx（12页珍藏版）》请在冰豆网上搜索。

第五章关系数据库标准语言SQL新

第五章关系数据库标准语言SQL

5.1SQL概述

SQL是结构化查询语言

STRCTUREQUERYLANGURAGE

1.简介

✓SQL--StructuredQueryLanguage（结构化查询语言）

✓SQL语言已经成为关系数据库通用的查询语言，几乎所有的关系数据库系统都支持它。

✓SQL语言功能：

数据定义、数据控制、数据操纵和数据查询功能，其中最重要的是数据查询功能。

✓数据库的所有操作都可以通过SQL语言来完成

表5-1SQL命令动词

SQL功能

命令动词

数据查询

SELECT

数据定义

CREATE,DROP,ALTER

数据操纵

INSERT,UPDATE,DELETE

数据控制

GRANT,REVOKE

2.特点

✓两种使用方式：

既可以直接以命令方式交互使用，也可以嵌入到程序设计语言中以程序方式使用。

✓高度非过程化：

只需要描述清楚用户要“做什么”，SQL语言就可以将要求交给系统，自动完成全部工作。

✓语言简洁，易学易用。

5.2查询功能

1.SQL的核心是查询。

SQL的查询命令也称为SELECT命令，它的基本形式由SELECT-FROM-WHERE查询块组成，多个查询块可以嵌套执行。

2.SELECT命令的语法格式（P77）

（1）其中主要短语的含义如下：

SELECT:

说明要显示查询的数据字段

FROM：

说明要查询的数据来自哪个或哪些表

WHERE：

说明查询条件，即选择元组的条件

JOIN:

指定多个表之间的联接

GROUPBY：

对查询结果进行分组，可以利用它进行分组汇总

HAVING：

必须跟随GROUPBY，它用来限定分组必须满足的条件

ORDERBY：

对查询结果进行排序

注意：

只有SELECT和FROM是命令的必需部分

（2）查询例子（以下查询例子都是采用bookinfo,readerinfo,borrowreturninfo,bookcategory数据表）p79—p80

5.2.1简单查询

简单查询是从一个表中查询某些列的信息。

✓由SELECT和FROM短语构成的无条件查询

✓基于单个表

✓或由SELECT、FROM和WHERE短语构成的条件查询

[例子5.1]在readerinfo表中查询所有读者信息。

其中“*”是通配符，表示所有属性，即字段。

[练习5.1]在bookinfo表中查询所有图书信息。

[例子5.2]在readerinfo表中查询读者所在院系。

[练习5.2]在bookinfo表中查询所有出版社信息。

[例子5.3]在readerinfo表中查询读者所在院系，并去掉重复值。

[练习5.3]在bookinfo表中查询所有出版社信息，并去掉重复值。

[例子5.4]在readerinfo表中查询所有读者的姓名、性别和年龄。

[练习5.4]在bookinfo表中查询所有图书名称，作者和出版年限。

用browse命令能实现吗？

创建查询？

5.2.2条件查询

可以在指定的表中查询满足WHERE条件子句的记录。

基本格式：

SELECT<字段名表>FROM<表名>WHERE<条件>

[例子5.5]在bookinfo表中查询30元以上的图书编号、名称、作者、出版社和定价。

[练习5.5]在readerinfo表中查询性别是男读者的信息。

[例子5.6]在bookinfo表中查询外文出版社的30元以上的图书的名称、作者和定价。

[练习5.6]在readerinfo表中查询性别是男并且是信工学院的读者的信息。

[例子5.7]在bookinfo表中查询20元到30元之间的图书的名称、作者和定价。

查询的条件等价于：

（成绩>=90）AND（成绩<=100）

[练习5.7]在bookinfo表中查询4元到20元之间的图书的名称、作者和定价。

[例子5.8]在readerinfo表中查询姓“于”的读者信息。

说明：

✓这里的LIKE是字符串匹配运算符

✓通配符“%”表示0个或多个不确定的字符

✓通配符“_”表示一个不确定的字符

[练习5.8]在readerinfo表中查询姓“李”的读者信息。

[例子5.9]在readerinfo表中查询“人文学院”和“外国语学院”的读者信息。

说明：

✓IN相当于集合运算符∈

✓IN的用法。

格式为：

IN（值列表）

[练习5.9]在bookinfo表中查询“人民教育出版社”和“科普出版社”的图书信息。

[例子5.10]在borrowreturninfo表中查询尚未归还的图书编号。

空值就是缺值或没有确定值，不能把它理解为任何意义的数据。

在数据库中可能会遇到尚未存储数据的字段，这时的空值与空字符串、数值0等具有不同的含义。

注意：

查询空值时要使用ISNULL，而“=NULL”是无效的，因为空值不是一个确定的值，所以不能用“=”这样的运算符进行比较。

5.2.3联接查询

联接查询是将两个或多个表的记录通过公用字段联接起来进行查询。

联接运算是关系数据库中重要的操作。

说明:

如果FROM之后有两个关系（表），

那么这两个关系之间必有一种联系，

否则无法构成检索表达式。

（1）内部联接查询

实现两个表内部联接的方式有两种

方法一：

使用where短语

方法二：

使用join短语

[例子5.11]根据bookinfo表和bookcategory表查询图书的编号、名称和图书的类别名称信息。

[例子5.12]根据readerinfo表和borrowreturninfo表查询借过书的读者编号和姓名。

[例子5.13]根据readerinfo表、borrowreturninfo表和bookinfo表查询读者借书情况（包括读者编号、姓名、图书编号和名称）

（2）外部联接查询

SQL还支持新的联接运算符—超联结运算符。

即首先保证一个表中满足条件的元组都在结果表中，然后将满足联接条件的元组与另一个表的元组进行联接，不满足联接条件的则应将来自另一个表的属性值置为空值。

基本格式：

超联接有3种：

1.左联接：

含义是在结果表中包含第一个表中满足条件的所有元组；如果有满足联接条件的元组，则在第二个表返回相应的值，否则第二个表返回值为空值。

即除满足联接条件的记录出现在查询结果外，第一个表中不满足联接条件的记录也出现在查询结果中。

2.右联接：

含义是在结果表中包含第二个表中满足条件的所有元组；如果有满足联接条件的元组，则在第一个表返回相应的值，否则第一个表返回值为空值。

即除满足联接条件的记录出现在查询结果外，第二个表中不满足联接条件的记录也出现在查询结果中。

3.全联接:

从相关的两个表中选取所有记录,按联接条件联接成新的记录作为查询结果输出，若表1或表2中不存在相关记录，则输出时相应字段的值为.null.。

即除满足联接条件的记录出现在查询结果外，两个表中不满足联接条件的记录也出现在查询结果中。

说明：

INNERJOIN等价于JOIN，是普通联接

LEFTJOIN为左联接。

RIGHTJOIN为右联接。

FULLJOIN为全联接

即两个表中的记录不管是否满足联接条件将都在目标表或查询结果中出现，不满足联接条件的记录对应部分NULL。

说明：

从以上格式可以看出，它的联接条件在ON短语中给出，而不在WHERE短语中，联接类型在FROM短语中给出。

[例子5.14]根据readerinfo表和borrowreturninfo表左联接查询读者编号、姓名和图书编号。

和例5.12进行比较。

[例子5.15]根据borrowreturninf表和bookinfo表右联接查询读者编号、图书编号和名称。

[例子5.16]根据readerinfo表、borrowreturninfo表和bookinfo表全联接查询读者借书情况（读者编号、姓名和图书编号和名称）。

（3）别名与自联接查询

1.SQL可以将同一个关系与其自身进行联接，这种联接称为自联接。

2.实现自联接的前提是：

该关系中的两个属性具有相同的值域。

3.在实现自联接操作时，必须为表起别名。

定义别名的格式为：

<关系名>.<别名>

[例子5.17]根据borrowreturninfo表查询借过编号为“0100006”和“0300006”的两本图书的读者编号。

思考：

能用查询设计器设计这个联接查询吗？

能browse命令！

5.2.4嵌套查询

在进行条件查询时，如果条件值是直接给出的，那么用一般的条件查询就可以，但有时条件的值并没有明确给出，而需要进一步查询才可得到，这时就可用sql提供的嵌套查询，嵌套查询是指在select命令语句中包含另一个select命令语句，基本形式如下：

Select<表1列名表>from<表名1>where<表1的关联字段><比较运算符>（selec<表2列名表>from<表名2>where<表2的查询条件>）

1、一般的嵌套查询

嵌套查询:

✓嵌套查询是一类基于多个关系的查询

✓最终的结果出自一个关系

✓但相关的条件却涉及多个关系

[例5.18]根据表bookinfo查询与《水的好处》同一类别的图书名称。

[练习5.181]根据表bookinfo查询与《数学趣谈》同一类别的图书名称。

[练习5.182]根据表readerinfo查询与于丹同一院系的读者姓名。

[例5.19]根据readerinfo表和borrowreturninfo表查询借过书的读者编号和姓名。

注：

由于内层查询会得到多个结果，所以外层的条件中，比较运算符不能用“=”只能用“in”

思考：

能用查询设计器设计这个嵌套查询吗？

能browse命令！

2、内外层互相关的嵌套查询（了解）

〓＊〓＊〓＊〓＊〓＊〓

5.2.5排序

在select命令中，使用orderby短语可以使查询结果按指定要求排序。

说明：

1.ASC是指按升序排序；DESC是指按降序排序。

2.ORDERBY允许按一列或多列排序。

3.只能对最终的查询结果排序，不能对中间结果进行排序。

4.当ASC和DESC都省略时，系统默认为按升序排序。

注意：

✓HAVING子句总是跟在GROUPBY子句之后，不可以单独使用。

✓HAVING子句与WHERE子句不矛盾，在查询中先用WHERE子句限定元组，然后进行分组，最后再用HAVING子句限定分组。

讨论：

嵌套查询：

都是外层查询依赖于内层查询的结果，而内层查询与外层查询无关。

内、外层互相关的查询：

内层查询的条件需要外层查询提供值，而外层查询的条件需要内层查询的结果

基本格式：

Select<列名表>from<表名>[where<条件>]orderby<排序项>[asc|desc]

注：

1排序项可以是字段名、由as子句命名的列标题和列序号

2不能依据备注型字段和通用型字段进行排序

3如果有多个排序项，之间用逗号分隔。

[例子5.21]根据readerinfo表中查询所有读者的姓名、性别和年龄，并按年龄由小到大排序。

[例子5.22]在bookinfo表中，先按出版日期降序显示，出版日期相同的再按定价升序显示图书的信息。

[例子5.23]在bookinfo表中，查询价格最高的三本图书的信息。

Top选项一定于orderby短语配套使用。

5.2.6统计和分组统计

Select命令语句不仅可以实现对表中数据的查询操作，还可以对数据计算之后输出，对于计算功能，除了使用一般的表达式外，sql提供了一些专门的函数，用于实现对整个表进行统计或分组统计的功能

Sum（）求总和

Avg（）求平均值

Max（）求最大值

Min（）求最小值

Count（）统计记录个数

1、简单统计

简单统计一般是对表中某个字段的数据进行统计

基本格式：

select<列名表>from<表名>[where<条件>]

[例子5.24]在bookinfo表中，查询出版社的数目。

[练习5.24]在readerinfo表中，查询读者所在院系的数目。

[例子5.25]在bookinfo表中，查询“01”类图书的平均价格。

[练习5.25]在bookinfo表中，查询“02”类图书的平均价格。

2、分组统计

基本格式

Select<列名表>from<表名>[where,<条件>]groupby<分组依据>[having<条件>]

注：

where和having的区别，两者虽然都跟着条件，但where是对记录的筛选，也就是说，哪些记录能参与分组；having是对分组的筛选，也就是说，哪些组能作为最后结果输出。

[例子5.26]在readerinfo表中，分别统计男、女读者的人数。

[练习5.26]在readerinfo表中，分别统计不同院系的读者人数。

[例子5.27]在bookinfo表中，查询每类图书的平均价格。

[例子5.28]在bookinfo表中，查询平均价格高于30元的每类图书的平均价格。

5.2.7使用量词和谓词的查询（了解）

5.2.8集合的并运算（了解）

5.2.9设置查询结果的输出

1、指定查询结果的输出目标

在select命令语句中，使用to短语可以查询结果输出到指定目标：

屏幕、文本文件或打印机，vfp默认为浏览窗口。

To短语的格式为：

Toscreen|filefilename[additive]

|printer[prompt]

[例5.33]在bookinfo表中，将价格最高的三本图书的信息存储到文本文件topbook.txt中。

2、指定查询结果的保存方向

在select命令语句中，使用into短语可以指定查询结果的保存方向。

Into短语的格式为

Intotabletablename|cursorcursorname

|arrayarrayname

（1）保存到表

[例子5.34]将bookinfo表复制，形成的新表名为“图书.dbf”

（2）将查询结果存放在临时文件中

[例子]将查询到的读者信息存放在临时文件TMP2。

（3）将查询结果存放到数组中

[例子]将查询到的读者信息存放在数组TMP1。

简单查询，条件查询，内部联接查询，一般嵌套查询，排序，统计和分组统计

5.3操作功能

SQL的操作功能是指对数据库中数据的操作功能，主要包括数据的插入、更新和删除3个方面的内容。

5.3.1数据插入

VFP支持两种SQL插入命令的格式，第一种格式是标准格式，第二种格式是VFP的特殊格式。

第一种格式：

INSERTINTO<表名>[（<字段名1>[,<字段名2>,…]）]VALUES（<表达式1>[,<表达式2>,…]）

第二种格式：

[例子5.35]在bookcategory表的末尾插入一条新记录。

类别编号：

06，类别名称：

古典文学类。

[例子5.36]在borrowreturninfo表的末尾插入一条新记录。

流水号：

090009,读者编号：

409109020328，图书编号：

0100001，借书日期：

01/20/09

[例子5.37]

[例子5.38]

5.3.2数据更新

命令格式

UPDATE<表名>；

SET<字段名1>=<表达式1>[,<字段名2>=<表达式2>…][WHERE<条件>]

[例子5.39]将readerinfo表中“李想”的生日改为“88年7月8日”。

[例子5.40]将自然科学基础类的图书定价增加10元。

5.3.3数据删除

命令格式

DELETEFROM<表名>[WHERE条件]

[例子5.41]将“赵启光”的借书记录逻辑删除。

5.4定义功能

5.4.1表的定义

命令格式

createtable命令建立表。

[例子5.42]用命令建立“图书管理”数据库

[例子5.39]用SQL命令创建一个图书类别bookcate，包括字段：

类别编号，字符型，宽度6位，主关键字；类别名称，字符型，宽度20位。

5.4.2表结构的修改

命令格式

altertable

该命令有3种格式

格式1：

该格式可以添加（add）新的字段或修改已有的字段，它的句法基本可以与createtable的句法相对应。

[例子5.41]为数据表kc4增加一个逻辑型的是否必修字段。

altertablekc4add是否必修L

[例子5.42]将数据表kc4的课程号字段的宽度由原来的3改4。

altertablekc4ALTER课程号c（4）

格式2:

该格式主要用于定义、修改和删除有效性规则和默认值定义。

[例子5.43]修改数据表cj4的成绩字段的有效性规则。

格式3：

改格式可以删除字段、修改字段名，可以定义、修改和删除表一级的有效性规则等。

[例子5.44]将数据表kc4中的“是否必修”改为“必修”

[例子5.45]删除数据表kc4中的必修字段。

5.4.3表的删除

DROPTABLE<表名>

5.4.4视图的定义及删除

创建视图：

createview<视图名>as

删除视图

Dropview<视图名>