[color=blue]请关注新浪微博 账号:大数据进行时([url]http://weibo.com/u/3272022684[/url])[/color]
1、
and 的优先级 高于 or
2、as
select sal as salary, comm as commission
from emp
3、连接字符串
Oracle/DB2/PostgreSQL:
select ename||' WORKS AS A '||job as msg from emp where deptno=10;
mysql
select concat(ename,'WORKS AS A',job) as msg from emp where deptno=10;
SqlServer
select ename + ' WORKS AS A ' + job as msg from emp where deptno=10;
4、case - when
select ename,sal,
case when sal <= 2000 then 'UNDERPAID'
when sal >= 4000 then 'OVERPAID'
else 'OK'
end as salary
from emp;
5、查询条数(分页查询)
DB2
select * from emp fetch first 5 rows only;
MySql
select * from table_name limit x , y;
select * from emp limit 5;
Oracle
select * from table_name where rownum >= x and rownum <= y;(rownum 从 1 开始)
select * from emp where rownum <= 5
SqlServer
select top 5 * from emp;
6、随机获取记录
DB2
select ename,job from emp order by rand() fetch first 5 rows only;
MySql
select * from table_name order by rand() limit x , y;
select * from emp order by rand() limit 5;
Oracle
select * from (
select * from table_name order by dbms_random.value()
)where rownum <= 5
7、is null / is not null
8、将 null 翻译成其他
可以使用 case when 语句
select coalesce(null_column,'is null') from table_name;
select coalesce(create_user , 'system') from emp;