初级认证培训SQL基本开发

用友政务A 咨询实施部张海2010年5月10日 A 认证培训 SQL开发 财务数据字典及输出报表定制 目录 培训前言数据库及SQL概述常用SQL语法及函数详细介绍输出报表定制流程 培训前言 培训对象培训目标 Oracle初学者 一 培训前言 培训对象 通过本次培训 对Oracle和SQL语言有一定的认识掌握SQL常用的语法和函数掌握财务常用的数据字典掌握输出报表定制的一般流程 一 培训前言 培训目标 目录 培训前言数据库及SQL概述常用SQL语法及函数详细介绍输出报表定制流程 数据库及SQL概述 数据库发展常用数据库Oracle产生与发展SQL语言介绍SQL语言组成及功能SQL开发工具介绍 数据库发展常用数据库a 桌面关系型数据库系统 Access visualfoxpro 基于windows操作系统 满足日常小型办公需要 不提供或提供有限的网络应用功能b 网络关系型数据库系统 MysqlSybasesqlserveroracledb2 二 数据库及SQL概述 Oracle产生和发展目前应用比较广泛的版本是Oracle9i i代表Internet 和10g g代表grid 网格 2007年7月推出oracle11g 二 数据库及SQL概述 SQL语言介绍SQL structuredquerylanguage高级非过程化语言用于存取数据以及查询 更新和管理关系数据库系统 二 数据库及SQL概述 SQL语言组成与功能数据定义语言 DDL 例如 CREATE DROP ALTER等语句数据操作语言 DML 例如 INSERT 插入 UPDATE 修改 DELETE 删除 语句数据查询语言 DQL 例如 SELECT语句数据控制语言 DCL 例如 GRANT REVOKE COMMIT ROLLBACK等语句 二 数据库及SQL概述 SQL开发工具SQLPlusPL SQLDeveloper 二 数据库及SQL概述 目录 培训前言数据库及SQL概述常用SQL语法及函数详细介绍输出报表定制流程 常用SQL语法及函数详细介绍 DDL语句及操作DML语句及操作查询语句单表查询子查询集合连接查询DCL语句及操作函数介绍 CREATETABLE DROPTABLE ALTERTABLE语法 CREATETABLEtable name column nametype CONSTRAINTconstraint def DEFAULTdefault exp DESCRIBEtable name DROPTABLEtable name 三 常用SQL语法及函数介绍 DDL CREATETABLE DROPTABLE ALTERTABLE语法 ALTERTABLEtable nameADD ALTERTABLEtable nameDROP ALTERTABLEtable nameMODIFY 三 常用SQL语法及函数介绍 DDL CREATEVIEW DROPVIEW语法 CREATE ORREPLACE VIEWview nameASsubquery WITHREADONLY DROPVIEWview name 三 常用SQL语法及函数介绍 DDL SELECT DISTINCT column expression alias FROMtable SELECT表示选择哪些列FROM表示从哪些表中查询指定列 全部列 去重 DISTINCT 改变输出 AS 连接符 算数表达式 三 常用SQL语法及函数介绍 DQL 三 常用SQL语法及函数介绍 DQL SELECTfiscal coa code coa nameFROMma coa 科目体系 指定列 SELECT FROMma coa 全部列 三 常用SQL语法及函数介绍 DQL SELECTt fiscalAS 年度 t coa codeAS 科目体系代码 t coa name 科目体系名称 FROMma coat 改变输出 SELECTfiscal 年度 coa code coa nameAS 科目体系 FROMma coa 连接符 三 常用SQL语法及函数介绍 DQL SELECTt co code t nd t co nameFROMma companyt 单位信息表ORDERBY1 去重 SELECTDISTINCT t co code t co nameFROMma companytORDERBY1 三 常用SQL语法及函数介绍 DQL SELECTf 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语法及函数介绍 单表查询 空值是一个不可用的 没有被分配的 或者不能得到的值 空值不同于零或者空格 空值的产生 非主键 未定义notnull都有可能产生空值 SELECT FROMas user 用户表 定义空值Null 三 常用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语法及函数介绍 单表查询 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语法及函数介绍 单表查询 NVL2 空值处理 SELECTDISTINCT user id user id user nameuser name NVL2 pk desc oper time 修改过凭证 未新增或修改凭证 is modvouFROMas log 日志查询 三 常用SQL语法及函数介绍 单表查询 在查询中限制查询得到的结果 通过WHERE子句限制查到的结果 WHERE子句紧跟着FROM子句 使用比较符 NOT AND OR 优先级从高到低 SELECT DISTINCT column expression alias FROMtable WHEREcondition s 三 常用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 查询零余额科目 OperatorBETWEEN AND IN set LIKEISNULL Meaning两个值之间 包含 匹配任何列表中的值模糊匹配字符串值是否空值 三 常用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之间的凭证 Lowerlimit Upperlimit 三 常用SQL语法及函数介绍 单表查询 使用BETWEEN运算符 三 常用SQL语法及函数介绍 单表查询 使用LIKE运算符执行基于通配符的模糊查询 查询包含字符串的所有数据 查询条件可以包含数字或者字符文本 表示零个或者多个字符 表示一个字符 SELECT FROMgl coa accWHEREco code 315002 ANDacc codeLIKE 102 使用LIKE运算符 三 常用SQL语法及函数介绍 单表查询 SELECT FROMgl coa accWHEREco code 315002 ANDacc codeLIKE 102 使用LIKE运算符 SELECT FROMgl coa accWHEREco code 315002 ANDacc codeLIKE 102 SELECT FROMgl coa accWHEREco code 315002 ANDpar acc codeISNULL 父代码为空的科目 一级科目 三 常用SQL语法及函数介绍 单表查询 使用ISNULL运算符 三 常用SQL语法及函数介绍 单表查询 条件表达式 提供了在SQL语句中使用IF THEN ELSE的方法 使用两种方法 CASE表达式DECODE函数 CASEexprWHENcomparison expr1THENreturn expr1 WHENcomparison expr2THENreturn expr2WHENcomparison exprnTHENreturn exprnELSEelse expr END DECODE col expression search1 result1 search2 result2 default SELECTnd co code account name CASEcoa codeWHEN 001 THEN 行政科目体系 WHEN 002 THEN 事业科目体系 ENDASkmtxFROMma co acc 使用CASE表达式 三 常用SQL语法及函数介绍 单表查询 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语法及函数介绍 单表查询 行转列应用 使用DECODE函数 SELECTnd co code account name DECODE coa code 001 行政科目体系 002 事业科目体系 其他 ASkmtxFROMma co acc 三 常用SQL语法及函数介绍 单表查询 三 常用SQL语法及函数介绍 单表查询 排序 ORDERBY SELECT DISTINCT column expression alias FROMtable WHEREcondition s ORDERBYexpression1 ASC DESC 单列排序多列排序 三 常用SQL语法及函数介绍 单表查询 排序 ORDERBY SELECTfiscal coa code acc code acc nameFROMma coa acc 系统会计科目表ORDERBYfiscalDESC coa code acc code SELECTfiscal coa code acc code acc nameFROMma coa accORDERBY1DESC 2 3 三 常用SQL语法及函数介绍 单表查询 分组 SELECT DISTINCT column expression alias FROMtable WHEREcondition s GROUPBYexpression HAVINGcondition s 常用聚合函数 MAX MIN COUNT SUM AVG 三 常用SQL语法及函数介绍 单表查询 分组 GROUPBY子句 SELECTco code单位 fiscal年度 bg project code预算方案 SUM bg amt 预算数FROMbg project detailGROUPBYco code fiscal bg project code 预算管理预算数查询 三 常用SQL语法及函数介绍 单表查询 分组 GROUPBY子句 SELECTco code单位 fiscal年度 fis perd期间 COUNT 1 凭证张数FROMgl vou headGROUPBYco code fiscal fis perdORDERBY1 2 3 查询单位每月凭证数 三 常用SQL语法及函数介绍 单表查询 分组 HAVING子句 SELECTco code单位 fiscal年度 bg project code预算方案 SUM bg amt 预算数FROMbg project detailGROUPBYco code fiscal bg project codeHAVINGSUM bg amt 150000ORDERBY4DESC 预算数大于150000的方案 子查询 子查询 内部查询 在主查询执行前执行 子查询的结果被主查询 外部查询 使用 SELECTselect listFROMtableWHEREexproperator SELECTselect listFROMtable 三 常用SQL语法及函数介绍 子查询 SELECTm co code m co nameFROMma companymWHEREm co codeIN SELECTco codeFROMgl vou head 查询录过凭证的单位 三 常用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语法及函数介绍 子查询 子查询 查询某一个项目的支出合计 只返回一行使用单行比较操作符 Operator MeaningEqualtoGreaterthanGreaterthanorequaltoLessthanLessthanorequaltoNotequalto 三 常用SQL语法及函数介绍 子查询 SELECTbg project code bg project nameFROMbg projectWHEREbg project code SELECTbg project codeFROMbg project detail 下面的语句错在哪里 ERRORatline4 ORA 01427 single rowsubqueryreturnsmorethanonerow 对多行子查询使用了单行比较操作符 三 常用SQL语法及函数介绍 子查询 返回多于一行使用多行比较操作符 操作符IN 含义等于列表中的任何值 三 常用SQL语法及函数介绍 子查询 多列子查询 Mainquery WHERE MANAGER ID DEPARTMENT ID IN Subquery 100901026012450 主查询条件中的每列与多列子查询返回的多行多列的结果进行比较 三 常用SQL语法及函数介绍 子查询 列的比较 与多列子查询的比较可以是 成对比较 Pairwisecomparisons非成对比较 Nonpairwisecomparisons 三 常用SQL语法及函数介绍 子查询 成对比较子查询 显示所有与141和174相同部门 相同经理的雇员的信息 不包括141和174雇员 SELECTemployee id manager id department idFROMemployeesWHERE manager id department id IN SELECTmanager id department idFROMemployeesWHEREemployee idIN 141 174 ANDemployee idNOTIN 141 174 三 常用SQL语法及函数介绍 子查询 非成对比较子查询 SELECTemployee id manager id department idFROMemployeesWHEREmanager idIN SELECTmanager idFROMemployeesWHEREemployee idIN 174 141 ANDdepartment idIN SELECTdepartment idFROMemployeesWHEREemployee idIN 174 141 ANDemployee idNOTIN 174 141 显示所有与141和174相同部门或者相同经理的雇员的信息 不包括141和174雇员 三 常用SQL语法及函数介绍 子查询 相关子查询 相关子查询被用于查询执行时的一行一行处理 每个相关子查询对于外部查询的每行执行一次 GETcandidaterowfromouterquery EXECUTEinnerqueryusingcandidaterowvalue USEvaluesfrominnerquerytoqualifyordisqualifycandidaterow 三 常用SQL语法及函数介绍 子查询 相关子查询 SELECTcolumn1 column2 FROMtable1WHEREcolumn1operator SELECTcolum1FROMtable2WHEREexpr1 expr2 outer outer 三 常用SQL语法及函数介绍 子查询 子查询参照主查询中表的每行的值 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语法及函数介绍 子查询 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语法及函数介绍 子查询 使用EXISTS操作符 EXISTS操作符测试子查询的结果是否存在 如果一个子查询找到了结果 在内部子查询中不在继续执行条件被设为TRUE如果一个子查询没有找到结果 条件被设为FALSE在内部子查询中继续执行查找 三 常用SQL语法及函数介绍 子查询 SELECT FROMma coa accaWHEREis lowest Y ANDEXISTS SELECT1FROMma coa accbWHEREa acc code b par acc code 使用EXISTS操作符 查询科目是否末级设置是否正确 三 常用SQL语法及函数介绍 子查询 SELECT FROMma coa accaWHEREis lowest N ANDNOTEXISTS SELECT1FROMma coa accbWHEREa acc code b par acc code 使用NOTEXISTS操作符 查询科目是否末级设置是否正确 三 常用SQL语法及函数介绍 子查询 UPDATE中的相关子查询 使用相关子查询可以基于另一个表来更新表中的每个符合条件的 UPDATEtable1alias1SETcolumn SELECTexpressionFROMtable2alias2WHEREalias1 column alias2 column 三 常用SQL语法及函数介绍 子查询 UPDATE中的相关子查询 修改存在下级科目的科目属性 是否末级 为 否 UPDATEma coa accaSETa is lowest N WHEREEXISTS SELECT1FROMma coa accbWHEREa acc code b par acc code 三 常用SQL语法及函数介绍 子查询 UPDATE中的相关子查询 修改存在下级科目的科目属性 是否末级 为 否 UPDATEma coa accaSETa is lowest Y WHERENOTEXISTS SELECT1FROMma coa accbWHEREa acc code b par acc code 三 常用SQL语法及函数介绍 子查询 A B UNION UNIONALL A B A B MINUS 三 常用SQL语法及函数介绍 集合 UNION操作返回两个查询结果除去重复值后的交集 A B 三 常用SQL语法及函数介绍 集合 三 常用SQL语法及函数介绍 集合 UNIONALL返回两个查询结果的交集 结果包括重复值 INTERSECT操作符 三 常用SQL语法及函数介绍 集合 MINUS操作符 三 常用SQL语法及函数介绍 集合 集合操作符的提示 在SELECT列表中的表达是必须有同样的数目和类型 括号可以用来修改集合操作的执行顺序 在结果中显示第一个Select语句中的列名 ORDERBY子句 只能在整个集合的最后出现可以按照第一个SELECT语句中的列名 别名或者位置号排序 除了UNIONALL操作外 其他的集合操作都自动去掉重复值 除了UNIONALL操作外 其他集合操作的输出结果默认按照升序的次序排列 三 常用SQL语法及函数介绍 集合 UNION SELECT 集合a 集合名称 ROWNUM序号 trunc dbms random VALUE 0 100 分数FROMdualCONNECTBYROWNUM 3UNIONSELECT 集合b 集合名称 ROWNUM序号 trunc dbms random VALUE 0 100 分数FROMdualCONNECTBYROWNUM 2 三 常用SQL语法及函数介绍 集合 UNIONALL WITHaAS SELECT 张三 name 男 sexFROMdualUNIONALLSELECT 张三 name 男 sexFROMdualUNIONALLSELECT 李四 name 男 sexFROMdual SELECT FROMa 三 常用SQL语法及函数介绍 集合 INTERSECT WITHaAS SELECT 张三 name 男 sexFROMdualINTERSECT SELECT 张三 name 男 sexFROMdualUNIONALLSELECT 李四 name 男 sexFROMdual SELECT FROMa 三 常用SQL语法及函数介绍 集合 连接查询的类型 内连接外连接 左外连接 右外连接 全外连接 交叉连接自连接 三 常用SQL语法及函数介绍 连接查询 内连接 相等连接不等连接 三 常用SQL语法及函数介绍 连接查询 SELECTFROMtable name1 table name2WHEREcondition s 三 常用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 单位及帐套信息 内连接 外连接 左外连接 左外连接返回那些存在于左表而右表中却没有的行 再加上内连接的行右外连接 右外连接返回那些存在于右表而左表中却没有的行 再加上内连接的行全外连接 两边表中的内容全部显示注意连接条件的对应关系 比如1对多关系的影响 三 常用SQL语法及函数介绍 连接查询 SELECTFROMtable name1 LEFT RIGHT FULL JOINtable name2ONcondition s 三 常用SQL语法及函数介绍 连接查询 SELECTa co code a nd a co name b account id b account nameFROMma companyaLEFTJOINma co accbONa nd b ndANDa co code b co codeORDERBY1 左连接 查询各单位建账情况 三 常用SQL语法及函数介绍 连接查询 SELECTa co code a nd a co name b account id b account nameFROMma companya ma co accbWHEREa nd b nd ANDa co code b co code ORDERBY1 换种写法 三 常用SQL语法及函数介绍 连接查询 SELECTa co code a nd a co name b account id b account nameFROMma companyaRIGHTJOINma co accbONa nd b ndANDa co code b co codeORDERBY1 右连接 查询建了帐套的单位 交叉连接 笛卡尔积 三 常用SQL语法及函数介绍 连接查询 SELECTFROMtable name1 table name2 自连接 三 常用SQL语法及函数介绍 连接查询 SELECTFROMtable nametable name1 table nametable name2WHEREcondition s 如果在同一时间需要同一个表中不同行中的信息需要用到自连接 自连接 三 常用SQL语法及函数介绍 连接查询 SELECTt1 co code单位代码 t1 co name单位名称 t1 nd年度 t1 parent co code上级单位代码 t2 co name上级单位名称FROMma companyt1 ma companyt