第六章 SQL语言的应用.docx

第六章 SQL语言的应用.docx

《第六章 SQL语言的应用.docx》由会员分享，可在线阅读，更多相关《第六章 SQL语言的应用.docx（24页珍藏版）》请在冰豆网上搜索。

第六章SQL语言的应用

院系：

经济管理系

教研室：

信管

教师：

《数据库原理及应用》课程教案

课程名称

数据库原理及应用

课程学分

3

课程属性

必修课（∨）专业选修课（）校定公共选修课（）

课程总学时

48学时

其中：

讲课：

32学时

实验：

16学时

先修课程

信息技术基础

授课专业、班级

经管系工商、信管、经济学、会计、工程造价各专业

主要教材

教材名称

出版社

作者

出版时间

VisualFoxPro程序设计教程（第二版）

北京邮电大学出版社

刘卫国

2006

教材名称

出版社

作者

出版时间

数据库系统概论（第三版）

高等教育出版社

王珊，萨师煊

2006

教学目的

及要求

教学目标：

本章主要介绍SQL语言的概念和应用，SQL语言作为关系数据库的通用语言，是本门课程的重点部分，也是其他很多课程的基础，通过课程学习

要求学生能够达到以下目标：

1.掌握SQL语言的概念和基本功能。

2.熟练掌握SQL语言数据查询的功能。

3.掌握SQL语言数据定义和数据操纵的功能。

3.

重点难点：

本章是本课程的重点部分，其中SQL语言的数据查询功能是重点和难点部分。

解决方法：

SQL语言比较抽象，并且参数很多。

针对该特点，在讲解过程中，必须结合具体实例，用SQL语言来实际操作表，通过课堂演示使学生掌握其功能。

同时对大量参数要从少量的简单参数入手，逐渐增加，循序渐进。

教学方法和手段：

多媒体教学、实例演示、板书

讲授学时：

4学时

注：

表中（）选项请打“∨”

第六章SQL语言的应用

[教学目的与要求]

通过课程学习，掌握SQL语言的概念和基本功能，熟练掌握SQL语言数据查询的功能，掌握SQL语言数据定义和数据操纵的功能

4.

[教学内容]

本章主要介绍SQL语言的概念和应用，内容包括SQL语言的概念和发展历程，SQL语言的功能，SQL语言的查询语句Select的使用方法。

SQL语言在VisualFoxPro中的使用方法。

[教学重点与难点]

SQL语言的数据查询语句Select的使用方法。

6.1SQL语言概述

20世纪80年代初，美国国家标准协会（ANSI）开始着手制定SQL标准，最早的ANSI标准于1986年完成，它也被叫做SQL86。

SQL标准的出台使SQL作为标准关系数据库语言的地位得到了加强。

随后，SQL标准几经修改和完善，其间经历了SQL89、SQL92，一直到最近的SQL99等多个版本，每个新版本都较前面的版本有重大改进，SQL99更是包含了对诸如面向对象、递归、触发器等概念的支持。

目前，各主流数据库产品采用的SQL标准是1992年制定的SQL92。

按照ANSI的规定，SQL被作为关系数据库的标准语言。

SQL语句可以用来执行各种各样的操作。

目前流行的关系数据库管理系统，如Qracle、Sybase、SQLServer、VisualFoxPro等都采用了SQL语言标准，而且很多数据库都对SQL语句进行了再开发和扩展。

SQL语言具有如下特点：



（1）SQL是一种一体化的语言。

尽管设计SQL的最初目的是查询，数据查询也是其最重要的功能之一，但SQL决不仅仅是一个查询工具，它集数据定义、数据查询、数据操纵和数据控制功能于一体，可以独立完成数据库的全部操作。



（2）SQL语言是一种高度非过程化的语言。

它没有必要一步步地告诉计算机“如何”去做，而只需要描述清楚用户要“做什么”，SQL语言就可以将要求交给系统，自动完成全部工作。

（3）SQL语言非常简洁。

虽然SQL语言功能很强，但它只有为数不多的9条命令：

CREATE、DROP、ALTER、SELECT、INSERT、UPDATE、DELETE、GRANT、REVOKE。

另外SQL的语法也非常简单，它很接近英语自然语言，因此容易学习和掌握。

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

现在很多数据库应用开发工具都将SQL语言直接融入到自身的语言之中，使用起来更方便，VisualFoxPro就是如此。

这些使用方式为用户提供了灵活的选择余地。

此外，尽管SQL的使用方式不同，但SQL语言的语法基本是一致的。



VisualFoxPro在SQL方面支持数据定义、数据查询和数据操纵功能，但在具体实现方面也存在一些差异。

另外，由于VisualFoxPro自身在安全控制方面的缺陷，所以它没有提供数据控制功能。



SQL虽然在各种数据库产品中得到了广泛的支持，但迄今为止，它只是一种建议标准，各种数据库产品中所实现的SQL在语法、功能等方面均略有差异，本章讲述VisualFoxPro中SQL的语法、功能与应用。

6.2数据定义

6.2.1建立表结构

命令格式是：



CREATETABLE|DBF<表名1>[NAME<长表名>][FREE]

（<字段名1><类型>（<宽度>[,<小数位数>]）[NULL|NOTNULL]

[CHECK<条件表达式1>[ERROR<出错显示信息>]]

[DEFAULT<表达式1>][PRIMARYKEY|UNIQUE]REFERENCES<表名2>[TAG<标识1>]

[<字段名2><类型>（<宽度>[,<小数位数>]）[NULL|NOTNULL]

[CHECK<条件表达式2>[ERROR<出错显示信息>]]

[DEFAULT<表达式2>][PRIMARYKEY|UNIQUE]REFERENCES<表名3>[TAG<标识2>]

……）|FROMARRAY<数组名>

参数说明：



表名1：

要建立的表的名称。



FREE：

如果当前已经打开一个数据库，这里所建立的新表会自动加入该数据库，除非使用参数“FREE”说明该新表作为一个自由表不加入当前数据库。

如果没有打开的数据库，该参数无意义。



字段名1、字段名2、……：

所要建立的新表的字段名，在语法格式中，两个字段名之间的语法成分都是对一个字段的属性说明，包括：

类型——说明字段类型。



宽度及小数位数——字段宽度及小数位数。



NULL、NOTNULL——该字段是否允许“空值”，其默认值为NULL，即允许“空”值。

CHECK条件表达式——用来检测字段的值是否有效，这是实行数据库的一种完整性检查。



ERROR出错显示信息——当完整性检查有错误，即条件表达式的值为假时的提示信息。

应当注意，当为一个表的某个字段建立了实行完整性检测的条件表达式后，在对该数据表输入数据时，系统会自动检测所输入的字段值是否使条件表达式为假，当有一个数据使其为假时，系统自动显示这里所提示的出错信息。



●DEFAULT表达式——为一个字段指定的默认值。



●PRIMARYKEY——指定该字段为关键字段，非数据库表不能使用该参数。

●UNIQUE——指定该字段为一个侯选关键字段。

注意，指定为关键或侯选关键的字段都不允许出现重复值，这称为对字段值的唯一性约束。



●REFERENCES表名——这里指定的表作为新建表的永久性父表，新建表作为子表。



●TAG标识——父表中的关联字段，若缺省该参数，则默认父表的主索引字段作为关联字段。

数组名——用指定数组的值建立输入表。



从以上命令格式可以看出，用CREATETABLE命令建立表可以完成用表设计器完成的所有功能。

除了建立表的基本功能外，它还包括满足实体完整性的主关键字（主索引）PRIMARYKEY、定义域完整性的CHECK约束及出错提示信息ERROR、定义默认值的DEFAULT等。

另外还有描述表之间联系的FOREIGNKEY和REFERENCES等。



例利用SQL命令建立学生管理数据库，其中包含3个表：

学生表、选课表和课程表。



操作步骤如下：



（1）用CREATE命令建立数据库。



CREATEDATABASED:

反斜扛学生管理

（2）用CREATE命令建立学生表。



CREATETABLE学生（学号C（5）PRIMARYKEY,姓名C（8）,;

入学成绩N（5,1）CHECK（入学成绩>0）ERROR″成绩应该大于0！

″）

（3）建立课程表。



CREATETABLE课程（课程号C（5）PRIMARYKEY,课程名C（20）,学分N

（1））

（4）建立选课表。



CREATETABLE选课（学号C（5），课程号C（5），;

成绩ICHECK（成绩>=0AND成绩<=100）;

ERROR″成绩值的范围0~100！

″DEFAULT60,;

FOREIGNKEY学号TAG学号REFERENCES学生，；

FOREIGNKEY课程号TAG课程号REFERENCES课程

图6-1利用SQL命令建立数据库

注意：

用SQLCREATE命令新建的表自动在最小可用工作区打开，并可以通过别名引用，新表的打开方式为独占方式，忽略SETEXCLUSIVE的当前设置。



如果建立自由表（当前没有打开的数据库或使用了FREE），则很多选项在命令中不能使用，如NAME、CHECK、DEFAULT、FOREIGNKEY、PRIMARYKEY和REFERENCES等。



6.2.2删除表

删除表的SQL命令是：



DROPTABLE<表名>

DROPTABLE命令直接从磁盘上删除所指定的表文件。

如果指定的表文件是数据库中的表并且相应的数据库是当前数据库，则从数据库中删除了表。

否则虽然从磁盘上删除了表文件，但是记录在数据库文件中的信息却没有删除，此后会出现错误提示。

所以要删除数据库中的表时，最好应使数据库是当前打开的数据库，在数据库中进行操作。



6.2.3修改表结构

修改表结构的命令是ALTERTABLE，该命令有3种格式。



格式1：



ALTERTABLE<表名1>

ADD|ALTER[COLUMN]<字段名><字段类型>[（<宽度>[,<小数位数>]）]

[NULL|NOTNULL][CHECK<逻辑表达式>[ERROR<出错显示信息>]]

[DEFAULT<表达式>][PRIMARYKEY|UNIQUE]

[REFERENCES<表名2>[TAG<标识名>]]

例为课程表增加一个整数类型的学时字段。



ALTERTABLE课程;

ADD学时ICHECK（学时>16）ERROR″学时应该大于16！

″

格式2：



ALTERTABLE<表名>

ALTER[COLUMN]<字段名>[NULL|NOTNULL]

[SETDEFAULT<表达式>[SETCHECK<逻辑表达式>[ERROR<出错显示信息>]]

[DROPDEFAULT][DROPCHECK]

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

例删除学时字段的有效性规则。



ALTERTABLE课程ALTER学时DROPCHECK

格式3：



ALTERTABLE<表名>[DROP[COLUMN]<字段名>]

[SETCHECK<逻辑表达式>[ERROR<出错显示信息>]]

[DROPCHECK]

[ADDPRIMARYKEY<表达式>TAG<索引标识>[FOR<逻辑表达式>]]

[DROPPRIMARYKEY]

[ADDUNIQUE<表达式>[TAG<索引标识>[FOR<逻辑表达式>]

[DROPUNIQUETAG<索引标识>

[ADDFOREIGNKEY<表达式>TAG<索引标识>[FOR<逻辑表达式>]]

REFERENCES<表名2>[TAG<索引标识>]]

[DROPFOREIGNKEYTAG<索引标识>[SAVE]]

[RENAMECOLUMN<原字段名>TO<目标字段名>]

该格式的命令可以删除指定字段（DROP[COLUMN]）、修改字段名（RENAMECOLUMN）、修改指定表的完整性规则，包括主索引、外关键字、候选索引及表的合法值限定的添加与删除。

例删除课程表中的学时字段。



ALTERTABLE课程DROPCOLUMN学时

VisualFoxPro的SQLSELECT命令的语法格式是：



SELECT[ALL|DISTINCT]

[<别名>.]<选项>[AS<显示列名>][,[<别名>.]<选项>[AS<显示列名>]…]

FROM[<数据库名!

]<表名>[[AS]<本地别名>]

[[INNER|LEFT[OUTER]|RIGHT[OUTER]|FULL[OUTER]

JOIN<数据库名>!

]<表名>[[AS]<本地别名>][ON<联接条件>…]

[[INTO<目标>|[TOFILE<文件名>][ADDITIVE]

|TOPRINTER[PROMPT]|TOSCREEN]]

6.3数据查询

VisualFoxPro的SQLSELECT命令的语法格式是：

SELECT[ALL|DISTINCT]

[<别名>.]<选项>[AS<显示列名>][,[<别名>.]<选项>[AS<显示列名>]…]

FROM[<数据库名!

]<表名>[[AS]<本地别名>]

[[INNER|LEFT[OUTER]|RIGHT[OUTER]|FULL[OUTER]

JOIN<数据库名>!

]<表名>[[AS]<本地别名>][ON<联接条件>…]

[[INTO<目标>|[TOFILE<文件名>][ADDITIVE]

|TOPRINTER[PROMPT]|TOSCREEN]]

[PREFERENCE<参照名>][NOCONSOLE][PLAIN][NOWAIT]

[WHERE<联接条件1>[AND<联接条件2>…]

[AND|OR<过滤条件1>[AND|OR<过滤条件2>…]]]

[GROUPBY<分组列名1>[,<分组列名2>…]][HAVING<过滤条件>]

[UNION[ALL]SELECT命令]

[ORDERBY<排序选项1>[ASC|DESC][,<排序选项2>[ASC|DESC]…]]

6.3.1基本查询

所谓基本查询是指无条件查询，其格式是：



SELECT[ALL|DISTINCT]

[<别名>.]<选项>[AS<显示列名>],[<别名>.]<选项>[AS<显示列名>…]

FROM<表名1>[别名1>][,<表名2>[别名2>…]

其中ALL表示输出所有记录，包括重复记录。

DISTINCT表示输出无重复结果的记录。

当选择多个数据库表中的字段时，可使用别名来区分不同的表。

显示列名的作用是在输出结果中，如果不希望使用字段名，可以根据要求设置一个名称。

选项可以是字段名、表达式或函数。

表名代表要查询的表。



例列出学生名单。



OPENDATABASED:

学生管理

SELECT*FROM学生

命令中的*表示输出所有字段，数据来源是学生表，所有内容以浏览方式显示。



例列出所有学生姓名，去掉重名。



SELECTDISTINCT姓名AS学生名单FROM学生

SELECT命令中的选项，不仅可以是字段名，还可以是表达式，也可以是一些函数，SELECT命令可操纵的函数很多。



例将所有的学生入学成绩四舍五入，只显示学号、姓名和入学成绩。



SELECT学号,姓名,ROUND（入学成绩,0）AS″入学成绩″FROM学生

注意：

这个结果不影响数据库表中的结果，只是在输出时通过函数计算输出。



例求出所有学生的入学成绩平均分。



SELECTAVG（入学成绩）AS″入学成绩平均分″FROM学生

由以上两例可见，直接使用VisualFoxPro提供的各种SQL函数在输出时进行计算，便可得到相应的输出结果。



6.3.2带条件查询

WHERE是条件语句关键字，是可选项，其格式是：



WHERE<条件表达式>

其中条件表达式可以是单表的条件表达式，也可以是多表之间的条件表达式，表达式用的比较符为：

=（等于）、<>、！

=（不等于）、==（精确等于）、>（大于）、>=（大于等于）、<（小于）、<=（小于等于）



例求出湖南学生入学成绩平均分。



SELECT籍贯,AVG（入学成绩）AS″入学成绩平均分″FROM学生WHERE籍贯=″湖南″

条件表达式是指查询的结果集合应满足的条件，如果某行条件为真就包括该行记录。

下面是可用于条件表达式中几个特殊运算符的意义和使用方法。



●<字段><比较符>ALL（<子查询>）

●<字段><比较符>ANY（<子查询>）

●<字段>BETWEEN<范围始值>AND<范围终值>

●EXISTS（<子查询>）

●<字段>IN<结果集合>或者<字段>IN（<子查询>）

●<字段>LIKE<字符表达式>

●<字段><比较符>SOME（<子查询>）

例列出非湖南籍的学生名单。

SELECT学号,姓名,籍贯FROM学生WHERE籍贯<>″湖南″

以上命令的功能等同于：



SELECT学号,姓名,籍贯FROM学生WHERE籍贯!

=″湖南″

或SELECT学号,姓名,籍贯FROM学生WHERENOT（籍贯=″湖南″）

例列出江苏籍和贵州籍的学生名单。



SELECT学号,姓名,籍贯FROM学生WHERE籍贯IN（″江苏″,″贵州″）

以上命令的功能等同于：



SELECT学号,姓名,籍贯FROM学生WHERE籍贯=″江苏″OR籍贯=″贵州″

例列出入学成绩在560分到650分之间的学生名单。



SELECT学号,姓名,入学成绩FROM学生WHERE入学成绩BETWEEN560AND650

在以上命令的功能等同于：



SELECT学号,姓名,专业FROM学生WHERE入学成绩>=560AND入学成绩<=650

例列出所有的姓赵的学生名单。



SELECT学号,姓名FROM学生WHERE姓名LIKE″赵%″

以上命令的功能等同于：



SELECT学号,姓名,专业FROM学生WHERE姓名=″赵″

例列出所有成绩为空值的学生学号和课程号。

SELECT学号,课程号FROM选课WHERE成绩ISNULL

在以上命令中，使用了运算符ISNULL，该运算符是测试字段值是否为空值，在查询时用“字段名IS[NOT]NULL”的形式，而不能写成“字段名=NULL”或“字段名！

=NULL”。



6.3.3嵌套查询

有时候一个SELECT命令无法完成查询任务，需要一个子SELECT的结果作为条件语句的条件，即需要在一个SELECT命令的WHERE子句中出现另一个SELECT命令，这种查询称为嵌套查询。

通常把仅嵌入一层子查询的SELECT命令称为单层嵌套查询，把嵌入子查询多于一层的查询称为多层嵌套查询。

VisualFoxPro只支持单层嵌套查询。



1.返回单值的子查询

例列出选修“数据库原理”的所有学生的学号。



SELECT学号FROM选课WHERE课程号=;

（SELECT课程号FROM课程WHERE课程名=″数据库原理″）

上述SQL语句执行的是两个过程，首先在课程表中找出“数据库原理”的课程号（比如“01001”），然后再在选课表中找出课程号等于“01101”的记录，列出这些记录的学号。



2.返回一组值的子查询

若某个子查询返回值不止一个，则必须指明在WHERE子句中应怎样使用这些返回值。

通常使用条件ANY（或SOME）、ALL和IN。



（1）ANY运算符的用法

例列出选修“01101”课的学生中期末成绩比选修“01102”的最低成绩高的学生的学号和成绩。



SELECT学号,成绩FROM选课WHERE课程号=″01101″AND成绩>ANY；

（SELECT成绩FROM选课WHERE课程号=″01102″）

该查询必须做两件事：

先找出选修“01102”课的所有学生的期末成绩（比如说结果为92和51），然后在选修“01101”课的学生中选出其成绩高于选修“01102”课的任何一个学生的成绩（即高于72分）的那些学生。



（2）ALL运算符的用法

例列出选修“01101”课的学生，这些学生的成绩比选修“01102”课的最高成绩还要高的学生的学号和成绩。



SELECT学号,成绩FROM选课WHERE课程号=″01102″AND成绩>ALL（SELECT成绩FROM选课WHERE课程号=″01102″）

该查询的含义是：

先找出选修“01102”课的所有学生的成绩（比如说结果为87和72），然后再在选修“01102”课的学生中选出其成绩中高于选修“01102”课的所有成绩（即高于87分）的那些学生。



（3）IN运算符的用法

例列出选修“数据库原理”或“软件工程”的所有学生的学号。

SELECT学号FROM选课WHERE课程号IN;

（SELECT课程号FROM课程WHERE课程名=″数据库原理″OR课程名=″软件工程″）

IN是属于的意思，等价于“=ANY”，即等于子查询中任何一个值。



6.3.4多表查询

在一个表中进行查询，一般说来是比较简单的，而在多表之间查询就比较复杂，必须处理表和表间的联接关系。

使用SELECT命令进行多表查询是很方便的。



1.等值联接

等值联接是按对应字段的共同值将一个表中的记录与另一个表中的记录相联接。



例输出所有学生的成绩单，要求给出学号、姓名、课程号、课程名和成绩。

SELECTa.学号,a.姓名,b.课程号,c.课程名,b.成绩；

FROM学生a,选课b,课程c;

WHEREa.学号=b.学号ANDb.课程号=c.课程号

图6-2学生成绩查询结果

例列出男生的选课情况，要求列出学号、姓名、课程号、课程名、授课教师和学分数。



SELECTa.学号,a.姓名AS学生姓名,b.课程号,c.课程名,e.姓名AS教师姓名,c.学分;

FROM学生a,选课b,课程c,授课d,教师e;

WHEREa.学号=b.学号ANDb.课程号=c.课程号ANDc.课程号=d.课程号;

ANDd.教师号=e.教师号ANDa.性别=″男″

图6-3男生选课情况查询结果

2.非等值联接

例列出选修“01102”课的学生中，期末成绩大于学号为“200109”的学生该门课成绩的那些的学号及其成绩。



SELECTa.学号,a.成绩FROM选课a,选课b;

WHEREa.成绩>b.成绩ANDa.课程号=b.课程号ANDb.课程号=″01102″ANDb.学号=″