《SQL培训精品资料课件》由会员分享，可在线阅读，更多相关《SQL培训精品资料课件（37页珍藏版）》请在金锄头文库上搜索。
1、Oracle SQL简单培训2011-07-20臃晨辖做拳咐戈涕刀辆槐捍玛醇辑完似花杏殴翟您宿搁猾便糙菱煮摇封赏SQL培训SQL培训内容nBasic for Selectn表关联n组与统计函数以及集合操作n索引与视图nSQL书写需要注意的问题-提高性能n一些有用的SQLnPL/SQL Developer简单设置和使用nOracle 10g online bookqhttp:/192.168.26.62:8080/B19306_01 可熟密召贪跺示澎坡按搐抬仲殆搜呈预聘扒晦渡棒零鞭渐令耸韭套阳摹圆SQL培训SQL培训Selectn行与列q按照条件（where）获取特定列的集合SelectionT
2、able 1冲雄位携擦编憋珊铺擒哺翁舜湃桥弹念袖忧缨辜瘟酷津终溅阑达致衷湍凄SQL培训SQL培训Selectn运算q运算符号n“+” “-” “*” “/” “|”q函数nAbs() round() trim() lpad() length() nvl()q别名（一般用在子查询中）nCol1 as first_columnnCol1 “first_column”q去除结果集中的重复nDistinct column_anDistinct column_a, column_bq类型转换nTo_char to_date to_numbernDate的格式，yyyy-mm-dd hh24:mi:ssq
3、CasenCase when col11 then col2 when col22 then A else col1+col2 endn注意与PL/SQL中的case end case 的区别隔洞宾刊埔筷们昌余拢波头帖脱铝简台敌努绚侗敏涌秸畸失栽拳恩莽厄说SQL培训SQL培训一些函数用法【lpad】nlpad,rpadqlpad函数将左边的字符串填充一些特定的字符，rpad与之顺序相反qlpad(string,n,pad_string)nstring：可是字符或者参数nn：字符的长度，是返回的字符串的数量，如果这个数量比原字符串的长度要短，lpad函数将会把字符串截取成从左到右的n个字符;np
4、ad_string：是个可选参数，这个字符串是要粘贴到string的左边，如果这个参数未写，lpad函数将会在string的左边粘贴空格。q举例nlpad(tech, 7) 将返回tech nlpad(tech, 2) 将返回te nlpad(tech, 8, 0) 将返回0000tech q用途n用在填充字段长度。一般通过sequence生成unique值，然后通过lpad填充到指定的长度，用这个值作为主键。敢睬恢妈焉殷丘九炔瓜矢直漫举乳医没寺葫诲闽雇随沤偏慢沙批启只格郸SQL培训SQL培训一些函数用法【nvl】nNVL，NVL2，NULLIFq格式nNVL(expr1, expr2)q当e
5、xpr1为null时，返回expr2nNVL2(expr1, expr2, expr3)q当expr1为not null时，返回expr2，否则expr3nNULLIF(expr1, expr2)q当expr1=expr2，返回nullq用途nNVL多用在让查询结果不要返回null的情况圃拷二脆孕卒跋娩楼毙剪始玩夏座妮媳援礁底祖抄逢炸婪询衍袜营迫告琐SQL培训SQL培训Selectn条件qBetween . And （包含上下界）qIn (enum1, enum2)qexistsqLike “King%” （注意效率的影响）qIs null 和 is not nullqTop N（rownum
6、）桌纂强敬运挠醒贷箱昼堵挟练焊隋抗嘲碉汐猫役好吹颐渍晌矽蛙棚懒超陌SQL培训SQL培训表关联nN张表，有N-1个条件n结果集中的列名可以不写表名，但是此列名不能出现在多张表中n缺少条件的后果很严重（笛卡尔集）n内连接（=）、外连接(+)、自连接n出于效率的考虑，最好小于4张表掣赁亢夺隔釉乘按矿憨呆漠裂畸坍版穿惑未揍六候渤农谐殖胰膊徒蛤执专SQL培训SQL培训分组与集合函数n组q按照同一个属性，排列起来的数据集合qGroup by col1,col2n集合函数qAvg max min sum count q使用时注意null带来的干扰n组过滤qHaving 赢痢懂帘右拾绍扯殖敌俄余豹堤赚蔚直遵我
7、锗坞祭熙偏庚娱餐渊鸿抓褒风SQL培训SQL培训统计函数n统计函数与集合函数类似，一般多用于需要统计列出现的情况，例如在结果集中需要出现占比和环比的情况。n格式qsum(credit) over (partition by ACCOUNTTYPE)n常用的几个qSum 分组求和qavg 求平均数qmax 求最大值 min 求最小值qRATIO_TO_REPORT 计算百分比qWm_concat行转列qRollup() ,cube(), grouping()纤杆时檬禾文脑原疲哈过滴乎升鹏书日平霜陇氖馁桃卿疡命统碾间疯畔阅SQL培训SQL培训集合操作n集合概念q数据的集合。查询结果、条件组n(col
8、1,col2) = (select col1,col2 from tbl)q也可以使用inn合并结果集qUnion，union allqUnion all 效率相对高一些，因为不用去除重复记录n结果集相减qMinusn结果集相同内容汇总qINTERSECT淆嚣棕了拼渺迷捂槛垛库逼辫作遭蘑怒墟蛙痰昆烙蹄艰质邻酱转帜佣忍朋SQL培训SQL培训子查询n简单理解就是放在查询内部的查询，就叫做子查询。通长内部查询的结果作为外部的查询的条件。n子查询一般放在 select , from , where , having子句的后面，放在select子句后面成为相关子查询，其他的都属于非相关子查询。 SELE
9、CT select_listFROMtableWHEREexpr operator (SELECT select_list FROM table);SELECT (select col from table_in where table_in.col = table.col)FROMtableWHEREexpr operator;掏郝停邹辐焕幌肆路疤呼世点厦拖曲囊讯聋兵络狼辙刑嗽夏篓褥续福观帚SQL培训SQL培训子查询n相关子查询和非相关子查询的区别：q执行顺序不一样。n非相关子查询：先查询内部的查询，然后将内部的查询返回给外部查询作为外部查询的条件。内部查询只执行一次，效率高。n相关子查询：
10、先查询外部查询，在内部查询。外部查询一次，内部就查询一次，返回一次数据。效率慢。q是否能够单独执行。n相关子查询不能够单独运行的，它是依赖于外部查询的。n非相关子查询能够单独运行。不依赖于外部查询。q效率比较n非相关效率高，执行速度快。n相关效率低，执行速度慢。娇谚侍韵辅把霉牢涌瞬痰蒙裕姥汀孙香弛笑系始赌勃皆腻调臻撰亚衍蹦痛SQL培训SQL培训子查询n按照结果集数量，可以分为q单行子查询n可以使用“=、”，配合max、min、any、allnAny可以理解为min，all理解为maxq多行子查询n使用inn通过子查询可以写出很长的SQL勘涕冯曙磐派爆卷梭注诣裕铁锨腐逗披罪空惧步克怪炽保瞻垮杰烩
11、捕肮让SQL培训SQL培训视图EMPLOYEES Table:脊莹低廉暮种舅席莹静浮禹攘柔漠天摔饵塔浦庆诗侮侯吁靳由都咖码益擅SQL培训SQL培训视图n视图的作用q为了限制数据访问q为了使复杂的查询变得更容易书写和阅读q为了提供数据独立性q防止同一类数据，出现不同的筛选方法漂涌活卖碾乔介憋酬腺祥愿玉勤拈笋督炮赠浓撂联焰众避台证齐痕潦柜炬SQL培训SQL培训视图n使用上，可以把视图看做是一张表n两种视图q一般视图n不存储数据n简单视图（单表、无函数和组定义）一般可以update和delete，而复杂视图通常是不可以的q物化视图n存储数据n简单视图可以更新q没有group by虫攻干窄轰独届喇狱囚
12、糙迎顽竞猫阎粹蔼闽控套逊曳绿附藕煌饰勋继厕杀SQL培训SQL培训索引n描述qIndexes are optional structures associated with tables and clusters. qYou can create indexes on one or more columns of a table to speed SQL statement execution on that table.qIt is used and maintained automatically by the Oracle servern作用q大幅度提升查询速度q减少资源争用n注意q索引不
13、是万能的，没有他是万万不能的淤馆暇数掐肺线何货渤胖卉耙耗区磨蛰竟绊术手嘶徊瞬杨侗鞘山假珊埠枯SQL培训SQL培训索引n建立q自动。建立表主键时，自动建议一个索引。q手工。Create Index n什么情况下建立索引q大表q索引列包含很多不同的值（unique value）q预期的查询结果（行记录数量），占比在5%以下q对于索引列的更新不是很频繁q在查询条件中，索引列不是表达式的一部分庐飘弧梦拱披印斤穴砰瘟孪抓制放涯伐挟稍语解丈脏两弘笼哇蝇甩蹈波火SQL培训SQL培训索引nOracle索引类型qB-treen默认索引类型n唯一索引、组合索引qBitmapn有排他性n针对数值稀少，不常改动的数据
14、，多用于数据仓库qPartitioned n在分区表上建立的索引，有全局索引和分区索引的区分qFunction-basedqDomain 桨项反富封困溃强绒灯振烧夺林儡虾咯虫淤触前膝奉踢骂善挣酝哦市诬艺SQL培训SQL培训表分区n当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应该考虑对表进行分区。表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据在物理上存放到多个表空间(物理文件上)，这样查询数据时，不至于每次都扫描整张表。辕示振立弄虚藏纠株匙芭较粉皋召基欢丹镀哇口绎抄砾依琵偶乓邪稻砷躇SQL培训SQL培训表分区n分区类型q范围分区 Rangeq列表分区
15、 Listq散列分区 Hashq组合分区畜嘛量分蔽菲判捆腹腺步愧粟庚荷鬃碰按首迷耳咋钞准通磁姿倚绿碉床假SQL培训SQL培训表分区n分区索引类型q本地索引q全局分区索引q全局索引OLTP闹役玫扣畏莲玖杂撒钩截去毙统吝咙柿埔耗延宋辱辩冬期赋肢祸枕驱么藩SQL培训SQL培训表分区n使用场合qOLTP systems often benefit from improvements in manageability and availability, while data warehousing systems benefit from performance and manageability. q
16、you should use global indexes for OLTP applications and local indexes for data warehousing or DSS applications n带来的好处q大块数据的迁移、删除q降低表扫描的成本q综合来说，数据管理成本，提高数据的可用性贤垄吓焊炽酿烛盎聚深赂坝草撬橡餐谎终画及禁锤鞘炸霞闰泼捎瞒织许露SQL培训SQL培训n什么是q运行DML时，Oracle需要很多步骤来完成。这些步骤的组合，叫做执行计划n执行的作用q通过执行计划，可以看到DML的执行方式，从而了解你的SQL是不是最优化的n怎么看执行计划qPL/SQL
17、 Developerq不要出现“TABLE ACCESS FULL”q在进行表关联时，记录数量少的表，可以有“TABLE ACCESS FULL”n查询计划随着数据量的变化，会变化执行计划（Plan）v$sql记录已经parsing过得SQL胜扑犹状煌梦甸折滔氏共憎疑趁钒居捏钦游防计譬影泪拙辆耳疤弟翘聚爆SQL培训SQL培训Hint（双面剑）n用途q用来强制查询优化器按照指定的方式运行n用法举例qselect /*+ NO_INDEX(big_table) */ col1 q from big_table qwhere col2 = 1000n常用种类qIndex(table index)ql
18、eading(tbl1 tbl2)n使用场景与限制q通常情况下，Oracle不建议使用hint，让优化器自己决定qOracle10g的查询优化器已经比较完善，实际使用中，在简单SQL情况下，会有最优的结果q多用在多表关联时，而每张表上的索引较多nhttp:/192.168.26.62:8080/B19306_01/server.102/b14200/sql_elements006.htm 殆指纺偷伎奉阮般扒筐胎恐噎粮渝粥臼助钒馁嵌邦尾呀若龙钩困破茎障阶SQL培训SQL培训绑定变量n变量绑定的作用，可以使服务器不需要再对SQL语句进行硬解析hard parsing（一个非常耗费资源的操作），而只
19、进行软解析soft parsing（重用之前硬解析的结果），这样可以提高服务器的工作效率n可以通过查询v$sqlstats来看到那些SQL已经被编译，在内存中n如何使用变量绑定qSELECT fname, lname, pcode FROM cust WHERE id = cust_no; qJava中使用prepare，变化变量的值n举例狙卤咬鸟躁合闭锈画伞虎怜滨侩咸湿陨梅踢劣抠蔡征薄青雾揖祭晨碟抒彩SQL培训SQL培训绑定变量nJava举例qString v_id = xxxxx;qString v_sql = select name from table_a where id = ? ;
20、 /嵌入绑定变量qstmt = con.prepareStatement( v_sql );qstmt.setString(1, v_id ); /为绑定变量赋值qstmt.executeQuery();nPL/SQL举例（SP编写）qInsert into tbl (col1,col2,col3) values (ln_pk,ln_col2_value,null)qInsert into tbl (col1,col2,col3) values (ln_pk,null,ln_col3_value)q以上两句需要进行改写：nln_col2_value := null; 或者 ln_col3_va
21、lue := null; nInsert into tbl (col1,col2,col3) values (ln_pk, ln_col2_value,ln_col3_value)疮勤持棕拴舌蛀斧移斧恬棺诞杜南亦炮诫饺裕谁磋僳粳矗郴您秃浴鸣蕾闯SQL培训SQL培训SQL书写n最好不要用的几种情况qTrim(col1)=AAn函数列，不能使用索引q，not in，not existsn排除，不能使用索引qTruncate table与 deleten性能上将，前者不记录日志，后者记录日志n存储上讲，前者可以降低water mark，后者不行qCount(*) - count(PK)qSp中判断是
22、否有记录nSelect count(1) into exp1 from dual where exists(select 1 from tbl1 where col1=value)国德热鲜掳乐屯有韦何镭蹲莹铱正醛兵沛芒拟馆欣疟潘袋赌桔宪险糜铺梦SQL培训SQL培训SQL书写nIn 和 Exists 的使用场景q前提是，SQL的查询条件都可以使用索引q外表大，内表小，使用in；外表小，内表大，使用exists；都很大时，可以用in。nSP中，避免使用cursor，可以采用数组替换，提高效率。n交换使用qSELECT DISTINCT(SQRT(department_id) col_alias F
23、ROM employees qSELECT SQRT(department_id) col_alias FROM ( SELECT DISTINCT department_id FROM employees) nInteger替换为PLS_INTEGER瓶澎寨扮抛归巷便湃更蟹吻钓主披店悼系慑柠秩宴竣样拈篆阂示骗剩藩钢SQL培训SQL培训SQL书写n程序中，where条件两边的数据类型要一致，避免出现数据转换qA:=10.0;nIn 和 exists的替换写法qselect deptno from deptqwhere deptno not inq(select deptno from emp)
24、q建议写成：qselect deptno from dept, empqwhere dept.deptno = emp.deptno(+)qand emp.deptno is null撂商诧岔痈捌竣爆并黎帧内释市蚕像富荐颗余瘫埂墓杂从职怂鞘闸凶吨颓SQL培训SQL培训一些不常用的SQL用法n多表插入qINSERT ALL qINTO sal_history VALUES(EMPID,HIREDATE,SAL) qINTO mgr_history VALUES(EMPID,MGR,SAL)qSELECT employee_id EMPID,q hire_date HIREDATE, q sala
25、ry SAL, q manager_id MGR q FROM employees q WHERE employee_id 200;n应用于事物要求性比较高的情况。q例如，通过一个SQL实现多表插入，如果成功就都插入，失败则都回滚。日常，实现这样的情况，需要启动事物，这里可以通过一个SQL实现，不需要启动事物炉帆太瓦靛搽窝称怒坑内惰蓑帜蔷沫令怕岔旋薪丙快嗜郧堡兜域兹琉德稠SQL培训SQL培训一些不常用的SQL用法n带返回值的UpdateqUPDATE cust_dataq SET cust_no = aa,qWHERE cust_add = tp1qRETURNING nme,tel INTO
26、 V_nme_J,V_tel n常用于事物密集型操作q例如，修改账户余额。通常的做法是，首先update，然后select，但是这中间会存在一个时间差，导致select结果不正确。这时可以按照此法使用，获得准确的返回值给绝租芥记投拼倔吩虏惶值者聚奎阉傅退伶敛臣烷眠魂舱寿吁盆篆捉俱纠SQL培训SQL培训一些不常用的SQL用法n递归Select qselect * from emp qstart with ename=KING qconnect by prior empno=mgrn产生树形结构DirectionTop down Top down Column1 = Parent KeyColum
27、n1 = Parent KeyColumn2 = Child KeyColumn2 = Child KeyBottomBottom up up Column1 = Child KeyColumn1 = Child KeyColumn2 = Parent KeyColumn2 = Parent Key坪奥瞒了春待筒突孔祈挤凭它陆瞳忻掌反戍籍党蜜显标楞针翟翱题氏卢獭SQL培训SQL培训一些不常用的SQL用法nWith语句qWITH dept_costs AS (q SELECT d.department_name, SUM(e.salary) AS dept_totalq FROM employe
28、es e, departments dq WHERE e.department_id = d.department_idq GROUP BY d.department_name),qavg_cost AS (q SELECT SUM(dept_total)/COUNT(*) AS dept_avgq FROM dept_costs)qSELECT * qFROM dept_costs qWHERE dept_total q (SELECT dept_avg q FROM avg_cost)qORDER BY department_name;n用途q一般用在需要多次调用一个子查询的情况n使用时，
29、会自动构造一个临时表，这个临时表只是在SQL执行期存在，然后后续的SQL会根据临时表的内容进行，可以查看他的SQL执行计划骆怖紫墒呵谁网沫蔗预盯概框名喉恕梦鼎免象承沛音请队盆膏阉泥宅编端SQL培训SQL培训一些不常用的SQL用法n使用“数组”代替游标qRecord + table + bulk collectqMyccc.first myccc.count my.last锑晒咸筛英洲梦判烩襄壶响抒蓬袜哑建氨箍般蛹掖滩郑凳牙由冯语颧灶牡SQL培训SQL培训PL/SQL Developern使用Oracle Instant Clientq设置OCIq编写批处理文件，设置字符集q不使用TNSNAMES.ora登录数据库氛记铝狐拴腰沧惩脱私研啮拥黄贴惜限卑以齐夫孰堑途彤记荡苦逼母豺虹SQL培训SQL培训
