OracleSQL基础及优化

OracleSQL基础及优化平台应用部陈炅2020年1月10日几个基本SQL语句CASE…WHEN…THEN…ELSE…ENDGROUPBYHAVINGLEFTJOIN、INNERJOINUNION正则表达式匹配REGEXP_LIKE数据转置PIVOT和UNPIVOT行转列LISTAGG和WM_CONCATSTARTWITH…CONNECTBY…两表联立UPDATE游标的使用SQL例句-CASEWHEN--简单Case函数CASEsexWHEN'1'THEN'男'WHEN'2'THEN'女'ELSE'其他'END--Case搜索函数CASEWHENsex='1'THEN'男'WHENsex='2'THEN'女'ELSE'其他'END--分组汇总SELECTgrade,SUM(CASEWHENsex=1THEN1ELSE0END)男生数,SUM(CASEWHENsex=2THEN1ELSE0END)女生数FROMstudentsGROUPBYgradeSELECTCASEWHENsalary=500THEN'1'WHENsalary500ANDsalary=600THEN'2'ELSENULLENDsalary_class,COUNT(*)FROMTableAGROUPBYCASEWHENsalary=500THEN'1'WHENsalary500ANDsalary=600THEN'2'ELSENULLEND;SQL例句-CASEWHEN与DECODE对比--DECODE用法selectDECODE(roleid,1,'A',2,'B','D')FROMgap_ca_user--DECODE与CASEWHEN区别CASEWHEN：标准SQL，可表达范围DECODE：ORACLE专用，不可表达范围SQL例句-GROUPBYHAVINGSELECTusername,mobile,COUNT(*)FROMeps_pb_ordermstWHEREisdeleted=0GROUPBYusername,mobileHAVINGCOUNT(*)2当同时含有where子句、groupby子句、having子句及聚集函数时，执行顺序如下：1执行where子句查找符合条件的数据；2使用groupby子句对数据进行分组3用having子句去掉不符合条件的组SQL例句-GROUPBYROLLUP和CUBE的区别Oracle的GROUPBY语句除了最基本的语法外，还支持ROLLUP和CUBE语句。如果是ROLLUP(A,B,C)的话，首先会对(A、B、C)进行GROUPBY，然后对(A、B)进行GROUPBY，然后是(A)进行GROUPBY，最后对全表进行GROUPBY操作。如果是GROUPBYCUBE(A,B,C)，则首先会对(A、B、C)进行GROUPBY，然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，(C)，最后对全表进行GROUPBY操作SQL例句-GROUPBYROLLUP和CUBE的区别SELECTplantid,supplierid,count(*)FROMeps_pb_ordermstWHEREtenantid=10004554andisdeleted=0GROUPBYplantid,supplieridSELECTplantid,supplierid,count(*)FROMeps_pb_ordermstWHEREtenantid=10004554andisdeleted=0GROUPBYROLLUP(plantid,supplierid)SELECTplantid,supplierid,count(*)FROMeps_pb_ordermstWHEREtenantid=10004554andisdeleted=0GROUPBYCUBE(plantid,supplierid)SQL例句-UNIONSELECT*FROM(SELECT1FROMdualUNIONselect2FROMdualUNIONselect3FROMdual)UNION,UNIONALL,INTERSECT,MINUSSELECT*FROM(SELECT1FROMdualUNIONselect2FROMdualUNIONselect4FROMdual)UNION：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；UNIONAll：对两个结果集进行并集操作，包括重复行，不进行排序；INTERSECT：对两个结果集进行交集操作，不包括重复行，同时进行默认规则的排序；MINUS：对两个结果集进行差操作，不包括重复行，同时进行默认规则的排序。REGEXP_LIKESELECT*FROMeps_su_supplierWHEREsuppliercodeLIKE'S____8';SELECT*FROMeps_su_supplierWHEREREGEXP_LIKE(suppliercode,'S….8');SELECT*FROMeps_su_supplierWHEREREGEXP_LIKE(suppliercode,'S[0-9]{4}8');POSIX正则表达式1，REGEXP_LIKE：与LIKE的功能相似2，REGEXP_INSTR：与INSTR的功能相似3，REGEXP_SUBSTR：与SUBSTR的功能相似4，REGEXP_REPLACE：与REPLACE的功能相似数据转置PIVOT和UNPIVOTSELECT*FROM(SELECTplantId,checkstatusfromeps_pb_ordermst@db_89WHEREtenantid=10004554)PIVOT(COUNT(checkstatus)FORcheckstatusIN(0a,1b,2c,3d,4e,5f,6g))SELECT*FROM(SELECTplantId,checkstatusfromeps_pb_ordermst@db_89WHEREtenantid=10004554)PIVOTXML(COUNT(checkstatus)FORcheckstatusIN(any))SELECTfruit,quarter,qtyFROM(SELECT'Apple'fruit,100q1,200q2,300q3,400q4FROMdualUNIONSELECT'Orange'fruit,200q1,300q2,400q3,500q4FROMdualUNIONSELECT'Banana'fruit,300q1,400q2,500q3,600q4FROMdual)UNPIVOT(qtyFORquarterIN(q1,q2,q3,q4))行转列LISTAGG和WM_CONCATWITHtempAS(SELECT'China'nation,'Guangzhou'cityFROMdualUNIONSELECT'China'nation,'Shanghai'cityFROMdualUNIONSELECT'China'nation,'Beijing'cityFROMdualUNIONSELECT'USA'nation,'NewYork'cityFROMdualUNIONSELECT'USA'nation,'Bostom'cityFROMdualUNIONSELECT'Japan'nation,'Tokyo'cityFROMdual)SELECTnation,LISTAGG(city,',')WITHINGROUP(ORDERBYcity)FROMtempGROUPBYnationSELECT'createorreplaceviewasselect'||WM_CONCAT(column_name)||'fromeps_ba_base'sqlStrFROMuser_tab_columnsWHEREtable_name='EPS_BA_BASE';SQL例句-两表联立updateUPDATEtableAaSETa.userName=(selectb.usernameFROMtableBbwhereb.userID=a.userIDANDrownum=1)WHEREEXISTS(SELECT1FROMtableBcWHEREc.userID=a.userID)自定义函数eps_getworktimediffCREATEORREPLACEFUNCTIONeps_getworktimediff(v_startdatetimeINDATE,--开始日期时间v_enddatetimeINDATE,--结束日期时间v_tenantidINNUMBER--租户ID)RETURNNUMBERAS…RETURNhours;END;数据库对象Table：系统表、用户表、临时表（事务级、会话级）Index：Primarykey、Unique、Clustered、ForeignkeyStoredProcedureSequence、View、Default、Trigger、Cursor等数据库对象-table-临时表会话级CREATEGLOBALTEMPORARYTABLETMP_TEST(IDNUMBER,NAMEVARCHAR2(32))ONCOMMITPRESERVEROWS;事务级CREATEGLOBALTEMPORARYTABLETMP_TEST(IDNUMBER,NAMEVARCHAR2(32))ONCOMMITDELETEROWS;系统表的应用selecta.table_name,b.comments,a.column_name,a.data_type,a.data_length,a.data_precision,a.data_scale,a.nullablefromdba_tab_columnsa,user_col_commentsbwherea.table_name=b.table_nameanda.column_name=b.column_nameanda.owner='YUNCAIJIA'anda.table_namelike'EPS%'orderbya.table_name,a.column_name数据库对象-storedprocedureEPS_SUMMARYPPR锁行级锁、表级锁共享锁、排他锁乐观锁、悲观锁(forupdateforupdatenowait)锁表查询及解锁锁表查询及解锁锁查询selectp.spid,a.serial#,c.object_name,b.session_id,b.oracle_username,b.os_user_namefromv$processp,v$sessiona,v$locked_objectb,all_objectscwherep.addr=a.paddranda.process=b.processandc.object_id=b.object_id;解锁altersystemkillsession‘sid,serial#’;SQL优化-原则开发人员不能只注重功能的实现，不管性能如何开发人员不能把Oracle当成一个黑盒子，必须了解其结构、处理SQL和数据的方法必需遵守既定的开发规范未经过SQL语句优化的模块不要上线SQL语句优化的过程定位有问题的语句检查执行计划检查执行过程中优化器的统计信息分析相关表的记录数、索引情况改写SQL语句、使用HINT、调整索引、表分析有些SQL语句不具备优化的可能，需要优化处理方式达到最佳执行计划SQL语句的处理过程在共享池中查找SQL语句检查语法检查语义和相关的权限合并(MERGE)视图定义和子查询确定执行计划在语句中查找绑定变量赋值(或重新赋值)1.应用执行计划2.执行必要的I/O和排序操作1.从查询结果中返回记录2.必要时进行排序3.使用ARRAYFETCH机制SQL优化的一