关于考勤管理系统中的SQL语句优化解析
摘要：该文针对沙钢集团人力资源管理信息系统中月初汇总上一月考勤数据的数据库过程运行效率低得问题，通过优化SQL语句，提高了汇总过程的执行效率。
关键词：ORACLE;SQL;考勤;数据库访问;索引
沙钢集团人力资源管理信息系统中考勤模块记录考勤信息时每人每天两条数据，分别记录上午和下午的出勤情况。以集团本部19500人计算，每个月的考勤记录大约是117万条数据，加上成员企业的考勤记录和考勤表中需要有三个月左右数据，日常考勤表中维持的数据量大概有600万条数据左右。本部薪酬计算用到的考勤数据需要从这张考勤表中取出数据并按工号及部门分别进行汇总形成每人每个部门(考虑到人员部门调动情况)一条考勤数据。
1 难点及主要采取措施
考勤数据汇总中遇到的难点一是数据量大，数据在600万条以上，对这些数据的处理需要考虑ORACLE的性能;二是对新入职员工职校考勤时的考勤项目名称是对应正常考勤项目名称后加“*”，但是在数据库中记录的考勤项目时是记录对应项目的FID。因此在汇总考勤数据前需要将职校考勤数据做特殊处理，将考勤项目转换成对应的正常考勤项目;三是人力资源管理信息系统数据库中各类基础表比较多，考勤表中记录的信息主要是各类信息的FID，需要多表关联才能将汇总表中的信息全部显示出来。
针对这些情况，主要采取的措施有以下几个方面：
1) 减少数据库访问次数。在执行每条SQL语句时， ORACLE内部执行了许多工作： 解析SQL语句， 估算索引的利用率，绑定变量， 读数据块等等。因此减少访问数据库的次数， 就能实际上减少ORACLE的工作量。
2) 用索引提高效率。索引是表的一个概念部分，用来提高检索数据的效率。实际上，ORACLE使用了一个复杂的自平衡B-tree结构。当ORACLE找出执行查询和Update语句的最佳路径时， ORACLE优化器将使用索引。 同样在联结多个表时使用索引也可以提高效率。 除了那些LONG或LONG RAW数据类型，你可以索引几乎所有的列。在大型表中使用索引特别有效。
3) 尽量多的使用COMMIT。只要有可能，在程序中尽量多使用COMMIT，这样程序的性能能得到提高，需求也会因为COMMIT所释放的资源而减少：COMMIT所释放的资源有回滚段上用于恢复数据的信息，被程序语句获得的锁，REDO LOG BUFFER中的空间，ORACLE为管理上述3种资源中的内部花费。
2 具体应用
针对具体在考勤汇总中遇到的问题主要在下面几个方面做了修改：
1) 尽量减少对数据库的访问次数，具体体现在能够一次SELECT，INSERT，UPDATE操作的数据不要分多次循环处理。因此将过程中对每个工号的员工进行循环统计各考勤项目的天数修改成对考勤项目进行循环统计所有人该考勤项目的对应天数。这种处理方式使循环次数由原来的19500次左右缩短到15次左右。大大缩短了过程执行的时间。
2) 用索引提高效率，具体应用是将过程中涉及的表按照条件中应用到的列的次序建立索引。这里要提到的是一开始只注意到考勤表数据量大，需要建立索引，没有注意到汇总表。虽然考勤表以百万计，而汇总表只以万计，但是在操作汇总表时索引的创建极大地提高了效率。对汇总表操作UPDATE语句由原来的数小时降低到半分钟左右。
3) 尽量多的使用COMMIT。在循环处理各考勤项目汇总时，对产生的数据进行COMMIT操作。这样做的好处不仅是释放了数据库资源，同时在數据中也即是保存了数据，能即是了解数据处理到哪一个环节。
4) 尽量不在条件语句中出现的列上使用函数。过程中使用到的日期在数据库中存储的是TIMESTAMP类型，而统计的维度是一个自然月。这里对进行比较的自然月进行了处理，将月初月末类型装换成TIMESTAMP类型来取代在原先采用的将数据库表日期列装换成自然月的字符串类型的方法。这种方法使数据转换由原来的每条数据一次缩减到一条SELECT一次，减少了ORACLE转换数据类型需要的时间。
3 结束语
ORACLE SQL性能优化的一些措施在人力资源管理信息系统的考勤汇总中的应用大大提高了数据库汇总过程的执行效率，为考勤记录的统计、考勤报表的生产提供了数据，为薪酬模块、绩效模块和奖金模块的顺利上线做好了数据准备。
参考文献：
[1] 黄河. Oracle9i数据库系统培训教程[M]. 北京： 清华大学出版社， 2002.
[2] Thomas Kyte. Oracle专家高级编程[M]. 北京： 清华大学出版社， 2002.
[3] Kevin Loney. Oracle9i： The Complete Reference[Z].Oracle公司技术参考书.
作者：黄娟
【摘要】 面对庞大的业务数据量，计算机辅助审计的开展解决了手工审计无法做到的审查功能，财政预算执行的审计领域迈进了一大步。本文结合实践对财政预算指标和国库集中支付系统的审计介绍了比较全面的计算机审计思路和相应SQL语言的运用。
【关键词】 财政 预算执行 计算机审计
国库集中支付制度实施后，部门单位的所有一切资金运作均要通过现代的电子银行信息支付系统，财务会计核算实现了电算化，财政也建立了一系列相应的财政管理信息系统。因此必须及时转换审计模式，改进审计方式，创新审计方法，从传统的手工审计向计算机审计转变，积极应对审计环境的变化和发展，强化分析法、比较法等现代审计技术方法的运用，促进审计质量的提高，全力提高审计监督的层次和水平。
一、预算指标系统中预算分配、调整、执行及结余结转审计
1、年初预算分配情况
(1)审计思路：在“指标来源”表中查询明细摘要中含有“年初预算”的记录，与报人大批准数、财政报表决算数相比较，检查年初预算分配情况以及验证财政决算报表数据的正确性;是否存在预留预算指标未按规定报经人大批准、没有按照人大批准的预算及时、足额向各部门批复、部分资金没有批复落实到具体部门和项目而是在年度执行中通过追加的方式再进行分配等问题。
(2)SQL语句实现。
Select [bh] as 文号，[mxzy] as 明细摘要，[ysje] as 预算金额 From业务_指标来源WHERE [mxzy] LIKE '年初预算%'
2、调整预算情况
(1)审计思路：查询“指标来源”表中明细摘要中含有“追加”的记录，得出该年度追加数，与报经人大批准的追加预算数进行比较，判断有无未经人大批准的支出金额。另外，计算当年追加支出预算数为占年初预算金额比例，是否存在预算执行中调整预算金额较大、年初预算编制不够科学的问题。
(2)SQL语句实现。
Select [bh] as 文号，[mxzy] as 明细摘要，[ysje] as 预算金额 From 业务_指标来源 WHERE mxzy LIKE '%追加%' OR mxzy LIKE '追加%' OR mxzy LIKE '%追加'
3、预算执行情况审计
(1)审计思路：一是上级专项拨款的使用情况。分别查询“指标来源”表和“指标通知单明细”表中上级专项补助情况，并进行核对，看当年上级专款是否全部拨出，将当年未拨出项目款项及未全部拨出项目款项与“上级专项资金结转下年”表中明细再进行核对，无结转下年支出的款项则说明已被挪用或调剂使用。二是专项拨款配套资金落实情况。根据“指标来源”表查询出上级专项资金明细，查找相关指标文件及涉及的农财科、企业科财务帐，看需要该市配套资金的项目是否将配套资金落实并支付到位。
(2)SQL语句实现。
第一，上级专项拨款的使用情况。
第一步：Select [bh] AS 文号，sum(ysje) AS 金额 INTO 来源中上级专项资金 From 业务_指标来源 group by [bh]
HAVING bh LIKE '苏%' OR bh LIKE '常%'
第二步：Select [tzsbh] AS 通知单文号，sum([hds]) AS 金额 INTO 通知单拨出上级专项资金 From 业务_指标通知单明细 group by [tzsbh]
HAVING tzsbh LIKE '苏%' OR tzsbh LIKE '常%'
第三步：Select a.[文号]，a.金额，b.[金额] INTO 来源中上级专项当年未拨资金 From [来源中上级专项资金] a left Join [通知单拨出上级专项资金] b On b.[通知单文号]=a.[文号] where b.[金额] is null or a.金额<>b.[金额]
第四步：Select a.[文号]，a.[来源中上级专项资金_金额]，a.[通知单拨出上级专项资金_金额]，b.[je] as 上级专项资金结转下年_金额 INTO 上级专项未拨付到位资金 From [来源中上级专项当年未拨资金] a left Join [业务_上级专项资金结转下年] b On a.[文号]=b.[wh] where b.[je] is null or a.[来源中上级专项资金_金额]<>b.[je]
第二，专项拨款配套资金落实情况。
Select [mxzy] as 明细摘要，[ysje] as 预算金额 From 业务_指标来源 WHERE bh LIKE '苏%' OR bh LIKE '常%'
4、预算结余及结转情况审计
(1)审计思路：由“指标来源”、“指标通知单明细”表分析净结余及当年应拨未拨上级专款金额，是否存在当年上级专项拨款滞留财政部门，未及时拨付有关单位的问题;应拨未拨金额减去上级专项补助结转下年支出金额是否存在差额，差额可能是上级专项补助用于平衡预算金额。
(2)SQL语句实现。
第一步：Select SUM(ysje) as 指标来源合计 From 业务_指标来源
第二步：Select SUM(hds) as 指标通知单拨款合计 From 业务_指标通知单明细
第三步：Select SUM(ysje) as 指标来源中上级专项资金 From 业务_指标来源 WHERE bh LIKE '苏%' OR bh LIKE '常%'
第四步：Select SUM(hds) as 指标通知单中拨出上级专项资金 from 业务_指标通知单明细 WHERE tzsbh LIKE '苏%' OR tzsbh LIKE '常%'
第五步：Select sum(je) as 上级专项结转下年支出 From 业务_上级专项资金结转下年
第六步：Select ?鄢 From 业务_指标来源 WHERE mxzy LIKE '上年结转%'
二、国库集中支付系统审计
1、国库拨款计划执行情况审计
通过审计，促进财政正确反映预算结余，减少预算单位沉淀财政资金，有利于增加财政库款结余、调度和运作财政资金。
(1)审计思路：根据当年预算指标执行进度及情况分析当年一般预算支出进度情况，发现年末预算支出比例较大，于是想查清年末预算支出的项目内容及资金去向，是否存在年末突击拨款而专项资金未形成实际支出的现象，因此，将年末由国库集中支付实拨至财政局各业务处上的预算支出查询出来，与国库处市级财政储备、预算列支账(原预算外资金账)、农业处账、企业处账核对，年末应下拨企业或部门的专项资金是否挂暂存款，并汇总各业务处账面滞留资金余额。按查询记录与各业务处账面核对，拨至各业务处的专项资金是否挂暂存款未及时下拨使用，影响了资金的使用效果。
(2)SQL语句实现。
select 预算月份，编号，a.单位代码，b.单位名称，日期，收款人名称，金额合计，说明 into 年末预算内实拨至各业务处账上资金 from 业务_拨款凭证 a inner join 业务_系统单位表 b on a. 单位代码=b. 单位代码 where 编号 like '%实%' and (单位名称 like '%常州市财政%' or单位名称like '%常州市%非税%') and str(预算月份)>10 order by预算月份
2、国库资金支付管理方式执行情况审计
检查有无按照国库集中支付制度规定方式管理，以促进提高财政监管力度，提高财政资金使用效率。
(1)审计思路：第一，检查直接支付、授权支付等支出金额及占比情况，分析支付结构的合理性。第二，从拨款凭证表中查询生成“授权支付中提取备用金表”，筛选大额提现单位，分析落实大额提现原因。第三，将拨款凭证中从零余额账户划单位基本户的记录检索出来，包括：直接支付从财政零余额账户划单位基本户和授权支付中从单位零余额账户划单位基本户两种情况，并分析确定疑点单位，延伸审计单位划入基本户资金真实使用情况。
(2)SQL语句实现。
第一，直接支付、授权支付情况。
第一步：按国库集中支付类别分类检索直接支付的记录生成“财政直接拨付”新表。
select bh，jehj into 财政直接拨付 from 业务_拨款凭证 where 编号 like '%直%'
第二步：汇总“财政直接拨付”表中支付的发生金额和拨付笔数，再计算直接支付占总支付额的比例。
select count(?鄢) as 财政直接拨付笔数，sum(jehj) as 直接拨付金额合计 from 财政直接拨付
第二，备用金提取情况。
第一步：先从拨款凭证表中查询生成“授权支付中提取备用金表”，汇总授权支付中提取备用金总额，从而计算提取备用金总额占授权支付总额的比例。
select ?鄢 into 授权支付中提取备用金 from [业务_源_拨款凭证] where 编号 like '%授%' and 说明 like '%备用%' order by 金额合计 desc
select sum(金额合计) as 提备用金总额 from 授权支付中提取备用金
第二步：检索汇总新表“授权支付中提取备用金表”中单笔提现5万元以上合计金额。
select sum(金额合计) as 金额合计 from [授权支付中提取备用金] where str(金额合计)>50000
第三，财政资金从零余额账户划入单位基本户情况。
第一步：从拨款凭证表中查询生成“授权支付中从单位零余额帐号划单位基本户”表并汇总划款金额。
select ?鄢 into 授权支付中从单位零余额帐号划单位基本户 from 业务_拨款凭证 where 收款人标识='1' and 支付类型代码='0202' order by 金额合计 desc
select sum(金额合计) from 业务_拨款凭证 where收款人标识s='1' and支付类型代码='0202'
第二步：从拨款凭证表中查询生成“直接支付中从财政零余额帐号划单位基本户”表并汇总划款金额。
select ?鄢 into 直接支付中从财政零余额帐号划单位基本户 from 业务_拨款凭证 where收款人标识='1' and支付类型代码='0201' order by 金额合计 desc
select sum(金额合计) from 业务_拨款凭证 where收款人标识s='1' and支付类型代码='0201'
3、公务卡执行情况审计
促进公务卡使用管理，进一步扩大公务卡支付范围和比例，规范公务卡使用，使财政部门对日常公用支出实时监控，保障财政资金安全使用。
(1)审计思路：首先通过查询两年使用公务卡实际报销金额的对比分析近两年来公务卡的使用趋势，然后再根据《某市市级预算单位公务卡结算财务管理暂行办法》具体分析公务卡结算制度执行情况，是否存在较大数额的一些未使用公务卡结算的公务支出，分析延伸审计有疑点的大额支出。
(2)SQL语句实现。
第一，查询近两年全市市级预算单位公务卡实际报销金额同期数据并进行对比，分析变化趋势。
select sum(金额合计) as 公务卡报销金额 from 业务_拨款凭证 where 编号 like '%授%' and 结算代码='71'
第二，查询未使用公务卡结算的一些公务支出情况，如全年预算单位差旅费金额、会议费金额、餐费招待费住宿及食品金额等情况。以差旅费查询为例，其他SQL查询语句相似。
第一步：select ?鄢 into 差旅费支出记录 from业务_拨款凭证 where 编号 like '%授%' and 说明 like '%差旅%'
第二步：select ?鄢 into 差旅费支付未使用公务卡记录 from 差旅费支出记录 where 结算代码<>'71'
第三步：select sum(金额合计) as 未使用公务卡合计金额 from 差旅费支付未使用公务卡记录
【参考文献】
[1] 马茹：关于预算执行审计的几点思考[J].现代审计与经济，2012(4).
[2] 李顺国：深化部门预算执行审计 当好公共财政卫士[J].审计月刊，2013(3).
[3] 郑向军：行政事业单位国库集中支付存在的问题及对策研究[J].中国总会计师，2013(3).
[4] 高鹏程：财政预算公务卡制度改革探讨[J].地方财政研究，2012(2).
作者：张婷
、字符转换函数
1、ASCII()
返回字符表达式最左端字符的ASCII 码值。在ASCII()函数中，纯数字的字符串可不用‘’括起来，但含其它字符的字符串必须用‘’括起来使用，否则会出错。
2、CHAR()
将ASCII 码转换为字符。如果没有输入0 ~ 255 之间的ASCII 码值，CHAR() 返回NULL 。
3、LOWER()和UPPER()
LOWER()将字符串全部转为小写;UPPER()将字符串全部转为大写。
4、STR()
把数值型数据转换为字符型数据。
STR ([，length[， ]])
length 指定返回的字符串的长度，decimal 指定返回的小数位数。如果没有指定长度，缺省的length 值为10， decimal 缺省值为0。
当length 或者decimal 为负值时，返回NULL;
当length 小于小数点左边(包括符号位)的位数时，返回length 个*; 先服从length ，再取decimal ;
当返回的字符串位数小于length ，左边补足空格。
二、去空格函数
1、LTRIM() 把字符串头部的空格去掉。
2、RTRIM() 把字符串尾部的空格去掉。
三、取子串函数
1、left()
LEFT (， )
返回character_expression 左起 integer_expression 个字符。
2、RIGHT()
RIGHT (， )
返回character_expression 右起 integer_expression 个字符。
3、SUBSTRING()
SUBSTRING (， ， length)
返回从字符串左边第starting_ position 个字符起length个字符的部分。
四、字符串比较函数
1、CHARINDEX()
返回字符串中某个指定的子串出现的开始位置。
CHARINDEX (<’substring_expression’>， )
其中substring _expression 是所要查找的字符表达式，expression 可为字符串也可为列名表达式。如果没有发现子串，则返回0 值。
此函数不能用于TEXT 和IMAGE 数据类型。
2、PATINDEX()
返回字符串中某个指定的子串出现的开始位置。
PATINDEX (<’%substring _expression%’>， )其中子串表达式前后必须有百分号“%”否则返回值为0。
与CHARINDEX 函数不同的是，PATINDEX函数的子串中可以使用通配符，且此函数可用于CHAR、 VARCHAR 和TEXT 数据类型。
五、字符串操作函数
1、QUOTENAME()
返回被特定字符括起来的字符串。
QUOTENAME (<’character_expression’>[， quote_ character]) 其中quote_ character 标明括字符串所用的字符，缺省值为“[]”。
2、REPLICATE()
返回一个重复character_expression 指定次数的字符串。
REPLICATE (character_expression integer_expression) 如果
integer_expression 值为负值，则返回NULL 。
3、REVERSE()
将指定的字符串的字符排列顺序颠倒。
REVERSE () 其中character_expression 可以是字符串、常数或一个列的值。
4、REPLACE()
返回被替换了指定子串的字符串。
REPLACE (， ， ) 用string_expression3 替换在string_expression1 中的子串string_expression2。
5、SPACE()
返回一个有指定长度的空白字符串。
SPACE () 如果integer_expression 值为负值，则返回NULL 。
6、STUFF()
用另一子串替换字符串指定位置、长度的子串。
STUFF (， ， ，)
如果起始位置为负或长度值为负，或者起始位置大于
character_expression1 的长度，则返回NULL 值。
如果length 长度大于character_expression1 中 start_ position 以右的长度，则character_expression1 只保留首字符。
六、数据类型转换函数
1、CAST()
CAST ( AS [ length ])
2、CONVERT()
CONVERT ([ length ]， [， style])
1)data_type为SQL Server系统定义的数据类型，用户自定义的数据类型不能在此使用。
2)length用于指定数据的长度，缺省值为30。
3)把CHAR或VARCHAR类型转换为诸如INT或SAMLLINT这样的INTEGER类型、结果必须是带正号或负号的数值。
4)TEXT类型到CHAR或VARCHAR类型转换最多为8000个字符，即CHAR或VARCHAR数据类型是最大长度。
5)IMAGE类型存储的数据转换到BINARY或VARBINARY类型，最多为8000个字符。
6)把整数值转换为MONEY或SMALLMONEY类型，按定义的国家的货币单位来处理，如人民币、美元、英镑等。
7)BIT类型的转换把非零值转换为1，并仍以BIT类型存储。
8)试图转换到不同长度的数据类型，会截短转换值并在转换值后显示“+”，以标识发生了这种截断。
9)用CONVERT()函数的style 选项能以不同的格式显示日期和时间。style 是将DATATIME 和SMALLDATETIME 数据转换为字符串时所选用的由SQL Server 系统提供的转换样式编号，不同的样式编号有不同的输出格式。
七、日期函数
1、day(date_expression)
返回date_expression中的日期值
2、month(date_expression)
返回date_expression中的月份值
3、year(date_expression)
返回date_expression中的年份值
4、DATEADD()
DATEADD (， ， )
返回指定日期date 加上指定的额外日期间隔number 产生的新日期。
5、DATEDIFF()
DATEDIFF (， ， )
返回两个指定日期在datepart 方面的不同之处，即date2 超过date1的差距值，其结果值是一个带有正负号的整数值。
6、DATENAME()
DATENAME (， )
以字符串的形式返回日期的指定部分此部分。由datepart 来指定。
7、DATEPART()
DATEPART (， )
以整数值的形式返回日期的指定部分。此部分由datepart 来指定。 DATEPART (dd， date) 等同于DAY (date)
DATEPART (mm， date) 等同于MONTH (date)
DATEPART (yy， date) 等同于YEAR (date)
8、GETDATE()
以DATETIME 的缺省格式返回系统当前的日期和时间。
八、统计函数
AVG ( ) -返回的平均价值
count( ) -返回的行数
first( ) -返回第一个值
last( ) -返回最后一个值
max( ) -返回的最大价值
min( ) -返回最小的价值
total( ) -返回的总和
九、数学函数
abs(numeric_expr)求绝对值
ceiling(numeric_expr)取大于等于指定值的最小整数 exp(float_expr)取指数
floor(numeric_expr)小于等于指定值得最大整数 pi()3.1415926.........
power(numeric_expr,power)返回power次方
rand([int_expr])随机数产生器
round(numeric_expr,int_expr)安int_expr规定的精度四舍五入 sign(int_expr)根据正数,0,负数,,返回+1,0,-1 sqrt(float_expr)平方根
十、系统函数
suser_name()用户登录名
user_name()用户在数据库中的名字 user用户在数据库中的名字 show_role()对当前用户起作用的规则
db_name()数据库名object_name(obj_id) 数据库对象名
col_name(obj_id,col_id) 列名
col_length(objname,colname) 列长度
valid_name(char_expr)是否是有效标识符
随着信息技术的不断发展，被审计对象的财务和业务系统也大多使用了专用软件进行计算机管理，如果暂不考虑计算机软硬件本身的系统审计，那么我们所面对的审计也就分为了以财务账套为基础的账套式审计模式和直接对数据本身(包括财务数据和业务数据)审计的数据式审计模式。但不论是何种审计模式，最根本的还是要使用计算机语句对具体问题进行查询分析，即通过数据库的SQL查询语句对数据库中的数据表进行查询分析。
虽然SQL语句的功能很多，且不同数据库环境下的具体使用也会因为所遵循的官方标准不同而略有差别，但多数情况下的使用是相通的。同时因为审计工作的特点，所使用的SQL语句也多是对数据的查询，一般并不会包括对原始数据的增加、删除、修改等操作，所以在计算机审计中重点需要掌握的内容就是灵活利用SQL语句的查询功能。实际审计工作当中，在数据审计模式下我们所常用的是如下所示的语句，也是从实际审计工作中收集出具有代表性的语句，并且所有语句都基于Access数据库，在AO现场审计实施系统中可正常使用。
一、对单个表的条件查询，用where指定查询条件，也有不使用where的情况，多用于对原始表的统计、分类等
该类情况下SQL语句的语法是“select * from 表名 where 条件”。可以指定单个条件或多个条件，并可配合其他函数或关键字使用，常用到的类型有如下几类。
(一)对单个条件的简单查询：
select * from 存款表 where 金额>=100000; 该情况的使用较为简单，适用于任何单条件查询。
(二)对多个条件的查询，各条件间可能是and或or的关系：
select * from 存款表 where金额>=100000 and 存款时间 >= #2006-1-1#;
该情况常用于对多个条件的查询，根据关键字and或or来决定是同时满足所有条件还是仅满足某些条件。
(三)对多个条件的查询，得到符合条件的某字段的合计值：
select sum(金额) from 存款表 where金额>=100000 and 存款时间 >= #2006-1-1# and 存款时间<= #2006-12-31# order by 金额 desc, 存款时间 asc;
该情况一般用于统计某种条件下的合计值，常用于对某种情况下的总金额情况进行了解。
(四)用集函数按条件查询：
select count(* ) from 凭证表 where 金额>=100000; select max(发生金额) from 存款表; select avg(发生金额) from 存款表;
该情况类似于上面求和函数的使用，可根据具体需求进行应用。
(五)对关键字的内容进行查询：
select * from 凭证表 where 摘要 like „%招待费%‟; 该情况较为常用，查询包含某些关键字的所有记录，也可结合关键字and或or等查询多个条件。
(六)查询内容是否包含在给定的范围内：
select * from 科目代码表 where 科目代码 in („101‟,‟102‟); 该情况用于所关心的字段包含内容类别较少的情况，常用在已基本确定查询范围之后。当所关心内容类别较多时可使用下面将提到的两张表的嵌套查询。
(七)对某个字段进行分组，查询各字段属性分别对应的发生次数，并按发生次数进行排序：
select 款项代码, count(款项代码) as 次数from大额现金支取表 group by 款项代码 order by count(款项代码) desc; 该情况常用于对某个所关心字段的分类统计，得出每个类别所发生的次数，并可按发生次数进行排序。
(八)按某个字段的长度进行分类并进行排序：
select len(科目代码) as 发生次数from 科目代码表 group by len(科目代码) order by len(科目代码) desc;
该情况用于了解某字段的长度分组情况，可对所取得的原始数据进行验证，以证实原始数据的规范性或找出不规范情况的问题所在。
(九)分组查询，group与having联用：
select 客户号,count(*) as 发生次数
from 存款表
where 发生金额>=100000 group by 客户号 having count(*)>1;
该类语句常用于要列出所关心某字段的某一个或几个条件限制下发生次数超出某范围的情况。如查询发生多次(即同一关键字发生的记录数大于1)大额存取的客户号及其存取次数，查询结果按客户号分组。
以上各类对单个表的查询结果并未直接存储成新表，不利于对查询结果的进一步分析利用，我们可以使用“select * into 新表名 from 表名 where 条件”这种语句格式把查询结果保存成新表，如
select * into 大额存款发生情况表 from 存款表 where 金额>=100000;
二、对多个表的条件查询，一般需根据各表的相应关键字进行连接
这种情况下必须对各表的对应关键字进行关联，一般情况下是对两张表的关联查询，当有更多张表要操作时可以每次对两张表操作后再和剩余的表关联操作，也可以扩展SQL语句同时对两个以上的表操作，以下只列出对两张表操作的情况。
(一)两张结构相同的表合并并生成新表，使用关键字union进行联合查询：
select * into 中间表 from
(select * from 原始表1 union select * from 原始表2); 该情况常用于对多个原始表的整理以生成中间表，便于后续的查询工作。如合并采集到的原始数据，将原本分月存储的数据表全并成分年度存储的数据表。
(二)两张表的连接，生成新的中间表：
select a.*,b.客户名称into 含客户名称的现金支取表 from现金支取表 as a, 客户基本信息表 as b where a.款项代码=b.款项代码; 使用该类语句的前提是两张表存在相关联的关键字，常用于一张表根据两表的连接关键字引入另一张表的某些字段，为后续的查询准备好中间表。其中使用别名可以简化SQL语句的输入。如财务数据中采集到的凭证表是两张表时，可连接两表引入摘要字段等。
(三)两张表的关联查询，可加入多个条件：
select a.*,b.科目代码 from 对公活期存款明细表 as a, 活期存款账户动态表 as b where a.款项代码=b.款项代码 and 科目代码 like ‟201%‟;
该情况可看作对两张表连接的扩展，即根据where所限定的条件来对两张表关联查询。
(四)两张表的嵌套查询，两层查询间要使用关键字in：
select * into频繁发生大额现金支取
from大额现金支取
where 交易金额>=100000 and 款项代码 in (
select 款项代码
from款项代码发生次数_大额现金支取
where 次数 >= 10 )
order by 款项代码,交易金额,记账日期;
这种情况也可理解为简单查询，不同的是其中where所指定的某个条件是由另一张表的又一个查询所指定的，与单表查询的语句
select * from 科目代码表 where 科目代码 in („101‟,‟102‟); 进行对比后发现，可将两张表的嵌套查询看作如上语句的扩展。如利用大额现金支取表和大额现金支取的款项代码发生次数表查询交易金额大且发生次数多的情况。再比如利用贷款表和股东表查询贷款表中含股东贷款的情况。
(五)多表的连接操作
多表连接可分为内连接、外连接、自身连接、交叉连接。其中内连接又分为等值连接、不等连接和自然连接，外连接又分为左连接、右连接和全连接。实际审计工作当中所用到的多是内连接与外连接，较少用到自身连接与交叉连接。多表的连接使用需根据具体情况而定，下面列出部分示例。
1.内连接的等值连接：
select * from 存款表 as a inner join 客户信息表 as b on a.款项代码=b.款项代码;
以上语句用于列出存款表和客户信息表中款项代码相同的记录。
2.外连接的左连接。
select * from 存款表 as a left join 客户信息表 as b on a.款项代码=b.款项代码; 左连接的特点是显示全部左边表中的所有项目，即使其中有些项中的数据未填写完全。右连接与此类似，其特点是显示全部右边表中的所有项目，甚至是左边表中没有和它匹配的记录。
以上列出的是数据审计模式下根据实际工作总结出的常用SQL语句，虽然所列内容并不全面，更不可能完全体现SQL查询的全面功能与精妙所在，但只要能够很好地分析出审计需求，把用自然语言表达的审计思路转化为计算机的SQL查询语句，并结合以上的常用语句适当组合运用，一定能使数据审计模式下的工作达到事半功倍的效果，更好地推动计算机审计的快速发展。
1.用SQL语句在“商学院教学管理”数据库中建立学生表、课程表、成绩表、教师表、授课表、班级表。实现下列要求：
(1)按课本第99-100页表结构要求建立以上六张表。
(2)在学生表中插入一个学生记录：(”99082901”,”程丽”,”女”,1981-2-10,”群众”， ”200704”)。
(3)使选修CZ004号课的同学的成绩为NULL值。
(4)为课程表加一”教师”列。
(5)修改课程表，使“肖贵”老师教CJ001号课。
(6)列出“王勇”老师所授课程的课号和课名。
(7)列出年龄不小于20的男生的学号和姓名。
(8)统计学生所选课程的门数。
(9)列出选修CJ001号课的学生的平均年龄。
(10)列出选修“计算机基础”课的学生的平均成绩、最高分和最低分。
(11)列出所有姓“李”的同学的姓名、年龄和性别。
(12)列出成绩表中成绩为空值的学生的姓名、课号。
(13)列出年龄大于女生平均年龄的男生的姓名和年龄。
(14)删除成绩表中无成绩的记录。
(15)把低于CJ001号课平均成绩的所有同学的所有课程的成绩提高5分。
(16)按学号升序，成绩降序列出每个同学的选课情况(包括姓名、课名、成绩)。
(17)按课号和成绩降序列出每门课的选课情况包括课名、姓名、成绩。
(18)按成绩降序列出每个同学的姓名、总成绩。
(19)求出学习全部课程的所有同学的名单。
(20)列出平均成绩大与80分同学的学号和平均成绩，按平均成绩降序排列。
(25)查询平均成绩排在前5名的学生姓名及平均成绩。
(26)统计选修课程在3门以上(含3门)的学生的学号、姓名和平均成绩。
(27)查询学生年龄超过21岁的学生信息。
(28)列出所有姓“李”的同学的学号、姓名、年龄和性别。
(29)按成绩降序列出每个同学的姓名、总成绩。
一、数据定义功能
创建表：Createtable 表名(字段名……) 修改表：Altertable表名
删除表：Droptable表名
二、数据操纵功能
数据插入：Insertinto 表名values ( …，…，) 数据更新：Update表名Set表达式 Where 条件 数据删除：DeleteFrom 表名Where 条件
三、查询功能
SELECT*FROM表名
WHERE条件
ORDERBY 字段排序
GROUPBY 字段分组
函数：SUM( )AVG( )COUNT( )MAX( )MIN( )去向：
INTOTABLE表
INTOCURSOR临时表
INTOARRAY数组
1. 创建数据库db_Student，在db_Student中创建四张表：
学生表tb_Student,字段:SID(编号),SName(姓名),Sage(年龄),SSex(性别)
教师表tb_Teacher,字段:TID(编号),TName(姓名)
课程表tb_Course,字段:CID(编号),CName(名称),TID(教师编号)
成绩表tb_Score,字段:SID(编号),CID(课程编号),Score(成绩)
要求:分析添加约束,如:主键约束，外键约束等(15分)
2. 给表添加一些数据。(10分)
3. 完成下列查询:(30分)
① 查询姓李的老师有多少位。
② 查询c#课程的平均分。
③ 查询sql最高分的学生编号，学生姓名及分数。
④ 查询sql课程超平均分的学生姓名及分数。
⑤ 查询没有考sql的学生编号及姓名。
⑥ 查询没有学王老师课程的学生编号及姓名。
⑦ 查询课程表的第5到第10条记录，条件为编号不连续。写两种方法。
⑧ 查询每个学生的编号，姓名，课程名称，成绩，老师信息。
⑨ 查询c#考试成绩前三名的学生，包含并列的行。(可能查询的结果超过三行) ⑩ 查询c#课程比sql课程的考试分数高的学生编号及姓名。
4. 写存储过程完成下列问题，并写出存储过程执行语句：(15分)
① 根据学生编号查询学生的各科成绩及所教课程的老师
② 根据教师编号，查询所教课程(教师编号，姓名，课程编号，课程名)。
③ 写一种分页的存储过程。
5. 将第3 题的查询⑧⑨两题写成视图，然后执行查询视图。(10分)
6. 创建存储过程，在存储过程里创建事务，执行删除课程表的行时，同时删除成绩表的相
应行。(10分)
7. 创建触发器，实现删除教师表的行时，同时删除课程表的相应行。(首先删除相应表的约
束)(10分)
1.Select * from student查询所有原始记录
2. Select StudentID, StudentName from student 只查询学号和姓名
3. Select StudentID as 学号, StudentName as 姓名, Sex as 性别，Class as 班级,NormalScore as 平时成绩，ExperimentScore as 实验成绩, ExaminationScore as 考试成绩 from student
4. Select * from student where sex=’男’只需要查看男生的成绩
5. Select * from student where NormalScore>80只需要查看平时成绩大于80分的记录
6. Select * from student where studentname like ‘陈%’查询姓陈的同学的记录
7. Select * from student order by examinationScore根据考试成绩排序
8. Select * from student order by examinationScore排序---降序
9. Select Sum(examinationScore) as 总成绩 from student求总成绩
10. Select Avg(examinationScore) as 平均成绩 from student求平均成绩
11. Select count(*) as 总记录数 from student求总记录数
12. Select Max(ExaminationScore) as 最高分 from student求最高分
13. Select Min(ExaminationScore) as 最高低分 from student求最低分
14. Select Class as 班级, Avg(ExaminationScore) as 平均成绩 from student group by class各班级成绩的分类汇总
Public Class frmMain
Private ObjCnn As New OleDbConnection
Private ObjCmd As OleDbCommand
Private StrDir As String = "D:MIS1025StudentMgr.accdb"
Private Sub frmMain_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
ObjCnn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & StrDir
ObjCnn.Open()
ObjCmd = New OleDbCommand
ObjCmd.Connection = ObjCnn
End Sub
Private Sub FindRecord(ByVal StrSQL As String)
ObjCmd.CommandText = StrSQL
Dim rd As OleDbDataReader = ObjCmd.ExecuteReader
Dim tbl As New DataTable
tbl.Load(rd)
DataGridView1.DataSource = tbl
End Sub
Private Sub CmdFind_Click(ByVal sender As System.Object,
System.EventArgs) Handles CmdFind.Click
FindRecord(txtSQL.Text)
End Sub ByVal e As
推荐阅读：
sql常用语句一览05-28
经典常用sql语句03-12
常用sql语句（精选8篇）11-15
常用简单sql语句（精选6篇）06-02
常用经典sql语句（精选6篇）07-31
常用的sql语句（推荐6篇）03-07
vf常用sql语句（通用6篇）03-18
vf常用sql语句大全（共4篇）06-01
英语常用赞美语句06-28
vb常用语句04-10
