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

1、第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 SQ

2、L数据查询功能的语句规则和特点。4 综合应用SQL数据查询功能完成实际问题。难点：1. 使用嵌套子查询的方法和规则。2. 处理数据表自身连接和复合条件查询的方法。3. 综合应用SQL数据查询功能完成实际问题的能力。三、【学法指导】1. SQL语言是数据库标准语言，包含数据定义、数据操纵、数据控制等，VFP对SQL语言的支持使VFP更加完善。2. 所有关系数据库都支持SQL语言，具有通用性。四、【教学要点】1SQL 概述SQL是结构化查询语言Structured Query Language 的缩写，查询是SQL的重要组成部分，SQL还包括数据定义、数据操纵和数据控制功能等部分，SQL已经成为关

3、系数据库的标准数据语言，该语言具有如下特点：SQL是一种一体化的语言，它包括了数据定义、数据查询、数据操纵和数据控制等方面的功能，它可以完成数据库活动的全部工作，SQL是一种高度非过程化的语言，用户只需要描述“做什么”，SQL语言就可以将要求交给系统，系统自动完成全部工作。SQL语言非常简洁，很接近自然语言（英语），因此易学，易掌握。SQL语言可以直接以命令方式交互使用，也可以嵌入到程序设计语言中以程序方式使用。2查询功能SQL的核心是查询，查询命令是SELECT。常用语法格式如下：SELECTFROMWHEREGROUP BY HAVING UNIONALLORDER BY说明：1 SELE

4、CT说明要查询的数据。2 FROM说明要查询的数据来自那个或那些表可以对单个表或多个表进行查询。3 WHERE说明查询条件即选择元组的条件。4 GROUP BY 短语用于对查询结果进行分组，可以利用它进行分组汇总。5 HAVING必须跟随GROUP BY使用，用来限定分组必须满足的条件。6 ORDER BY用来对查询的结果进行排序。（1）简单查询无条件查询：由SELECT和FROM短语构成条件查询： 由SELECT、FROM和WHERE短语构成DISTINCT短语,去掉结果中的重复值例1：从职工关系检索仓库WH2的所有工资值SELECT工资FROM职工WHERE仓库号=“WH2”例2：检索仓库

5、中的所有元组 SELECT * FROM 仓库等同于 select仓库号，城市，面积 from 仓库（2）简单的联接查询联接是关系的基本操作之一，联接查询是基于多个关系的查询。例：找出工作在面积大于400的仓库的职工号以及这些职工工作所在的城市select 职工号，城市 from仓库，职工；where(面积400) and (职工.仓库号=仓库.仓库号)说明: 在VF的SQL命令中分号是续行符号。当from后的多个关系中有同名字段时,须用前缀指明字段所属的表。多表连接查询时要指明查询联接条件。（3）嵌套查询（有两个以上查询条件，且在不同关系中）当查询所要求的结果出自一个关系，但相关的条件却涉及

6、多个关系，则使用嵌套查询。即当检索关系X中的元组时，它的条件依赖于相关的关系Y中的元组属性值，此时用嵌套查询比较方便。外层查询的条件依赖于内层查询的结果。注：记录字段值区分大小写例1：哪些城市至少有一个仓库的职工工资为1250元？Select城市from 仓库 WHERE仓库号 IN； （SELECT 仓库号 FROM 职工 WHERE工资=1250）例2：找出和职工E4挣同样工资的所有职工 select 职工号 from 职工 where工资=； (select 工资 from职工 where职工号=“E4”)例3：查询所有职工的工资都多于1210元的仓库的信息分析：查询范围来源于职工表，而

7、结果在仓库表，故用嵌套查询。 若条件设为工资1210,则比如WHI中工资有满足条件的,也没不满足条件的 ,故无法适应此情况应该设为:仓库号not in工资=1210如果某仓库还没有职工,则也满足的条件not in职工，故应添加条件,保证该仓库至少有一名职工。解: select * from 仓库 where仓库号not in ; (select 仓库号from 职工 where工资=1220）and(工资=2（8）利用空值查询 IS NULL IS NOT NULL例1：找出尚未确定供应商的定单信息。Select * from 订购单 where 供应商号IS NULL注：空值查询不能用“=N

8、ULL”， 因为空值不是一个确定的值，不能用“=”运算符比较例题：已确定了供应商。Select * from 订购单 where 供应商号IS NOT NULL（9）别名与自连接查询别名定义：为了简化关系名.格式；例:从定货管理的四张表中找出仓库和供应商都在北京的供应商.Select 供应商名 from 供应商S,订购单P,职工E,仓库W； where地址=“北京” and 城市=“北京”；and s.供应商号=p.供应商号；and p.职工号=E.职工号；and e.仓库号=w.仓库号自联接查询自联接：将同一关系与其自身进行联接称自联接。在此关系上，存在着一种递归联系，既关系中的一些元组，据

9、出自同一值域的两个不同属性，可以与另外一些元组有一种对应关系（一对多的联系）。例：雇员（雇员号，雇员姓名，经理）雇员号和经理两个属性同一值域，且同一元组的这两个属性值是“上、下级”关系根据雇员关系例出上一级经理及所领导的职员清单。 Select S.雇员姓名，“领导”，E.雇员姓名 from 雇员S,雇员E;where S.雇员号=E.经理（10）内外层互相关联嵌套查询内外层互相关的查询：指内层查询需要外层查询提供值，而外层查询的条件的需要内层查询的结果。例：例出每个职工经手的具有最高总金额的订购单信息。Select out.职工号，out.供应商号，out.订购单号,out.总金额;from

10、 订购单 out where总金额=；（select max（总金额） from 订购单innerl;where out.职工号=innerl.职工号）（11）使用量词和谓词的查询在嵌套查询中还可以有如下两种形式：1、ANY|SOME（子查询）2、NOT EXISTS（子查询）说明：any、all、some是量词，其中any和some是同义词，比较时只要子查询中有一行能使结果为真，则结果就为真；而all要求子查询中的所有行都使结果为真时，结果才为真。exists或not exists用来检查在子查询中是否有结果返回，即存在元组或不存在元组。例1：检索那些仓库中还没有职工的仓库的信息。Selec

11、t * from 仓库 where not exists;(Select * from 职工 where 仓库号=仓库.仓库号)例2：检索有职工的工资大于且等于“WH1”仓库中任何一名职工的工资的仓库号。Select distinct仓库号 from 职工 where工资=any;(select 工资 from 职工 where 仓库号=“WH1”)例3：检索有职工的工资大于或等于“WH1”仓库中所有职工的工资的仓库号。Select distinct 仓库号 from 职工 where 工资=all;(select 工资 from 职工 where 仓库号=“WH1”)（12）超联接查询超联接

12、：首先保证一个表中满足条件的元组都在结果表中；然后将满足联接条件的元组与另一个表的元组进行联接，不满足联接条件的则将应来自另一表的属置为空值。格式：SELECTFROM Table INNER|KEFT|RIGHT|FULL Join Table ON Join ConditionWHERE说明：INER JOIN普通联接,称内部联接LEFT JOIN 为左联接RIGHT JOIN为右联接FULL JOIN 以称为全联接，即两个表中的条件不管是否满足联接条件将都在目标表或查询结果中出现，不满足联接条件的记录对应部分为NULL.ON Join Condicion联接条件例1：内部联接_只有满足联

13、接条件的记录才出现在查询结果中。SELECT 仓库.仓库号，城市，面积，职工号，工资； From 仓库 inner join 职工on仓库.仓库号=职工.仓库号例2：左联接_即除满足是联接条件的记录出现在查询结果中外，第一个表中不满足联接条件的记录也出现在查询结果中。SELECT 仓库.仓库号，城市，面积，职工号，工资； From 仓库 left join 职工on仓库.仓库号=职工.仓库号例3：右联接_即除满足联接条件的记录出现在查询结果中外，第二个表中不满足联接条件的记录也出现在查询结果中。SELECT 仓库.仓库号，城市，面积，职工号，工资； From 仓库 right join 职工o

14、n仓库.仓库号=职工.仓库号例4：全联接_除满足条件的记录出现，两表中不满足联系条件的记录也出现在查询结果中。SELECT 仓库.仓库号，城市，面积，职工号，工资； From 仓库 full join 职工on仓库.仓库号=职工.仓库号（13）集合的合并运算SQL可以将两个select语句的查询结果通过合并运算union合并成一个查询结果，要求：两查询结果具有相同的字段个数，且对应字段的值要有相同的值域。例1：查询北京和上海的仓库信息 select * from 仓库 where 城市=“北京” union；select * from 仓库 where城市=“上海”（14）Visual Fox

15、Pro 中SQL SELECT的几个特殊选项显示部分结果说明：有时只需显示满足条件的前几个记录，用短语：TOP nExprPERCENT需与ORDRE BY短语同时用nExpr是132767.Percent是0.0199.99显示结果中前百分之几的记录.例1：显示工资最高的三位职工的信息。Select * TOP 3 from 职工 order by 工资 desc例2：显示工资最低的那30%职工的信息select * top 30 percert from 职工 order by 工资将查询结果存放到数组中.用INTO ARRAY ArrayName 短语将查询结果存放到数组中。一般用二维数

16、组，每行一条记录，每列对应查询结果的一列。例：将查询到的职工信息存放在数组tmp中.Select * from 职工 into array tmp结果存放在临时文件中用into curser Cursername将结果存放到临时数据库文件中，临时文件是一个只读的dbf文件，当关闭文件时该文件将自动删除。例：按查询到的职工信息存放在TOP中select * from 职工INTO CURSOR tmp将查询结果存放到永久表中.短语：INTO DBF|TABLE TableName 可将结果存放到永久表中.（dbf文件）select * Top 3 from 职工 into table highs

17、al order by工资desc将查询结果存放到文本文件中.短语: TO FILE filenameADDITIVE例：select * top 3 from 职工 to file time order by 工资 dese将查询结果输出打印机短语：TO PRINT可将结果输出到打印机，若用prompt选项则在开始打印之前会打开打印机设置对话框。 3操作功能（1）插入第一种标准格式： INSERT INTO dbf_name（fnamel,fname2,） VALUES (eExpressionl,eExpression2, )第二种特殊格式 INSERT INTO dbf_name fro

18、m ARRAY ArrayName| FROM MEMVAR说明： 向dbf_name指定的表中插入记录，当插入的不是完整记录时，用 fnamel,fname2, 指定字段；用values短语给出具体的记录值。 From array arrayname说明从指定的数组中插入记录值。 Form MEMVAR 说明根据同名的内存变量插入记录，若不存在同名的变量，则相应的字段为默认值或定值。例： 向职工表中插入元组(“WH4” “E5”,1245) Insent into 职工 Values(“Wh4”, “E5”,1245)注意： 当一个表定义了主索引或候选索引后，由于其相应字段具有关键字特性，不

19、能为空，所以不能用insert或append插入，只能用此SQL语句插入记录。（2）更新格式：UPDATE TableName SET Columm_namel=eExpressionl,Columm_Name2=eExpression2 Where condttion例：给所有学生年龄增加1岁 Update 学生 set 年龄=年龄+1（3）删除格式：DELETE FROM TableName WHERE Condition说明：where指定被删除的记录满足的条件，不指定则删除表中的全部记录。例： 删除供应商号为空值的定单。 Delete from 订购单 where供应商号IS NULL

20、4定义功能（1）表的定义命令格式： CREATE TABLE|DBF TableName|长表名FREE (FieldNamel类型，宽度 NULL|NOT NULL CHECK 规则信息默认值PRIMARY KEY|UNIQUE REFERENCES TableName2TAG tagName1 ,FieldName2 ,FOREIGN KEY eExpression TAAG TagName NODUP 例： 用命令建立学生表和成绩表。 CREATE TABLE 学生（ 学号 C(7) PRIMATY KEY,姓名C(8)年龄 I CKECK between(年龄，17,20) error

21、“年龄出错！” Default 18)CREATE TABLE 成绩（学号 C(7) refe 学生 tag 学号, 课程号 C(6) refe 成绩 tag 课程号,Primary key 学号+课程号 tag 学号课程）（2）表的删除SQL命令：DROP TABLE table_name 功能：从磁盘上删除table_name 对应的dbf文件。（3）表结构的修改命令：ALTER TABLE有三种格式：格式1：ALTER TABLE Tablename|ADD|ALTERcolumnfieldname1Fieldtype(nEileldwidth,nprecision) null|not

22、nullchecklExpression1ERROR cMessageTtext1Default expressionlprimary key |uniquereferences tablename2tag tagname1例： 为学生增加一个总成绩字段 Alter table 学生；Add 总成绩I ckeck between(总成绩，0,100) Error “绩应该在0100间” default 80格式2：ALTER TABLE Tablename |ATTER columnfielname2 null|not null set default expression2set check

23、 lexpression2error cmessageText2DROP Defaultdropcheck功能：定义、修改和删除有效性规则和默认值定义。例：删除总成绩的有效性规则。 Alter table 学生 alter 总成绩 Drop check drop default格式3：Alter table tablename1dropcolumnfieldname3ser check lexpression3reeor cmessage text3drop checkadd primarmy key eExpression3 tag tagname2for(expression4)drop

24、primary ketadd unique expression4TAG tagname3for表达式drop unique tag tagname4add foreign key expression5 tag tagname4for expression6 References tablename2tag tagname5drop foreign key tag tagneme6saverename column fieldname4 to fieldname5功能：可以删除字段、修改字段名、可以定义、修改和删除表一级的有效性规则等。（4）视图的定义定义：是根据表定义或派生出来的虚拟表，可

25、以是本地的、远程的或带参数的。视图可以引用一个或多个表，或引用其他视图。视图是可更新的，它可引用远程表。格式： 视图是根据对表的查询定义的 Create view view_name AS select_statement说明： select_staement 可以是任意的select查询语句，它说明和限定了视图中的数据、视图的字段名将查询中指定的字段名或表中的字段名同名。从单个表派生出的视图例1： create view e_w as; Select 职工号，仓库号 from 职工说明：视图一经定义，就可以和基本表一样进行各种查询，也可以进行一些修改操作。即等效命令：（1） Select *

26、 from e_w（2） Select 职工号，仓库号 from e_w（3） Select 职工号，仓库号 from 职工例2：定义北京仓库的信息视图 Create view v_bj as;Seleet 仓库号，面积 from 仓库 where城市=“北京”从多个表派生出视图例1：列出每个职工今年具有最高金额的定单信息。 Create view v_sample AS;Select out.职工号，out.供应商号，out.订购单号，;out.订购日;期,out总金额；From 订购单 out where 总金额=； （select max(总金额) from 订购单 innerl; where out.职工号=inner1.职工号）此时，可以在视图中查询： Select * from v_sample例2： create view v_emp as; Select 职工号，工资，城市from 职工，仓库； Where 职工.仓库号=仓库.仓库号视图中的虚字段 虚字段：视图中的select子句可以包含算术表达式或函数，由于这些表达式或函数是计算得来的，并不存储表内，故称为虚字段。 例：定义一