Oracle回顾--常用函数（2）

最新推荐文章于 2020-09-10 15:35:55 发布

原创最新推荐文章于 2020-09-10 15:35:55 发布 · 243 阅读

1 ·

CC 4.0 BY-SA版权

本文全面介绍了SQL的基本操作，包括伪列的使用、聚合函数、模糊查询、NULL操作、字符串处理、日期转换、数值函数等，同时深入讲解了连接查询、子查询的高级应用，适合初学者快速掌握SQL核心技能。

一、伪列【不用你创建，只要创建完表，自己就会存在的】
rowid【用来寻址用的，我想找到表中的某一行，通过rowid定位】
rownum【代表行号，来标记一行，唯一的号码的】

select owner from t where rownum <=10; 伪列  rowid

delete from t where rownum<=10000;（删除t表10000行）

二、聚合函数

avg(平均值)
SQL> select avg(gongzi) from t2;

sum(求和)
SQL> select sum(gongzi) from t2;

min(最小值)
SQL> select min(gongzi) from t2;

max(最大值)
SQL> select max(gongzi) from t2;

count(行计数)
SQL> select count(*) from t2;

三、模糊查询like ( _代表1个字符，%代表0或多个字符)

SQL> select OBJECT_NAME,owner from t where owner like '_TT';

SQL> select * from t where owner like '%TT%';

四、**null操作【is null（空） is not null （非空）**】个人认为这个null真是个坑爹的地方，使用过程中要非常注意

SQL> select * from t where owner is null;

SQL> select * from t where owner is not null;

五、between 某范围之间

select * from emp where sal between 1000 and 5000; 【等价下面的】

select * from emp where sal >= 1000 and sal<=5000;

（OBJECT_ID值在10000到20000之间）

六、字符串连接

SQL> select id，xingming from t1;
SQL> select '员工编号:     '||empno as "员工编号",'  员工姓名:      '||ename as "员工姓名" from emp;

七、length

select length('##123##') len from dual;

八、LTRIM,RTRIM,TRIM【多用于处理空格】

LTRIM:左删除----left
RTRIM:右删除----right
TRIM:删除串两边的字符

select length('123') len1, length(ltrim('  123   ')) lentrim from dual;

select length('123') len1, length(rtrim('  123   ')) lentrim from dual;

select length('123') len1, length(trim('  123   ')) lentrim from dual;

九、TO_CHAR 是把日期或数字转换为字符串

SQL> select sysdate from dual;

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

SQL> select to_char(123,'9999.00') from dual; 

SQL> select to_char(12333,'$99,999.99') from dual;

SQL> select to_char(123334444,'$99,999.99') from dual;

十、substr 截取函数

SQL> select substr('12345',2,3) from dual;
(截取从第二个字符开始的3个字符)

select substr('1234523232323',2) from dual;
(截取从第二个字符开始,一直到字符串结束)


SQL> select substr('123456789',-5) from dual;
(截取后5位)

十一、字符是否包含instr(string,substring,position,occurrence)
string:代表源字符串
substring:代表想从源字符串中查找的子串
position:代表查找开始的位置,默认为1
occurrence:代表查找值第几次出现,结果为字符串的位置

没有找到，instr函数返回0.

SELECT instr('syranmo','s') FROM dual; -- 返回 1

SELECT instr('syranmo','ra') FROM dual;  -- 返回 3

SELECT instr('syran mo','a',1,2) FROM dual;  -- 返回 0

（根据条件，由于a只出现一次，第四个参数2，就是说第2次出现a的位置，显然第2次是没有再出现了，所以结果返回0。注意空格也算一个字符！）

SELECT instr('syranmo','an',-1,1) FROM dual;  -- 返回 4

（就算是由右到左数，索引的位置还是要看‘an’的左边第一个字母的位置，所以这里返回4）

SELECT instr('abc','d') FROM dual;  -- 返回 0

注：也可利用此函数来检查 ‘abc’ 中是否包含 ‘d’，如果返回0表示不包含，否则表示包含。

十二、abs 绝对值

SQL> select abs(-5) from dual; (返回值为5)
SQL> select abs(5.5)  from dual;(返回值为5.5)

十三、Round 函数 (四舍五入)

SQL> select round(123.123) from dual;
SQL> select round(123.8) from dual;

十四、取整

SQL> select 3/2 from dual;
SQL>  select trunc(-3/2),ceil(-3/2),floor(-3/2),-3/2 from dual;

十五、to_date 把字符串转换成日期

SQL> create table tt2(id int,time date);

SQL> insert into tt2 values (1,to_date('2011-11-11 11:11:11','YYYY-MM-DD HH24:MI:SS'));


select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as nowTime from dual;   //日期转化为字符串   
select to_char(sysdate,'yyyy') as nowYear   from dual;   //获取时间的年   
select to_char(sysdate,'mm')    as nowMonth from dual;   //获取时间的月   
select to_char(sysdate,'dd')    as nowDay    from dual;   //获取时间的日   
select to_char(sysdate,'hh24') as nowHour   from dual;   //获取时间的时   
select to_char(sysdate,'mi')    as nowMinute from dual;   //获取时间的分   
select to_char(sysdate,'ss')    as nowSecond from dual;   //获取时间的秒

求某天是星期几

 select to_char(to_date('2016-04-17','yyyy-mm-dd'),'day') from dual;  ---周天

两个日期间的天数

select sysdate - to_date('20160416','yyyymmdd') from dual;     
select floor(sysdate - to_date('20160416','yyyymmdd')) from dual;      
select trunc(sysdate - to_date('20150501','yyyymmdd')) from dual;

月份差

 select months_between(to_date('03-31-2015','MM-DD-YYYY'),to_date('01-31-2015','MM-DD-YYYY')) "MONTHS" FROM DUAL;     
 
  select months_between(to_date('03-31-2015','MM-DD-YYYY'),to_date('01-15-2015','MM-DD-YYYY')) "MONTHS" FROM DUA

L;

一年的第几天

  select TO_CHAR(to_date('2015-01-01','yyyy-mm-dd'),'DDD'),sysdate from dual;      
   select TO_CHAR(to_date('2015-01-31','yyyy-mm-dd'),'DDD'),sysdate from dual;

十六、Replace替换函数

   select replace('abc','b','######') from dual;

十七、lpad [左添充] rpad [右填充]

select lpad('func',7,'=') s1, rpad('func',7,'-') s2 from dual;

另附上一些比较常见的其他sql用法，这些语法也经常使用，就不再新写博文了，建议也认真阅读一下

1、decode[实现if ..then 逻辑]   注:第一个是表达式,最后一个是不满足任何一个条件的值

   select ename,deptno,decode(deptno,10,'部门10',20,'部门20',30,'部门30','其他部门') "所在部门" from emp;




2、case  when [实现if ..then 逻辑] 

(1)
工资<1000        挣得太少
1000<工资<2000   挣得中等
2000<工资<4000   挣得比较高
工资>4000   		 挣得太多

实现：
select ename,sal,
case 
when sal<1000 then '挣得太少' 
when sal>1001 and sal<2000 then '挣得中等' 
when sal>2001 and sal<4000 then '挣得比较高' 
else '挣得太多' 
end as "评论工资"
from emp;




(2)
工资5000以上的职员，工资减少10%
工资在2000到4600之间的职员，工资增加15%

很容易考虑的是选择执行两次UPDATE语句，如下所示 
--条件1  
UPDATE test  SET sal = sal * 0.9  WHERE sal >= 5000;  

--条件2  
UPDATE test  SET sal = sal * 1.15  WHERE sal >= 2000 AND sal < 4600;  



但是事情没有想象得那么简单，假设有个人工资5000块。首先，按照条件1，工资减少10%，变成工资4500。
接下来运行第二个SQL时候，因为这个人的工资是4500在2000到4600的范围之内， 需增加15%，最后这个人的工资结果是5175,不但没有减少，反而增加了。
暂且不管这个规章是多么荒诞，如果想要一个SQL 语句实现这个功能的话，我们需要用到Case函数。代码如下: 

create table test as select * from emp;  【创建测试表】

UPDATE test    
SET sal = 
CASE 
WHEN sal >= 5000  THEN sal * 0.9  
WHEN sal >= 2000 AND sal < 4600  THEN sal * 1.15  
ELSE sal 
END;  

这里要注意一点，最后一行的ELSE sal是必需的，要是没有这行，不符合这两个条件的人的工资将会被写成NUll,那可就大事不妙了。
在Case函数中Else部分的默认值是NULL，这点是需要注意的地方。

UPDATE test    
SET sal = 
CASE 
WHEN sal >= 5000  THEN sal * 0.9  
WHEN sal >= 2000 AND sal < 4600  THEN sal * 1.15  
END;  





3、左、右连接、全连接

create table bl(id int,name varchar2(100));

insert into bl values(1,'dave');
insert into bl values(2,'bl');
insert into bl values(3,'big bird');
insert into bl values(4,'exc');
insert into bl values(9,'怀宁');



create table dave(id int,name varchar2(100));

insert into dave values(1,'dave');
insert into dave values(1,'bl');
insert into dave values(2,'bl');
insert into dave values(2,'dave');
insert into dave values(3,'dba');
insert into dave values(4,'sf-express');
insert into dave values(5,'dmm');
insert into dave values(8,'安庆');

commit;


(1) 左外连接（Left outer join/ left join）
     left join是以左表的记录为基础的,示例中Dave可以看成左表,BL可以看成右表,它的结果集是Dave表中的数据，在加上Dave表和BL表匹配的数据。
     换句话说,左表(Dave)的记录将会全部表示出来,而右表(BL)只会显示符合搜索条件的记录。BL表记录不足的地方均为NULL.
 
示例：
SQL> select * from dave a left join bl b on a.id = b.id;
 
 
 select a.id,a.name,decode(b.id,null,'null',b.id),decode(b.name,null,'null',b.name) from dave a left join bl b on a.id = b.id;
 
       ID NAME               ID NAME
--------- ---------- ---------- ----------
        1 bl                  1 dave
        1 dave                1 dave
        2 dave                2 bl
        2 bl                  2 bl
        3 dba                 3 big bird
        4 sf-express          4 exc
        5 dmm                             -- 此处B表为null，因为没有匹配到
        8 安庆                             -- 此处B表为null，因为没有匹配到
        
        
 
用（+）来实现， 这个+号可以这样来理解： + 表示补充，即哪个表有加号，这个表就是匹配表。所以加号写在右表，左表就是全部显示，故是左连接。
 
SQL> Select * from dave a,bl b where a.id=b.id(+);    -- 注意： 用（+） 就要用关键字where
 
 Select  a.id,a.name,decode(b.id,null,'null',b.id),decode(b.name,null,'null',b.name) from dave a,bl b where a.id=b.id(+); 
 
        ID NAME               ID NAME
---------- ---------- ---------- ----------
         1 bl                  1 dave
         1 dave                1 dave
         2 dave                2 bl
         2 bl                  2 bl
         3 dba                 3 big bird
         4 sf-express          4 exc
         5 dmm
         8 安庆
    
(2) 右外连接（right outer join/ right join）
和left join的结果刚好相反,是以右表(BL)为基础的, 显示BL表的所以记录，在加上Dave和BL 匹配的结果。 Dave表不足的地方用NULL填充.
 
示例：
SQL> select * from dave a right join bl b on a.id = b.id;
 
        ID NAME               ID NAME
---------- ---------- ---------- ----------
         1 dave                1 dave
         2 bl                  2 bl
         1 bl                  1 dave
         2 dave                2 bl
         3 dba                 3 big bird
         4 sf-express          4 exc
                               9 怀宁    --此处左表不足用Null 填充
已选择7行。

 
用（+）来实现， 这个+号可以这样来理解： + 表示补充，即哪个表有加号，这个表就是匹配表。所以加号写在左表，右表就是全部显示，故是右连接。
 
SQL> Select * from dave a,bl b where a.id(+)=b.id;
 
        ID NAME               ID NAME
---------- ---------- ---------- ----------
         1 dave                1 dave
         2 bl                  2 bl
         1 bl                  1 dave
         2 dave                2 bl
         3 dba                 3 big bird
         4 sf-express          4 exc
                               9 怀宁
 
(3)全连接（full join）
     左表和右表都不做限制，所有的记录都显示，两表不足的地方用null 填充。 全外连接不支持（+）这种写法。
 
示例：
 
SQL> select * from dave a full join bl b on a.id = b.id;
        ID NAME               ID NAME
---------- ---------- ---------- ----------
         8 安庆
         1 dave                1 dave
         2 bl                  2 bl
         1 bl                  1 dave
         2 dave                2 bl
         3 dba                 3 big bird
         4 sf-express          4 exc
         5 dmm
                               9 怀宁
 
已选择9行。
 

  
  
--等值查询
select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno; 
  
  

--内联接查询   inner join  on 
    查询员工姓名和所在部门的名称
select a.ename,b.dname  from emp a inner join dept b on a.deptno=b.deptno
  
  

--自连接查询
   查询员工姓名和其直接上级的姓名

emp员工表 字段内容如下:
	empno  员工号
	ename  员工姓名
	job  工作
	mgr  上级的员工号
	hiredate 受雇日期
	sal  薪金
	comm  佣金
	deptno  部门编号

例如："SMITH"的上级是"FORD"  
empno：7369    ename：SMITH    mgr：7902
empno：7902    ename：FORD       
   
   
  
select '员工:'||a.ename||'   上级是：'||b.ename from emp a left join emp b on a.mgr=b.empno;

SELECT worker.ename||' 的经理是 '||manager.ename AS 雇员经理  FROM emp worker, emp manager  WHERE worker.mgr = manager.empno;  
  
  
  
  
  
  
  
--子查询

如果子查询未返回任何行，则主查询也不会返回任何结果

(空值)select * from emp where sal > (select sal from emp where empno = 8888);

 

如果子查询返回单行结果，则为单行子查询，可以在主查询中对其使用相应的单行记录比较运算符

(正常)select * from emp where sal > (select sal from emp where empno = 7369);

 

如果子查询返回多行结果，则为多行子查询，此时不允许

对其使用单行记录比较运算符

(多值)select * from emp where sal > (select avg(sal) from emp group by deptno);//非法 

子查询中常用方法

(1)any即任何一个。如果在where条件中加入>any，意思是大于任何一个，也就是大于最小的

select * from emp t where t.sal > any (select sal from emp where deptno=30);



(2)all即所有。如果在where条件中加入>all，意思是大于每一个，也就是大于最大的。

select * from emp t where t.sal> all(select sal from emp where deptno=30);

 
create table test(id int);

insert into test values(1);
insert into test values(2);
insert into test values(3);
insert into test values(4);
commit;

--All：对所有数据都满足条件，整个条件才成立，例如：5大于所有返回的id
select *
from test where 5>All(select id from test)

--Any：只要有一条数据满足条件，整个条件成立，例如：3大于1，2
select *
from test where 3>any(select id from test)


--Some和Any一样 
备注：Any的用法中，在作数字比对时，也可以改用先select subquery的min/max value的方法，某些情况下效率更高
 
 这两个都是用于子查询的

any 是任意一个
all 是所有

比如

select * from student where 班级='01' and age > all (select age from student where 班级='02');
就是说，查询出01班中，年龄大于 02班所有人的同学
相当于

select * from student where 班级='01' and age > (select max(age) from student where 班级='02');

而
select * from student where 班级='01' and age > any (select age from student where 班级='02');
就是说，查询出01班中，年龄大于 02班任意一个 的 同学
相当于

select * from student where 班级='01' and age > (select min(age) from student where 班级='02');
 
 
 
 
 
 
 
 
 

(3)In 在某集合内

select * from emp t where t.deptno in(30,10);

 
(4)exists 是否存在

   查找部门号是10的所有员工信息  
   select * from emp t where t.deptno in(10);
 select * from emp e where exists(select 1 from dept d where e.deptno=d.deptno and d.deptno=10);

   查找部门号不是10的所有员工信息
  select * from emp e where not exists(select 1 from dept d where e.deptno=d.deptno and d.deptno=10);