创建和设计查询.ppt

《创建和设计查询.ppt》由会员分享，可在线阅读，更多相关《创建和设计查询.ppt（77页珍藏版）》请在三一办公上搜索。

1、第4章 查询与视图操作,“文件”-“新建”（查询）-“向导”例：从“学生管理数据库”的学生表中查询籍贯为河南，入学成绩在590以上学生的信息。,4.1 创建和设计查询,4.1.1利用查询向导设计查询,4.1.2 用查询设计器设计查询,利用查询设计器创建查询操作方法是：“文件”-“新建”（查询）-“新建文件”例：从“学生管理数据库”的“学生”表、“选课”表和“课程”表中查询籍贯为“河南”或“湖北”且是“少数民族”的所有学生的学号、姓名，选修的课程名及相应的成绩。,4.1.2 用查询设计器设计查询,count()、sum()、avg()、min()、max()等函数的使用例：对学生选课表进行统计，

2、统计出各门课程的平均分、最高分和最低分。,4.1.3 查询菜单的使用,“查询”-“查看SQL”命令“查询设计器”工具栏中的“显示SQL窗口”按钮 注意：SQL 语句显示为一个只读窗口。,1.查看SQL语句,2.为查询添加注释,“查询”-“备注”,3.查询去向,“查询”-“查询去向”命令,4.使用图形向导建立一个图表,例：依据“学生管理数据库”的“学生”表的“姓名”及“入学成绩”字段，建立一个图表，查看各个学生的入学成绩。,4.1.4 建立交叉表,使用交叉表向导建立交叉表查询,例：依据“学生成绩表”表中数据，建立一个交叉表，统计每个人各科成绩。,4.2 创建和设计视图,4.2.1 利用向导创建本

3、地视图,例：建立一个本地视图，在“学生管理数据库”中的“学生”表中，筛选女生入学成绩在580分以上记录，要求只显示编号、姓名、性别、籍贯、入学成绩五个字段内容。注意：要建“视图”必须先打开数据库。,例：在“学生管理数据库”中筛选出少数民族，并且是云南或四川学生，要求只显示学号、姓名、民族、课程名称及其成绩等字段内容。,4.2.2 利用视图设计器创建本地视图,4.2.3 创建参数化视图,例：对“学生管理数据库”建立视图，列出任一性别为“女”的学生所选的课程名和成绩。,设置关键字段,钥匙下的是关键字段,4.2.4 使用视图更新数据,设置可更新字段,发送更新,铅笔下的是可更新字段,选中“发送SQL更

4、新”复选框。,例：对“学生管理”数据库中的“学生”表建立一个视图，使其显示所有学生的学号、姓名、性别、籍贯、入学成绩，并将姓名为“赵田田”改为“赵天天”。,4.2.5 视图与查询的区别,功能不同：视图可以更新字段内容并返回源表，而查询文件中的记录数据不能被修改。从属不同：视图不是一个独立的文件而从属于某一个数据库。查询是一个独立的文件，它不从属于某一个数据库。,4.3 关系数据库标准语言SQL*,SQL(Structured Query Language)SQL语言具有如下特点:（1）SQL是一种一体化的语言；（2）SQL是一种高度非过程化的语言；（3）SQL语言非常简洁；（4）SQL语言可以

5、直接以命令方式交互使用，也可以嵌入到程序设计语言中以程序方式使用。,1.SQL的主要特点,4.3.1 SQL概述,4.3.2 查询功能,SQL SELECT 命令的语法格式如下：SELECT 字段列表 FROM 表列表 WHERE GROUP BY.HAVING UNION ORDER BY,说明：select：说明要查询的数据。from：说明要查询的数据来自哪个或哪些表。where：说明查询条件，即选择记录的条件。group by：用于对查询结果进行分组，可以利用它进行分组汇总。having:必须跟在group by之后使用，字用来限定分组必须满足的条件。order by：用来对查询的结果进

6、行排序。,订货管理系统数据库,1.简单查询,简单查询常用格式：SELECT all|distinct.as,.asFROM,WHERE,1.简单查询,【例】检索仓库关系中的所有元组。SELECT*FROM 仓库等价于SELECT 仓库号，城市，面积 FROM 仓库【例】从职工关系中检索所有的工资值，去掉重复值。SELECT 工资 FROM 职工SELECT DISTINCT 工资 FROM 职工,【例】检索工资多于3220元的职工号SELECT 职工号 FROM 职工 WHERE 工资3220【例】检索哪些仓库有工资多于3220元的职工SELECT DISTINCT 仓库号 FROM 职工;W

7、HERE 工资3220,1.简单查询,【例】给出在仓库”CK1”或”CK2”工作,并且工资少于3250元的职工。SELECT 职工号 FROM 职工;WHERE 工资3250 AND;(仓库号=CK1 OR 仓库号=CK2),1.简单查询,2.简单的联接查询,联接查询是一种基于多个关系的查询.【例】找出工资多于3230元的职工号和这些职工所在的城市。SELECT 职工号,城市 FROM 职工,仓库;WHERE(工资3230)AND(职工.仓库号=仓库.仓库号)仓库关系和职工关系之间存在一个一对多的联系。【例】找出工作在面积大于1400的仓库的职工号以及这些职工所在的城市。SELECT 职工号,

8、城市 FROM 职工,仓库;WHERE(面积1400)AND(职工.仓库号=仓库.仓库号),写SQL语句时顺序应该是，并且是二张表之间的关系，在条件子句中应该有体现。,3.嵌套查询,基于多个关系的查询，这类查询所要求的结果出自一个关系，但相关的条件却涉及多个关系。当检索关系X中的元组时,它的条件依赖于相关的关系Y中的元组属性值,这时使用嵌套查询.【例】哪些城市至少有一个仓库的职工工资为3250元 分析:此例要求查询仓库表中的城市信息,而查询条件是职工表中的工资字段值.SELECT 城市 FROM 仓库 WHERE 仓库号 IN;(SELECT 仓库号 FROM 职工 WHERE 工资=3250

9、),写SQL语句时顺序应该是，并且是二张表之间的关系，在条件子句中应该有体现。,【例】查询所有的职工工资都多于3210元的仓库的信息。分析:此例也可描述为”没有一个职工的工资少于或等于3210元的仓库的信息SELECT*FROM 仓库 WHERE 仓库号 NOT IN;(SELECT 仓库号 FROM 职工 WHERE 工资=3210),3.嵌套查询,排除那些还没有职工的仓库,检索要求描述为:查询所有的职工工资都多于3210元的仓库的信息,并且该仓库至少要有一名职工.SELECT*FROM 仓库 WHERE 仓库号 NOT IN;(SELECT 仓库号 FROM 职工 WHERE 工资=321

10、0);AND 仓库号 IN(SELECT 仓库号 FROM 职工),3.嵌套查询,【例】找出和职工ZG4挣同样工资的所有职工。SELECT 职工号 FROM 职工 WHERE 工资=;(SELECT 工资 FROM 职工 WHERE 职工号=ZG4),3.嵌套查询,4.排序查询,使用SQL SELECT 可以将查询结果排序,使用短句ORDER BYORDER BY Order_Item ASC|DESC,Order_Item ASC|DESC,4.排序查询,【例】按职工的工资值升序检索出全部职工信息SELECT*FROM 职工 ORDER BY 工资降序：SELECT*FROM 职工 ORDE

11、R BY 工资 DESC,【例】先按仓库号排序,再按工资排序并输出全部职工信息SELECT*FROM 职工 ORDER BY 仓库号,工资注意：ORDER BY 是对最终的查询结果进行排序，不可以在子查询中使用该短语,4.排序查询,5.简单的计算查询,用于计算检索的函数有:(1)COUNT()-计数(2)SUM()-求和(3)AVG()-计算平均值(4)MAX()-求最大值(5)MIN()-求最小值这些函数可以用在SELECT短语中对查询结果进行计算,【例】找出供应商所在地的数目SELECT COUNT(DISTINCT 地址)FROM 供应商注意:除非对关系中的元组个数进行计数,一般COUN

12、T函数应该使用DISTINCT.例如:SELECT COUNT(*)as 数目 FROM 供应商【例】求支付的工资总数SELECT SUM(工资)as 总和 FROM 职工,若使用SELECT SUM(DISTINCT 工资)FROM 职工,5.简单的计算查询,【例】求北京和上海的仓库职工的工资总和SELECT SUM(工资)FROM 职工 WHERE 仓库号 IN;(SELECT 仓库号 FROM 仓库 WHERE 城市=北京 OR;城市=上海),5.简单的计算查询,【例】求所有职工的工资都多于3210元的仓库的平均面积sele avg(面积)as 平均 from 仓库 where 仓库号

13、not in;(sele 仓库号 from 职工 where 工资=3210)注意:以上结果包含了尚没有职工的CK4仓库,如果要排除没有职工的仓库,以上语句应改为:sele avg(面积)as 平均 from 仓库 where 仓库号 not in;(sele 仓库号 from 职工 where 工资=3210)and;仓库号 in(sele 仓库号 from 职工),5.简单的计算查询,【例】求在CK2仓库工作的职工的最高工资值SELECT MAX(工资)as 最高工资 FROM 职工 WHERE 仓库号=CK2求最低工资值:SELECT MIN(工资)as 最低工资 FROM 职工 WHE

14、RE 仓库号=CK2,5.简单的计算查询,6.分组与计算查询,利用GROUP BY 进行分组计算查询,GROUP BY 短语的格式如下:GROUP BY GroupColumn,GroupColumnHAVING FilterCondition,【例】求每个仓库的 职工的平均工资SELECT 仓库号,AVG(工资)as 平均工资 FROM 职工 GROUP BY 仓库号,【例】求至少有两个职工的每个仓库的平均工资SELECT 仓库号,COUNT(*),AVG(工资)FROM 职工;GROUP BY 仓库号 HAVING COUNT(*)=2注意：HAVING子句总是跟在GROUP BY 子句之

15、后,单独使用。HAVING子句和WHERE子句不矛盾,在查询中是先用WHERE子句限定元组,然后进行分组,最后再用HAVING子句限定分组.,6.分组与计算查询,7.利用空值查询,【例】找出尚未确定供应商的订购单。SELECT*FROM 订购单 WHERE 供应商号 IS NULL注意：查询空值时要使用IS NULL，而=NULL是无效的，因为空值不是一个确定的值，所以不能用“=”这样的运算符进行比较。【例】列出已经确定了供应商的订购单信息。SELECT*FROM 订购单 WHERE 供应商号 IS NOT NULL,8.别名与自然连接查询,在联接操作中,经常需要使用关系名作为前缀,有时这样做

16、显得很麻烦.因此,SQL允许在FROM短语中为关系名定义别名,8.别名与自然连接查询,【例】列出所有职工的订购单信息（包括职工号，订购单号，订购日期，总金额）及工资。sele a.职工号,订购单号,订购日期,总金额,工资 from 职工 a,订购单 b where a.职工号=b.职工号,10.几个特殊的运算符,BETWEENANDLIKE!=NOT【例】检索出工资在3220元到3240元范围内的职工信息。SELECT*FROM 职工 WHERE 工资 BETWEEN 3220 AND 3240等价于：SELECT*FROM 职工 WHERE(工资 3220)AND(工资3240),（1）有特

17、殊运算符的查询,【例】从供应商关系中检索出全部电子仪器厂的信息SELECT*FROM 供应商 WHERE 供应商名;LIKE%电子仪器厂“,（1）有特殊运算符的查询,通配符：%：表示0个或多个字符 _：表示一个字符,【例】找出不在北京的全部供应商信息SELECT*FROM 供应商 WHERE 地址!=北京SELECT*FROM 供应商 WHERE NOT(地址=北京)找出工资不在3220元和3240元之间的全部职工信息SELECT*FROM 职工 WHERE 工资 NOT BETWEEN 3220 AND 3240,（1）有特殊运算符的查询,2.使用量词和谓词的查询,【格式】ANY|ALL|S

18、OME(子查询)NOTEXISTS(子查询)【说明】ANY,ALL,SOME是量词,其中ANY和SOME是同义词,在进行比较运算时,只要子查询中有一行能使结果为真,则结果就为真;而ALL 则要求子查询中的所有行都为真,结果才为真.EXISTS 或 NOT EXISTS是用来检查子查询中是否有结果返回,即存在的元组或不存在元组.,【例】检索那些仓库中还没有职工的仓库的信息SELECT*FROM 仓库 WHERE NOT EXISTS;(SELECT*FROM 职工 WHERE 仓库号=仓库.仓库号)注意:这里的内层查询引用了外层查询的表,只有这样使用谓词EXISTS或NOT EXISTS才有意义

19、.所以这类查询都是内外层互相关嵌套查询.以上查询等价于:SELECT*FROM 仓库 WHERE 仓库号 NOT IN;(SELECT 仓库号 FROM 职工),2.使用量词和谓词的查询,【例】检索那些仓库中至少已经有一个职工的仓库的信息.SELECT*FROM 仓库 WHERE EXISTS;(SELECT*FROM 职工 WHERE 仓库号=仓库.仓库号)以上查询等价于:SELECT*FROM 仓库 WHERE 仓库号 IN;(SELECT 仓库号 FROM 职工),2.使用量词和谓词的查询,【例】检索有职工的工资大于或等于CK1仓库中任何一名职工工资的仓库号这个查询使用ANY或SOME.

20、量词SELECT DISTINCT 仓库号 FROM 职工 WHERE 工资=ANY;(SELECT 工资 FROM 职工 WHERE 仓库号=CK1)以上查询等价于:SELECT DISTINCT 仓库号 FROM 职工 WHERE 工资=;(SELECT MIN(工资)FROM 职工 WHERE 仓库号=CK1),2.使用量词和谓词的查询,【例】检索有职工的工资大于或等于CK1仓库中所有职工工资的仓库号这个查询使用ALL.量词SELECT DISTINCT 仓库号 FROM 职工 WHERE 工资=ALL;(SELECT 工资 FROM 职工 WHERE 仓库号=CK1)以上查询等价于:S

21、ELECT DISTINCT 仓库号 FROM 职工 WHERE 工资=;(SELECT MAX(工资)FROM 职工 WHERE 仓库号=CK1),2.使用量词和谓词的查询,11.超联接查询,超联接查询首先保证一个表中满足条件的元组都在结果表中,然后将满足联接条件的元组与另一个表的元组进行联接,不满足联接条件的则将应来自另一表的属性值置为空值.“*=”左联接,含义是在结果表中包含第一个表中满足条件的所有记录;如果有在联接条件上匹配的元组,则第二个表返回相应值,否则返回空值.“=*”右联接,含义是在结果表中包含第二个表中满足条件的所有记录;如果有在联接条件上匹配的元组,则第一个表返回相应值,否

22、则返回空值.,注 意,Visual Foxpro不支持超联接运算符:”*=”和”=*”,Visual Foxpro使用专门的联接运算语法格式,来支持超联接查询 其语法如下:SELECT FROM Table INNER|LEFT|RIGHT|FULL JOIN Table ON JoinCondition WHERE 其中:INNER JOIN 等价于JOIN,为普通的联接,在VF中称为内部联接.LEFT JOIN称为左联接RIGHT JOIN称为右联接FULL JOIN称为全联接,即两个表中的记录不论是否满足联接条件都将在目标表或查询结果中出现,不满足联接条件的记录对应部分为NULL.ON

23、JoinCondition 指定联接条件,【例】内部联接,即只有满足联接条件的记录才出现在查询结果中.SELECT 仓库.仓库号,城市,面积,职工号,工资;FROM 仓库 JOIN 职工;ON 仓库.仓库号=职工.仓库号如下两种命令格式也是等价的 SELECT 仓库.仓库号,城市,面积,职工号,工资;FROM 仓库 INNER JOIN 职工;ON 仓库.仓库号=职工.仓库号和SELECT 仓库.仓库号,城市,面积,职工号,工资;FROM 仓库,职工 WHERE 仓库.仓库号=职工.仓库号,11.超联接查询,【例】左联接,即满足联接条件的记录出现在查询结果中,第一个表中不满足联接条件的记录也出

24、现在查询结果中SELECT 仓库.仓库号,城市,面积,职工号,工资;FROM 仓库 LEFT JOIN 职工;ON 仓库.仓库号=职工.仓库号,11.超联接查询,【例】右联接,即满足联接条件的记录出现在查询结果中,第二个表中不满足联接条件的记录也出现在查询结果中.SELECT 仓库.仓库号,城市,面积,职工号,工资;FROM 仓库 RIGHT JOIN 职工;ON 仓库.仓库号=职工.仓库号,11.超联接查询,【例】全联接,即满足联接条件的记录出现在查询结果中,二个表中不满足联接条件的记录也出现在查询结果中.SELECT 仓库.仓库号,城市,面积,职工号,工资;FROM 仓库 FULL JOI

25、N 职工;ON 仓库.仓库号=职工.仓库号,11.超联接查询,12.集合的并运算-UNION,【例】查询北京和上海的仓库信息。SELECT*FROM 仓库 WHERE 城市=北京;UNION;SELECT*FROM 仓库 WHERE 城市=上海,子句格式：UNION ALLSELECT命令合并的规则：（1）不能合并子查询的结果；（2）两个select命令必须列数相同，列的数据类型相同；（3）仅最后一个select命令中可以用order by子句，且排序选项必须用数字说明。,13.查询结果的其他显示方式,（1）显示部分结果 在应用中有时只显示前几项记录使用TOP nExpr PERCENT。TO

26、P 要与ORDER BY同时使用才有效。【例】显示工资最高的前3位职工的信息SELECT*TOP 3 FROM 职工 ORDER BY 工资 DESC【例】显示工资最低的30%职工的信息SELECT*TOP 30 PERCENT FROM 职工 ORDER BY 工资,（2）将查询结果放在数组中使用INTO ARRAY ArrayName 将查询结果放在数组中.【例】将查询到的职工信息存放在数组 SZ中。SELECT*FROM 职工 INTO ARRAY SZ,13.查询结果的其他显示方式,（3）将查询结果存放在临时文件中使用INTO CURSOR CursorName将查询结果放在临时数据库

27、表文件中.该表为只读dbf文件，当关闭文件时该文件将自动删除。【例】将查询到的职工信息存放在临时表文件 ZGB中。SELECT*FROM 职工 INTO CURSOR ZGB,13.查询结果的其他显示方式,（4）将查询结果存放在永久表中使用INTO DBF|TABLE TableName将查询结果放在永久表中。【例】将例4.43查询的结果存放在表文件 ZGB1.dbf中。SELECT*TOP 3 FROM 职工 INTO TABLE ZGB1 ORDER BY 工资DESC,13.查询结果的其他显示方式,（5）将查询结果存放在文本文件中使用TO FILE FileNameADDITIVE将查询

28、结果放在文本文件中.ADDITIVE选项使结果追加到原文件的尾部，否则将覆盖原有文件。【例】将上例查询的结果以文本的形式存放在文本文件 WBWJ.txt中。SELECT*TOP 3 FROM 职工 TO FILE WBWJ;ORDER BY 工资DESC如果TO 短语和INTO短语同时使用，则TO短语将会被忽略。,13.查询结果的其他显示方式,（6）将查询结果直接输出到打印机使用TO PRINTER PROMPT 将查询结果直接输出到打印机PROMPT打开打印机设置对话框,13.查询结果的其他显示方式,1.数据插入功能,格式1：INSERT INTO 表名(字段名1，字段名2,)VALUES(

29、表达式1,表达式2,)功能：在指定的表尾添加一条新记录，其值为VALUES后面表达式的值。格式2：INSERT INTO 表名 FROM ARRAY 数组名 FROM MEMVAR功能：在指定的表尾添加一条新记录，其值来自于数组或对应的同名内存变量。,4.3.3 操作功能,【例】往订购单关系中插入元组：（”A7”,”S4”,”OR01”,2004/05/25）INSERT INTO 订购单 VALUES（”A7”,”S4”,”OR01”,2004-05-25,0）假如供应商未确定,只能先插入.INSERT INTO 订购单(职工号,订购单号)VALUES（”A7”,”OR01”）这时,另外两个

30、属性值为空.,2.数据更新功能,SQL的数据更新命令如下:UPDATE TableNameSET Column_Name1=eExpresssion1,Column_Name2=eExpresssion2 WHERE Condition【例】给SH1仓库的职工提高10%的工资UPDATE 职工 SET 工资=工资*1.10 WHERE 仓库号=”SH1”,3.数据删除功能,命令格式:DELETE FROM TableName WHERE Condition【例】删除仓库关系中仓库号值是SH2的元组。DELETE FROM 仓库 WHERE仓库号=”SH2”注意:此删除同样是逻辑删除记录。,现有

31、三个自由表：学生信息表：，由学号(Sno)、姓名(Sname)、性别(Ssex)、年龄(Sage)、所在院系(Sdept)五个属性组成，其中为Sno主码。课程信息表：，由课程号(Cno)、课程名(Cname)、选修课号(Cpno)、学分(Ccredit)四个属性组成，其中Cno为主码。学生选课表：，由学号(Sno)、课程号(Cno)、成绩(Grade)三个属性组成，主码为(Sno,Cno)。请按下面要求写出SQL代码:1.查询其他系中比生物系某一学生年龄小的学生的姓名和年龄select Sname,Sage from mySTudent where Sage 生物系,2.查询与“肖艳芬”在同一

32、个系学习的学生。select Sno,Sname,Sdept from mySTudent;where Sdept in(select Sdept from;mySTudent where Sname=肖艳芬)或select S1.Sno,S1.Sname,S1.Sdept from;mySTudent S1,myStudent S2;where S1.Sdept=S2.Sdept and S2.Sname=肖艳芬3.查询所有选过课程的每个学生及其选修课程的全部信息。select myStudent.*,mySC.*from mySTudent,mySCwhere myStudent.Sno=

33、mySC.Sno,4.查询选修课程“2102”且成绩在80分以上的所有学生。select myStudent.Sno,Sname from mySTudent,mySC;where myStudent.Sno=mySC.Sno;and mySC.Cno=2102 and mySC.Grade805.查询所有选修课程“3501”的学生学号和姓名。select Sno,Sname from myStudent;where exists(select*from mySC;where Sno=myStudent.Sno and Cno=3501),6.将学生表中的所在系为“生物系”的记录的所在系改为“生物科学系”。update mystudent set sdept=生物科学系 where sdept=生物系,通过本章的学习，读者应掌握以下内容：查询的建立与修改。查询的操作。视图的建立与修改创建参数化视图。了解远程视图的基本概念和操作。掌握SQL语句的查询功能。掌握SQL的数据定义功能和数据操纵功能理解视图的定义以及数据控制的功能。,小 结,Thank You!,