mysql与mssql、oracle的异同 (一点小笔记)

mysql与mssql、oracle的异同
 
2008.4.5
1.在where子句中引用取别名的列
select * from (
select c1 as column1,c2 as column2 
from tbl1) x
where column1 <2000
 
2.连接列值
mysql：
select concat(c1,c2) from tbl1
mssql:
select c1 + c2 from tbl1
oracle:
select c1 || c2 from tbl1
 
3.限制返回的行数
mysql:
select * from tbl1 limit 5
mssql:
select top 5 * from tbl1
oracle:
select * from tbl1 where rownum<=5
 
4.随机返回n条数据
mysql:
select c1,c2 from tbl1 order by rand() limit 5
mssql:
select top 5 c1,c2 from tbl1 order by newid()
oracle:
select * from (
select c1,c2 from tbl1 order by dbms_random.value()
) where rownum<=5
 
5.将空值转化为实际值
select coalesce(c1,0)
from tbl1
 
select case when c1 is null then 0
else c1 end from tbl1