第五章sql语言

1、第五章 关系数据库标准语言 SQL一、SQL 概述SQL是结构化查询语言即Structured Query Language1、 特点： 是一种一体化语言，包括了数据定义、数据查询、数据操纵和数据控制等，可以完成数据库活动中的全部工作。 是一种高度非过程化的语言 语言非常简洁 可以直接以命令方式交互使用，也可以嵌入到程序设计语言中以程序方式使用。2、 功能： 数据定义：Create Drop Alter 数据查询：Select 数据操纵：Insert Update Delete 数据控制：Grant Revoke二、查询功能1、简单查询基本格式 Select From where； Group

2、 By Order By Select 数据对象 （目标 即字段名，表达式等） From 数据来源 （表名） Where 查询条件 Group By 分组 Order By 排序例：1、从职工关系中检索所有的工资值Select (distinct) 工资 From 职工 Distinct：去掉查询结果中的重复值。 2、检索仓库关系中的所有元组Select * From 仓库 *：是通配符，表示所有属性，即字段3、检索工资多于1230元的职工号Select 职工号 From 职工 where 工资12304、给出在仓库“WH1”或“WH2”工作，并且工资少于1250元的职工号。Select 职工

3、号 From 职工 where工资1250 and ( 仓库号= “WH1” or 仓库号=”WH2”)或Select 职工号 From 职工；where工资1230) and (职工.仓库号=仓库. 仓库号)如果在from之后有两个关系，那么这两个关系之间肯定有一种关系，职工.仓库号=仓库. 仓库号是联接条件，仓库号是职工表的外部关键字，通过公共字段实现联接练习：1、检索在北京的供应商的名称2、检索仓库表中的城市和面积3、检索出向供应商S3发过订购单的职工的职工号和仓库号3、嵌套查询当检索关系X中的元组时，它的条件依赖于相关的关系Y在的元组属性值，这是使用SQL的嵌套查询功能。例 1、 哪些

4、城市至少有一个仓库的职工工资为1250元？ Select 城市 from 仓库 where 仓库号 in; (select 仓库号 from 职工 where 工资 =1250)2、 查询所有职工的工资都多于1210元的仓库的信息即 没有一个职工的工资少于或等于1210元的仓库的信息 select * from 仓库 where 仓库号 not in; (select 仓库号 from 职工 where 工资1210) select * from 仓库 where 仓库号 not in; (select 仓库号 from 职工 where 工资=1210); and仓库号 in (select

5、 仓库号 from 职工)3、 找出和E4挣同样工资的所有职工 select 职工号 from 职工 where 工资=； （select 工资 from 职工 where 职工号=”E4”） 练习：1、检索出向供应商S3发过订购单的职工的职工号和仓库号2、 检索出和职工E1、E3都有联系的北京的供应商信息4、 几个特殊运算符between and 在之间 like 字符串匹配 通配符“%”表示0个或多个字符 “”表示一个字符 != 不等于例：1、检索出工资在1220元到1240元范围内的职工信息 select * from 职工 where 工资 between 1220 and 1240

6、2、从供应商关系中检索出公司的信息，不要工厂或其他供应商的信息 select * from 供应商 where 供应商名 like “%公司” 3、找出不在北京的全部供应商信息 select * from 供应商 where 地址 !=”北京” 或 select * from 供应商 where not (地址 =”北京”)5、排序 基本格式：order by 字段名 asc desc 多列排序：order by 字段名1，字段名2 例 1、 按职工的工资值升序检索出全部职工信息 select * from 职工 order by 工资2、先按仓库号排序，再按工资排序并输出全部职工信息 sel

7、ect * from 职工 order by 仓库号，工资6、简单的计算查询 用于计算查询的常用函数：count 计数 sum 求和 avg 计算平均值 max 求最大值 min 求最小值例1、 求北京和上海的仓库职工的的总和select sum(工资) from 职工 where 仓库号 in; (select 仓库号 from 仓库 where 城市=”北京” or “城市=上海”)例2、 求所有职工的工资都多于 1210元的仓库的平均面积select AVG(面积) from 仓库 where 仓库号 not in; (select 仓库号from 职工 where 工资=28、利用空值

8、查询如果字段变量尚未取值，把这个信息存入数据库，则这个属性为空值。（NULL） 空值：null 在使用时用 is null ，空值不是一个确定的值因此不能用 = null 例1、找出尚未确定供应商的订购单 sele * from 订购单 where 供应商号 is null 例2、列出已经确定了供应商的订购单信息 sele * from 订购单 where 供应商号 is not null9、别名与自联接查询在联接操作中，经常要使用关系名作前缀，有时这样显得很麻烦，因此，SQL允许在FROM短语中为关系名定义别名格式：如：SELECT 供应商名 FROM 供应商，订购单，职工，仓库；WHERE

9、 地址=“北京”AND 城市=“北京”； AND 供应商.供应商号=订购单.供应商号； AND 订购单.职工号=职工.职工号； AND 职工.仓库号=仓库.仓库号使用别名改成：SELECT 供应商名 FROM 供应商 S，订购单 P，职工 E，仓库 W； WHERE 地址“北京”AND 城市=“北京”； AND S.供应商号=P.供应商号； AND P.职工号=E职工号； AND E.仓库号=W.仓库号 自连接：将同一关系与其自身进行连接在可以进行自连接操作的关系上，存在着一中特殊的递归关系，即关系中的一些，元组根据出自同一值域的两个不同的属性，可以与另外一些元组有一种对应关系，这种关系为一对

10、多的联系。雇员关系：雇员雇员姓名经理E3赵涌E4钱潮E3E6孙洁E3E8李绿E6根据雇员关系列出上一级经理及其所领导的职员清单。SELECT S.雇员姓名，“领导”，E.雇员姓名 FROM 雇员 S，雇员E；WHERE S.雇员号=E.经理S为经理关系，E是雇员关系10、内外层互相关嵌套查询建立订购单表：（职工号，供应商号，订购单号，订购日期，总金额）例：列出每个职工经手的具有最高总金额的订购单信息。 SELECT out.职工号，out.供应商号，out.订购单号，out.订购日期，out.总金额； FROM 订购单 out WHERE 总金额=； （SELECT MAX（总金额）FROM

11、订购单 innerl; WHERE out.职工号=innerl.职工号）外层查询和内层查询使用同一个关系，给他们分别指定别名out和innerl。Out关系中每个元组的职工号值给内层查询使用；内层查询利用这个职工号值，确定该职工经手的具有最高总金额的订购单的总金额；接着外层查询再根据out关系的同一元组的总金额值与该总金额值进行比较，如果相等，则该元组被选择。（本题也可用表别名做）11、使用量词和谓词的查询和子查询有关的运算符：ANY/ALL/SOMENOT EXISTSANY、ALL和SOME是量词，其中ANY和SOME是同义词，在进行比较运算时只要子查询中有一行能使结果为真，则结果就为真

12、；而ALL则要求子查询中的所有行都使结果为真时，结果才为真。 EXISTS是谓词，EXISTS或NOT EXISTS是用来检查在子查询中是否有结果返回，即存在元组或不存在元组。例：1、查询那些仓库中还没有职工的仓库的信息。SELECT * FROM 仓库 WHERE NOT EXISTS； （SELECT * FROM 职工 WHERE 仓库号 = 仓库.仓库号）2、检索那些仓库中至少已经有一个职工的仓库的信息。SELECT * FROM 仓库 WHERE EXISTS； （SELECT * FROM 职工 WHERE 仓库号 = 仓库.仓库号）3、检索职工的工资大于或等于WH1仓库中任何一名

13、职工工资的仓库号SELECT DIDTINCT 仓库号 FROM 职工 WHERE 工资=ANY; (SELECT 工资 FROM 职工 WHERE 仓库号=“WH1”)4、检索有职工的工资大于或等于WH1仓库中所有职工工资的仓库号SELECT DISTINCT 仓库号 FROM 职工 WHERE 工资=ALL (SELECT 工资 FROM 职工 WHERE 仓库号=“WH1”)12、超连接查询在SQL中超连接运算符是：左连接“*=”和右连接“=*”左连接：在结果表中包含第一个表中满组条件的所有记录；如果有在连接条件上匹配的元组，则第二个表返回相应值，否则第二个表返回空值。右连接：在结果表中

14、包含第二个表中满足条件的所有记录；如果有在联接条件上匹配的元组，则第一个表返回相应值，否则第一个表返回空值。格式：SELECTFROM 表 INNER /LEFT/RIGHT/FULL JOIN 表ON 连接条件INNER JOIN ：普通连接，在VFP中称为内部联接。LEFT JOIN ：左连接RIGHT JOIN ：右连接FULL JOIN ：全联接1、 内部联接，即只有满足联接条件的记录才出现在查询结果中SELECT 仓库.仓库号，城市，面积，职工号，工资；FROM 仓库 JOIN 职工；ON 仓库.仓库号=职工.仓库号SELECT 仓库.仓库号，城市，面积，职工号，工资；FROM 仓库

15、 INNER JOIN 职工；ON 仓库.仓库号=职工.仓库号SELECT 仓库.仓库号，城市，面积，职工号，工资；FROM 仓库，职工 WHERE 仓库.仓库号=职工.仓库号2、 左联接，即消除满足联接条件的记录出现在出查询结果中外，第一个表总不满足联接条件的记录也出现在查询结果中。SELECT 仓库.仓库号，城市，面积，职工号，工资；FROM 仓库 LEFT JION 职工；ON 仓库.仓库号=职工.仓库号3、 右连接，除满足联接条件的记录出现在查询结果中外，第二个表中不满足联接条的记录也出现在查询结果中。SELECT 仓库.仓库号，城市，面积，职工号，工资；FROM 仓库 RIGHT J

16、OIN 职工；ON 仓库.仓库号=职工.仓库号4、 全联接，除满足连接条件的记录出现在查询结果中外，两个表中不满足联接条件的记录也出现在查询结果中。SELECT 仓库.仓库号，城市，面积，职工号，工资；FROM 仓库 FULL JOIN 职工；ON 仓库.仓库号=职工.仓库号注：JOIN连接格式在连接多个表时的书写方法要特别注意，在这种格式中JOIN 的顺序和ON的顺序是很重要的，特别要注意JOIN的顺序要和ON的顺序正好相反。13、集合的并运算集合的并运算（UNION），可以将两个SELECT语句的查询结果通过并运算并成一个查询结果。条件：两个查询结果具有相同的字段个数，并且对应字段的值要出

17、自同一个值域。语句的结果是城市为北京和上海的的仓库信息SELECT * FROM 仓库 WHERE 城市=“北京”；UNION；SELECT * FROM 仓库 WHERE 城市=“上海” 14、几个特殊选项1、 显示只显示前几项记录TOP 数字表达式 PERCENT如果不使用PERCENT时，数字表达式的取值是1到32767之间的整数，说明显示前几个记录当使用PERCENT时，数字表达式为0.01到99.99间的实数，说明显示结果中前百分之几的记录。TOP短语要与ORDER BY 使用时才有效。显示工资最高的三位职工的信息SELECT * TOP 职工 ORDER BY 工资 DESC显示工

18、资最低的那30%职工的信息SELECT * TOP 30 PERCENT FROM 职工 ORDER BY 工资2、 将查询结果存放到数组中INTO ARRAY 数组变量名将查询结果的数组做为二维数组来使用，每行一条记录，每列对应于查询结果的一列。将查询结果到的职工信息存放在数组TMP中SELECT * FROM 职工 INTO AYYAY TMP3、 将查询结果存放在临时文件中INTO CURSOR 临时文件名 （当关闭文件时该文件将自动删除）将查询到的职工信息存放在临时DBF文件TMPSELECT * FROM 职工 INTO CURSOR TMP4、 将查询结果存放到永久表中INTO D

19、BF / TABLE 表名将查询结果存放在表HIGHSALSELECT * TOP 3 FROM 职工 INTO TABLE HIGHSAL ORDER BY 工资 DESC5、 将查询结果存放到文本文件中TO FILE 文件名（默认扩展名是TXT） ADDITIVE如果使用ADDITIVE结果将追加在原文件的尾部，否则将覆盖原有文件将查询结果以文本的形式存储在文本文件TMP.TXT中SELECT * TOP 3 FROM 职工 TO FILE TMP ORDER BY 工资 DESC如果TO短语和INTO短语同时使用，则TO 短语将会被忽略。6、 将查询结果直接输出到打印机TO PRINTE

20、R PROMPT三、操作功能1、插入记录基本格式1：insert into 表名字段名1，字段名2 values(记录值1，记录值2)例：往订购单中插入元组 use订购单insert into 订购单 values ( “E7”,”S4”,”OR01”,2001-05-25)若只插入职工号和订购单号两个属性 insert into 订购单(职工号,订购单号) values ( “E7”,”OR01”)基本格式2：insert into 表名 from array 数组名例：use订购单scatter to aacopy structure to 订购单1insert into 订购单1 fro

21、m array aaselect订购单1 或者select 工作区号browseuse delete file 订购单1 基本格式3：insert into 表名 from memvar例：use订购单scatter to memvarcopy structure to 订购单2insert into 订购单2 from memvarselect订购单2 或者select 工作区号browseuse delete file 订购单2 注意：以前的插入命令 insert 或 append 是先插入一条空记录，但当字段设置为关键字时则不允许为空， 所以应该用上述方法插入3、 更新记录基本格式：up

22、date 表名 set 字段名1= 表达式1 ，字段名2= 表达式2 where 条件例1：给WH1 仓库的职工提高10%的工资update 职工 set 工资=工资*1.10 where 仓库号=WH1例2：给所有学生的年龄增加1岁 update 学生 set 年龄=年龄+14、 删除记录基本格式：delete from 表名 where 条件例：要删除仓库关系中仓库号值是WH2的元组 delete from 仓库 where 仓库号=”WH2”注意：SQL的delete 命令同样是逻辑删除记录，如果要物理删除记录需要继续使用pack命令练习：利用第四章建立订货管理数据库和本章给出的记录实例

23、，用SQL语句完成以下更新操作（1）插入一个新的供应商元组（S9，智通公司，沈阳）（2）删除目前没有供应商号的订购单信息（3）删除订购日期在01年6月前的订购单信息（4）北京的所有仓库增加100平方米的面积（5）给低于所有职工平均工资的职工提高5%的工资四、定义功能1、表的定义 即用命令建表 基本格式：create table 表名 free （字段名 字段类型（长度） primary key ,字段名 字段类型（长度） check,字段名 字段类型（长度） default ） 字段类型见书P135-136 free 建立一个自由表primary key 设为主关键字（主索引） check e

24、rror 定义域完整性并说明出错原因 default 设置默认值 foreign key references 设置表之间的联系 null 或 not null 说明字段允许或不允许为空值 unique 建立候选索引例：create database 订货管理create table 仓库表（仓库号 C(5) primary key, 城市 C(10), 面积 I check (面积=0) error “面积应该大于0”） create table 职工（仓库号 C(5),职工号 C(5) primary key,工资 I check (工资=1000 and 工资0 error “总金额应该

25、大于0” 2、将订购单表的订购单号字段的宽度由原来的5改为6 alter table 订购单表 alter 订购单号 C(6) 格式2：用于定义、修改和删除有效性规则和默认值 alter table 表名 alter字段名 set check 表达式error 出错信息 set default 默认值 drop check | default 例：1、修改或定义总金额字段的有效性规则 alter table 订购单 alter 总金额 set check 总金额100 error “总金额应该大于100”2、删除总金额字段的有效规则alter table 订购单 alter 总金额 drop check 格式3：删除字段、修改字段名 alter table 表名 rename column 字段名to 新字段名 drop column 字段名 add 索引类型 索引字段 tag 索引名1、 将订购单表的总金额字段名改为金额alter table 订购单 rename column 总金额 to 金额2、 删除订购单表中的金额字段alter table 订购单 drop column 金额3、 将订购单表的职工号和供应商号定义为候选索引（候选关键字），索引名是emp-supalter table 订购单 订购单 add unique 职工号+供应商号 tag emp-sup