Access数据库及其应用参考模板

本课程主要讲述：数据库基本理论 Access 数据库操作 SQL 数据库查询语句 * VBA语言及模块设计第一章 数据库基础知识讲述内容：1、数据管理发展史2、数据库基本概念 * 3、数据库四级结构模型4、概念模型：E-R模型 *5、逻辑模型：关系模式 *6、关系运算 *7、数据规范化：1NF,2NF,3NF *一、数据管理发展史1、人工管理阶段计算机发展初期，主要用于科学计算，数据不独立，完全依赖于程序，不能共享。2、文件管理阶段 数据以文件形式管理，数据无结构化，共享性差。1 / 473、数据库管理阶段 数据结构化（数据库形式），出现多种数据库管理系统，共享性好。二、数据库基本概念1、数据库（DB）：计算机处理，结构化，数据集合。2、数据库管理系统（DBMS）：数据库管理系统软件，提供数据库操作平台。对数据库可以建立，添加记录，修改记录，删除、查询、排序、连接等。常见系统VFP、ACCESS、SQL SERVER等。3、数据库应用系统（DBAS）：利用数据库开发管理应用软件。4、数据库系统（DBS）：和数据库有关的所有系统总和，包括计算中硬件系统、软件系统、数据库、管理者、操作者、用户。5、信息三个世界划分： 客观世界 信息世 计算机世界 研究实体对象，特性 结构化，形成不同的 计算机存储结构 实体之间联系。 数据模型，关系型、 面向计算机 E-R模型 网状、层次型。面向对象 （概念模型） （逻辑模型） （物理模型）常用概念：实体、实体集、属性、标识符-数据库、表、字段、记录、关键字。6. 联系在现实世界中，事物内部以及事物之间是有联系的，这些联系同样也要抽象和反映到信息世界中来，两个实体型之间的联系有如下三种类型：（1）一对一联系（1:1）实体集A中的一个实体至多与实体集B中的一个实体相对应，反之亦然，则称实体集A与实体集B为一对一的联系。记作1:1。v如：班级与班长，观众与座位，病人与床位。（2）一对多联系（1:n）实体集A中的一个实体与实体集B中的多个实体相对应，反之，实体集B中的一个实体至多与实体集A中的一个实体相对应。记作1:n。v如：班级与学生、公司与职员、省与市。（3）多对多（m:n）实体集A中的一个实体与实体集B中的多个实体相对应，反之，实体集B中的一个实体与实体集A中的多个实体相对应。记作（m:n）。v如：教师与学生,学生与课程，工厂与产品。实际上，一对一联系是一对多联系的特例，而一对多联系又是多对多联系的特例。可以用图形来表示两个实体型之间的这三类联系，如图书 1.7所示。A B A B A B (a) (b) (c)不同实体集实体之间的联系三、数据库模型及DBMS功能1、现实世界数据-抽象-计算机管理数据概念模型：用户需求观点，ER图逻辑模型：开发者角度，计算机实现观点，逻辑结构（层次、网状、关系）外部模型：用户使用观点，根据需求形成模型。内部模型：计算机物理结构2、数据库管理系统主要功能。（P14 解释）3、关系模型中常用术语： 关系模型，元组，属性，域，候选键，主键，外键，完整性规则，参照完整性。四、概念模型：E-R模型“实体-联系方法”的简称E-R模型。E-R图基本成分包含实体型、属性和联系。 实体型：用矩形框表示，框内标注实体名称。属性：用椭圆形框表示，框内标注属性名称。联系：指实体之间的联系，有一对一（1：1），一对多（1：n）或多对多（m ：n）三种联系类型。 (a)两个实体之间的联系学生选修成绩课程系主任领导系学生属于系11n1系主任n(c)实体集内部的联系m职工领导1n供应商供应数量零件项目mnn(b)多个实体之间的联系例如：学生，宿舍，班级，教室，班主任，档案材料 课程名学生学号姓名性别年龄系别课程课程号学分n选修成绩m这样，得到学籍管理局部应用的分E-R图 五、逻辑模型：关系模式1、关系模式表示：关系名称（属性1，属性2，。） 下划线代表主键如：学生（学号，姓名，性别，政治面貌）2、E-R模型转化关系模式E-R模型中，1:1联系可以转换为一个独立的关系模式，也可以与任意一端对应的关系模式合并。如班级-管理教师联系为1:1，我们可以将其转换为一个独立的关系模式：班级（班级号，学生人数） 教师（职工号，姓名，性别，职称，是否优秀班主任） 管理（职工号，班级号）或 管理（职工号，班级号） 管理”联系也可以与班级或教师关系模式合并。班级（班级号，学生人数，职工号） 教师（职工号，姓名，性别，职称，是否优秀班主任）或班级（班级号，学生人数） 教师（职工号，姓名，性别，职称，班级号，是否为优秀班主任）E-R模型中，1:n联系可以转换为一个独立的关系模式，也可以与n端对应的关系模式合并。例如：“学生-组成-班级”的联系为1:n联系。 学生（学号，姓名，出生日期）班级（班级号，学生人数，班主任） 组成（学号，班级号）另一种方法是将其学生关系模式(n端实体)合并，学生关系模式为： 学生（学号，姓名，出生日期，班级号）班级（班级号，学生人数，班主任） E-R模型中，联系是一个m:n联系，将联系独立出来。如“学生选课课程”关系 学生（学号，姓名，性别，年龄，所在系） 课程（课程号，课程名，学分） 选课（学号，课程号，成绩）如：P22-25旅客-乘机座位关系 1：1旅客-航班关系 1：n顾客-每种商品关系 m：n教师（教师号，姓名）学生（学号，姓名，性别）课程（课程号，课程名，学时，教师号）选课表（学号，课程号，成绩）六、关系运算一、关系运算 并、交、笛卡尔积；投影、选择、连接（并），-（差），（交），X（笛卡尔积），(选择)，（投影），（连接）；（大于） （大于等于） ，=，）和逻辑运算符（ ）连接起来的逻辑表达式，结果为逻辑值“真”或“假”。R 是从关系R中选取使逻辑表达式为真的元组，进行行运算。类似For 例：在S关系中，查询计算机系的全体学生，形成新的关系。DEPT=计算机(S) 或 5=计算机(S)例：在T关系中，查询工资高于1000元的男教师。形成新的关系。(工资1000) (性别=男) (T) 例：在S关系中，查询姓张和姓王的工程师。形成新的关系。（(姓名=张)V（姓名=王） (性别=男) (S) 6. 投影 投影运算也是单目运算，关系R上的投影是从R中选择出若干属性列，组成新的关系，即对关系在垂直方向进行的运算，从左到右按照指定的若干属性及顺序取出相应列，删去重复元组。记作：A(R)=tA|tR 其中A为R中的属性列，为投影运算符。 从其定义可看出，投影运算是按列进行的运算。类似Fields T 关系教师号姓 名性别工资系别课程号1001李力男1000计算机C11002王平女600工商C21003王红女700土木C31004刘伟男900计算机C2例 查询教师的姓名、教师号及其工资。姓名,教师号，工资(T)或 2,1,4(T)（结果为教师号，姓名，工资三个属性的关系）姓 名教师号工资李力10011000王平1002600王红1003700刘伟1004900*投影运算可以改变关系的属性次序例 查询教师关系中有哪些系。系别(T)系别计算机工商土木计算机投影后取消了某些属性列后，可能出现重复行，应该取消这些完全相同的行。所以投影之后，不但减少了属性，元组也可能减少，新关系与原关系不相容。 例 查询讲授C5课程的教师号。教师号,姓名(课程号=C2(T)教师号姓 名1002王平1004刘伟例：检索王老师所授课程的课程号、课程名（T关系）课程号,课程名(姓名=王(T)检索年龄大于20岁的男同学的学号和姓名（S关系）学号,姓名(年龄23性别=男(S)T关系 S关系学号姓 名课程号课程号课程名教师1001李力C1C1法律张老师1002王平C2C2网络王老师1003王红C3C3电子李老师1004刘伟C2C4计算机赵老师检索学号为1003学生所选的课程名与任课老师。课程名，教师（学号=1003 T.课程号=S.课程号（T X S）检索李力同学不选的课程号。课程号（T）-课程号（姓名=李力T.课程号=S.课程号（T X S） 例 R ，S关系 求：RUS，R-S，RnS , RXS ，3,2(S)A B C A B C B5(R) 3 6 7 3 4 5 2 5 7 7 2 3 7 2 3 4 4 3RUS - R-S - RnS - A B C A B C A B C - - - 3 6 7 3 6 7 7 2 3 2 5 7 2 5 7 7 2 3 4 4 3 4 4 3 3 4 5RXS - R.A R.B R.C S.A S.B S.C - 3 6 7 3 4 5 3 6 7 7 2 3 2 5 7 3 4 5 2 5 7 7 2 3 7 2 3 3 4 5 7 2 3 7 2 3 4 4 3 3 4 5 4 4 3 3 4 53,2(S) - B5(R) - C B A B C - - 5 4 7 2 3 3 2 4 4 37. 连接（Join）连接运算是二目运算，是从两个关系的笛卡尔积中选取满足连接条件的元组，组成新的关系。 为“=”时，称为等值连接； RS=xy(RS)在连接运算中，一种最常用的连接是自然连接(R*S)。所谓自然连接就是在等值连接的情况下，当连接属性X与Y具有相同属性组时，把在连接结果中重复的属性列去掉。即如果R与S具有相同的属性组Y，则自然连接可记作：R、S关系。例：R S 学号姓名性别学号平均分总分1001A男1001601201002B男1002701401003C女1003801601004D女1004901801005E女R*S学号姓名性别平均分总分1001A男601201002B男701401003C女801601004D女90180例：设关系R： S：A B B C a b b c c b e a d e b d求：R S , A=C(RS), R*SBSS+RDSS 关系 RD关系S# SN SA SS SD 94001 王言 18 男 机械系 94002 刘里 19 男 机械系 94003 张兰 18 女 材料系 例1、假设某商业集团数据库中有一关系模式R如下：R (商店编号，商品编号，数量，部门编号，负责人)如果规定：(1) 每个商店的每种商品只在一个部门销售； (2) 每个商店的每个部门只有一个负责人； (3) 每个商店的每种商品只有一个库存数量。试回答下列问题：（此题15分） (1) 根据上述规定，写出关系模式R的基本函数依赖； (2) 找出关系模式R的候选码； (3) 试问关系模式R最高已经达到第几范式？为什么？ (4) 如果R不属于3NF，请将R分解成3NF模式集。(1) 有三个函数依赖：(商店编号，商品编号) 部门编号 (商店编号，部门编号) 负责人 (商店编号，商品编号) 数量 (2) R的候选码是 (商店编号，商品编号) (3) 因为R中存在着非主属性“负责人”对候选码 (商店编号、商品编号)的传递函数依赖，所以R属于2NF，R不属于3NF。 (4) 将R分解成：R1 (商店编号，商品编号，数量，部门编号) R2 (商店编号，部门编号，负责人)例2、 设有商店和顾客两个实体：“商店”有属性商店号、商店名、商店地址、电话，“顾客”有属性编号、姓名、顾客地址、年龄、性别。假设一个商店有多个顾客购物，一个顾客可以到多个商店购物，顾客每次去商店购物有一个消费金额和日期。而且规定每个顾客在每个商店里每天最多消费一次。（1）试画出E-R图，并注明属性和联系类型。 （2）将其转换成关系模型，注明关键字，写出函数依赖关系。商店（商店号，商店名，商店地址，电话） 商店号商店名，地址，电话顾客（编号，姓名，顾客地址，年龄、性别） 编号姓名，顾客地址，年龄、性别消费（编号，商品号，金额，日期）编号，商品号金额，日期第二章 ACCESS及数据库操作一、ACCESS启动，界面.二、数据库（。mdb）七种不同对象。 多表，窗体，查询，报表，宏，模块，数据库访问页除外。 组是数据库对象快捷方式.三、数据库建立，保存，打开，更名，删除，复制，剪切，粘贴。四、数据库安全性（独占方式） 数据库密码, 用户组与权限 , 加密解密第三章 表及其操作一、表结构设计三种方法（设计器，向导，输入数据） 两种视图（设计视图，数据视图）。 字段设置 主键设置（自动编号ID，单字段，多字段）与撤销 索引（逻辑排序）：单字段，多字段 表设计视图常规，查阅选项。二、表记录基本操作：添加，删除，修改，修饰，查找替换，筛选（窗体，内容，排除，高级），排序，数据导出。三、多表关系：一对一，一对多。打开数据库，利用工具栏中工具 一对一：左表（第一表），右表（第二表）参照照完整性，更新级联，删除级联。一对多：主(父)表，从(子)表。参照照完整性，更新级联，删除级联。第四章 数据表查询一、查询：查询是数据库的一个对象，是表的一种视图（结果可以看成虚表）。查询时可以对表的数据进行计算，更新，删除，生成新表等操作。查询可以作为其他对象数据源。二、查询类型：选择查询：根据条件查询，可以分组，统计，计数，求平均等其他计算。参数查询：交互式，用户输入参数查询。交叉表查询：对数值型字段，分析计算处理。操作查询：对记录操作（删除，更新，追加，生成表）查询。SQL查询：允许利用数据库通用操作语言SQL查询语句操作。三、查询三种视图：设计视图，数据表视图，SQL视图。四、条件表达式用法：比较： = = = betweenand in()逻辑：not and or 通配符：like * ? ! 字符串，数值，日期表示，比较五、SQL语句。SQL语言提供了SELECT语句进行数据表的查询，该语句具有灵活的使用方式和丰富的功能。其一般格式为： SELECT ， FROM ， WHERE GROUP BY HAVING ORDER BY ASC|DESC； 一、简单查询： 1查询指定列 SELECT SNO,SN,AGE FROM S 查询全体学生的学号、姓名和年龄。2查询全部列 SELECT * FROM S * 代表所有列名3查询经过计算的值 目标列表达式可以是算术表达式，还可以是字符串常量、函数等。SELECT 姓名+姓名,工资*0.5，FROM S 运算符含义=, , =, =, != 比较大小多重条件AND,ORBETWEEN AND确定范围IN确定集合LIKE字符匹配二、条件查询 WHERE 1比较大小查询 2、多重条件查询3、确定范围 SELECT 姓名,性别 WHERE 学号 BETWEEN “1001”AND “1008” FROM STUDENT SELECT * WHERE 平均分 BETWEEN 60 AND 100 FROM STUDENT SELECT * WHERE平均分 NOT BETWEEN 60 AND 100 FROM STUDENT4、确定集合 SELECT 姓名 WHERE 学号 IN(“1001”,“1008”) FROM STUDENTSELECT * WHERE 数学IN( 80,100) FROM STUDENTSELECT * WHERE 数学NOT IN( 80,100) FROM STUDENT5、字符匹配 SELECT 姓名,性别 WHERE 学号 LIKE “张%” FROM STUDENTSELECT 姓名,性别 WHERE 学号 LIKE “%力” FROM STUDENTSELECT * WHERE 书名 LIKE “%网络%” FROM BOOK6、空值的查询 SELECT 姓名 WHERE 高数 IS NULL FROM STUDENT三、使用库函数、统计汇总查询。 AVG(),SUM(),MAX(),MIN(),COUNT()select sum(高数) as x1,avg(高数) as x2 from student select max(平均分) as x1 from student where 学号=“1”四、分组查询1、GROUP BY子句将查询结果：按属性列或属性列组合进行分组，每组具有相同的属性值。分组计算过程SELECT COUNT(*) AS x1 FROM student GROUP BY 性别SELECT max(高数) AS x1 FROM student GROUP BY 性别2、Group by 子句 having 条件，指分组操作后的条件选择。 查询选修两门以上课程学生的学号和选课门数 Select 学号,count(*) from 成绩表 group by 学号 having count(*)=2 在工资表中，按职称分组，查询每类职称工资总和超过20000元的职称和工资总和。 Select 职称,sum(基本工资) form 工资表 group by 职称having sum(基本工资)200003、条件顺序： Where 条件-group by 分组-having 条件 如查询：计算机系每班英语平均分在90分以上的班级及英语平均分。 Select 班级，avg(英语) from 成绩表 where 系别=“计算机” group by 班级 having avg(英语)90 如：查询班级人数大于40人的班级及人数 查询每班英语平均分不及格的班级及班级平均分五、一、 查询排序用ORDER BY子句可以指定排序方式，DESC为降序，ASC为升序，缺省时为升序。SELECT 姓名, 平均分 FROM student ORDER BY 平均分DESCSELECT 姓名, 高数 AS x1 FROM student WHERE 高数=60ORDER BY 高数 DESC六、二、 多表查询：数据表连接及连接查询连接字段: 数据表之间的联系是通过表的字段值连接查询: 同时涉及两个以上的表查询。表的连接方法：表之间满足一定的条件的行进行连接，FROM子句中指明进行连接的表名WHERE子句指明连接的列名及其连接条件。例 查询刘伟老师所讲授的课程。T TC编号姓名编号课程T1刘伟T1C1T2AT2C1T3CT1C3T4DT2C2T5ET3C3SELECT 姓名,课程 FROM T, TC WHERE T.编号 = TC.编号 说明: T.TNO = TC.TNO 为连接条件，TNO为连接字段。如: 查询刘伟老师教授的课程 (思考).例 查询所有选课学生的学号、姓名、选课名称及成绩。S SC CSNOTNSNOCNOSCORECNOCN1001A1001C160C1计算机1002B1002C170C2法律1003C1001C350C3数学1002C2801003C390SELECT S.SNO,SN,CN,SCOREFROM S,C,SCWHERE S.SNO=SC.SNO AND SC.CNO=C.CNO如：查询学号为1001学生的姓名、选课名称及成绩。（思考） 1.查询设备名称,价格,部门名称2.查询设备名称,部门名称,增值七、子查询：从一个查询结果中再进行查询如：从学生表中查询平均分最高的学生的姓名和性别。Select 姓名，性别 from 学生表 where平均分 in (select max(平均分) form 学生表)八、联合查询成绩表1.dbf 成绩表2.dbf 学号姓名性别平均分学号姓名性别平均成绩1001AA男602001CC男501002BB女702002DD女80Select 学号，姓名，性别，平均分 as 录取分 from 成绩表1Union Select 学号，姓名，性别，平均成绩 as 录取分 from 成绩表2九、* 内部联接、左联接、右联接、完全联接学生表 成绩表学号姓名性别学号英语数学平均分1001AA男10016080701002BB女10027090801003CC男20014060502002507060内部联接：联接字段等值连接Select 姓名，性别，平均分 from 学生表 inner join 成绩表 On 学生表.学号=成绩表.学号左联接：除联接字段等值连接外，左表（第一表）不满足连接条件的记录出现在查询结果中。Select 姓名，性别，平均分 from 学生表 left join 成绩表 On 学生表.学号=成绩表.学号右联接: 除联接字段等值连接外，右表（第二表）不满足连接条件的记录出现在查询结果中。Select 姓名，性别，平均分 from 学生表 right join 成绩表 On 学生表.学号=成绩表.学号完全联接: 除联接字段等值连接外，左、右两表不满足连接条件的记录都出现在查询结果中。Select 姓名，性别，平均分 from 学生表 full join 成绩表 On 学生表.学号=成绩表.学号第五章 窗体主要内容：1、窗体操作三种视图2、创建窗体方式3、窗体控件使用、编辑4、窗体数据处理：删除、添加、修改、查找、筛选、排序、计算。一、窗体视图：设计视图，窗体视图，数据表视图。二、创建窗体方式： 设计视图 向导：表窗体，图表窗体，透视图窗体 自动创建窗体三、设计视图创建窗体。 1、设计视图窗口五个组成： 窗体页眉：窗口标题页面页眉：打印窗体说明主体：显示表记录。页面页脚：打印窗体中每页页码，日期窗体页脚：页码，日期，统计计算结果 2、窗体控件使用：标签、文本框、选项组、切换按钮、选项按钮、复选框、组合框、列表框、命令按钮、选项卡、图像。 3、控件：绑定型（表字段）、未绑定型（静态显示）计算型：如：文本框中 =字段表达式四、窗体数据处理：删除、添加、修改、查找、筛选、排序、计算。第六章 报表、访问页、宏一、报表1、报表组成：报表页眉、报表页脚、页面页眉、页面页脚、主体 组页眉、组页脚。2、报表视图：设计视图、打印视图、版面视图3、创建报表方式： 设计视图、向导、自动创建、图表向导、标签向导。4、设计视图报表及控件应用。5、报表中计算：总计、平均、百分比等。二、数据访问页1、数据库一个对象，以单独文件存储数据库之外。文件格式HTML，扩展名 .htm。 操作数据方式和窗体相似，窗体操作本地数据库数据； 页可以访问网络服务器上的数据。2、页有两种视图：设计视图、页面视图。3、创建页方式：向导，设计视图（超级链接、文字滚动）。三、宏1、宏是一个或多个操作的集合。宏中每个操作完成一个特定功能。2、创建宏、运行宏。3、常用宏操作： Opentable Openform Openquery Openreport Opendataccesspage Close Maximize Minimize RunSQL Quit4、条件宏引用： （1）。在窗体中，设计一个文本框，一个按钮。 （2）。选中按钮对象（快捷菜单）-事件生成器-宏生成器-建立宏名-选工具栏条件宏 ，输入：forms！窗体名！文本框名=“123456”，选择操作msgbox，在消息中输入显示信息。forms！窗体名！文本框名“123456”，选择操作msgbox，在消息中输入显示信息。 （3）运行窗体5、宏组中引用宏名：宏组名.宏名(1).设计宏1：openform 打开窗体1 宏2：openform 打开窗体2 宏3：openquery 打开查询1 宏4：openreport 打开报表1 （2）. 设计宏组：新建宏设计视图-宏组名宏1：openform 宏2：openform 宏3：openquery 宏4：openreport （3）。设计窗体中四个命令按钮。分别设置属性-事件-单击-宏名.宏1第七章 模 块1、模块：完成一定功能程序段。包含：声明和过程两部分。 (1). 声明：过程中变量数据类型进行定义。 常用数据类型: Integer , long single , double , string ,Boolean ,date 声明: dim x1 as integer, 用在函数和子程序前面.(2). 过程：函数和子程序Function 过程名程序代码End function Sub 过程名程序代码End sub (3). 数据形式: 常量, 变量, 数组, 函数, 表达式 25, 1.2, 1.2E+3 “ABCD”, “中国” , #1986-10-12# , False ,Ture Const PI=3.1415926 变量使用需要声明. 数据库变量引用: 窗体对象: forms! 窗体名称 ! 控件名称 报表对象: reports! 报表名称 ! 控件名称 如:forms ! 窗体1 ! 姓名=”张