数据库基本知识数据采集与分析.ppt

数据库基本知识数据采集与分析 安徽省审计厅信息办 现场审计实施系统数据采集与分析 数据采集概述 通过多年多的努力探索和实践 逐步总结出一套切实可行且方便实用的数据采集流程 被审单位财务系统数据库 导出 采集 导入本地审计数据库系统中 进行本地数据处理 转换 进行AO系统导入操作 还原被审计单位财务数据 分析 几点说明 在以上流程中 其关键在对被审计单位的数据导出和本地数据的处理过程最容易出现问题 如果处理不当 可能导致利用AO现场实施系统进行计算机审计的方式以失败而告终 处理原则 在导出被审计单位数据过程中 审计人员可灵活运用ODBC或者直接利用SQL2003 SQL2008自带的数据库备份功能直接将所需数据导出 在本地数据处理过程中 将数据利用数据库的一些技术转换为符合AO系统条件的数据 一 数据库技术 当今数据库技术已成为计算机应用的核心技术 可以认为当今任何计算机信息系统和计算机应用系统都与数据库的应用分不开 数据库已成为其核心和基础 而数据库技术则使人们对信息的利用突破了时间和空间的限制 而成为社会信息化的重要支撑 大型数据库ORACLE SYBASE MSSQLServer INFOMIX IBMDB2等 中小型数据库PowerBuild MSAccess VisualFoxpro Paradox 各种数据库数据的一般采集方法 由于数据库系统的种类繁多 要想具体的按照某一条固有原则进行数据的提取是不可行的 需针对每种数据采用不同的方法进行采集 以下方法以各类常见数据库系统与SQL2008的采集规则 SYSBASE SQL2008 在被审计单位数据库系统上建立ODBC数据源 提取SYSBASE数据转为MDB ACCESS 数据库数据 利用移动介质MDB数据拷贝到审计人员机器中 利用SQL的导入数据库功能导入到审计人员机器中 ORACLE SQL2008 ORACLE数据库系统一般常见与小型机数据库系统中 由于被其数据库系统是安装与UNIX LINUX系统中 无法直接在数据库系统中利用ODBC直接采集 一般常用方法无法使用 以下将简单介绍 ORACLE数据库系统一般常见于大型企业中 采用网络传送数据 利用这一特点 采用网络采集方法 在远端机器中建立ODBC数据源 利用SQL2000自带的DTS工具采用网络远程采集数据库的方法 将数据采集到远端数据库系统中 再利用移动设备将数据库导入被审计人员数据库系统中 SQL2000 SQL2008 以这种方法采集数据为最简单的一种数据采集 以下将介绍一种简洁的方法 利用数据库系统自带数据库备份功能将数据库系统直接本份于本地 利用移动设备将数据库文件拷贝到被审计人员机器中 利用MSSQL的数据库还原功能 将该数据库文件还原 完成导入功能 字段定义 数据类型短整型 99999 999999整数型 9999999999 99999999999日期型 MM DD YY字符型 有1 254字符组成逻辑型 真 假浮点型 flot19位有效数字 SQL数据类型 数值型Smallint Int Bigint 32768 32767 2147483648 2147483647字符型Char n varchar n Text8000 2147483647日期型SmalldatetimeDatetime精确到天 精确到百分之秒2001 11 20 建立表结构 删除表 建立表结构createTABLE表名 姓名char 8 notnull 工作单位char 24 notnull 年龄intnotnull 职务char 10 notnull 编号char 3 notnull primarykey 编号 建立 表名 表 且建立姓名8字节 年龄数值性 编号3字节 编号3字节 关键字为编号 删除表DropTABLEdroptable表名 将 表名 表删除 数据输入 Insertintovalues 将字段1值 字段2值 插入到表中 insertinto表名values 张三 审计厅 24 办事员 001 向表 表名 中插入一行 其各个字段为 张三 审计厅 24 办事员 001 insertinto表名values 李四 审计厅 29 办事员 002 向表 表名 中插入一行 其各个字段为 李四 审计厅 29 办事员 002 SQL语句 Select语句语法结构 SELECT ALL DISTINCT TOP FROM 表名2 WHERE GROUPBY HAVING ORDERBY ASC DESC 注 GROUPBY分组 ORDERBY排序HAVING所有条件 ASC升序 DESC降序 检查表中内容 简单查询 表的复制 selectfromwhere 从表中选择符合条件的记录 并将符合的记录显示 select表名 编号from表名 从表名表中 选择所有记录 且只显示记录中表名和编号字段 select intotable1fromtablewhere 从TABLE表中选择符合条件表达式的记录 并且将这些记录插入到TABLE1中 记录修改 删除表记录 记录修改Updateset 数值update表名set表号 002 更新 表名 表 并将所有表号字段都改为002update表名set表号 002 where 更新 表名 表 并将符合以上where的所有记录的表号字段全部改为002记录删除deletefromwheredeletefrom表名where表号 002 删除 表名 表中 表号字段等于002的所有记录 关系运算符 等于大于 大于等于 不等于 逻辑运算符 or或and且Not非 特殊运算符 between定义一个区间范围isnull测试属性是否为空Like字符串匹配操作符In检查属性是否属于一组值之中exists检查属性是否有值 特殊运算符 select from表名where年龄between20and30 从 表名 表中选择年龄在20 30之间的所有记录 select from表名where编号ISNULL 从 表名 表中选择编号字段为NULL的所有记录 表示零或者多个字符 表示任一个字符select from表名where表名like 表 从 表名 表中选择表名字段中包涵以 表 字开头的所有记录 select from表名where年龄in 24 30 从 表名 表中选择年龄字段值为24或者30的所有记录 select from表名where编号exists 从 表名 表中选择编号是否有值的所有记录 摘要包含 费 Where摘要LIKE 费 摘要以 费 结束Where摘要LIKE 费 摘要以 费 开始Where摘要LIKE 费 摘要第二个字是 费 Where摘要LIKE 费 摘要倒数第三个字是 费 Where摘要LIKE 费 摘要为空WhereTrim 摘要 or摘要isnull 复杂的数据管理命令 alterbablemodify alterbable表名modify 编号char 5 将 表名 表中的编号字段修改为5字符的字段 alterbableadd alterbable表名add numberchar 3 在 表名 表中增加number字段并修改为3字符的字段 排序 消除重复 排序select fromorderby列名 desc select from表名orderby编号从 表名 表中选择所有记录 并按编号顺序 默认升序 进行排列 消除重复selectdistinctfromselectdistinct编号from表名将 表名 表中的编号字段中的记录消除重复记录 sql函数 Count计数Min寻找最小值Max寻找最大值Avg求平均值Sum求和 连接数据库的表 Select from表1 表2where 例 select学生表 姓名 成绩表 成绩 成绩表 课程名from学生表 成绩表where学生表 学生号 成绩表 学生号将学生表和成绩表按学生号连接 并查询学生每门课程的成绩 AO中使用SQL语句简例 使用前先打开AO 从菜单 选择电子帐簿 把每条语句写到SQL编辑器中 然后点击 我们就可以看到执行结果 基本语法SELECTselect list INTO新表名 FROM表一 表二 WHERE条件表达式 GROUPBY列名 HAVING条件表达式 ORDERBY列名 ASC DESC 其中 中的内容为可选项 根据使用者需要而选择 SELECT和FROM为必选项 1 不带任何选项的最基本SQL语句 select from凭证库 选择凭证库中所有信息 表示所有列select科目编码 科目名称 摘要 凭证日期 借方金额 贷方金额from凭证库 从凭证库中选择以上几列 selectdistinct科目编码from凭证库 如果不要distinct 表示从凭证库中选择科目编码 加上distinct表示去掉那些重复科目编码 使每个科目编码都唯一 Selectsum 借方金额 sum 贷方金额 from凭证库 求凭证库中所有凭证的借方发生额总和和贷方发生额总和 此语句可以查看借贷发生额是否平衡 Selectsum 借方金额 as借方发生总额 sum 贷方金额 as贷方发生总额from凭证库 注意和上个语句之间的差别 以as含义 2 带有条件 where 的SQL语句 select from凭证库where科目编码 101 在凭证库中选择与现金有关的凭证 有的科目设置中现金为 1001 可能要做变化 Select科目编码 科目名称 摘要 凭证日期 借方金额 贷方金额from凭证库where摘要like 招待费 通过摘要选择与招待费有关的凭证 不显示所有列只显示以上几列Selectsum 借方金额 as现金借方发生总额 sum 贷方金额 as现金贷方发生总额from凭证库where科目编码like 101 and凭证日期between 2004 07 01 and 2004 12 31 在凭证库中统计7到12月份现金的借贷发生总额 And表示并列条件 两个条件都要满足 select from凭证库where科目编码like 101 and贷方金额 5000and摘要notlike 工资 and摘要notlike 奖金 and摘要notlike 稿费 and摘要notlike 补 and摘要notlike 津贴 and摘要notlike 劳保 and摘要notlike 福利 and摘要notlike 出差 and摘要notlike 差旅费 筛选出 贷 现金 且摘要中不含 工资 或 奖金 或 稿费 或 补 或 津贴 或 劳保 或 福利 或 出差 或 差旅费 字段 且现金贷方发生额大于结算起点 5000元 的所有记录 此语句目的为审查除发工资 奖金或借差旅费等情况之外 使用现金是否超范围 超限额 以5000做为界点 使用者在使用是可能要对其做具体修改 如果现在科目不为101 可能对编码也要做修改 3 带有分组的 groupby 语句 GROUPBY主要是对表中的信息细化分组 对属于同组信息进行统计 一般与函数一起使用 select科目编码 sum 借方金额 sum 贷方金额 from凭证库where科目编码like 113 groupby科目编码 在凭证库中统计其他应受款 113 中各末级科目的年借方发生额和贷方发生额 selectleft 科目编码 5 sum 借方金额 sum 贷方金额 from凭证库where科目编码like 113 groupbyleft 科目编码 5 在凭证库中统计其他应受款 113 中二级科目 二级科目长度为5 的年借方发生总额和贷方发生总额 在本例中 可通过调整长度来控制所求的科目级别 如果是3就是求其他应受款所有借贷方总额 如果是7就是所有的三级科目借贷方总额 当然其具体数值大小根据实际编码长度为准 selectleft 科目编码 5 as二级科目编码 month 凭证日期 as月份 sum 借方金额 as月借方额 sum 贷方金额 as月贷方总额from凭证库where科目编码like 113 groupbyleft 科目编码 6 month 凭证日期 上例是对年度求借贷发生总额 本语句是求出其他应收款 113 中二级科目的每个月的借贷发生总额 同样改变数字大小可以改变所求的科目级别 本组例子是对其他应收款 113 为例 使用时也可以根据需要对其他科目统计借贷发生年总额或月总额 主要应用与报表或总帐的核对 4 多个表关联 selecta from凭证库asainnerjoin凭证库asbona 凭证日期 b 凭证日期anda 凭证号 b 凭证号whereb 科目编码like