高级审计培训SQL入门讲义

,SQLServer2000,本机IP：02,基本概念,查询语句,主要内容,数据库类型实体关系模型数据库基本知识,SQLserver2000安装与使用创建数据库、表和字段Select语句、连接、分组和函数SQLserver数据导入/导出工具AO的SQL语句操作,第一部分基本概念一、数据库是什么,数据库是相互关联的数据的集合。具有较小的数据冗余，可供多个用户共享，具有较高的数据独立性，具有安全控制机制，能够保证数据的安全、可靠，允许并发地使用数据库，能有效、及时地处理数据，能保证数据的一致性和完整性。,基本概念,实体关系模型,数据的三种范畴,现实,信息,电脑,抽取,存储,实例化,数据模型是用来抽象、表示和处理现实世界中的数据和信息的。即，数据模型就是对现实世界的模拟。（如：数据库、文件、文档）,基本概念,数据库关键概念,实体：客观存在并可以相互区分的客观事物或抽象事件称为实体。属性：描述实体的特性。如职工的职工号，姓名，性别，出生日期，职称等。关键字：如果某个属性或属性组合的值能唯一地标识出实体集中的每一个实体，可以选作关键字。联系：实体集之间的对应关系称为联系，它反映现实世界事物之间的相互关联,基本概念,联系的三种类型,1）一对一联系（1:1）2）一对多联系（1:n）3）多对多联系（m:n）,基本概念,二、数据模型,为了准确地反映事物本身及事物之间的各种联系，数据库中的数据必须有一定的结构。模型结构包括:1、层次模型、2、网状模型、3、关系模型、4、面向对象模型数据模型还包括：数据操作和完整性,基本概念,数据库发展趋势,后关系型数据库XMLSQL审计数据整合,基本概念,三、常见关系型数据库,SQLserverOracleSyBaseDB2Access,基本概念,数据库系统的组成,DBMSDBDBAAPPSYSTEM,基本概念,第二部分：SQLserver2000安装,企业管理器查询分析器数据库,SQLDB,数据库数据查询过程,提交查询语句,返回查询结果,第三部分SQL基本知识3.1SQL的动词,3.2SQLServer注释,行内注释：-块注释：/*/,3.3SQL数据类型,1.数值型:int,numeric,float2.字符串型:char,varchar3.日期、时间型:datetime4.货币类型:,3.3.1数值型,定长（准确表达）整数：INT（全字长32位），SMALLINT（半字长）小数：NUMERIC（p,q）或DECIMAL（p,q），其中：p为数字位长度，q：小数位长度。浮点（近似）数：FLOAT、REAL等,3.3.2.字符串型,CHAR（n）:定长存储，n0,2.查询所有数据,Select*fromAcctOne,3.选择需要的列,selectPZH,FSRQ,SHYfromAcctOne,4.给列取别名（重命名）,selectPZHas凭证号,FSRQas发生日期,SHYas审计员fromAcctOne,5.选择数据行,使用Where条件判断句,比较运算1.数值类型的数据判断：=,=,2.日期类型判断：=,=,3.字符串判断：like,=,notlike4.集合判断:in,notin5.空值判断:isnull6.确定范围:betweenand,6.数值条件：查询借方金额大于10000的分录,select*fromaccttwowhereJF=10000,7.日期条件：查询2005年上半年的凭证,Select*fromacctOnewhereFSRQ=2005-7-1,12.2逻辑运算或者所有发生额大于10000的分录,select*fromAcctTwowhereJF=10000orDF=10000,12.3删除结果集的重复行,Distinct,查看余额表余额方向的样本selectdistinctJDFXfromKMYE,13对查询结果排序,可对查询结果进行排序排序子句为：ORDERBY,ASC|DESC说明：按进行升序（ASC）或降序（DESC）排序。,排序举例：按照借方金额进行排序,Select*FromAcctTwoOrderbyJF-从小到大排序,Select*FromAcctTwoOrderbyJFDesc-从大到小排序,14.获得查询的前n行,获取借方金额前10名的分录Selecttop10*fromAcctTwoOrderbyJFdesc,15.SQL提供的计算函数,COUNT(*)：统计表中元组个数COUNT()：统计本列非空列值个数SUM()：计算列值总和（必须是数值型列）AVG()：计算列值平均值（必须是数值型列）MAX()：求列值最大值MIN()：求列值最小值,15.1.查询记录数,Selectcount(*)as记录数fromAcctOne,15.2求最大值和最小值,Selectmax(JF)as最大值,min(JF)as最小值fromAcctTWO,Selectmax(JF)as最大值,min(JF)as最小值fromAcctTWOWhereJF0,Select*intoTemp1FromAcctTWOWhereYF=1,16查询结果保存到新表,Select*intoTemp2FromAcctTWOWhereYF=2,例：1季度凭证详细信息保存到3张临时表,Select*intoTemp3FromAcctTWOWhereYF=3,SelectYF,PZH,KMDH,JF,DFfromtemp1UnionSelectYF,PZH,KMDH,JF,DFfromtemp2UnionSelectYF,PZH,KMDH,JF,DFfromtemp3,17多个表合并,例：将1月、2月凭证表合并成一张表,五.对查询结果分组,作用：可以分别对每张凭证进行借贷平衡计算。对每一组数据进行求最大值，最小值，平均值，求和，计数等。分组语句的一般形式：GROUPBYHAVING,例：请检算凭证表的借贷是否平衡。分析：（1）检查全部数据是否平衡（2）检查各月是否平衡（3）检查各凭证是否借贷平衡（4）显示借贷不平衡的凭证的详细信息,（1）检查全部数据是否平衡,Selectsum(JF)as借方合计，sum(DF)as贷方合计FromAcctTwo,（2）检查各月是否平衡,selectYF,sum(JF)as借方,sum(DF)as贷方fromAcctTwogroupbyYFHavingsum(JF)!=sum(DF)Havingabs(sum(JF)-sum(DF)0.001,直接显示有哪些借贷不平衡的月份,selectYF,sum(JF)as借方,sum(DF)as贷方fromAcctTwogroupbyYF,浮点数不是精确数，不能直接使用等号进行比较,selectYF,PZH，sum(JF)as借方,sum(DF)as贷方fromAcctTwogroupbyYF，PZHHavingabs(sum(JF)-sum(DF)0.001,（3）检算每张凭证借贷平衡情况,Select*FromAcctTWOWhereYF=?AndPZH=?,（4）显示借贷不平衡的凭证的详细信息,4.8条件筛选和分组,1月份各科目余额,SelectKMDH,sum(JF),sum(DF)FromAcctTwoWhereYF=1GroupbyKMDH,先筛选，后分组，再筛选，再选择,条件筛选和组筛选,验证分录数大于3条的，6月份的凭证借贷发生额,SelectPZH,sum(JF),sun(DF)FromAcctTwoWhereYF6GroupbyPZHHavingcount(*)3,4.3多表连接查询,若一个查询同时涉及两个或两个以上的表，则称之为连接查询。连接查询是关系数据库中最主要的查询连接查询包括内连接、外连接和交叉连接等。,理解表连接,Join,例：查看每张凭证的凭证日期，摘要和金额,Selecta.YF,z.PZH.a.FSRQ,a.ZY,b.JF,b.DFFromAcctOneaJoinAcctTwobOna.YF=b.YFanda.PZH=b.PZH,例：按以下形式显示1季度各月各科目余额（假设各月各科目均有发生额）,（1）一月各科目发生额,SelectKMDH,sum(JF)-sum(DF)as余额IntotmpYE1fromACCTTWOwhereYF=1GroupbyKMDH,（2）二月各科目发生额,SelectKMDH,sum(JF)-sum(DF)as余额IntotmpYE2fromACCTTWOwhereYF=2GroupbyKMDH,（3）一月各科目发生额,SelectKMDH,sum(JF)-sum(DF)as余额IntotmpYE3fromACCTTWOwhereYF=3GroupbyKMDH,（4）合并各月余额表,selecta.KMDH,a.余额as1月,b.余额as2月,c.余额as3月fromtmpYE1ajointmpYE2bona.KMDH=b.KMDHjointmpYE3cona.KMDH=c.KMDH,例：从学生数据库，查询学生各科成绩，要求显示学生的姓名，科目和成绩,selects.cName,c.cName,sc.nScorefromstudentsjoinSCorescons.iSNo=sc.iSnojoincourseconSc.iCno=c.iNo,4.4外连接,外连接会返回FROM子句中提到的至少一个表的所有行，只要这些行符合任何WHERE或HAVING搜索条件。分类：LEFTOUTERJOIN或LEFTJOIN：左外连接RIGHTOUTERJOIN或RIGHTJOIN：右外连接,1.使用左外连接,左向外连接运算符LEFTOUTERJOIN指明，不管第二个表中是否有匹配的数据，结果将包含第一个表中的所有行。运算过程：（1）首先将2张表完全连接；（2）在完全连接的表中，保留那些符合条件的数据行，删除不符合条件的数据行。（3）在完全连接的表中，如果左边表某行与右边表中任何一行都不符合匹配条件，则保留左边表的那一行数据，对应右边表的数据列全部为null。,例：,在学生数据库中查询没有选课的学生,Selects.*Fromstudentsleftjoinscorescons.isno=sc.isnoWheresc.isnoisnull,例,查询没有被学生选学的课程,Selectc.*FromCoursecLeftjoinscoresconc.iCno=sc.iCnoWheresc.iCnoisnull,例：,从某财务数据库xData提取科目表，验证科目表的科目是否完整。分析:在该未知财务数据中，没有明确的科目表，需要进行提取。但在凭证明细表中和科目余额表中，均有科目代码和科目名称字段。因此科目表可以从此两表中提取。两个表提取的科目表到底哪一个涵盖了哪一个？还是互相不涵盖？需要逐一验证。过程：（1）分别提取科目表KMA，KMB（2）使用外连接，KMA是否存在比KMB多出的科目（3）使用外连接，KMB是否存在比KMA多出的科目（4）根据分析结果，选用科目表,（1）分别提取科目表KMA，KMB,SelectdistinctKMDH,KMMCintoKMAfromACCTTWO,selectdistinctKMDH,KMMCintoKMBfromKMYE,（2）使用外连接，KMA是否存在比KMB多出的科目,select*fromKMAaleftjoinKMBbona.KMDH=b.KMDHwhereb.KMDHisnull,（3）使用外连接，KMB是否存在比KMA多出的科目,select*fromKMBaleftjoinKMAbona.KMDH=b.KMDHwhereb.KMDHisnull,（4）根据分析结果，选用科目表.分析结果：KMA表的所有行含在KMB中，KMB中有38行不含在KMA中。因此KMB可以当作科目代码表使用。,4.5删除数据,1、删除表内全部数据DeleteTemp12、按照条件删除DeletestudentwherecName=游七,4.6批量插入数据,将查询语句的结果插入到一个已经创建数据表中。Insertintostudent_BK(iSno,cName,dtBirthay,iSex)SelectiSno,cName,dtBirthay,iSexfromstudentwheredtBirthay1982-1-1,4.7表中列数据更新,格式：UPDATESET,WHERE,例：将所有学生的成绩加一分,UpdateScoresetnScore=nScore+1,例：将高等数据的学分加一分,UpdateScoresetnGrade=nGrade+1WhereiCno=2,例：将摘要为空的摘要改成“无摘要”,UpdateAcctOnesetZY=无摘要WhereZYisnull,第五部分SQLserver2000数据与Access数据迁移,一、从SQL导出Access,（1）创建空的Access文件（2）【程序】【MicrosoftSQLserver】【导入和导出数据】,二、从Access到SQLserver2000,（1）在SQLserver创建目标数据库（2）【程序】【MicrosoftSQL