sql 基础 一
1、and 的优先级 高于 or2、asselect sal as salary, comm as commission from emp3、连接字符串 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 <= 57、is null / is not null8、将 null 翻译成其他 可以使用 case when 语句 select coalesce(null_column,'is null') from table_name; select coalesce(create_user , 'system') from emp;
