oracle函数，多表查询

1. 函数

函数一般是在数据上执行的，它给数据的转换和处理提供了方便。只是将取出的数据进行处理，不会改变数据库中的值。

函数根据处理的数据分为单行函数和聚合函数（组函数）

组函数又被称作聚合函数，用于对多行数据进行操作，并返回一个单一的结果，组函数仅可用于选择列表或查询的having子句

单行函数对单个数值进行操作，并返回一个值。

 dual是一个系统表。注意用于测试。

2.字符相关

-- dual用于测试

select * from dual;

 

-- 1.字符串连接

selectconcat('aa','12') from dual;

select'aa'||'12'from dual;

 

-- 2.首字母大写

selectinitcap('abc') from dual;

--- 把大写转化小写

selectlower('ABc') from dual;

selectupper('abc') from dual;

 

-- 把所有员工的姓名小写输出

selectlower(e.ename),e.empno

from emp e

 

-- 3.填充字符lpad/rpad

selectlpad('sxt',5,'*') from dual;

selectrpad('sxt',5,'*') from dual;

 

-- 4.去掉空白字符

select'  kallen'from dual;

selectltrim('  kallen',' ') from dual;

selectrtrim('  kallen  ',' ') from dual;

-- trim 删除左右两边的字符

selecttrim('a'from'abc') from dual;

 

-- 5.求子串 substr(str,loc,len)-->loc从1开始

selectsubstr('abcd',2,2) from dual;

 

-- 6.查找字符串

/*

如果找到返回>=1的索引；如果没找到返回0

*/

selectinstr('abcd','b') from dual;

 

-- 7.求长度

selectlength('abcd') from dual;

需求:格式化电话号码

selectsubstr('18612341234',1,3)||'-'||substr('18612341234',4,4)||'-'||substr('18612341234',8,4)

from dual;

3.  数值型函数

-- 数值型函数

-- 四舍五入round(x,y)对x保留y为小数

selectround(23.652) from dual;

selectround(23.652,1) from dual;

selectround(25.652,-1) from dual;

 

-- 返回x按精度y截取后的值

selecttrunc(23.652) from dual;

selecttrunc(23.652,2) from dual;

selecttrunc(23.652,-1) from dual;

 

-- mod（x，y）求余数

selectmod(9,2) from dual;

 

-- ceil 向上取整

selectceil(1.9) from dual;

-- floor 向下取整

selectfloor(1.9) from dual;

 

4.  日期时间函数

1）  日期函数

-- 返回系统当前时间

selectsysdatefrom dual;

-- 返回当前会话时区中的当前日期

select current_date from dual;

 

-- 添加月数

select add_months(sysdate,1) from dual;

-- 返回两个时间相差的月数

select months_between(sysdate,add_months(sysdate,2)) from dual;

 

-- 需求:查询工作年限在30年以上

select e.ename,e.hiredate

from emp e

where months_between(sysdate,e.hiredate)/12>30

 

-- 返回date所在月份最后的一天

select last_day(add_months(sysdate,1)) from dual;

-- next_day(date1，week) 返回date1下周星期几的日期

selectsysdate "当时日期",next_day(sysdate,'Monday') "下周星期一" from dual;

 

-- 查询会话的环境参数

select * from nls_session_parameters;

2）  日期计算相关

selectsysdate+2from dual;

selectsysdate-2from dual;

两个时间进行四则运算的单位是天。

5.   转换函数

转换函数就是把字符、日期、数值型数据进行相互转换。类型转换分两种：隐式类型转换和显式类型转换

1）  隐式类型转换

字符和数字/日期之间的隐式转换

-- 字符隐式转换成数值

select'100' - 10from dual;

 

-- 字符隐式转化为日期

-- DD-MON-RR 默认的日期格式

select1from dual

wheresysdate>'13-May-19';

 

-- 查date format格式

select * from nls_session_parameters;

2）   显示类型转换

 

 

[1]   to_char

把日期转化成字符

 

 

 把数值格式化成字符串

 

 

 

-- to_char

-- 【1】把日期转化成字符

-- 按照默认格式DD-MON-RR

select to_char(sysdate) from dual;

-- 按指定格式

select to_char(sysdate,'YYYY"年"MM"月"DD"日" HH24:MI:SS') as t from dual;

 

-- 【2】把数值格式化成字符串

select to_char(12345,'99999.99') from dual;

select to_char(12345,'99,999.99') from dual;

select to_char(12345,'999,999.99') from dual;

-- 不够位置0

select to_char(12345,'000,000.00') from dual;

-- 格式化成美元显示

select to_char(12345,'$000,000.00') from dual;

 

需求:把18612341234格式化成186-1234-1234

-- 需求:把18612341234格式化成186-1234-1234

selectreplace(to_char(18612341234,'999,9999,9999'),',','-') from dual;

[2]     to_number、to_date

-- to_number

select to_number('$12,345','$99,999') from dual;

select to_number('$12,345.12','$99,999.99') from dual;

 

-- to_date

select to_date('14-May-19','DD-MON-RR') from dual;

select to_date('2004-09-19','YYYY-MM-DD') from dual;

 

函数可以嵌套

-- 查询雇用期满6个月的下一个周一的日期。

select e.ename,e.hiredate,next_day(add_months(e.hiredate,6),'Monday')

from emp e

where months_between(sysdate,e.hiredate) >6

 

-- 查询公司boss

select e.ename || nvl(to_char(e.mgr),' is boss')

from emp e

where e.mgr isnull

6.  decode/case when

decode(条件,值1,“返回值1”,值2,“返回值2”,,,“默认值”)

-- 需求:查询员工所在的部门名称

select

e.ename,

e.deptno,

decode(e.deptno,10,'部门1',20,'部门2',30,'部门3','未知')

from emp e;

 

case when

-- case when

select

e.ename,

e.deptno,

case e.deptno

when10then'部门1'

when20then'部门2'

when30then'部门3'

else'未知'

end

from emp e;

-- 需求:对各个部门进行涨薪，10->1.1 20->1.2 30->1.3 其他->1.0

select

e.deptno,

e.ename,

e.sal "涨薪前",

decode(e.deptno,10,e.sal*1.1,20,e.sal*1.2,30,e.sal*1.3,e.sal) "涨薪后"

from emp e

 

-- 需求:根据工资分布输出以下信息

/*

<1000 真屌丝

 (1001,2000] 屌丝

 (2001,3000] 白领

 (3001,5000] 高富帅

 (5001,10000] 土豪

*/

 

select

e.ename "姓名",

e.sal "工资",

case

when e.sal <= 1000then'真屌丝'

when e.sal <= 2000then'屌丝'

when e.sal <= 3000then'白领'

when e.sal <= 5000then'高富帅'

when e.sal <= 10000then'土豪'

else'未知'

end "描述"

from emp e;

decode多用于等值匹配；case when可以用于等值，多用于条件分支。

7.  组函数

组函数把多行数据经过运算后返回单个值。也称聚合函数。

 

 

 

-- 求公司雇员的数量

selectcount(*)

from emp e;

 

selectcount(e.empno)

from emp e;

 

selectcount(1)

from emp e;

 

-- avg:对多个记录的某个字段求平均值

-- 需求:求底薪的平均值

selectavg(e.sal)

from emp e;

 

 

-- 需求:求雇员的最高薪资/最低薪资

selectmax(e.sal),min(e.sal),avg(e.sal)

from emp e;

 

-- 需求:求公司一个月的员工基本开销

selectsum(e.sal)

from emp e;

 

注意:

[1] 组函数或聚合函数是对一个数据集(表数据、查询出来的表、分组的表)进行聚合。

[2]聚合函数对字段是null的值进行忽略。count(*)

-- 求有津贴的员工的数量

selectcount(e.comm)

from emp e;

 

[3] max/min 适合任意数据类型，sum/avg 只适用于数值类型。

聚合函数的结果可以作为其他查询条件。

-- 最早入职的员工

select e.ename,e.hiredate

from emp e

where e.hiredate = (selectmin(e.hiredate) from emp e);

8.  分组(group by)

在处理统计或聚合数据时，很多时候需要对数据进行分组语法

select field1,。。。

from tableName

group by field1[,field2,…]

按照field1[,field2,…]分组，字段值相同的记录分到一组。

 

1)   groupby的工作原理

 

 

对数据进行分组后，select语句的字段值只能是分组字段或者聚合函数。

2)分组和聚合函数

 

-- 需求:求各个部门的人数

select e.deptno

from emp e

group by e.deptno;

 

-- 需求:统计部门10的人数

select count(1)

from emp e

where e.deptno = 10;

 

 

 

-- 需求:求各个部门的人数

select e.deptno,e.ename

from emp e

group by e.deptno;

 

-- 需求:求各个部门的平均薪资

select e.deptno,avg(e.sal)

from emp e

group by e.deptno

 

-- 需求:求各个部门的月收入平均值

select e.deptno,avg(e.sal+nvl(e.comm,0))

from emp e

group by e.deptno

3)  null值归为一组

-- 特例:按照津贴分组

select e.comm,count(1)

from emp e

group by e.comm

4) having

如果需要对分组的数据进行条件过滤，必须使用having!!!

-- group by having

-- 查询部门平均薪资大于3000的部门

select e.deptno

from emp e

group by e.deptno

having avg(e.sal) >3000

 

-- 查询部门薪资大于3000的雇员按部门分组的平均薪资

select e.deptno,avg(e.sal)

from emp e

where e.sal >3000

group by e.deptno;

注意:

[1]Where过滤行(字段)，having过滤分组。

[2] Having支持所有where操作符。

9.  排序 (order by)

当需要对数据集进行排序操作时，语法

select field1, field2,。。。

from tablename

order by field1,field2

 

对数据集进行排序，先按field1排序，如果field1排序相同，按照field2排序，依次类推。

-asc 升序,默认

-desc 降序

-- order by

-- 需求：按雇员薪资排序

 

select e.ename,e.sal

from emp e

orderby e.sal desc

 

-- 按薪资升序，名称降序

select e.ename,e.sal

from emp e

orderby e.sal,e.ename desc;

order by 一般都是最后执行。

 

需求:查询薪资大于1200的雇员在部门中平均薪资大于1500的部门,按照平均薪资升序排序。

 

--薪资大于1200的雇员所在部门的平均薪资大于1500的部门，按照平均薪资升序排序

 

-- [1] 查询薪资大于1200的雇员

select e.*

from emp e

where e.sal >1200

 

-- [2]按部门分组

select e.*

from emp e

where e.sal >1200

groupby e.deptno

 

-- [3] 输出聚合结果

select e.deptno,avg(e.sal)

from emp e

where e.sal >1200

groupby e.deptno

havingavg(e.sal) >1500

 

-- [4]对数据集进行排序

select e.deptno,avg(e.sal)

from emp e

where e.sal >1200

groupby e.deptno

havingavg(e.sal) >1500

orderbyavg(e.sal) asc

orderby既可以用于数据行(记录)排序。

也可以对分组的结果进行排序，此时需要聚合函数配合。

 10.  Select语言的执行顺序

1. 读取from子句中的基本表、视图的数据，[执行笛卡尔积操作]。
2. 选取满足where子句中给出的条件表达式的元组
3. 按group子句中指定列的值分组，同时提取满足Having子句中组条件表达式的那些组
4. 按select子句中给出的列名或列表达式求值输出
5. Order by子句对输出的目标表进行排序。

         from -> where -> group by -> having -> select -> order by

 

11.  交集、全集、并集、差集

并集:把集合A的结果和集合B的结果合并，并去掉重复的记录。

-- 并集

select e.* from emp e where e.deptno = 10

union

select e.* from emp e where e.deptno = 20;

 

-- 有重复记录取并集

select e.* from emp e where e.deptno = 10or e.deptno = 20

union

select e.* from emp e where e.deptno = 20;

 

全集:把集合A的结果和集合B的结果合并,保留重复记录

select e.* from emp e where e.deptno = 10 or e.deptno = 20

union all

select e.* from emp e where e.deptno = 20;

 

交集:把集合A的结果和集合B的结果取相同部门

select e.* from emp e where e.deptno = 10or e.deptno = 20

intersect

select e.* from emp e where e.deptno = 10;

差集:在集合A的结果中去掉集合B的结果 (A-B)

select e.* from emp e where e.deptno = 10or e.deptno = 20

minus

select e.* from emp e where e.deptno = 10;

 

 12.   多表关联

1)     笛卡尔积

 

 

-- 笛卡尔积

select *

from emp,dept

2)   等值连接

-- 等值连接

-- 需求:查询雇员的部门名称

select e.ename,e.deptno,d.dname

from emp e,dept d

where e.deptno = d.deptno

 

3) 不等值连接

 

-- 不等值连接

-- 查询每个雇员的薪资等级

select e.ename,e.sal,sg.grade

from emp e,salgrade sg

where e.sal >= sg.losal and e.sal <= sg.hisal

-- where e.sal between sg.losal and sg.hisal

 

 

4)    外连接

左外连接:左边的表作为主表，右边表作为从表，主表数据都显示，从表数据没有，用null填充,用+号表示。

-- 左外连接（B）

-- 需求:查询所有部门的雇员

select *

from dept d,emp e

where d.deptno = e.deptno(+)

 

右外连接:右边的表作为主表，左边表作为从表，主表数据都显示，从表数据没有，用null填充,用+号表示。

-- 右外连接（B）

select *

from emp e,dept d

where e.deptno(+) = d.deptno;

 

5)  自连接

一个表自身连接自身时，称为自连接。自连接以不同的视角看待同一张表。

-- 查询每个雇员的上级领导

select e.ename "雇员",m.ename "领导"

from emp e,emp m

where e.mgr = m.empno

 

-- 优化king

select e.ename "雇员",nvl(m.ename,'boss') "领导"

from emp e,emp m

where e.mgr = m.empno(+)

 

6)  多于两张表的查询

如果有多个表参与查询，先把t1xt2笛卡尔积得到一个大表T1，再把T1xt3笛卡尔积得到一个另外的大表T2，依次类推。

所有的多表查询最终都是两种表的查询。

13.  多表关联 - 99 语法

92的问题:

[1]表的过滤条件和表的连接条件混合在一起，维护麻烦

[2]数据库的数据适合变化，根据where子句的执行规则，sql语言也会相应发生变化，给维护造成一定成本。

 

1） 笛卡尔积

table 1 cross join table2

-- 99 笛卡尔积

select *

from dept d crossjoin emp e

 

2） 自然连接

NATURAL JOIN子句基于两个表中列名完全相同的列产生连接

 

[1]两个表有相同名字的列

[2]数据类型相同

[3]从两个表中选出连接列的值相等的所有行

 

-- [2]自然连接

select *

from dept d naturaljoin emp e

 

自然连接只保留一个重复列，整个数据集只有一个deptno。

 

注意:

自然连接最优的使用场景是：主外键关系且主外键字段只有一个。

3） using

using 主要用于指定连接字段。

[1]按照指定的字段连接两个表。

[2] 选指定字段值相同的数据行。

 

using同样适用于自然连接。

 

4）  on指定连接条件

自然连接的条件是基于表中所有同名列的等值连接

为了设置任意的连接条件或者指定连接的列，需要使用ON子句

连个表的关联用关键字join ，默认内连接(inner) 语法

select filed1,fild2,…

from table1

join table2 on condition1

[join table3 on condition2]*

-- 查询出员工的部门名称

select *

from dept d join emp e

on d.deptno = e.deptno

-- 查询scott的上级领导

select e.ename,m.ename

from emp e join emp m

on e.mgr = m.empno

where e.ename = 'SCO%T'

 

使用join on语句连接3张表

-- 查询SCO%T部门名称和薪资等级

select e.ename,d.dname,sg.grade

from dept d join emp e on d.deptno = e.deptno

join salgrade sg on e.sal between sg.losal and sg.hisal

where e.ename = 'SCO%T'

-- 查询SCO%T的管理者名称和其管理者的薪资等级

select e.ename,m.ename,sg.grade

from emp e join emp m on e.mgr = m.empno

join salgrade sg on m.sal between sg.losal and sg.hisal

where e.ename = 'SCO%T'

5）   外连接

外连接在99语法中通过outer 关键字，按照主从表的位置可以分为left outer/right outer,语法

select filed1,field2

from table1 left/right outer join table2 on condition1

[left/right outer join table3 on condition2]*

左外连接

-- 查询所有部门的所有员工

select *

from dept d leftouterjoin emp e

on d.deptno = e.deptno

右外连接

-- 查询所有部门的所有员工

select *

from emp e rightouterjoin dept d

on e.deptno = d.deptno;