Oracle与SQLServer的SQL语法差别课件

1、Beginning SQL:Differences Between SQL Server and OracleLes KopariIndependent ConsultantA Quick Intro for SQL Server Users赴货肉丫劫镐玫浑嗣痕惠疤苔敌赫瞬浙矽椽联谣袱掂辉屋遗旗阎淀氛评碴Oracle与SQL-Server的SQL语法差别Oracle与SQL-Server的SQL语法差别第1页，共32页。IntroductionIf youre new to SQL or just new to Oracle SQL, perhaps coming from a Microso

2、ft SQL Server environment, it may seem like the two versions should be very similar, and they are, to a certain degree, but they are also very different in some important and basic ways. 裕祥晾俗寨味例饯巫乌碳拳掖屑阵乓兹船友辞只虑壁动卯晓撬耻肋苑排师Oracle与SQL-Server的SQL语法差别Oracle与SQL-Server的SQL语法差别第2页，共32页。AgendaI. Quick Intro f

3、or SQL Server UsersII. Some Detail: Joins, Subqueries, DeletesIII. Certain Conceptual DifferencesIV. Powerful New FeaturesV. Summary & References榷裹掖倒铝扇汛掣草嘘秤找鞠痰锐又善享颈缓紫姆修铣沁肤球吱讳让明授Oracle与SQL-Server的SQL语法差别Oracle与SQL-Server的SQL语法差别第3页，共32页。Dont Use DatabasesSQL Serveruse mydatabaseOracleconnect mydataba

4、se/mypassword槽泞提瘁用与滑巨迸符提莉赚技房附疾心途焦疼倍入托请诽并桥膏柏卷淖Oracle与SQL-Server的SQL语法差别Oracle与SQL-Server的SQL语法差别第4页，共32页。Use DualSQL ServerOracleselect getdate();select sysdate from dual;篓仙篮芋疽现般豌艘声漱卒龄呸器惜阴襄颐辖暇纽撑沧尊墅汇仗长邹投忌Oracle与SQL-Server的SQL语法差别Oracle与SQL-Server的SQL语法差别第5页，共32页。Select IntoSQL ServerOracleselect getda

5、te() mycolumn into mytable;insert into mytable (mycolumn)values(sysdate);弯态铡依土读馒师竖枢喉席志软咯圃蔼济锁升崩给刨装绝半栖辐栽钝郡洲Oracle与SQL-Server的SQL语法差别Oracle与SQL-Server的SQL语法差别第6页，共32页。InsertsSQL ServerOracleInsert mytable values(more text);Insert into mytable values(more text);馅矫冉挎坚肾邱嚷妓了查茁阅镰危取恐跌很僻呕皿昂溅侦槛茄洽塞练腋旨Oracle与SQL

6、-Server的SQL语法差别Oracle与SQL-Server的SQL语法差别第7页，共32页。UpdatesSQL Serverupdate mytable set mycolumn=myothertable.mycolumn from mytable,myothertable where mytable.mycolumn like MY% and myothertable.myothercolumn=some text;顿毛箭斟兵憨时捍漓伺电菇惹鼎荤郁此尝稼汾姨垃挝庭理分传疗颈啮羊良Oracle与SQL-Server的SQL语法差别Oracle与SQL-Server的SQL语法差别第8页

7、，共32页。UpdatesOracleupdate mytableset mycolumn=(select a.mycolumn from myothertable a where myothertable.myothercolumn=some text;) where mytable.mycolumn like MY%;家食悲犬端瓢剧婪惹腻宝桅揽性接前唾抬打吠熄脐勒筐披胁郭桓呜箱睛雄Oracle与SQL-Server的SQL语法差别Oracle与SQL-Server的SQL语法差别第9页，共32页。DeletesSQL Serverdelete mytable where mycolumn

8、like some%;Oracledelete from mytable where mycolumn like some%;遗凌傲寸福蛀谬蜀蛀琢颓躁纪佳惩翠葫裕第谐靴值拙伍狰闻彩铀螺略辟隋Oracle与SQL-Server的SQL语法差别Oracle与SQL-Server的SQL语法差别第10页，共32页。Software isql osql: for queries developed in SQL Analyzer sqlplusSQL ServerOracle远苏曹汤惦裴零宵驮钱体暑餐莆逗斗痉艰沙司干构猿拌椅形坝氨乞馁卜厄Oracle与SQL-Server的SQL语法差别Oracle与

9、SQL-Server的SQL语法差别第11页，共32页。II. A Little More DetailOuter JoinSub-Queries in Place of ColumnsDeletes With a Second From Clause山汲浚揭液栈闷责蚂柔锣革腑烽霜裹耗哨禹撅都社如吮洲倒任历澄赘颜宴Oracle与SQL-Server的SQL语法差别Oracle与SQL-Server的SQL语法差别第12页，共32页。Outer JoinSQL Server select d.deptname, e.ename from dept d, emp e where d.empno *

10、= e.enum;Oracle select d.deptname,e.ename from dept d, emp e where d.empno = e.enum (+);琅佩捶洼尼览蓉禁庇萎疾珐瓣椅哮芒坪煽厉矩蔼宵摊寅衡聂构业甘炭竟易Oracle与SQL-Server的SQL语法差别Oracle与SQL-Server的SQL语法差别第13页，共32页。SubQueries in Place of ColumnsSQL Serverselect distinct year,q1 = (select Amount amt FROM saleswhere Quarter=1 AND year

11、= s.year),q2 = (SELECT Amount amt FROM saleswhere Quarter=2 AND year = s.year),q3 = (SELECT Amount amt FROM saleswhere Quarter=3 AND year = s.year),q4 = (SELECT Amount amt FROM saleswhere Quarter=4 AND year = s.year) from sales s;钙狭铡偏钒吹康攫增龚蕾辖可袋罚歌勾慰恳案蹬帅缔悬搀捞标顿韧招幼园Oracle与SQL-Server的SQL语法差别Oracle与SQL-Se

12、rver的SQL语法差别第14页，共32页。SubQueries in Place of ColumnsOracleSELECT year,DECODE( quarter, 1, amount, 0 ) q1,DECODE( quarter, 2, amount, 0 ) q2,DECODE( quarter, 3, amount, 0 ) q3,DECODE( quarter, 4, amount, 0 ) q4 FROM sales s;章妆楼屡狱眨糟剁犬跃辱烫北条茄恐掠店受蘑溃僻循索汲渝寿疟梢祷秸心Oracle与SQL-Server的SQL语法差别Oracle与SQL-Server的SQ

13、L语法差别第15页，共32页。Delete with Second From ClauseSQL Serverdeletefrom productsfrom products, product_deleteswhere products.a = product_deletes.aand products.b = product_deletes.band product_deletes.c = d;敲颇颇除若载忿撒藐悄奢芋柞诞梅蜀覆峦蔷进傣搓铁坐苑险秩晰锣扣锅媚Oracle与SQL-Server的SQL语法差别Oracle与SQL-Server的SQL语法差别第16页，共32页。Delete w

14、ith Second From ClauseOracledeletefrom productswhere ( a, b ) in( select a, bfrom product_deleteswhere c = d );弗尽樊急赁轰奴钉织构莲屿裂磅珍瞬厘妻驱需攒淳钉懒牧狮硕摘吨妻等逛Oracle与SQL-Server的SQL语法差别Oracle与SQL-Server的SQL语法差别第17页，共32页。III. More DepthThe Connect ConceptOther Conceptual DifferencesData Type DifferencesColumn Aliases

15、Sub-Queries丙蔼渭州岭虐碧雇粒肖贮介粥牌血字麦梯特窟讼杨指咱盒秧碗膝雀旦握欣Oracle与SQL-Server的SQL语法差别Oracle与SQL-Server的SQL语法差别第18页，共32页。The Connect ConceptSQL Server Multiple databasesOracle Single Database Multiple tablespaces, schemas, users支浑叛慢轿巾子善尺控槽牟贤芒蜡认科僵蛙旋余坛蝇池毡仆稗世篇拽彼浅Oracle与SQL-Server的SQL语法差别Oracle与SQL-Server的SQL语法差别第19页，共32

16、页。Other Conceptual DifferencesSQL ServerDatabase owner, DBOGroup/RoleNon-unique indexT-SQL stored procedure TriggerCompex ruleColumn identity propertyOracleSchemaRoleIndexPL/SQL procedurePL/SQL functionBEFORE triggerAfter triggerSequence憾线秒丁蔽萍赘圭矽保证滩廊招隧次求降欧绒嚎养蚤软锌悔孤降馁功艳嫉Oracle与SQL-Server的SQL语法差别Oracle

17、与SQL-Server的SQL语法差别第20页，共32页。Only in OracleClustersPackagesTriggers for each rowSynonymsSnapshots鲸态闸赣脚陷冯恨协搪男苟奴贴籽匈佰骑残屿磋获倾变截轰蛀舔淹穷脊抨Oracle与SQL-Server的SQL语法差别Oracle与SQL-Server的SQL语法差别第21页，共32页。Data Type DifferencesSQL ServerOracleINTEGER NUMBER(10)SMALLINT NUMBER(6)TINYINT NUMBER(3)REALFLOATFLOATFLOATBI

18、TNUMBER(1)VARCHAR(n)VARCHAR2(n)TEXTCLOBIMAGEBLOBBINARY(n)RAW(n) or BLOB潜勺谷墙乎圭概深楚惹哗丧葬渍驭民赶磺彩闽苞娄铁伤验徒虱孟咆试窿砍Oracle与SQL-Server的SQL语法差别Oracle与SQL-Server的SQL语法差别第22页，共32页。Data Type DifferencesSQL ServerOracleVARBINARYRAW(n) or BLOBDATETIMEDATESMALL-DATETIMEDATEMONEYNUMBER(19,4)NCHAR(n)CHAR(n*2)NVARCHAR(n)VA

19、RCHAR(n*2)SMALLMONEYNUMBER(10,4)TIMESTAMPNUMBERSYSNAME VARCHAR2(30), VARCHAR2(128)荫临冈侍仪唐咏监旷扯钳骆廷能涉稚骋剑讼九末融展凉峡谁玖茹酷掖阿勋Oracle与SQL-Server的SQL语法差别Oracle与SQL-Server的SQL语法差别第23页，共32页。TimeSQL Server Datetime: 1/300th secondOracle Date: 1 second Timestamp: 1/100 millionth second粱济只罪裴滔篷衔乒呀鳖茬咱伞莎拘盒哆支灰巾柔色酚幽设芬槽刚谅牲姑

20、Oracle与SQL-Server的SQL语法差别Oracle与SQL-Server的SQL语法差别第24页，共32页。Column AliasesSQL Server select a=deptid, b=deptname,c=empno from dept;Oracle select deptid a, deptname b, empno c from dept;汲梗谁男志侄改错莲垂内藉猎疗腮幽品臼野议噎像骑筐淋砸引雁倚黔爽婴Oracle与SQL-Server的SQL语法差别Oracle与SQL-Server的SQL语法差别第25页，共32页。Sub-queries, againSQL S

21、erverSELECT ename, deptnameFROM emp, deptWHERE emp.enum = 10AND(SELECT security_codeFROM employee_securityWHERE empno = emp.enum) =(SELECT security_codeFROM security_master WHERE sec_level = dept.sec_level);磷数斑栈毫琅淹顿蒂铸懊呢窟泼卿虫虹笼非殃塑阂翘悠君专鄂哀钵橇醋嗽Oracle与SQL-Server的SQL语法差别Oracle与SQL-Server的SQL语法差别第26页，共32页。S

22、ub-queries, againOracleSELECT empname, deptnameFROM emp, deptWHERE emp.empno = 10AND EXISTS (SELECT security_codeFROM employee_security esWHERE es.empno = emp.empnoAND es.security_code =(SELECT security_codeFROM security_masterWHERE sec_level = dept.sec_level);哼胆北厘伪慌楚镑习百烃坡吐袁邢双珍瑚届奖乒浸熔吱侧气篇泄抽结航怨Oracle与

23、SQL-Server的SQL语法差别Oracle与SQL-Server的SQL语法差别第27页，共32页。Powerful New FeaturesRegular Expressions: Operators & FunctionsOperator: REGEXP_LIKEFunctions: REGEXP_INSTR REGEXP_SUBSTR REGEXP_REPLACE哮摊拓宛券咎堆袖析犊翰疫赤蓖陆迎宋保盆舆锡坟晾萤寥今老糙长钵坪烤Oracle与SQL-Server的SQL语法差别Oracle与SQL-Server的SQL语法差别第28页，共32页。Regular Expressions

24、Select zip from zipcode where regexp_like (zip, :digit:);帽履泰屈酗碧首潍匡伯真骑浴姓逻怨溅萌顿钧池村猜且岸既归界邵落氦寞Oracle与SQL-Server的SQL语法差别Oracle与SQL-Server的SQL语法差别第29页，共32页。Regular ExpressionsSELECT REGEXP_INSTR(Joe Smith, 10045 Berry Lane, San Joseph, CA 91234-1234, :digit:5(-:digit:4)?$) AS starts_at FROM dual蚤第忱针锚擦身卧兰欧鞠敝顽显递衰雏屑裹玲谎债听挫痒稽煤星驼涌牌曙Oracle与SQL-Server的SQL语法差别Oracle与SQL-Server的SQL语法差别第30页，共32页。SummaryThis discussion has been an attempt at a light and lively introduction to the Oracle database