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

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

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

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

第5章关系数据库标准语言SQL（16课时）

一、【教学目标】

1.了解SQL语言的作用和主要特点。

2.理解SQL语言的数据定义功能，掌握用CREATE命令建立表结构、用ALTER命令修改

表结构、用DROP命令删除表。

3.理解SQL语言的数据操作功能，掌握用INSERT命令插入记录、用DELETE命令删除记

录、用UPDATE命令更新记录。

4.理解SQL语言的数据查询功能，掌握用SELECT命令进行简单查询、条件查询、多表

查询、嵌套查询、统计查询、分组查询等。

二、【重点和难点】

重点：

1．SQL定义、修改表结构的规则与方法。

2．SQL数据修改方法与规则。

3．SQL数据查询功能的语句规则和特点。

4．综合应用SQL数据查询功能完成实际问题。

难点：

1.使用嵌套子查询的方法和规则。

2.处理数据表自身连接和复合条件查询的方法。

3.综合应用SQL数据查询功能完成实际问题的能力。

三、【学法指导】

1.SQL语言是数据库标准语言，包含数据定义、数据操纵、数据控制等，VFP对SQL语言的支持使VFP更加完善。

2.所有关系数据库都支持SQL语言，具有通用性。

四、【教学要点】

1．SQL概述

SQL是结构化查询语言StructuredQueryLanguage的缩写，查询是SQL的重要组成部分，SQL还包括数据定义、数据操纵和数据控制功能等部分，SQL已经成为关系数据库的标准数据语言，该语言具有如下特点：

SQL是一种一体化的语言，它包括了数据定义、数据查询、数据操纵和数据控制等方面的功能，它可以完成数据库活动的全部工作，

SQL是一种高度非过程化的语言，用户只需要描述“做什么”，SQL语言就可以将要求交给系统，系统自动完成全部工作。

SQL语言非常简洁，很接近自然语言（英语），因此易学，易掌握。

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

2．查询功能

SQL的核心是查询，查询命令是SELECT。

常用语法格式如下：

SELECT……

FROM……

[WHERE……]

[GROUPBY……][HAVING……]

[UNION[ALL]……]

[ORDERBY……]

说明：

1SELECT说明要查询的数据。

2FROM说明要查询的数据来自那个或那些表可以对单个表或多个表进行查询。

3WHERE说明查询条件即选择元组的条件。

4GROUPBY短语用于对查询结果进行分组，可以利用它进行分组汇总。

5HAVING必须跟随GROUPBY使用，用来限定分组必须满足的条件。

6ORDERBY用来对查询的结果进行排序。

（1）简单查询

无条件查询：

由SELECT和FROM短语构成

条件查询：

由SELECT、FROM和WHERE短语构成

[DISTINCT]短语,去掉结果中的重复值

例1：

从职工关系检索仓库WH2的所有工资值

SELECT工资FROM职工WHERE仓库号=“WH2”

例2：

检索仓库中的所有元组

SELECT*FROM仓库

等同于

select仓库号，城市，面积from仓库

（2）简单的联接查询

联接是关系的基本操作之一，联接查询是基于多个关系的查询。

例：

找出工作在面积大于400的仓库的职工号以及这些职工工作所在的城市

select职工号，城市from仓库，职工；

where（面积>400）and（职工.仓库号=仓库.仓库号）

说明:

①在VF的SQL命令中分号是续行符号。

②当from后的多个关系中有同名字段时,须用前缀指明字段所属的表。

③多表连接查询时要指明查询联接条件。

（3）嵌套查询（有两个以上查询条件，且在不同关系中）

当查询所要求的结果出自一个关系，但相关的条件却涉及多个关系，则使用嵌套查询。

即当检索关系X中的元组时，它的条件依赖于相关的关系Y中的元组属性值，此时用嵌套查询比较方便。

外层查询的条件依赖于内层查询的结果。

注：

记录字段值区分大小写

例1：

哪些城市至少有一个仓库的职工工资为1250元？

Select城市from仓库WHERE仓库号IN；

（SELECT仓库号FROM职工WHERE工资=1250）

例2：

找出和职工E4挣同样工资的所有职工

select职工号from职工where工资=；

（select工资from职工where职工号=“E4”）

例3：

查询所有职工的工资都多于1210元的仓库的信息

分析：

①查询范围来源于职工表，而结果在仓库表，故用嵌套查询。

②若条件设为工资>1210,则比如WHI中工资有满足条件的,也没不满足条件的,故无法适应此情况

应该设为:

仓库号notin……工资<=1210

③如果某仓库还没有职工,则也满足②的条件notin职工，故应添加条件,保证该仓库至少有一名职工。

解:

select*from仓库where仓库号notin;

（select仓库号from职工where工资<=1210）

and仓库号in（select仓库号from职工）

（4）几个特殊运算符

BETWEEN……AND……在……和……之间

例：

检索出工资在1220元到1240范围内的职工信息。

Select*from职工where工资between1220and1240

说明：

①此条件等价于（工资>=1220）and（工资<=1240），但更简洁。

②“不在……之间”可用notbetween……and……

select*from职工where工资notbetween1220and1240

LIKE%——表示0个或多个字符

LIKE_——表示一个字符

例：

检索出供应商是某公司的信息。

select*from供应商where供应商名Like“%公司”

不等于“！

=”可用否定运算符NOT表示。

例：

找出不在北京的供应商信息。

Select*from供应商wherenot（地址=“北京”）

（5）排序

使用排序短语ORDERBY可以查询结果排序。

格式：

ORDERBY字段名1[ASC|DESC][，字段名2[ASC|DESC]…]

说明：

①可以指定排序方式为升序或降序

②允许按一列或多列排序

例：

先按仓库号升序排序，再按工资降序排序检索出全部职工信息。

Selet*from职工orderby仓库号ASC，工资desc

（6）简单的计算查询

SQL语句可以直接对检索结果进行计算，有5个计算函数：

1COUNT——计数

2SUM——求和

3AVG——计算平均值

4MAX——就最大值

5MIN——求最小值

例：

求WH2仓库的职工人数、工资总数、平均工资、最高工资和最低工资。

Selectcount（*）as人数，avg（工资）as平均工资，;

Sum（工资）as工资合计,max（工资）as最高工资，；

min（工资）as最低工资from职工Where仓库号=“WH2”

（7）分组与计算查询

短语：

GROUPBY组名[，组名……][HAVING条件]

功能：

先按属性分组，再进行查询，还可根据多个属性分组。

当需要分组满足某条件时才检索，可用Having子句来限定分组。

例1：

求每个仓库的职工的平均工资。

select仓库号，AVG（工资）from职工groupby仓库号

例2：

求至少有两个职工的每个仓库的平均工资。

select仓库号，count（*）from职工;

groupby仓库号havingcount（*）>=2

（8）利用空值查询

ISNULL

ISNOTNULL

例1：

找出尚未确定供应商的定单信息。

Select*from订购单where供应商号ISNULL

注：

空值查询不能用“=NULL”，因为空值不是一个确定的值，不能用“=”运算符比较

例题：

已确定了供应商。

Select*from订购单where供应商号ISNOTNULL

（9）别名与自连接查询

别名定义：

为了简化关系名.

格式；<关系名><别名>

例:

从定货管理的四张表中找出仓库和供应商都在北京的供应商.

Select供应商名from供应商S,订购单P,职工E,仓库W；

where地址=“北京”and城市=“北京”；

ands.供应商号=p.供应商号；

andp.职工号=E.职工号；

ande.仓库号=w.仓库号

自联接查询

自联接：

将同一关系与其自身进行联接称自联接。

在此关系上，存在着一种递归联系，既关系中的一些元组，据出自同一值域的两个不同属性，可以与另外一些元组有一种对应关系（一对多的联系）。

例：

雇员（雇员号，雇员姓名，经理）

雇员号和经理两个属性同一值域，且同一元组的这两个属性值是“上、下级”关系

根据雇员关系例出上一级经理及所领导的职员清单。

SelectS.雇员姓名，“领导”，E.雇员姓名from雇员S,雇员E;

whereS.雇员号=E.经理

（10）内外层互相关联嵌套查询

内外层互相关的查询：

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

例：

例出每个职工经手的具有最高总金额的订购单信息。

Selectout.职工号，out.供应商号，out.订购单号,out.总金额;

from订购单outwhere总金额=；

（selectmax（总金额）from订购单innerl;

whereout.职工号=innerl.职工号）

（11）使用量词和谓词的查询

在嵌套查询中还可以有如下两种形式：

1、<表达式><比较运算符>[ANY|SOME]（子查询）

2、[NOT]EXISTS（子查询）

说明：

①any、all、some是量词，其中any和some是同义词，比较时只要子查询中有一行能使结果为真，则结果就为真；而all要求子查询中的所有行都使结果为真时，结果才为真。

②exists或notexists用来检查在子查询中是否有结果返回，即存在元组或不存在元组。

例1：

检索那些仓库中还没有职工的仓库的信息。

Select*from仓库wherenotexists;

（Select*from职工where仓库号=仓库.仓库号）

例2：

检索有职工的工资大于且等于“WH1”仓库中任何一名职工的工资的仓库号。

Selectdistinct仓库号from职工where工资>=any;

（select工资from职工where仓库号=“WH1”）

例3：

检索有职工的工资大于或等于“WH1”仓库中所有职工的工资的仓库号。

Selectdistinct仓库号from职工where工资>=all;

（select工资from职工where仓库号=“WH1”）

（12）超联接查询

超联接：

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

格式：

SELECT……

FROMTableINNER|KEFT|RIGHT|FULLJoinTableONJoinCondition

WHERE……

说明：

①INERJOIN普通联接,称内部联接

②LEFTJOIN为左联接

③RIGHTJOIN为右联接

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

⑤ONJoinCondicion联接条件

例1：

内部联接___只有满足联接条件的记录才出现在查询结果中。

SELECT仓库.仓库号，城市，面积，职工号，工资；

From仓库innerjoin职工on仓库.仓库号=职工.仓库号

例2：

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

SELECT仓库.仓库号，城市，面积，职工号，工资；

From仓库leftjoin职工on仓库.仓库号=职工.仓库号

例3：

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

SELECT仓库.仓库号，城市，面积，职工号，工资；

From仓库rightjoin职工on仓库.仓库号=职工.仓库号

例4：

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

SELECT仓库.仓库号，城市，面积，职工号，工资；

From仓库fulljoin职工on仓库.仓库号=职工.仓库号

（13）集合的合并运算

SQL可以将两个select语句的查询结果通过合并运算union合并成一个查询结果，要求：

两查询结果具有相同的字段个数，且对应字段的值要有相同的值域。

例1：

查询北京和上海的仓库信息

select*from仓库where城市=“北京”union；

select*from仓库where城市=“上海”

（14）VisualFoxPro中SQLSELECT的几个特殊选项

显示部分结果

说明：

有时只需显示满足条件的前几个记录，用短语：

TOPnExpr[PERCENT]需与ORDREBY短语同时用

nExpr是1~32767.

Percent是0.01~99.99显示结果中前百分之几的记录.

例1：

显示工资最高的三位职工的信息。

Select*TOP3from职工orderby工资desc

例2：

显示工资最低的那30%职工的信息

select*top30percertfrom职工orderby工资

将查询结果存放到数组中.

用INTOARRAYArrayName短语将查询结果存放到数组中。

一般用二维数组，每行一条记录，每列对应查询结果的一列。

例：

将查询到的职工信息存放在数组tmp中.

Select*from职工intoarraytmp

结果存放在临时文件中

用intocurserCursername将结果存放到临时数据库文件中，临时文件是一个只读的dbf文件，当关闭文件时该文件将自动删除。

例：

按查询到的职工信息存放在TOP中

select*from职工INTOCURSORtmp

将查询结果存放到永久表中.

短语：

INTODBF|TABLETableName可将结果存放到永久表中.（dbf文件）

select*Top3from职工intotablehighsalorderby工资desc

将查询结果存放到文本文件中.

短语:

TOFILEfilename[ADDITIVE]

例：

select*top3from职工tofiletimeorderby工资dese

将查询结果输出打印机

短语：

TO[PRINT]可将结果输出到打印机，若用[prompt]选项则在开始打印之前会打开打印机设置对话框。

3．操作功能

（1）插入

第一种标准格式：

INSERTINTOdbf_name[（fnamel[,fname2,…]）]

VALUES（eExpressionl[,eExpression2,…]）

第二种特殊格式

INSERTINTOdbf_namefromARRAYArrayName|FROMMEMVAR

说明：

✧向dbf_name指定的表中插入记录，当插入的不是完整记录时，用fnamel,fname2,…指定字段；用values短语给出具体的记录值。

✧Fromarrayarrayname说明从指定的数组中插入记录值。

✧FormMEMVAR说明根据同名的内存变量插入记录，若不存在同名的变量，则相应的字段为默认值或定值。

例：

向职工表中插入元组（“WH4”“E5”,1245）

Insentinto职工Values（“Wh4”,“E5”,1245）

注意：

当一个表定义了主索引或候选索引后，由于其相应字段具有关键字特性，不能为空，所以不能用insert或append插入，只能用此SQL语句插入记录。

（2）更新

格式：

UPDATETableName

SETColumm_namel=eExpressionl[,Columm_Name2=eExpression2…]

Wherecondttion

例：

给所有学生年龄增加1岁

Update学生set年龄=年龄+1

（3）删除

格式：

DELETEFROMTableName[WHERECondition]

说明：

where指定被删除的记录满足的条件，不指定则删除表中的全部记录。

例：

删除供应商号为空值的定单。

Deletefrom订购单where供应商号ISNULL

4．定义功能

（1）表的定义

命令格式：

CREATETABLE|DBFTableName|[长表名][FREE]

（FieldNamel类型，宽度[NULL|NOTNULL]

[CHECK规则[信息][默认值]

[PRIMARYKEY|UNIQUE]

[REFERENCESTableName2[TAGtagName1]]

[,FieldName2…]

[,FOREIGNKEYeExpressionTAAGTagName[NODUP]

………

例：

用命令建立学生表和成绩表。

CREATETABLE学生（

学号C（7）PRIMATYKEY,

姓名C（8）

年龄ICKECKbetween（年龄，17,20）error“年龄出错！

”

Default18）

CREATETABLE成绩（

学号C（7）refe学生tag学号,

课程号C（6）refe成绩tag课程号,

Primarykey学号+课程号tag学号课程）

（2）表的删除

SQL命令：

DROPTABLEtable_name

功能：

从磁盘上删除table_name对应的dbf文件。

（3）表结构的修改

命令：

ALTERTABLE

有三种格式：

格式1：

ALTERTABLETablename|ADD|ALTER[column]fieldname1

Fieldtype[（nEileldwidth[,nprecision]）][null|notnull]

[checklExpression1[ERRORcMessageTtext1]][Defaultexpressionl]

[primarykey|unique]

[referencestablename2[tagtagname1]]

例：

为学生增加一个总成绩字段

Altertable学生；

Add总成绩Ickeckbetween（总成绩，0,100）

Error“绩应该在0~100间”default80

格式2：

ALTERTABLETablename|ATTER[column]fielname2[null|notnull]

[setdefaultexpression2][setchecklexpression2

[errorcmessageText2]][DROPDefault][dropcheck]

功能：

定义、修改和删除有效性规则和默认值定义。

例：

删除总成绩的有效性规则。

Altertable学生alter总成绩Dropcheckdropdefault

格式3：

Altertabletablename1[drop[column]fieldname3]

[serchecklexpression3[reeorcmessagetext3]]

[dropcheck]

[addprimarmykeyeExpression3tagtagname2[for（expression4）]

[dropprimaryket]

[adduniqueexpression4{TAGtagname3[for表达式]}]

[dropuniquetagtagname4]

[addforeignkey[expression5]tagtagname4[forexpression6]

Referencestablename2[tagtagname5]]

[dropforeignkeytagtagneme6[save]

[renamecolumnfieldname4tofieldname5]

功能：

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

（4）视图的定义

定义：

是根据表定义或派生出来的虚拟表，可以是本地的、远程的或带参数的。

视图可以引用一个或多个表，或引用其他视图。

视图是可更新的，它可引用远程表。

格式：

视图是根据对表的查询定义的

Createviewview_name

ASselect_statement

说明：

select_staement可以是任意的select查询语句，它说明和限定了视图中的数据、视图的字段名将查询中指定的字段名或表中的字段名同名。

从单个表派生出的视图

例1：

createviewe_was;

Select职工号，仓库号from职工

说明：

视图一经定义，就可以和基本表一样进行各种查询，也可以进行一些修改操作。

即等效命令：

（1）Select*frome_w

（2）Select职工号，仓库号frome_w

（3）Select职工号，仓库号from职工

例2：

定义北京仓库的信息视图

Createviewv_bjas;

Seleet仓库号，面积from仓库where城市=“北京”

从多个表派生出视图

例1：

列出每个职工今年具有最高金额的定单信息。

Createviewv_sampleAS;

Selectout.职工号，out.供应商号，out.订购单号，;

out.订购日;期,out总金额；

From订购单outwhere总金额=；

（selectmax（总金额）from订购单innerl;

whereout.职工号=inner1.职工号）

此时，可以在视图中查询：

Select*fromv_sample

例2：

createviewv_empas;

Select职工号，工资，城市from职工，仓库；

Where职工.仓库号=仓库.仓库号

视图中的虚字段

虚字段：

视图中的select子句可以包含算术表达式或函数，由于这些表达式或函数是计算得来的，并不存储表内，故称为虚字段。

例：

定义一