试谈数据库创建及其查询

第二章数据库创建及其查询,内容简介,基础篇数据库概述表及其相互间的联系建立ODBC数据源数据查询提高篇数据库建立复杂查询,第一节数据库概述,信息的保存和管理人工管理信息：费时费力、容易出错,第一节数据库概述（续）,人工管理信息：查找困难,利用文件系统来保存信息利用数据库系统管理信息,第一节数据库概述（续）,数据库定义数据库就是存放在计算机中的、以一种合理的方法组织起来的、与公司或组织的业务活动和组织结构相对应的各种相关数据的集合，该集合中的数据可以为公司的各级经过授权的用户和信息系统所共享。数据库是存在于计算机中的、与公司或组织的业务活动和组织结构相对应的各种相关数据的一个集合。存放在数据库中的数据是按一定的方式组织起来的，而不是杂乱无章地存放的。数据库是一个共享的信息资源，它可以被企业或组织中的多个经过授权的用户使用，也可以被与企业有关的各种信息系统使用。,第一节数据库概述（续）,数据库管理系统（DBMS）DBMS是位于用户与操作系统之间的一层数据管理软件。DBMS功能：数据库的建立数据的插入、查询、删除、修改数据控制功能数据安全性控制：保护数据，防止对数据库的非法操作所引起的数据的丢失、泄露和破坏。数据完整性控制：保证数据库中的数据永远是正确的、有效的和相容的。并发控制：避免因多个用户同时存取、修改数据库时所引起的相互干扰，保证数据的正确性。数据库的恢复：当数据库中的数据由于种种原因（如系统故障、介质故障、计算机病毒等）而变得不正确，或部分甚至全部丢失时，数据库管理系统有能力将数据库恢复到最近某时刻的一个正确状态。,第一节数据库概述（续）,数据模型层次模型网状模型关系模型面向对象模型采用关系模型的数据库称为关系数据库采用关系模型的数据库管理系统称为关系型数据库管理系统e.g.Oracle、DB2、SQLServer、Informix、Access,第二节表及其相互间的联系,关系数据库是由一系列的表组成的一般情况下，每个表都具有一个主键通过这些主键，数据库的表与表之间可以建立各种联系,一、表,采用关系模型的数据库由一系列的“二维表”组成，每个表保存着企业业务活动中所涉及的一个特定实体集（或者两个实体集之间的某种联系）的所有实例的各种属性值数据。实体是指客观存在、可相互区分的事物。e.g.一个产品、一个仓库、一艘船、一个房子、一个学生、一门课程、一次订货、一次购房、一次选课实体集是指同类实体的集合。,一、表（续）,会员表,一、表（续）,商品表,二、主键,主键在关系数据库的某些表中存在着其值能够唯一地确定一个记录的属性或属性组，这样的属性（或属性组）称为表的主键。一个表的主键可以由一个属性组成，也可以由多个属性组成。,订单明细表的主键:(订单号，产品号),二、主键（续）,三、表与表之间的联系,一对一联系一对多联系多对多联系,三、表与表之间的联系（续）,一对一联系如果同一数据库中两个表的各个记录之间存在着一种一一对应的关系，亦即，每个表中的一个记录均（通过主键）与对方表中的一个记录相对应，那么这两个表存在着一对一的联系。,三、表与表之间的联系（续）,一对一联系,三、表与表之间的联系（续）,一对多联系如果数据库的一个表中的一个记录与同一数据库的另一个表中的多个记录（包括0个）相对应，反过来，后一个表中的一个记录只与前一个表中的一个记录相对应，那么这两个表存在着一对多的联系。,三、表与表之间的联系（续）,一对多联系,三、表与表之间的联系（续）,多对多联系在同一数据库的两个表中，如果每个表的一个记录都与对方表中的多个记录（包括0个）相对应，那么这两个表之间就存在着多对多的联系。,多对多联系,三、表与表之间的联系（续）,多对多联系转化成一对多联系,四、Northwind示例数据库中表之间的联系,四、Northwind示例数据库中表之间的联系（续）,客户、雇员、运货商和订单表间的联系,四、Northwind示例数据库中表之间的联系（续）,订单、订单明细和产品间的联系,四、Northwind示例数据库中表之间的联系（续）,产品、类别和供应商表间的联系,第三节建立ODBC数据源,一、MicrosoftODBC体系结构,一、MicrosoftODBC体系结构,一个ODBC数据源包括下面三方面的内容：数据源名字：给要定义的数据源起一个名字。系统将根据该名字建立一个扩展名为.DSN的文件，以存放数据库连接信息。驱动程序名称：选择一个具体的数据库驱动程序，该驱动程序应与数据源所要引用的数据库的类型相匹配。如要引用的是Access数据库，则应选择MicrosoftAccess驱动程序；如要引用Foxpro数据库，则需选择MicrosoftFoxProVFP驱动程序，依次类推。数据库连接信息：给出所要连接的数据库的文件名或文件夹。,二、ODBC数据源的建立,ODBC数据源的建立利用控制面板定义ODBC数据源利用MicrosoftQuery应用程序定义ODBC数据源【例2-1】利用控制面板中的ODBC数据源管理器定义一个名为“nw”的ODBC数据源，该数据源中引用的是“Northwind”示例数据库，该数据库的文件名为“Northwind.mdb”，该数据库存放在“ProgramFilesMicrosoftOfficeOfficeSamples”文件夹下。,二、ODBC数据源的建立（续）,【例2-2】利用控制面板中的ODBC数据源管理器定义一个名为“abcsales”的ODBC数据源，该数据源中引用的是ABC公司销售数据库，假设该数据库中只有一个表，该表存放在文件“ABC公司销售数据.dbf”中，存放位置是“E:ABC公司数据库”文件夹。,二、ODBC数据源的建立（续）,【例2-3】利用MicrosoftQuery定义一个名为“NW”的ODBC数据源，该数据源中引用的是“Northwind”示例数据库，该数据库的文件名为“Northwind.mdb”，该数据库存放在“ProgramFilesMicrosoftOfficeOfficeSamples”文件夹下。,第四节数据查询,所谓数据查询就是从数据库所保存的众多数据中挑出符合某种条件的一部分数据，或者将这些数据挑出来之后再对它们进行适当的运算而得到某种汇总信息(统计信息)。数据查询包括：单表查询：只涉及一个表的查询。简单的多表查询：涉及两个或两个以上表的查询。用MicrosoftQuery进行数据查询的方法：利用“查询向导”进行数据查询。直接进入“查询设计”窗口进行数据查询。,一、单表查询,单表查询是指仅涉及一个表的查询操作，这种查询分为如下几种查询：无条件的查询：从数据库的一个表中查询所有记录中的某几个字段的值。涉及一个条件的查询：从数据库的一个表中按一个查询条件挑出部分记录，显示这些记录中全部字段或部分字段的值。涉及多个条件的查询：从数据库的一个表中按多个查询条件挑出部分记录，显示这些记录中全部字段或部分字段的值。,一、单表查询（续）,【例2-4】“Northwind”示例数据库中存放了其供应商的一些信息，现要求查询其中的供应商ID、公司名称、地区、城市和电话等信息。,一、单表查询（续）,【例2-5】“Northwind”示例数据库中存放了其供应商的信息，现要求查询其中位于“华东”地区的供应商的ID、公司名称、地区、城市和电话等信息。,一、单表查询（续）,【例2-6】查询“Northwind”示例数据库中位于“华东”或“华南”地区的供应商的ID、公司名称、地区、城市和电话等信息。,一、单表查询（续）,【例2-7】查询“Northwind”示例数据库中供应商的ID、公司名称、地区、城市和电话等信息。其中的一些供应商位于华东或华南地区，另外一些供应商所在的城市是天津。,一、单表查询（续）,【例2-8】查询“Northwind”示例数据库中供应商的ID、公司名称、地区、城市和电话等信息。这些供应商所在的地区是华北，联系人职务是销售代表。,一、单表查询（续）,【例2-9】“Northwind”数据库中存放了三年的订单数据，现有关人员想查询该公司的客户在1996年下半年订购的所有订单的订购日期、订单ID、客户ID和雇员ID等信息。,二、简单的多表查询,对于多表查询须了解如下两个问题：查询中使用到的各个字段分别来自于哪些表。分析查询所涉及的表与表之间存在着何种联系，这些联系是通过哪些字段建立起来的。【例2-10】查询Northwind公司所有产品的产品ID、名称、单价和供应商的公司名称等信息。,第五节数据库的建立,一、表的设计表是数据库的基本构件。要创建易于维护的有效数据库，设置合理的表结构是一个关键步骤。例：“响当当”网上书店数据库表的设计“响当当”网上书店在业务活动中所涉及的信息：会员信息商品信息订货单和发货单,例：“响当当”网上书店数据库表的设计,会员信息：,另：根据会员在“响当当”网上书店所购买的商品总价值，可将会员分为：一般会员和钻石会员。,商品信息包括：商品名称、原价、折扣价、钻石价、类别和库存量商品的类别分为：书、CD和软件等订货单信息另：付款方式分为货到时付款、在线支付、预存货款、邮局汇款、银行电汇、储蓄卡汇款和支票付款等方式。,例：“响当当”网上书店数据库表的设计（续）,例：“响当当”网上书店数据库表的设计（续）,发货单信息,例：“响当当”网上书店数据库表的设计（续）,“响当当”网上书店数据库中所涉及的表及相互间的联系,例：“响当当”网上书店数据库表的设计（续）,会员表、商品表和订单表,例：“响当当”网上书店数据库表的设计（续）,付款方式表、订单明细表、级别表和类别表,二、创建数据表,创建数据表的方法表向导：可以利用事先设计好的标准格式帮助经验不多的用户创建所需要的表数据视图【例2-11】“响当当”网上书店需要将其业务活动中的数据存放到一个数据库中，也就是说要将表2-5和表2-6中的七个数据表存放在一个MicrosoftAccess数据库中，假设这个数据库的名称为“xddBookstore”。,二、创建数据表（续）,xddBookstore数据库,二、创建数据表间的联系,创建数据表间的联系【例2-12】建立xddBookstore数据库中各数据表之间的联系,四、数据输入,【例2-13】在xddBookstore数据库中输入级别表的数据,四、数据输入（续）,在输入数据的过程中需注意如下几点：输入数据的类型应与表中相应字段的数据类型一致，否则系统会报错。例如，用户在订单表的“订购日期”字段中输入了一个“非日期”类型的值（如上海），系统报错：,四、数据输入（续）,输入的主键（或称主关键字）的值必须惟一，否则系统会报错。,当两个表之间建立了联系并实施了参照完整性时，应先输入父表中的数据，再输入子表中的数据。另外还应保证子表中输入的外码值必须是父表中主键中出现过的，否则系统将拒绝相应操作。例如，用户在订单表的外码“收货人”字段中输入了一个会员表中不存在的“00000011”会员号，系统报错,若用户不希望考虑表之间的输入顺序的话，在输入各表数据之前，先不要建立表之间的联系。,五、数据导入与导出,数据导入【例2-14】将本书配套磁盘提供的xddBookstore.xls文件中的商品表数据（如图2-76所示）导入到“xddBookstore”数据库的商品表中。,五、数据导入与导出（续）,导入数据时的注意点：导入的外部文件中各列数据的类型应与数据库中对应表的相应字段类型一致。导入的外部文件中主键的值应惟一，否则导入操作也会出错，主键值重复的记录会丢失。若数据库的各表之间建立了联系且实施了参照完整性的话，应先导入父表数据，再导入子表数据，否则导入操作也会出错，系统会提示用户参照完整性被违反了。若用户不希望考虑表之间的导入顺序的话，在导入各表数据之前，先不要建立表之间的联系。,五、数据导入与导出（续）,数据导出将Access数据库中的数据导出到其他的外部文件中；具体方法是：先在Access中选中要导出的表（如商品表），然后单击“文件”菜单的“导出”命令，在随后出现的“将表商品导出为”对话框中设置好相应的保存位置、文件名和保存类型（如下图），然后单击“导出”按钮即可。,第六节复杂查询,一、多表查询内连接：将多个表中符合条件的记录挑选出来组成一个结果集。查询结果中包含的都是符合连接条件的记录。外连接：查询时可以将不符合连接条件的记录一并查询出来。,一、多表查询（续）,内连接【例2-15】Northwind数据库中有三年的数据，现在需要查询最后一个月中每份订单的的订购日期、订单ID、相应订单的客户公司名称、负责订单的雇员的姓氏和名字等信息。并将查询结果按雇员的“姓氏”和“名字”字段的升序排列，“姓氏”和“名字”值相同的记录按“订单ID”的降序排列。,一、多表查询（续）,一、多表查询（续）,在内连接查询中，正确建立表之间的联系的三种方式：若查询中涉及的表有公共的字段名，则MicrosoftQuery应用程序会自动根据两个表之间的公共字段名建立联系。若查询中涉及的表之间没有直接的联系，则可引入中间表，再由MicrosoftQuery应用程序自动根据表之间的公共字段名建立联系。若查询中涉及的表之间有联系但却没有公共的字段名，则可以通过手工方式来添加联系。,一、多表查询（续）,【例2-16】Northwind数据库中存放了其所有订单的信息，现要求查询其中的“10248”和“10254”号订单的订单ID、运货商的公司名称以及订单上所订购的产品的名称。,一、多表查询（续）,【例2-1