ORALCE的SQL语句整理

末蓝、 2022-09-25 06:18 224阅读 0赞

\--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