第六章 SQL语言的应用.docx

1、第六章 SQL语言的应用 院 系： 经济管理系 教研室： 信管 教 师： 数据库原理及应用课程教案课程名称数据库原理及应用课程学分3课程属性必修课（ ） 专业选修课（ ） 校定公共选修课（ ）课程总学时48学时其中：讲课：32学时实验：16学时先修课程信息技术基础授课专业、班级经管系工商、信管、经济学、会计、工程造价各专业主要教材教材名称出版社作者出版时间Visual FoxPro 程序设计教程（第二版）北京邮电大学出版社刘卫国2006教材名称出版社作者出版时间数据库系统概论（第三版）高等教育出版社王珊，萨师煊2006教学目的及要求教学目标： 本章主要介绍SQL语言的概念和应用，SQL语言作为

2、关系数据库的通用语言，是本门课程的重点部分，也是其他很多课程的基础，通过课程学习要求学生能够达到以下目标：1. 掌握SQL语言的概念和基本功能。2. 熟练掌握SQL语言数据查询的功能。3. 掌握SQL语言数据定义和数据操纵的功能。3. 重点难点： 本章是本课程的重点部分，其中SQL语言的数据查询功能是重点和难点部分。解决方法：SQL语言比较抽象，并且参数很多。针对该特点，在讲解过程中，必须结合具体实例，用SQL语言来实际操作表，通过课堂演示使学生掌握其功能。同时对大量参数要从少量的简单参数入手，逐渐增加，循序渐进。 教学方法和手段：多媒体教学、实例演示、板书讲授学时：4学时注：表中（ ）选项请

3、打“”第六章 SQL语言的应用教学目的与要求通过课程学习，掌握SQL语言的概念和基本功能，熟练掌握SQL语言数据查询的功能，掌握SQL语言数据定义和数据操纵的功能4. 教学内容本章主要介绍SQL语言的概念和应用，内容包括SQL语言的概念和发展历程，SQL语言的功能，SQL语言的查询语句Select的使用方法。SQL语言在Visual FoxPro中的使用方法。教学重点与难点SQL语言的数据查询语句Select的使用方法。 6.1 SQL语言概述 20世纪80年代初，美国国家标准协会（ANSI）开始着手制定SQL标准，最早的ANSI标准于1986年完成，它也被叫做SQL86。SQL标准的出台使S

4、QL作为标准关系数据库语言的地位得到了加强。随后，SQL标准几经修改和完善，其间经历了SQL89、SQL92，一直到最近的SQL99等多个版本，每个新版本都较前面的版本有重大改进，SQL99更是包含了对诸如面向对象、递归、触发器等概念的支持。目前，各主流数据库产品采用的SQL标准是1992年制定的SQL92。 按照ANSI的规定，SQL被作为关系数据库的标准语言。SQL语句可以用来执行各种各样的操作。目前流行的关系数据库管理系统，如Qracle、Sybase、SQL Server、Visual FoxPro等都采用了SQL语言标准，而且很多数据库都对SQL语句进行了再开发和扩展。SQL语言具有

5、如下特点：（1） SQL是一种一体化的语言。尽管设计SQL的最初目的是查询，数据查询也是其最重要的功能之一，但SQL决不仅仅是一个查询工具，它集数据定义、数据查询、数据操纵和数据控制功能于一体，可以独立完成数据库的全部操作。（2） SQL语言是一种高度非过程化的语言。它没有必要一步步地告诉计算机“如何”去做，而只需要描述清楚用户要“做什么”，SQL语言就可以将要求交给系统，自动完成全部工作。（3） SQL语言非常简洁。虽然SQL语言功能很强，但它只有为数不多的9条命令：CREATE、DROP、ALTER、SELECT、INSERT、UPDATE、DELETE、GRANT、REVOKE。另外SQ

6、L的语法也非常简单，它很接近英语自然语言，因此容易学习和掌握。 （4） SQL语言可以直接以命令方式交互使用，也可以嵌入到程序设计语言中以程序方式使用。现在很多数据库应用开发工具都将SQL语言直接融入到自身的语言之中，使用起来更方便，Visual FoxPro就是如此。这些使用方式为用户提供了灵活的选择余地。此外，尽管SQL的使用方式不同，但SQL语言的语法基本是一致的。Visual FoxPro在SQL方面支持数据定义、数据查询和数据操纵功能，但在具体实现方面也存在一些差异。另外，由于Visual FoxPro自身在安全控制方面的缺陷，所以它没有提供数据控制功能。 SQL虽然在各种数据库产品

7、中得到了广泛的支持，但迄今为止，它只是一种建议标准，各种数据库产品中所实现的SQL在语法、功能等方面均略有差异，本章讲述Visual FoxPro 中SQL的语法、功能与应用。6.2 数据定义6.2.1 建立表结构命令格式是：CREATE TABLE|DBF NAME FREE( (,)NULL|NOT NULLCHECK ERRORDEFAULT PRIMARY KEY | UNIQUEREFERENCES TAG (,)NULL|NOT NULLCHECK ERRORDEFAULT PRIMARY KEY | UNIQUEREFERENCESTAG)|FROM ARRAY 参数说明：表名1

8、：要建立的表的名称。FREE：如果当前已经打开一个数据库，这里所建立的新表会自动加入该数据库，除非使用参数“FREE”说明该新表作为一个自由表不加入当前数据库。如果没有打开的数据库，该参数无意义。字段名1、字段名2、：所要建立的新表的字段名，在语法格式中，两个字段名之间的语法成分都是对一个字段的属性说明，包括：类型说明字段类型。宽度及小数位数字段宽度及小数位数。NULL、NOT NULL该字段是否允许“空值”，其默认值为NULL，即允许“空”值。 CHECK 条件表达式用来检测字段的值是否有效，这是实行数据库的一种完整性检查。ERROR 出错显示信息当完整性检查有错误，即条件表达式的值为假时的

9、提示信息。应当注意，当为一个表的某个字段建立了实行完整性检测的条件表达式后，在对该数据表输入数据时，系统会自动检测所输入的字段值是否使条件表达式为假，当有一个数据使其为假时，系统自动显示这里所提示的出错信息。 DEFAULT 表达式为一个字段指定的默认值。 PRIMARY KEY指定该字段为关键字段，非数据库表不能使用该参数。 UNIQUE指定该字段为一个侯选关键字段。注意，指定为关键或侯选关键的字段都不允许出现重复值，这称为对字段值的唯一性约束。 REFERENCES 表名这里指定的表作为新建表的永久性父表，新建表作为子表。 TAG 标识父表中的关联字段，若缺省该参数，则默认父表的主索引字段

10、作为关联字段。数组名用指定数组的值建立输入表。从以上命令格式可以看出，用CREATE TABLE命令建立表可以完成用表设计器完成的所有功能。除了建立表的基本功能外，它还包括满足实体完整性的主关键字（主索引）PRIMARY KEY、定义域完整性的CHECK约束及出错提示信息ERROR、定义默认值的DEFAULT等。另外还有描述表之间联系的FOREIGN KEY和REFERENCES等。例 利用SQL命令建立学生管理数据库，其中包含3个表：学生表、选课表和课程表。操作步骤如下：（1）用CREATE命令建立数据库。CREATE DATABASE D:反斜扛学生管理（2）用CREATE命令建立学生表。

11、CREATE TABLE 学生(学号 C(5) PRIMARY KEY,姓名 C(8),;入学成绩 N(5,1) CHECK(入学成绩0) ERROR 成绩应该大于0！)（3）建立课程表。CREATE TABLE 课程(课程号 C(5) PRIMARY KEY,课程名 C(20),学分 N(1)（4）建立选课表。CREATE TABLE 选课(学号C（5），课程号 C(5)，;成绩 I CHECK(成绩=0 AND 成绩=100);ERROR成绩值的范围0100！ DEFAULT 60,;FOREIGN KEY 学号 TAG 学号 REFERENCES 学生，；FOREIGN KEY 课程号

12、TAG 课程号 REFERENCES 课程图6-1 利用SQL命令建立数据库注意：用SQL CREATE命令新建的表自动在最小可用工作区打开，并可以通过别名引用，新表的打开方式为独占方式，忽略SET EXCLUSIVE的当前设置。如果建立自由表（当前没有打开的数据库或使用了FREE），则很多选项在命令中不能使用，如NAME、CHECK、DEFAULT、FOREIGN KEY、PRIMARY KEY和REFERENCES等。6.2.2 删除表删除表的SQL命令是：DROP TABLE DROP TABLE命令直接从磁盘上删除所指定的表文件。如果指定的表文件是数据库中的表并且相应的数据库是当前数据

13、库，则从数据库中删除了表。否则虽然从磁盘上删除了表文件，但是记录在数据库文件中的信息却没有删除，此后会出现错误提示。所以要删除数据库中的表时，最好应使数据库是当前打开的数据库，在数据库中进行操作。6.2.3 修改表结构修改表结构的命令是ALTER TABLE，该命令有3种格式。格式1：ALTER TABLE ADD|ALTER COLUMN (,)NULL | NOT NULLCHECK ERRORDEFAULT PRIMARY KEY|UNIQUEREFERENCES TAG 例 为课程表增加一个整数类型的学时字段。ALTER TABLE 课程 ;ADD 学时 I CHECK(学时16) E

14、RROR 学时应该大于16！格式2：ALTER TABLE ALTER COLUMN NULL|NOT NULLSET DEFAULT SET CHECK ERROR DROP DEFAULTDROP CHECK该格式命令主要用于定义、修改和删除有效性规则以及默认值定义。例 删除学时字段的有效性规则。ALTER TABLE 课程 ALTER 学时 DROP CHECK格式3：ALTER TABLE DROP COLUMN SET CHECK ERROR DROP CHECKADD PRIMARY KEY TAG FOR DROP PRIMARY KEYADD UNIQUE TAG FOR DR

15、OP UNIQUE TAG ADD FOREIGN KEY TAG FOR REFERENCES TAG DROP FOREIGN KEY TAG SAVERENAME COLUMN TO 该格式的命令可以删除指定字段（DROP COLUMN）、修改字段名(RENAME COLUMN)、修改指定表的完整性规则，包括主索引、外关键字、候选索引及表的合法值限定的添加与删除。例 删除课程表中的学时字段。ALTER TABLE 课程 DROP COLUMN 学时Visual FoxPro的SQL SELECT命令的语法格式是：SELECT ALL|DISTINCT.AS ,.AS FROM 数据库名!

16、AS INNER | LEFT OUTER | RIGHTOUTER|FULL OUTERJOIN !ASON INTO |TO FILEADDITIVE|TO PRINTER PROMPT|TO SCREEN6.3 数据查询Visual FoxPro的SQL SELECT命令的语法格式是： SELECT ALL|DISTINCT .AS ,.AS FROM 数据库名!AS INNER | LEFT OUTER | RIGHTOUTER|FULL OUTER JOIN !ASON INTO |TO FILEADDITIVE |TO PRINTER PROMPT|TO SCREEN PREFER

17、ENCE NOCONSOLEPLAINNOWAITWHERE AND AND|OR AND|OR GROUP BY ,HAVING UNIONALLSELECT命令ORDER BY ASC|DESC,ASC|DESC6.3.1 基本查询所谓基本查询是指无条件查询，其格式是：SELECT ALL|DISTINCT.AS ,.AS FROM 别名1,别名2其中ALL表示输出所有记录，包括重复记录。DISTINCT表示输出无重复结果的记录。当选择多个数据库表中的字段时，可使用别名来区分不同的表。显示列名的作用是在输出结果中，如果不希望使用字段名，可以根据要求设置一个名称。选项可以是字段名、表达式或函

18、数。表名代表要查询的表。例 列出学生名单。OPEN DATABASE D:学生管理SELECT * FROM 学生命令中的*表示输出所有字段，数据来源是学生表，所有内容以浏览方式显示。例 列出所有学生姓名，去掉重名。SELECT DISTINCT 姓名 AS 学生名单 FROM 学生SELECT 命令中的选项，不仅可以是字段名，还可以是表达式，也可以是一些函数，SELECT命令可操纵的函数很多。例 将所有的学生入学成绩四舍五入，只显示学号、姓名和入学成绩。SELECT 学号,姓名,ROUND(入学成绩,0) AS 入学成绩 FROM 学生注意：这个结果不影响数据库表中的结果，只是在输出时通过函

19、数计算输出。例 求出所有学生的入学成绩平均分。SELECT AVG(入学成绩) AS 入学成绩平均分 FROM 学生由以上两例可见，直接使用Visual FoxPro提供的各种SQL函数在输出时进行计算，便可得到相应的输出结果。6.3.2 带条件查询WHERE是条件语句关键字，是可选项，其格式是：WHERE 其中条件表达式可以是单表的条件表达式，也可以是多表之间的条件表达式，表达式用的比较符为：=（等于）、！=（不等于）、=（精确等于）、（大于）、=（大于等于）、（小于）、=（小于等于）例 求出湖南学生入学成绩平均分。SELECT 籍贯,AVG(入学成绩) AS 入学成绩平均分 FROM 学生

20、 WHERE籍贯=湖南条件表达式是指查询的结果集合应满足的条件，如果某行条件为真就包括该行记录。下面是可用于条件表达式中几个特殊运算符的意义和使用方法。 ALL() ANY（） BETWEEN AND EXISTS() IN 或者 IN () LIKE SOME()例 列出非湖南籍的学生名单。 SELECT 学号,姓名,籍贯 FROM 学生 WHERE 籍贯湖南以上命令的功能等同于：SELECT 学号,姓名,籍贯 FROM 学生 WHERE 籍贯!= 湖南或SELECT 学号,姓名,籍贯 FROM 学生 WHERE NOT(籍贯=湖南)例 列出江苏籍和贵州籍的学生名单。SELECT 学号,姓名

21、,籍贯 FROM 学生 WHERE 籍贯 IN (江苏,贵州)以上命令的功能等同于：SELECT 学号,姓名,籍贯 FROM 学生 WHERE 籍贯=江苏 OR 籍贯=贵州例 列出入学成绩在560分到650分之间的学生名单。SELECT 学号,姓名,入学成绩 FROM 学生 WHERE 入学成绩 BETWEEN 560 AND 650在以上命令的功能等同于：SELECT 学号,姓名,专业 FROM 学生 WHERE 入学成绩=560 AND 入学成绩ANY；(SELECT 成绩 FROM 选课 WHERE 课程号=01102)该查询必须做两件事：先找出选修“01102”课的所有学生的期末成绩（

22、比如说结果为92和51），然后在选修“01101”课的学生中选出其成绩高于选修“01102”课的任何一个学生的成绩（即高于72分）的那些学生。（2）ALL运算符的用法例 列出选修“01101”课的学生，这些学生的成绩比选修“01102”课的最高成绩还要高的学生的学号和成绩。SELECT 学号,成绩FROM 选课 WHERE 课程号=01102 AND成绩ALL (SELECT 成绩 FROM 选课 WHERE 课程号=01102)该查询的含义是：先找出选修“01102”课的所有学生的成绩（比如说结果为87和72），然后再在选修“01102”课的学生中选出其成绩中高于选修“01102”课的所有成

23、绩（即高于87分）的那些学生。（3）IN运算符的用法例 列出选修“数据库原理”或“软件工程”的所有学生的学号。 SELECT 学号 FROM 选课 WHERE 课程号 IN;(SELECT 课程号 FROM 课程 WHERE 课程名=数据库原理OR课程名=软件工程)IN是属于的意思，等价于“=ANY”，即等于子查询中任何一个值。6.3.4 多表查询 在一个表中进行查询，一般说来是比较简单的，而在多表之间查询就比较复杂，必须处理表和表间的联接关系。使用SELECT命令进行多表查询是很方便的。1. 等值联接 等值联接是按对应字段的共同值将一个表中的记录与另一个表中的记录相联接。例 输出所有学生的成

24、绩单，要求给出学号、姓名、课程号、课程名和成绩。 SELECT a.学号,a.姓名,b.课程号,c.课程名,b.成绩；FROM 学生 a,选课 b,课程 c;WHERE a.学号=b.学号 AND b.课程号=c.课程号图6-2学生成绩查询结果例 列出男生的选课情况，要求列出学号、姓名、课程号、课程名、授课教师和学分数。SELECT a.学号,a.姓名 AS 学生姓名,b.课程号,c.课程名,e.姓名 AS 教师姓名,c.学分;FROM 学生 a,选课 b,课程 c,授课 d,教师 e;WHERE a.学号=b.学号 AND b.课程号=c.课程号 AND c.课程号=d.课程号;AND d.教师号=e.教师号 AND a.性别=男图6-3男生选课情况查询结果2. 非等值联接 例 列出选修“01102”课的学生中，期末成绩大于学号为“200109”的学生该门课成绩的那些的学号及其成绩。SELECT a.学号,a.成绩 FROM 选课a,选课b;WHERE a.成绩b.成绩AND a.课程号=b.课程号AND b.课程号=01102AND b.学号=