MYSQL和ORACLE的分页

冷不防 2024-04-01 17:48 70阅读 0赞

### MYSQL使用limit进行分页 ###

select * from stu limit m,n; // m=(pageIndex-1)*pageSize,n=pageSize
    -- 返回总条,查询表添加字段sql_calc_found_rows
    select sql_calc_found_rows a.* from AAA a limit m,n
    -- found_rows单独查询总条数
    select found_rows total;

1.  第一个参数m表示起始行，第二个参数表示取多少行；
2.  pageIndex表示页索引，即查询第几页的数据（从1开始），pageSize表示页大小，即一页最多显示多少行记录；
3.  m= (2-1)\*10+1=11,n=10，limit 11,10，表示从第11行开始取记录行（包含第11行），共取10行，也表示查询第2页的数据，这一页最多显示10行记录。

### ORACLE使用rownum进行分页 ###

select * from(
      select rownum rn,a.*,count(*) over() total from table_name a where rownum <= x 
      -- 结束行，x=pageIndex*pageSize
    )
    where rn >= y; -- 起始行，y=(pageIndex-1)*pageSize+1
    
    -- 返回总条数
    select count(*) over() total from AAA

1.  >= y，<= x表示从第y行（起始行）~x行（结束行） 。
2.  rownum只能比较小于，不能比较大于，因为rownum是先查询后排序的，例如你的条件为rownum>1，当查询到第一条数据，rownum为1，则不符合条件。第2、3…类似，一直不符合条件，所以一直没有返回结果。所以查询的时候需要设置别名，然后查询完成之后再通过调用别名进行大于的判断。

### MYSQL与ORACLE级联查询 ###

1.  start with: 指定起始节点的条件
2.  connect by: 指定父子行的条件关系
3.  prior: 查询父行的限定符，格式: prior column1 = column2 or column1 = prior column2 and ...
4.  nocycle: 若数据表中存在循环行，那么不添加此关键字会报错，添加关键字后，便不会报错，但循环的两行只会显示其中的第一条
5.  循环行: 该行只有一个子行，而且子行又是该行的祖先行
6.  connect\_by\_iscycle: 前置条件:在使用了nocycle之后才能使用此关键字，用于表示是否是循环行，0表示否，1 表示是
7.  connect\_by\_isleaf: 是否是叶子节点，0表示否，1 表示是
8.  level: level伪列,表示层级，值越小层级越高，level=1为层级最高节点

-- connect by: 指定父子行的条件关系
    -- start with: 指定起始节点的条件
    select c.bc_id,c.bc_name from org_busicorp c 
    connect by prior c.bc_pid = c.bc_id
    start with c.bc_id = '001';

**MYSQL省市区级联查询**

-- 二三级查询
    select c1.bc_id,c1.bc_name from org_busicorp c1 where c1.bc_pid = '00'
    union
    select c2.bc_id,c2.bc_name from org_busicorp c2 
    left join org_busicorp c1 on c1.bc_id = c2.bc_pid
    where c1.bc_pid = '00'
    -- 包含当前id
    union
    select c3.bc_id,c3.bc_name from org_busicorp c3 where c3.bc_id = '00'

**MYSQL** 递归函数

SELECT
    	ID.LEVEL,
    	DATA.* 
    FROM
    	(
    	SELECT
    		@ids AS _ids,
    		( SELECT @ids := GROUP_CONCAT( id ) FROM 表名 WHERE FIND_IN_SET( 父级 id字段, @ids ) ) AS cids,
    		@l := @l + 1 AS LEVEL 
    	FROM
    		表名,
    		( SELECT @ids := '条件id', @l := 0 ) b 
    	WHERE
    		@ids IS NOT NULL 
    	) ID,
    	表名 DATA 
    WHERE
    	FIND_IN_SET( DATA.id, ID._ids ) 
    ORDER BY
    	LEVEL,
    	id

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5bCP5bCP5oCq5ZGAfg_size_20_color_FFFFFF_t_70_g_se_x_16][]

### **MYSQL**与ORACLE**插入数据存在修改、不存在新增** ###

**MYSQL插入数据存在修改、不存在新增**

insert into `subject`(subjectId,subjectName) 
    values('7','离散') 
    on duplicate key update subjectName='离散数学';

**ORACLE插入数据存在修改、不存在新增**

merge into 目标表 a
     
    using 源表 b
     
    on(a.条件字段1=b.条件字段1 and a.条件字段2=b.条件字段2 ……)  
     
    when matched then update set a.字段=b.字段 --目标表别称a和源表别称b都不要省略
     
    when  not matched then insert (a.字段1,a.字段2……)values(b.字段1,b.字段2……) --目标表别称a可省略,源表别称b不可省略

*  源表b可能是一张表结构不同于a的表,有可能是一张构建相同表结构的临时表,也有可能是我们自己组起来的数据

merge into student a
    using (select '7' as id from dual) s
    on (a.id = s.id)
    when matched then
      update set a.student_name = '小明二号'
    when not matched then
      insert (id, student_name, fk_class) values ('7', '小明', '2')

[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5bCP5bCP5oCq5ZGAfg_size_20_color_FFFFFF_t_70_g_se_x_16]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/4f6069ed4ae845e1b623ba03e3fb3645.png