XXXX年初級認證培訓-SQL基本開發(fā)(共96張)

用友政務A++咨詢實施部張海2010年5月10日A++認證培訓——SQL開發(fā)、財務數(shù)據(jù)字典及輸出報表定制目錄培訓前言數(shù)據(jù)庫及SQL概述常用SQL語法及函數(shù)詳細介紹輸出報表定制流程培訓前言培訓對象培訓目標

Oracle初學者一、培訓前言——培訓對象

通過本次培訓，對Oracle和SQL語言有一定的認識掌握SQL常用的語法和函數(shù)掌握財務常用的數(shù)據(jù)字典掌握輸出報表定制的一般流程一、培訓前言——培訓目標目錄培訓前言數(shù)據(jù)庫及SQL概述常用SQL語法及函數(shù)詳細介紹輸出報表定制流程數(shù)據(jù)庫及SQL概述數(shù)據(jù)庫發(fā)展常用數(shù)據(jù)庫Oracle產(chǎn)生與發(fā)展SQL語言介紹SQL語言組成及功能SQL開發(fā)工具介紹

數(shù)據(jù)庫發(fā)展常用數(shù)據(jù)庫a、桌面關系型數(shù)據(jù)庫系統(tǒng)：Access\visualfoxpro?；趙indows操作系統(tǒng)，滿足日常小型辦公需要，不提供或提供有限的網(wǎng)絡應用功能b、網(wǎng)絡關系型數(shù)據(jù)庫系統(tǒng)：MysqlSybasesqlserveroracledb2二、數(shù)據(jù)庫及SQL概述

Oracle產(chǎn)生和發(fā)展 目前應用比較廣泛的版本是Oracle9i(i代表Internet)和10g（g代表grid,網(wǎng)格

）,2007年7月推出oracle11g.二、數(shù)據(jù)庫及SQL概述

SQL語言介紹SQL——structuredquerylanguage高級非過程化語言用于存取數(shù)據(jù)以及查詢、更新和管理關系數(shù)據(jù)庫系統(tǒng)

二、數(shù)據(jù)庫及SQL概述

SQL語言組成與功能數(shù)據(jù)定義語言(DDL)，例如：CREATE、DROP、ALTER等語句數(shù)據(jù)操作語言(DML)，例如：INSERT（插入）、UPDATE（修改）、DELETE（刪除）語句數(shù)據(jù)查詢語言(DQL)，例如：SELECT語句數(shù)據(jù)控制語言(DCL)，例如：GRANT、REVOKE、COMMIT、ROLLBACK等語句二、數(shù)據(jù)庫及SQL概述

SQL開發(fā)工具SQLPlusPL/SQLDeveloper二、數(shù)據(jù)庫及SQL概述目錄培訓前言數(shù)據(jù)庫及SQL概述常用SQL語法及函數(shù)詳細介紹輸出報表定制流程常用SQL語法及函數(shù)詳細介紹DDL語句及操作DML語句及操作查詢語句單表查詢子查詢集合連接查詢DCL語句及操作函數(shù)介紹

CREATETABLE/DROPTABLE/ALTERTABLE語法：CREATETABLEtable_name(column_nametype[CONSTRAINTconstraint_def][DEFAULTdefault_exp],…);DESCRIBEtable_name;DROPTABLEtable_name;三、常用SQL語法及函數(shù)介紹——DDL

CREATETABLE/DROPTABLE/ALTERTABLE語法：ALTERTABLEtable_nameADD…;ALTERTABLEtable_nameDROP…;ALTERTABLEtable_nameMODIFY…;三、常用SQL語法及函數(shù)介紹——DDL

CREATEVIEW/DROPVIEW語法：CREATE[ORREPLACE]VIEWview_nameASsubquery[WITHREADONLY];DROPVIEWview_name;三、常用SQL語法及函數(shù)介紹——DDL

SELECT *|{[DISTINCT]column|expression[alias],...}FROM table;SELECT表示選擇哪些列FROM表示從哪些表中查詢指定列、全部列、去重（DISTINCT）、改變輸出（AS）連接符（||）算數(shù)表達式三、常用SQL語法及函數(shù)介紹——DQL三、常用SQL語法及函數(shù)介紹——DQLSELECTfiscal,coa_code,coa_nameFROMma_coa;--科目體系指定列SELECT*FROMma_coa;全部列三、常用SQL語法及函數(shù)介紹——DQLSELECTt.fiscalAS"年度",t.coa_codeAS"科目體系代碼",t.coa_name"科目體系名稱"FROMma_coat;改變輸出SELECTfiscal"年度",coa_code||'_'||coa_nameAS"科目體系"FROMma_coa;連接符三、常用SQL語法及函數(shù)介紹——DQLSELECTt.co_code,t.nd,t.co_name

FROMma_companyt–-單位信息表

ORDERBY1;去重SELECTDISTINCT(t.co_code),t.co_nameFROMma_companytORDERBY1;三、常用SQL語法及函數(shù)介紹——DQLSELECTf.co_code,f.card_id,f.fa_name,f.fa_item_n09+f.fa_item_n10+f.fa_item_n11+f.fa_item_n12+f.fa_item_n14AS"使用方向_面積合計"FROMfa_cardf--資產(chǎn)卡片算數(shù)表達式三、常用SQL語法及函數(shù)介紹——單表查詢

空值是一個不可用的,沒有被分配的,或者不能得到的值.空值不同于零或者空格.空值的產(chǎn)生：非主鍵、未定義notnull都有可能產(chǎn)生空值

SELECT*

FROMas_user;--用戶表定義空值Null三、常用SQL語法及函數(shù)介紹——單表查詢

在算術表達式中，與空值運算的結果為空?？罩狄矔硪恍┧惴ǖ母淖儯瑫箍焖偎饕L問失效。

SELECTf.co_code,f.card_id,f.fa_name,fa_item_n09,fa_item_n10,fa_item_n11,fa_item_n12,f.fa_item_n14,

f.fa_item_n09+f.fa_item_n10+f.fa_item_n11+f.fa_item_n12+f.fa_item_n14AS"使用方向_面積合計"FROMfa_cardf三、常用SQL語法及函數(shù)介紹——單表查詢

NVL()：如果為空，則賦予一個值空值處理

SELECTf.co_code,f.card_id,f.fa_name,fa_item_n09,fa_item_n10,NVL(fa_item_n11,0),fa_item_n12,f.fa_item_n14,

f.fa_item_n09+f.fa_item_n10+NVL(f.fa_item_n11,0)+f.fa_item_n12+f.fa_item_n14AS"使用方向_面積合計"FROMfa_cardf三、常用SQL語法及函數(shù)介紹——單表查詢

NVL2()空值處理

SELECTDISTINCT(user_id)user_id,user_nameuser_name,NVL2(pk_desc,oper_time||'_修改過憑證','未新增或修改憑證')is_modvouFROMas_log;--日志查詢三、常用SQL語法及函數(shù)介紹——單表查詢

在查詢中限制查詢得到的結果通過WHERE子句限制查到的結果.WHERE子句緊跟著FROM子句.使用比較符（=、<、<=、>、>=、<>）NOT、AND、OR（優(yōu)先級從高到低）SELECT *|{[DISTINCT]column|expression[alias],...}FROM table[WHERE condition(s)];三、常用SQL語法及函數(shù)介紹——單表查詢

字符串和日期數(shù)據(jù)字符串和日期數(shù)據(jù)應該使用單引號擴起來.字符數(shù)據(jù)大小寫敏感,日期數(shù)據(jù)格式敏感.SELECT*FROMma_coa_accWHEREcoa_code='001';--系統(tǒng)級科目表'SELECT*FROMma_coa_accWHEREacc_code<'201';SELECT*FROMma_coa_accWHEREacc_code<'201'ANDcoa_code='001';--小于201的行政科目SELECT*FROMma_coa_accWHERE(acc_code='107'ANDcoa_code='001')OR(acc_code='103'ANDcoa_code='002');--查詢零余額科目OperatorBETWEEN

...AND...IN(set)LIKEISNULLMeaning兩個值之間(包含),

匹配任何列表中的值模糊匹配字符串值是否空值三、常用SQL語法及函數(shù)介紹——單表查詢其他比較運算符SELECT*FROMgl_vou_headWHEREamt_drBETWEEN1000AND10000;--查詢憑證主表借方金額合計在1000到10000之間的憑證SELECT*FROMgl_vou_headWHERETO_CHAR(vou_date,'YYYY-MM-DD')BETWEEN'2010-01-01'AND'2010-01-05';--查詢憑證主表單據(jù)日期在2010-01-01到2010-01-05之間的憑證LowerlimitUpperlimit三、常用SQL語法及函數(shù)介紹——單表查詢使用BETWEEN運算符三、常用SQL語法及函數(shù)介紹——單表查詢使用LIKE

運算符執(zhí)行基于通配符的模糊查詢，查詢包含字符串的所有數(shù)據(jù).查詢條件可以包含數(shù)字或者字符文本:%

表示零個或者多個字符._

表示一個字符.SELECT*

FROMgl_coa_acc

WHEREco_code='315002'

ANDacc_codeLIKE'102%';使用LIKE運算符三、常用SQL語法及函數(shù)介紹——單表查詢SELECT*

FROMgl_coa_acc

WHEREco_code='315002'

ANDacc_codeLIKE'102_%';使用LIKE運算符SELECT*

FROMgl_coa_acc

WHEREco_code='315002'

ANDacc_codeLIKE'%102%';SELECT*

FROMgl_coa_acc

WHEREco_code='315002'

ANDpar_acc_codeISNULL;--父代碼為空的科目（一級科目）三、常用SQL語法及函數(shù)介紹——單表查詢使用ISNULL運算符三、常用SQL語法及函數(shù)介紹——單表查詢條件表達式提供了在SQL語句中使用IF-THEN-ELSE的方法.使用兩種方法:CASE表達式DECODE函數(shù)CASEexprWHENcomparison_expr1THENreturn_expr1

[WHENcomparison_expr2THENreturn_expr2WHENcomparison_exprnTHENreturn_exprnELSEelse_expr]ENDDECODE(col|expression,search1,result1

[,search2,result2,...,]

[,default])SELECTnd,co_code,account_name,CASEcoa_codeWHEN'001'THEN'行政科目體系'WHEN'002'THEN'事業(yè)科目體系'ENDASkmtxFROMma_co_acc;使用CASE表達式三、常用SQL語法及函數(shù)介紹——單表查詢SELECTco_code單位,account_id帳套,fiscal年度,CASEWHENoutlay_code='30201'THENstad_amtENDAS辦公費,CASEWHENoutlay_code='30202'THENstad_amtENDAS印刷費,CASEWHENoutlay_codeNOTIN('30202','30201')THENstad_amtENDAS其他FROMgl_vou_detail_assWHEREfiscal=2010;使用CASE表達式三、常用SQL語法及函數(shù)介紹——單表查詢行轉列應用使用DECODE函數(shù)SELECTnd,co_code,account_name,DECODE(coa_code,'001','行政科目體系','002','事業(yè)科目體系','其他')ASkmtxFROMma_co_acc;三、常用SQL語法及函數(shù)介紹——單表查詢三、常用SQL語法及函數(shù)介紹——單表查詢

排序——ORDERBYSELECT *|{[DISTINCT]column|expression[alias],...}FROM table[WHERE condition(s)][ORDERBYexpression1[ASC|DESC],…];單列排序多列排序三、常用SQL語法及函數(shù)介紹——單表查詢

排序——ORDERBYSELECTfiscal,coa_code,acc_code,acc_name

FROMma_coa_acc–-系統(tǒng)會計科目表

ORDERBYfiscalDESC,coa_code,acc_code;

--SELECTfiscal,coa_code,acc_code,acc_name

FROMma_coa_acc

ORDERBY1DESC,2,3;三、常用SQL語法及函數(shù)介紹——單表查詢

分組SELECT *|{[DISTINCT]column|expression[alias],...}FROM table[WHERE condition(s)][GROUPBYexpression,…][HAVINGcondition(s)]常用聚合函數(shù)：MAX()MIN()COUNT()SUM()AVG()三、常用SQL語法及函數(shù)介紹——單表查詢

分組——GROUPBY子句SELECTco_code單位,fiscal年度,bg_project_code預算方案,SUM(bg_amt)預算數(shù)FROMbg_project_detailGROUPBYco_code,fiscal,bg_project_code;--預算管理預算數(shù)查詢三、常用SQL語法及函數(shù)介紹——單表查詢

分組——GROUPBY子句SELECTco_code單位,fiscal年度,fis_perd期間,COUNT(1)憑證張數(shù)FROMgl_vou_headGROUPBYco_code,fiscal,fis_perdORDERBY1,2,3;--查詢單位每月憑證數(shù)三、常用SQL語法及函數(shù)介紹——單表查詢

分組——HAVING子句SELECTco_code單位,fiscal年度,bg_project_code預算方案,SUM(bg_amt)預算數(shù)FROMbg_project_detailGROUPBYco_code,fiscal,bg_project_codeHAVINGSUM(bg_amt)>150000ORDERBY4DESC;--預算數(shù)大于150000的方案子查詢子查詢（內部查詢）在主查詢執(zhí)行前執(zhí)行.子查詢的結果被主查詢（外部查詢）使用.SELECT select_listFROM tableWHERE exproperator(SELECTselect_list FROM table);三、常用SQL語法及函數(shù)介紹——子查詢SELECTm.co_code,m.co_nameFROMma_companymWHEREm.co_codeIN(SELECTco_codeFROMgl_vou_head);--查詢錄過憑證的單位三、常用SQL語法及函數(shù)介紹——子查詢子查詢SELECTco_code,fiscal,item_code,SUM(stad_amt)FROMgl_vou_detail_assWHEREco_code='315002'ANDitem_code=(SELECTitem_codeFROMgl_itemWHEREco_code='315002'ANDitem_name='隧道照明用電經(jīng)費')ANDfiscal=2010ANDacc_codeLIKE'50402%'ANDdr_cr='1'GROUPBYco_code,fiscal,item_code;三、常用SQL語法及函數(shù)介紹——子查詢子查詢查詢某一個項目的支出合計只返回一行使用單行比較操作符Operator=>>= <<= <>MeaningEqualtoGreaterthanGreaterthanorequaltoLessthanLessthanorequaltoNotequalto三、常用SQL語法及函數(shù)介紹——子查詢SELECTbg_project_code,bg_project_name

FROMbg_project

WHEREbg_project_code=

(SELECTbg_project_code

FROMbg_project_detail);下面的語句錯在哪里?ERRORatline4:ORA-01427:single-rowsubqueryreturnsmorethan

onerow對多行子查詢使用了單行比較操作符.三、常用SQL語法及函數(shù)介紹——子查詢返回多于一行使用多行比較操作符操作符

IN

含義等于列表中的任何值三、常用SQL語法及函數(shù)介紹——子查詢多列子查詢MainqueryWHERE(MANAGER_ID,DEPARTMENT_ID)INSubquery10090

10260

12450主查詢條件中的每列與多列子查詢返回的多行多列的結果進行比較.三、常用SQL語法及函數(shù)介紹——子查詢列的比較與多列子查詢的比較可以是:

成對比較--Pairwisecomparisons

非成對比較--Nonpairwisecomparisons

三、常用SQL語法及函數(shù)介紹——子查詢成對比較子查詢顯示所有與141和174相同部門、相同經(jīng)理的雇員的信息（不包括141和174雇員）.SELECT employee_id,manager_id,department_idFROM employeesWHERE(manager_id,department_id)IN(SELECTmanager_id,department_idFROMemployeesWHEREemployee_idIN(141,174))AND employee_idNOTIN(141,174);三、常用SQL語法及函數(shù)介紹——子查詢非成對比較子查詢SELECTemployee_id,manager_id,department_idFROMemployeesWHEREmanager_idIN

(SELECTmanager_idFROMemployeesWHEREemployee_idIN(174,141))ANDdepartment_idIN

(SELECTdepartment_idFROMemployeesWHEREemployee_idIN(174,141))

AND employee_idNOTIN(174,141);顯示所有與141和174相同部門或者相同經(jīng)理的雇員的信息（不包括141和174雇員）.三、常用SQL語法及函數(shù)介紹——子查詢相關子查詢相關子查詢被用于查詢執(zhí)行時的一行一行處理.每個相關子查詢對于外部查詢的每行執(zhí)行一次.GETcandidaterowfromouterqueryEXECUTEinnerqueryusingcandidaterowvalueUSEvaluesfrominnerquerytoqualifyordisqualifycandidaterow三、常用SQL語法及函數(shù)介紹——子查詢相關子查詢

SELECTcolumn1,column2,...FROMtable1WHEREcolumn1operator (SELECTcolum1FROMtable2WHEREexpr1= .expr2);outerouter三、常用SQL語法及函數(shù)介紹——子查詢子查詢參照主查詢中表的每行的值.SELECTDISTINCT(t.emp_code),t.co_code,t.year,t.mo,t.pr_paylist_n57--假如為工資合計FROMPR_PAYLISTtWHEREt.pr_paylist_n57>使用相關子查詢每次外部查詢得到一行,內部查詢就執(zhí)行一次計算.找到所有人員的工資大于所在單位平均工資的人員信息.

(SELECTAVG(t1.pr_paylist_n57)FROMPR_PAYLISTt1WHEREt.co_code=t1.co_codeANDt.year=t1.yearANDt.mo=t1.moGROUPBYt1.co_code,t1.year,t1.moHAVINGAVG(t1.pr_paylist_n57)>0

)三、常用SQL語法及函數(shù)介紹——子查詢SELECTa.co_code,a.fiscal,a.bg_project_code,a.bg_project_name,b.ys預算數(shù)FROMbg_projecta,(SELECTco_code,fiscal,bg_project_code,SUM(bg_amt)ysFROMbg_project_detailGROUPBYco_code,fiscal,bg_project_code)bWHEREa.co_code=b.co_codeANDa.fiscal=b.fiscalANDa.bg_project_code=b.bg_project_code在FROM子句中使用子查詢三、常用SQL語法及函數(shù)介紹——子查詢使用EXISTS操作符EXISTS操作符測試子查詢的結果是否存在.如果一個子查詢找到了結果:在內部子查詢中不在繼續(xù)執(zhí)行條件被設為TRUE如果一個子查詢沒有找到結果:條件被設為FALSE在內部子查詢中繼續(xù)執(zhí)行查找三、常用SQL語法及函數(shù)介紹——子查詢SELECT*FROMma_coa_accaWHEREis_lowest='Y'ANDEXISTS(SELECT1FROMma_coa_accbWHEREa.acc_code=b.par_acc_code);使用EXISTS操作符查詢科目是否末級設置是否正確三、常用SQL語法及函數(shù)介紹——子查詢SELECT*FROMma_coa_accaWHEREis_lowest='N'ANDNOTEXISTS(SELECT1FROMma_coa_accbWHEREa.acc_code=b.par_acc_code);使用NOTEXISTS操作符查詢科目是否末級設置是否正確三、常用SQL語法及函數(shù)介紹——子查詢UPDATE中的相關子查詢使用相關子查詢可以基于另一個表來更新表中的每個符合條件的.UPDATEtable1alias1SETcolumn=(SELECTexpressionFROMtable2alias2WHEREalias1.column=alias2.column);三、常用SQL語法及函數(shù)介紹——子查詢UPDATE中的相關子查詢修改存在下級科目的科目屬性“是否末級”為“否”UPDATEma_coa_acca

SETa.is_lowest='N'

WHERE

EXISTS(SELECT1

FROMma_coa_accb

WHEREa.acc_code=b.par_acc_code);三、常用SQL語法及函數(shù)介紹——子查詢UPDATE中的相關子查詢修改存在下級科目的科目屬性“是否末級”為“否”UPDATEma_coa_accaSETa.is_lowest='Y'WHERENOTEXISTS(SELECT1FROMma_coa_accbWHEREa.acc_code=b.par_acc_code);三、常用SQL語法及函數(shù)介紹——子查詢ABUNION/UNIONALLABABINTERSECTABMINUS三、常用SQL語法及函數(shù)介紹——集合UNION操作返回兩個查詢結果除去重復值后的交集.AB三、常用SQL語法及函數(shù)介紹——集合AB三、常用SQL語法及函數(shù)介紹——集合UNIONALL返回兩個查詢結果的交集,結果包括重復值

INTERSECT操作符AB三、常用SQL語法及函數(shù)介紹——集合MINUS操作符AB三、常用SQL語法及函數(shù)介紹——集合集合操作符的提示在SELECT列表中的表達是必須有同樣的數(shù)目和類型.括號可以用來修改集合操作的執(zhí)行順序.在結果中顯示第一個Select語句中的列名.ORDERBY子句:只能在整個集合的最后出現(xiàn)可以按照第一個SELECT語句中的列名,別名或者位置號排序.除了UNIONALL操作外,其他的集合操作都自動去掉重復值.除了UNIONALL操作外，其他集合操作的輸出結果默認按照升序的次序排列.三、常用SQL語法及函數(shù)介紹——集合UNIONSELECT'集合a'集合名稱, ROWNUM序號, trunc(dbms_random.VALUE(0,100))分數(shù)FROMdualCONNECTBYROWNUM<=3UNIONSELECT'集合b'集合名稱, ROWNUM序號, trunc(dbms_random.VALUE(0,100))分數(shù)FROMdualCONNECTBYROWNUM<=2三、常用SQL語法及函數(shù)介紹——集合UNIONALLWITHaAS(SELECT'張三'name,'男'sexFROMdualUNIONALLSELECT'張三'name,'男'sexFROMdualUNIONALLSELECT'李四'name,'男'sexFROMdual)SELECT*FROMa;三、常用SQL語法及函數(shù)介紹——集合INTERSECTWITHaAS(SELECT'張三'name,'男'sexFROMdualINTERSECT(SELECT'張三'name,'男'sexFROMdualUNIONALLSELECT'李四'name,'男'sexFROMdual))SELECT*FROMa;三、常用SQL語法及函數(shù)介紹——集合連接查詢的類型內連接外連接（左外連接、右外連接、全外連接）交叉連接自連接三、常用SQL語法及函數(shù)介紹——連接查詢內連接相等連接不等連接三、常用SQL語法及函數(shù)介紹——連接查詢SELECT<table_name1.*/table_name2.column1,…>FROMtable_name1,table_name2WHEREcondition(s);三、常用SQL語法及函數(shù)介紹——連接查詢SELECTt1.nd,t1.co_code,t2.co_name,t1.account_id,t1.account_nameFROMma_co_acct1,ma_companyt2WHEREt1.nd=t2.ndANDt1.co_code=t2.co_code;--單位及帳套信息內連接外連接左外連接:左外連接返回那些存在于左表而右表中卻沒有的行，再加上內連接的行右外連接:右外連接返回那些存在于右表而左表中卻沒有的行，再加上內連接的行全外連接：兩邊表中的內容全部顯示注意連接條件的對應關系，比如1對多關系的影響三、常用SQL語法及函數(shù)介紹——連接查詢SELECT<table_name1.*/table_name2.column1,…>FROMtable_name1[LEFT|RIGHT|FULL]JOINtable_name2ONcondition(s);三、常用SQL語法及函數(shù)介紹——連接查詢SELECT

a.co_code,

a.nd,

a.co_name,

b.account_id,

b.account_name

FROMma_companya

LEFTJOINma_co_accb

ONa.nd=b.nd

ANDa.co_code=b.co_code

ORDERBY1;--左連接，查詢各單位建賬情況三、常用SQL語法及函數(shù)介紹——連接查詢SELECT

a.co_code,

a.nd,

a.co_name,

b.account_id,

b.account_name

FROMma_companya,ma_co_accb

WHEREa.nd=b.nd(+)

ANDa.co_code=b.co_code(+)

ORDERBY1;--換種寫法三、常用SQL語法及函數(shù)介紹——連接查詢SELECT

a.co_code,

a.nd,

a.co_name,

b.account_id,

b.account_name

FROMma_companya

RIGHTJOINma_co_accb

ONa.nd=b.nd

ANDa.co_code=b.co_code

ORDERBY1;--右連接，查詢建了帳套的單位交叉連接——笛卡爾積三、常用SQL語法及函數(shù)介紹——連接查詢SELECT<table_name1.*/table_name2.column1,…>FROMtable_name1,table_name2自連接三、常用SQL語法及函數(shù)介紹——連接查詢SELECT<table_name1.*/table_name2.column1,…>FROMtable_nameta