mysql语句的执行顺序
select 查询列表 # 7
from 表 # 1
[join type join 表2 # 2
on 连接条件 # 3
where 筛选条件 # 4
group by 分组条件 # 5
having 分组后筛选 # 6
order by 排序字段] # 8
limit [offset,]size; # 9
每一步生成虚拟的结果集
分页 :
select 查询列表
from 表
limit (page - 1) * size,size; # offset为偏移量
七种JOIN理论
FULL OUTER JOIN ==>
SELECT * FROM tb1_emp a left join tb1_dept b on a.deptId = b.id
UNION
SELECT * FROM tb1_emp a right join tb1_dept b on a.deptId = b.id
#UNION有去重功能
SELECT * FROM tb1_emp a left join tb1_dept b on a.deptId = b.id WHERE
b.id is NULL
UNION
SELECT * FROM tb1_emp a right join tb1_dept b on a.deptId = b.id WHERE
a.deptId is NULL;
deptId = b.id WHERE
a.deptId is NULL;