数据库操作（八）

1、分页查询
Oracle分页关键字：rownum
MySql的分页关键字：limit

1）、rownum：伪列
该列在数据库表中并不存在，但是它又“存在于”
任何的表中。该字段的取值取决于查询语句的记录数。
rownum其实就是对查询出的记录数据做编号处理，
编号从1开始。

    rownum存在的sql，必须是对真实字段名的查询。

    --rownum不能直接跟*一起使用。
    select *,rownum from emp;

    --rownum正确使用
    select empno,ename,hiredate,sal,deptno,rownum
    from emp;

    --查询rownum <= 终止位置
    --查询前10条数据
    select empno,ename,hiredate,sal,deptno,rownum
    from emp
    where rownum <= 10;

    --若按每页5条数据
    --查询第一页为前5条数据:1~5
    select empno,ename,hiredate,sal,deptno,rownum
    from emp
    where rownum >= 1 and rownum <= 5;

    --查询第二页为后5条数据：6~10
    select empno,ename,hiredate,sal,deptno,rownum
    from emp
    where rownum >= 6 and rownum <= 10;

结论：在第一次进行rownum查询时，不建议使用rownum
做大于判断，否则可能达不到预期效果。
如：上面的第一页有数据，第二页无数据。

原因：rownum默认从1开始，而rownum的编号自增前提
根据条件能查询到下一条数据。查询一条数据，才会
有rownum的自增。而查询语句中where条件中要求
一上来就rownum>=6，这里有矛盾关系，
rownum从1开始，要想rownum>=6,必须使得rownum先
自增到6才行。
所以导致where rownum>=6的条件，永远都不可能满足。

解决方案：如何才能做rownum的大于操作？
先编号，再分页。

--后分页
select empno,ename,hiredate,sal,deptno,temp.num
from(
    --先编号
    select empno,ename,hiredate,sal,deptno,
            rownum num --****给rownum取别名
    from emp
) temp
where temp.num >= 6 and temp.num <= 10;

2）、分页要排序
按部门排序
因为：select ruwnum执行顺序在order by之前，
会导致查询的数据，先编号，再排序。
排序的结果比如会导致编号顺序杂乱，也仅仅是
对固定编号内的结果数据，进行小范围的排序而已。
根本也无法实现真正意义上的排序。

结论：排序一定要在编号之前完成。

最后的分页sql就得按照如下顺序：
先排序、再编号、最后分页

--最后分页
select empno,ename,hiredate,sal,deptno,num
from(
    --再编号
    select empno,ename,hiredate,sal,deptno,
            rownum num --****给rownum去别名
    from (
    --先排序
        select * from emp order by deptno
    )
    --where rownum <= 10   --小于操作也可以此处。
)
where num >= 6 and num <= 10;--推荐使用这样编写

3）、分页参数
a、pageSize：
每页显示条数（记录数）
即一页多少条数据。

b、pageNum：
页码数
即需要查询为第几页。

举例：每页显示5条
第1页：1~5
第2页：6~10
第3页：11~15
...
第100页：496~50
对应页码的记录数为：
rownum起始位置：
(pageNum - 1) * pageSize + 1

rownum终止位置：
pageNum * pageSize;

4）、终极分页sql

    --最后分页
    select empno,ename,hiredate,sal,deptno,num
    from(
        --再编号
        select empno,ename,hiredate,sal,deptno,
                rownum num --****给rownum去别名
        from (
        --先排序
            select * from emp order by deptno
        )
    )
    where num >= ((pageNum - 1) * pageSize + 1) 
    and num <= (pageNum * pageSize);

2、高级函数
1）、decode函数
可以实现类似switch-case效果。

语法：
decode(字段名,
    search1,result1,
    search2,result2,
    ...
    searchN,resultN,  --如果没有default，逗号省略
    [default]
)

可以使用decode函数，用在两个方面：
①、分组
    统计人数
    --按照job职位分组统计人数
    select job,count(*) from emp
    group by job;

    --将MANAGER与ANALYST统计为vip组，
    --其他职位统计为other组
    --要求统计vip与other组的人数。
    select decode(job,
        'MANAGER','vip',
        'ANALYST','vip',
        'other'
    ) 职位,count(*) from emp
    group by 
    decode(job,
        'MANAGER','vip',
        'ANALYST','vip',
        'other'
    );

②、排序
    --按薪资、部门、job排序
    select * from emp
    order by job;--按职位的首字母ASIIC码自然排序

    --现实中，必须要严格遵循职位重要性来排序。
    PRESIDENT
    MANAGER
    ANALYST
    CLERK
    SALESMAN

     select * from emp
     order by 
    decode(job,
        'PRESIDENT',1,
        'MANAGER',2,
        'ANALYST',3,
        'CLERK',4,
        5
    );

2）、case-when函数
与decode效果一致，但是语法复杂很多，建议使用decode。

    语法：

        case 字段名
        when search1 then result1
        when search2 then result2
        ...
        when searchN then resultN
        else resultN+1
        end;

    --现实中，必须要严格遵循职位重要性来排序。
        PRESIDENT
        MANAGER
        ANALYST
        CLERK
        SALESMAN
         
         select * from emp
         order by 
        (case job
            when 'PRESIDENT' then 1
            when 'MANAGER' then 2
            when 'ANALYST' then 3 
            when 'CLERK' then 4
            else 5
        end);

        

    decode(字段名,
        search1,result1,
        search2,result2,
        ...
        searchN,resultN,  --如果没有default，逗号省略
        [default]
    )

3）、排序函数
查询员工信息，根据部门分组，工资顺序排序。
select * from emp
group by deptno
order by sal;
怎么办？？？
对于如上组内排序的sql，可以直接使用高级排序函数来实现。
根据排序结果，分为：

    ①、组内（编号）连续且唯一排序
        row_number

        语法：row_number() --连续且唯一编号
          --按以下规则
          over( 
            partition by 字段1 --按字段1分组
            order by 字段2     --按字段2排序
          )

        如：查询员工信息，根据部门分组，工资顺序排序。
        select empno,ename,hiredate,sal,deptno,
            (row_number() --连续且唯一编号
            --按以下规则
            over( 
                partition by deptno --按字段1分组
                order by sal     --按字段2排序
            )) 连续且唯一
        from emp;

    ②、组内（编号）连续不唯一排序
        dense_rank
        语法：dense_rank() --连续不唯一编号
          --按以下规则
          over( 
            partition by 字段1 --按字段1分组
            order by 字段2     --按字段2排序
          )

        如：查询员工信息，根据部门分组，工资顺序排序。
        select empno,ename,hiredate,sal,deptno,
            (dense_rank() --连续不唯一编号
            --按以下规则
            over( 
                partition by deptno --按字段1分组
                order by sal     --按字段2排序
            )) 连续不唯一编号
        from emp;
    

    ③、组内（编号）不连续不唯一排序
        rank
        语法：rank() --不连续不唯一编号
          --按以下规则
          over( 
            partition by 字段1 --按字段1分组
            order by 字段2     --按字段2排序
          )

        如：查询员工信息，根据部门分组，工资顺序排序。
        select empno,ename,hiredate,sal,deptno,
            (rank() --不连续不唯一编号
            --按以下规则
            over( 
                partition by deptno --按字段1分组
                order by sal     --按字段2排序
            )) 不连续不唯一编号
        from emp;

2、高级函数
1）、分支函数
decode
分支效果。
来实现排序、分组。

case-when与decode函数功能相同

2）、排序函数
row_number()：组内连续且唯一。
rank()：组内不连续不唯一。
dense_rank()：组内连续不唯一。

正课：
1、高级函数
1）、分支函数
2）、排序函数
3）、集合操作

    ①、并集
        union：自动去重联合查询
        --得到15条数据
        select * from emp -- 员工表集合 15
        union
        select * from leader;--领导表集合 6
        
        union all：不去重联合查询
        --得到21条数据
        select * from emp -- 员工表集合 15
        union all
        select * from leader;--领导表集合 6

    ②、交集
        intersect
        --得到6条数据
        select * from emp -- 员工表集合 15
        intersect
        select * from leader;--领导表集合 6

    ③、差集
        minus
        集合1 差 集合2
            --得到9条数据
        select * from emp --员工表集合 15
        minus
        select * from leader;--领导表集合 6
    
        集合2 差 集合1
        --得到0条数据
        select * from leader--领导表集合 6
        minus
        select * from emp;--员工表集合 15

最后编辑于：2017.12.04 11:27:02

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 203,324评论 5赞 476
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 85,303评论 2赞 381
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 150,192评论 0赞 337
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 54,555评论 1赞 273
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 63,569评论 5赞 365
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 48,566评论 1赞 281
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 37,927评论 3赞 395
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 36,583评论 0赞 257
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 40,827评论 1赞 297
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 35,590评论 2赞 320
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 37,669评论 1赞 329
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 33,365评论 4赞 318
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 38,941评论 3赞 307
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 29,928评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 31,159评论 1赞 259
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 42,880评论 2赞 349
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 42,399评论 2赞 342

数据库操作（八）

推荐阅读更多精彩内容