access 2007-查询[内部培训资料]整理笔记.ppt

1、分组加总小计,1、查询各部门的平均薪资。,2、查询各部门男员工与女员工的平均薪资。,*3、查询各部门的总人数。,4、查询各个年龄层的人数。,5、查询各个年龄层的男员工和女员工的人数。,6、查询各个年龄层的平均薪资。,分组条件小计,1、查询哪些部门的平均薪资1800。,2、查询哪些部门中男员工或女员工的平均薪资1800。,3、查询同名同姓的员工人数。 !*!,4、查询目前薪资在1500-2000，2000-2500的男女员工所在部门的人数。,名列前茅与垫底查询,1、查询目前薪资最高的10名员工的信息。（姓名,性别,所在部门,目前薪资）,3、查询目前薪资最高的前10%的员工信息。（ .）,2、查询

2、目前薪资最低的10名员工的信息。 （）,5、查询年龄最大的3名员工信息。 （姓名，性别，年龄，目前薪资,所在部门）,4、查询目前平均薪资最低的3个部门。（ 所在部门，平均薪资）,6、查询平均薪资最高的前3个年龄层与该年龄层的人数。（年龄层，平均薪资， 所在部门）,关系式查询,1、查询每一位客户每一笔订单的产品金额。（客户编号，公司名称，订单号码，订单日期，产品名称，单价，数量，小计）,2、查询每一位客户每一笔订单订总购金额。 （）,3、查询每一位客户的订单总金额。（ .）,4、查询每一位客户每一年之各月的订货总额。（）,5、查询出销售地区的业绩。,6、查询（条件）发票报表的数据来源。用C C

3、u r函数计算小计价格。,7、查询在2005年9月份采购之大连市客户的基本数据、订单数据及订货明细。,8、查询在所有公司在2006年下的订单，以及负责处理此位客户之订单的员工基本数据。,9、查询出每一位业务人员的销售总金额。,10、查询出在2004年4月销售总金额最高的前三位业务人员。 nodo,11、由“产品数据”与“订货明细”表来查询出销售总金额最高的前10项产品。no,12、查询出每一位客户的采购次数与订货总金额。,13、查询出在06年9月下订单之客户的采购次数与订货总金额,根据订单明细作一个订单小计,数据表联接类型,1、INNER JOIN,3、RIGHT JOIN,2、LEFT JO

4、IN,只包含两个表中联接字段相等的行.,包括第二个表中的所有记录和第一个表中联接字段相等垢那些记录.,包括第一个表中的所有记录和第二个表中联接字段相等的那些记录.,同上,查询属性,1、如何使用”输出所有字段”属性。 eg60,3、如何使用“唯一值”属性。 查询公司中有哪些部门.,2、如何使用“上限值”属性。,4、如何使用“唯一记录”属性。,自订子数据表,1、查询客户表的数据，并通过子数据表查看订货文件的数据。,2、查询客户表的数据，并通过子数据表查看客户每年每月的订货小计。,数据表视图中 插入-子数据表 格式-子数据表,全部展开,全部折叠,删除,什么是子查询(Subquery),“子查询”（S

5、ubQuery）就是内含于某一SELECT 、INSERT、UPDATE命令中的SELECT查询。,SELECT 、INSERT、UPDATE或DELETE命令中允许是一个运算式的地方皆可以内含于查询。,子查询甚至可以再内含于另外一个子查询中。,在WHERE后的表达式中出现另一个查询，这另一个查询称为子查询，子查询的结果一般表“IN”要查询值的集合,子查询是可以嵌套的。,当您无法直接取得查询的条件值时，而且此一条件值可以通过查询来求得或计算出来的进时候，就非常适合使用子查询。,利用子查询的查询结果作为WHERE引数中之个别数据记录的过滤条件或是HAVING引数中之分组数据的过滤条件。,何时使用

6、应该使用子查询,简单查询语法格式： SELECT ALL*/选择列表 FROM 基表名 WHERE 条件表达式 GROUP BY 列名1HAVING 条件表达式 ORDER BY 列名2ASC/DESC DISTINCT：检索去掉重复组的所有元组，缺省值为ALL。,子查询使用注意事项,子查询务必内含在一对小括弧中。,使用=、=、等运算符与子查询进行比较，则子查询务必返回单一值。,EXISTS(存在于子查询)、IN(包含于子查询)、ALL(子查询之全额检验)与ANY(子查询之部分检验)等运算符。,如何使用”存在于子查询”,WHERE NOT EXISTS ( Subquery ) 只要子查询不是

7、空集合-亦即至少存在一笔数据记录于子查询之查询结果中,过滤条件式将返回 TRUE。 子查询之SELECT 命令语句的字段串列中通常使用万用字符星号*(但并非一定只能使用星号)。 109,如何使用”包含于子查询”,WHERE expression NOT IN (Subquery) 使用“包含于子查询”之检验时，子查询必须只返回单一字段。110,111,112 子查询之SELECT 命令语句的字段串列中通常使用万用字符星号*(但并非一定只能使用星号)。 109,如何使用”子查询之全额检验”,WHERE expression comparison_operator ALL(Subquery) co

8、mparison_operator 指的就是,=,=.等比较运算符。 113-115,如何使用”子查询之部分检验”,WHERE expression comparison_operator ANY|SOME(Subquery),comparison_operator 指的就是,=,=.等比较运算符。 117,子查询操作,1、查询出每一笔订单上的最低单价与最高单价。,2、查询出每一笔订单上的最高单价并列出是哪些产品。,最高单价: (SELECT MAX(订货明细.单价) FROM 订货明细 WHERE 订货主文件.订单号码 = 订货明细.订单号码),(SELECT MAX(订单明细.单价) FR

9、OM 订单明细 WHERE 订货主文件.订单号码=订单明细.订单号码),3、查询出目前薪资等于公司中最低薪资的姓名。,4、查询出有哪些员工的薪资比公司的平均薪资还高出10%。,5、查询出人数比业务部少的各部门的平均薪资。,In (SELECT Max(cur_salary) FROM 个人信息表 group by department),(SELECT Avg(cur_salary)*1.1 FROM 个人信息表),(SELECT count(*) from 个人信息表 where department =业务部 ),6、查询出薪资高于“财务部”部门任何一位员工之薪资。,7、查询出薪资低于“财

10、务部”部门任何一位员工之薪资。（全额查询）,8、查询出薪资低于“财务部”部门任何一位员工之薪资。（）,9、列出员工的姓名与薪资,条件是薪资至少必须大于“财务部”此一部门 其中一位员工的薪资。,All (SELECT cur_salary FROM 个人信息表 WHERE (department=财务部),在SQL视图里写： SELECT 个人信息表.name, 个人信息表.birthday, 个人信息表.cur_salary, 个人信息表.department FROM 个人信息表 WHERE (个人信息表.cur_salary) (SELECT MIN(cur_salary) FROM 个人

11、信息表 WHERE (department=财务部);,Some (SELECT cur_salary FROM 个人信息表 WHERE (department=财务部),联集查询,1、什么是联集查询？,使用UNION运算符将两个或两个以上之选择查询的查询结果合并起来。要建立联集查询，必须自行在查询设计视图的SQL视图中撰写SELECT命令语句。, |() UNION ALL |() UNION ALL n ,联集查询,2、使用UNION运算符，必须注意下列规则：,要被合并查询结果的每一条SELECT命令语句必须输出相同数目的字段。 一条SELECT命令语句所产生之查询结果中的每一个字段必须与

12、另一条SELECT命令语句中相对应的字段拥有能彼此相容的数据类型。 只有最后一条SELECT命令语句可以拥有一个ORDER BY参数，将会影响合并后之查询结果的排列顺序。 各个查询皆可以使用GROUP BY与HAVING参数，不过它们并不会影响合并后之查询结果。,1、查询各部门中薪资最高的前二名员工信息。,SELECT * from q财务部 UNION ALL SELECT * FROM q采购部 UNION ALL SELECT * FROM q管理部 UNION ALL SELECT * FROM q技术部 UNION ALL SELECT * FROM q生产部 UNION ALL S

13、ELECT * FROM q资讯部 ORDER BY 目前薪资 DESC;,动作查询,查询可以分为“选择查询（Selection query）”与“动作查询（Aaction query）”两大类。,所谓“选择查询”，仅仅是从一或多个数据表中提取出数据记录，然后加汇总，统计，分析与运算，它并不会对数据记录造成任何改动。,所谓“动作查询”，表示该查询专门是用来追加、修改与删除数据记录，甚至能够产生新的数据表。分为： “删除查询”、“更新查询”、“追加查询”、“生成数据表” 四种类型。,动作查询,1、删除查询,由于数据记录一被删除将无法复原，为了避免不小心删除数据记录而懊悔不已，建议先预览哪些数据将

14、会被所定义的删除查询删除，工具栏中“数据表视图”, 删除“生产部”的员工数据。, 删除目前工资在1000-1600的员工数据。, 删除不姓“林”的员工，出生月份是10，11，或12月，年龄大于 40岁。,2、更新查询,利用更新查询能够更新单一或多个关系数据表中符合特定条件的一或多笔数据记录，而达到所谓“批次更新”效果。, 将雇用日期加长90天。, 年龄大于等于30，或目前薪资在1000-1700，将其工资提高20%。,UPDATE 个人信息表2 SET 雇有日期 = 雇有日期-90;,SQL视图： UPDATE 个人信息表2 SET 个人信息表2.目前薪资 = 目前薪资*1.2 WHERE (

15、个人信息表2.目前薪资) Between 1000 And 1700) AND (DateDiff(yyyy,出生日期,Date()=30);,3、追加查询,将本月出生的员工记录加到本月寿星表中。,利用追加查询，能够把查询结果直接储存至一个既存的数据表中，很轻易将一个或一个以上之数据表中的数据记录经过或不经过汇总、分析、统计与运算后再存放至某一个数据表中。,将个人信息表中各部门最高薪资和姓名，加至“查询库.mdb”数据库中。,INSERT INTO 本月寿星 ( 姓名, 出生日期 ) SELECT 个人信息表2.姓名, 个人信息表2.出生日期 FROM 个人信息表2 WHERE (Month(

16、出生日期)=Month(Date();,INSERT INTO 部门平均薪资 ( 员工姓名, 平均薪资, 所在部门 ) IN E:zxh计算机编程Access数据库查询库.mdb SELECT 个人信息表2.姓名, Avg(个人信息表2.目前薪资) AS 目前薪资之平均值, 个人信息表2.部门 FROM 个人信息表2 GROUP BY 个人信息表2.姓名, 个人信息表2.部门;,4、生成数据表查询,根据客户，订货主文件、订货明细三个表，在另一个查询库生成一个数据表（订货小计）。,生成数据表的最大特色就是自动建立一个数据表，然后将自单一或多个关系数据表的查询结果储存至此一新建立的数据表中。 同追

17、加数据表的最大区别：生成数据表查询是将查询结果储存至一个新建立的数据表，而追加查询则是将查询结果存入一个既存之数据表的尾端。,在同一库中建立一个各年龄层、各薪资级距的人数。,SELECT 客户.客户编号, 客户.公司名称, 订货主文件.订单日期, 订单明细.产品名称, CCur(订单明细.单价*数量*(1-折扣) AS 订货小计 INTO 订货小计 IN E:zxh计算机编程Access数据库查询库.mdb FROM (订单明细 INNER JOIN 订货主文件 ON 订单明细.订单号码 = 订货主文件.订单号码) INNER JOIN 客户 ON 订货主文件.客户编号 = 客户.客户编号;,

18、年龄层: Partition(DateDiff(yyyy,出生日期,Date(),20,119,10),薪资级距: Partition(目前薪资,500,5500,500),Partition(DateDiff(yyyy,出生日期,Date(),20,119,10),SQL语句： SELECT Partition(DateDiff(yyyy,出生日期,Date(),20,119,10) AS 年龄层, Partition(目前薪资,500,5500,500) AS 薪资级距, Count(个人信息表2.身份证号) AS 人数 INTO 各年龄层的各薪资级距人数 FROM 个人信息表2 GROU

19、P BY Partition(DateDiff(yyyy,出生日期,Date(),20,119,10), Partition(目前薪资,500,5500,500), Partition(DateDiff(yyyy,出生日期,Date(),20,119,10);,SQL 创建新表,CREATE TABLE 表名（字段1 类型1（长度），字段2 类型2（长度）,插 入,语法格式： INSERT INTO 表名 （列名1,列名) VALUES（常量1，常量）,删 除,语句格式： DELETE FROM 表名WHERE 条件表达式 如果没有WHERE 子句则删除所有元组，删除后成为空表。也可以用子查询

20、。,更 新,UPDATE 表名 SET 列名1=表达式1 ，列名2=表达式2WHERE 条件表达式,参数查询,我们所建立的查询经常都会包含特定的条件，以便能查询出切实符合所需的数据记录，并非一下提取出所有的数据记录。只要善用参数查询，一切便能迎刃而解，最大特色就是条件不因定，在查询被实际运行时由用户输入条件值。,方法： 在查询设计视图中“条件”栏中输入 “提示文字”。,1、由用户输入所要查询的员工姓名。,2、由用户输入所要查询的员工的姓氏. ( &和+作用相同),3、由用户输入所要查询指定的城市。,4、由用户输入所要查询的员工的年龄。,5、由用户输入所要查询特定部门与特定年龄层的员工。,6、由

21、用户输入所要查询薪资介于某两个薪资之间，且任职于特定 三个部门的员工。,创建窗体,窗体是一种比较灵活的查看和输入数据的方法，能够在看到所有字段的同时一次查看一条或多条记录。利用自身带有的控件，可以用不同方法去查看数据。,创建窗体,标签控件中显示字面文本,数据被键入到文本框中,可拥有多个单选钮、复选框或切换按钮,切换按钮,选项按钮,复选框,组合框,列表框,用鼠标按下执行某个动作,图像，显示一个位图图像,非绑定对象框：容纳不与表字段链接的OLE对象或嵌入式图像。,绑定对象框：容纳与表字段链接的OLE对象或嵌入式图像,分页符,选项卡控件：可在文件来形式的界面中显示多页,子窗体子报表,直线，矩形,在窗

22、体中创建公式： 1、创建计算表达式 = + , - , * , / 2、创建计算汇总表达式 =Sum( ) 3、创建筛选的计算汇总 IIF( 不再销售=true, ”是” , ”否” ) -产品资料窗体,1、 使用窗体向导创建子窗体 创建窗体和选择“窗体向导” 为主窗体选择字段 选择用于子窗体的表或查询 为子窗体选择字段 选择窗体数据布局 选择子窗体布局：分两种（带有子窗体的窗体，链接窗体） 选择窗体样式 选择窗体标题,创建子窗体,2、利用拖动的方法创建子窗体,1、利用Frame 框架中含有option选项，选择一项命令后执行某项操作。将框架名称改成宏中的名称 ，宏中的条件中部门=1，对应某个

23、查询。Option 中的选项值改为1，2对应宏中的条件。 2、在属性中：获得焦点。,1、导入外部数据包括: Microsoft Access(其他没打开的窗体、表等数据库对象)。 FoxPro(所有使用ODBC驱动程序的版本)。 d BASE . Microsoft Excel(所有版本)。 HTML文档。 带分隔符的文本文件（字段被分隔符分隔）。 固定宽度文本文件（每个字段规定固定长度）。 SQL数据库。 XML 文档。,2、导出外部数据包括: Microsoft Access(其他没打开的窗体、表等数据库对象)。 FoxPro(所有使用ODBC驱动程序的版本)。 d BASE . Micr

24、osoft Excel(所有版本)。 HTML文档。 带分隔符的文本文件（字段被分隔符分隔）。 固定宽度文本文件（每个字段规定固定长度）。,创建报表,报表用于提供自定义的数据视图。数据可被分组和按任何次序排序，然后以分组次序显示。可以创建把数值相加、计算平均值或其他统计信息的汇总，并且可以用图形方式显示数据。,1、创建多级分组总计报表（根据月份）,2、创建多级分组总计报表（根据公司名称）,宏,1、宏的概念 宏的主要目的是用来运行一连串的一般处理操作，因此宏主要是由一个或多个操作所构成。通过宏可以打开窗体并运行某些记录筛选的操作。 在宏中不包含条件表达式，如果设置了条件表达式，就代表宏要依照某些

25、特定的状况来运行不同的区块命令，所以命令的流程是会有变化的。,宏的操作参数所对应的功能,右键:事件生成器,事件生成器,打印的宏同上,只不过将视图变为打印,选择数据表的宏,VBA编程,1、什么是VBA？ 虽然宏很好用，但它运行的速度比较慢，也不能直接运行很多WINDOWS的程序。尤其是不能自定义一些函数，这样当我们要对某些数据进行一些特殊的分析时，它就无能为力了。 由于宏具有这些局限性，所以在给数据库设计一些特殊的功能时，需要用到“模块”对象来实现，而这些“模块”都是由一种叫做“VBA”的语言来实现的。使用它编写程序，然后将这些程序编译成拥有特定功能的“模块”，以便在Access2000中调用。

26、 VB，就是微软公司推出的可视化BASIC语言，用它来编程非常简单。因为它简单，而且功能强大，所以微软公司将它的一部分代码结合到OFFICE中，形成我们今天所说的VBA。它的很多语法继承了“VB”，所以我们可以像编写VB语言那样来编写VBA程序，以实现某个功能。当这段程序编译通过以后，将这段程序保存在Access中的一个模块里，并通过类似在窗体中激发宏的操作那样来启动这个“模块”，从而实现相应的功能。 “模块”和“宏”的使用是差不多的。其实Access中的“宏”也可以存成“模块”，这样运行起来的速度还会更快呢。“宏”的每个基本操作在“VBA”中都有相应的等效语句，使用这些语句就可以实现所有单独

27、“宏”命令，所以“VBA”的功能是非常强大的。如果你要用Access来完成一个负责的桌面数据库系统，你就应该掌握“VBA”，它可以帮你实现很多功能。但如果你只是偶尔使用一下Access或者只是用Access来做一些简单的工作，你只要简单了解一下它就可以了。,1、子过程：不返回值的程序代码。因为不返回值，所以它不能用于表达式或将其调用赋给一个变量。子过程通常被报表或窗体里的事件调用作为一段单独的程序运行。 Sub cmdExit_Click() DoCmd.close End Sub 2、函数：返回一个值，可以将函数用于表达式或将其赋给某个变量，可以被其他函数和子过程调用。也可以给函数传递参数。

28、 Function nSquareFeet(dbHeight As Double, dblWidth As Double) As Double nSquareFeet = dblHeight * dblWidth End Function 3、事件程序：如同子程序，用来响应用户的事件 4、属性程序：用来设置新的属性。,Access2003 中程序分4种:,Access VBA 的基本类型,常量 自定义常量必须通过Const表达式来声明，也可设置为Private或Public ,默认常量是Public。 语法： Public/Private Const 常量名称AS 数据类型 Const Def

29、aultWeight =35 Const DefaultName=“Smith” Const SPI=3.1416 Global Const SPI=3.1416,变量 变量的主要功能是用来暂时存放数据，例如酒店中的房间可能住过不同的客户。声明变量时可通过Dim,Private,Public,Static等表达式来声明，而Dim ,Private功能是相同的。 语法： Dim 变量 As 数据类型 Dim A As Integer Dim B As Double,变量声明,方法：工具-选项-编辑器选项卡 将“要求变量声明”的复选框击活。 会自动加上：Option Compare Datebas

30、e Option Explicit,VBA的流程命令,1、If Then Else命令 If 条件表达式 Then 表达式区块1 Else 表达式区块2 End If,可以省略,2、For Next 循环控制 For 计数变量=起始值勤 To 终值（Step 步值） 表达式区块 Next 计数器变量,3、Select Case End Select Select Case VarName 可以是字符串或者数据变量 Case Expression1 Case Expression2 To Expression3 Case Is RelationalExpression Case Else End

31、 Select case表达式可以采用下列四种格式之一： 单一数值或一行并列的数值，用来与VarName的值相比较。 由关键字To分隔开的两个数值或表达式之间的范围。 关键字Is后面接关系运算符，如,=,，后面再接变量或精确值 关键字Case Else 后的表达式，是在以前的Case条件没有一个满足时执行的。,4、Do Loop 循环 Do D o While 条件表达式 表达式区块 表达式区块 Loop While 条件表达式 Loop 至少执行一次,5、函数,Ctrl + G 视图立即窗口,1、事件 是指发生在对象上的事情。如，用脚踢球，就是发生在对象球上的一件事情。某些事情只能发生在某些

32、对象上，而不能发生在其他一些对象上。 2、事件过程 是指对象对发生在其上的某一事件的反应。不同的对象对同一事件的反应可能是不同的，这是因为不同对象的事件过程是不同的。,Print date_w2e(2004-12-31) 二四年十二月三十一日 Function date_w2e(ByVal myDate As Date) As String Dim eDate As String Dim intY, intM, intD As Integer Dim intD2 As Integer Dim I As Integer intY = Year(myDate) intM = Month(myDat

33、e) intD = Day(myDate) For I = 1 To Len(intY) eDate = eDate & number_w2e(Mid(intY, I, 1) Next eDate = eDate & 年 eDate = eDate & number_w2e(intM) eDate = eDate & 月 Select Case intD Case 1 To 9, 10, 20, 30 eDate = eDate & number_w2e(intD) Case 11 To 19 eDate = eDate & number_w2e(10) & number_w2e(Mid(in

34、tD, 2, 1) Case 21 To 29 eDate = eDate & number_w2e(20) & number_w2e(Mid(intD, 2, 1) Case 31 eDate = eDate & number_w2e(30) & number_w2e(1) End Select eDate = eDate & 日 date_w2e = eDate End Function Function number_w2e(ByVal I As Integer) As String Select Case I Case 0 number_w2e = Case 1 number_w2e

35、= 一 Case 2 number_w2e = 二 Case 3 number_w2e = 三 Case 4 number_w2e = 四 Case 5 number_w2e = 五 Case 6 number_w2e = 六 Case 7 number_w2e = 七 Case 8 number_w2e = 八 Case 9 number_w2e = 九 Case 10 number_w2e = 十 Case 11 number_w2e = 十一 Case 12 number_w2e = 十二 Case 20 number_w2e = 二十 Case 30 number_w2e = 三十 E

36、nd Select End Function,中文格式日期,控件事件过程,1、进入一个控件从其他控件获得焦点前 2、退出 在控件失去焦点前 3、获得焦点非激活或激活控件获得焦点时 4、失去焦点 控件失去焦点时 5、单击单击鼠标时 6、双击双击鼠标时 7、鼠标按下在控件上按下鼠标时 8、鼠标移动鼠标指针在控件上移动时 9、鼠标释放释放按下的鼠标键时 10、键按下某控件具有焦点，键被按下或SendKeys宏被使用时 11、键释放当按下的键被释放或在宏SendKeys 被使用之后 12、键击某控件具有焦点，键按下又释放，并且SendKeys宏被使用时,用事件过程打开窗体,打开主窗体中的”每月产品价格”按钮 利用事件生成器: Private Sub