SQL执行循序：
手写：
SELECT DISTINCT <query_list> FROM <left_table> <join type> JOIN <right_table> ON <join_condition> WHERE <wherer_condition> GROUP BY <group_by_list> HAVING <having_condition> ORDER BY <order_by_list> LIMIT <limit number>
机读：
FROM <left_table> ON <join_condition> <join_type> JOIN <right_table> WHERE <where_condition> GROUP BY <group_by_list> HAVING <having_condition> SELECT DISTINCT <query_list> ORDER BY <order_by_condition> LIMIT <limit_number>
总结：
理论图谱：
MySQL数据库实例：
1.创建数据库：
mysql> create database db_test;
Query OK, 1 row affected (0.01 sec)
2.使用数据库：
mysql> use db_test;
Database changed
3.创建表、添加数据：
CREATE TABLE `tb_dept` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '部门主键', `deptName` varchar(30) DEFAULT NULL COMMENT '部门名称', `locAdd` varchar(40) DEFAULT NULL COMMENT '楼层', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; CREATE TABLE `tb_emp` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '员工主键', `name` varchar(20) DEFAULT NULL COMMENT '员工姓名', `deptId` int(11) DEFAULT NULL COMMENT '部门外键', PRIMARY KEY (`id`), KEY `fk_dept_id` (`deptId`) #CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `tb_dept` (`id`) COMMENT '部门外键设置, 已经注释掉。' ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; INSERT INTO `tb_dept` VALUES ('1', 'RD', '11'); INSERT INTO `tb_dept` VALUES ('2', 'HR', '12'); INSERT INTO `tb_dept` VALUES ('3', 'MK', '13'); INSERT INTO `tb_dept` VALUES ('4', 'MIS', '14'); INSERT INTO `tb_dept` VALUES ('5', 'FD', '15'); INSERT INTO `tb_emp` VALUES ('1', '张三', '1'); INSERT INTO `tb_emp` VALUES ('2', '李四', '1'); INSERT INTO `tb_emp` VALUES ('3', '王二', '1'); INSERT INTO `tb_emp` VALUES ('4', '麻子', '2'); INSERT INTO `tb_emp` VALUES ('5', '小马', '2'); INSERT INTO `tb_emp` VALUES ('6', '马旭', '3'); INSERT INTO `tb_emp` VALUES ('7', '小丁', '4'); INSERT INTO `tb_emp` VALUES ('8', '小西', '51');
tb_emp表数据：
mysql> select * from tb_emp; +----+--------+--------+ | id | name | deptId | +----+--------+--------+ | 1 | 张三 | 1 | | 2 | 李四 | 1 | | 3 | 王二 | 1 | | 4 | 麻子 | 2 | | 5 | 小马 | 2 | | 6 | 马旭 | 3 | | 7 | 小丁 | 4 | | 8 | 小西 | 51 | +----+--------+--------+ 8 rows in set (0.01 sec)
tb_dept表数据：
mysql> select * from tb_dept; +----+----------+--------+ | id | deptName | locAdd | +----+----------+--------+ | 1 | RD | 11 | | 2 | HR | 12 | | 3 | MK | 13 | | 4 | MIS | 14 | | 5 | FD | 15 | +----+----------+--------+ 5 rows in set (0.01 sec)
笛卡儿积：
mysql> select * from tb_emp, tb_dept; +----+--------+--------+----+----------+--------+ | id | name | deptId | id | deptName | locAdd | +----+--------+--------+----+----------+--------+ | 1 | 张三 | 1 | 1 | RD | 11 | | 1 | 张三 | 1 | 2 | HR | 12 | | 1 | 张三 | 1 | 3 | MK | 13 | | 1 | 张三 | 1 | 4 | MIS | 14 | | 1 | 张三 | 1 | 5 | FD | 15 | | 2 | 李四 | 1 | 1 | RD | 11 | | 2 | 李四 | 1 | 2 | HR | 12 | | 2 | 李四 | 1 | 3 | MK | 13 | | 2 | 李四 | 1 | 4 | MIS | 14 | | 2 | 李四 | 1 | 5 | FD | 15 | | 3 | 王二 | 1 | 1 | RD | 11 | | 3 | 王二 | 1 | 2 | HR | 12 | | 3 | 王二 | 1 | 3 | MK | 13 | | 3 | 王二 | 1 | 4 | MIS | 14 | | 3 | 王二 | 1 | 5 | FD | 15 | | 4 | 麻子 | 2 | 1 | RD | 11 | | 4 | 麻子 | 2 | 2 | HR | 12 | | 4 | 麻子 | 2 | 3 | MK | 13 | | 4 | 麻子 | 2 | 4 | MIS | 14 | | 4 | 麻子 | 2 | 5 | FD | 15 | | 5 | 小马 | 2 | 1 | RD | 11 | | 5 | 小马 | 2 | 2 | HR | 12 | | 5 | 小马 | 2 | 3 | MK | 13 | | 5 | 小马 | 2 | 4 | MIS | 14 | | 5 | 小马 | 2 | 5 | FD | 15 | | 6 | 马旭 | 3 | 1 | RD | 11 | | 6 | 马旭 | 3 | 2 | HR | 12 | | 6 | 马旭 | 3 | 3 | MK | 13 | | 6 | 马旭 | 3 | 4 | MIS | 14 | | 6 | 马旭 | 3 | 5 | FD | 15 | | 7 | 小丁 | 4 | 1 | RD | 11 | | 7 | 小丁 | 4 | 2 | HR | 12 | | 7 | 小丁 | 4 | 3 | MK | 13 | | 7 | 小丁 | 4 | 4 | MIS | 14 | | 7 | 小丁 | 4 | 5 | FD | 15 | | 8 | 小西 | 51 | 1 | RD | 11 | | 8 | 小西 | 51 | 2 | HR | 12 | | 8 | 小西 | 51 | 3 | MK | 13 | | 8 | 小西 | 51 | 4 | MIS | 14 | | 8 | 小西 | 51 | 5 | FD | 15 | +----+--------+--------+----+----------+--------+ 40 rows in set (0.00 sec)
查询tb_emp表和tb_dept中公共的数据：
mysql> select * from tb_emp e -> inner join tb_dept d on e.deptId = d.id; +----+--------+--------+----+----------+--------+ | id | name | deptId | id | deptName | locAdd | +----+--------+--------+----+----------+--------+ | 1 | 张三 | 1 | 1 | RD | 11 | | 2 | 李四 | 1 | 1 | RD | 11 | | 3 | 王二 | 1 | 1 | RD | 11 | | 4 | 麻子 | 2 | 2 | HR | 12 | | 5 | 小马 | 2 | 2 | HR | 12 | | 6 | 马旭 | 3 | 3 | MK | 13 | | 7 | 小丁 | 4 | 4 | MIS | 14 | +----+--------+--------+----+----------+--------+ 7 rows in set (0.00 sec)
查询tb_emp表中全部的数据：
mysql> select * from tb_emp e -> left join tb_dept d on e.deptId = d.id; +----+--------+--------+------+----------+--------+ | id | name | deptId | id | deptName | locAdd | +----+--------+--------+------+----------+--------+ | 1 | 张三 | 1 | 1 | RD | 11 | | 2 | 李四 | 1 | 1 | RD | 11 | | 3 | 王二 | 1 | 1 | RD | 11 | | 4 | 麻子 | 2 | 2 | HR | 12 | | 5 | 小马 | 2 | 2 | HR | 12 | | 6 | 马旭 | 3 | 3 | MK | 13 | | 7 | 小丁 | 4 | 4 | MIS | 14 | | 8 | 小西 | 51 | NULL | NULL | NULL | #该条数据为 tb_emp 表中独有的数据, tb_dept 表中的字段用 null 占位 +----+--------+--------+------+----------+--------+ 8 rows in set (0.00 sec)
查询tb_dept表中全部的数据：
mysql> select * from tb_emp e -> right join tb_dept d on e.deptId = d.id; +------+--------+--------+----+----------+--------+ | id | name | deptId | id | deptName | locAdd | +------+--------+--------+----+----------+--------+ | 1 | 张三 | 1 | 1 | RD | 11 | | 2 | 李四 | 1 | 1 | RD | 11 | | 3 | 王二 | 1 | 1 | RD | 11 | | 4 | 麻子 | 2 | 2 | HR | 12 | | 5 | 小马 | 2 | 2 | HR | 12 | | 6 | 马旭 | 3 | 3 | MK | 13 | | 7 | 小丁 | 4 | 4 | MIS | 14 | | NULL | NULL | NULL | 5 | FD | 15 | #该条数据为 tb_dept 表中独有的数据, tb_emp 表中的字段用 null 占位 +------+--------+--------+----+----------+--------+ 8 rows in set (0.01 sec)
查询tb_emp表中独有的数据：
mysql> select * from tb_emp e -> left join tb_dept d on e.deptId = d.id -> where d.id is null; +----+--------+--------+------+----------+--------+ | id | name | deptId | id | deptName | locAdd | +----+--------+--------+------+----------+--------+ | 8 | 小西 | 51 | NULL | NULL | NULL | +----+--------+--------+------+----------+--------+ 1 row in set (0.00 sec)
查询tb_dept表中独有的数据：
mysql> select * from tb_emp e -> right join tb_dept d on e.deptId = d.id -> where e.deptId is null; +------+------+--------+----+----------+--------+ | id | name | deptId | id | deptName | locAdd | +------+------+--------+----+----------+--------+ | NULL | NULL | NULL | 5 | FD | 15 | +------+------+--------+----+----------+--------+ 1 row in set (0.00 sec)
查询tb_emp和tb_dept表中所有的数据：
mysql> select * from tb_emp e -> full outer join tb_dept d on e.deptId = d.id; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'full outer join tb_dept d on e.deptId = d.id' at line 2
出错原因：mysql 中不支持这种连接方式。
解决办法：左连接数据和右连接数据相加，将公共的部分去重。
mysql> select * from tb_emp e -> left join tb_dept d on e.deptId = d.id -> union #去重 -> select * from tb_emp e -> right join tb_dept d on e.deptId = d.id; +------+--------+--------+------+----------+--------+ | id | name | deptId | id | deptName | locAdd | +------+--------+--------+------+----------+--------+ | 1 | 张三 | 1 | 1 | RD | 11 | | 2 | 李四 | 1 | 1 | RD | 11 | | 3 | 王二 | 1 | 1 | RD | 11 | | 4 | 麻子 | 2 | 2 | HR | 12 | | 5 | 小马 | 2 | 2 | HR | 12 | | 6 | 马旭 | 3 | 3 | MK | 13 | | 7 | 小丁 | 4 | 4 | MIS | 14 | | 8 | 小西 | 51 | NULL | NULL | NULL | #该条数据为 tb_emp 表中独有的数据, tb_dept 表中的字段用 null 占位 | NULL | NULL | NULL | 5 | FD | 15 | #该条数据为 tb_dept 表中独有的数据, tb_emp 表中的字段用 null 占位 +------+--------+--------+------+----------+--------+ 9 rows in set (0.00 sec)
查询tb_emp和tb_dept表中独有的数据：
mysql> select * from tb_emp e -> left join tb_dept d on e.deptId = d.id -> where d.id is null -> union -> select * from tb_emp e -> right join tb_dept d on e.deptId = d.id -> where e.deptId is null; +------+--------+--------+------+----------+--------+ | id | name | deptId | id | deptName | locAdd | +------+--------+--------+------+----------+--------+ | 8 | 小西 | 51 | NULL | NULL | NULL | | NULL | NULL | NULL | 5 | FD | 15 | +------+--------+--------+------+----------+--------+ 2 rows in set (0.00 sec)