关联查询

数据库sql

/*Table structure for table `dept` */

DROP TABLE IF EXISTS `dept`;

CREATE TABLE `dept` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `deptName` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

/*Data for the table `dept` */

insert  into `dept`(`id`,`deptName`) values (1,'开发部'),(2,'市场部'),(3,'财务部'),(4,'运维部'),(5,'总经办');

/*Table structure for table `employee` */

DROP TABLE IF EXISTS `employee`;

CREATE TABLE `employee` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `empName` varchar(20) NOT NULL,
  `deptId` int(11) DEFAULT NULL,
  `bossId` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `deptId` (`deptId`),
  KEY `bossId` (`bossId`),
  CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`deptId`) REFERENCES `dept` (`id`),
  CONSTRAINT `employee_ibfk_2` FOREIGN KEY (`bossId`) REFERENCES `employee` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

/*Data for the table `employee` */

insert  into `employee`(`id`,`empName`,`deptId`,`bossId`) values (1,'张三',4,NULL),(2,'李四',1,1),(3,'王五',3,2),(4,'赵六',2,3),(5,'田七',1,4),(6,'王八',3,5),(7,'李九',NULL,6);

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

查询语句

-- 笛卡尔积（一般不使用）
SELECT empName, deptName FROM employee,dept 

-- 内连接查询
SELECT empName, deptName FROM employee e,dept d  
WHERE e.deptId = d.id

-- 内连接 写法二
SELECT empName, deptName FROM employee e INNER JOIN dept d 
ON e.deptId = d.id

-- 左外连接 //查询每个部门的员工
SELECT deptName, empName FROM dept d LEFT JOIN employee e 
ON d.id =  e.deptId

-- 右外连接 //查询每个部门的员工
SELECT empName, deptName FROM dept d RIGHT JOIN employee e 
ON d.id = e.deptId

-- 全连接  //MySQL数据库不支持全连接 但是可以用union关键字将左外连接和右---- 外连接的结果集合并得到全连接
SELECT deptName, empName FROM dept d LEFT JOIN employee e 
ON d.id = e.deptId
UNION 
SELECT empName, deptName FROM dept d RIGHT JOIN employee e 
ON d.id = e.deptId

-- 内连接（自连接） //查询员工及其上司
SELECT e1.empName,e2.empName 
FROM employee e1 LEFT JOIN employee e2 
ON e1.bossId = e2.id