sql 基础 一

请关注新浪微博 账号：大数据进行时（http://weibo.com/u/3272022684）

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;