SQL 语句
SQL 语法格式
语法格式如下：
SQL 解析过程
SQL 执行过程
SQL 解析过程
JSON 语句
JSON 图解
SQL 图解如下
建表SQL
create database `oemp`; use `oemp`; #部门表 create table `tb_dept` ( `id` int(11) not null auto_increment, `name` varchar(30) default null, `storey` varchar(40) default null, primary key(`id`) ) engine = innodb auto_increment=1 default charset=utf8; #员工表 create table `tb_emp` ( `id` int(11) not null auto_increment, `name` varchar(30) default null, `dept_id` int(11) default null, primary key(`id`), key `idx_dept_id`(`dept_id`) #, constraint `fk_dept_id` foregign key(`dept_id`) references `tb_dept` (`id`) ) engine = innodb auto_increment=1 default charset=utf8; #部门数据 insert into `tb_dept`(`id`, `name`, `storey`) values('1', 'RD', '11'); insert into `tb_dept`(`id`, `name`, `storey`) values('2', 'HR', '12'); insert into `tb_dept`(`id`, `name`, `storey`) values('3', 'MK', '13'); insert into `tb_dept`(`id`, `name`, `storey`) values('4', 'MIS', '14'); insert into `tb_dept`(`id`, `name`, `storey`) values('5', 'FD', '15'); #员工数据 insert into `tb_emp`(`name`, `dept_id`) values('z3', 1); insert into `tb_emp`(`name`, `dept_id`) values('z4', 1); insert into `tb_emp`(`name`, `dept_id`) values('z5', 1); insert into `tb_emp`(`name`, `dept_id`) values('w5', 2); insert into `tb_emp`(`name`, `dept_id`) values('w6', 2); insert into `tb_emp`(`name`, `dept_id`) values('s7', 3); insert into `tb_emp`(`name`, `dept_id`) values('s8', 4); insert into `tb_emp`(`name`, `dept_id`) values('s9', 51);
7 种 JOIN 查询
#关联查询 select * from `tb_dept`; select * from `tb_emp`; #内连接查询 select * from `tb_emp` a inner join `tb_dept` b on a.dept_id = b.id; #左连接查询 select * from `tb_emp` a left join `tb_dept` b on a.dept_id = b.id; #右连接查询 select * from `tb_emp` a right join `tb_dept` b on a.dept_id = b.id; #左差集查询 select * from `tb_emp` a left join `tb_dept` b on a.dept_id = b.id where b.id is null; #右差集查询 select * from `tb_emp` a right join `tb_dept` b on a.dept_id = b.id where a.id is null; #全连接查询 （全集）(MySQL不支持 full outer join 语法) # oracle: select * from `tb_emp` a full outer join `tb_dept` b on a.dept_id = b.id; # union 拼接且去重 select * from `tb_emp` a left join `tb_dept` b on a.dept_id = b.id union select * from `tb_emp` a right join `tb_dept` b on a.dept_id = b.id; #A/B独有的数据（差集） select * from `tb_emp` a left join `tb_dept` b on a.dept_id = b.id where b.id is null union select * from `tb_emp` a right join `tb_dept` b on a.dept_id = b.id where a.id is null;
1. left join
#左连接查询 select * from `tb_emp` a left join `tb_dept` b on a.dept_id = b.id;
2. right join
#右连接查询 select * from `tb_emp` a right join `tb_dept` b on a.dept_id = b.id;
3. 交集 （inner join）
#关联查询 select * from `tb_dept`; select * from `tb_emp`; #内连接查询 select * from `tb_emp` a inner join `tb_dept` b on a.dept_id = b.id;
4. 左差集
#左差集查询 select * from `tb_emp` a left join `tb_dept` b on a.dept_id = b.id where b.id is null;
5. 右差集
#右差集查询 select * from `tb_emp` a right join `tb_dept` b on a.dept_id = b.id where a.id is null;
6. 全集
#全连接查询 （全集）(MySQL不支持 full outer join 语法) # oracle: select * from `tb_emp` a full outer join `tb_dept` b on a.dept_id = b.id; # union 拼接且去重 select * from `tb_emp` a left join `tb_dept` b on a.dept_id = b.id union select * from `tb_emp` a right join `tb_dept` b on a.dept_id = b.id;
7. 差集
#A/B独有的数据（差集） select * from `tb_emp` a left join `tb_dept` b on a.dept_id = b.id where b.id is null union select * from `tb_emp` a right join `tb_dept` b on a.dept_id = b.id where a.id is null;
