创建示例表
部门表
DROP TABLE IF EXISTS `department`;
CREATE TABLE `department` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`deptName` varchar(30) DEFAULT NULL,
`address` varchar(40) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO `department` VALUES ('1', '研发部(RD)', '2层');
INSERT INTO `department` VALUES ('2', '人事部(HR)', '3层');
INSERT INTO `department` VALUES ('3', '市场部(MK)', '4层');
INSERT INTO `department` VALUES ('4', '后勤部(MIS)', '5层');
INSERT INTO `department` VALUES ('5', '财务部(FD)', '6层');
员工表
DROP TABLE IF EXISTS `employee`;
CREATE TABLE `employee` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`dep_id` int(11) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`salary` decimal(10,2) DEFAULT NULL,
`cus_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO `employee` VALUES ('1', '鲁班', '1', '10', '1000.00', '1');
INSERT INTO `employee` VALUES ('2', '后裔', '1', '20', '2000.00', '1');
INSERT INTO `employee` VALUES ('3', '孙尚香', '1', '20', '2500.00', '1');
INSERT INTO `employee` VALUES ('4', '凯', '4', '20', '3000.00', '1');
INSERT INTO `employee` VALUES ('5', '典韦', '4', '40', '3500.00', '2');
INSERT INTO `employee` VALUES ('6', '貂蝉', '6', '20', '5000.00', '1');
INSERT INTO `employee` VALUES ('7', '孙膑', '6', '50', '5000.00', '1');
INSERT INTO `employee` VALUES ('8', '蔡文姬', '30', '35', '4000.00', '1');
应用示例表
内连接
- 作用：查询两张表的共有部分的数据。
编写语句写法
SELECT <select_list> FROM tableA A INNER JOIN tableB B ON A.Key = B.Key;
使用示例如下所示
SELECT * FROM employee e INNER JOIN department d ON e.dep_id = d.id;
左连接
- 作用：把左边表的内容全部查出，右边表只查出满足条件的记录。
编写语句写法
SELECT <select_list> FROM tableA A LEFT JOIN tableB B ON A.Key = B.Key;
使用示例如下所示
SELECT * FROM employee e LEFT JOIN department d on e.dep_id = d.id;
右连接
- 作用：把右边表的内容全部查出，左边表只查出满足条件的记录。
编写语句写法
SELECT <select_list> FROM tableA A RIGHT JOIN tableB B ON A.Key = B.Key;
使用示例如下所示
SELECT * FROM employee e RIGHT JOIN department d on e.dep_id = d.id;
查询左表独有数据
- 作用：查询 A 表的独有数据。
编写语句写法
SELECT <select_list> FROM tableA A LEFT JOIN tableB B ON A.Key = B.Key WHERE B.key IS NULL;
使用示例如下所示
SELECT * FROM employee e LEFT JOIN department d ON e.dep_id = d.id WHERE d.id IS NULL;
查询右表独有数据
- 作用：查询 B 表的独有数据。
编写语句写法
SELECT <select_list> FROM tableA A RIGHT JOIN tableB B ON A.Key = B.Key WHERE A.key IS NULL;
使用示例如下所示
SELECT * FROM employee e RIGHT JOIN department d on e.dep_id = d.id WHERE e.id IS NULL;
全连接
- 作用：查询两个表的全部信息。
编写语句写法
SELECT <select_list> FROM tableA A FULL OUTTER JOIN tableB B ON A.Key = B.Key;
注：如上语法，MySQL 默认不支持此写法，Oracle 支持，使用其它全连接写法，使用示例如下所示
SELECT * FROM employee e LEFT JOIN department d ON e.dep_id = d.id
UNION
SELECT * FROM employee e RIGHT JOIN department d ON e.dep_id = d.id;
查询左右表各自独有的数据
- 作用：查询 A 和 B 表各自独有的数据。
编写语句写法
SELECT <select_list> FROM tableA A FULL OUTTER JOIN tableB B ON A.Key = B.Key WHERE A.key = NULL OR B.key= NULL;
使用示例如下所示
SELECT * FROM employee e LEFT JOIN department d ON e.dep_id = d.id WHERE d.id IS NULL
UNION
SELECT * FROM employee e RIGHT JOIN department d ON e.dep_id = d.id WHERE e.dep_id IS NULL;
