ORACLE和SQLServer SQL语句的区别.doc

ORACLE和SQLServer SQL语句的区别 S: SQL?? Server: ?O: Oracle: ?? ??S: Select?? top?? 1?? *?? from?? tablename?? ??O: Select?? *?? from?? tablename?? where?? rownum?? ?? 2 ?? ?? ??S: Select?? A.*,?? B.*?? from?? A?? left?? Join?? B?? on?? A.bh=B.bh ??O: Select?? A.*,?? B.*?? from?? A,?? B?? where?? A.bh=B.bh(+) ?? ??S: Select?? A.*,?? B.*?? from?? A?? right?? join?? B?? where?? A.bh=B.bh ??O: Select?? A.*,?? B.*?? from?? A,?? B?? where?? A.bh(+)=B.bh ?? ?注：oracle9开始支持join写法 ?S: Select?? *?? from?? tablename?? where?? lrsj?? =?? ‘2002-11-11’ ??O: Select?? *?? from?? tablename?? where?? lrsj=?? to_date(‘2002-11-11’,’yyyy-mm-dd’) ?? ??S: Select?? getdate() ??O: Select?? sysdate?? from?? dual ?? ??S: Select?? Field1+Field2?? from?? A ??O: Select?? Field1?? ||?? Field2?? from?? A?? (指字符串的） ?? ?标准的SQL是一样的.?? SQL的扩展部分各自有不同的语法?? ?? sql?? server?? :Transact-SQL ?? oracle?????????? :PL/SQL ?? ? BJ_TIME?????????????????????????? LOS_ANGLES ?? -------------------?? ------------------- ?? 2004.05.09?? 11:05:32?? 2004.05.09?? 18:05:32 ?? ?? ?? 40.NEXT_DAY(date,day) ?? 给出日期date和星期x之后计算下一个星期的日期 ?? SQL?? select?? next_day(18-5月-2001,星期五)?? next_day?? from?? dual; ?? ?? NEXT_DAY ?? ---------- ?? 25-5月?? -01 ?? ?? ?? ?? 41.SYSDATE ?? 用来得到系统的当前日期 ?? SQL?? select?? to_char(sysdate,dd-mm-yyyy?? day)?? from?? dual; ?? ?? TO_CHAR(SYSDATE, ?? ----------------- ?? 09-05-2004?? 星期日 ?? trunc(date,fmt)按照给出的要求将日期截断,如果fmt=mi表示保留分,截断秒 ?? SQL?? select?? to_char(trunc(sysdate,hh),yyyy.mm.dd?? hh24:mi:ss)?? hh, ?????? 2???? to_char(trunc(sysdate,mi),yyyy.mm.dd?? hh24:mi:ss)?? hhmm?? from?? dual; ?? ?? HH???????????????????????????????????? HHMM ?? -------------------?? ------------------- ?? 2004.05.09?? 11:00:00?? 2004.05.09?? 11:17:00 ?? ?? ?? ?? 42.CHARTOROWID ?? 将字符数据类型转换为ROWID类型 ?? SQL?? select?? rowid,rowidtochar(rowid),ename?? from?? scott.emp; ?? ?? ROWID???????????????????????????? ROWIDTOCHAR(ROWID)?? ENAME ?? ------------------?? --