1、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;
- 第一个参数m表示起始行,第二个参数表示取多少行;
- pageIndex表示页索引,即查询第几页的数据(从1开始),pageSize表示页大小,即一页最多显示多少行记录;
- m= (2-1)*10+1=11,n=10,limit 11,10,表示从第11行开始取记录行(包含第11行),共取10行,也表示查询第2页的数据,这一页最多显示10行记录。
2、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
- >= y,<= x表示从第y行(起始行)~x行(结束行) 。
- rownum只能比较小于,不能比较大于,因为rownum是先查询后排序的,例如你的条件为rownum>1,当查询到第一条数据,rownum为1,则不符合条件。第2、3…类似,一直不符合条件,所以一直没有返回结果。所以查询的时候需要设置别名,然后查询完成之后再通过调用别名进行大于的判断。
3、MYSQL与ORACLE级联查询
- start with: 指定起始节点的条件
- connect by: 指定父子行的条件关系
- prior: 查询父行的限定符,格式: prior column1 = column2 or column1 = prior column2 and ...
- nocycle: 若数据表中存在循环行,那么不添加此关键字会报错,添加关键字后,便不会报错,但循环的两行只会显示其中的第一条
- 循环行: 该行只有一个子行,而且子行又是该行的祖先行
- connect_by_iscycle: 前置条件:在使用了nocycle之后才能使用此关键字,用于表示是否是循环行,0表示否,1 表示是
- connect_by_isleaf: 是否是叶子节点,0表示否,1 表示是
- 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
4、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')