数据库原理及应用CH.ppt

1,表与表之间的关系数据库的设计数据库的创建与打开使用向导创建表字段的定义表的设计视图表中数据输入与操纵,4,章,第,创建数据库,4.1 表与表之间的关系,1.表,关系数据库：用二维表格来表示和处理信息世界的实体集和属性关系。,2.表与表之间的关联,主码（主关键字）：表中一个或多个字段，其值惟一地标识一条记录。在两个表的关系中，主码用来在一个表中引用来自于另一个表中的特定记录。系统不允许任何重复值或Null值进行主码字段中。主码的性质：主码不能为空，否则将失去对某些记录的控制和访问主码不能重复，否则主码所标识的记录将失去唯一性 主码不能修改。它不仅标识表中的记录，而且经常要与其他表中的字段进行关联。如果硬要修改，很容易给其所在的表以及其他相关的表造成意想不到的错误 外码（外部关键字）：也是表中的一个或多个字段，外码的值与相关表的主码相匹配。,3.主码与外码,关系：利用两个表的共有字段创建的关联性。通过表间的关联性，可将数据库中多个表联络成一个有机的整体表与表间的关系类型：一对一关系：基本表每条记录只对应相关联表中一个匹配的记录，反之亦然一对多关系：关系中最常用的类型。基本表一条记录可与相关联表中多条记录相匹配。但相关联表一条记录只与基本表一条相匹配,4.关系,多对多关系：基本表一条记录可与相关联表多个记录相匹配。相关联表中的一条记录也可与基本表的多条记录相匹配。这种类型的关系只能通过定义第三个表（联结表）来实现。联结表的主码包含两个字段：来源于两个表的外码。多对多的关系实际上是通过第三个表来实现的两个一对多的关系。,本讲首页,4.2 数据库的设计,1.数据库的规划,表是Access数据库的基础，记录着数据库中的全部内容，而其他对象只是Access提供的工具，用于对数据库进行维护和管理。因此，设计数据库的关键是设计表,ER模型设计：定义了数据库结构。识别和创建数据库中实体及其关系实体可是具体的，也可是抽象的。如Northwind的订单明细：是产品实体和订单实体间的联系,规范化的目标：设计一个优化的和符合逻辑的数据库方案，使得开发阶段对数据库的修改要求降到最低程度，且有助于组织数据和消除数据库中的数据冗余规范化的要求：表中都是不可再分的基本字段（1NF）例如，当产品有出厂，批发，零售等多种价格时，要分别设置相应字段。若价格结构太复杂，则可建新表专门存放价格数据。表中所有字段都必须依赖于主关键字（2NF）一个表只存储一种实体对象。表中所有字段都是惟一的且不互相依赖（3NF）例如，表中只设一个日期字段。不能再包含日，月和星期的字段。,2.数据库规范化,确保数据的完整性：参照完整性：输入或删除记录时，为维持表间已定义的关系而必须遵循的一个规则系统。若实施了参照完整性，则在将与基本表无关的记录加入相关表时，Access会警告。若要从基本表中删除记录，相关表中却有对应记录时，Access会防止删除。设置参照完整性的条件：来自于主表的匹配字段是主码或具有唯一的索引相关字段的数据类型相同。例外：自动编号与长整型，均为同步复制 ID的自动编号与数字字段可相关。两个表都属于同一个Access数据库实行参照完整性后必须遵守的规则：不能将值输入到相关表的外码字段中，若相关表中有匹配记录，不能从主表中删除它,3.数据库设计步骤,例：设计“教学管理”数据库,S1：明确建库目的与功能要求实现四方面信息的综合管理：教师，学生，课程，学习成绩教师可以查看学生的简况，成绩，课程学生可以选择教师，课程和查看成绩,S2：确定数据库中的表按下列原则对信息进行分类：表中不含重复信息，且信息不应在表之间复制 每条信息只保存在一个表中每个表只包含一个主题的数据。,S3：确定表中的字段每个字段的内容均与表的主题相关相关主题的全部数据都要表现出来。不包含需要推导或计算的数据，要以最小逻辑部分作为字段来保存数据。S3.1：教师课程表分析基本特征：姓名，性别，职称，专业特长等 其它特征：工作时间，政治面貌，电话，课程号，所属院系等选取：教工号，姓名，性别，职称，课程号，电话S3.2：学生登记表分析基本特征：学号，姓名，性别，班级，出生日期其它特征：籍贯，政治面貌，宿舍，班级，特长等 选取：学号，姓名，性别，班级，出生日期，简历,S3.3：课程名称表分析基本特征信息：课程号，教工号，课程名称其它特征信息：学分，课时，先修课选取：课程号，教工号，课程名称，学分S3.4：学生成绩表分析基本特征信息：学号，课程号，平时成绩，考试成绩其它特征信息：学分，教工号，课程名称选取：学号，课程号，平时成绩，考试成绩,S4：确定主码学生登记表：学号教师课程表：教工号课程名称表：课程号学生成绩表：学号课程号【注】可增加一个值为序列号的字段作为主码,S5：优化设计问题：教师课程表，若一人讲授多门课程，则有重复数据，如：教工号 姓名 性别 职称 课程编号 电话 4382 王大中 男 讲师 00667 3565678 4382 王大中 男 讲师 00789 3565678分为两个表：教师登记表：教工号，姓名，性别，职称，电话教师课程表：ID，教工号，课程号【注】ID是给教师课程表添加的主码。则上例中数据放在两个表中：教工号 姓名 性别 职称 电话 ID 教工号 课程号4382 王大中 男 讲师 3565678 1 4382 00667 2 4382 00789,本讲首页,S6：确定表间的联系,3.3 数据库的创建与打开,两种创建数据库的方法：使用数据库向导创建数据库及表，查询等各种对象 先创建空数据库，然后再分别添加对象,1.Microsoft Access对话框,动,2.创建空白数据库,动,3.使用向导创建数据库,4.打开数据库的命令方式,5.使用收藏夹打开数据库（对象）,6.使用快捷方式打开数据库,本讲首页,4.4 使用向导创建表,1.创建表的过程,表：关系型数据库的基本结构。在Access中，表是一种关系特定主题的数据集合，如产品，供应商等。每个主题对应单个表。表的形式：记录，字段组成的二维表格。创建一个表的过程：创建表的结构：定义字段名，字段的类型，宽度等填充表中的数据：各类不同数据类型的字段的填充方式不尽相同根据表与表间的共有字段建立联系Access 提供多种创建表的方法,2.表的两种视图,表的两种视图：设计视图：创建及编辑表的结构，即定义字段名，数据类型，宽度，设置字段默认值，格式等数据表视图：查看，添加，删除，编辑表中数据。,表视图,设计视图,3.新建表对话框,动,4.使用向导创建表的例子,本讲首页,4.5 字段的定义,字段：数据库操纵最小单位。设计表实际上是设计字段。其他对象的设计重心都是存取或使用字段的数据字段基本属性：字段名、数据类型、字段说明、及数据存储使用方式字段查阅属性：显示格式、掩码、默认值、有效性规则等,1.字段名与字段说明,Access 2000中字段可用的数据类型有以下几种：文本(Text)：文本、文本与数字、不必计算的数字，最长255个字符。备注(Memo)：较长的文本，最长64,000字符。数字(Number)型：用于算术运算的数字。可在“字段大小”中分字节、整型、单精度等子类型日期及时间(Date/Time)：从100到9999的日期及时间值。可进行日期及时间的计算。8字节货币(Currency)：货币值。使用货币类型可避免计算时四舍五入。8字节自动编号(AutoNumber)：添加记录时的自动序号 递增：添一条记录，字段值增1；随机：添一条记录，字段值为另一随机数。,2.字段的数据类型,是/否(Yes/No)：取两种值之一，如 Yes/No、True/False、On/Off。大小为1位。OLE对象(Object)：可链接或嵌入其他使用OLE 协议的程序所创建的对象，如Word文档、图象、声音等。最多1G。对象可链接或嵌入到Access表中。但只能在窗体或报表中使用绑定对象框来显示。超链接(Hyperlink)：超文本，用作超链接地址。超链接最多包含三部分：显示文本：在字段或控件中显示的文本；地址：到文件（UNC）或页面（URL）的路径；子地址：在文件或页面中的地址。查阅向导(Lookup Wizard)：由查阅向导创建。字段的值来自另一个表或数据列表中，可在列表框或组合框中选择一个数据作为其值。,3.字段的“常规”属性,字段大小：文本长度或数值范围格式：Number、Date/Time、Currency和Yes/No类型有预定义格式，也可设置格式小数位数：小数型（数字、货币）数据的小数位 输入法模式：可设为输入时自动切换到某种输入法,输入掩码：输入数据时的格式，可用“输入掩码向导”来编辑输入掩码 标题：数据表视图或控件中显示字段时所用的标题。用于在字段名意义不明确时再设置一个标题默认值：添加新记录时，自动加入到字段中的值有效性规则：限制输入数据的表达式，如100、Like?#等。可使用表达式生成器来创建有效性文本：数据不符合有效性规则时显示的信息 必填字段：是否必须输入数据允许空字符串：是否允许输入空字符串（长度为0）索引：是否进行索引及索引方式。索引：加快查询和排序速度，但更新操作变慢索引方法：无：字段不索引。有（有重复）：索引、允许重复值。有（无重复）：索引、不允许重复值。,4.字段的查阅属性,显示控件：选择值的控件：组合框或列表框行来源类型：从其他表或查询还是值列表中得到数据行来源：得到数据的值列表或查询语句 绑定列：哪个列作为控件的值,备注型、超链接型和OLE对象型字段不能索引。若在表中输入数据后更改字段的数据类型，则 在保存表时，由于进行大量数据转换，时间较长；若新的“数据类型”属性的设置与段原数据类型冲突，可能会丢失某些数据。Number、Date/Time、Currency和Yes/No类型具有预定义格式。可设置格式，但OLE对象型除外,5.选择数据类型时应注意的问题,本讲首页,3.6 表的设计视图,1.打开表的设计视图,表的设计视图,2.表设计器,字段属性,字段定义网格,3.创建课程表,本讲首页,4.创建值列表字段,字段的查阅功能：值来自一组固定的数据（值列表），或其他表的某个字段。,5.创建查阅列表字段,本讲首页,4.7 表中数据输入与操纵,1.数据表视图,打开“学生”表的数据表视图：在数据库窗口的对象列表中，双击“学生”表，或选定“学生”表，再单击“打开”按钮若当前正处于表设计视图，单击数据库窗口工具条上的“打开”按钮，切换到数据表视图。,2.各类数据的输入方法,文本型：直接在网络中输入。数字型：直接在网络中输入。是否型：标记复选框。日期时间型：按简洁方式键入，自动按设计格式显示。备注型：直接在网络中输入，最好创建窗体输入。,OLE对象型：,超链接型：,3.字段的选择,选中一个网格：方向键移光标；Tab键反方向移动。双击网格；鼠标放在网格左侧，变为空心十字时单击。选中一个网格后，再选相邻的多个网格：Shift方向键按住Shift，再单击另一网格，选中矩形区域选中一个记录：鼠标放在某记录左侧小方钮上，变为右箭头时单击。选中一个字段：鼠标放在某字段上方的标题方钮上，变为下箭头时选中表中所有内容：单击左上角的小方钮。,4.创建表与表之间的关系,定义表间关系有以下优点：表自动链接：查询时打开多个表时，关系表将自动链接，节省操作步骤自动生成索引：Access会自动生成必要的索引，使关系表工作更快实施参照完整性：若主表无关联记录，则不能将记录添加到相关表中或更改主表中的数据，以免形成相关表中的记录在主表中无对应记录 若相关表中有相关记录与主表相匹配时，不能删除主表中的记录在数据表视图中显示子数据表：单击单元格左侧的符号即可打开子数据表,内连接,左外连接,右外连接,展开子数据库表,5.列的隐藏与冻结,隐藏字段：选中要隐藏的字段(可多个)；选择：格式隐藏列。显示被隐藏的字段：选择：格式撤消隐藏列,冻结列：滚动字段时，冻结的列不动。例：冻结“姓名”和“学号”列选中“姓名”和“学号”列；选择：格式冻结列取消对列的冻结:选择：格式取消对所有列的冻结,6.记录的排序,例：“学生”表按“入学总分”排序,取消对记录的排序：,7.记录的筛选,按选定内容筛选：选指定值的记录内容排除筛选：选除指定值外的记录,取消筛选,本讲首页,52,查询的种类使用向导创建查询查询设计器的使用准则与参数查询统计计算与计算字段操作查询设计SQL查询设计,5,章,第,查 询,5.1 查询的种类,1.查询的概念,查询：具有条件检索和计算功能的数据库对象。查询是以表或查询为数据源的再生表。即每次使用查询时，都从指定数据源（表或查询）中创建记录集。使用查询可以：以一个或多个表，或其他查询为基础创建新数据集。按不同的方式来查看、更改和分析数据。生成表，实现数据源表数据的删除、更新或追加。作为窗体、报表或数据访问页的数据源，实现多个表作为数据源。,2.查询的特点,可以重组表中的数据，也可以通过计算再生新数据。查询的结果总是与数据源中的数据同步。每次使用查询，都依据数据源最新的数据组织查询结果。Access允许创建几种不同的查询。最常用的是选择查询。,数据源,查询中的字段,3.查询的种类,Access 2000提供了5种查询：选择查询、参数查询、交叉表查询、操作查询和SQL查询。选择查询：最常见的查询类型，可以：从一个或多个表中检索数据。在一定条件下，更改相关表中的记录。对记录进行分组，并进行总计、计数、平均以及其他类型的计算。交叉表查询：显示来源于表中某个字段的合计值、计算值、平均值等，并将数据分组，一组列在数据表左侧，一组列在数据表上部。参数查询：通过运行查询时的参数定义创建的动态查询结果，可以更多、更方便地查找有用信息。适合于作为窗体或报表的基础。,操作查询：在操作中更改多个记录的查询，可分为四类：删除查询、更新查询、追加查询和生成表查询。删除查询：从一个或多个表中删除一组记录。更新查询：批量更改一个或多个表中的一组记录。追加查询：将一个或多个表中的一组记录添加到另一个或多个表的尾部。生成表查询：根据一个或多个表中的全部或部分数据新建表。SQL查询：使用SQL语句创建的查询。包括联合查询、传递查询、数据定义查询和子查询等。联合查询：将来自一个或多个表或查询的字段组合为查询结果中的一个字段或列。传递查询：直接将命令发送到 ODBC 数据库，如 Microsoft SQL Server等。,数据定义查询：用于创建或更改数据库中的对象，如Access或SQL Server表等。子查询：包含另一个选择查询或操作查询中的SQL Select语句。可在查询设计网格“字段”行输入语句来定义新字段，或在“准则”行来定义字段的准则。,本讲首页,5.2 使用向导创建查询,1.创建查询的方法,动,2.简单查询向导的使用,简单查询向导的功能：以一个或多个表中的指定字段创建查询；对一组记录或全部记录进行各种统计。,3.创建子查询,子查询：数据源为一个查询而不是表的查询。可使用简单查询向导来创建子查询。,指定已有查询作数据源,动,交叉表查询：某些字段名作行标，某些作列标，行与列交叉处显示值,4.交叉表查询向导的使用,本讲首页,5.3 查询设计器的使用,1.查询设计器的构造,2.打开查询设计器,3.查询设计视图中的工具条,视图按钮：在查询的三种视图之间切换查询类型按钮：可在选择查询、交叉表查询、生成表查询、更新查询、追加查询和删除查询之间切换执行按钮：执行查询，以工作表形式显示结果集显示表按钮：显示“显示表”对话框，列出当前数据库中所有的表和查询，以便选择查询的数据源合计按钮：在查询设计区增加“总计”行，用于各种统计计算，如求和、求平均值等。,合计按钮：在查询设计区增加“总计”行，用于各种统计计算，如求和、求平均值等。上限值按钮：可以对查询结果的显示进行约定，用户可在文本框内指定所要显示的范围。属性按钮：显示光标处的对象属性。若光标在查询设计器上半部，则将显示查询的属性若光标在查询设计网格内，则将显示字段列表属性 若光标在字段内，则将显示字段属性生成器按钮：弹出表达式生成器对话框，用于生成准则表达式。在光标位于查询设计网格的“准则”栏时有效数据库窗口按钮：回到数据库窗口。新对象按钮：打开“新建表”、“新建查询”、“新建报表”等各种对话框，准备生成相应的对象。,选择数据源：表或查询，放在上半部；从表中选择字段，放在下半部设计网格；键入准则，设置排序方法，设置是否显示等。,4.查询设计的过程,本讲首页,5.4 条件与参数查询,1.准则的使用,条件：创建查询时添加的限制条件。可使查询结果集中包含且仅包含必要的数据记录。创建查询时，在查询设计器的准则行中输入准则表达式，使Access只显示那些符合准则的记录。如 10 只显示字段值大于10的记录 Between 1000 And 5000 显示值为10005000的记录输入准则的方法：在设计视图中打开查询，在指定字段“准则”行的网格中键入准则表达式，可使用表达式生成器构造表达式，还可切换到查询的SQL视图，观察相应的SQL语句。,2.查询中表达式的使用,Access中表达式的使用场合：有效性规则、查询或筛选准则、属性或参数的设置值、计算控件、宏的条件等,查询中表达式的使用,3.特殊运算符,“与”运算符：And 例，And，须同时满足两个准则A和B。“或”运算符：Or例，Or，须满足两个准则A和B之一。指定一个范围：Between And例，Between And，数值应在A、B之间。指定系列值的列表：In 例，In(“西安”,“汉中”,“延安”)，等价于：“西安”Or“汉中”Or“延安”使用通配符实现模糊查询：Like例，Like“Smith”，查找包含字符串“Smith”的记录 Like Sm*“，查找包含以“Sm”开头的字符串的记录。,样式中可以使用的通配符：?单一字符*零个或多个字符#单一数字（0-9）为字符串中该位置的字符设置范围 字符表 字符表中的一个字符，如 a-z、0-9!字符表 不在字符表中的一个字符，如!0-9例：Like PA-F#查找以P开头、后跟A到F间任何字母及3个数字的数据 Like a?a-f#!0-9*查找的字符串中第一个为a，第二个任意，第三个为a到f 中任意一个，第四个为数字，第五个为非0-9的字符，其后为任意字符串。,4.样式中可使用的通配符,5.多个准则的作用,同一行的多个准则相与,不同行的多个准则相或,查找公司名称以“联”开头，且地区为“华北”的记录,查找公司名称以“联”开头且地区为“华北”，或地区为“西北”且城市名以“西”开头的记录。,6.参数查询,参数也是准则，不同的是，准则是在创建查询时输入的，而参数是在运行即打开查询时输入的。打开参数查询时，Access系统显示对话框，要求输入数据，然后将输入的数据插入到指定准则的网格。,参数：字段名和提示信息要用方括号括起来。,将条件改为参数,本讲首页,5.5 统计计算与计算字段,1.查询中的计算功能,查询：查找功能、计算功能。查询中两种基本计算：预定义计算：即“总计”计算，计算查询中全部或部分记录的字段值，包括总和、平均值、数量、最小值、最大值、标准偏差或方差等。使用查询设计网格中的“总计”行进行总计计算。自定义计算：用一个或多个字段值进行数值、日期和文本的计算。要在查询设计网格中添加计算字段，方法是将表达式输入到查询设计网格中的“字段”格中。,2.进行统计计算的方法,3.统计计算函数,4.创建计算字段,本讲首页,5.6 操作查询设计,1.操作查询的功能,操作查询：是在选择查询的基础上创建的。具有选择查询、参数查询的特性，可对数据源中的数据进行追加、删除、更新，并可在选择查询基础上创建新表。操作查询的种类：按查询结果对一个或多个表执行特定的操作。删除查询：从表中删除符合条件的记录。更新查询：更新表中的符合条件的记录。追加查询：将符合条件的记录添加到表尾。生成表查询：根据查询结果生成新表。,2.创建生成表查询,3.创建更新查询,创建追加查询的步骤：利用查询设计器打开或创建一个选择查询发追加查询命令，弹出“追加”对话框：主菜单：查询|更新查询；设计器快捷菜单：查询类型|更新查询；查询设计工具条：查询类型钮|更新查询在对话框中输入待追加数据的表名、确定所在数据库在“追加查询”设计器“追加到”网格中，确定对应的“追加”字段，并在“准则”格输入准则表达式；运行追加查询：主菜单：查询|运行；查询设计工具条：运行钮切换到“表”对象页，查看追加了查询结果集的表。,4.创建追加查询,创建删除查询的步骤：利用查询设计器打开或创建一个选择查询发删除查询命令，在设计网格中添加“删除”行：主菜单：查询|删除查询；设计器快捷菜单：查询类型|删除查询；查询设计工具条：查询类型钮|删除查询在“删除查询”设计器的“准则”网格中输入要删除记录的条件表达式保存查询，结束删除查询的创建,本讲首页,5.创建删除查询,5.7 SQL查询设计,1.SQL语言,SQL语言：简单易学的关系数据库语言。SQL语言的功能：数据定义语言DDL：定义数据库逻辑结构，包括定义表、视图和索引等。数据操纵语言DML：包括数据检索和数据更新两大类操作，其中数据更新包括插入、删除和修改操作。数据控制语言：包括基本表和视图等的授权、完整性描述以及事务开始和结束等控制语句等。嵌入式SQL语言：规定了SQL语言在宿主语言程序中的使用规则。,SQL语言应用的几个方面：在查询“设计”视图中创建查询时，Access将在后台构造等效的SQL语句。某些查询不能在设计网格中创建。对于传递查询、数据定义查询和联合查询，必须直接在 SQL 视图中创建 SQL 语句。在Access中，也可以在输入表名、查询名或字段名等许多地方使用SQL语句。在某些情况下，Access会帮助用户填入SQL语句。SQL语句的使用方法：可以在 SQL 视图中编辑或查看SQL语句。如果在 SQL视图中更改了查询，则该查询可能与其原先在查询“设计”视图中的显示方式有所区别。,2.SQL语言在Access中的应用,SQL查询语句：也称为SELECT命令。基本形式：SELECT-FROM-WHERE查询块。SQL基本查询模块的结构：SELECT FROM WHERE；SELECT子句：用逗号隔开的表达式作查询目标，常用的是属性名列表。系统据此对查询结果进行投影运算。FROM子句：指定查询目标以及下面的Where子句中所涉及的所有“关系”的名称。WHERE子句：指定查询目标必须满足的条件，系统根据条件进行选择运算，输出条件为真的元组集合。,3.SQL查询语句的格式,例1：查找“李玉”的学号和所在的班级SELECT 学号,姓名，班级FROM 学生WHERE 姓名=“李玉;例2：查找所有学生的全部情况SELECT*FROM 学生;例3：查找通信21班、电子21班学生的姓名和入学成绩,结果按入学分数的降序排列。SELECT 学号,姓名,入学总分FROM 学生WHERE 班级 IN(“通信21”,“电子21”)ORDER BY 入学总分 DESC;ORDER BY子句：查询结果排序，DESC:降序，ASC:升序,3.SQL查询语句举例,例4：查找选修了“面向对象编程”课程的学生姓名、所在班级及考试成绩SELECT 学生.姓名,学生.班级,选课.考试成绩FROM 学生WHERE 学号 In(SELECT 学号 FROM 选课 WHERE 课程号=37002);嵌套查询：在SELECT-FROM-WHERE查询块内部再嵌入另一个查询块。允许多层嵌套。例5：查找每门课程的平均考试成绩和选修人数 SELECT 课程号,AVG(考试成绩),COUNT(考试成绩)FROM 选课 GROUP BY 课程号;GROUP BY：按属性值对元组分组。,5.创建SQL查询,打开查询设计视图,本讲首页,