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

1、第五章 关系数据库标准语言SQL新第五章 关系数据库标准语言SQL5.1 SQL概述SQL是结构化查询语言STRCTURE QUERY LANGURAGE1.简介 SQL-Structured Query Language(结构化查询语言) SQL语言已经成为关系数据库通用的查询语言，几乎所有的关系数据库系统都支持它。 SQL语言功能：数据定义、数据控制、数据操纵和数据查询功能，其中最重要的是数据查询功能。 数据库的所有操作都可以通过SQL语言来完成 表5-1 SQL命令动词SQL功能命令动词数据查询SELECT数据定义CREATE,DROP,ALTER数据操纵INSERT,UPDATE,DE

2、LETE数据控制GRANT,REVOKE2.特点 两种使用方式：既可以直接以命令方式交互使用，也可以嵌入到程序设计语言中以程序方式使用。 高度非过程化：只需要描述清楚用户要“做什么”，SQL语言就可以将要求交给系统，自动完成全部工作。 语言简洁，易学易用。5.2 查询功能1. SQL 的核心是查询。SQL的查询命令也称为SELECT命令，它的基本形式由SELECT-FROM-WHERE查询块组成，多个查询块可以嵌套执行。2. SELECT命令的语法格式(P77)(1) 其中主要短语的含义如下：SELECT :说明要显示查询的数据字段FROM：说明要查询的数据来自哪个或哪些表WHERE：说明查询

3、条件，即选择元组的条件JOIN:指定多个表之间的联接GROUP BY：对查询结果进行分组，可以利用它进行分组汇总HAVING：必须跟随GROUP BY，它用来限定分组必须满足的条件 ORDER BY： 对查询结果进行排序注意：只有SELECT和FROM是命令的必需部分(2) 查询例子（以下查询例子都是采用bookinfo,readerinfo,borrowreturninfo,bookcategory数据表）p79p805.2.1简单查询简单查询是从一个表中查询某些列的信息。 由SELECT和FROM短语构成的无条件查询 基于单个表 或由SELECT、FROM和WHERE短语构成的条件查询例子

4、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条件查询可以在指定的表

5、中查询满足WHERE条件子句的记录。基本格式：SELECT FROMWHERE例子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元之间的图书的名称、作者和

6、定价。例子5.8 在readerinfo表中查询姓“于”的读者信息。 说明： 这里的LIKE是字符串匹配运算符 通配符“%”表示0个或多个不确定的字符 通配符“_”表示一个不确定的字符练习5.8 在readerinfo表中查询姓“李”的读者信息。 例子5.9 在readerinfo表中查询“人文学院”和“外国语学院”的读者信息。说明： IN 相当于集合运算符 IN的用法。格式为：IN（值列表）练习5.9 在bookinfo表中查询“人民教育出版社”和“科普出版社”的图书信息。例子5.10 在borrowreturninfo表中查询尚未归还的图书编号。空值就是缺值或没有确定值，不能把它理解为任何

7、意义的数据。在数据库中可能会遇到尚未存储数据的字段，这时的空值与空字符串、数值0等具有不同的含义。 注意：查询空值时要使用IS NULL，而“=NULL” 是无效的，因为空值不是一个确定的值，所以不能用“=”这样的运算符进行比较。 5.2.3联接查询联接查询是将两个或多个表的记录通过公用字段联接起来进行查询。联接运算是关系数据库中重要的操作。说明:如果FROM之后有两个关系(表)，那么这两个关系之间必有一种联系，否则无法构成检索表达式。(1)内部联接查询实现两个表内部联接的方式有两种方法一：使用where短语方法二：使用join短语例子5.11根据bookinfo表和bookcategory表

8、查询图书的编号、名称和图书的类别名称信息。例子5.12根据readerinfo表和borrowreturninfo表查询借过书的读者编号和姓名。例子5.13根据readerinfo表、borrowreturninfo表和bookinfo表查询读者借书情况(包括读者编号、姓名、图书编号和名称)（2）外部联接查询SQL还支持新的联接运算符超联结运算符。即首先保证一个表中满足条件的元组都在结果表中，然后将满足联接条件的元组与另一个表的元组进行联接，不满足联接条件的则应将来自另一个表的属性值置为空值。基本格式：超联接有3种：1.左联接：含义是在结果表中包含第一个表中满足条件的所有元组；如果有满足联接条

9、件的元组，则在第二个表返回相应的值，否则第二个表返回值为空值。即除满足联接条件的记录出现在查询结果外，第一个表中不满足联接条件的记录也出现在查询结果中。2.右联接：含义是在结果表中包含第二个表中满足条件的所有元组；如果有满足联接条件的元组，则在第一个表返回相应的值，否则第一个表返回值为空值。即除满足联接条件的记录出现在查询结果外，第二个表中不满足联接条件的记录也出现在查询结果中。3. 全联接:从相关的两个表中选取所有记录,按联接条件联接成新的记录作为查询结果输出，若表1或表2中不存在相关记录，则输出时相应字段的值为.null.。即除满足联接条件的记录出现在查询结果外，两个表中不满足联接条件的记

10、录也出现在查询结果中。说明：INNER JOIN 等价于JOIN，是普通联接LEFT JOIN 为左联接。RIGHT JOIN 为右联接。FULL JOIN 为全联接 即两个表中的记录不管是否满足联接条件将都在目标表或查询结果中出现，不满足联接条件的记录对应部分NULL。说明： 从以上格式可以看出，它的联接条件在ON短语中给出，而不在WHERE短语中，联接类型在FROM短语中给出。例子5.14 根据readerinfo表和borrowreturninfo表左联接查询读者编号、姓名和图书编号。和例5.12进行比较。例子5.15 根据borrowreturninf表和bookinfo表右联接查询读

11、者编号、图书编号和名称。例子5.16 根据readerinfo表、borrowreturninfo表和bookinfo表全联接查询读者借书情况(读者编号、姓名和图书编号和名称)。 （3）别名与自联接查询1. SQL可以将同一个关系与其自身进行联接，这种联接称为自联接。2. 实现自联接的前提是：该关系中的两个属性具有相同的值域。3. 在实现自联接操作时，必须为表起别名。 定义别名的格式为：.例子5.17 根据borrowreturninfo表查询借过编号为“0100006”和“0300006”的两本图书的读者编号。思考：能用查询设计器设计这个联接查询吗？ 能browse命令！5.2.4嵌套查询在

12、进行条件查询时，如果条件值是直接给出的，那么用一般的条件查询就可以，但有时条件的值并没有明确给出，而需要进一步查询才可得到，这时就可用sql提供的嵌套查询，嵌套查询是指在select命令语句中包含另一个select命令语句，基本形式如下：Select from where (selec from where )1、 一般的嵌套查询嵌套查询: 嵌套查询是一类基于多个关系的查询 最终的结果出自一个关系 但相关的条件却涉及多个关系例5.18根据表bookinfo查询与水的好处同一类别的图书名称。练习5.181根据表bookinfo查询与数学趣谈同一类别的图书名称。练习5.182根据表readerin

13、fo查询与于丹同一院系的读者姓名。例5.19根据readerinfo表和borrowreturninfo表查询借过书的读者编号和姓名。注：由于内层查询会得到多个结果，所以外层的条件中，比较运算符不能用“=”只能用“in”思考：能用查询设计器设计这个嵌套查询吗？ 能browse命令！2、内外层互相关的嵌套查询（了解） 5.2.5排序在select命令中，使用order by 短语可以使查询结果按指定要求排序。说明：1.ASC是指按升序排序；DESC是指按降序排序。2.ORDER BY允许按一列或多列排序。3.只能对最终的查询结果排序，不能对中间结果进行排序。4.当ASC和DESC都省略时，系统默

14、认为按升序排序。注意： HAVING 子句总是跟在GROUP BY子句之后，不可以单独使用。 HAVING子句与WHERE子句不矛盾，在查询中先用WHERE子句限定元组，然后进行分组，最后再用HAVING 子句限定分组。讨论： 嵌套查询：都是外层查询依赖于内层查询的结果，而内层查询与外层查询无关。 内、外层互相关的查询：内层查询的条件需要外层查询提供值，而外层查询的条件需要内层查询的结果基本格式：Select from where order by asc|desc注：1 排序项可以是字段名、由as子句命名的列标题和列序号2不能依据备注型字段和通用型字段进行排序3如果有多个排序项，之间用逗号分

15、隔。 例子5.21 根据readerinfo表中查询所有读者的姓名、性别和年龄，并按年龄由小到大排序。例子5.22 在bookinfo表中，先按出版日期降序显示，出版日期相同的再按定价升序显示图书的信息。例子5.23 在bookinfo表中，查询价格最高的三本图书的信息。Top选项一定于order by短语配套使用。 5.2.6统计和分组统计Select命令语句不仅可以实现对表中数据的查询操作，还可以对数据计算之后输出，对于计算功能，除了使用一般的表达式外，sql提供了一些专门的函数，用于实现对整个表进行统计或分组统计的功能Sum()求总和Avg()求平均值Max()求最大值Min()求最小值

16、Count()统计记录个数1、简单统计简单统计一般是对表中某个字段的数据进行统计基本格式：select from where 例子5.24在bookinfo表中，查询出版社的数目。练习5.24在readerinfo表中，查询读者所在院系的数目。 例子5.25在bookinfo表中，查询“01”类图书的平均价格。 练习5.25在bookinfo表中，查询“02”类图书的平均价格。 2、分组统计基本格式Select from where , group by having注：where和having的区别，两者虽然都跟着条件，但where是对记录的筛选，也就是说，哪些记录能参与分组；having是

17、对分组的筛选，也就是说，哪些组能作为最后结果输出。例子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短语的格式为：To scree

18、n|file filenameadditive|printerprompt例5.33在bookinfo表中，将价格最高的三本图书的信息存储到文本文件topbook.txt中。2、指定查询结果的保存方向在select 命令语句中，使用into短语可以指定查询结果的保存方向。Into短语的格式为Into table tablename|cursor cursorname|array arrayname (1)保存到表例子5.34将bookinfo表复制，形成的新表名为“图书.dbf”(2)将查询结果存放在临时文件中例子将查询到的读者信息存放在临时文件TMP2。(3) 将查询结果存放到数组中例子将查

19、询到的读者信息存放在数组TMP1。简单查询，条件查询，内部联接查询，一般嵌套查询，排序，统计和分组统计5.3操作功能SQL的操作功能是指对数据库中数据的操作功能，主要包括数据的插入、更新和删除3个方面的内容。5.3.1 数据插入VFP支持两种SQL 插入命令的格式，第一种格式是标准格式，第二种格式是VFP的特殊格式。第一种格式：INSERT INTO ( ,) VALUES (,)第二种格式：例子5.35在bookcategory表的末尾 插入一条新记录。类别编号：06，类别名称：古典文学类。例子5.36在borrowreturninfo表的末尾插入一条新记录。流水号：090009,读者编号：

20、409109020328，图书编号：0100001，借书日期：01/20/09例子5.37例子5.385.3.2数据更新命令格式UPDATE ；SET =,=WHERE 例子5.39将readerinfo表中“李想”的生日改为“88年7月8日”。例子5.40将自然科学基础类的图书定价增加10元。5.3.3 数据删除命令格式DELETE FROM WHERE 条件例子5.41将“赵启光”的借书记录逻辑删除。5.4 定义功能5.4.1 表的定义命令格式 create table 命令建立表。例子5.42用命令建立“图书管理”数据库例子5.39用SQL命令创建一个图书类别bookcate，包括字段：

21、类别编号，字符型，宽度6位，主关键字；类别名称，字符型，宽度20位。5.4.2 表结构的修改命令格式 alter table该命令有3种格式格式1：该格式可以添加(add)新的字段或修改已有的字段，它的句法基本可以与create table 的句法相对应。例子5.41为数据表kc4 增加一个逻辑型的是否必修字段。alter table kc4 add 是否必修 L例子5.42将数据表kc4的课程号字段的宽度由原来的3改4。 alter table kc4 ALTER 课程号 c(4)格式2:该格式主要用于定义、修改和删除有效性规则和默认值定义。例子5.43修改数据表cj4的成绩字段的有效性规则。格式3：改格式可以删除字段、修改字段名，可以定义、修改和删除表一级的有效性规则等。例子5.44将数据表kc4中的“是否必修”改为“必修” 例子5.45删除数据表kc4中的必修字段。5.4.3表的删除DROP TABLE 5.4.4 视图的定义及删除创建视图：create view as 删除视图Drop view