Oracle - 函数及多表关联

 1 -- 1.字符串连接
 2 select concat('aa','12') from dual;
 3 select 'aa'||'12' from dual;
 4 
 5 -- 2.首字母大写
 6 select initcap('abc') from dual;
 7 --- 把大写转化小写
 8 select lower('ABc') from dual;
 9 select upper('abc') from dual;
10 
11 -- 把所有员工的姓名小写输出
12 select lower(e.ename),e.empno
13 from emp e
14 
15 -- 3.填充字符lpad/rpad
16 select lpad('sxt',5,'*') from dual;
17 select rpad('sxt',5,'*') from dual;
18 
19 -- 4.去掉空白字符
20 select '  kallen' from dual;
21 select ltrim('  kallen',' ') from dual;
22 select rtrim('  kallen  ',' ') from dual;
23 -- trim 删除左右两边的字符
24 select trim('a' from 'abc') from dual;
25 
26 -- 5.求子串 substr(str,loc,len)-->loc从1开始
27 select substr('abcd',2,2) from dual;
28 
29 -- 6.查找字符串
30 /*
31 如果找到返回>=1的索引；如果没找到返回0
32 */
33 select instr('abcd','b') from dual;
34 
35 -- 7.求长度
36 select length('abcd') from dual;

 1 -- 四舍五入round(x,y)对x保留y为小数
 2 select round(23.652) from dual;
 3 select round(23.652,1) from dual;
 4 select round(25.652,-1) from dual;
 5 
 6 -- 返回x按精度y截取后的值
 7 select trunc(23.652) from dual;
 8 select trunc(23.652,2) from dual;
 9 select trunc(23.652,-1) from dual;
10 
11 -- mod（x，y）求余数
12 select mod(9,2) from dual;
13 
14 -- ceil 向上取整
15 select ceil(1.9) from dual;
16 -- floor 向下取整
17 select floor(1.9) from dual;

 1 -- 返回系统当前时间
 2 select sysdate from dual;
 3 -- 返回当前会话时区中的当前日期 
 4 select current_date from dual;
 5 
 6 -- 添加月数
 7 select add_months(sysdate,1) from dual;
 8 -- 返回两个时间相差的月数
 9 select months_between(sysdate,add_months(sysdate,2)) from dual;
10 
11 -- 需求:查询工作年限在30年以上
12 select e.ename,e.hiredate
13 from emp e
14 where months_between(sysdate,e.hiredate)/12 > 30
15 
16 -- 返回date下一月份的最后一天
17 select last_day(add_months(sysdate,1)) from dual;
18 -- next_day(date1，week) 返回date1下周星期几的日期
19 select sysdate "当时日期",next_day(sysdate,'Monday') "下周星期一" from dual;

1 --两个时间进行四则运算的单位是天
2 select sysdate+2 from dual;
3 select sysdate-2 from dual;

1 --字符和数字/日期之间的隐式转换
2 -- 字符隐式转换成数值
3 select '100' - 10 from dual;
4 
5 -- 字符隐式转化为日期
6 -- DD-MON-RR 默认的日期格式
7 select 1 from dual
8 where sysdate > '13-May-19';

1 -- 把日期转化成字符
2 -- 按照默认格式DD-MON-RR
3 select to_char(sysdate) from dual;
4 -- 按指定格式
5 select to_char(sysdate,'YYYY"年"MM"月"DD"日" HH24:MI:SS') as t from dual;
6 -- 格式内要打中文（非给定允许的时间分隔符）需要用双引号引起来

1 -- 把数值格式化成字符串
2 select to_char(12345,'99999.99') from dual;
3 select to_char(12345,'99,999.99') from dual;
4 -- 没有数的位置补空格
5 select to_char(12345,'999,999.99') from dual;
6 -- 没有数的位置补0
7 select to_char(12345,'000,000.00') from dual;
8 -- 格式化成美元显示
9 select to_char(12345,'$000,000.00') from dual;

 1 -- to_number
 2 select to_number('$12,345','$99,999') from dual;
 3 select to_number('$12,345.12','$99,999.99') from dual;
 4 
 5 -- to_date
 6 select to_date('14-May-19','DD-MON-RR') from dual;
 7 select to_date('2004-09-19','YYYY-MM-DD') from dual;
 8 
 9 -- 查询雇用期满6个月的下一个周一的日期。
10 select e.ename,e.hiredate,next_day(add_months(e.hiredate,6),'Monday')
11 from emp e
12 where months_between(sysdate,e.hiredate) > 6

1 -- 需求:查询员工所在的部门名称
2 select 
3 e.ename,
4 e.deptno,
5 decode(e.deptno,10,'部门1',20,'部门2',30,'部门3','未知')
6 from emp e;

 1 select 
 2 e.ename,
 3 e.deptno,
 4 case e.deptno
 5   when 10 then '部门1'
 6   when 20 then '部门2'
 7   when 30 then '部门3'
 8   else '未知'
 9 end
10 from emp e;

 1 -- 需求:根据工资分布输出以下信息
 2 /*
 3  <1000 真屌丝
 4  (1001,2000] 屌丝
 5  (2001,3000] 白领 
 6  (3001,5000] 高富帅
 7  (5001,10000] 土豪
 8 */
 9 
10 select 
11 e.ename "姓名",
12 e.sal "工资",
13 case
14   when e.sal <= 1000 then '真屌丝'
15   when e.sal <= 2000 then '屌丝'
16   when e.sal <= 3000 then '白领'
17   when e.sal <= 5000 then '高富帅'
18   when e.sal <= 10000 then '土豪'
19   else '未知'
20 end "描述"
21 from emp e;

 1 -- 求公司雇员的数量
 2 select count(*)
 3 from emp e;
 4 
 5 select count(e.empno) 
 6 from emp e;
 7 
 8 select count(1)
 9 from emp e;
10 
11 -- avg:对多个记录的某个字段求平均值
12 -- 需求:求底薪的平均值
13 select avg(e.sal)
14 from emp e;
15 
16 -- 需求:求雇员的最高薪资/最低薪资
17 select max(e.sal),min(e.sal),avg(e.sal)
18 from emp e;
19 
20 -- 需求:求公司一个月的员工基本开销
21 select sum(e.sal)
22 from emp e;

1 -- 最早入职的员工
2 select e.ename,e.hiredate
3 from emp e
4 where e.hiredate = (select min(e.hiredate) from emp e);

1 select field1,...
2 from tableName
3 group by field1[,field2,…]

 1 -- 需求:统计部门10的人数
 2 select count(1)
 3 from emp e
 4 where e.deptno = 10;
 5 
 6 -- 需求:求各个部门的平均薪资
 7 select e.deptno,avg(e.sal)
 8 from emp e
 9 group by e.deptno
10 
11 -- 需求:求各个部门的月收入平均值
12 select e.deptno,avg(e.sal+nvl(e.comm,0))
13 from emp e
14 group by e.deptno

1 -- 特例:按照津贴分组
2 select e.comm,count(1)
3 from emp e
4 group by e.comm;

 1 -- group by having
 2 -- 查询部门平均薪资大于3000的部门
 3 select e.deptno
 4 from emp e
 5 group by e.deptno
 6 having avg(e.sal) > 3000
 7 
 8 -- 查询部门薪资大于3000的雇员按部门分组的平均薪资
 9 select e.deptno,avg(e.sal)
10 from emp e
11 where e.sal > 3000
12 group by e.deptno;

1 select field1, field2,...
2 from tablename
3 order by field1,field2

 1 -- order by
 2 -- 按雇员薪资排序
 3 select e.ename,e.sal
 4 from emp e
 5 order by e.sal desc
 6 
 7 -- 按薪资升序，名称降序
 8 select e.ename,e.sal
 9 from emp e
10 order by e.sal,e.ename desc;

1 --薪资大于1200的雇员的平均薪资大于1500的部门，按照平均薪资升序排序 
2 select e.deptno,avg(e.sal)
3 from emp e
4 where e.sal > 1200
5 group by e.deptno
6 having avg(e.sal) > 1500
7 order by avg(e.sal) asc

1 -- 笛卡尔积
2 select *
3 from emp,dept

1 -- 等值连接
2 -- 需求:查询雇员的部门名称
3 select e.ename,e.deptno,d.dname
4 from emp e,dept d
5 where e.deptno = d.deptno

1 -- 查询每个雇员的薪资等级
2 select e.ename,e.sal,sg.grade
3 from emp e,salgrade sg
4 where e.sal >= sg.losal and e.sal <= sg.hisal
5 -- where e.sal between sg.losal and sg.hisal

1 -- 需求:查询所有部门的雇员
2 select *
3 from dept d,emp e
4 where d.deptno = e.deptno(+)

1 -- 右外连接（B）
2 select *
3 from emp e,dept d
4 where e.deptno(+) = d.deptno;

1 -- 查询每个雇员的上级领导
2 select e.ename "雇员",m.ename "领导"
3 from emp e,emp m
4 where e.mgr = m.empno