sql second day

最新推荐文章于 2024-05-28 10:44:55 发布

转载最新推荐文章于 2024-05-28 10:44:55 发布 · 120 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/exchange2015/p/5813336.html

本文详细介绍了SQL中的数据类型转换方法，包括显示转换与隐式转换，并对常用的转换函数进行了说明。此外，还深入探讨了聚合函数如SUM、MAX、MIN等的应用场景及其注意事项，同时介绍了分组查询、高级分组函数以及分支函数的使用技巧。

转换函数

       to_char(x,y)
       to_date(x,y)
       to_number(x,y)

number <---->   char <---->   date

       number 转换成date必须先转换成char，然后由char再转换成date
       隐式转换
               SQL> select ename,job from emp where empno=7788;
               SQL> select ename,job from emp where empno='7788';
       返回结果一样，但是由于empno本身是数值类型，如果使用''将数字转换成字符，在查找时会自动转换成数值，从而降低执行效率

       显示转换
               SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
       sysdate是日期类型，把日期类型转换成字符类型 to_char


       查找1981年入职的员工
               SQL>select ename,hiredate from emp where to_char(hiredate,'yyyy')='1981'


通用函数
       nvl(x,y)       当x为空的时候，将其赋值为y
               SQL> select nvl(comm,0) from emp;

       nvl2(x,y,z)   当x为空，返回z，否则返回y
               SQL> select ename,sal,nvl2(comm,'yes','no') from emp;
               SQL> select comm,sal,nvl2(comm,comm+sal,sal) from emp;

       nullif(x,y)       如果x与y相等返回null，否则返回x，x与y的类型必须一致




正则表达式：了解
       regexp_like
       regexp_substr
       regexp_instr
       regexp_replace





多行函数，聚合函数，对空值不做处理，*是例外
       sum
       max
       min
       avg
       count

       SQL> select count(comm) from emp;          返回值   4    ，空值不被统计

       SQL> select count() from emp;       报错
       SQL> select count(*) from emp;    返回值14.空值被统计




       显示有多少部门
               SQL> select distinct deptno from emp;

       group by 分组
       group by column, ...可以跟多个列，按照多个列进行分组，例如每个部门的每个职位的统计


       SQL> select sum(sal) from emp group by deptno;
       SQL> select deptno, sum(sal) from emp group by deptno;
       SQL> select ename,deptno, sum(sal) from emp group by deptno;           报错，ORA-00979: not a GROUP BY expression
       SQL> select ename,deptno, sum(sal) from emp group by deptno,ename;           前面出现的列，除了聚合函数外，其他必须在group by后面出现
       SQL> select deptno dpt,sum(sal) from emp group by dpt;               报错，不可以使用别名



       统计不同入职年分的工资总和
       SQL> select sum(sal),to_char(hiredate,'yyyy') from emp group by to_char(hiredate,'yyyy');
              SUM(SAL) TO_C
           ---------- ----
                  4100 1987
                   800 1980
                  1300 1982
               22825 1981
       SQL> select sum(sal),to_char(hiredate,'yyyy') from emp group by hiredate;       这条语法没有错误，但是统计结果不正确，前后必须都使用相同的格式



       having       对聚合函数的结果再进行筛选，跟在group by子句后面
       SQL>select sum(sal),to_char(hiredate,'yyyy') from emp group by to_char(hiredate,'yyyy') having sum(sal)>2000;
           SUM(SAL) TO_C
           ---------- ----
                  4100 1987
               22825 1981

查询语句基本构造顺序
       select ......
       from ......
       where ......
       group by ......
       having ......
       order by .....

       select ename,sal from emp order by sal desc;



高级分组函数：数据仓储，数据分析中使用的比较多
       cube           跟在group by后面，对统计的结果进行再次统计
       rollup
       grouping sets


       cube(a) ---> a, 0
       cube(a,b)   ---> ab,a,b, 0 先ab统计一次；再a统计一次，b统计一次；最后在做一次统计总的

       分解：
       select sum(sal),deptno,job from emp group by deptno,job;
       select sum(sal),deptno from emp group by cube(deptno);
       select sum(sal),deptno,job from emp group by cube(deptno,job);

       grouping()判断某个列有没有参与分组 0表示参与，1表示不参与
   select sum(sal),deptno,job,grouping(deptno) d,grouping(job) j from emp group by cube(deptno,job);


       rollup
       rollup(a,b)   ---> ab,a, 0
       rollup(a,b,c)   ---> abc,ab, a,0

       select sum(sal),deptno,job from emp group by rollup(deptno,job);


       select sum(sal),deptno,grouping(deptno) from emp group by cube(deptno)
       grouping sets(a,b)   --->a,b

       select sum(sal),deptno,job,grouping(deptno),grouping(job) from emp group by grouping sets (deptno,job)


分支函数

       decode(column|exp,value1,result1,value2,result2,default) as 别名        as 别名可以不要
       如果列或者表达式的值是value1,则返回result1，如果value2,则返回result2..... 如果条件都不满足就返回default，default可以没有



       case
       方法1
       case when ... then ...
               when ... then ...
               ......
               else   ...
               end as '<>';           else可以省略，as也可以省略
       方法1
       case <> when ... then ...
                      when ... then ...
                      ......
                      else   ...
                      end as '<>';
       等值判断可以选择decode 或者case 2种方法都可以，如果是不等判断，则使用case方法1


       SQL> select deptno,                           字段后面需要加上逗号
                case deptno when 10 then 'p1'
                when 20 then 'p2'
                when 30 then 'p3'
                else 'not in'
                end as partment
                from dept;



行列转换
       decode 和count 配合使用实现行列转换
       model

        select count(decode(to_char(hiredate,'yyyy'),'1981',1)) "1981" from emp;
        这里的decode 函数没有提供默认值，因为count不会统计空，如果设置了默认值，会把所有的统计出来

       ''       指定字符串
       ""   指定对象
       字符串做别名的时候，要么不用引号，要么使用 ""



多表查询
       显示ename   job danme
       每个列出现在哪张表，表与表建的关系通过什么联系

       如果两个表没有联系，直接进行多表查询，结果数是一个迪卡尔乘积（每张表的行的乘积）

       表与表之间的链接条件最少为表数量-1

       1. 表与表之间的关系
       2. 链接条件n个表需要有n-1 个条件
       3. 迪卡尔乘积，行*行的结果

       sql标准
       工业标准

       sql标准

       select a.column b.column, ......           需要使用别名指定字段所属表，提高可读性
       from table1 a, table2 b, ......
       where a.column=b.column
       ......
       group by ......
       order by ......

       1 等值链接           条件相等
           SQL> select e.ename,d.dname,e.deptno from emp e,dept d where e.deptno=d.deptno;
       2 不等链接           范围
           显示 ename sal comm grade
           select e.ename,e.sal,e.comm,s.grade
           from emp e, salgrade s
           where e.sal between s.losal and s.hisal;

           select e.ename,e.sal,d.dname,s.grade
           from emp e, salgrade s,dept d
           where e.sal between s.losal and s.hisal
           and d.deptno=e.deptno;


       3 自链接       自己与自己链接
       4 外链接       显示没有的，分为左外链接，右外链接

转载于:https://www.cnblogs.com/exchange2015/p/5813336.html