本文主要是讲述 SQL 的语法结构，以及 SQL 的解析过程。还有就是 7 种查询语法介绍。

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;

参考文档

• mysql.com