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

数据库基本知识 数据采集与分析,安徽省审计厅信息办,现场审计实施系统数据采集与分析,数据采集概述,通过多年多的努力探索和实践，逐步总结出一套切实可行且方便实用的数据采集流程。 被审单位财务系统数据库导出（采集）导入本地审计数据库系统中进行本地数据处理（转换）进行AO系统导入操作还原被审计单位财务数据（分析）。,几点说明,在以上流程中，其关键在对被审计单位的数据导出和本地数据的处理过程最容易出现问题。如果处理不当, 可能导致利用AO现场实施系统进行计算机审计的方式以失败而告终。 处理原则： 在导出被审计单位数据过程中，审计人员可灵活运用ODBC或者直接利用SQL2003-SQL2008自带的数据库备份功能直接将所需数据导出。 在本地数据处理过程中，将数据利用数据库的一些技术转换为符合AO系统条件的数据。,一、数据库技术,当今数据库技术已成为计算机应用的核心技术，可以认为当今任何计算机信息系统和计算机应用系统都与数据库的应用分不开，数据库已成为其核心和基础。而数据库技术则使人们对信息的利用突破了时间和空间的限制，而成为社会信息化的重要支撑。 大型数据库ORACLE、SYBASE、MS SQL Server、INFOMIX、IBM DB2等，中小型数据库PowerBuild、MS Access、Visual Foxpro、Paradox,各种数据库数据的一般采集方法,由于数据库系统的种类繁多，要想具体的按照某一条固有原则进行数据的提取是不可行的，需针对每种数据采用不同的方法进行采集。 以下方法以各类常见数据库系统与 SQL2008的采集规则。,SYSBASE-SQL2008,在被审计单位数据库系统上建立ODBC数据源-提取SYSBASE数据转为MDB（ACCESS）数据库数据-利用移动介质MDB数据拷贝到审计人员机器中-利用SQL的导入数据库功能导入到审计人员机器中。,ORACLE-SQL2008,ORACLE数据库系统一般常见与小型机数据库系统中，由于被其数据库系统是安装与UNIX、LINUX系统中，无法直接在数据库系统中利用ODBC直接采集。 一般常用方法无法使用，以下将简单介绍。 ORACLE数据库系统一般常见于大型企业中，采用网络传送数据，利用这一特点，采用网络采集方法。 在远端机器中建立ODBC数据源，利用SQL2000自带的 DTS工具采用网络远程采集数据库的方法。 将数据采集到远端数据库系统中。再利用移动设备将数据库导入被审计人员数据库系统中。,SQL2000-SQL2008,以这种方法采集数据为最简单的一种数据采集。以下将介绍一种简洁的方法。 利用数据库系统自带数据库备份功能将数据库系统直接本份于本地-利用移动设备将数据库文件拷贝到被审计人员机器中-利用MS SQL的数据库还原功能，将该数据库文件还原。完成导入功能。,字段定义,数据类型 短整型：-99999999999 整数型：-999999999999999999999 日期型：MM /DD /YY 字符型：有1-254字符组成 逻辑型：真 、 假 浮点型：flot 19位有效数字,SQL数据类型,数值型 Small int ,Int,Bigint -3276832767， -21474836482147483647 字符型 Char (n),varchar(n),Text 8000,2147483647 日期型 Smalldatetime Datetime 精确到天 ，精确到百分之秒 2001-11-20,建立表结构、删除表,建立表结构 create TABLE 表名 ( 姓名 char(8) not null, 工作单位 char(24) not null,年龄 int not null, 职务 char(10), not null,编号 char(3) not null, primary key (编号); /建立“表名”表，且建立姓名8字节，年龄数值性，编号3字节，编号3字节，关键字为编号。 删除表 Drop TABLE drop table 表名 /将“表名”表删除。,数据输入,Insert into values(,”) / 将字段1值, 字段2值插入到表中。 insert into 表名 values(张三,审计厅,24,办事员,001); /向表表名中插入一行，其各个字段为张三,审计厅,24,办事员，001 insert into 表名 values(李四,审计厅,29,办事员,002); /向表表名中插入一行，其各个字段为李四,审计厅,29,办事员，002,SQL语句-Select语句语法结构,SELECT ALL|DISTINCT|TOP ， FROM ，表名2 WHERE GROUP BY HAVING ORDER BY ASC|DESC 注： GROUP BY分组、 ORDER BY排序 HAVING所有条件、ASC升序、DESC降序,检查表中内容/简单查询、表的复制,select from where /从表中选择符合条件的记录，并将符合的记录显示。 select 表名,编号 from 表名 /从表名表中，选择所有记录，且只显示记录中表名和编号字段。 select * into table1 from table where /从TABLE表中选择符合条件表达式的记录。并且将这些记录插入到TABLE1中。,记录修改、删除表记录,记录修改 Update set =数值 update 表名 set 表号=002 /更新“表名”表，并将所有表号字段都改为002 update 表名 set 表号=002 where /更新“表名”表，并将符合以上where的 所有记录的表号字段全部改为002 记录删除 delete from where delete from 表名 where 表号=002 /删除“表名”表中，表号字段等于002的所有记录。,关系运算符,= 等于 大于 = 大于等于 /! 不等于,逻辑运算符,or 或 and 且 Not 非,特殊运算符,between 定义一个区间范围 is null 测试属性是否为空 Like 字符串匹配操作符 In 检查属性是否属于一组值之中 exists 检查属性是否有值,特殊运算符,select * from 表名 where 年龄 between 20 and 30 /从“表名”表中选择年龄在20-30之间的所有记录。 select * from 表名 where 编号 IS NULL /从“表名”表中选择编号字段为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 %费_ _ 摘要为空 Where Trim(摘要)= or 摘要 is null,复杂的数据管理命令,alter bable modify () alter bable 表名 modify (编号 char(5) 将“表名”表中的编号字段修改为 5字符的字段。 alter bable add () alter bable 表名 add (number char(3) 在“表名”表中增加number字段并修改为3字符 的字段,排序、消除重复,排序 select * from order by 列名 desc select * from 表名 order by 编号 从“表名”表中选择所有记录，并按编号顺序(默认升序)进行排列。 消除重复 select distinct from select distinct 编号 from 表名 将“表名”表中的编号字段中的记录消除重复记录。,sql函数,Count 计数 Min 寻找最小值 Max 寻找最大值 Avg 求平均值 Sum 求和,连接数据库的表,Select , from 表1,表2 where = 例：select 学生表.姓名,成绩表.成绩,成绩表.课程名 from 学生表,成绩表 where 学生表.学生号=成绩表.学生号 将学生表和成绩表按学生号连接，并查询学生每门课程的成绩。,AO中使用SQL语句简例,使用前先打开AO，从菜单,选择电子帐簿。把每条语句写到SQL编辑器中，然后点击，我们就可以看到执行结果。 基本语法 SELECT select_list INTO 新表名 FROM 表一 ,表二. WHERE 条件表达式 GROUP BY 列名 HAVING 条件表达式 ORDER BY 列名 ASC | DESC 其中中的内容为可选项，根据使用者需要而选择， SELECT 和FROM为必选项。,1、不带任何选项的最基本SQL语句,select * from 凭证库 /选择凭证库中所有信息，*表示所有列 select 科目编码 ,科目名称 ,摘要 , 凭证日期 ,借方金额, 贷方金额 from 凭证库 /从凭证库中选择以上几列。 select distinct 科目编码 from 凭证库 /如果不要distinct，表示从凭证库中选择科目编码，加上distinct表示去掉那些重复科目编码，使每个科目编码都唯一。 Select sum(借方金额), sum(贷方金额) from 凭证库 /求凭证库中所有凭证的借方发生额总和和贷方发生额总和，此语句可以查看借贷发生额是否平衡。 Select sum(借方金额) as 借方发生总额 , sum(贷方金额) as 贷方发生总额 from 凭证库 /注意和上个语句之间的差别，以as含义。,2、带有条件（where）的SQL语句,select * from 凭证库 where 科目编码=101 /在凭证库中选择与现金有关的凭证，有的科目设置中现金为1001,可能要做变化。 Select科目编码 ,科目名称 ,摘要 , 凭证日期 ,借方金额, 贷方金额 from 凭证库 where 摘要 like %招待费% /通过摘要选择与招待费有关的凭证，不显示所有列只显示以上几列 Select sum(借方金额) 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 贷方金额 5000 and 摘要 not like %工资% and 摘要 not like %奖金% and 摘要 not like %稿费% and 摘要 not like %补% and 摘要 not like %津贴% and 摘要 not like %劳保% and 摘要 not like %福利% and 摘要 not like %出差% and 摘要 not like %差旅费% /筛选出“贷：现金”，且摘要中不含“工资”或“奖金”或“稿费”或“补”或“津贴”或“劳保”或“福利”或“出差”或“差旅费”字段，且现金贷方发生额大于结算起点（5000元）的所有记录,此语句目的为审查除发工资、奖金或借差旅费等情况之外，使用现金是否超范围、超限额。以5000做为界点，使用者在使用是可能要对其做具体修改。如果现在科目不为101，可能对编码也要做修改。,3、带有分组的（group by）语句,GROUP BY主要是对表中的信息细化分组，对属于同组信息进行统计，一般与函数一起使用。 select 科目编码 ,sum(借方金额),sum(贷方金额) from 凭证库 where 科目编码 like 113% group by 科目编码 /在凭证库中统计其他应受款（113）中各末级科目的年借方发生额和贷方发生额。 select left(科目编码,5),sum(借方金额),sum(贷方金额) from 凭证库 where 科目编码 like 113% group by left(科目编码,5) /在凭证库中统计其他应受款（113）中二级科目（二级科目长度为5）的年借方发生总额和贷方发生总额。在本例中，可通过调整长度来控制所求的科目级别，如果是3就是求其他应受款所有借贷方总额，如果是7就是所有的三级科目借贷方总额。当然其具体数值大小根据实际编码长度为准。,select left(科目编码,5) as 二级科目编码,month(凭证日期) as 月份,sum(借方金额) as 月借方额 ,sum(贷方金额) as 月贷方总额