高級審計培訓(xùn)SQL入門講義

《高級審計培訓(xùn)SQL入門講義》由會員分享，可在線閱讀，更多相關(guān)《高級審計培訓(xùn)SQL入門講義（94頁珍藏版）》請在裝配圖網(wǎng)上搜索。

1、Click to edit Title Slide,Click to edit Master text styles,Second level,Third level,Fourth level,Fifth level,SQL Server 2000,本機,IP,：,192.168.0.102,基本概念,查詢語句,主要內(nèi)容,數(shù)據(jù)庫類型,實體關(guān)系模型,數(shù)據(jù)庫基本知識,SQL server 2000,安裝與使用,創(chuàng)建數(shù)據(jù)庫、表和字段,Select,語句、連接、分組和函數(shù),SQL server,數(shù)據(jù)導(dǎo)入,/,導(dǎo)出工具,AO,的,SQL,語句操作,第一部分基本概念,一、數(shù)據(jù)庫是什么,數(shù)據(jù)庫是相互關(guān)聯(lián)的數(shù)

2、據(jù)的集合。,具有較小的數(shù)據(jù)冗余，,可供多個用戶共享，,具有較高的數(shù)據(jù)獨立性，,具有安全控制機制，,能夠保證數(shù)據(jù)的安全、可靠，,允許并發(fā)地使用數(shù)據(jù)庫，能有效、及時地處理數(shù)據(jù)，,能保證數(shù)據(jù)的一致性和完整性。,基本概念,實體關(guān)系模型,數(shù)據(jù)的三種范疇,現(xiàn)實,信息,電腦,抽取,存儲,實例化,數(shù)據(jù)模型是用來抽象、表示和處理現(xiàn)實世界中的數(shù)據(jù)和信息的。即，數(shù)據(jù)模型就是對現(xiàn)實世界的模擬。（如：數(shù)據(jù)庫、文件、文檔）,基本概念,數(shù)據(jù)庫關(guān)鍵概念,實體,：,客觀存在并可以相互區(qū)分的客觀事物或抽象事件稱為實體。,屬性,：,描述實體的特性。如職工的職工號，姓名，性別，出生日期，職稱等。 關(guān)鍵字：如果某個屬性或?qū)傩越M合的值能

3、唯一地標(biāo)識出實體集中的每一個實體，可以選作關(guān)鍵字。,聯(lián)系,：,實體集之間的對應(yīng)關(guān)系稱為聯(lián)系，它反映現(xiàn)實世界事物之間的相互關(guān)聯(lián),基本概念,聯(lián)系的三種類型,1,）一對一聯(lián)系（,1:1,）,2,）一對多聯(lián)系（,1:n,）,3,）多對多聯(lián)系（,m:n,）,基本概念,二、,數(shù)據(jù)模型,為了準(zhǔn)確地反映事物本身及事物之間的各種聯(lián)系，數(shù)據(jù)庫中的數(shù)據(jù)必須有一定的結(jié)構(gòu),。,模型結(jié)構(gòu)包括,:,1,、層次模型、,2,、網(wǎng)狀模型、,3,、關(guān)系模型、,4,、面向?qū)ο竽Ｐ?數(shù)據(jù)模型還包括：,數(shù)據(jù)操作和完整性,基本概念,數(shù)據(jù)庫發(fā)展趨勢,后關(guān)系型數(shù)據(jù)庫,XML,SQL,審計數(shù)據(jù)整合,基本概念,三、常見關(guān)系型數(shù)據(jù)庫,SQL ser

4、ver,Oracle,SyBase,DB2,Access,基本概念,數(shù)據(jù)庫系統(tǒng)的組成,DBMS,DB,DBA,APP,SYSTEM,基本概念,第二部分：,SQL server 2000,安裝,企業(yè)管理器,查詢分析器,數(shù)據(jù)庫,SQL DB,數(shù)據(jù)庫數(shù)據(jù)查詢過程,提交查詢語句,返回查詢結(jié)果,第三部分,SQL,基本知識,3.1,SQL,的動詞,SQL,功能,命令動詞,數(shù)據(jù)查詢,SELECT,數(shù)據(jù)定義,CREATE,、,DROP,、,ALTER,數(shù)據(jù)操縱,INSERT,、,UPDATE,、,DELETE,數(shù)據(jù)控制,GRANT,、,REVOKE,、,DENY,3.2 SQL Server,注釋,行內(nèi)注釋：

5、,-,塊注釋：,/* */,3.3 SQL,數(shù)據(jù)類型,1.,數(shù)值型,:,int,numeric,float,2.,字符串型,:,char,varchar,3.,日期、時間型,:,datetime,4.,貨幣類型,:,3.3.1,數(shù)值型,定長,（準(zhǔn)確表達）,整數(shù),：,INT,（全字長,32,位），,SMALLINT,（半字長）,小數(shù),：,NUMERIC,（,p,q,）或,DECIMAL,（,p,q,），,其中：,p,為數(shù)字位長度，,q,：小數(shù)位長度。,浮點,（近似）數(shù)：,FLOAT,、,REAL,等,3.3.2.,字符串型,CHAR,（,n,）,:,定長存儲，,n8000,VARCHAR,（,n

6、,）,：不定長存儲（按實際長度存儲），長度最大不超過,n,3.3.3.,日期時間型,DATE TIME,（,8,字節(jié)）：年月日時分秒毫秒,（例：,2001/08/03 10:30:00 000,）,SmallDateTime,（,4,字節(jié)）：年月日時分（例：,2001/08/03 10:30:00,）,4.,貨幣類型,表示正的或負的貨幣值,Money,：精確到貨幣單位的千分之十。存儲大小為,8,個字節(jié)。,Smallmoney,：精確到貨幣單位的千分之十。存儲大小為,4,個字節(jié)。,限制到小數(shù)點后,4,位。,貨幣數(shù)據(jù)不需要用單引號,(),括起來。但是，貨幣數(shù)值之前必須帶有適當(dāng)?shù)呢泿欧枴＠?，若?/p>

7、指定,100,英鎊，請使用,100,。,3.4,數(shù)據(jù)定義功能,數(shù)據(jù)庫的定義,基本表的定義與刪除,3.4.1,數(shù)據(jù)庫的定義,create database,DB_NAME,ON( NAME=,數(shù)據(jù)文件邏輯名, FILENAME=,物理存儲位置, SIZE=,初始大小,MAXSIZE=,最大大小,FILEGROWTH=,增長方式,) LOG ON,參數(shù)同上,數(shù)據(jù)庫的定義例,CREATE DATABASE,XShGL,ON( NAME=,XShGL_data, FILENAME=,c:mssqldataXShGL.mdf, SIZE=5MB,MAXSIZE=15MB,FILEGROWTH=20%)

8、LOG ON,(NAME=,XShGL_log, FILENAME=,c:mssqldataXShGL.ldf, SIZE=2MB,MAXSIZE=5MB,FILEGROWTH=1MB),CREATE TABLE,（列定義,|,約束定義）,（,1,）表名：表達本表應(yīng)用語義的字串。,（,2,）列定義（單列定義序列）：單列定義,，單列定義，, ,單列定義由列名、列類型、尺寸、列取值約束，列缺省值子句組成（用空格分隔）,3.4.2,表定義,約束定義,列取值約束,Not null,default(,值,),表主碼約束,Primary key,（,）,外碼引用約束（外碼主表數(shù)據(jù)引用）,FOREIGN K

9、EY,（,）,REFERENCES,（,）,例：定義學(xué)生表,(,學(xué)號,，姓名，性別，年齡、所在系）,CREATE TABLE,Student,(,iSno,int,primary key,cName,char(10) not null,iSex,int,default(0),dtBirthDay,DateTime,cDept,varchar(64),),定義課程表,（,課程號,，課程名，學(xué)分，學(xué)期）,Create Table,Course,(,iNo,int,not null,cName,varchar(32) not null,nCredit,numeric(6,1) not null,-,

10、學(xué)分,iSemester,int,-,學(xué)期,primary key,(,iCno,),),學(xué)生修課成績表（,學(xué)號,，,課程號,，成績）,Create Table,Score,(,iSNo,int,not null,iCNo,int,not null,nGrade,numeric(4,1) default(0),primary key,(,iSno,iCno,),foreign key,(,iSno,),references,Student(iSno,),foreign key,(,iCno,),references,Course(iCno,),),練習(xí)：,1,、創(chuàng)建財務(wù)數(shù)據(jù)庫：,CWDB,2,

11、、創(chuàng)建科目代碼表：,KMB,3,、創(chuàng)建憑證主表,：,PZ,4,、創(chuàng)建憑證明細表,:PZMX,參見練習(xí)一,3.5,刪除數(shù)據(jù)庫,從企業(yè)管理器的相關(guān)對象上右鍵，選擇刪除即可。,或者使用,SQL,語句,3.6,刪除表,從企業(yè)管理器對象樹選擇需要刪除的表，右鍵，選擇,【,刪除,】,或者,SQL,Drop table Student;,3.7,追加、修改、刪除字段,從企業(yè)管理器對象樹選擇需要編輯的表，右鍵，選擇,【,設(shè)計表,】,，進行字段修改。,或者,SQL,alter table student add,myCol,int,;,alter table student alter column,myCol

12、,varchar(12);,alter table student drop column,myCol,;,4.1,基本表數(shù)據(jù)的插入,一單行數(shù)據(jù)的插入,第四部分,SQL,語句,一單行數(shù)據(jù)的插入,插入單行記錄的,INSERT,語句的格式為：,INSERT INTO VALUES,（值表）,功能：新增一個符合表結(jié)構(gòu)的數(shù)據(jù)行，將值表數(shù)據(jù)按表中列定義順序,或列名表順序,賦給對應(yīng)列名。,說明：,1.,列名表與值表：,列名必須在表中已定義，值可取常量或,NULL,。,2.,賦值規(guī)則：,(1),值與列名按順序?qū)?yīng)，要求值類型與列數(shù)據(jù)類型一致。,(2),對語句中無值對應(yīng)的列名賦,NULL,。,3.,如果,IN

13、TO,子句中沒有指明列名，則新插入記錄的值的順序必須與表中列的順序一致，且每一列均有值（可為空）。,例,1,將新生記錄（,95020,，陳冬，男，信息系，,18,歲）插入到,Student,表中,INSERT INTO Student,VALUES,（,1,，,陳冬,，,0,，,1976-03-20,IS,）,例,2,在,SC,表中插入一新記錄，成績暫缺,INSERT INTO,SCore(iSno,iCno,iGrade,) VALUES(,1,1,100,),注：,此時必須列出列名（因為有缺省）,SC,中的,Grade,必須允許為,0,實際插入的值為：,(,95020,1,，,0),練習(xí)二

14、,4.2,數(shù)據(jù)查詢功能,1.,簡單,SQL,語句,SELECT,（需要哪些列）,FROM,（從哪些表）,WHERE,（根據(jù)什么條件）,查詢過程,Select PZH,KMDH,KMMC,DF,from,ACCTTwo,where DF0,2.,查詢所有數(shù)據(jù),Select * from,AcctOne,3.,選擇需要的列,select PZH,FSRQ,SHY,from,AcctOne,4.,給列取別名（重命名）,select PZH as,憑證號,FSRQ as,發(fā)生日期,SHY as,審計員,from,AcctOne,5.,選擇數(shù)據(jù)行,使用,Where,條件判斷句,比較運算,1.,數(shù)值類型的

15、數(shù)據(jù)判斷：,=,=,2.,日期類型判斷：,=,=,3.,字符串判斷：,like,=,not like,4.,集合判斷,:,in,not,in,5.,空值判斷,:is null,6.,確定范圍,:between and ,6.,數(shù)值條件：查詢借方金額大于,10000,的分錄,select *,from,accttwo,where JF=10000,7.,日期條件：查詢,2005,年上半年的憑證,S,elect *,from,acctOne,where FSRQ=2005-7-1,12.2,邏輯運算或者,所有發(fā)生額大于,10000,的分錄,select *,from,AcctTwo,where J

16、F=10000 or DF=10000,12.3,刪除結(jié)果集的重復(fù)行,Distinct,查看余額表余額方向的樣本,select distinct JDFX from KMYE,13,對查詢結(jié)果排序,可對查詢結(jié)果進行排序,排序子句為：,ORDER BY , ASC | DESC ,說明：按,進行升序（,ASC,）或降序（,DESC,）排序。,排序舉例：,按照借方金額進行排序,S,elect *,F,rom,AcctTwo,O,rder by JF-,從小到大排序,S,elect *,F,rom,AcctTwo,O,rder by JF,Desc,-,從大到小排序,14.,獲得查詢的前,n,行,獲

17、取借方金額前,10,名的分錄,Select top 10 *,from,AcctTwo,Order by JF,desc,15. SQL,提供的計算函數(shù),COUNT( * ),：統(tǒng)計表中元組個數(shù),COUNT(),：統(tǒng)計本列非空列值個數(shù),SUM(),：,計算列值總和（,必須是數(shù)值型列,）,AVG(),：,計算列值平均值（,必須是數(shù)值型列,）,MAX(),：求列值最大值,MIN(),：求列值最小值,15.1.,查詢記錄數(shù),Select count(*) as,記錄數(shù),from,AcctOne,15.2,求最大值和最小值,Select,max(JF,) as,最大值,min(JF,) as,最小值,

18、from,AcctTWO,Select,max(JF,) as,最大值,min(JF,) as,最小值,from,AcctTWO,Where JF0,S,elect *,into Temp1,F,rom,AcctTWO,W,here YF=1,16,查詢結(jié)果保存到新表,S,elect *,into Temp2,F,rom,AcctTWO,W,here YF=2,例：,1,季度憑證詳細信息保存到,3,張臨時表,S,elect *,into Temp3,F,rom,AcctTWO,W,here YF=3,Select YF,PZH,KMDH, JF,DF from temp1,Union,Sele

19、ct YF,PZH,KMDH, JF,DF from temp2,Union,Select YF,PZH,KMDH, JF,DF from temp3,17,多個表合并,例：將,1,月、,2,月憑證表合并成一張表,五,.,對查詢結(jié)果分組,作用：可以分別對每張憑證進行借貸平衡計算。,對每一組數(shù)據(jù)進行求最大值，最小值，平均值，求和，計數(shù)等。,分組語句的一般形式：,GROUP BY ,HAVING ,例：,請檢算憑證表的借貸是否平衡。分析,：,（,1,）,檢查全部數(shù)據(jù)是否平衡（,2,）檢查各月是否平衡（,3,）檢查各憑證是否借貸平衡（,4,）顯示借貸不平衡的憑證的詳細信息,（,1,）,檢查全部數(shù)據(jù)是

20、否平衡,Select,sum(JF,) as,借方合計，,sum(DF,) as,貸方合計,From,AcctTwo,（,2,）檢查各月是否平衡,select,YF,sum(JF,) as,借方,sum(DF,) as,貸方,from,AcctTwo,group by,YF,Having,sum(JF,)!=,sum(DF,),Having,abs(sum(JF)-sum(DF,)0.001,直接顯示有哪些借貸不平衡的月份,select,YF,sum(JF,) as,借方,sum(DF,) as,貸方,from,AcctTwo,group by,YF,浮點數(shù)不是精確數(shù)，不能直接使用等號進行比較

21、,select,YF, PZH,，,sum(JF,) as,借方,sum(DF,) as,貸方,from,AcctTwo,group by,YF,，,PZH,Having,abs(sum(JF)-sum(DF,)0.001,（,3,）,檢算每張憑證借貸平衡情況,S,elect *,F,rom,AcctTWO,W,here YF=?,A,nd PZH=?,（,4,）顯示借貸不平衡的憑證的詳細信息,4.8,條件篩選和分組,1,月份各科目余額,Select,KMDH,sum(JF),sum(DF,),From,AcctTwo,Where YF=1,Group by KMDH,先篩選，后分組，再篩選，

22、再選擇,條件篩選和組篩選,驗證分錄數(shù)大于,3,條的，,6,月份的憑證借貸發(fā)生額,Select,PZH,sum(JF),sun(DF,),From,AcctTwo,Where YF,6,Group by PZH,Having count(*)3,4.3,多表連接查詢,若一個查詢同時涉及兩個或兩個以上的表，則稱之為連接查詢。,連接查詢是關(guān)系數(shù)據(jù)庫中最主要的查詢,連接查詢包括內(nèi)連接、外連接和交叉連接等。,理解表連接,Table1,A,B,100,200,101,300,102,400,Table2,X,Y,Z,101,abc,56.89,102,def,256.41,新表,A,B,X,Y,Z,100

23、,200,101,abc,56.89,100,200,102,def,256.41,101,300,101,abc,56.89,101,300,102,def,256.41,102,400,101,abc,56.89,102,400,102,def,256.41,Join,例：,查看每張憑證的憑證日期，摘要和金額,Select,a.YF,z.PZH.a.FSRQ,a.ZY,b.JF,b.DF,From,AcctOne,a,Join,AcctTwo,b,On,a.YF,=,b.YF,and,a.PZH,=,b.PZH,例：按以下形式顯示,1,季度各月各科目余額（假設(shè)各月各科目均有發(fā)生額）,KMD

24、H,1,月余額,2,月余額,3,月余額,1011,1014,（,1,）一月各科目發(fā)生額,Select,KMDH,sum(JF,) -,sum(DF,) as,余額,Into tmpYE1,from ACCTTWO,where YF=1,Group by KMDH,（,2,）二月各科目發(fā)生額,Select,KMDH,sum(JF,) -,sum(DF,) as,余額,I,nto tmpYE2,from ACCTTWO,where YF=2,Group by KMDH,（,3,）一月各科目發(fā)生額,Select,KMDH,sum(JF,) -,sum(DF,) as,余額,I,nto tmpYE3,

25、from ACCTTWO,where YF=3,Group by KMDH,（,4,）合并各月余額表,select,a.KMDH,a.,余額,as 1,月,b.,余額,as 2,月,c.,余額,as 3,月,from tmpYE1 a,join tmpYE2 b on,a.KMDH,=,b.KMDH,join tmpYE3 c on,a.KMDH,=,c.KMDH,例：從學(xué)生數(shù)據(jù)庫，查詢學(xué)生各科成績，要求顯示學(xué)生的姓名，科目和成績,select,s.cName,c.cName,sc.nScore,from student s,join,SCore,sc on,s.iSNo,=,sc.iSno,

26、join course c on,Sc.iCno,=,c.iNo,4.4,外連接,外連接會返回,FROM,子句中提到的至少一個表的所有行，只要這些行符合任何,WHERE,或,HAVING,搜索條件。,分類：,LEFT OUTER JOIN,或,LEFT JOIN,：左外連接,RIGHT OUTER JOIN,或,RIGHT JOIN,：右外連接,1.,使用左外連接,左向外連接運算符,LEFT OUTER JOIN,指明，不管第二個表中是否有匹配的數(shù)據(jù)，結(jié)果將包含第一個表中的所有行。,運算過程：,（,1,）首先將,2,張表完全連接；,（,2,）在完全連接的表中，保留那些符合條件的數(shù)據(jù)行，刪除不符

27、合條件的數(shù)據(jù)行。,（,3,）在完全連接的表中，如果左邊表某行與右邊表中任何一行都不符合匹配條件，則保留左邊表的那一行數(shù)據(jù)，對應(yīng)右邊表的數(shù)據(jù)列全部為,null,。,例：,在學(xué)生數(shù)據(jù)庫中查詢沒有選課的學(xué)生,Select s.*,From student s,left join score sc on,s.isno,=,sc.isno,Where,sc.isno,is null,例,查詢沒有被學(xué)生選學(xué)的課程,Select c.*,From Course c,Left join score sc on,c.iCno,=,sc.iCno,Where,sc.iCno,is null,例：,從某財務(wù)數(shù)據(jù)庫,

28、xData,提取科目表，驗證科目表的科目是否完整。,分析,:,在該未知財務(wù)數(shù)據(jù)中，沒有明確的科目表，需要進行提取。但在憑證明細表中和科目余額表中，均有科目代碼和科目名稱字段。,因此科目表可以從此兩表中提取。兩個表提取的科目表到底哪一個涵蓋了哪一個？還是互相不涵蓋？需要逐一驗證。,過程：,（,1,）分別提取科目表,KMA,，,KMB,（,2,）使用外連接，,KMA,是否存在比,KMB,多出的科目,（,3,）使用外連接，,KMB,是否存在比,KMA,多出的科目,（,4,）根據(jù)分析結(jié)果，選用科目表,（,1,）分別提取科目表,KMA,，,KMB,Select distinct KMDH,KMMC,in

29、to KMA,from ACCTTWO,select distinct KMDH,KMMC,into KMB,from KMYE,（,2,）使用外連接，,KMA,是否存在比,KMB,多出的科目,select *,from KMA a left join KMB b,on,a.KMDH,=,b.KMDH,where,b.KMDH,is null,（,3,）使用外連接，,KMB,是否存在比,KMA,多出的科目,select *,from KMB a left join KMA b on,a.KMDH,=,b.KMDH,where,b.KMDH,is null,（,4,）根據(jù)分析結(jié)果，選用科目表,.

30、,分析結(jié)果：,KMA,表的所有行含在,KMB,中，,KMB,中有,38,行不含在,KMA,中。因此,KMB,可以當(dāng)作科目代碼表使用。,4.5,刪除數(shù)據(jù),1,、刪除表內(nèi)全部數(shù)據(jù),Delete Temp1,2,、按照條件刪除,Delete student where,cName,=,游七,4.6,批量插入數(shù)據(jù),將查詢語句的結(jié)果插入到一個已經(jīng)創(chuàng)建數(shù)據(jù)表中。,Insert into,student_BK(iSno,cName,dtBirthay,iSex,),Select,iSno,cName,dtBirthay,iSex,from student,where,dtBirthay,1982-1-1,4

31、.7,表中列數(shù)據(jù)更新,格式,：,UPDATE ,SET ,WHERE ,例,：將所有學(xué)生的成績加一分,Update Score set,nScore,=nScore+1,例,：將高等數(shù)據(jù)的學(xué)分加一分,Update Score set,nGrade,=nGrade+1,Where,iCno,=2,例,：將摘要為空的摘要改成“無摘要”,Update,AcctOne,set ZY=,無摘要,Where ZY is null,第五部分,SQL server,2000,數(shù)據(jù)與,Access,數(shù)據(jù)遷移,一、從,SQL,導(dǎo)出,Access,（,1,）創(chuàng)建空的,Access,文件,（,2,）,【,程序,】,【

32、Microsoft SQL server】,【,導(dǎo)入和導(dǎo)出數(shù)據(jù),】,二、從,Access,到,SQL server 2000,（,1,）在,SQL server,創(chuàng)建目標(biāo)數(shù)據(jù)庫,（,2,）,【,程序,】,【Microsoft SQL server】,【,導(dǎo)入和導(dǎo)出數(shù)據(jù),】,第六部分,AO2008,單機版項目數(shù)據(jù)簡介,1,、系統(tǒng)數(shù)據(jù)庫：,查看本地項目情況,C:Program Files,現(xiàn)場審計實施系統(tǒng),2008,版,System,DataBaseAOBaseDB.mdb,2,、項目數(shù)據(jù)庫：,項目帳套信息,C:Program Files,現(xiàn)場審計實施系統(tǒng),2008,版,Project12,12SJFXBase.mdb,3,、,審計分析數(shù)據(jù)庫,：,C:Program Files,現(xiàn)場審計實施系統(tǒng),2008,版,Project12312DataBaseSJFX1SJFX.mdb,直接從審計分析數(shù)據(jù)庫進行數(shù)據(jù)分析,一般使用查詢分析面板進行語句編寫,謝謝,2008,年,1,月,6,日,