信息系统概论二PPT课件

9数据库体验 SQL与Access 9数据库体验 SQL与Access SQL是一种标准计算机语言 用来对关系数据库中的数据进行检索和操作 在Oracle DB2 SQLServer Sybase Informix Access等主流数据库管理系统中 都采用SQL来进行数据库操作 Access是基于Windows系统的桌面关系数据库管理系统 为用户提供数据库管理的工具集和应用程序的开发环境 9数据库体验 SQL与Access 9 1SQL语言 9 2用Access创建数据库 9 3查询 9 4本章小结 9 1SQL语言 SQL StructuredQueryLanguage 结构化查询语言 是一种数据库查询和程序设计语言 用于存取数据以及查询 更新和管理关系数据库系统 同时也是数据库脚本文件的扩展名 用SQL可以完成以下任务 面向数据库执行查询 从数据库读取数据 向数据库中插入新的数据 更新数据库中的数据 从数据库删除数据 创建新数据库 在数据库中创建新表 在数据库中创建存储过程 在数据库中创建视图 设置表 存储过程和视图的权限等 SQL语言有很多不同的版本 但是为了与ANSI标准相容 它们必须以相似的方式共同支持一些主要的关键词 9 1SQL语言 1 SQL的组成SQL由以下几个部分组成 数据定义语言 DataDefinitionLanguage DDL 数据操纵语言 DataManipulationLanguage DML 数据控制语言 DataControlLanguage DCL 以及其它语言要素 SQL的数据定义语言使用户能够创建或删除表 也可以定义索引 键 规定表之间的连接 以及施加表间的约束 SQL的数据定义语言 DDL 提供的主要命令如下 CREATEDATABASE 创建新数据库ALTERDATABASE 修改当前的数据库CREATETABLE 创建新表ALTERTABLE 变更 改变 表的结构DROPTABLE 删除表CREATEINDEX 创建索引DROPINDEX 删除索引 9 1SQL语言 1 SQL的组成SQL的数据操纵语言使用户能够在数据库中添加 删除 修改以及查询数据 查询和更新命令构成了SQL的DML部分 SQL的数据操纵语言 DML 提供的主要命令如下 SELECT 从数据库表中获取数据UPDATE 更新数据库表中的数据DELETE 从数据库表中删除数据INSERT 向数据库表中插入数据SQL的数据控制语言包括用来设置或更改数据库用户或角色权限的命令 主要包括GRANT DENY REVOKE等 另外 SQL还提供了很多用于统计和计算的内建函数 例如AVG函数返回数值类型字段的平均值 COUNT函数返回匹配指定条件的记录数等 SQL用户可以是应用程序 也可以是用户 9 1SQL语言 2 常用的SQL语句SQL语句主要用于对数据表进行操作 一个数据库中通常包含一个或多个表 每个表都有一个名字 表中的数据分为行 记录 和列 字段 例如 下面是一个名称为 stud info 的示例表 包含三行 每行对应一个学生的相关信息 和四列 学号 姓名 出生日期和性别 9 1SQL语言 2 常用的SQL语句 1 CREATETABLE 创建数据库表例如 创建名为 stud info 的表的SQL语句如下 CREATETABLEstud info 学号CHAR 10 姓名VARCHAR 30 出生日期DATE 性别CHAR 2 UNIQUE 学号 9 1SQL语言 2 常用的SQL语句 2 INSERTINTO 向表中插入记录其一般格式为 INSERTINTO表名VALUES 例如 向名为 stud info 的表中加入一条学号为 03102102 姓名为 张东 生日为 1987 01 01 的男生记录 对应的INSERT语句如下 INSERTINTOstud infoVALUES 03102102 张东 1987 01 01 男 9 1SQL语言 2 常用的SQL语句 3 DELETE 从表中删除记录其一般格式为 DELETEFROM表名WHERE条件 例如 删除 stud info 表中学号为 03101001 的学生记录 DELETEFROMstud infoWHERE学号 03101001 9 1SQL语言 2 常用的SQL语句 4 UPDATE 更新其一般格式为 UPDATE表名SET字段名1 值1 字段名2 值2 WHERE条件 例如 将 stud info 表中学号为 01101101 的学生的出生日期改为1988 05 01 UPDATEstud infoSET出生日期 1988 05 01WHERE学号 01101101 9 1SQL语言 2 常用的SQL语句 5 SELECT 选择选择表中部分记录 行 使用SELECT命令 表示所有字段 其简单格式为 SELECT FROM表名WHERE条件 例如 选择 stud info 表中所有的男生记录 SELECT FROMstud infoWHERE性别 男 9 1SQL语言 2 常用的SQL语句 6 投影投影操作可用于选出表中部分字段 列 的信息 用SELECT语句实现 例如 仅列出 stud info 表中所有人的姓名和性别 SELECT姓名 性别FROMstud info 7 连接连接是通过对两个表中的公共字段进行拼接的一种运算 实现连接运算的SQL语句的基本格式为 SELECT字段名1 字段名2 FROM表名1 表名2WHERE条件 9 2用Access创建数据库 9 2 1应用需求分析9 2 2创建空白数据库9 2 3创建表9 2 4在表中添加 删除和修改数据9 2 5编辑表 9 2 1用Access创建数据库 9 2 1应用需求分析每个数据库在创建时都有明确的应用需求 即数据库中要存放哪些数据 这些数据如何组织等等 作为一个简单的例子 我们考虑建立一个简化的小型超市数据库 商品信息员工信息会员信息销售信息 9 2用Access创建数据库 9 2 1应用需求分析商品信息员工信息会员信息销售信息 9 2用Access创建数据库 9 2 1应用需求分析商品信息员工信息会员信息销售信息 9 2用Access创建数据库 9 2 1应用需求分析销售信息下面分两个表记录销售信息 销售小票汇总表的每个记录表示一张小票的汇总信息 包括小票编号 实收金额 收款日期和时间 收银员的编号以及顾客的会员编号 销售小票明细表则记录所售商品的清单 包括小票编号 序号 商品编号 销售数量和商品单价等 9 2用Access创建数据库 9 2 1应用需求分析销售信息 销售小票汇总表销售小票明细表 9 2 2创建空白数据库 使用Access管理数据时 需先创建一个空白数据库 然后再向其中添加对象和数据 Access提供了表 查询 报表 窗体 宏和模块共6组数据库对象 表是数据库的核心与基础 它存放着数据库中的全部数据信息 报表 查询和窗体都从表中获得数据信息 以实现用户某一特定的需要 例如查找 计算统计 打印和编辑修改等 窗体可以提供良好的用户操作界面 通过它们可以直接或间接调用完成模块 执行查询 打印 预览 计算等功能 甚至对数据表进行编辑修改 9 2 2创建空白数据库 MicrosoftAccess2007的启动界面 9 2 2创建空白数据库 用户可以通过启动界面上的 空白数据库 按钮 位于启动界面中部的上方 来创建一个空白数据库 单击 空白数据库 按钮之后 Access会创建一个默认名为 Database1 的数据库 并在 数据表 视图中打开一张默认名为 表1 的空白表 这张表是Access自动创建的 9 2 3创建表 建立了空白数据库后 就要向数据库中添加对象 其中最基本的对象是表 表是所有其他类型对象的基础 每张表由若干个字段构成 每个字段对应实体的相关属性 表中的数据由若干条记录组成 每条记录都对应于一个实体 同一表中的每个记录都具有相同的字段定义 在数据库中创建表之前 需要先进行表的设计 包括表的数目 每个表的作用和结构 即表中字段的类型和含义 表之间的联系等 9 2 3创建表 建立了空白数据库后 就要向数据库中添加对象 其中最基本的对象是表 表是所有其他类型对象的基础 每张表由若干个字段构成 每个字段对应实体的相关属性 表中的数据由若干条记录组成 每条记录都对应于一个实体 同一表中的每个记录都具有相同的字段定义 在数据库中创建表之前 需要先进行表的设计 包括表的数目 每个表的作用和结构 即表中字段的类型和含义 表之间的联系等 9 2 3创建表 2 表的结构设计根据前面关于超市应用场景的简要叙述和数据存储要求 设计了5个表 分别为商品表 员工表 会员表 销售小票汇总表和销售小票明细表 其结构 即包含的字段 说明如下 商品表 用Products命名 包含商品编号 商品名称 进价 零售价和库存数量字段 商品编号用于唯一标识一条商品记录 员工表 用Employee命名 包含的字段为工号 姓名 性别 密码 身份证号和联系电话 工号用于唯一标识一条员工记录 会员表 用Member命名 包含的字段为会员编号 积分 姓名 身份证号和联系电话 会员编号用于唯一标识一条会员记录 9 2 3创建表 2 表的结构设计商品表 用Products命名 员工表 用Employee命名 会员表 用Member命名 销售小票汇总表 用销售小票汇总命名 包含的字段为小票编号 日期 时间 实收金额 会员编号 收银员编号 小票编号用于唯一标识一条销售小票汇总记录 会员编号可以没有值 即为空 收银员编号与员工表中的工号相对应 销售小票明细表 用销售小票明细命名 包含的字段为小票编号 序号 商品编号 数量 单价 小票编号和序号用于唯一标识一条商品销售记录 9 2 3创建表 2 表的字段类型数据库表中的每一个字段都有类型 Access允许的数据类型为 文本 备注 数字 日期 时间 货币 自动编号 是 否 OLE对象 超级链接 查询向导 文本 类型名为VARCHAR 或CHAR 这种类型允许最多255个字符或数字 Access默认的大小是50个字符 备注 类型名为TEXT 这种类型用来保存长度较长的文本信息 它允许存储长达64000个字符的内容 但是 Access不能对备注字段进行排序或索引 对文本字段则可以 数字 类型名为INTEGER 长整型 SHORT 整型 DOUBLE或FLOAT 双精度型 REAL 单精度型 BYTE 字节型 NUMERIC 小数 这种字段类型可以用来存储进行算术计算的数字型数据 用户还可以设置 字段大小 属性定义一个特定的数字类型 在Access中通常默认为 双精度型 9 2 3创建表 2 表的字段类型日期 时间 类型名为DATE TIME或DATETIME 这种类型是用来存储日期 时间或日期时间一起的 每个日期 时间字段需要8个字节的存储空间 货币 类型名为MONEY 这种类型是数字数据类型的特殊类型 等价于具有双精度属性的数字类型 自动编号 这种类型较为特殊 每次向表格添加新记录时 Access会自动插入唯一顺序或者随机编号 即在自动编号字段中指定某一数值 自动编号一旦被指定 就会永久地与记录连接 是 否 类型名为BIT 这种字段是针对于某一字段中只包含两个不同的可选值而设立的字段 通过是 否数据类型的格式特性 用户可以对是 否字段进行选择 9 2 3创建表 2 表的字段类型OLE对象 类型名为OLEObject 指字段允许单独地 链接 或 嵌入 OLE对象 添加数据到OLE对象字段时 可以链接或嵌入到Access表中的OLE对象是指在其他使用OLE协议的程序所创建的对象 例如WORD文档 EXCEL电子表格 图像 声音或其他二进制数据 超级链接 这种类型主要是用来保存超级链接的 包含作为超级链接地址的文本或以文本形式存储的字符与数字的组合 查阅向导 这种类型为用户提供了一个建立字段内容的列表 可以在列表中选择所列内容作为添入字段的内容 9 2 3创建表 3 主键 外键和索引在关系数据库中 表的一条记录中有若干个字段属性 若其中某一个 或一组 属性能唯一地标识一条记录 则该属性 组 就可以成为一个主键 或主码 主关键字 例如 在前面给出的商品表中 每种商品都有一个独一无二的编号 由不同厂家生产的同种商品 其编号也不同 因此 可用商品编号作为该表的主键 有些表需要将多个字段属性组合在一起来唯一标识一条记录 此时 主键就是一个属性组 例如 在销售小票明细表中 不同记录的小票编号可能相同 但是其序号一定不同 反之 序号相同的记录 其小票编号一定不同 因此 小票编号和序号组合起来作为销售小票明细表的主键 9 2 3创建表 3 主键 外键和索引在关系数据库中 表的一条记录中有若干个字段属性 若其中某一个 或一组 属性能唯一地标识一条记录 则该属性 组 就可以成为一个主键 或主码 主关键字 数据库中的每个表都必须有主键 以使表中的记录唯一 记录间不能完全重复 因为它与实体一一对应 一个表通过外键与另一张表关联 用于保持数据的一致性 即A表中的一个字段X是B表的主键 那么X就可以是A表的外键 表内还可以定义索引 可以是一个或多个 当表内存放大量数据时 建立索引可以加速数据的查找 9 2 3创建表 4 应用示例在Access中可以应用SQL语句实现数据表的建立 修改和查询等操作 要运行SQL语句 必须打开SQL语句的编辑窗口 9 2 3创建表 4 应用示例在Access中可以应用SQL语句实现数据表的建立 修改和查询等操作 要运行SQL语句 必须打开SQL语句的编辑窗口 在其中输入创建表结构的SQL语句并运行 即可创建内容为空的表 9 2 3创建表 4 应用示例在Access中可以应用SQL语句实现数据表的建立 修改和查询等操作 要运行SQL语句 必须打开SQL语句的编辑窗口 在其中输入创建表结构的SQL语句并运行 即可创建内容为空的表 在Access中还可以通过设计器创建表 而不用编写SQL代码 创建商品表 用Products命名 员工表 用Employee命名 会员表 用Member命名 销售小票汇总表 用销售小票汇总命名 销售小票明细表 用销售小票明细命名 9 2 4在表中添加 删除和修改数据 创建表仅仅建立起了数据库中表的结构 还需要通过添加 删除和更改操作在其中加入数据 或根据情况删除和修改数据 1 用INSERT命令添加数据2 用DELETE命令删除数据3 用UPDATE命令修改数据 9 2 4在表中添加 删除和修改数据 1 用INSERT命令添加数据SQL语言中的INSERTINTO命令用于向表中插入新的记录 行 其基本格式为 INSERTINTO表名VALUES 值1 值2 例如 表Products初始为空表 在SQL视图窗口中执行SQL语句 INSERTINTOProductsVALUES 6938279211152 纸杯 8 50 12 90 2500 后 则添加了一条新的商品记录 9 2 4在表中添加 删除和修改数据 2 用DELETE命令删除数据SQL语言中的DELETE命令删除表中的记录 行 例如 删除商品表Products中商品编号为 6938279211152 的纸杯记录 对应的SQL语句如下 DELETEFROMProductsWHERE商品编号 6938279211152 9 2 4在表中添加 删除和修改数据 3 用UPDATE命令修改数据SQL语言中的UPDATE命令删除表中的记录 行 例如 将商品表Products中商品编号为 6940477402003 的牙膏的零售价改为17 45 对应的SQL语句如下 UPDATEProductsSET零售价 17 45WHERE商品编号 6940477402003 9 2 5编辑表 表结构建好后 可以用INSERT DELETE和UPDATE命令对其中的数据进行增 删 改操作 但实际应用中可能还需要对表的结构进行修改 例如添加字段或删除字段 修改字段的名称和类型等 这就是编辑表的任务 各种数据库管理系统除了支持通过SQL语言编辑表外 还提供了图形化用户界面完成编辑表的相关操作 下面简单介绍这两种方式在Access中的实现方法 1 用SQL语句修改表结构2 用Access的表设计视图编辑表 9 2 5编辑表 1 用SQL语句修改表结构SQL的ALTERTABLE语句用于修改表的结构 即在已有的表中添加 修改或删除字段 用ADD子句向已存在的表中增加字段用MODIFY子句修改已存在表中的字段用DROP子句删除已存在表中的字段 9 2 5编辑表 1 用SQL语句修改表结构用ADD子句向已存在的表中增加字段语句格式为 ALTERTABLE表名ADD 例如 在员工表中增加住址和入职时间字段 对应的语句为 ALTERTABLEEmployeeADD 住址VARCHAR 50 入职时间DATENOTNULL 9 2 5编辑表 1 用SQL语句修改表结构用MODIFY子句修改已存在表中的字段语句格式为 ALTERTABLE表名MODIFY 用DROP子句删除已存在表中的字段语句格式为 ALTERTABLE表名DROP 9 2 5编辑表 2 用Access的表设计视图编辑表 9 3查询 查询是数据库应用中的一个重要操作 数据库中的不同数据分门别类地保存在不同的表中 例如 员工表 保存员工的有关信息 而 商品表 则保存与商品相关的信息 在应用中 人们经常需要从多个表中获取信息 但是又不需要将所有可能用到的数据全部都罗列出来 这就需要使用查询 在数据库中可以利用 查询 产生多样化的数据 这些数据可以来自一个或多个表 9 3查询 查询的种类在Access中最常使用的是选取查询 即输入条件后 将符合条件的数据从一个或多个表中选取出来 除了选取查询外 还有交叉数据表查询 参数查询及动作查询等 查询与数据表之间的关系查询是对表中的数据进行过滤后获取而来的 这些数据来源可以是窗体 原始数据 或是过滤出来的数据 查询 而查询和表中的数据 都可以当作其它Access对象 如窗体 报表 或是数据页 的参照来源 在很多实际的应用中 都是以查询中的数据当作参照来源 其主要原因是查询可以设置条件 以便过滤数据库中的原始数据 如果要从多个表中筛选出所要查询的数据 则这多个表之间必须要设置联系 9 3查询 9 3 1用SQL建立查询9 3 2使用查询设计窗口9 3 3筛选数据与查询 9 3查询 9 3 1用SQL建立查询SQL中的SELECT语句是使用最为频繁的语句 用于从数据表中检索出所需要的数据 一个SELECT语句包含需要从数据库中获得的一组数据的完整描述 其中包括 哪些表包含数据 不同数据源中的数据怎样关联 哪些字段或计算将产生数据 数据必须符合哪些条件才能被选中 是否以及怎样对结果进行排序 例如 要查询商品表Products中所有牙膏的编号和库存数量 相应的SELECT语句为SELECT商品编号 库存数量FROMProductsWHERE商品名称 牙膏 9 3查询 9 3 1用SQL建立查询所有SQL查询都可以直接在SQL视图中创建 9 3查询 9 3 1用SQL建立查询所有SQL查询都可以直接在SQL视图中创建 9 3查询 9 3 1用SQL建立查询所有SQL查询都可以直接在SQL视图中创建 9 3查询 9 3 1用SQL建立查询1 SELECT的基本格式SELECT语句构成了SQL语言的核心 它包括5个主要子句 分别是FROM WHERE GROUPBY HAVING ORDERBY子句 完整的SELECT语句格式如下 SELECT ALL DISTINCT FROM WHERE GROUPBY HAVING ORDERBY ASC DESC 其中 子句SELECT FROM是必须的 其余的子句为可选项 顺序为WHERE GROUPBY HAVING ORDERBY HAVING条件子句只能与GROUPBY搭配起来使用 9 3查询 9 3 1用SQL建立查询1 SELECT的基本格式SELECT ALL DISTINCT FROM WHERE GROUPBY HAVING ORDERBY ASC DESC 其中 子句SELECT FROM是必须的 其余的子句为可选项 顺序为WHERE GROUPBY HAVING ORDERBY SELECT语句中用方括号括起来的部分表明这些子句是可选项目WHERE子句是一个用于选择行的说明子句 用这个子句可以对表中要选的记录进行限制 当WHERE后面的行选择 条件表达式 为真的时候 才将这些行作为查询结果的行 在WHERE子句中还可以有多种约束条件 这些条件可以通过 AND 与 OR 或 等逻辑运算符联接起来 9 3查询 9 3 1用SQL建立查询1 SELECT的基本格式SELECT ALL DISTINCT FROM WHERE GROUPBY HAVING ORDERBY ASC DESC 其中 子句SELECT FROM是必须的 其余的子句为可选项 顺序为WHERE GROUPBY HAVING ORDERBY GROUPBY子句用于对表或查询中的记录进行分组 若同时要求对所选的记录进行限制 只允许满足条件的行进行分组和统计计算 则需使用HAVING条件子句 ORDERBY子句用于将各种记录进行排序 ASC 表示升序 DESC 表示降序 9 3查询 9 3 1用SQL建立查询2 SELECT应用举例 9 3查询 9 3 1用SQL建立查询2 SELECT应用举例例1 查询 销售小票明细 表中购买数量大于1的商品 只列出商品编号和数量 对应的SELECT语句如下所示 SELECT商品编号 数量FROM销售小票明细WHERE数量 1 9 3查询 9 3 1用SQL建立查询例2 查询 销售小票明细 表中购买数量大于1的商品 列出商品编号 商品名称和数量 由于销售小票明细表中仅保存了商品的编号 因此对于其中的每一种商品 还需从商品表 Products 中获得其名称 对应的SELECT语句如下所示 SELECT销售小票明细 商品编号 Products 商品名称 销售小票明细 数量FROM销售小票明细 ProductsWHERE 销售小票明细 数量 1 AND Products 商品编号 销售小票明细 商品编号 9 3查询 9 3 1用SQL建立查询例3 查询 销售小票明细 表中各种商品的销售数量 列出商品编号 商品名称 销售数量 单价和金额 完成该项查询要求涉及销售小票明细表和 Products 两个表 同时 每条销售记录