SQL-函数及多表关联

晟功恠望

函数一般是在数据上执行的，它给数据的转换和处理提供了方便。只是将取出的数据进行处理，不会改变数据库中的值。函数根据处理的数据分为单行函数和聚合函数（组函数），组函数又被称作聚合函数，用于对多行数据进行操作，并返回一个单一的结果，组函数仅可用于选择列表或查询的having子句；单行函数对单个数值进行操作，并返回一个值。

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

1 字符相关

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;

复制代码

2 数值型函数

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;

复制代码

3 日期时间函数

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. 1 --两个时间进行四则运算的单位是天
   
2. 2 select sysdate+2 from dual;
   
3. 3 select sysdate-2 from dual;

复制代码

4 类型转换
4.1 隐式类型转换

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';

复制代码

4.2 显示类型转换
4.2.1 to_char()
把日期转化成字符串，格式元素列表如下

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;

复制代码

4.2.2 to_number 、to_date

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

复制代码

5 decode/case when
decode(条件，值1,“返回值1"，值2,“返回值2”,,,“默认值”)

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

复制代码

case when - 值匹配

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;

复制代码

case when - 条件匹配

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;

复制代码

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

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.组函数或聚合函数是对一个数据集(表数据、查询出来的表、分组的表)进行聚合。（组函数仅可用于选择列表或查询的having子句）
   2.聚合函数对字段是 null 的值进行忽略。
   3.max/min 适合任意数据类型，sum/avg 只适用于数值类型。

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

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

复制代码

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

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

复制代码

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

7.1 分组和聚合函数

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

复制代码

7.2 null 值会归为一组

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

复制代码

7.3 having （重点）
如果需要对分组后的数据进行条件过滤，必须使用having。

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;

复制代码

Where过滤行，having过滤分组。
Having支持所有where操作符。

8 排序 (order by)
当需要对数据集进行排序操作时，语法

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

复制代码

对数据集进行排序，先按 field1 排序，如果 field1 排序相同，按照 field2 排序，以此类推。
asc 升序,默认
desc 降序

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

复制代码

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

10 多表关联
10.1 关联原理

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

复制代码

10.2 等值连接

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

复制代码

10.3 不等值连接

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

复制代码

10.4 外连接
左外连接：左边的表作为主表，右边表作为从表，主表数据都显示，从表数据没有的位置，用null填充，用 （+） 在 where 中作为后缀标识主表。

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

复制代码

右外连接: 右边的表作为主表，左边表作为从表，主表数据都显示，从表数据没有的位置，用null填充，用 （+） 在 where 中作为后缀标识主表。

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

复制代码

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

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

复制代码

10.6 多于两张表的查询
如果有多个表参与查询，先把 t1 x t2 笛卡尔积得到一个大表 T1，再把 T1 x t3 笛卡尔积得到一个另外的大表T2，依次类推。所有的多表查询最终都是两种表的查询。