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

1、安徽省审计厅信息办安徽省审计厅信息办 通过多年多的努力探索和实践，逐步总结通过多年多的努力探索和实践，逐步总结出一套切实可行且方便实用的数据采集流程。出一套切实可行且方便实用的数据采集流程。被审单位财务系统数据库被审单位财务系统数据库导出导出（采集）（采集）导入本地审计数据库系统中导入本地审计数据库系统中进行本地数据进行本地数据处理处理（转换）（转换）进行进行AO系统导入操作系统导入操作还原被还原被审计单位财务数据审计单位财务数据（分析）（分析）。在以上流程中，其关键在对在以上流程中，其关键在对被审计单位的被审计单位的数据导出数据导出和和本地数据的处理过程本地数据的处理过程最容易出现问最容易出

2、现问题。如果处理不当题。如果处理不当,可能导致利用可能导致利用AO现场实施现场实施系统进行计算机审计的方式以失败而告终。系统进行计算机审计的方式以失败而告终。处理原则：处理原则：在导出被审计单位数据过程中，审计人员在导出被审计单位数据过程中，审计人员可灵活运用可灵活运用ODBC或者直接利用或者直接利用SQL2003-SQL2008自带的数据库备份功能直接将所需数自带的数据库备份功能直接将所需数据导出。据导出。在本地数据处理过程中，将数据利用数据在本地数据处理过程中，将数据利用数据库的一些技术转换为符合库的一些技术转换为符合AO系统条件的数据。系统条件的数据。当今数据库技术已成为计算机应用的核心

3、当今数据库技术已成为计算机应用的核心技术，可以认为当今任何计算机信息系统和计技术，可以认为当今任何计算机信息系统和计算机应用系统都与数据库的应用分不开，数据算机应用系统都与数据库的应用分不开，数据库已成为其核心和基础。而数据库技术则使人库已成为其核心和基础。而数据库技术则使人们对信息的利用突破了时间和空间的限制，而们对信息的利用突破了时间和空间的限制，而成为社会信息化的重要支撑。成为社会信息化的重要支撑。大型数据库大型数据库ORACLE、SYBASE、MS SQL Server、INFOMIX、IBM DB2等，中小等，中小型数据库型数据库PowerBuild、MS Access、Visual

4、 Foxpro、Paradox 由于数据库系统的种类繁多，要想具体的由于数据库系统的种类繁多，要想具体的按照某一条固有原则进行数据的提取是不可行按照某一条固有原则进行数据的提取是不可行的，需针对每种数据采用不同的方法进行采集。的，需针对每种数据采用不同的方法进行采集。以下方法以各类常见数据库系统与以下方法以各类常见数据库系统与 SQL2008的采集规则。的采集规则。在被审计单位数据库系统上建立在被审计单位数据库系统上建立ODBC数数据源据源-提取提取SYSBASE数据转为数据转为MDB（ACCESS）数据库数据）数据库数据-利用移动介质利用移动介质MDB数据拷贝到审计人员机器中数据拷贝到审计人

5、员机器中-利用利用SQL的导入数据库功能导入到审计人员机器中。的导入数据库功能导入到审计人员机器中。ORACLE数据库系统一般常见与小型机数据库系数据库系统一般常见与小型机数据库系统中，由于被其数据库系统是安装与统中，由于被其数据库系统是安装与UNIX、LINUX系系统中，无法直接在数据库系统中利用统中，无法直接在数据库系统中利用ODBC直接采集。直接采集。一般常用方法无法使用，以下将简单介绍。一般常用方法无法使用，以下将简单介绍。ORACLE数据库系统一般常见于大型企业中，采数据库系统一般常见于大型企业中，采用网络传送数据，利用这一特点，采用网络采集方法。用网络传送数据，利用这一特点，采用网

6、络采集方法。在远端机器中建立在远端机器中建立ODBC数据源，利用数据源，利用SQL2000自带的自带的 DTS工具采用网络远程采集数据库的方法。工具采用网络远程采集数据库的方法。将数据采集到远端数据库系统中。再利用移动设将数据采集到远端数据库系统中。再利用移动设备将数据库导入被审计人员数据库系统中。备将数据库导入被审计人员数据库系统中。以这种方法采集数据为最简单的一种数据以这种方法采集数据为最简单的一种数据采集。以下将介绍一种简洁的方法。采集。以下将介绍一种简洁的方法。利用数据库系统自带数据库备份功能将数利用数据库系统自带数据库备份功能将数据库系统直接本份于本地据库系统直接本份于本地-利用移动

7、设备将利用移动设备将数据库文件拷贝到被审计人员机器中数据库文件拷贝到被审计人员机器中-利用利用MS SQL的数据库还原功能，将该数据库文件的数据库还原功能，将该数据库文件还原。完成导入功能。还原。完成导入功能。数据类型数据类型短整型：短整型：-99999999999整数型：整数型：-999999999999999999999日期型：日期型：MM/DD/YY字符型：有字符型：有1-254字符组成字符组成逻辑型：真逻辑型：真、假假浮点型：浮点型：flot 19位有效数字位有效数字 数值型数值型Small int,Int,Bigint-3276832767，-21474836482147483647

8、 字符型字符型Char(n),varchar(n),Text8000,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字节，年龄数值性，

9、编号字节，年龄数值性，编号3字节，编号字节，编号3字节，关键字为编号。字节，关键字为编号。删除表删除表 Drop TABLE drop table 表名表名 /将将“表名表名”表删除。表删除。Insert into values(,”.)/将字段将字段1值值,字段字段2值值插入到表中。插入到表中。insert into 表名表名 values(张三张三,审计厅审计厅,24,办事员办事员,001);/向表向表表名表名中插入一行，其各个字段为中插入一行，其各个字段为张三张三,审审计厅计厅,24,办事员办事员，001insert into 表名 values(李四,审计厅,29,办事员,002);/

10、向表向表表名表名中插入一行，其各个字段为中插入一行，其各个字段为李四李四,审审计厅计厅,29,办事员办事员，002SELECT 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 表名表名 /从表名表中，选择

11、所有记录，且只显示记录中表名和编从表名表中，选择所有记录，且只显示记录中表名和编号字段。号字段。select*into table1 from table where /从从TABLE表中选择符合条件表达式的记录。并且将这表中选择符合条件表达式的记录。并且将这些记录插入到些记录插入到TABLE1中。中。记录修改记录修改Update set=数值数值 update 表名表名 set 表号表号=002 /更新更新“表名表名”表，并将所有表号字段都改为表，并将所有表号字段都改为002 update 表名表名 set 表号表号=002 where /更新更新“表名表名”表，并将符合以上表，并将符合以上

12、where的的 所有记录的表号字段全部改为所有记录的表号字段全部改为002记录删除记录删除delete from where delete from 表名表名 where 表号表号=002 /删除删除“表名表名”表中，表号字段等于表中，表号字段等于002的所有记录。的所有记录。=等于等于 小于小于 大于大于=大于等于大于等于/!不等于不等于or 或或and 且且Not 非非between 定义一个区间范围定义一个区间范围is null 测试属性是否为空测试属性是否为空Like 字符串匹配操作符字符串匹配操作符In 检查属性是否属于一组值之中检查属性是否属于一组值之中exists 检查属性是否有

13、值检查属性是否有值select*from 表名表名 where 年龄年龄 between 20 and 30 /从从“表名表名”表中选择年龄在表中选择年龄在20-30之间的所有记录。之间的所有记录。select*from 表名表名 where 编号编号 IS NULL /从从“表名表名”表中选择编号字段为表中选择编号字段为NULL的所有记录。的所有记录。%表示零或者多个字符表示零或者多个字符 _表示任一个字符表示任一个字符select*from 表名表名 where 表名表名 like 表表%/从从“表名表名”表中选择表名字段中包涵以表中选择表名字段中包涵以“表表”字开头的所有记录。字开头的所

14、有记录。select*from 表名表名 where 年龄年龄 in(24,30)/从从“表名表名”表中选择年龄字段值为表中选择年龄字段值为24或者或者30的所有记录。的所有记录。select*from 表名表名 where 编号编号 exists /从从“表名表名”表中选择编号是否有值的所有记录。表中选择编号是否有值的所有记录。摘要包含摘要包含“费费”Where 摘要摘要 LIKE%费费%摘要以摘要以“费费”结束结束Where 摘要摘要 LIKE%费费 摘要以摘要以“费费”开始开始Where 摘要摘要 LIKE 费费%摘要第二个字是摘要第二个字是“费费”Where 摘要摘要 LIKE _费费

15、%摘要倒数第三个字是摘要倒数第三个字是“费费”Where 摘要摘要 LIKE%费费_ _ 摘要为空摘要为空 Where Trim(摘要摘要)=or 摘要摘要 is nullalter bable modify()alter bable 表名表名 modify(编号编号 char(5)将将“表名表名”表中的编号字段修改为表中的编号字段修改为 5字符的字字符的字段。段。alter bable add()alter bable 表名表名 add(number char(3)在在“表名表名”表中增加表中增加number字段并修改为字段并修改为3字符字符 的字段的字段排序排序select*from or

16、der by 列名列名 desc select*from 表名表名 order by 编号编号 从从“表名表名”表中选择所有记录，并按编号顺序表中选择所有记录，并按编号顺序(默认升序默认升序)进行排列。进行排列。消除重复消除重复select distinct from select distinct 编号编号 from 表名表名 将将“表名表名”表中的编号字段中的记录消除重复表中的编号字段中的记录消除重复记录。记录。Count 计数计数 Min 寻找最小值寻找最小值 Max 寻找最大值寻找最大值 Avg 求平均值求平均值 Sum 求和求和 Select,from 表表1,表表2 where=例

17、例：select 学生表学生表.姓名姓名,成绩表成绩表.成绩成绩,成绩表成绩表.课程名课程名 from 学生表学生表,成绩表成绩表 where 学生表学生表.学学生号生号=成绩表成绩表.学生号学生号 将学生表和成绩表按学生号连接，并查询将学生表和成绩表按学生号连接，并查询学生每门课程的成绩。学生每门课程的成绩。使用前先打开使用前先打开AOAO，从，从 菜单菜单 ,选择电子帐簿选择电子帐簿 。把每条语句写到把每条语句写到SQLSQL编辑器中，然后点击编辑器中，然后点击 ，我们就可以看到执行结果。，我们就可以看到执行结果。基本语法基本语法 SELECT select_listSELECT sele

18、ct_list INTO INTO 新表名新表名 FROM FROM 表一表一 ,表二表二.WHERE WHERE 条件表达式条件表达式 GROUP BY GROUP BY 列名列名 HAVING HAVING 条件表达式条件表达式 ORDER BY ORDER BY 列名列名 ASC|DESC ASC|DESC 其中其中 中的内容为可选项，根据使用者需要而选择，中的内容为可选项，根据使用者需要而选择，SELECT SELECT 和和FROMFROM为必选项。为必选项。select *from 凭证库凭证库 /选择凭证库中所有信息，选择凭证库中所有信息，*表示所有列表示所有列 select 科

19、目编码科目编码,科目名称科目名称,摘要摘要,凭证日期凭证日期,借方金额借方金额,贷方贷方金额金额 from 凭证库凭证库 /从凭证库中选择以上几列。从凭证库中选择以上几列。select distinct 科目编码科目编码 from 凭证库凭证库 /如果不要如果不要distinct，表示从凭证库中选择科目编码，加上，表示从凭证库中选择科目编码，加上distinct表示表示去掉那些重复科目编码，使每个科目编码都唯一。去掉那些重复科目编码，使每个科目编码都唯一。Select sum(借方金额借方金额),sum(贷方金额贷方金额)from 凭证库凭证库 /求凭证库中所有凭证的借方发生额总和和贷方发生额

20、总和，此语句可求凭证库中所有凭证的借方发生额总和和贷方发生额总和，此语句可以查看借贷发生额是否平衡。以查看借贷发生额是否平衡。Select sum(借方金额借方金额)as 借方发生总额借方发生总额,sum(贷方金额贷方金额)as 贷方发生总额贷方发生总额 from 凭证库凭证库 /注意和上个语句之间的差别，以注意和上个语句之间的差别，以as含义。含义。select*from 凭证库凭证库 where 科目编码科目编码=101 /在凭证库中选择与现金有关的凭证，有的科目设置中现金在凭证库中选择与现金有关的凭证，有的科目设置中现金为为1001,可能要做变化。可能要做变化。Select科目编码科目编

21、码,科目名称科目名称,摘要摘要,凭证日期凭证日期,借方金额借方金额,贷方金额贷方金额 from 凭证库凭证库 where 摘要摘要 like%招待费招待费%/通过摘要选择与招待费有关的凭证，不显示所有列只显示以上几列通过摘要选择与招待费有关的凭证，不显示所有列只显示以上几列 Select sum(借方金额借方金额)as 现金借方发生总额现金借方发生总额,sum(贷方金额贷方金额)as 现金贷方发生总额现金贷方发生总额 from 凭证库凭证库 where 科目编码科目编码 like 101%and 凭证日期凭证日期 between#2004-07-01#and#2004-12-31#/在凭证库中

22、统计在凭证库中统计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 摘要摘要

23、 not like%出差出差%and 摘要摘要 not like%差旅费差旅费%/筛选出筛选出“贷：现金贷：现金”，且摘要中不含，且摘要中不含“工资工资”或或“奖金奖金”或或“稿稿费费”或或“补补”或或“津贴津贴”或或“劳保劳保”或或“福利福利”或或“出差出差”或或“差旅差旅费费”字段，且现金贷方发生额大于结算起点（字段，且现金贷方发生额大于结算起点（5000元）的所有记录元）的所有记录,此此语句目的为审查除发工资、奖金或借差旅费等情况之外，使用现金是语句目的为审查除发工资、奖金或借差旅费等情况之外，使用现金是否超范围、超限额。以否超范围、超限额。以5000做为界点，使用者在使用是可能要对其做

24、做为界点，使用者在使用是可能要对其做具体修改。如果现在科目不为具体修改。如果现在科目不为101，可能对编码也要做修改。，可能对编码也要做修改。GROUP BY主要是对表中的信息细化分组，对属于同组信息主要是对表中的信息细化分组，对属于同组信息进行统计，一般与函数一起使用。进行统计，一般与函数一起使用。select 科目编码科目编码,sum(借方金额借方金额),sum(贷方金额贷方金额)from 凭证凭证库库 where 科目编码科目编码 like 113%group by 科目编码科目编码 /在凭证库中统计其他应受款（在凭证库中统计其他应受款（113）中各末级科目的年借方发生额和）中各末级科目

25、的年借方发生额和贷方发生额。贷方发生额。select left(科目编码科目编码,5),sum(借方金额借方金额),sum(贷方金额贷方金额)from 凭证库凭证库 where 科目编码科目编码 like 113%group by left(科目编科目编码码,5)/在凭证库中统计其他应受款（在凭证库中统计其他应受款（113）中二级科目（二级科目长度为）中二级科目（二级科目长度为5）的年借方发生总额和贷方发生总额。在本例中，可通过调整长度来控制所的年借方发生总额和贷方发生总额。在本例中，可通过调整长度来控制所求的科目级别，如果是求的科目级别，如果是3就是求其他应受款所有借贷方总额，如果是就是求其

26、他应受款所有借贷方总额，如果是7就是就是所有的三级科目借贷方总额。当然其具体数值大小根据实际编码长度为准。所有的三级科目借贷方总额。当然其具体数值大小根据实际编码长度为准。select left(科目编码科目编码,5)as 二级科目编码二级科目编码,month(凭凭证日期证日期)as 月份月份,sum(借方金额借方金额)as 月借方月借方额额,sum(贷方金额贷方金额)as 月贷方总额月贷方总额 from 凭证库凭证库 where 科目编码科目编码 like 113%group by left(科目编科目编码码,6),month(凭证日期凭证日期)/上例是对年度求借贷发生总额，本语句是求出其他

27、应收款上例是对年度求借贷发生总额，本语句是求出其他应收款（113）中二级科目的每个月的借贷发生总额。同样改变数字）中二级科目的每个月的借贷发生总额。同样改变数字大小可以改变所求的科目级别。大小可以改变所求的科目级别。本组例子是对其他应收款（本组例子是对其他应收款（113）为例，使用时也可）为例，使用时也可以根据需要对其他科目统计借贷发生年总额或月总以根据需要对其他科目统计借贷发生年总额或月总额。主要应用与报表或总帐的核对。额。主要应用与报表或总帐的核对。select a.*from 凭证库凭证库 as a inner join 凭证库凭证库 as b on a.凭证日期凭证日期=b.凭证日期凭

28、证日期 and a.凭证号凭证号=b.凭证号凭证号 where b.科科目编码目编码 like 101%and(b.借方金额借方金额5000 or b.贷方金额贷方金额5000)/通过两张凭证库关联检索出现金收支大于通过两张凭证库关联检索出现金收支大于5000元的凭证数据。元的凭证数据。select a.*from 凭证库凭证库 as a,凭证库凭证库 as b,凭证库凭证库 as c where a.凭证日期凭证日期=b.凭证日期凭证日期 and a.凭证号凭证号=b.凭证号凭证号 and c.凭证日期凭证日期=b.凭证日期凭证日期 and c.凭证号凭证号=b.凭证号凭证号 and b.科目编码科目编码 like 503%and b.借方金额借方金额0 and not(c.科科目编码目编码 like 102%)/通过凭证库三次关联，检索出所有借方拨出专款，贷方非通过凭证库三次关联，检索出所有借方拨出专款，贷方非银行存银行存款款的凭证数据。审计有无虚列专款支出，转移资金的问题的凭证数据。审计有无虚列专款支出，转移资金的问题谢谢大家