oracle函数和连接

--  函数使用
-- 字符处理
select * from emp;
-- 连接字符
select concat('b','n') from dual;
-- 首字符小写转大写
select initcap('aaString') from  dual;
--  左填充字符型数据
select lpad('dddddddddddddddddd',140,'aa') from dual ;
-- 大小写
select upper(lower(ename)) from emp;
-- 截取
select substrb('abcdefg',1,4) from dual;
select ename ,substr(ename,2) from emp;
-- 字符长度
select ename , length(ename) from emp;
-- 字段ename中大写A所在的位置
select ename,instr(ename,'A') from emp;
-- 右填充定义的字符
select ename,rpad(ename,20,'_') from emp;
-- 替换目标中存在的字符
select ename,replace(ename,'A','*') from emp;
-- 去掉右边的X
select rtrim('CXgao qian jingXXXX','X') from dual;
-- 去掉左右两边的X
select trim(both 'X' from 'XXXfd辅导费XX东方饭店XX 大幅度XX') from  dual;
-- 数字处理
-- 四舍五入
select round(13.56) from dual;
-- 舍弃几位,以点进行0点分割
select trunc(13.96,1) from dual;
select trunc(13.96,-1) from dual;
-- 数字处理
select ceil(19.2) from dual; -- 向上取整
select floor(19.7) from dual;-- 向下取整
select mod(13,5) from dual;-- 求余数
-- 时间函数
select sysdate from dual;
select current_date from dual;

select last_day(sysdate) from dual;-- 返回所在月份的最后一天
select add_months(sysdate,4) from dual; -- 返回加上4个月的日期
select months_between(sysdate,add_months(sysdate,4))  from dual; -- 返回之间月的个数

select sysdate+2 from dual; -- 加两天
select sysdate-2 from dual; -- 减两天
-- 返回两个日期相差多少个星期
select ename,round((sysdate-hiredate)/7) weeks from emp where deptno =10; 
-- 返回两个日期相差多少天
select ename,round(sysdate-hiredate) weeks from emp where deptno =10; 
-- 转换函数
-- 字符和数字的相互转换&字符和日期的相互转换
-- to_char
select to_char(sysdate,'YYYY-MM-DD') from dual;
select sysdate , to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
-- 超过了后面的值为显示为############
select sal, to_char(sal,'$99999.999') salary from emp where ename ='ALLEN';
select to_char(45550,'9999.00') from dual;
-- to_number 将char或vachar2类型的string转换为number类型
-- to_date 将char或者varchar2类型的string转换为date类型
select to_number('$897.78','$999999.99') from dual;
-- 嵌套函数从最深层到最底层求值
select ename,nvl(to_char(mgr),'no manager') from emp where mgr is null;
-- 显示员工雇佣期满6个月后下一个星期五的日期
select to_char(next_day(add_months(hiredate,6),'Friday'),'fmDay,Monthddth,YYYY') review 
from emp order by hiredate;
-- 其他函数decode,case when
-- 组函数 avg() min() max() sum() count()返回某列的行数
-- 数字类型数据使用avg 和sum函数
-- min和max使用于任何数据类型

-- 出现在select列表中的字段,如果出现的位置不是在组函数中,那么必须出现在group by 子句中
select deptno,avg(sal) from emp group by deptno;
-- 不能在where子句中使用组函数,不能再where子句中限制组,使用having对分组进行限制
select avg(sal) from emp group by deptno having avg(sal)>1000;


select e.*,d.*,rownum rn from emp e  join dept d on e.deptno=d.deptno;

select r.rn from (
select e.*,d.*,rownum rn from emp e join dept d on e.deptno=d.deptno
) r where r.rn<100;

select * from emp;
select * from dept;
--关联查询
/*
select t1.c1,t2.c2 from t1,t2 where t1.c3 = t2.c4
在进行连接的时候，可以使用等值连接，可以使用非等值连接

*/
--查询雇员的名称和部门的名称
select ename,dname from emp,dept where emp.deptno = dept.deptno;
--查询雇员名称以及自己的薪水等级
select e.ename,e.sal,sg.grade from emp e,salgrade sg where e.sal between sg.losal and sg.hisal;

-- 三表连接  等值连接和非等值连接
select ename,dname,grade from emp join dept on emp.deptno=dept.deptno 
join salgrade on emp.sal between salgrade.losal and salgrade.hisal;
-- 求每个部门平均薪水的等级,首先将每个部门的平均薪水求出来,当成一张表
-- 在于salgrade表做非等值连接
select * from salgrade s,(select deptno,avg(sal) avg_sal from emp group by deptno)t
where t.avg_sal between s.losal and s.hisal;
-- 分页
-- 单表
SELECT * FROM (SELECT t.*,ROWNUM r FROM TABLE t WHERE ROWNUM <= pageNumber*pageSize)
 WHERE r >(pageNumber-1)*pageSize;
-- 两张表
SELECT * FROM (SELECT ROWNUM RN,XX.* FROM (SELECT 表名.字段名, 表名.字段名, 表名.字段名... 
FROM TABLE1 t1, TABLE2  t2 WHERE t1.字段=t2.字段) XX 
WHERE ROWNUM<=pageSize*pageNumber) WHERE RN >(pageNumber-1)*pageSize;
-- 分页查询
select * from (
select e.* ,rownum rn from emp e where rownum<5
) r where r.rn>0;
-- 本地开始修改scott的密码
1,利用sqlplus登录,要先登录system,密码为自己填写的安装密码
2,采用system用户名，密码为自己填写的安装密码
3，conn sys/sys as sysdba;
4，alter user scott account unlock;
5. alter user scott identified by tiger;
6. conn scott/tiger