SQL培训

OracleSQL简单培训2011-07-20内容BasicforSelect表关联组与统计函数以及集合操作索引与视图SQL书写需要注意的问题-提高性能一些有用的SQLPL/SQLDeveloper简单设置和使用Oracle10gonlinebook行与列按照条件（where）获取特定列的集合SelectionTable1Select运算运算符号“+”“-”“*”“/”“||”函数Abs()round()trim()lpad()length()nvl()别名（一般用在子查询中）Col1asfirst_columnCol1“first_column”去除结果集中的重复Distinctcolumn_aDistinctcolumn_a,column_b类型转换To_charto_dateto_numberDate的格式，yyyy-mm-ddhh24:mi:ssCaseCasewhencol11thencol2whencol22then‘A’elsecol1+col2end注意与PL/SQL中的case–endcase的区别一些函数用法【lpad】lpad,rpadlpad函数将左边的字符串填充一些特定的字符，rpad与之顺序相反lpad(string,n,[pad_string])string：可是字符或者参数n：字符的长度，是返回的字符串的数量，如果这个数量比原字符串的长度要短，lpad函数将会把字符串截取成从左到右的n个字符;pad_string：是个可选参数，这个字符串是要粘贴到string的左边，如果这个参数未写，lpad函数将会在string的左边粘贴空格。举例lpad('tech',7)将返回'︻︻︻tech'lpad('tech',2)将返回'te'lpad('tech',8,'0')将返回'0000tech'用途用在填充字段长度。一般通过sequence生成unique值，然后通过lpad填充到指定的长度，用这个值作为主键。一些函数用法【nvl】NVL，NVL2，NULLIF格式NVL(expr1,expr2)当expr1为null时，返回expr2NVL2(expr1,expr2,expr3)当expr1为notnull时，返回expr2，否则expr3NULLIF(expr1,expr2)当expr1=expr2，返回null用途NVL多用在让查询结果不要返回null的情况Select条件Between..And…（包含上下界）In(enum1,enum2)existsLike“King%”（注意效率的影响）Isnull和isnotnullTopN（rownum）表关联N张表，有N-1个条件结果集中的列名可以不写表名，但是此列名不能出现在多张表中缺少条件的后果很严重（笛卡尔集）内连接（=）、外连接(+)、自连接出于效率的考虑，最好小于4张表分组与集合函数组按照同一个属性，排列起来的数据集合Groupbycol1,col2集合函数Avgmaxminsumcount使用时注意null带来的干扰组过滤Having统计函数统计函数与集合函数类似，一般多用于需要统计列出现的情况，例如在结果集中需要出现占比和环比的情况。格式sum(credit)over(partitionbyACCOUNTTYPE)常用的几个Sum分组求和avg求平均数max求最大值min求最小值RATIO_TO_REPORT计算百分比Wm_concat行转列Rollup(),cube(),grouping()集合操作集合概念数据的集合。查询结果、条件组(col1,col2)=(selectcol1,col2fromtbl)也可以使用in合并结果集Union，unionallUnionall效率相对高一些，因为不用去除重复记录结果集相减Minus结果集相同内容汇总INTERSECT子查询简单理解就是放在查询内部的查询，就叫做子查询。通长内部查询的结果作为外部的查询的条件。子查询一般放在select,from,where,having子句的后面，放在select子句后面成为相关子查询，其他的都属于非相关子查询。SELECTselect_listFROMtableWHEREexproperator(SELECTselect_listFROMtable);SELECT(selectcolfromtable_inwheretable_in.col=table.col)FROMtableWHEREexproperator;子查询相关子查询和非相关子查询的区别：执行顺序不一样。非相关子查询：先查询内部的查询，然后将内部的查询返回给外部查询作为外部查询的条件。内部查询只执行一次，效率高。相关子查询：先查询外部查询，在内部查询。外部查询一次，内部就查询一次，返回一次数据。效率慢。是否能够单独执行。相关子查询不能够单独运行的，它是依赖于外部查询的。非相关子查询能够单独运行。不依赖于外部查询。效率比较非相关效率高，执行速度快。相关效率低，执行速度慢。子查询按照结果集数量，可以分为单行子查询可以使用“=、、”，配合max、min、any、allAny可以理解为min，all理解为max多行子查询使用in通过子查询可以写出很长的SQL视图EMPLOYEESTable:视图视图的作用为了限制数据访问为了使复杂的查询变得更容易书写和阅读为了提供数据独立性防止同一类数据，出现不同的筛选方法视图使用上，可以把视图看做是一张表两种视图一般视图不存储数据简单视图（单表、无函数和组定义）一般可以update和delete，而复杂视图通常是不可以的物化视图存储数据简单视图可以更新没有groupby索引描述Indexesareoptionalstructuresassociatedwithtablesandclusters.YoucancreateindexesononeormorecolumnsofatabletospeedSQLstatementexecutiononthattable.ItisusedandmaintainedautomaticallybytheOracleserver作用大幅度提升查询速度减少资源争用注意索引不是万能的，没有他是万万不能的索引建立自动。建立表主键时，自动建议一个索引。手工。CreateIndex什么情况下建立索引大表索引列包含很多不同的值（uniquevalue）预期的查询结果（行记录数量），占比在5%以下对于索引列的更新不是很频繁在查询条件中，索引列不是表达式的一部分索引Oracle索引类型B-tree默认索引类型唯一索引、组合索引Bitmap有排他性针对数值稀少，不常改动的数据，多用于数据仓库Partitioned在分区表上建立的索引，有全局索引和分区索引的区分Function-basedDomain表分区当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应该考虑对表进行分区。表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据在物理上存放到多个表空间(物理文件上)，这样查询数据时，不至于每次都扫描整张表。表分区分区类型范围分区Range列表分区List散列分区Hash组合分区表分区分区索引类型本地索引全局分区索引全局索引OLTP表分区使用场合OLTPsystemsoftenbenefitfromimprovementsinmanageabilityandavailability,whiledatawarehousingsystemsbenefitfromperformanceandmanageability.youshoulduseglobalindexesforOLTPapplicationsandlocalindexesfordatawarehousingorDSSapplications带来的好处大块数据的迁移、删除降低表扫描的成本综合来说，数据管理成本，提高数据的可用性什么是运行DML时，Oracle需要很多步骤来完成。这些步骤的组合，叫做执行计划执行的作用通过执行计划，可以看到DML的执行方式，从而了解你的SQL是不是最优化的怎么看执行计划PL/SQLDeveloper不要出现“TABLEACCESSFULL”在进行表关联时，记录数量少的表，可以有“TABLEACCESSFULL”查询计划随着数据量的变化，会变化执行计划（Plan）v$sql记录已经parsing过得SQLHint（双面剑）用途用来强制查询优化器按照指定的方式运行用法举例select/*+NO_INDEX(big_table)*/col1frombig_tablewherecol2=1000常用种类Index(tableindex)leading(tbl1tbl2)使用场景与限制通常情况下，Oracle不建议使用hint，让优化器自己决定Oracle10g的查询优化器已经比较完善，实际使用中，在简单SQL情况下，会有最优的结果多用在多表关联时，而每张表上的索引较多绑定变量变量绑定的作用，可以使服务器不需要再对SQL语句进行硬解析hardparsing（一个非常耗费资源的操作），而只进行软解析softparsing（重用之前硬解析的结果），这样可以提高服务器的工作效率可以通过查询v$sqlstats来看到那些SQL已经被编译，在内存中如何使用变量绑定SELECTfname,lname,pcodeFROMcustWHEREid=cust_no;Java中使用prepare，变化变量的值举例绑定变量Java举例Stringv_id='xxxxx';Stringv_sql='selectnamefromtable_awhereid=?';//嵌入绑定变量stmt=con.prepareStatement(v_sql);stmt.setString(1,v_id);//为绑定变量赋值stmt.executeQuery();PL/SQL举例（SP编写）Insertintotbl(col1,col2,col3)values(ln_pk,ln_col2_value,null)Insertintotbl(col1,col2,col3)values(ln_pk,null,ln_col3_value)以上两句需要进行改写：ln_col2_value:=null;或者ln_col3_value:=null;Insertintotbl(col1,col2,col3)values(ln_pk,ln_col2_value,ln_col3_value)SQL书写最好不要用的几种情况Trim(col1)=‘AA’函数列，不能使用索引，notin，notexists排除，不能使用索引Truncatetable与delete性能上将，前者不记录日志，后者记录日志存储上讲，前者可以降低watermark，后者不行Count(*)-count(PK)Sp中判断是否有记录Selectcount(1)intoexp1fromdualwhereexists(select1fromtbl1wherecol1=value)SQL书写In和Exists的使用场景前提是，SQL的查询条件都可以使用索引