Access数据库及其应用(98页).doc
-本课程主要讲述：数据库基本理论 Access 数据库操作 SQL 数据库查询语句 * VBA语言及模块设计第一章 数据库基础知识讲述内容：1、数据管理发展史2、数据库基本概念 * 3、数据库四级结构模型4、概念模型：E-R模型 *5、逻辑模型：关系模式 *6、关系运算 *7、数据规范化：1NF,2NF,3NF *一、数据管理发展史1、人工管理阶段计算机发展初期，主要用于科学计算，数据不独立，完全依赖于程序，不能共享。2、文件管理阶段 数据以文件形式管理，数据无结构化，共享性差。3、数据库管理阶段 数据结构化（数据库形式），出现多种数据库管理系统，共享性好。二、数据库基本概念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（笛卡尔积），(选择)，（投影），（连接）；>（大于） （大于等于） <（小于） （小于等于）=（等于） （不等）；逻辑运算符：（与）,（或）,（非）1、并运算：关系R和关系S的并，是R和S的所有元组合并，删去重复元组，组成一个新关系如：R=（李力，男），（王红，女） S=（王红，女），（刘伟，男） RS=（李力，男），（王红，女），（刘伟，男）* 对于关系数据库，记录的插入和添加可通过并运算实现。2. 差关系R与关系S的差，由属于R而不属于S的所有元组组成，即R中删去与S中相同的元组，组成一个新关系。如：R=（李力，男），（王红，女） S=（王红，女），（刘伟，男） R-S=（李力，男）* 通过差运算，可实现关系数据库记录的删除。3. 交关系R与关系S的交，由既属于R又属于S的元组组成，即R与S中相同的元组，组成一个新关系。如：R=（李力，男），（王红，女） S=（王红，女），（刘伟，男） RS=（王红，女）如果两个关系没有相同的元组，那么它们的交为空。4、笛卡尔积(集合中元素的组合)，如D1，D2，Dn，它们笛卡尔积为D1×D2××Dn=（d1，d2，dn）。|diDi，i=1，2，n。笛卡尔积也是一个集合。例如：D1=A,B,C D2=1,2,3D1,D2两个域的笛卡尔积为D1XD2=（A，1），（A，2），（A，3），（B，1），（B，2），（B，3），（C，1），（C，2），（C，3）笛卡尔积的基数M=m1Xm2=3X3=9 例如：D1(李力，王平，刘伟),D2(男、女) 两个关系的笛卡尔积为：D1×D2=（李力，男），（李力，女），（王平，男），（王平，女），（刘伟，男），（刘伟，女）基数M=m1×m2=3*2=6，元组的个数为6笛卡尔积可用二维表的形式表示。姓名性别李力男李力女王平男王平女刘伟男刘伟女 例如: R S ABCABCA1B1C1A1B1C1A1B1C2A2B2C1A2B2C1A2B3C2 RUS R-S ABCABCA1B1C1A1B1C2A1B1C2A2B3C2A2B2C1 RnS ABCA1B1C1A2B2C1RXSABCABCA1B1C1A1B1C1A1B1C1A2B2C1A1B1C1A2B3C2A1B1C2A1B1C1A1B1C2A2B2C1A1B1C2A2B3C2A2B2C1A1B1C1A2B2C1A2B2C1A2B2C1A2B3C25.选取：选取运算是单目运算，是根据一定的条件在给定的关系R中选取若干个元组，组成一个新关系，记作：F(R)=t|tRF(t)为真Ø 为选取运算符;F为选取的条件，它由运算对象（属性名、常数、简单函数）、算术比较运算符（ > ，<，=，）和逻辑运算符（ ）连接起来的逻辑表达式，结果为逻辑值“真”或“假”。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 B<5(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) - B<5(R) - C B A B C - - 5 4 7 2 3 3 2 4 4 37. 连接（Join）Ø连接运算是二目运算，是从两个关系的笛卡尔积中选取满足连接条件的元组，组成新的关系。Ø 为“=”时，称为等值连接；Ø RS=xy(R×S)Ø在连接运算中，一种最常用的连接是自然连接(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(R×S), R*SB<C_ _ _A R.B S.B C A R.B S.B C A B C_ _ _a b b c a b e a a b cc b b c c b b c a b da b b d d e b d c b cc b b d c b d d e aT 教师关系 TC授课关系 C关系TNOTNSEXAGETNOCNOCNOCNCTT1李力男30T1002001数据库60T2王平女40T2001002法律40T3刘伟男26T3001003英语90T4张雪女34T4002T5张兰女45T5001例：查询讲授数据库课程的教师姓名。TN(CN=数据库(C)*TNO,CNO(TC)*TNO,TN(T)或TN(TNO(CN=数据库(C)*TC)*TNO,TN(T)TN王平刘伟张兰* 笛卡尔积、等值连接、自然连接区别：笛卡尔积产生的新关系的目是两个关系(k1+k2)的和，元组的和是两个关系的积。等值连接是在笛卡尔积的基础上，挑选分量值相等的元组连接，不要求属性名相同。自然连接要求相等属性值的属性名必须相同，即两关系只有在同名属性才能进行连接。等值连接不将重复属性去掉，而自然连接去掉重复属性。R关系 S关系 T关系XY YZXYZABB EABEC D D FCDF D GCDG AT=RS BT=RUS CT=R*S DT=R×S七、数据规范化：1NF,2NF,3NF 所谓范式是指规范化的关系模式。由于规范化的程度不同，就产生了不同的范式。 一个好的关系模型四个条件：尽量减少冗余、没有插入、删除、更新异常。1、第一范式 关系中的每个属性必须是不可再分的简单项，不能是属性组合。 例：某人设计了一个关系模式如下： S（S#，SN，SA，SS，SD，DN，C#，GR） 其中S是关系模式名，S#表示学生学号，SN表示学生姓名，SA表示学生年龄，SS表示学生性别，SD表示学生所在的系别，DN表示学生所在系的系主任，C#表示学生选修的课程号，GR表示学生选修该课程时的成绩。 在关系模式S中，关键字是（S#，C#）。S# SN SA SS SD DN C# GR94001 王言 18 男 机械系 田放 C1 8894001 王言 18 男 机械系 田放 C2 7494001 王言 18 男 机械系 田放 C3 8294001 王言 18 男 机械系 田放 C4 6594002 刘里 19 男 机械系 田放 C1 9294002 刘里 19 男 机械系 田放 C2 9594002 刘里 19 男 机械系 田放 C3 9194002 刘里 19 男 机械系 田放 C4 7894003 张兰 18 女 材料系 王敏 C5 7294003 张兰 18 女 材料系 王敏 C2 7594003 张兰 18 女 材料系 王敏 C4 7994003 张兰 18 女 材料系 王敏 C3 80可见: 关系模式S中所有的属性都是不可再分的简单属性。因此，关系模式S属于第一范式。 通过分析，我们不难发现关系模式S不是一个好的关系模式。 关系模式S存在的问题：（1）冗余大。每一个系名和系主任的名字存储的次数等于该系学生人数乘以每个学生选修 的课程门数；（2）插入异常。一个新系没有招生时，系名和系主任名无法插入到数据库中，因为这个关系模式中的关键字是（S#，C#），而这时没有学生因而S#无值，所以没有关键字值，关系数据库无法操作，因此，引起插入异常。（3）删除异常。当一个系的学生都毕业了而又没有招新生时，删除了全部学生记录，随之也删除了系名和系主任名。这个系依然存在，而在数据库中却无法找到该系的信息。（4）更新异常。若某系主任更换，数据库中该系的学生记录应全部修改。如有不慎，某些记录漏改了，则造成数据的不一致。称之为更新异常。2、第二范式 第二范式就是在满足第一范式条件的基础上，关系模式的非关键字属性都由整个关键字决定（而不是关键字的一部分）。 就上例而言，如果我们把关系模式S变成如下的两个关系模式：SR（S#，SN，SA，SS，SD，DN） SC（S#，C#，GR）SR关系：S# SN SA SS SD DN 94001 王言 18 男 机械系 田放 94002 刘里 19 男 机械系 田放 94003 张兰 18 女 材料系 王敏 SC关系：S# C# GR94001 C1 8894001 C2 7494001 C3 8294001 C4 6594002 C1 9294002 C2 9594002 C3 9194002 C4 7894003 C5 7294003 C2 7594003 C4 7994003 C3 80 这时，在关系模式SR中，S#（SN，SA，SS，SD），SDDN。在关系模式SC中，（S#，C#）GR。 这样就很好地解决了非关键字属性由关键字的一部分来决定的问题。关系模式SR，SC符合第二范式条件的关系模式。第二范式的关系模式仍然存在一些问题： 就SR而言（1）数据冗余。在关系模式SR中，系主任名存储的次数与学生数一样多。（2）插入异常。当某个系的学生全部毕业而没有新生入校时，删除了学生记录也随之删除了这个系的信息。3、第三范式 第三范式就是符合第二范式，并且每个非关键字属性都仅由关键字决定，而且一个非关键字属性值不依赖于另一个非关键字属性值。（消除第二范式中函数依赖传递关系） 就SR而言，我们可以把它分解为SS和RD两个关系模式： SS（S#，SN，SA，SS，SD） RD（SD，DN）。 可见，在SS中非关键字属性SN，SA，SS，SD都依赖于关键字S#，没有非主属性对关键字的部分依赖，也没有非关键字属性值不依赖于另一个非关键字属性值，因此，SS是属于第三范式的关系模式，同理RD也是属于第三范式的关系模式。SR>SS+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 <列名1> HAVING <条件表达式> ORDER BY <列名2> 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三、使用库函数、统计汇总查询。
