--asc 按升序排列 select t.amount,ROWNUM from T_STUDENT T order by T.times asc --desc 按降序排列 select t.amount,ROWNUM from T_STUDENT T order by T.times desc --类似 select * from T_STUDENT where username like '三%' --选择多个条件的数据 select * from T_STUDENT where username in('张三','张三9') --选择多个条件的数据 select * from T_STUDENT where username in(SELECT username FROM SIXGOD) --是空判断 select * from T_STUDENT where age is null --非空判断 select * from T_STUDENT where age is not null --select的查询语句可支持多层 select * from T_STUDENT where (SELECT age FROM T_STUDENT WHERE AGE=78)=AGE --复制表的结构及数据 create table SIXGOD2 as select * from SIXGOD --distinst rows 两个表的数据记录全部合并显示但是去除重复数据 select t.username, t.age from SIXGOD2 t union select t.username, t.age from SIXGOD2 t --all rows 两个表的数据记录全部合并显示 select t.username, t.age from SIXGOD2 t union all select t.username, t.age from SIXGOD2 t -- left join inner join right join 左联结 内联结 右联结 -- left join (左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录 -- right join (右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录 -- inner join (等值连接) 只返回两个表中联结字段相等的行 select t.name,d.pro_name,d.price from USERS t inner JOIN T_ORDER d on d.username_id = t.name select t.name,d.pro_name,d.price from USERS t,T_ORDER d where d.username_id(+) = t.name --分页 SELECT * FROM ( SELECT A.*, ROWNUM RN FROM (SELECT * FROM SIXGOD2) A WHERE ROWNUM <= 40 ) WHERE RN >= 1 --分页第二个方式，可以使用between and的方式做分页 SELECT * FROM ( SELECT A.*, ROWNUM RN FROM (SELECT * FROM SIXGOD2) A ) WHERE RN BETWEEN 2 AND 4 --条件取值decode,类似if else，后面可以写多个else select DECODE(T.AGE,78,'张三78',26,'张三26',t.username), t.age from SIXGOD2 t --条件取值case when，类似If else select case T.AGE when 78 then '张三78' end from SIXGOD2 t --生成随机值 select sys_guid() from dual --判断是否为空，如果为空就显示第二个参数 select NVL (T.USERNAME_ID, ' IS NULL ') from T_ORDER t
