sql的基础语句-单行函数，dual，数字函数，日期函数，表连接，集合运算，分组报表，单行子查询，多行子查询

ewrQ

3. 单行函数

3.1 转换函数

select ascii('A'),chr(65) from dual;

select to_char(1243123),1231451 from dual;
靠左边的就是字符串，靠右边的就是数字

select to_char(123512a121) from dual;   --错误的写法，没有引号表示数字，但是数字里面包含了字母，不合法的输入值

select to_number('123141211') from dual;

select to_number('12314a1211') from dual;  --不合法的输入值


select to_date('20140610121212','yyyy-mm-dd hh24:mi:ss') from dual;

不合法的输入值：
select to_date('20141310121212','yyyy-mm-dd hh24:mi:ss') from dual;


select to_date(20140610121212,'yyyy-mm-dd hh24:mi:ss') from dual;  --日期不加引号，数字类型

将日期转换为固定格式字符串
select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') from dual;

select to_char(sysdate,'yyyymmdd') from dual;

select to_number(to_char(sysdate,'yyyymmdd')) from dual;

3.2 字符串的运算

(1)字符串的连接

select concat('123124','asdfda') from dual;    --只能传两个参数，但是||可以不断拼接

select '123124'||'asdfda' from dual;


(2)求长度

select length('adsfasgsadfasdf') from dual;

(3)截取串

select substr('asdfasdfasdfasdf',5,4) from dual;
从左向右第5位开始向后截取4个字节长度

select substr('asdfasdfasdfasdf',-5,4) from dual;
从右向左第5位开始向后截取4个字节长度

select length(substr('asdfasdfasdfasdf',-5,7)) from dual;    --截取完为止

(4)求子串的位置

select instr('asdfasdfasdfasdf','asdf',3,1) from dual;
从左向右第3位开始，第1次出现‘asdf’的索引位置

select instr('asdfasdfasdfasdf','asdf',3,2) from dual;
从左向右第3位开始，第2次出现‘asdf’的索引位置

select instr('asdfasdfasdfasdf','asdf',-3,2) from dual;         --9 （红色的为第一个，***开始为第二个，也就是第五个a是第二个，然后顺序再从右边第四个开始算起为1，所以也就是第九位） ；
从右向左第3位开始，第2次出现‘asdf’的索引位置
意思是说从右边的第三位开始算起，也就是从右边的第四位开始由1开始，然后第二次出现字符创的位置仍然是从左边开始的，也就是 a s d f a  s d f a,但是数字就是从右边算起
                       9 8 7 6 5 4 3 2 1
select instr('asdfasdfasdfasdf','asdf',-3,1) from dual;         --13
从右向左第3位开始，第1次出现‘asdf’的索引位置

(5)大小写的转换

select upper('asdfasgas'),lower('GHJAJDGJ') from dual;

select lower(ename),lower(job) from emp;

(6) 替换

select replace('asdfasdfasdfasdf','asdf','H') from dual;

(7) 补足字符串

select lpad('asdf',20,'*'),rpad('asdf',20,'*') from dual;

(8)去空格

select trim('     sadf      ') as a,ltrim('      asdf       ') as b,rtrim('      asdf       ') as c from dual;

(9)去中间的空格

select replace('   asdf  sdf  dfasdf  sdf    '   ,   ' '   ,   ''    ) as a from dual;
                      ‘ 字符串  ’，分隔符   ‘ ’匹配条件 ‘’匹配结果  

asdfsdfdfasdfsdf

###########################################################################################
4.数字函数

(1) 小数点后1位四舍五入

select round(2.76,1) from dual;

(2) 对整数四舍五入

select round(2.76) from dual;

(3)向上取整，ceil表示天花板

select ceil(4.3) from dual;

(4)向下取整，floor表示地板

select floor(4.8) from dual;

(5)求余数

select mod(10,3) from dual;

(6)截断

select trunc(10.454212348211,2),trunc(21154.1454211,-2) from dual;
注意：第二个参数是正数，截断小数点后面的两位
      第二个参数是负数，截断小数点前面的两位

##########################################################################################
5.日期函数

(1)截断日期

select trunc(to_date('20140610','yyyy-mm-dd'),'dd'),trunc(to_date('20140610','yyyy-mm-dd'),'mm'),
       trunc(to_date('20140610','yyyy-mm-dd'),'yyyy') from dual;

select trunc(to_date('20140610121212','yyyy-mm-dd hh24:mi:ss'),'hh'),
       trunc(to_date('20140610121212','yyyy-mm-dd hh24:mi:ss'),'mi') from dual;

(2) 两个日期之间的天数  =  两个日期相减

select sysdate - to_date('20130610095000','yyyy-mm-dd hh24:mi:ss') from dual;

(3) 月份前后推

select add_months(sysdate,-12),add_months(sysdate,3) from dual;

第二个参数为负数，往前推
第二个参数为正数，往后推

(4) 算两个日期间隔的月数

select months_between(sysdate,to_date('20130610100000','yyyy-mm-dd hh24:mi:ss')) from dual;

例子：

   计算入职时间年数的分布：

    A：1年以内
    B：1~3年
    C：3~5年
    D: 5年以上

   select rs1,count(*) from
     (select empno,(case when rs<=1 then 'BELOW 1'
                    when rs>1 and rs<3 then '1~3'
                    when rs>=3 and rs<5 then '3~5'
                    when rs>=5 then 'over 5' end) as rs1
       from(
        select empno,round((months_between(sysdate,hiredate)/12)) as rs
          from emp
        )
     )group by rs1;

(5) decode函数
   
    F=decode(p1,p2,p3,p4,p5...,pn)
   含义：如果p1 = p2,F=p3
     如果p1 = p4,F=p5
         .....
     如果都不满足，F=pn

  select decode(deptno,10,'dept10',20,'dept20',30,'dept30','dept40') as x  from dept;
=
  select (case when deptno=10 then 'dept10'
             when deptno=20 then 'dept20'
             when deptno=30 then 'dept30'
             else 'dept40' end
        ) as x
  from dept;

(6)nvl函数

   nvl(comm,9999) -- 如果第一个参数为null，就显示第二个参数

示例：求当第一个数为null的时候，值取9999，否则等于原值

select empno,nvl(comm,9999) as x,decode(comm,null,9999,comm) as y,(case when comm is null then 9999 else comm end) as z  from emp;

###########################################################################################
6.表的连接

6.1等值连接

select b.deptno,a.ename from dept b (inner) join emp a on(a.deptno=b.deptno)
等值连接其实就是内连接


6.2左连接

select b.deptno,a.ename from dept b left (outer) join emp a on(a.deptno=b.deptno);
dept -- 主表
emp  -- 副表
编写方式：主表 left join 副表，保证主表的数据不丢失

6.3右连接

select b.deptno,a.ename from emp a right join dept b on(a.deptno=b.deptno);
dept  -- 主表
emp   -- 副表
编写方式：副表 right join 主表，保证主表的数据不丢失

oracle语法：将左右连接统一表示
select b.deptno,a.ename from emp a, dept b where a.deptno(+)=b.deptno;
select b.deptno,a.ename from emp a, dept b where b.deptno=a.deptno(+);
(+)的对面的表就是主表
dept表是主表
emp 表是副表


注意：
  A：如果副表上有过滤条件的时候，怎样满足左右连接的效果

select b.deptno,a.ename from dept b left join emp a on(a.deptno=b.deptno) where a.sal>1000;
为了加过滤条件，把主表的数据丢了，此时失去左连接的意义

select b.deptno,a.ename from dept b left join emp a on(a.deptno=b.deptno and a.sal>1000);
=
select b.deptno,a.ename from dept b,emp a where a.deptno(+)=b.deptno and a.sal(+)>1000;

  B：主表上有过滤条件，不存在这个问题

数学原理：

左连接 ：   A∪(A∩B)
等值连接：  A∩B

6.4多表关联

select * from a,b,c,d where a.x=b.x and b.y=c.y and c.z=d.z;

特殊的连接：笛卡尔积
  select * from a,b

6.5非等值连接

select e.ename,s.grade,e.sal
  from emp e,salgrade s
where e.sal between s.losal and s.hisal;

salgrade：薪资等级表

6.6自连接

查询所有员工的直接上司的对应关系，表示成“员工名称 work for 上司名称”，一般用在有层级关系的表上面
  select worker.ename ||' work for '||manager.ename
  from emp worker,emp manager
where worker.mgr=manager.empno;


##########################################################################################
7 集合运算

7.1 求差集

A. not exists操作和not in 操作

select b.deptno,b.dname,b.loc from dept b
where not exists(select a.deptno from emp a where a.deptno=b.deptno);
=
select b.deptno,b.dname,b.loc from dept b
where b.deptno not in(select a.deptno from emp a where a.deptno=b.deptno);

B. minus操作

select b.deptno from dept b
minus
select a.deptno from emp a;

7.2 求交集

A exists操作 和 in操作

select b.deptno,b.dname,b.loc from dept b
where  exists(select a.deptno from emp a where a.deptno=b.deptno);
=
select b.deptno,b.dname,b.loc from dept b
where b.deptno in(select a.deptno from emp a where a.deptno=b.deptno);

B 等值连接

select distinct b.deptno,b.dname,b.loc from emp a,dept b where a.deptno=b.deptno;

7.3 求并集

A 左右连接（其中一个集合是另外一个集合的子集）

select b.deptno,a.ename from dept b left join emp a on(a.deptno=b.deptno);

B union操作

select b.deptno from dept b    --合并后去重
union
select a.deptno from emp a;

select b.deptno from dept b     --合并之后不去重
union all
select a.deptno from emp a;

7.4 求全集

select * from emp;

7.5 求子集

select * from emp where ename like 'T%';

7.6 求补集

select * from emp where ename not like 'T%';

7.7 求映射

select ename,job from emp where ename like 'T%';
在操作ename集合的时候，同时查询job字段映射的值

###########################################################################################
8.分组报表

select b.deptno,b.dname,nvl(floor(avg(a.sal)),0) as x,nvl(sum(a.sal),0) as y,nvl(min(a.sal),0) as z,nvl(max(a.sal),0) as l
  from emp a,dept b
where a.deptno(+)=b.deptno
group by b.deptno,b.dname
order by b.deptno,b.dname;

having 字句  --  对聚合函数的结果进行过滤

select b.deptno,b.dname,nvl(floor(avg(a.sal)),0) as x,nvl(sum(a.sal),0) as y,nvl(min(a.sal),0) as z,nvl(max(a.sal),0) as l
  from emp a,dept b
where a.deptno(+)=b.deptno
group by b.deptno,b.dname
having avg(a.sal)>2000
order by b.deptno,b.dname;

注意：having字句一定要放在order by的前面，group by的后面

##########################################################################################
9.单行子查询还有多行子查询

(1) 单行子查询：返回一行的子查询

   需求：显示scott员工姓名、薪水、部门号并且与其同部门的人相同信息也显示出来

   select ename,sal,deptno from emp
    where deptno=(select deptno from emp where ename='SCOTT')
  =
   select ename,sal,deptno from emp
    where deptno in (select deptno from emp where ename='SCOTT')

(select deptno from emp where ename='SCOTT')返回单行，叫做单行子查询。 =、in 叫做单行操作符

(2) 多行子查询：返回多行的子查询

  需求：显示部门10所有岗位job在全公司对应相同岗位人的姓名、薪水、部门号
   
select ename,sal,deptno from emp
where job in (select job from emp where deptno=10)
in 在这里是作为多行操作符使用

select ename,sal,deptno from emp
where job = (select job from emp where deptno=10)  
错误的写法，=是单行操作符

  A 在多行子查询中使用any操作符
  
  需求：显示全公司工资高于部门10任一员工工资的员工的姓名、薪水、部门号

select ename,sal,deptno from emp
where sal > any(select sal from emp where deptno=10)
>是单行操作符，而子查询是多行的，用any来解决语法冲突，满足'任一'这种需求

  B 在多行子查询中使用all操作符

  需求：显示全公司工资高于部门20所有员工工资的员工的姓名，薪水，部门号
  select ename,sal,deptno from emp where sal > all(select sal from emp where deptno=20)
>是单行操作符，而子查询是多行的，用all来解决语法冲突，满足'所有'这种需求

注意：
1.子查询中不能写order by
2.多行子查询不能用单行操作符匹配
3.单行子查询可以匹配多行操作符
4.子查询必须写在括号里面
5.子查询要放在比较运算符后面

10. 嵌套子查询(多个子查询)

select ename,sal,deptno from emp
where sal > (select sal from emp where empno=7876) and job = (select job from emp where empno=7369);