1.查看表的结构 desc tablename;
查看表中空值 select * from tablename where col is NULL;
满足多个条件的查询,加条件 or
在where子句中引用取别名的列时需要在外层嵌套一层select查询语句;
拼接列用|| col1 || col1
条件逻辑:CASE WHEN
用法:
CASE
WHEN express1 THEN result1
WHEN express2 THEN result2
ELSE result3
END
取前两行数据:select * from tablename where rownum <=2;
取第二行数据:select * from (select rownum as sn,emp.* from emp where rownum <=2) where sn =2;
从表中随机返回n条记录:
select empno,ename from (select empno,ename from emp order by dbms_random.value()) where rownum <=3;
select empno,ename,random.value as ran from emp where rownum <= 3 order by ran;
执行的顺序必须符合:1.先随机排序 2.取数据
sql语句的顺序查询,order by可以用数字代替列名,其他的不可以;
select empno,ename,sal,job from emp order by 2 asc,3 desc;
按第二列升序,按第二列降序;
查尾号为排序前四的用户:
select last_name as 名称,phone_num as 号码,salary as 工资,substr(phone_num,-4) as 尾号 from tablename where rownum <=5 order by 4;