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

数据库基本知识

数据采集与分析安徽省审计厅信息办数据库基本知识

数据采集与分析安徽省审计厅信息办1现场审计实施系统数据采集与分析现场审计实施系统数据采集与分析2数据采集概述

通过多年多的努力探索和实践，逐步总结出一套切实可行且方便实用的数据采集流程。被审单位财务系统数据库导出（采集）导入本地审计数据库系统中进行本地数据处理（转换）进行AO系统导入操作还原被审计单位财务数据（分析）。数据采集概述3几点说明

在以上流程中，其关键在对被审计单位的数据导出和本地数据的处理过程最容易出现问题。如果处理不当,可能导致利用AO现场实施系统进行计算机审计的方式以失败而告终。

处理原则：在导出被审计单位数据过程中，审计人员可灵活运用ODBC或者直接利用SQL2003-SQL2008自带的数据库备份功能直接将所需数据导出。在本地数据处理过程中，将数据利用数据库的一些技术转换为符合AO系统条件的数据。几点说明在以上流程中，其关键在对被审计单4一、数据库技术

当今数据库技术已成为计算机应用的核心技术，可以认为当今任何计算机信息系统和计算机应用系统都与数据库的应用分不开，数据库已成为其核心和基础。而数据库技术则使人们对信息的利用突破了时间和空间的限制，而成为社会信息化的重要支撑。

大型数据库ORACLE、SYBASE、MSSQLServer、INFOMIX、IBMDB2等，中小型数据库PowerBuild、MSAccess、VisualFoxpro、Paradox一、数据库技术当今数据库技术已成为计算5各种数据库数据的一般采集方法

由于数据库系统的种类繁多，要想具体的按照某一条固有原则进行数据的提取是不可行的，需针对每种数据采用不同的方法进行采集。

以下方法以各类常见数据库系统与SQL2008的采集规则。各种数据库数据的一般采集方法由于数据库6SYSBASE----SQL2008

在被审计单位数据库系统上建立ODBC数据源--提取SYSBASE数据转为MDB（ACCESS）数据库数据--利用移动介质MDB数据拷贝到审计人员机器中---利用SQL的导入数据库功能导入到审计人员机器中。SYSBASE----SQL2008在7ORACLE----SQL2008

ORACLE数据库系统一般常见与小型机数据库系统中，由于被其数据库系统是安装与UNIX、LINUX系统中，无法直接在数据库系统中利用ODBC直接采集。一般常用方法无法使用，以下将简单介绍。

ORACLE数据库系统一般常见于大型企业中，采用网络传送数据，利用这一特点，采用网络采集方法。在远端机器中建立ODBC数据源，利用SQL2000自带的DTS工具采用网络远程采集数据库的方法。将数据采集到远端数据库系统中。再利用移动设备将数据库导入被审计人员数据库系统中。ORACLE----SQL2008OR8SQL2000---SQL2008

以这种方法采集数据为最简单的一种数据采集。以下将介绍一种简洁的方法。利用数据库系统自带数据库备份功能将数据库系统直接本份于本地---利用移动设备将数据库文件拷贝到被审计人员机器中--利用MSSQL的数据库还原功能，将该数据库文件还原。完成导入功能。SQL2000---SQL2008以这9字段定义数据类型短整型：-99999~999999整数型：-9999999999~99999999999日期型：MM/DD/YY字符型：有1-254字符组成逻辑型：真、假浮点型：flot19位有效数字字段定义数据类型10

SQL数据类型数值型Smallint,Int,Bigint-32768—32767，-2147483648—2147483647字符型Char(n),varchar(n),Text8000,2147483647日期型SmalldatetimeDatetime精确到天，精确到百分之秒2001-11-20SQL数据类型数值型11建立表结构、删除表建立表结构createTABLE表名

(

姓名char(8)notnull,工作单位char(24)notnull,年龄intnotnull,职务char(10),notnull,编号char(3)notnull,primarykey(编号));

//建立“表名”表，且建立姓名8字节，年龄数值性，编号3字节，编号3字节，关键字为编号。删除表

DropTABLE<表名>droptable表名

//将“表名”表删除。建立表结构、删除表建立表结构12数据输入Insertinto<表>values(‘<字段1值>’,”<字段2值>”..)//将字段1值,字段2值…插入到表中。insertinto表名values('张三','审计厅','24','办事员','001');//向表‘表名’中插入一行，其各个字段为‘张三’,‘审计厅’,‘24’,‘办事员’，‘001’insertinto表名values(‘李四’,‘审计厅’,‘29’,‘办事员’,‘002');//向表‘表名’中插入一行，其各个字段为‘李四’,‘审计厅’,‘29’,‘办事员’，‘002’数据输入13SQL语句-Select语句语法结构SELECT[ALL|DISTINCT|TOP]<目标表达式1>[，<目标表达式2>]…FROM<表名1>[，表名2]…[WHERE<条件表达式>][GROUPBY<列名1>][HAVING<条件表达式>]][ORDERBY<列名2>[ASC|DESC]]

注：GROUPBY分组、

ORDERBY排序

HAVING所有条件、ASC升序、DESC降序SQL语句-Select语句语法结构SELECT[ALL|14检查表中内容/简单查询、表的复制select<列名>from<表名>where<条件表达式>//从表中选择符合条件的记录，并将符合的记录显示。select表名,编号from表名

//从表名表中，选择所有记录，且只显示记录中表名和编号字段。select*intotable1fromtablewhere<条件表达式>

//从TABLE表中选择符合条件表达式的记录。并且将这些记录插入到TABLE1中。检查表中内容/简单查询、表的复制select<列名>fr15记录修改、删除表记录记录修改Update<表>set<字段>=数值

update表名set表号=‘002’//更新“表名”表，并将所有表号字段都改为002

update表名set表号=‘002’where<条件1>

//更新“表名”表，并将符合以上where<条件1>的所有记录的表号字段全部改为002记录删除deletefrom<表>where<条件表达式>

deletefrom表名where表号=002

//删除“表名”表中，表号字段等于002的所有记录。记录修改、删除表记录记录修改16关系运算符=等于<小于<=小于等于>大于>=大于等于<>/!不等于关系运算符=等于17逻辑运算符or或and且Not非逻辑运算符or18特殊运算符between定义一个区间范围isnull测试属性是否为空Like字符串匹配操作符In检查属性是否属于一组值之中exists检查属性是否有值特殊运算符between定义一19特殊运算符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

//从“表名”表中选择编号是否有值的所有记录。特殊运算符select*from表名where年龄20摘要包含“费”Where摘要LIKE'%费%'摘要以“费”结束Where摘要LIKE'%费'摘要以“费”开始Where摘要LIKE'费%‘摘要第二个字是“费”Where摘要LIKE‘_费%‘摘要倒数第三个字是“费”Where摘要LIKE‘%费__'摘要为空WhereTrim(摘要)=‘'or摘要isnull摘要包含“费”21复杂的数据管理命令alterbable<表>modify(<列名><新的性质>)alterbable表名modify(编号char(5))

将“表名”表中的编号字段修改为5字符的字段。alterbable<表>add(<新列名><新列的性质>)alterbable表名add(numberchar(3))

在“表名”表中增加number字段并修改为3字符的字段复杂的数据管理命令alterbable<表>modif22排序、消除重复排序select*from<表>orderby列名[desc]select*from表名orderby编号

从“表名”表中选择所有记录，并按编号顺序(默认升序)进行排列。消除重复selectdistinct<字段>from<表>selectdistinct编号from表名

将“表名”表中的编号字段中的记录消除重复记录。排序、消除重复排序23sql函数Count计数

Min寻找最小值Max寻找最大值Avg求平均值Sum求和sql函数Count计数24连接数据库的表

Select<表1.列名>,<表2.列名>from表1,表2where<表1.列名>=<表2.列名>

例：select学生表.姓名,成绩表.成绩,成绩表.课程名from学生表,成绩表where学生表.学生号=成绩表.学生号

将学生表和成绩表按学生号连接，并查询学生每门课程的成绩。连接数据库的表Select<表1.列名>25AO中使用SQL语句简例

使用前先打开AO，从<审计分析>菜单→<帐表分析>→<电子帐簿管理>,选择电子帐簿→<数据分析>。把每条语句写到SQL编辑器中，然后点击<执行SQL到排序表>，我们就可以看到执行结果。

基本语法

SELECTselect_list[INTO新表名]FROM表一,[表二]…….[WHERE条件表达式][GROUPBY列名][HAVING条件表达式][ORDERBY列名[ASC|DESC]]

其中[]中的内容为可选项，根据使用者需要而选择，

SELECT和FROM为必选项。AO中使用SQL语句简例261、不带任何选项的最基本SQL语句select*from凭证库

//选择凭证库中所有信息，*表示所有列select科目编码,科目名称,摘要,凭证日期,借方金额,贷方金额from凭证库

//从凭证库中选择以上几列。selectdistinct科目编码from凭证库

//如果不要distinct，表示从凭证库中选择科目编码，加上distinct表示去掉那些重复科目编码，使每个科目编码都唯一。

Selectsum(借方金额),sum(贷方金额)from凭证库

//求凭证库中所有凭证的借方发生额总和和贷方发生额总和，此语句可以查看借贷发生额是否平衡。

Selectsum(借方金额)as借方发生总额,sum(贷方金额)as贷方发生总额from凭证库

//注意和上个语句之间的差别，以as含义。

1、不带任何选项的最基本SQL语句select*fr272、带有条件（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表示并列条件，两个条件都要满足2、带有条件（where）的SQL语句select*fr28select*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，可能对编码也要做修改。

select*from凭证库where科目编码l293、带有分组的（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就是所有的三级科目借贷方总额。当然其具体数值大小根据实际编码长度为准。3、带有分组的（groupby）语句GROUP30selectleft(科目编码,5)as二级科目编码,month(凭证日期)as月份,sum(借方金额)as月借方额,sum(贷方金额)as月贷方总额from凭证库where科目编码like'113%'groupbyleft(科目编码,6),month(凭证日期)

//上例是对年度求借贷发生总额，本语句是求出其他应收款（113）中二级科目的每个月的借贷发生总额。同样改变数字大小可以改变所求的科目级别。

本组例子是对其他应收款（113）为例，使用时也可以根据需要对其他科目统计借贷发生年总额或月总额。主要应用与报表或总帐的核对。

selectleft(科目编码,5)as二级科目编码,314、多个表关联selecta.*from凭证库asainnerjoin凭证库asbona.凭证日期=b.凭证日期anda.凭证号=b.凭证号whereb.科目编码like‘101%’and(b.借方金额>5000orb.贷方金额>5000)//通过两张凭证库关联检索出现金收支大于5000元的凭证数据。selecta.*from凭证库asa,凭证库asb,凭证库ascwherea.凭证日期=b.凭证日期anda.凭证号=b.凭证号andc.凭证日期=b.凭证日期andc.凭证号=b.凭证号andb.科目编码like'503%'andb.借方金额<>0andnot(c.科目编码like'102%')

//通过凭证库三次关联，检索出所有借方拨出专款，贷方非‘银行存款’的凭证数据。审计有无虚列专款支出，转移资金的问题4、多个表关联selecta.*from凭证库as32二、数据采集实例二、数据采集实例33谢谢大家谢谢大家34数据库基本知识

数据采集与分析安徽省审计厅信息办数据库基本知识

数据采集与分析安徽省审计厅信息办35现场审计实施系统数据采集与分析现场审计实施系统数据采集与分析36数据采集概述

通过多年多的努力探索和实践，逐步总结出一套切实可行且方便实用的数据采集流程。被审单位财务系统数据库导出（采集）导入本地审计数据库系统中进行本地数据处理（转换）进行AO系统导入操作还原被审计单位财务数据（分析）。数据采集概述37几点说明

在以上流程中，其关键在对被审计单位的数据导出和本地数据的处理过程最容易出现问题。如果处理不当,可能导致利用AO现场实施系统进行计算机审计的方式以失败而告终。

处理原则：在导出被审计单位数据过程中，审计人员可灵活运用ODBC或者直接利用SQL2003-SQL2008自带的数据库备份功能直接将所需数据导出。在本地数据处理过程中，将数据利用数据库的一些技术转换为符合AO系统条件的数据。几点说明在以上流程中，其关键在对被审计单38一、数据库技术

当今数据库技术已成为计算机应用的核心技术，可以认为当今任何计算机信息系统和计算机应用系统都与数据库的应用分不开，数据库已成为其核心和基础。而数据库技术则使人们对信息的利用突破了时间和空间的限制，而成为社会信息化的重要支撑。

大型数据库ORACLE、SYBASE、MSSQLServer、INFOMIX、IBMDB2等，中小型数据库PowerBuild、MSAccess、VisualFoxpro、Paradox一、数据库技术当今数据库技术已成为计算39各种数据库数据的一般采集方法

由于数据库系统的种类繁多，要想具体的按照某一条固有原则进行数据的提取是不可行的，需针对每种数据采用不同的方法进行采集。

以下方法以各类常见数据库系统与SQL2008的采集规则。各种数据库数据的一般采集方法由于数据库40SYSBASE----SQL2008

在被审计单位数据库系统上建立ODBC数据源--提取SYSBASE数据转为MDB（ACCESS）数据库数据--利用移动介质MDB数据拷贝到审计人员机器中---利用SQL的导入数据库功能导入到审计人员机器中。SYSBASE----SQL2008在41ORACLE----SQL2008

ORACLE数据库系统一般常见与小型机数据库系统中，由于被其数据库系统是安装与UNIX、LINUX系统中，无法直接在数据库系统中利用ODBC直接采集。一般常用方法无法使用，以下将简单介绍。

ORACLE数据库系统一般常见于大型企业中，采用网络传送数据，利用这一特点，采用网络采集方法。在远端机器中建立ODBC数据源，利用SQL2000自带的DTS工具采用网络远程采集数据库的方法。将数据采集到远端数据库系统中。再利用移动设备将数据库导入被审计人员数据库系统中。ORACLE----SQL2008OR42SQL2000---SQL2008

以这种方法采集数据为最简单的一种数据采集。以下将介绍一种简洁的方法。利用数据库系统自带数据库备份功能将数据库系统直接本份于本地---利用移动设备将数据库文件拷贝到被审计人员机器中--利用MSSQL的数据库还原功能，将该数据库文件还原。完成导入功能。SQL2000---SQL2008以这43字段定义数据类型短整型：-99999~999999整数型：-9999999999~99999999999日期型：MM/DD/YY字符型：有1-254字符组成逻辑型：真、假浮点型：flot19位有效数字字段定义数据类型44

SQL数据类型数值型Smallint,Int,Bigint-32768—32767，-2147483648—2147483647字符型Char(n),varchar(n),Text8000,2147483647日期型SmalldatetimeDatetime精确到天，精确到百分之秒2001-11-20SQL数据类型数值型45建立表结构、删除表建立表结构createTABLE表名

(

姓名char(8)notnull,工作单位char(24)notnull,年龄intnotnull,职务char(10),notnull,编号char(3)notnull,primarykey(编号));

//建立“表名”表，且建立姓名8字节，年龄数值性，编号3字节，编号3字节，关键字为编号。删除表

DropTABLE<表名>droptable表名

//将“表名”表删除。建立表结构、删除表建立表结构46数据输入Insertinto<表>values(‘<字段1值>’,”<字段2值>”..)//将字段1值,字段2值…插入到表中。insertinto表名values('张三','审计厅','24','办事员','001');//向表‘表名’中插入一行，其各个字段为‘张三’,‘审计厅’,‘24’,‘办事员’，‘001’insertinto表名values(‘李四’,‘审计厅’,‘29’,‘办事员’,‘002');//向表‘表名’中插入一行，其各个字段为‘李四’,‘审计厅’,‘29’,‘办事员’，‘002’数据输入47SQL语句-Select语句语法结构SELECT[ALL|DISTINCT|TOP]<目标表达式1>[，<目标表达式2>]…FROM<表名1>[，表名2]…[WHERE<条件表达式>][GROUPBY<列名1>][HAVING<条件表达式>]][ORDERBY<列名2>[ASC|DESC]]

注：GROUPBY分组、

ORDERBY排序

HAVING所有条件、ASC升序、DESC降序SQL语句-Select语句语法结构SELECT[ALL|48检查表中内容/简单查询、表的复制select<列名>from<表名>where<条件表达式>//从表中选择符合条件的记录，并将符合的记录显示。select表名,编号from表名

//从表名表中，选择所有记录，且只显示记录中表名和编号字段。select*intotable1fromtablewhere<条件表达式>

//从TABLE表中选择符合条件表达式的记录。并且将这些记录插入到TABLE1中。检查表中内容/简单查询、表的复制select<列名>fr49记录修改、删除表记录记录修改Update<表>set<字段>=数值

update表名set表号=‘002’//更新“表名”表，并将所有表号字段都改为002

update表名set表号=‘002’where<条件1>

//更新“表名”表，并将符合以上where<条件1>的所有记录的表号字段全部改为002记录删除deletefrom<表>where<条件表达式>

deletefrom表名where表号=002

//删除“表名”表中，表号字段等于002的所有记录。记录修改、删除表记录记录修改50关系运算符=等于<小于<=小于等于>大于>=大于等于<>/!不等于关系运算符=等于51逻辑运算符or或and且Not非逻辑运算符or52特殊运算符between定义一个区间范围isnull测试属性是否为空Like字符串匹配操作符In检查属性是否属于一组值之中exists检查属性是否有值特殊运算符between定义一53特殊运算符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

//从“表名”表中选择编号是否有值的所有记录。特殊运算符select*from表名where年龄54摘要包含“费”Where摘要LIKE'%费%'摘要以“费”结束Where摘要LIKE'%费'摘要以“费”开始Where摘要LIKE'费%‘摘要第二个字是“费”Where摘要LIKE‘_费%‘摘要倒数第三个字是“费”Where摘要LIKE‘%费__'摘要为空WhereTrim(摘要)=‘'or摘要isnull摘要包含“费”55复杂的数据管理命令alterbable<表>modify(<列名><新的性质>)alterbable表名modify(编号char(5))

将“表名”表中的编号字段修改为5字符的字段。alterbable<表>add(<新列名><新列的性质>)alterbable表名add(numberchar(3))

在“表名”表中增加number字段并修改为3字符的字段复杂的数据管理命令alterbable<表>modif56排序、消除重复排序select*from<表>orderby列名[desc]select*from表名orderby编号

从“表名”表中选择所有记录，并按编号顺序(默认升序)进行排列。消除重复selectdistinct<字段>from<表>selectdistinct编号from表名

将“表名”表中的编号字段中的记录消除重复记录。排序、消除重复排序57sql函数Count计数

Min寻找最小值Max寻找最大值Avg求平均值Sum求和sql函数Count计数58连接数据库的表

Select<表1.列名>,<表2.列名>from表1,表2where<表1.列名>=<表2.列名>

例：select学生表.姓名,成绩表.成绩,成绩表.课程名from学生表,成绩表where学生表.学生号=成绩表.学生号

将学生表和成绩表按学生号连接，并查询学生每门课程的成绩。连接数据库的表Select<表1.列名>59AO中使用SQL语句简例

使用前先打开AO，从<审计分析>菜单→<帐表分析>→<电子帐簿管理>,选择电子帐簿→<数据分析>。把每条语句写到SQL编辑器中，然后点击<执行SQL到排序表>，我们就可以看到执行结果。

基本语法

SELECTselect_list[INTO新表名]FROM表一,[表二]…….[WHERE条件表达式][GROUPBY列名][HAVING条件表达式][ORDERBY列名[ASC|DESC]]

其中[]中的内容为可选项，根据使用者需要而选择，

SELECT和FROM为必选项。AO中使用SQL语句简例601、不带任何选项的最基本SQL语句select*from凭证库

//选择凭证库中所有信息，*表示所有列select科目编码,科目名称,摘要,凭证日期,借方金额,贷方金额from凭证库

//从凭证库中选择以上几列。selectdistinct科目编码from凭证库

//如果不要distinct，表示从凭证库中选择科目编码，加上distinct表示去掉那些重复科目编码，使每个科目编码都唯一。

Selectsum(借方金额),sum(贷方金额)from凭证库

//求凭证库中所有凭证的借方发生额总和和贷方发生额总和，此语句可以查看借贷发生额是否平衡。

Selectsum(借方金额)as借方发生总额,sum(贷方金额)as贷方发生总额from凭证库

//注意和上个语句之间的差别，以as含义。

1、不带任何选项的最基本SQL语句select*fr612、带有条件（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表示并列条件，两个条件都要满足2、带有条件（where）的SQL语句select*fr62select*from凭证库where科目编码like'101%'and贷方金额>5000and摘要notlike'%工资%'and