XXXX年初级认证培训-SQL基本开发课件

用友政务A++咨询实施部张海2010年5月10日A++认证培训——SQL开发、财务数据字典及输出报表定制用友政务A++咨询实施部A++认证培训——目录培训前言数据库及SQL概述常用SQL语法及函数详细介绍输出报表定制流程目录培训前言培训前言培训对象培训目标培训前言培训对象

Oracle初学者一、培训前言——培训对象一、培训前言——培训对象

通过本次培训，对Oracle和SQL语言有一定的认识掌握SQL常用的语法和函数掌握财务常用的数据字典掌握输出报表定制的一般流程一、培训前言——培训目标通过本次培训，对Oracle和SQL语言有一定的认识一、培目录培训前言数据库及SQL概述常用SQL语法及函数详细介绍输出报表定制流程目录培训前言数据库及SQL概述数据库发展常用数据库Oracle产生与发展SQL语言介绍SQL语言组成及功能SQL开发工具介绍数据库及SQL概述数据库发展

数据库发展常用数据库a、桌面关系型数据库系统：Access\visualfoxpro。基于windows操作系统，满足日常小型办公需要，不提供或提供有限的网络应用功能b、网络关系型数据库系统：MysqlSybasesqlserveroracledb2二、数据库及SQL概述数据库发展二、数据库及SQL概述

Oracle产生和发展 目前应用比较广泛的版本是Oracle9i(i代表Internet)和10g（g代表grid,网格

）,2007年7月推出oracle11g.二、数据库及SQL概述Oracle产生和发展 二、数据库及SQL概述

SQL语言介绍SQL——structuredquerylanguage高级非过程化语言用于存取数据以及查询、更新和管理关系数据库系统

二、数据库及SQL概述SQL语言介绍二、数据库及SQL概述

SQL语言组成与功能数据定义语言(DDL)，例如：CREATE、DROP、ALTER等语句数据操作语言(DML)，例如：INSERT（插入）、UPDATE（修改）、DELETE（删除）语句数据查询语言(DQL)，例如：SELECT语句数据控制语言(DCL)，例如：GRANT、REVOKE、COMMIT、ROLLBACK等语句二、数据库及SQL概述SQL语言组成与功能二、数据库及SQL概述

SQL开发工具SQLPlusPL/SQLDeveloper二、数据库及SQL概述SQL开发工具二、数据库及SQL概述目录培训前言数据库及SQL概述常用SQL语法及函数详细介绍输出报表定制流程目录培训前言常用SQL语法及函数详细介绍DDL语句及操作DML语句及操作查询语句单表查询子查询集合连接查询DCL语句及操作函数介绍常用SQL语法及函数详细介绍DDL语句及操作

CREATETABLE/DROPTABLE/ALTERTABLE语法：CREATETABLEtable_name(column_nametype[CONSTRAINTconstraint_def][DEFAULTdefault_exp],…);DESCRIBEtable_name;DROPTABLEtable_name;三、常用SQL语法及函数介绍——DDLCREATETABLE/DROPTABLE/ALTER

CREATETABLE/DROPTABLE/ALTERTABLE语法：ALTERTABLEtable_nameADD…;ALTERTABLEtable_nameDROP…;ALTERTABLEtable_nameMODIFY…;三、常用SQL语法及函数介绍——DDLCREATETABLE/DROPTABLE/ALTER

CREATEVIEW/DROPVIEW语法：CREATE[ORREPLACE]VIEWview_nameASsubquery[WITHREADONLY];DROPVIEWview_name;三、常用SQL语法及函数介绍——DDLCREATEVIEW/DROPVIEW三、常用SQL语

SELECT *|{[DISTINCT]column|expression[alias],...}FROM table;SELECT表示选择哪些列FROM表示从哪些表中查询指定列、全部列、去重（DISTINCT）、改变输出（AS）连接符（||）算数表达式三、常用SQL语法及函数介绍——DQLSELECT *|{[DISTINCT]column|e三、常用SQL语法及函数介绍——DQLSELECTfiscal,coa_code,coa_nameFROMma_coa;--科目体系指定列SELECT*FROMma_coa;全部列三、常用SQL语法及函数介绍——DQLSELECTfisc三、常用SQL语法及函数介绍——DQLSELECTt.fiscalAS"年度",t.coa_codeAS"科目体系代码",t.coa_name"科目体系名称"FROMma_coat;改变输出SELECTfiscal"年度",coa_code||'_'||coa_nameAS"科目体系"FROMma_coa;连接符三、常用SQL语法及函数介绍——DQLSELECT改变输出S三、常用SQL语法及函数介绍——DQLSELECTt.co_code,t.nd,t.co_name

FROMma_companyt–-单位信息表

ORDERBY1;去重SELECTDISTINCT(t.co_code),t.co_nameFROMma_companytORDERBY1;三、常用SQL语法及函数介绍——DQLSELECTt.co三、常用SQL语法及函数介绍——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--资产卡片算数表达式三、常用SQL语法及函数介绍——DQLSELECT算数表达式三、常用SQL语法及函数介绍——单表查询

空值是一个不可用的,没有被分配的,或者不能得到的值.空值不同于零或者空格.空值的产生：非主键、未定义notnull都有可能产生空值

SELECT*

FROMas_user;--用户表定义空值Null三、常用SQL语法及函数介绍——单表查询空值是一个不可用的三、常用SQL语法及函数介绍——单表查询

在算术表达式中，与空值运算的结果为空。空值也会带来一些算法的改变，会使快速索引访问失效。

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语法及函数介绍——单表查询在算术表达式中，与三、常用SQL语法及函数介绍——单表查询

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语法及函数介绍——单表查询NVL()：如果为三、常用SQL语法及函数介绍——单表查询

NVL2()空值处理

SELECTDISTINCT(user_id)user_id,user_nameuser_name,NVL2(pk_desc,oper_time||'_修改过凭证','未新增或修改凭证')is_modvouFROMas_log;--日志查询三、常用SQL语法及函数介绍——单表查询NVL2()空值处三、常用SQL语法及函数介绍——单表查询

在查询中限制查询得到的结果通过WHERE子句限制查到的结果.WHERE子句紧跟着FROM子句.使用比较符（=、<、<=、>、>=、<>）NOT、AND、OR（优先级从高到低）SELECT *|{[DISTINCT]column|expression[alias],...}FROM table[WHERE condition(s)];三、常用SQL语法及函数介绍——单表查询在查询中限制查询得三、常用SQL语法及函数介绍——单表查询

字符串和日期数据字符串和日期数据应该使用单引号扩起来.字符数据大小写敏感,日期数据格式敏感.SELECT*FROMma_coa_accWHEREcoa_code='001';--系统级科目表'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');--查询零余额科目三、常用SQL语法及函数介绍——单表查询字符串和日期数据字OperatorBETWEEN

...AND...IN(set)LIKEISNULLMeaning两个值之间(包含),

匹配任何列表中的值模糊匹配字符串值是否空值三、常用SQL语法及函数介绍——单表查询其他比较运算符OperatorMeaning三、常用SQL语法及函数介绍—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';--查询凭证主表单据日期在2010-01-01到2010-01-05之间的凭证LowerlimitUpperlimit三、常用SQL语法及函数介绍——单表查询使用BETWEEN运算符SELECT*LowerlimitUpperlimit三、常用SQL语法及函数介绍——单表查询使用LIKE

运算符执行基于通配符的模糊查询，查询包含字符串的所有数据.查询条件可以包含数字或者字符文本:%

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

表示一个字符.SELECT*

FROMgl_coa_acc

WHEREco_code='315002'

ANDacc_codeLIKE'102%';使用LIKE运算符三、常用SQL语法及函数介绍——单表查询使用LIKE运算三、常用SQL语法及函数介绍——单表查询SELECT*

FROMgl_coa_acc

WHEREco_code='315002'

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

FROMgl_coa_acc

WHEREco_code='315002'

ANDacc_codeLIKE'%102%';三、常用SQL语法及函数介绍——单表查询SELECT*

FSELECT*

FROMgl_coa_acc

WHEREco_code='315002'

ANDpar_acc_codeISNULL;--父代码为空的科目（一级科目）三、常用SQL语法及函数介绍——单表查询使用ISNULL运算符SELECT*

FROMgl_coa_acc

WHERE三、常用SQL语法及函数介绍——单表查询条件表达式提供了在SQL语句中使用IF-THEN-ELSE的方法.使用两种方法:CASE表达式DECODE函数CASEexprWHENcomparison_expr1THENreturn_expr1

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

[,search2,result2,...,]

[,default])三、常用SQL语法及函数介绍——单表查询条件表达式提供了在SSELECTnd,co_code,account_name,CASEcoa_codeWHEN'001'THEN'行政科目体系'WHEN'002'THEN'事业科目体系'ENDASkmtxFROMma_co_acc;使用CASE表达式三、常用SQL语法及函数介绍——单表查询SELECTnd,co_code,account_nameSELECTco_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语法及函数介绍——单表查询行转列应用SELECTco_code单位,使用CASE表达式三使用DECODE函数SELECTnd,co_code,account_name,DECODE(coa_code,'001','行政科目体系','002','事业科目体系','其他')ASkmtxFROMma_co_acc;三、常用SQL语法及函数介绍——单表查询使用DECODE函数SELECTnd,co_code,三、常用SQL语法及函数介绍——单表查询

排序——ORDERBYSELECT *|{[DISTINCT]column|expression[alias],...}FROM table[WHERE condition(s)][ORDERBYexpression1[ASC|DESC],…];单列排序多列排序三、常用SQL语法及函数介绍——单表查询排序——ORDER三、常用SQL语法及函数介绍——单表查询

排序——ORDERBYSELECTfiscal,coa_code,acc_code,acc_name

FROMma_coa_acc–-系统会计科目表

ORDERBYfiscalDESC,coa_code,acc_code;

--SELECTfiscal,coa_code,acc_code,acc_name

FROMma_coa_acc

ORDERBY1DESC,2,3;三、常用SQL语法及函数介绍——单表查询排序——ORDER三、常用SQL语法及函数介绍——单表查询

分组SELECT *|{[DISTINCT]column|expression[alias],...}FROM table[WHERE condition(s)][GROUPBYexpression,…][HAVINGcondition(s)]常用聚合函数：MAX()MIN()COUNT()SUM()AVG()三、常用SQL语法及函数介绍——单表查询分组SELECT 三、常用SQL语法及函数介绍——单表查询

分组——GROUPBY子句SELECTco_code单位,fiscal年度,bg_project_code预算方案,SUM(bg_amt)预算数FROMbg_project_detailGROUPBYco_code,fiscal,bg_project_code;--预算管理预算数查询三、常用SQL语法及函数介绍——单表查询分组——GROUP三、常用SQL语法及函数介绍——单表查询

分组——GROUPBY子句SELECTco_code单位,fiscal年度,fis_perd期间,COUNT(1)凭证张数FROMgl_vou_headGROUPBYco_code,fiscal,fis_perdORDERBY1,2,3;--查询单位每月凭证数三、常用SQL语法及函数介绍——单表查询分组——GROUP三、常用SQL语法及函数介绍——单表查询

分组——HAVING子句SELECTco_code单位,fiscal年度,bg_project_code预算方案,SUM(bg_amt)预算数FROMbg_project_detailGROUPBYco_code,fiscal,bg_project_codeHAVINGSUM(bg_amt)>150000ORDERBY4DESC;--预算数大于150000的方案三、常用SQL语法及函数介绍——单表查询分组——HAVIN子查询子查询（内部查询）在主查询执行前执行.子查询的结果被主查询（外部查询）使用.SELECT select_listFROM tableWHERE exproperator(SELECTselect_list FROM table);三、常用SQL语法及函数介绍——子查询子查询子查询（内部查询）在主查询执行前执行.SELECT sSELECTm.co_code,m.co_nameFROMma_companymWHEREm.co_codeIN(SELECTco_codeFROMgl_vou_head);--查询录过凭证的单位三、常用SQL语法及函数介绍——子查询子查询SELECT三、常用SQL语法及函数介绍——子查询子查询SELECTco_code,fiscal,item_code,SUM(stad_amt)FROMgl_vou_detail_assWHEREco_code='315002'ANDitem_code=(SELECTitem_codeFROMgl_itemWHEREco_code='315002'ANDitem_name='隧道照明用电经费')ANDfiscal=2010ANDacc_codeLIKE'50402%'ANDdr_cr='1'GROUPBYco_code,fiscal,item_code;三、常用SQL语法及函数介绍——子查询子查询查询某一个项目的支出合计SELECTco_code,fiscal,item_cod只返回一行使用单行比较操作符Operator=>>= <<= <>MeaningEqualtoGreaterthanGreaterthanorequaltoLessthanLessthanorequaltoNotequalto三、常用SQL语法及函数介绍——子查询只返回一行OperatorMeaning三、常用SQL语法及SELECTbg_project_code,bg_project_name

FROMbg_project

WHEREbg_project_code=

(SELECTbg_project_code

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

onerow对多行子查询使用了单行比较操作符.三、常用SQL语法及函数介绍——子查询SELECTbg_project_code,bg_pro返回多于一行使用多行比较操作符操作符

IN

含义等于列表中的任何值三、常用SQL语法及函数介绍——子查询返回多于一行操作符含义三、常用SQL语法及函数介绍——子查询多列子查询MainqueryWHERE(MANAGER_ID,DEPARTMENT_ID)INSubquery10090

10260

12450主查询条件中的每列与多列子查询返回的多行多列的结果进行比较.三、常用SQL语法及函数介绍——子查询多列子查询MainqueryWHERE(MANAGER_列的比较与多列子查询的比较可以是:

成对比较--Pairwisecomparisons

非成对比较--Nonpairwisecomparisons

三、常用SQL语法及函数介绍——子查询列的比较与多列子查询的比较可以是:三、常用SQL语法及函数介成对比较子查询显示所有与141和174相同部门、相同经理的雇员的信息（不包括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语法及函数介绍——子查询成对比较子查询显示所有与141和174相同部门、相同经理的雇非成对比较子查询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相同部门或者相同经理的雇员的信息（不包括141和174雇员）.三、常用SQL语法及函数介绍——子查询非成对比较子查询SELECTemployee_id,m相关子查询相关子查询被用于查询执行时的一行一行处理.每个相关子查询对于外部查询的每行执行一次.GETcandidaterowfromouterqueryEXECUTEinnerqueryusingcandidaterowvalueUSEvaluesfrominnerquerytoqualifyordisqualifycandidaterow三、常用SQL语法及函数介绍——子查询相关子查询相关子查询被用于查询执行时的一行一行处理.每个相关相关子查询

SELECTcolumn1,column2,...FROMtable1WHEREcolumn1operator (SELECTcolum1FROMtable2WHEREexpr1= .expr2);outerouter三、常用SQL语法及函数介绍——子查询子查询参照主查询中表的每行的值.相关子查询SELECTcolumn1,column2,SELECTDISTINCT(t.emp_code),t.co_code,t.year,t.mo,t.pr_paylist_n57--假如为工资合计FROMPR_PAYLISTtWHEREt.pr_paylist_n57>使用相关子查询每次外部查询得到一行,内部查询就执行一次计算.找到所有人员的工资大于所在单位平均工资的人员信息.

(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语法及函数介绍——子查询SELECTDISTINCT(t.emp_code),使用SELECTa.co_code,a.fiscal,a.bg_project_code,a.bg_project_name,b.ys预算数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语法及函数介绍——子查询SELECTa.co_code,在FROM子句中使用子使用EXISTS操作符EXISTS操作符测试子查询的结果是否存在.如果一个子查询找到了结果:在内部子查询中不在继续执行条件被设为TRUE如果一个子查询没有找到结果:条件被设为FALSE在内部子查询中继续执行查找三、常用SQL语法及函数介绍——子查询使用EXISTS操作符EXISTS操作符测试子查询的结SELECT*FROMma_coa_accaWHEREis_lowest='Y'ANDEXISTS(SELECT1FROMma_coa_accbWHEREa.acc_code=b.par_acc_code);使用EXISTS操作符查询科目是否末级设置是否正确三、常用SQL语法及函数介绍——子查询SELECT*使用EXISTS操作符查询科目是否末级设SELECT*FROMma_coa_accaWHEREis_lowest='N'ANDNOTEXISTS(SELECT1FROMma_coa_accbWHEREa.acc_code=b.par_acc_code);使用NOTEXISTS操作符查询科目是否末级设置是否正确三、常用SQL语法及函数介绍——子查询SELECT*使用NOTEXISTS操作符查询科目是UPDATE中的相关子查询使用相关子查询可以基于另一个表来更新表中的每个符合条件的.UPDATEtable1alias1SETcolumn=(SELECTexpressionFROMtable2alias2WHEREalias1.column=alias2.column);三、常用SQL语法及函数介绍——子查询UPDATE中的相关子查询UPDATEtable1aliUPDATE中的相关子查询修改存在下级科目的科目属性“是否末级”为“否”UPDATEma_coa_acca

SETa.is_lowest='N'

WHERE

EXISTS(SELECT1

FROMma_coa_accb

WHEREa.acc_code=b.par_acc_code);三、常用SQL语法及函数介绍——子查询UPDATE中的相关子查询修改存在下级科目的科目属性“是否末UPDATE中的相关子查询修改存在下级科目的科目属性“是否末级”为“否”UPDATEma_coa_accaSETa.is_lowest='Y'WHERENOTEXISTS(SELECT1FROMma_coa_accbWHEREa.acc_code=b.par_acc_code);三、常用SQL语法及函数介绍——子查询UPDATE中的相关子查询修改存在下级科目的科目属性“是否末ABUNION/UNIONALLABABINTERSECTABMINUS三、常用SQL语法及函数介绍——集合ABUNION/UNIONALLABABINTERSECTUNION操作返回两个查询结果除去重复值后的交集.AB三、常用SQL语法及函数介绍——集合UNION操作返回两个查询结果除去重复值后的交集.AB三、AB三、常用SQL语法及函数介绍——集合UNIONALL返回两个查询结果的交集,结果包括重复值AB三、常用SQL语法及函数介绍——集合UNIONALL返

INTERSECT操作符AB三、常用SQL语法及函数介绍——集合

INTERSECT操作符AB三、常用SQL语法及函数介绍MINUS操作符AB三、常用SQL语法及函数介绍——集合MINUS操作符AB三、常用SQL语法及函数介绍——集合集合操作符的提示在SELECT列表中的表达是必须有同样的数目和类型.括号可以用来修改集合操作的执行顺序.在结果中显示第一个Select语句中的列名.ORDERBY子句:只能在整个集合的最后出现可以按照第一个SELECT语句中的列名,别名或者位置号排序.除了UNIONALL操作外,其他的集合操作都自动去掉重复值.除了UNIONALL操作外，其他集合操作的输出结果默认按照升序的次序排列.三、常用SQL语法及函数介绍——集合集合操作符的提示在SELECT列表中的表达是必须有同样的数UNIONSELECT'集合a'集合名称, ROWNUM序号, trunc(dbms_random.VALUE(0,100))分数FROMdualCONNECTBYROWNUM<=3UNIONSELECT'集合b'集合名称, ROWNUM序号, trunc(dbms_random.VALUE(0,100))分数FROMdualCONNECTBYROWNUM<=2三、常用SQL语法及函数介绍——集合UNIONSELECT'集合a'集合名称,三、常用SQLUNIONALLWITHaAS(SELECT'张三'name,'男'sexFROMdualUNIONALLSELECT'张三'name,'男'sexFROMdualUNIONALLSELECT'李四'name,'男'sexFROMdual)SELECT*FROMa;三、常用SQL语法及函数介绍——集合UNIONALLWITHaAS(三、常用SQL语法及INTERSECTWITHaAS(SELECT'张三'name,'男'sexFROMdualINTERSECT(SELECT'张三'name,'男'sexFROMdualUNIONALLSELECT'李四'name,'男'sexFROMdual))SELECT*FROMa;三、常用SQL语法及函数介绍——集合INTERSECTWITHaAS(三、常用SQL语法及函连接查询的类型内连接外连接（左外连接、右外连接、全外连接）交叉连接自连接三、常用SQL语法及函数介绍——连接查询连接查询的类型内连接三、常用SQL语法及函数介绍——连接查询内连接相等连接不等连接三、常用SQL语法及函数介绍——连接查询SELECT<table_name1.*/table_name2.column1,…>FROMtable_name1,table_name2WHEREcondition(s);内连接相等连接三、常用SQL语法及函数介绍——连接查询SEL三、常用SQL语法及函数介绍——连接查询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;--单位及帐套信息内连接三、常用SQL语法及函数介绍——连接查询SELECTt1.外连接左外连接:左外连接返回那些存在于左表而右表中却没有的行，再加上内连接的行右外连接:右外连接返回那些存在于右表而左表中却没有的行，再加上内连接的行全外连接：两边表中的内容全部显示注意连接条件的对应关系，比如1对多关系的影响三、常用SQL语法及函数介绍——连接查询SELECT<table_name1.*/table_name2.column1,…>FROMtable_name1[LEFT|RIGHT|FULL]JOINtable_name2ONcondition(s);外连接左外连接:左外连接返回那些存在于左表而右表中却没有的行三、常用SQL语法及函数介绍——连接查询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语法及函数介绍——连接查询SELECT

三、常用SQL语法及函数介绍——连接查询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语法及函数介绍——连接查询SELECT

三、常用SQL语法及函数介绍——连接查询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语法及函数介绍——连接查询SELECT

交叉连接——笛卡尔积三、常用SQL语法及函数介绍——连接查询SELECT<table_name1.*/table_name2.column1,…>FROMtable_name1,table_name2交叉连接——笛卡尔积三、常用SQL语法及函数介绍——连接查询自连接三、常用SQL语法及函数介绍——连接查询SELECT<table_name1.*/table_name2.column1,…>FROMtable_nametable_name1,table_nametable_name2WHEREcondition(s)如果在同一时间需要同一个表中不同行中的信息需要用到自连接自连接三、常用SQL语法及函数介绍——连接查询SELECT自连接三、常用SQL语法及函数介绍——连接查询SELECTt1.co_code单位代码,t1.co_name单位名称,t1.nd年度,t1.parent_co_code上级单位代码,t2.co_name上级单位名称FROMma_companyt1,ma_companyt2WHEREt1.nd=t2.nd(+)ANDt1.parent_co_code=t2.co_code(+)ORDERBY1;--查询单位及上级单位名称自连接三、常用SQL语法及函数介绍——连接查询SELECT事务处理三、常用SQL语法及函数介绍——DCLCOMMITROLLBACK事务处理三、常用SQL语法及函数介绍——DCLCOMMIT三、常用SQL语法及函数介绍——函数ABS(x)ROUND(X[,Y])INSTR(X,Y)LENGTH(X)LOWER(X)UPPER(X)LPAD(S,X[,Y])TRIMNVLNVL2REPLACE(X,Y,Z)SUBSTR(X,N[,M])SYSDATETO_CHARTO_DATE三、常用SQL语法及函数介绍——函数ABS(x)三、常用SQL语法及函数介绍——视图CREATE[ORREPLACE]VIEWview_nameASsubquery[WITHREADONLY]三、常用SQL语法及函数介绍——视图CREATE[ORR三、常用SQL语法及函数介绍——书写规范目标：清晰易读语句中出现的所有表名、字段名全部小写，系统保留字、内置函数名、Sql保留字大写；连接符or、in、and、以及＝、=等前后加上一个空格多用注释，尤其是对较为复杂的sql语句，说明算法、功能SQL语句的缩进风格：1)一行有多列，超过80个字符时，基于列对齐原则，采用下行缩进(2)where子句书写时，每个条件占一行，语句令起一行时，以保留字或者连接符开始，连接符右对齐。多表连接时，使用表的别名来引用列三、常用SQL语法及函数介绍——书写规范目标：清晰易读目录培训前言数据库及SQL概述常用SQL语法及函数详细介绍输出报表定制流程目录培训前言四、输出报表定制流程定义流程：定义数据表（表或视图）定义数据源（或组合数据源）及查询条件（支持自定义function）定义报表生成部件、赋权限输出报表工具就是用图形化的界面引导生成SQL语句，并提供报表模版定制生成用户需要的报表

四、输出报表定制流程定义流程：输出报表工具就是用图形化的界面四、输出报表定制流程数据表描述：提供数据来源，主要包括表信息和字段信息。表类型：业务数据表、基础数据表业务数据表：存放单位实际业务数据的表基础数据表：往往用以代码翻译，比如功能分类、单位等表或视图：做输出报表时用视图较多字段设置：字段数据：字符型、数值型、日期型引用表：引用基础数据表，用以代码翻译预置字段：预置常用字段导入字典：如果是视图，用此导入字段基础数据表：需要设置基础数据表代码和基础数据表名称四、输出报表定制流程数据表描述：提供数据来源，主要包括表信息四、输出报表定制流程数据源：数据源的定义分为自定义数据源和组合数据源两种。自定义数据源就是基于数据表来定义的查询。说的更通俗些，自定义数据源就是以图形化，引导方式生成sql语句。组合数据源是对自定义数据源的处理，实际是对自定义查询结果进行再加工处理的过程，这种处理结果主要包括联合(union暂时未实现)和连接(join)。设置数据源主表设置自定义字段设置使用字段设置数据源条件四、输出报表定制流程数据源：数据源的定义分为自定义数据源和组四、输出报表定制流程报表定义：报表定义是设置报表的格式以及指定报表的数据源报表基本参数设置报表数据源分组排序设置设置报表参数报表模版定制安置数据源四、输出报表定制流程报表定义：报表定义是设置报表的格式以及指四、输出报表定制流程报表参数：用于标识报表在定义时不确定的值，由用户在报表查询时才指定的值数据类型引用表权限设置权限SQL应用四、输出报表定制流程报表参数：用于标识报表在定义时不确定的值四、输出报表定制流程生成部件赋予权限其他设置四、输出报表定制流程生成部件四、输出报表定制流程系统演示四、输出报表定制流程系统演示联系电话：邮箱：zhanghai@联系电话：演讲完毕，谢谢观看！演讲完毕，谢谢观看！用友政务A++咨询实施部张海2010年5月10日A++认证培训——SQL开发、财务数据字典及输出报表定制用友政务A++咨询实施部A++认证培训——目录培训前言数据库及SQL概述常用SQL语法及函数详细介绍输出报表定制流程目录培训前言培训前言培训对象培训目标培训前言培训对象

Oracle初学者一、培训前言——培训对象一、培训前言——培训对象

通过本次培训，对Oracle和SQL语言有一定的认识掌握SQL常用的语法和函数掌握财务常用的数据字典掌握输出报表定制的一般流程一、培训前言——培训目标通过本次培训，对Oracle和SQL语言有一定的认识一、培目录培训前言数据库及SQL概述常用SQL语法及函数详细介绍输出报表定制流程目录培训前言数据库及SQL概述数据库发展常用数据库Oracle产生与发展SQL语言介绍SQL语言组成及功能SQL开发工具介绍数据库及SQL概述数据库发展

数据库发展常用数据库a、桌面关系型数据库系统：Access\visualfoxpro。基于windows操作系统，满足日常小型办公需要，不提供或提供有限的网络应用功能b、网络关系型数据库系统：MysqlSybasesqlserveroracledb2二、数据库及SQL概述数据库发展二、数据库及SQL概述

Oracle产生和发展 目前应用比较广泛的版本是Oracle9i(i代表Internet)和10g（g代表grid,网格

）,2007年7月推出oracle11g.二、数据库及SQL概述Oracle产生和发展 二、数据库及SQL概述

SQL语言介绍SQL——structuredquerylanguage高级非过程化语言用于存取数据以及查询、更新和管理关系数据库系统

二、数据库及SQL概述SQL语言介绍二、数据库及SQL概述

SQL语言组成与功能数据定义语言(DDL)，例如：CREATE、DROP、ALTER等语句数据操作语言(DML)，例如：INSERT（插入）、UPDATE（修改）、DELETE（删除）语句数据查询语言(DQL)，例如：SELECT语句数据控制语言(DCL)，例如：GRANT、REVOKE、COMMIT、ROLLBACK等语句二、数据库及SQL概述SQL语言组成与功能二、数据库及SQL概述

SQL开发工具SQLPlusPL/SQLDeveloper二、数据库及SQL概述SQL开发工具二、数据库及SQL概述目录培训前言数据库及SQL概述常用SQL语法及函数详细介绍输出报表定制流程目录培训前言常用SQL语法及函数详细介绍DDL语句及操作DML语句及操作查询语句单表查询子查询集合连接查询DCL语句及操作函数介绍常用SQL语法及函数详细介绍DDL语句及操作

CREATETABLE/DROPTABLE/ALTERTABLE语法：CREATETABLEtable_name(column_nametype[CONSTRAINTconstraint_def][DEFAULTdefault_exp],…);DESCRIBEtable_name;DROPTABLEtable_name;三、常用SQL语法及函数介绍——DDLCREATETABLE/DROPTABLE/ALTER

CREATETABLE/DROPTABLE/ALTERTABLE语法：ALTERTABLEtable_nameADD…;ALTERTABLEtable_nameDROP…;ALTERTABLEtable_nameMODIFY…;三、常用SQL语法及函数介绍——DDLCREATETABLE/DROPTABLE/ALTER

CREATEVIEW/DROPVIEW语法：CREATE[ORREPLACE]VIEWview_nameASsubquery[WITHREADONLY];DROPVIEWview_name;三、常用SQL语法及函数介绍——DDLCREATEVIEW/DROPVIEW三、常用SQL语

SELECT *|{[DISTINCT]column|expression[alias],...}FROM table;SELECT表示选择哪些列FROM表示从哪些表中查询指定列、全部列、去重（DISTINCT）、改变输出（AS）连接符（||）算数表达式三、常用SQL语法及函数介绍——DQLSELECT *|{[DISTINCT]column|e三、常用SQL语法及函数介绍——DQLSELECTfiscal,coa_code,coa_nameFROMma_coa;--科目体系指定列SELECT*FROMma_coa;全部列三、常用SQL语法及函数介绍——DQLSELECTfisc三、常用SQL语法及函数介绍——DQLSELECTt.fiscalAS"年度",t.coa_codeAS"科目体系代码",t.coa_name"科目体系名称"FROMma_coat;改变输出SELECTfiscal"年度",coa_code||'_'||coa_nameAS"科目体系"FROMma_coa;连接符三、常用SQL语法及函数介绍——DQLSELECT改变输出S三、常用SQL语法及函数介绍——DQLSELECTt.co_code,t.nd,t.co_name

FROMma_companyt–-单位信息表

ORDERBY1;去重SELECTDISTINCT(t.co_code),t.co_nameFROMma_companytORDERBY1;三、常用SQL语法及函数介绍——DQLSELECTt.co三、常用SQL语法及函数介绍——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--资产卡片算数表达式三、常用SQL语法及函数介绍——DQLSELECT算数表达式三、常用SQL语法及函数介绍——单表查询

空值是一个不可用的,没有被分配的,或者不能得到的值.空值不同于零或者空格.空值的产生：非主键、未定义notnull都有可能产生空值

SELECT*

FROMas_user;--用户表定义空值Null三、常用SQL语法及函数介绍——单表查询空值是一个不可用的三、常用SQL语法及函数介绍——单表查询

在算术表达式中，与空值运算的结果为空。空值也会带来一些算法的改变，会使快速索引访问失效。

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语法及函数介绍——单表查询在算术表达式中，与三、常用SQL语法及函数介绍——单表查询

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语法及函数介绍——单表查询NVL()：如果为三、常用SQL语法及函数介绍——单表查询

NVL2()空值处理

SELECTDISTINCT(user_id)user_id,user_nameuser_name,NVL2(pk_desc,oper_time||'_修改过凭证','未新增或修改凭证')is_modvouFROMas_log;--日志查询三、常用SQL语法及函数介绍——单表查询NVL2()空值处三、常用SQL语法及函数介绍——单表查询

在查询中限制查询得到的结果通过WHERE子句限制查到的结果.WHERE子句紧跟着FROM子句.使用比较符（=、<、<=、>、>=、<>）NOT、AND、OR（优先级从高到低）SELECT *|{[DISTINCT]column|expression[alias],...}FROM table[WHERE condition(s)];三、常用SQL语法及函数介绍——单表查询在查询中限制查询得三、常用SQL语法及函数介绍——单表查询

字符串和日期数据字符串和日期数据应该使用单引号扩起来.字符数据大小写敏感,日期数据格式敏感.SELECT*FROMma_coa_accWHEREcoa_code='001';--系统级科目表'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');--查询零余额科目三、常用SQL语法及函数介绍——单表查询字符串和日期数据字OperatorBETWEEN

...AND...IN(set)LIKEISNULLMeaning两个值之间(包含),

匹配任何列表中的值模糊匹配字符串值是否空值三、常用SQL语法及函数介绍——单表查询其他比较运算符OperatorMeaning三、常用SQL语法及函数介绍—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';--查询凭证主表单据日期在2010-01-01到2010-01-05之间的凭证LowerlimitUpperlimit三、常用SQL语法及函数介绍——单表查询使用BETWEEN运算符SELECT*LowerlimitUpperlimit三、常用SQL语法及函数介绍——单表查询使用LIKE

运算符执行基于通配符的模糊查询，查询包含字符串的所有数据.查询条件可以包含数字或者字符文本:%

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

表示一个字符.SELECT*

FROMgl_coa_acc

WHEREco_code='315002'

ANDacc_codeLIKE'102%';使用LIKE运算符三、常用SQL语法及函数介绍——单表查询使用LIKE运算三、常用SQL语法及函数介绍——单表查询SELECT*

FROMgl_coa_acc

WHEREco_code='315002'

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

FROMgl_coa_acc

WHEREco_code='315002'

ANDacc_codeLIKE'%102%';三、常用SQL语法及函数介绍——单表查询SELECT*

FSELECT*

FROMgl_coa_acc

WHEREco_code='315002'

ANDpar_acc_codeISNULL;--父代码为空的科目（一级科目）三、常用SQL语法及函数介绍——单表查询使用ISNULL运算符SELECT*

FROMgl_coa_acc

WHERE三、常用SQL语法及函数介绍——单表查询条件表达式提供了在SQL语句中使用IF-THEN-ELSE的方法.使用两种方法:CASE表达式DECODE函数CASEexprWHENcomparison_expr1THENreturn_expr1

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

[,search2,result2,...,]

[,default])三、常用SQL语法及函数介绍——单表查询条件表达式提供了在SSELECTnd,co_code,account_name,CASEcoa_codeWHEN'001'THEN'行政科目体系'WHEN'002'THEN'事业科目体系'ENDASkmtxFROMma_co_acc;使用CASE表达式三、常用SQL语法及函数介绍——单表查询SELECTnd,co_code,account_nameSELECTco_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表达式三、常