1、第5章 关系数据库标准语言SQL,易学良 13087300765 群号码：8188368,5.1 SQL概述与查询功能,SQL是Structured Query Language(结构化查询语言)的缩写。查询是SQL语言的重要组成部分，但不是全部，SQL还包含数据定义、数据操作和数据控制功能等部分。,(一)SQL概述,SQL的查询命令是SELECT命令，VFP的SQL SELECT命令的其常用格式如下： SELECT ALLDISTINCT别名选项AS显示列名,别名选项AS显示列名 FROM 表名1 别名1,表名2 别名2 WHERE GROUP BY HAVING UNION ALL ORD
2、ER BY ,(二)查询功能,图5-1 本节用到的四个表,基本格式：select from或者select fromwhere,1 简单查询,例5-1 从职工关系中检索仓库WH2的所有工资值。select 工资 from 职工 where 仓库号=“WH2”如果结果中有重复值，并且要去掉的话，可以用disctinct短语：select distinct 工资 from 职工 where 仓库号=“WH2”例5-2 检索仓库关系中的所有元组。select * from 仓库其中“*”是通配符，表示所有属性（字段）。,1 简单查询,联接是关系的基本操作之一，联接查询是一种基于多个关系的查询。例5-
3、3 找出工作在面积大于400的仓库的职工号以及这些 职工工作所在的城市。,2 简单的联接查询,select 职工号,城市 from 仓库,职工;where 面积400 and 职工.仓库号=仓库.仓库号,如果查询所要求的结果出自一个关系，但相关的条件却涉及多个关系，则可以利用嵌套查询来完成。例5-4 哪些城市至少有一个仓库的职工的工资为1250。 select 城市 from 仓库 where 仓库号 in;(select 仓库号 from 职工 where 工资=1250)这里的in相当于集合运算。表否定，即集合的不属于可以使用 not in,3 简单嵌套查询,例5-5 找出和职工E4挣同样
4、工资的所有职工。select 职工号 from 职工 where 工资=;(select 工资 from 职工 where 职工号=“E4”),Between and 和like的用法：例5-6检索出工资在（或不在）1220元到1240元范围内 的职工信息。select * from 职工 where 工资 between 1220 and 1240select * from 职工 where 工资 not between 1220 and 1240例5-7从供应商关系中检索出全部公司的信息（不要工厂 或其他供应商的信息）select * from 供应商 where 供应商名 like “%
5、公司” 其中like是字符串匹配运算符，通配符“%”表示0个或多个字符，通配符“_”（下划线）表示一个字符。,4 几个特殊运算符,例5-8先按仓库号升序排序、再按工资降序排序检索出 全部职工的信息。select * from 职工 order by 仓库号 asc,工资 descOrder by是对最终的查询结果排序，不可以在子查询中使用该短语。,5 排序,用于计算检索的函数有： (1)count:计数；(2)sum：求和；(3)avg:计算平均值； (4)max:求最大值；(5)min:求最小值。 这些函数可以用在select短语中对查询结果进行计算。例5-9 求WH2仓库的职工人数、工资总
6、数、平均工资、最高工资和最低工资。 Select count() as 人数,sum(工资) as 工资合计,; avg(工资) as 平均工资, max(工资) as 最高工资,; min(工资) as 最低工资 from 职工 where 仓库号=“WH2”,6 简单的计算查询,Group by having的用法：例5-10 求每个仓库的平均工资。select 仓库号,avg(工资) from 职工 group by 仓库号例5-11 求至少有两个职工的每个仓库的平均工资。select 仓库号,count(),avg(工资) from 职工 group by;仓库号 having cou
7、nt()=2,7 分组与计算查询,例5-12找出尚未确定供应商的订购单。select * from 订购单 where 供应商号 is null空值不是一个确定的值，不能用“= null”，要用“is null”；不是空值用“is not null”,8 利用空值查询,为了说明联接，我们在职工关系中加入“姓名”和“经理”两个字段：职工(仓库号,职工号,姓名,工资,经理)其中“职工号”和“经理”两个属性出自同一个值域，同一个元组的这两个属性是“上、下级”关系。例5-13根据新的职工关系列出上一级经理及其职员（被其领导）的清单。select s.姓名,“领导”,e.姓名 from 职工 s,职工
8、e ;where s.职工号=e.经理,9 自联接查询,嵌套查询可以是内、外层互相关的查询。这时内层查询的条件需要外层查询提供值，而外层查询的条件需要内层查询的结果。例如在订购单表中加入一个新字段“总金额”，说明完成该订购单所应付出的总金额数，新的订购单表的结构如下：订购单(职工号,供应商号,订购单号,订购日期,总金额)例5-14列出每个职工经手的具有最高总金额的订购单信息。select out.职工号,out.供应商号,out.订购单号,out.订购日期,;out.总金额 from 订购单 out where 总金额=;(select max(总金额) from 订购单 inner1;whe
9、re out.职工号=inner1.职工号),10 内外层互相嵌套查询,嵌套查询中还可以有如下两种形式：any|all|some(子查询) 和 not exists (子查询)Any、all和some是量词； Exists是谓词，exists或not exists是用来检查在子查询中是否有结果返回。,11 使用量词和谓词的嵌套查询,例5-15检索那些仓库中还没有职工的仓库的信息。select * from 仓库 where not exists;(select * from 职工 where 仓库号=仓库.仓库号)例5-16检索那些仓库中至少已经有一个职工的仓库的信息。select * fro
10、m 仓库 where exists;(select *from 职工 where 仓库号=仓库.仓库号),例5-17检索有职工的工资大于或等于WH1仓库中任何一名职工的工资的仓库号。select distinct 仓库号 from 职工 where 工资=any;(select 工资 from 职工 where 仓库号=“WH1”)例5-18检索有职工的工资大于或等于WH1仓库中所有职工的工资的仓库号。select distinct 仓库号 from 职工 where 工资=all;(select 工资 from 职工 where 仓库号=“WH1”),超联接是首先保证一个表中满足条件的元组都
11、在结果表中，然后将满足联接条件的元组与另一个表的元组进行联接，不满足联接条件的则将来自另一表的属性值设为空值。语法格式如下：selectfrom table |left|right|full join tableon join conditionwhere,12 超联接查询,例5-19普通联接，即只有满足联接条件的记录才出现在查询结果中。select 仓库.仓库号,城市,面积,职工号,工资;from 仓库 join 职工 on 仓库.仓库号=职工.仓库号其中from子句部分和下列两中形式等价：from 仓库 inner join 职工 on 仓库.仓库号=职工.仓库号 或from 仓库,职工
12、on where 仓库.仓库号=职工.仓库号,例5-20左联接，即除满足联接条件的记录出现在查询结果中，第一个表中不满足联接条件的记录也出现在查询结果中。select 仓库.仓库号,城市,面积,职工号,工资;from 仓库 left join 职工 on 仓库.仓库号=职工.仓库号例5-21右联接，即除满足联接条件的记录出现在查询结果中，第二个表中不满足联接条件的记录也出现在查询结果中。select 仓库.仓库号,城市,面积,职工号,工资;from 仓库 right join 职工 on 仓库.仓库号=职工.仓库号,例5-22全联接，即除满足联接条件的记录出现在查询结果中，两个表中不满足联接条
13、件的记录也出现在查询结果中。select 仓库.仓库号,城市,面积,职工号,工资;from 仓库 full join 职工 on 仓库.仓库号=职工.仓库号Join联接格式在联接多个表时的书写方法要特别注意： 例如下面是一个基于四个关系的联接查询：select 仓库.仓库号,城市,供应商名,地址;from 供应商 join 订购单 join 职工 join 仓库;on 职工.仓库号=仓库.仓库号;on 订购单.职工号=职工.职工号;on 供应商.供应商号=订购单.供应商号,集合的并(union)运算，可以将两个select语句的查询结果合并成一个查询结果。但必须要求两个查询结果具有相同的字段个
14、数，并且对应字段的值要出自同一个值域。例5-23如下语句的结果是城市为北京和上海的仓库信息。select * from 仓库 where 城市=“北京”;union;select * from 仓库 where 城市=“上海”,13 集合的并运算,只显示前几项记录短语：top nexprpercent （必须与order by连用） 例5-24显示工资最高的三位职工的信息，即按降序排列，只显示前3条记录。select * top 3 from 职工 order by 工资 desc例5-25显示工资最低的那30%职工的信息，即按升序排序、只显示前30%的记录。select * top 30 p
15、ercent from 职工 order by 工资,13 vfp sql select 的几个特殊选项,（2）将查询结果存放到数组中短语：into array arrayname例5-26如下语句将查询到的职工信息存放到数组tmp中。select * from 职工 into array tmp,（3）将查询结果存放在临时文件中短语：into cursor cursorname该短语产生的临时文件是一个只读的DBF文件，当查询结束后该文件是当前文件，可以像一般的表文件一样使用（当然是只读），当关闭文件时该文件自动删除。例5-27如下语句将查询到的职工信息存放到临时DBF文件tmp中。sele
16、ct * from 职工 into cursor tmp,（4）将查询结果存放到永久表中短语：into dbf|table tablename例5-28将查询结果存放到表highsal中可以使用如下语句。select * from 职工 into table highsal,（5）将查询结果存放到文件中短语：to file filenameadditive将查询结果放到文本文件中，默认扩展名为.txt，如有additive，将查询结果追加到文件的尾部。例5-29 如下语句将查询结果以文本的形式存储到文本文件tmp.txt中。select * from 职工 to file tmp,5.2 SQ
17、L操作功能,SQL的操作功能包括数据的插入、更新和删除三个方面的内容。,命令格式：（1）、insert into dbf_name(字段名1,字段名2,)values(表达式1,表达式2,)（2）、 insert into dbf_name from array arrayname|from memvar,1 插入操作,例5-30 以下命令都是往订购单关系中插入一条记录：insert into 订购单 values (“E7”,”S4”,”OR01”,2006-09-25)insert into 订购单(职工号,订购单号) values(“E7”,”OR01”)例5-31假设定义了一数组arr
18、1(5),其中arr1(1)=“E8”,arr1(2)=“S5”, arr1(3)=“OR02”,arr1(4)=2006-03-12,arr1(5)=4000 执行如下命令可以把数组的值插入到表中：insert into 订购单 from array arr1,注意：VFP要求数组中各元素与表中各字段顺序对应，如果数组中元素的数据类型与其对应的字段类型不一致，则新记录对应的字段为空值。如果字段个数大于数组的宽度，则多出的字段为空值。,命令格式：update tablenameset =,=where例5-32给WH1仓库的职工提高10%的工资。update 职工 set 工资=工资*1.10
19、 where 仓库号=“WH1”思考如下命令的功能是什么？update 职工 set 工资=工资*1.10 where 仓库号 in;(select 仓库号 from 仓库 where 城市=“北京”)注意：update一次只能对单一的表中更新记录。,2 更新操作,命令格式：delete from tablenamewhere例5-33删除仓库关系中仓库号为WH2元组。delete from 仓库 where 仓库号=“WH2”注意：在VFP中，SQL delete 同样逻辑删除记录，要物理删除需要继续使用pack命令。,3 删除操作,有关数据定义的SQL命令分为3组，它们是建立(CREATE
20、)数据库对象、修改(ALTER)数据库对象和删除(DROP)数据库对象。每一组命令针对不同的数据库对象(如数据库、查询、视图等)分别有3个命令。,5.3 SQL定义功能,命令格式： CREATE TABLEDBF 表名1 NAME 长表名 FREE(字段名1 类型(宽度,小数位数) NULLNOT NULL CHECK 条件表达式1 ERROR 出错显示信息 功能：建立表可以完成用表设计器完成的所有功能。,1 表的定义,例5-34用SQL CREATE 命令建立仓库表。create table 仓库(仓库号 C(5) primary key,城市 C(10),面积 I check(面积0) e
21、rror “面积应该 大于0！”)例5-35用SQL CREATE 命令建立职工表。create dbf 职工(仓库号 C(5),职工号 C(5) primary key,工资 I check(工资=1000 and 工资=5000)error “工资值的范围在1000-5000!”defaut 1200,foreign key 仓库号 tag 仓库号 references 仓库),例5-36用SQL CREATE 命令建立供应商表。 Create database 供货售货系统create table 供应商(供应商号 C(5) primary key,供应商名 C(20),地址 C(20)
22、例5-37用SQL CREATE 命令建立订购单表。create dbf 订购单(职工号 C(5),供应商号 C(5),订购单号 C(5) primary key,订购日期 D null，工资 I check(工资=1000 and 工资=5000)error “工资值的范围在1000-5000!”default 1200, foreign key 职工号 tag 职工号 references 职工foreign key 供应商号 tag 供应商号 references 供应商),Name:为建立的表指定一个长名。 Free:建立自由表。例如：create table d:学生.dbf fre
23、e(xh c(7),xm c(8) Null或not null：说明字段允许或不允许为空值，默认为null。 Unique：建立侯选索引（不是惟一索引），该字段不允许 出现重复值。注意：（1）、SQL create命令新建的表自动在最小工作区打开，并可以通过别名引用，新表打开方式为独占。（2）、如果建立自由表，很多选项不能使用，如NAME、CHECK、 DEFAULT、FOREIGN KEY、PRIMARY KEY 和REFEFENCES等。,命令格式：drop table table_name,2 表的删除,格式1：ALTER TABLE 表名1 ADDALTER COLUMN 字段名 字段
24、类型(宽度,小数位数)功能：可以添加(ADD)新的字段或修改(ALTER)已有的字段，它的句法基本可以与CREATE TABLE的句法相对应。,3 表结构的修改,例5-38为订购单表增加一个总金额字段（货币类型）。alter table 订购单 add 总金额 Y check 总金额0 error “总 金额应该大于0！”例5-39将订购单表的订购单号字段的宽度改为6(原来为5)。alter table 订购单 alter 订购单号 C(6),格式2：ALTER TABLE 表名 ALTER COLUMN 字段名 NULLNOT NULL SET DEFAULT表达式 SET CHECK 逻辑
25、表达式ERROR 出错显示信息 DROP DEFAULT DROP CHECK 功能：定义、修改和删除有效性规则以及默认值定义。,例5-40修改（定义）订购单表总金额字段的有效性规则。alter table 订购单 alter 总金额 set check 总金额100 error “总金额应该大于100！” 例5-41删除订购单表总金额字段的有效性规则。alter table 订购单 alter 总金额 drop check,格式3： ALTER TABLE 表名 DROPCOLUMN 字段名 SET CHECK逻辑表达式ERROR 出错显示信息 DROP CHECK ADD PRIMARY
26、KEY表达式TAG索引标识FOR逻辑表达式 DROP PRIMARY KEYADD UNIQUE表达式TAG索引标识 FOR逻辑表达式 DROP UNIQUE TAG索引标识 ADD FOREIGN KEY表达式 TAG索引标识FOR逻辑表达式 REFERENCES 表名2TAG 索引标识 DROP FOREIGN KEY TAG 索引标识SAVE RENAME COLUMN TO 功能：删除指定字段(DROP COLUMN)、修改字段名（RENAME COLUMN）、 修改指定表的完整性规则，包括主索引、外关键字、候选索引及表的 合法值限定的添加与删除。,例5-42将订购单表的总金额字段名改
27、为金额。alter table 订购单 rename column 总金额 to 金额 例5-43删除订购单表的金额字段。alter table 订购单 drop column 金额 例5-44将订购单表的职工号和供应商号定义为候选索引，索引名是emp_sup。alter table 订购单 add unique 职工号+供应商号 tag emp_sup例5-45删除订购单表的候选索引emp_supalter table 订购单 drop unique tag emp_sup,（1）创建视图：create view view_name as select_statement(查询语句）（2）删除视图：drop view ,4 视图的定义,
