MySQL与Oracle的对比分析（全面整理）

区别一：

mysql使用limit关键字实现分页

oracle使用rownum关键字使用分页

rownum伪列：在查询数据的时候会自动分配一个编号(1,2,3,4)

select name, rownum from t1;

rownum在项目中的具体应用：

select * from (select t1.*, rownum r from t1) t2 where r >= 1 and r <= 3;

区别二：

mysql数据库：并集

oracle数据库：并集、交集、差集

交集：intersect

select * from t1 where name in ('张三','李四')

intersect

select * from t1 where name in ('张三','王五')

差集：

select * from t1 where name in ('张三','李四')

minus

select * from t1 where name in ('张三','王五')

并集

select * from t1 where name in ('张三','李四')

union

select * from t1 where name in ('张三','王五')

select * from t1 where name in ('张三','李四')

union all

select * from t1 where name in ('张三','王五')

区别三：

左连接

select com.name, emp.name from com left join emp on com.id = emp.comid;

select com.name, emp.name from com, emp where com.id = emp.comid(+);

左连接

select com.name, emp.name from com right join emp on com.id = emp.comid;

select com.name, emp.name from com, emp where com.id(+) = emp.comid;

全连接

select com.name, emp.name from com full join emp on com.id = emp.comid;