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

1、第四章 关系数据库标准语言SQL第一节 查询功能一.最短格式:SELECT FROM 注:查询项:要查询的字段,表达式例:查询职工表中的所有信息SELE * FROM 职工注: *代表所有字段例:查询仓库表中仓库号,面积信息SELE 仓库号,面积 FROM 仓库例:查询职工表中不重复的仓库号信息SELE DIST 仓库号 FROM 职工注: DISTINCT代表在查询结果中去掉重复值,并进行升序排序例:查询职工表所有职工的职工号,月工资(工资)和年工资(工资*12)SELE 职工号,工资 AS 月工资,工资*12 AS 年工资 FROM 职工二.条件短语 WHERE 注:条件表达式是能返回一个

2、逻辑值的式子,该短语相当于VFP命令中的FOR 短语,让查询命令仅对符合条件的记录进行操作例:查询职工表中工资大于1210元的职工信息SELE * FROM 职工 WHERE 工资1210例:查询在北京和上海的仓库信息 SELE * FROM 仓库 WHERE 城市 IN(北京,上海)注: IN (表达式列表)当字段的值与表达式列表中的任何一项相等时返回.T.例:查询不在北京和上海的仓库信息 SELE * FROM 仓库 WHERE 城市 NOT IN (北京,上海)注: NOT IN (表达式列表)当字段的值与表达式列表中的任何一项都不相等时返回.T.例:查询职工表中工资在1230与1250

3、之间的职工信息 SELE * FROM 职工 WHERE 工资 BETWEEN 1230 AND 1250注: BETWEEN AND 当字段的值在数据1与数据2之间时返回.T.例:查询订购单表中所有供应商号为NULL值的信息SELE * FROM 订购单 WHERE 供应商号 IS NULL注: IS NULL当字段的值为NULL值时返回.T.例:查询订购单表中所有订购日期不为NULL值的信息 SELE * FROM 订购单 WHERE 订购日期 IS NOT NULL注: IS NOT NULL当字段的值不为NULL值时返回.T.例:查询供应商表中供应商名里包含厂字的信息 SELE * F

4、ROM 供应商 WHERE 供应商名 LIKE %厂%注: 字段名 LIKE 当字符型字段的值符合通配符表达式时返回.T.在SQL中,%代表多个任意字符, _代表单个字符三.排序短语 ORDER BY DESCASC, DESCASC例:按面积降序查询仓库表的所有信息 SELE * FROM 仓库 ORDER BY 面积 DESC例:按工资降序,工资相同按职工号升序查询职工表的所有信息 SELE * FROM 职工 ORDER BY 工资 DESC,职工号例:按仓库号降序查询职工表的所有信息 SELE * FROM 职工 ORDER BY 1 DESC注: ORDER BY 或GROUP BY

5、 短语后跟的是数字而不是字段名则代表按查询结果的第几列进行排序或分组四.计算查询1.SUM 求和 2.AVG 求平均 3.MAX 求最大值4.MIN 求最小值 5.COUNT计数SELE (表达式) FROM 注:根据使用的计算函数,查询计算结果.一般在查询项中只包括计算项,查询结果如果没有分组短语则只有一条记录例:查询职工表中的职工人数 SELE COUNT(*) AS 职工人数 FROM 职工注:COUNT函数是用来统计记录个数的,因此,括号内如果没有DISTINCT选项则无论是使用哪一个字段名都是同样的结果,所以一般使用*例:统计职工表中的仓库个数 SELE COUNT(DIST 仓库号

6、) AS 仓库个数 FROM 职工例:查询职工表中职工的总工资和平均工资以及年总工资和年平均工资 SELE SUM(工资) AS 总工资,AVG(工资) AS 平均工资,SUM(工资*12) AS 年总工资,AVG(工资*12) AS 年平均工资 FROM 职工例:查询职工表中职工的最高工资和最低工资 SELE MAX(工资) AS 最高工资,MIN(工资) AS 最低工资 FROM 职工五.分组计算查询GROUP BY , HAVING 例:查询各仓库职工的总工资和平均工资 SELE 仓库号,SUM(工资) AS 总工资,AVG(工资) AS 平均工资 FROM 职工 GROUP BY 仓库

7、号例:查询订单数不小于2的所有职工的订单数,平均金额信息SELE 职工号,COUNT(*) AS 订单数,AVG(总金额) AS 平均金额 FROM 订购单 GROUP BY 职工号 HAVI COUNT(*)=2六.联接查询SELE FROM , WHERE AND 例:查询在北京工作的职工的职工号,工资及所在仓库的仓库号,面积 SELE 职工号,工资,仓库.仓库号,面积 FROM 仓库,职工 WHERE 仓库.仓库号=职工.仓库号 AND 城市=北京例:查询由在北京工作的职工所开的订购单的订购单号,订购日期,职工号,供应商号,并将结果按订购单号升序排列 SELE 订购单号,订购日期,职工.

8、职工号,供应商号 FROM 仓库,职工,订购单 WHERE 仓库.仓库号=职工.仓库号 AND 职工.职工号=订购单.职工号 AND 城市=北京 ORDER BY 订购单号七.嵌套查询 SELE FROM WHERE IN|=|=|=|NOT IN 注:子查询是另一个查询,它只有一个查询项,它的查询结果是括号外查询的条件的一部分例:查询在北京和上海工作的职工的职工号,工资信息SELE 职工号,工资 FROM 职工,仓库 WHERE 职工.仓库号=仓库.仓库号 AND 城市 IN (北京,上海)*以上方法是用连接完成的* SELE 职工号,工资 FROM 职工 WHERE 仓库号 IN (SEL

9、E 仓库号 FROM 仓库 WHERE 城市 IN (北京,上海)*此类查询(查询项在一个表,查询条件在另一表)的一般格式SELE FROM WHERE IN (SELE FROM WHERE )注:在VFP中,SQL语言的嵌套只能有一层例:查询没有职工工作的仓库信息SELE * FROM 仓库 WHERE 仓库号 NOT IN (SELE 仓库号 FROM 职工)例:查询工资高于所有职工平均工资的职工信息(大于等于)SELE * FROM 职工 WHERE 工资=(SELE AVG(工资) FROM 职工)八.超连接查询SELE FROM INNER|LEFT|RIGHT|FULL JOIN

10、 ON INNER JOIN 等价于 JOIN 为内部连接,与普通连接相同LEFT JOIN 左连接,即除满足条件的记录出现在查询结果中外,第一个表中不满足连接条件的记录也出现在查询中RIGH JOIN 右连接,即除满足条件的记录出现在查询结果中外,第二个表中不满足连接条件的记录也出现在查询中FULL JOIN 完全连接,即两个表中记录不管是否满足条件都在查询结果中出现注:不满足条件的记录对应部分显示为.NULL.例:INSERT INTO 职工 VALUE(WH5,E8,1240)注: 在职工表的尾部加入一条新记录*普通连接SELE * FROM 仓库,职工 WHERE 仓库.仓库号=职工.

11、仓库号*超连接SELE * FROM 仓库 INNER JOIN 职工 ON仓库.仓库号=职工.仓库号将INNER JOIN 改为LEFT JOIN 、RIGHT JOIN、FULL JOIN分别练习。例:查询由工资高于1230元的职工所开的订购单信息SELE 订购单.* FROM 订购单 INNER JOIN 职工 ON 订购单.职工号=职工.职工号 WHERE 工资=1230九.显示部分结果SELE TOP PERCENT FROM ORDER BY 例29:显示面积最大的2个仓库的信息SELE TOP 2 * FROM 仓库 ORDER BY 面积 DESC例30:显示工资最低的50%职

12、工的职工号,工资信息SELE TOP 50 PERCENT 职工号,工资 FROM 职工 ORDER BY 工资十查询去向1.将查询结果保存在数组INTO ARRAY *DISP MEMO LIKE AAA2.将查询结果保存在临时文件(临时表)INTO CURSOR 显示方法:BROWSE3.将查询结果保存在DBF表文件INTO TABLE|DBF 例33:将仓库表复制到CKBAK.DBF中 SELE * FROM 仓库 INTO TABLE CKBAK例34:查询工资最高的三个职工的职工号,仓库号信息,并将结果保存在表文件JG.DBF中SELE TOP 3 职工号,仓库号 FROM 职工 O

13、RDER BY 工资 DESC INTO TABLE JG注:此时JG.DBF自动处于打开状态,且为当前表4.将查询结果保存在文本文件TO FILE ADDITIVE显示方法：MODIFY FILE JG5.将查询结果通过打印机进行输出TO PRINTER PROMPT 第二节 操作功能一.插入INSERT INTO (字段名列表) VALUES(数据列表)例:在仓库表中插入一条记录 WH5 兰州 700 INSERT INTO 仓库 VALUE(WH5,兰州,700)注:当为指定表的所有字段都给数据时,不用指定字段,但数据的类型和个数必须与表结构相符二.更新UPDATE SET = WHER

14、E 例:为所有北京职工工资上调20%UPDA 职工 SET 工资=工资*1.2 WHERE 仓库号 IN (SELE 仓库号 FROM 仓库 WHERE 城市=北京)三.删除DELETE FROM WHERE 注:本命令同样为逻辑删除,如果要物理删除记录需要继续使用PACK命令例:删除面积为750的仓库DELE FROM 仓库 WHERE 面积=750第三节 定义功能一.表的定义CREATE TABLE|DBF FREE (,)【字段描述】 字段名 类型(,) NULL|NOT NULL PRIMARY KEY|UNIQUE CHECK ERROR DEFAULT 【表联系描述】 FOREIG

15、N KEY TAG REFERENCES TAG 例:创建数据库订货管理1,并创建数据库表 仓库1,职工1,订购单1,供应商1以及表与表之间的联系 MODI DATA 订货管理1 CREATE TABLE 仓库1(仓库号 C(5) PRIM KEY,城市 C(10),面积 I CHECK 面积=0 ERROR 面积不能小于0 DEFAULT 400) CREATE TABLE 职工1(仓库号 C(5),职工号 C(5) PRIM KEY,工资 I,FORE KEY 仓库号 TAG 仓库号 REFE 仓库1) CREATE TABLE 供应商1(供应商号 C(5) PRIM KEY,供应商名 C

16、(20),地址 C(5) CREATE TABLE 订购单1(职工号 C(5),供应商号 C(5) NULL,订购单号 C(5) PRIM KEY,订购日期 D NULL,FORE KEY 职工号 TAG 职工号 REFE 职工1,FORE KEY 供应商号 TAG 供应商号 REFE 供应商1)二.表的修改格式1:ALTER TABLE ALTER|ADD COLUMN 功能:修改(ALTER)或添加(ADD)指定的字段*在表创建命令完成的基础上完成如下例题:例:为订购单1表添加一个新字段 总金额 N (7)ALTER TABLE 订购单1 ADD 总金额 N(7)例:将订购单1表中的总金额

17、字段改为整型ALTER TABLE 订购单1 ALTER 总金额 I 格式2:ALTER TABLE ALTER COLUMN NULL NOT NULLSET CHECK ERROR SET DEFAULT DROP CHECKDROP DEFAULT功能:为指定的字段添加有效规则或默认值,也可用来删除有效规则或默认值例:为订购单1表的总金额字段设置有效规则为总金额不能小于0ALTER TABLE 订购单1 ALTER 总金额 SET CHECK 总金额=0 ERROR 总金额不能小于0!例:删除订购单1表总金额字段的有效规则ALTER TABLE 订购单1 ALTER 总金额 DROP C

18、HECK格式3:ALTER TABLE DROP COLUMN DROP PRIMARY KEYDROP UNIQUE TAG ADD PRIMARY KEY TAG ADD UNIQUE TAG RENAME COLUMN TO 功能:删除字段,主索引,候选索引或增加主索引,候选索引或给指定的字段改名例:将订购单1表的总金额字段改名为金额ALTER TABLE 订购单1 RENAME 总金额 TO 金额例:删除订购单1中的金额字段ALTER TABLE 订购单1 DROP 金额例:将订购单1中的订购日期字段创建为候选索引,索引名RQALTER TABLE 订购单1 ADD UNIQUE 订购日期 TAG RQ例:删除订购单1中的候选索引RQALTER TABLE 订购单1 DROP U