数据库开发-SQL-王坤朋.ppt

为什么不能这么执行 = *为什么不能这么执行* = SELECT 3, 1.1 货物贸易, A.S_BANKORGCODE, SUM(CASE WHEN A.C_TYPE = I AND A.D_PAYEEDATE BETWEEN DTHISMONTHSTART AND DTHISMONTHEND THEN A.F_AMT ELSE 0 END) AS F_AMT1,-收入部分本期发生额 SUM(CASE WHEN A.C_TYPE = I AND A.D_PAYEEDATE BETWEEN DTHISYEARSTART AND DTHISMONTHEND THEN A.F_AMT ELSE 0 END) AS F_AMT2,-收入部分1当月累计额 SUM(CASE WHEN A.C_TYPE = I AND A.D_PAYEEDATE BETWEEN DLASTYEARSTART AND DLASTMONTHEND THEN A.F_AMT ELSE 0 END) AS F_AMT3,-收入部分去年1同月累计额 SUM(CASE WHEN A.C_TYPE = P AND A.D_PAYEEDATE BETWEEN DTHISMONTHSTART AND DTHISMONTHEND THEN A.F_AMT ELSE 0 END) AS F_AMT4,-支出部分本期发生额 SUM(CASE WHEN A.C_TYPE = P AND A.D_PAYEEDATE BETWEEN DTHISYEARSTART AND DTHISMONTHEND THEN A.F_AMT ELSE 0 END) AS F_AMT5,-支出部分1-当月累计额 SUM(CASE WHEN A.C_TYPE = P AND A.D_PAYEEDATE BETWEEN DLASTYEARSTART AND DLASTMONTHEND THEN A.F_AMT ELSE 0 END) AS F_AMT6 -支出部分去年1同月累计额 FROM SESSION.TB_INCOMEPAYOUT_GOODS A WHERE F_AMT1 0 OR F_AMT3 0 OR F_AMT5 0 GROUP BY A.S_BANKORGCODE = SQL执行顺序 = 一条普通的SQL = SELECT 3, 1.1 货物贸易, A.S_BANKORGCODE, SUM(CASE WHEN A.C_TYPE = I AND A.D_PAYEEDATE BETWEEN DTHISMONTHSTART AND DTHISMONTHEND THEN A.F_AMT ELSE 0 END),- 收入部分本期发生额 SUM(CASE WHEN A.C_TYPE = I AND A.D_PAYEEDATE BETWEEN DTHISYEARSTART AND DTHISMONTHEND THEN A.F_AMT ELSE 0 END),- 收入部分1当月累计额 SUM(CASE WHEN A.C_TYPE = I AND A.D_PAYEEDATE BETWEEN DLASTYEARSTART AND DLASTMONTHEND THEN A.F_AMT ELSE 0 END),- 收入部分去年1同月累计额 SUM(CASE WHEN A.C_TYPE = P AND A.D_PAYEEDATE BETWEEN DTHISMONTHSTART AND DTHISMONTHEND THEN A.F_AMT ELSE 0 END),- 支出部分本期发生额 SUM(CASE WHEN A.C_TYPE = P AND A.D_PAYEEDATE BETWEEN DTHISYEARSTART AND DTHISMONTHEND THEN A.F_AMT ELSE 0 END),- 支出部分1-当月累计额 SUM(CASE WHEN A.C_TYPE = P AND A.D_PAYEEDATE BETWEEN DLASTYEARSTART AND DLASTMONTHEND THEN A.F_AMT ELSE 0 END) - 支出部分去年1同月累计额 FROM SESSION.TB_INCOMEPAYOUT_GOODS A GROUP BY A.S_BANKORGCODE HAVING ( SUM(CASE WHEN A.C_TYPE = I AND A.D_PAYEEDATE BETWEEN DTHISMONTHSTART AND DTHISMONTHEND THEN A.F_AMT ELSE 0 END) 0 OR SUM(CASE WHEN A.C_TYPE = I AND A.D_PAYEEDATE BETWEEN DLASTYEARSTART AND DLASTMONTHEND THEN A.F_AMT ELSE 0 END) 0 OR SUM(CASE WHEN A.C_TYPE = P AND A.D_PAYEEDATE BETWEEN DTHISYEARSTART AND DTHISMONTHEND THEN A.F_AMT ELSE 0 END) 0 ) = 执行顺序 1：FROM 字句 组装来自不同表或试图的数据 2：WHERE 对FROM 返回的数据进行过滤 注意（WHERE 后边 表达式的字段都是FROM 后表中的字段，如果出现伪列谓词动作会报错） 3：GROUP BY 对WHERE 过滤后的数据进行分组 4：聚集函数如SUM、COUNT 等 如果聚集函数中有表达式 先计算表达式在执行聚集函数 5：HAVING 对分组后的经聚合函数计算后的数据进行过滤 6：计算所有的表达式如 CASE WHEN 7：使用ORDER BY 对结果集进行排序 8：SELECT 集合输出 经常用到的表连接 JOIN 内连接等价于等值连接 SELECT * FROM EMP A JOIN DEPT B ON A.DEPTNO = B.DEPTNO (连接键主 外键，如果不是则必须建立索引) 等价于 SELECT * FROM EMP A,DEPT B WHERE A.DEPTNO = B.DEPTNO LEFT OUTER JOIN 左外连接（存在某种业务关联 ） 等值连接是最初级的连接 也是最不负责任的连接 比如 我想知道员工表中，每个部门工资总 和是多少。 SELECT A.DEPTNO,B.DNAME,SUM(A.SAL) FROM EMP A LEFT OUTER JOIN DEPT B ON A.DEPTNO = B.DEPTNO GROUP BY A.DEPTNO,B.DNAME 这里 DEPT（部门表起到翻译作用） SQL是面向集合的 UNION 排序去重 并集 前提条件列的数量和数据类型必须兼容 UNION ALL并集 UNION 等价于 SELECT DISTINCT FROM ( UNION ALL ) DISTINCT 进行隐式排序 子查询 SELECT * FROM ( SELECT * FROM EMP WHERE EMPNO = 7802 ) 标量子查询 放在SELECT 列中的子查询 SELECT E.ENAME,D.LOC, ( SELECT B.COMM FROM BONUS B WHERE B.ENAME = E.ENAME ) AS RECEIVED FROM EMP E,DEPT D WHERE E.DEPTNO = D.DEPTNO 这是最简单的自查询 介绍个复杂的子查询 临时表 三种 1、（）内的有名，匿名 做子查询时用到 2、WITH TMP，调试汇总时用到 3、存储过程定义的临时表 函数-单行 单行函数 常用到的 SUBSTR,截取字符串 | 字符连接 COALESCE（A,B） 当A出现null时用B来代替 CASE WHEN 表达式 IS NULL IS NOT NULL 函数-多行函数 AVG SUM COUNT如果count函数内的字段出现重复值 怎么办？ MAX,MIN 谓词 出现在WHERE后面的关键字 = = = 1000 常见问题 在一个表中查找与其他表不匹配的记录 想查询中增加关联而不影响其他关联 消除笛卡尔积（n-1原则） 如何处理中间结果集全零数据 如何处理聚集于联接混合操作 1：在调用聚集函数时使用关键字 2：使用子查询，先消除重复值。 删除重复记录 DELETE FROM DUPES WHERE ID NOT IN (SELECT MIN(ID) FROM DUPES GROUP BY NAME) 索引 索引：是表的一个或个列的键值的有序列表，存在索引文件中。 采取B+树形式存储 CREATE INDEX IDX_TEST1 ON TABPE(COLUMN1); CREATE INDEX IDX_TEST1 ON TABLE(COLUMN1，COLUMN2，COLUMN3) 列出表的索引 SELECT * FROM EMP WHERE DEPTNO = 20 是否走索引 DB2 SELECT * FROM SYSCAT.INDEXES A JOIN SYSCAT.INDEXCOLUSE B ON A.INDNAME = B.INDNAME WHERE A.TABNAME = TB_INCOME_ITEM AND A.TABSCHEMA = DB2ADMIN ORACLE SELECT * FROM SYS.ALL_IND_COLUMNS WHERE TABLE_NAME = EMP AND TABLE_OWNER = SCOTT 经典案例 SELECT C.* FROM ( SELECT A.I_SEQNO,A.S_LEVYNO,A.S_CUSTORGCODE,A.S_CUSTORGNAME, A.D_CUSTOMSDATE,A.C_TRADECLASS,A.C_BALANCETYPE,A.F_ENTRTYAMT, A.C_LISTOPERTYPE ,B.S_BANKORGCODE AS BANKORGCODE,B.S_BANKORGNAME AS BANKORGNAME, B.S_PAYEENAME AS SPAYEENAME,B.C_PAYEEATTR AS CPAYEEATTR, B.S_PAYEEORGCODE AS SPAYEEORGCODE,B.S_SWIFTBIC AS SSWIFTBIC, B.D_PAYEEDATE AS DPAYEEDATE FROM TB_INCOME_LIST A LEFT OUTER JOIN TB_INCOME_ITEM B ON A.S_LEVYNO = B.S_LEVYNO WHERE A.C_BALANCETYPE 3 AND A.S_CUSTORGCODE = 788832354 AND A.C_TRADECLASS = 2 )C LEFT OUTER JOIN TR_DAY_RECQUAN_DETAIL D ON D.S_ORGCODE = C.S_CUSTORGCODE AND D.D_CUSTOMERDATE = C.DPAYEEDATE AND D.S_TRATYPE = C.C_TRADECLASS 重新收集统计信息 runstats on table TB_INCOME_ITEM on KEY columns and indexes all tablesample SYSTEM(10); runstats on table TB_INCOME_LIST on KEY columns and indexes all tables