第5章 关系数据库标准查询语言SQL.ppt

1、第四章 关系数据库标准查询语言SQL,5.1 SQL概述 5.2 查询功能 5.3 操作功能 5.4 定义功能,第5章 关系数据库标准查询语言SQL,5.1 SQL概述 SQL语言具有如下主要特点： （1）SQL是一种一体化的语言，它包括了数据定义、数据查询、数据操纵和数据控制等方面的功能，它可以完成数据库活动中的全部工作。 （2）SQL语言是一种高度非过程化的语言，它没有必要一步步地告诉计算机“如何”去做，而只需要描述清楚用户要“做什么”。SQL语言的大多数语句都是独立执行的，与上下文无关。它既不是数据库管理系统，也不是应用软件开发语言，只能用于对数据库中数据的操作。,（3）SQL语言非常简

2、洁。虽然SQL语言功能很强，但它只有为数不多的几条命令。另外SQL的语法也非常简单，它很接近英语自然语言，因此容易学习、掌握。 （4）SQL语言可以直接以命令方式交互使用，也可以嵌入到程序设计语言中以程序方式使用。现在很多数据库应用开发工具都将SQL语言直接融入到自身的语言之中，使用起来更方便，Visual FoxPro就是如此。这些使用方式为用户提供了灵活的选择方式。另外，尽管SQL的使用方式不同，但SQL语言的语法基本是一致的。 SQL语言包括数据定义、数据查询、数据操纵、数据控制4个方面。数据定义包括定义基本表、定义视图、定义索引3部分；数据查询是建立数据库的主要目的，SQL语言提供了S

3、ELECT语句进行查询；数据操纵包括数据检索和更新两部分功能；数据控制是指控制用户对数据的存储权力。,5.2查询功能,Select all/distincttop npercent 列名,列名 From 表名，表名 |inner/left/right/full join 表名 on 连接条件 into array 数组名/cursor 临时表名/dbf 表名/table 表名/to file 文件名additive/to printprompt/to screen Where 条件表达式 group by 列名, 列名having 条件 order by 表达式asc/desc,一、简单查询

4、查询仅涉及一个表，是一种最简单的查询操作 一、选择表中的若干列 二、选择表中的若干元组 三、对查询结果排序 四、使用集函数 五、对查询结果分组,一、选择表中的若干列 1.查询指定列 例:从职工关系中检索出所有的工资值 。,2.查询全部列,Select 工资 from 职工,SELECT * FROM 职工,3. 查询经过计算的值,查询每个订购单的职工号、供应商号和订购至今的天数。,select 职工号,供应商号,date()-订购日期 from 订购单,使用列别名改变查询结果的列标题 select 职工号,供应商号,date()-订购日期 as 订购天数 from 订购单,查询功能,二、选择表

5、中的若干元组 1.消除取值重复的行 例: 查询有职工的仓库号。,SELECT DISTINCT 仓库号 from 职工,2.查询满足条件的元组,WHERE子句常用的查询条件,(1) 比较大小,在WHERE子句的中使用比较运算符 =，=， 逻辑运算符NOT + 比较运算符,Select 职工号 from 职工 where 工资1200,检索出工资多于1200元的职工号,检索哪些仓库有工资多于1210元的职工,Select distinct 仓库号 from 职工 where 工资1210,找出不在北京的全部供应商信息。,Select * from 供应商 where 地址!=”北京”,(2) 确

6、定范围,使用谓词 BETWEEN AND NOT BETWEEN AND ,检索出工资在1220元到1240元范围内的所有职工信息,Select * from 职工 where 工资 between 1220 and 1240,(3) 确定集合,使用谓词 IN , NOT IN ：用逗号分隔的一组取值,查询在北京或上海的仓库的基本信息。,Select * from 仓库 where 城市 in (“北京”,”上海”),(4) 字符串匹配,NOT LIKE ：指定匹配模板 匹配模板：固定字符串或含通配符的字符串,通配符,% (百分号) 代表任意长度（长度可以为0）的字符串 例：a%b表示以a开头

7、，以b结尾的任意长度的字符串。如acb，addgb，ab 等都满足该匹配串 _ (下横线) 代表任意单个字符 例：a_b表示以a开头，以b结尾的长度为3的任意字符串。如acb，afb等都满足该匹配串,从供应商关系中检索出全部公司的信息（不要工厂或其他供应商的信息）,Select * from 供应商 where 供应商名 like “%公司”,(5) 涉及空值的查询,使用谓词 IS NULL 或 IS NOT NULL “IS NULL” 不能用 “= NULL” 代替,找出尚未确定供应商的订购单,Select * from 订购单 where 供应商号 is null,列出已经确定了供应商的

8、订购单的信息,Select * from 订购单 where 供应商号is not null,(6) 多重条件查询,用逻辑运算符AND和 OR来联结多个查询条件 AND的优先级高于OR 可以用括号改变优先级 可用来实现多种其他谓词 NOT IN NOT BETWEEN AND ,Select 职工号 from 职工 where 工资1250 and (仓库号=”wh1” or 仓库号=”wh2”),例：给出在仓库”wh1”或“wh2”工作并且工资少于1250元的职工号。,三、对查询结果排序,使用ORDER BY子句 可以按一个或多个属性列排序 升序：ASC；降序：DESC；缺省值为升序 当排序

9、列含空值时 ASC：排序列为空值的元组最后显示 DESC：排序列为空值的元组最先显示,按职工的工资值升序检索出全部职工信息,Select * from 职工 order by 工资 asc,先按仓库号排序，再按工资排序输出全部职工信息,Select * from 职工 order by 仓库号,工资,四、使用集函数,5类主要集函数 计数 COUNT（DISTINCT|ALL *）计算元组的COUNT（DISTINCT|ALL ）对一列中的值计算个数 计算总和 SUM（DISTINCT|ALL ）某一列值的总和(此列的值必须是数值型) 计算平均值 AVG（DISTINCT|ALL ）求某一列值的

10、平均值(此列的值必须是数值型),求最大值 MAX（DISTINCT|ALL ）求某一列值的最大值 求最小值 MIN（DISTINCT|ALL ）求某一列值的最小值 DISTINCT短语：在计算时要取消指定列中的重复值 ALL短语：不取消重复值 ALL为缺省值,找出供应商所在地的数目。,Select count(distinct 地址) from 供应商,求支付的工资总数.,Select sum(工资) from 职工,求在WH2仓库工作的职工的最高(最低)工资,Select max(工资) from 职工 where 仓库号=”WH2”,五、对查询结果分组,使用GROUP BY子句分组 细化集

11、函数的作用对象 未对查询结果分组，集函数将作用于整个查询结果 对查询结果分组后，集函数将分别作用于每个组,GROUP BY子句的作用对象是查询的中间结果表 分组方法：按指定的一列或多列值分组，值相等的为一组 使用GROUP BY子句后，SELECT子句的列名列表中只能出现分组属性和集函数,使用HAVING短语筛选最终输出结果,只有满足HAVING短语指定条件的组才输出 HAVING短语与WHERE子句的区别：作用对象不同 WHERE子句作用于基表或视图，从中选择满足条件的元组。 HAVING短语作用于组，从中选择满足条件的组。,求每个仓库的职工的平均工资,Select avg(工资) from

12、 职工 group by 仓库号,求至少有两个职工的每个仓库的平均工资,Select avg(工资) from 职工 group by 仓库号 having count(*)=2,同时涉及多个表的查询称为连接查询 用来连接两个表的条件称为连接条件或连接谓词 一般格式： . . 比较运算符：=、=、. BETWEEN . AND .,六、多表查询,1.简单的连接查询,Select 字段 From 表1，表2 Where 连接条件 and 筛选条件 group by 字段having 条件 order by 字段asc/desc ,例：找出工资多于1230元的职工的职工号和他们所在的城市,Sele

13、ct 职工号,城市 from 职工,仓库 where 职工.仓库号=仓库.仓库号 and 工资1230,例：找出工作在面积大于400的仓库的职工号以及这些职工工作的城市。 。,Select 职工号,城市 from 仓库,职工 where 仓库.仓库号=职工.职工号 and 面积400,例：查询签了订单且工资1500元的职工的职工号，姓名，所在的仓库城市和订单日期。,SELECT 职工.职工号，姓名，城市,订购日期 FROM 仓库，职工，订单 WHERE 仓库.仓库号=职工.仓库号 AND职工.职工号=订单.职工号 AND 工资1500,2.别名与自连接查询,定义别名的格式： 表名 别名,例：

14、select 供应商名; From 供应商, 订购单,职工,仓库; Where 地址=“北京” and 城市=“北京” and 供应商.供应商号=订购单.供应商号 and 订购单.职工号=职工.职工号 and 职工.仓库号=仓库.仓库号,例： select 供应商名 From 供应商 S , 订购单 P ,职工 E,仓库 W; Where 地址=“北京” and 城市=“北京” and S.供应商号=P.供应商号 and P.职工号=E.职工号 and E.仓库号=W.仓库号,根据雇员关系列出上一级经理及其所领导的职员清单,Select S.雇员姓名,”领导”,E.雇员姓名 from 雇员 S

15、 ,雇员 E; Where S.雇员号=E.经理,3. 超连接查询（Outer Join）,外连接与普通连接的区别 普通连接操作只输出满足连接条件的元组 外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出,内连接,Select 仓库仓库号,城市,面积,职工号,工资 from 仓库 inner join 职工 on 仓库仓库号职工仓库号,左外连接,Select 仓库仓库号,城市,面积,职工号,工资 from 仓库 left join 职工 on 仓库仓库号职工仓库号,Select 仓库仓库号,城市,面积,职工号,工资 from 仓库 right join 职工 on 仓库仓库号

16、职工仓库号,全连接,Select 仓库仓库号,城市,面积,职工号,工资 from 仓库 full join 职工 on 仓库仓库号职工仓库号,右外连接,七、嵌套查询,1.不相关查询,这类查询所要求的结果出自一个表，但相关的条件却涉及多个表,哪些城市至少有一个仓库的职工的工资为1250元,Select 城市 from 仓库 where 仓库号 in （select 仓库号 from 职工 where 工资=1250）,查询所有职工的工资都多于1210元的仓库的信息.,Select * from 仓库 where 仓库号 not in (select 仓库号 from 职工 where 工资=12

17、10),找出和职工e4挣同样工资的所有职工,Select 职工号 from 职工 where 工资=(select 工资 from 职工 where 职工号=”e4”),求北京和上海的仓库职工的工资总和,Select sum(工资) from 职工 where 仓库号 in(select 仓库号 from 仓库 where 城市=”北京” or 城市=”上海”),求所有职工的工资都多于1210元的仓库的平均面积,Select avg(面积) from 仓库 where 仓库号 not in (select 仓库号 from 职工 where 工资=1210) and 仓库号 in (selec

18、t 仓库号 from 职工),2.内外层互相关嵌套查询,内层查询条件需要外层查询提供值,列出每个职工经手的具有最高总金额的订购单信息。,Select out.职工号,out.供应商号,out.订购单号,out.订购日期,out.总金额 from 订购单 out where 金额=(select max(总金额) from 订购单 inner where out.职工号=inner.职工号),八、使用 量词和谓词的查询,量词有：any,all,some 。 any /all/some (子查询 ) All要求子查询中的所有的行都满足条件结果为真，any同some要求只要有一个满足条件 结果为真。

19、 谓词：exists not exists not exist (子查询) 用来检查子查询中是否有结果返回，即存在元组还是不存在元组。,检索哪些仓库中还没有职工的仓库的信息,Select * from 仓库 where not exists (select * from 职工 where 仓库号=仓库.仓库号),检索那些仓库中至少已经有一个职工的仓库的信息,Select * from 仓库 where exists (select * from 职工 where 仓库号=仓库.仓库号),检索出有职工的工资大于或等于WH1中任何一名职工的工资的仓库号。,Select 仓库号 from 职工 wh

20、ere 工资=any(select 工资 from 职工 where 仓库号=”WH1”),检索有职工的工资大于或等于WH1仓库中所有职工的工资的仓库号,Select 仓库号from 职工where 工资=all(select 工资 from 职工 where 仓库号=”WH1”),九、集合的并运算,Select * from 仓库 where 城市=”北京”; Union; Select * from 仓库 where 城市”上海”,查询北京和上海的仓库的基本信息,十、几个特殊选项,1.显示部分结果 显示工资最高的位职工的信息,Select * top 3 from 职工 order by

21、工资 desc,显示工资最低的那的职工的信息,Select * top 30 percent from 职工 order by 工资 asc,将查询结果送到数组中去,select * from 职工 into array tmp,将查询结果存放在临时文件中,select * from 职工 into cursor tmp,将查询结果送到永久表中,select * from 职工 into table/dbf tmp,将查询结果送到文本文件中,select * from 职工 to file tmp,将查询结果直接输出到打印机上,select * from 职工 to printprompt,S

22、QL向表中插入数据的语句是INSERT语句。 (1) INSERT INTO () VALUES () (2) INSERT INTO from array 数组名/from memvar,5.3操作功能,一、插入,例：往订购单关系中插入元组（”E7”,”S4”,”OR01”,2001/05/25),INSERT INTO 订购单（职工号,供应商号,订单号,订购日期）values(”E7”,”S4”,”OR01”,2001/05/25),例：建立表订购单备份，该表的结构和订购单结构相同。在该表中插入记录（ ”E6”,”S2”,”OR11”,2002/06/25 ），再将订单表中的所有记录插入到

23、该表中。,Use 订购单 Copy struct to 订购单备份 Use Use 订购单备份 Insert into订购单备份 values(”E6”,”S2”,”OR11”,2002/06/25 ) Select * from 订购单 into array a Insert into订购单备份 from array a,二、更新,当需要对基本表中某些元组的某写列值修改时，可以使用更新语句来实现。其语法格式是： UPDATE SET =,= WHERE 该语句的功能是：修改基本表中满足条件表达式的那些元组中的列值，需要修改的列值在SET子句中列出。SET子句中是对符合条件元组中的列值修改，既

24、修改满足条件的行。,例:给WH1仓库的职工提高10%的工资。,Update 职工 set 工资=工资*1.1 where 仓库号=“WH1”,三、删除,SQL删除操作是指从基本表中删除元组，其语法格式是： DELETE FROM WHERE注意: （1）DELETE 语句只能从一个基本表中删除元组,如果想从多个基本表中删除元,必须为每个基本表写一条DELETE语。 （2）如果省略WHERE子句，则基本表中的所有元组被删除，因此使用删除语句要慎重。目前大多数数据库管理系统在删除基本表中的所有元组时提示拥护确认后才执行。,例：删除仓库号为WH2的元组,Delete from 仓库 where 仓库

25、号=“WH2”,5.4定义功能,一、表的定义 Create table 表名 NAME loangtablenamefree (字段名 类型（长度，精度）null/not nullcheck 逻辑表达式error 提示信息default 默认值primary key/uniquereferences 表2 tag 索引名，字段2, ，primary key 表达式 tag 索引名 ，unique 表达式 tage 索引名 ，foreign key 表达式 tag 索引名 references 表名 tag 索引名 ，check 逻辑表达式 error 提示信息),使用命令创建订货管理数据库1，

26、建立仓库1表其中仓库号是主键且面积要大于0，建立职工1表其中职工号为主键、仓库号为外键、工资要在10005000之间，建立供应商1表、供应商号为主键，建立订购单1表、订购单号为主键、职工号和供应商号为外键。,创建订货管理1数据库,Create database 订货管理1,建立仓库1表其中仓库号是主键且面积要大于0，有效性规则为“面积要大于0”,Create table 仓库1(仓库号 c(5) primary,城市 c(10),面积 I check(面积0) error “面积应该大于0”,建立职工1表其中职工号为主键、仓库号为外键、工资要在10005000之间,提示信息为“工资值范围在10

27、00-500”，工资默认值为1200。,Create table 职工1（仓库号 c(5),职工号 c(5) primary key ，工资 I check (工资=100 and 工资=5000) error “工资值范围在1000-500” default 1200,foreign key 仓库号 tag 仓库号 reference 仓库1）,建立供应商1表、供应商号为主键,Create table 供应商1（供应商号 c(5) primary key,供应商名 c(20),地址 c(20)）,建立订购单1表、订购单号为主键、职工号和供应商号为外键。,Create table 订购单1（订

28、购单号 c(5) primary key,职工号 c(5),供应商号 c(5),订购日期 D,foreign key 职工号 tag 职工号 reference 职工1，foreign key 供应商号 tag 供应商号 reference 供应商1）,二、删除表,Drop table 表名,修改字段属性 在Visual FoxPro系统中，创建完成的表一旦不能满足应用系统的需求，可以使用菜单方Modify Structure命令和SQL语句进行字段属性的修改。 用SQL语句修改表结构，无须用Use命令打开表。 格式1：ALTER TABLE ADD/ALTER COLUMN 字段类型(字段宽

29、度，小数位)NULL NOT NULLcheck 表达式 error “出错信息” primary key|uniquereferences 表2 tag 索引名,三、修改表结构,例：为订购单1表增加一个总金额字段（货币类型），总金额要大于0,Alter table 订购单1 add 总金额 Y check 总金额0 error “总金额要大于0”,例：为订购单1表的订购单号字段的宽度由原来的5改为6,Alter table 订购单1 alter 订购单号 c(6),格式2： Alter table 表名 alter column 字段名 null|not null set default 表达式set check 逻辑表达式 error 提示信息drop defaultdrop check,例：修改订购单1表中总金额字段的有效性规则为总金额100,Alter table 订购单1 alter 总金额 set check 总金额100 error “总金额100”,格式3： Alter table 表名 drop column 字段名 set check 表达式 error “信息” drop check add primary key 表达式 tag 索引名 drop primary key add unique 表达