SQL Server开发者Oracle快速入门 http://kb.cnblogs.com/a/853694

简单概念的介绍

1. 连接数据库
S:    use mydatabase
O:    connect username/password@DBAlias
       conn username/password@DBAlias

2. 在Oracle中使用Dual, Dual是Oracle一个特有的虚拟表, Oracle中很多系统的变量和函数都可以通过Dual中获得
S:    select getdate();
O:    select sysdate from dual;

3. Select Into和Insert 语句的使用, 在SQL Server中的Select Into语句在Oracle中一般是Insert into…select…, 另外2个数据库都支持标准的SQL, 写法上略有区别
S:    select getdate() mycolumn into mytable;
       Insert mytable values(‘more text’);
O:    insert into mytable select getdate() mycolumn from dual
       insert into mytable (mycolumn) values(sysdate);

4. Update语句
S:    update mytable set mycolumn=myothertable.mycolumn  
from mytable,myothertable 
where mytable.mycolumn like 'MY%' and myothertable.myothercolumn='some text';
O:    update mytable set mycolumn=
(select a.mycolumn from myothertable a
where myothertable.myothercolumn='some text')
where mytable.mycolumn like 'MY%';

5. Delete语句
S:    delete mytable where mycolumn like 'some%';
O:    delete from mytable where mycolumn like 'some%';

6. 使用开发管理的软件
S:    isql
osql: for queries developed in SQL Analyzer
SQL Server Management Studio Express 图形化管理工具
O:    sqlplus
       PL/SQL Developer 图形化开发管理工具
       TOAD                   图形化开发管理工具
注: 个人建议基本的简单的Select, Update, Delete使用标准的SQL语句,如SQL92或SQL99的定义

一些细节问题: Joins, Subqueries, Deletes

1. Outer Join 外连接
S:    select d.deptname, e.ename from dept d, emp e where d.empno *= e.enum;
O:    select d.deptname,e.ename from dept d, emp e where d.empno = e.enum (+);

2. SubQueries in Place of Columns
S:    select distinct year,
q1 = (select Amount amt FROM sales where Quarter=1 AND year = s.year),
q2 = (SELECT Amount amt FROM sales where Quarter=2 AND year = s.year),
q3 = (SELECT Amount amt FROM sales where Quarter=3 AND year = s.year),
q4 = (SELECT Amount amt FROM sales where Quarter=4 AND year = s.year)
from sales s;
O:    SELECT 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;

3. Delete with Second From Clause
S:    delete from products, product_deletes
where products.a = product_deletes.a
and products.b = product_deletes.b
and product_deletes.c = 'd';
O:    delete from products
where (a, b ) in
(select a, b from product_deletes where c = 'd' );

某些概念上的区别

1. The Connect Concept
S:    Multiple databases
O:    Single Database, Multiple tablespaces, schemas, users

2. Other Conceptual Differences

Oracle中独有的概念, SQL Server2005中也开始支持了:
Clusters; Packages; Triggers for each row; Synonyms; Snapshots

时间上:
S:    Datetime: 1/300th second
O:    Date: 1 second
Timestamp: 1/100 millionth second

4. 列别名
S:    select a=deptid, b=deptname,c=empno   from dept;
O:    select deptid a, deptname b, empno c from dept;

5. 子查询

S:    SELECT ename, deptname
FROM emp, dept
WHERE emp.enum = 10 AND
(SELECT security_code FROM employee_security WHERE empno = emp.enum) =
(SELECT security_code FROM security_master WHERE sec_level = dept.sec_level);

O:    SELECT empname, deptname
FROM emp, dept
WHERE emp.empno = 10
AND EXISTS
(SELECT security_code FROM employee_security es WHERE es.empno = emp.empno AND es.security_code =(SELECT security_code FROM security_master WHERE sec_level = dept.sec_level));

强大的新特性

正则表达式的支持, SQLServer2005查找和替换可以用正则表达式

Regular Expressions: Operators & Functions

Operator: REGEXP_LIKE

Functions: REGEXP_INSTR; REGEXP_SUBSTR; REGEXP_REPLACE

Select zip from zipcode where regexp_like (zip, ‘[^[:digit:]]’);

SELECT REGEXP_INSTR(

'Joe Smith, 10045 Berry Lane, San Joseph, CA 91234-1234',

' [[:digit:]]{5}(-[[:digit:]]{4})?$') 

AS starts_at

FROM dual