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單獨(dú)查詢總條數(shù)
select found_rows total;

select * from(
  select rownum rn,a.*,count(*) over() total from table_name a where rownum <= x 
  -- 結(jié)束行，x=pageIndex*pageSize
)
where rn >= y; -- 起始行，y=(pageIndex-1)*pageSize+1
-- 返回總條數(shù)
select count(*) over() total from AAA

-- connect by: 指定父子行的條件關(guān)系
-- start with: 指定起始節(jié)點(diǎn)的條件
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';

-- 二三級(jí)查詢
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'
-- 包含當(dāng)前id
union
select c3.bc_id,c3.bc_name from org_busicorp c3 where c3.bc_id = '00'

SELECT
	ID.LEVEL,
	DATA.* 
FROM
	(
	SELECT
		@ids AS _ids,
		( SELECT @ids := GROUP_CONCAT( id ) FROM 表名 WHERE FIND_IN_SET( 父級(jí) 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

insert into `subject`(subjectId,subjectName) 
values('7','離散') 
on duplicate key update subjectName='離散數(shù)學(xué)';

merge into 目標(biāo)表 a
using 源表 b
on(a.條件字段1=b.條件字段1 and a.條件字段2=b.條件字段2 ……)  
when matched then update set a.字段=b.字段 --目標(biāo)表別稱a和源表別稱b都不要省略
when  not matched then insert (a.字段1,a.字段2……)values(b.字段1,b.字段2……) --目標(biāo)表別稱a可省略,源表別稱b不可省略

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 = '小明二號(hào)'
when not matched then
  insert (id, student_name, fk_class) values ('7', '小明', '2')