函数一般是在数据上执行的,它给数据的转换和处理提供了方便。只是将取出的数据进行处理,不会改变数据库中的值。函数根据处理的数据分为单行函数和聚合函数(组函数),组函数又被称作聚合函数,用于对多行数据进行操作,并返回一个单一的结果,组函数仅可用于选择列表或查询的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 --两个时间进行四则运算的单位是天 2 select sysdate+2 from dual; 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;
- 组函数或聚合函数是对一个数据集(表数据、查询出来的表、分组的表)进行聚合。(组函数仅可用于选择列表或查询的having子句)
- 聚合函数对字段是 null 的值进行忽略。
- max/min 适合任意数据类型,sum/avg 只适用于数值类型。
聚合函数的结果可以作为其他查询条件。
1 -- 最早入职的员工 2 select e.ename,e.hiredate 3 from emp e 4 where e.hiredate = (select min(e.hiredate) from emp e);
7 分组
在处理统计或聚合数据时,很多时候需要对数据进行分组。语法
1 select field1,... 2 from tablename 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 -- 特例:按照津贴分组 2 select e.comm,count(1) 3 from emp e 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 select field1, field2,... 2 from tablename 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 -- 笛卡尔积 2 select * 3 from emp,dept
10.2 等值连接
1 -- 等值连接 2 -- 需求:查询雇员的部门名称 3 select e.ename,e.deptno,d.dname 4 from emp e,dept d 5 where e.deptno = d.deptno
10.3 不等值连接
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
10.4 外连接
左外连接:左边的表作为主表,右边表作为从表,主表数据都显示,从表数据没有的位置,用null填充,用 (+) 在 where 中作为后缀标识主表。
1 -- 需求:查询所有部门的雇员 2 select * 3 from dept d,emp e 4 where d.deptno = e.deptno(+)
右外连接: 右边的表作为主表,左边表作为从表,主表数据都显示,从表数据没有的位置,用null填充,用 (+) 在 where 中作为后缀标识主表。
1 -- 右外连接(b) 2 select * 3 from emp e,dept d 4 where e.deptno(+) = d.deptno;
10.5 自连接
一个表自身连接自身时,称为自连接。自连接以不同的视角看待同一张表。
1 -- 查询每个雇员的上级领导 2 select e.ename "雇员",m.ename "领导" 3 from emp e,emp m 4 where e.mgr = m.empno
10.6 多于两张表的查询
如果有多个表参与查询,先把 t1 x t2 笛卡尔积得到一个大表 t1,再把 t1 x t3 笛卡尔积得到一个另外的大表t2,依次类推。所有的多表查询最终都是两种表的查询。
需要了解更多数据库技术:SQL-函数及多表关联,都可以关注数据库技术分享栏目—计算机技术网(www.ctvol.com)!
本文来自网络收集,不代表计算机技术网立场,如涉及侵权请联系管理员删除。
ctvol管理联系方式QQ:251552304
本文章地址:https://www.ctvol.com/dtteaching/626166.html