CUUG官方博客: CUUG官网_CUUG怎么样_CUUG培训就业怎么样_CUUG学Oracle要多少钱

【单行函数,表连接,创建聚组数据,子查询】 兰州交大 徐辉

 

【单行函数】
 dual表:是一个虚拟表,是为了语法的完整性。
 1.字符函数:1>.lower 小写;upper 大写;initcap 首字母大写。
                eg:select ename,lower(ename),upper(ename),initcap(ename) from emp;
             2>.分隔符 initcap。eg:select initcap('hello@xuhui_shanxi!shangluo%shangnan') from dual;
             3>.concat 连接字符串,列,表达式
                eg:select concat('good','boy') from dual;
                   select concat(concat(ename,'is'),job) from emp;
             4>.substr 截取字符串
                eg:select substr('xuhui',2,3) form dual;
             5>.length 获取字符串长度(字符数)
                eg:select length('xuhui') from dual;
             6>.instr 后面字符串在前面字符串第一次出现的位置 (经常用来判断一个字符串是否在另一个字符串当中)
                eg:select instr('xuhui','u') from dual;
                   select instr('1*2*3*4','*',2,2) from dual;查询从第二个字符串开始*第二次出现的位置。
             7>.lpad,rpad 左补全,右补全
                eg:select sal,lpad(sal,5,'*'),rpad(sal,5,'*') from emp;
             8>.trim 去掉前后空格,或者从后面串首尾去掉前面字符。
                eg:select trim('  xuhui    ')from dual;
                   select trim('*' from '***xuhui****') from dual;
                补:leading/trailing/both 开头,结尾,两头删除字符
                  eg:select trim(leading '*' from '***xuhui****') from dual;
 2.数字函数:1>.round 四舍五入;trunc 截取;mod 取模 取余数。
               eg:select trunc(45.923,2),trunc(45.923),trunc(45.923,-1) from dual;             
                  select mod(1600,300) from dual;取余 
 3.日期函数:1>.对日期进行计算  select sysdate+1 from dual; 一天以后
                                select sysdate+1/24 from dual; 一小时后
             2>.设置会话时间格式 alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
             3>.months_between 月份之间
                eg:select empno,ename,sal,months_between(sysdate,hiredate)months from emp;
             4>.add_months 为日期增加月份
                eg:select sysdate,add_months(sysdate,1) from dual; 一个月后
             5>.next_day 下一个 eg:select next_day(sysdate,'MONDAY')from dual; 下周一
             6>.last_day 某月最后一天 eg:select sysdate,last_day(sysdate) from dual;
 4.转换函数:1>.隐式数据类型转换:后台转换。(数据类型与默认格式相匹配)
             2>.显示数据类型转换:
                     to_char  eg:select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss am') from dual;
                                 select to_char(sysdate,'FMDay,"the" ddth "of" Month,yyyy') from dual;(FM去除开头或结尾的空格)
                                 带有数字的to_char eg:select to_char(sal,'$99,999.00') from emp;
                     to_number 将一个字符串转化为数字 eg:select to_number('$12,345.00','$99,999.00') from dual;
                     to_date   将一个字符串转化为日期函数 eg:select to_date('2006-05-24','yyyy-mm-dd') from dual;                                                                                                                 
 5.其他函数decode 1>.nvl函数  nvl(expr1,expr2)如果1为真,则返回2,否则返回1。eg:nvl(comm,0)
                              nvl2(expr1,expr2,expr3) 如果1非空,则返回2的值,否则返回参数3的值。
                                 eg:select sal,sal+nvl(comm,0),nvl2(comm,sal+nvl(comm,0),sal) from emp;
                  2>.decode函数 eg:select empno,ename,job,sal,decode(job,'ANALYST', sal*1.1,'CLERK',sal*1.15) new_sal from emp

                        order by job;
                     case when______ then_______ else____ end__  也可以表示范围
【表连接】
 用一个连接来从多个表中获取数据 eg:select e.ename,e.deptno,d.deptno,d.dname from emp e,dept d where e.deptno=d.deptno;
 表连接的类型:
 1.等值连接:标准SQL语法:eg:select emp.ename,emp.deptno,dept.deptno,dept.dname from emp inner join dept on(emp.deptno=dept.deptno);
                             select emp.ename,deptno,dept.dname from emp inner join dept using (deptno); 
             自然连接:   eg: select emp.ename,deptno,dept.dname from emp natural join dept;
 2.非等值连接:eg: select e.empno,e.ename,e.sal,s.losal,s.hisal,s.grade from emp e,salgrade s where e.sal>=s.losal and e.sal<=hisal;  
                                                                                               -----e.sal between s.losal and s.hisal;
 3.外连接:1>.左外连接:eg:select e.empno,e.ename,e.sal,d.deptno,d.dname from emp e,dept d where e.deptno=d.deptno(+);
                            select e.empno,e.ename,e.sal,d.deptno,d.dname from emp e left outer join dept d on (e.deptno=d.deptno);
           2>.右外连接:例子同上,左改为右。
           3>.全外连接:例子同上,左改为full。
           4>.自连接:(在一个表中有主从关系)eg:查询员工的姓名以及其经理的姓名 (员工的经理号=经理的员工号)
                                                 select e.ename,e.empno,e.mgr,m.empno,m.ename from emp e,emp m where e.mgr=m.empno;
 4.多表关联:eg:select e.ename,d.dname,s.grade from emp e,dept d,salgrade s where (e.sal between s.losal and s.hisal) and (e.deptno=d.deptno);
                select e.ename,d.dname,s.grade from emp e join dept d on (e.deptno=d.deptno) join salgrade s on (e.sal between s.losal and s.hisal);
【使用分组函数对数据进行聚合】
 1.常用的分组函数:avg();max();min();sum()  平均值,最大值,最小值,总和。
                   count() 返回查询出的行数。count(*) 返回总行数,包括含有空值的行。
 2.分组函数和空值:nvl函数使得分组函数可以处理空值。
                   eg:select avg(nvl(comm,0)) from emp;
 3.创建聚组数据:将表中的数据行用group by 语句分为几个组。
                 1>.select语句中,没有使用分组函数的列必须出现在group by子句中。eg:select avg(sal) from emp group by deptno;
                 2>.group by后面的列可以不出现在select语句中。
                 3>.group by后面不可以使用列的别名。
 4.根据多个列进行分组:eg:select deptno,job,sum(sal) from emp group by deptno,job order by deptno;
                       分组函数的误用:select语句中有分组函数,没有在分组函数中出现的列必须在group by 子句中!
                                       不能在where子句中对聚组列做出限定,要使用having子句来限定分组。
 5.having排除限定结果:eg:查询部门工资大于2900的部门 select deptno,max(sal) from emp group by deptno having max(sal)>2900;
 6.分组函数的嵌套使用:eg:select max(avg(sal)) from emp group by deptno;(注意,分组函数最多嵌套两层)
【子查询】
 在主查询之前,子查询首先要执行一次。子查询的结果要在主查询中使用。
 1.where子句中使用子查询:eg:select ename,sal from emp where sal>(select sal from emp where empno=7566);
                         子查询放在比较操作符的右侧,增强可读性!
 2.单行子查询:(子查询返回一行结果)eg:select ename,job from emp where job=(select job from emp where empno=7369) and
                                        sal>(select sal from emp where empno=7876);
 3.在having子句中使用子查询:eg:查询哪些部门最低工资比20号部门最低工资高
                                select deptno,min(sal) from emp group by deptno having min(sal)>(select min(sal) from emp where deptno=20);
 4.多行子查询:多行子查询用(in,any,all)多行比较操作符
              1>.in eg:查询是经理的员工 select empno,ename from emp where empno in(select mgr from emp);(注意not in 的空值处理!)
              2>.any (任意) 例子如上将in改为any(注意大于小于的含义!)
              3>.all (全部) 例子如上注意大鱼小鱼的含义!
 5.多列子查询:eg:select ename,job from emp where job=(select job from emp where empno=7369) and sal>(select sal from emp where empno=7876);
 6.多行多列子查询:多列子查询存在子查询返回多行的情况,要使用多行比较操作符。
                   eg:select empno,ename,sal,job from emp where (job,sal) in (select job,sal from emp where deptno=10) and empno<>7788;
 7.在from子句中使用子查询:eg:查询工资比本部门平均工资高的员工
                              select a.ename,a.sal,a.deptno,b.salavg from emp a,(select deptno,avg(sal) salavg from emp group by deptno) b
                              where a.deptno=b.deptno and a.sal>b.salavg;(关联条件where在主查询中,子查询执行一次)
 8.关联子查询:eg:select e.ename,e.sal,e.deptno from emp e where e.sal>(select avg(s.sal) from emp s where e.deptno=s.deptno);
                   关联条件where写在子查询中,对于每一名员工,均进行一次子查询!

作者:逗你玩儿 分类:学员作业 浏览:465 评论:0
留言列表
发表评论
来宾的头像

点击关闭
  • 在线客服