8.4 连接查询---参考黑马程序员

 连接查询用到的数据表及命令，供参考：

CREATE  DATABASE  db_307;

USE db_307;

-----部门表----
create table dept(
    id   INT  AUTO_INCREMENT   PRIMARY KEY,
    name  VARCHAR(50) NOT  NULL 
);
----插入部门信息---
INSERT INTO  dept  VALUES
(1, '研发部'), 
(2, '市场部'),
(3, '财务部'), 
(4, '销售部'), 
(5, '总经办'),
(6, '人事部');

--------员工表------

CREATE TABLE emp(
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) not null ,
age INT,
job VARCHAR(20) ,
salary INT ,
entrydate DATE,
managerid INT ,
dept_id INT 
);
----插入员工信息---
INSERT INTO emp  VALUES
(1, '金庸', 66, '总裁',20000, '2000-01-01', null,5),
(2, '张无忌', 20, '项目经理',12500, '2005-12-05', 1,1),
(3, '杨逍', 33, '开发', 8400,'2000-11-03', 2,1),
(4, '韦一笑', 48, '开发',11000, '2002-02-05', 2,1),
(5, '常遇春', 43, '开发',10500, '2004-09-07', 3,1),
(6, '小昭', 19, '程序员鼓励师',6600, '2004-10-12', 2,1),
(7, '灭绝', 60, '财务总监',8500, '2002-09-12', 1,3),
(8, '周芷若', 19, '会计',48000, '2006-06-02', 7,3),
(9, '丁敏君', 23, '出纳',5250, '2009-05-13', 7,3),
(10, '赵敏', 20, '市场部总监',12500, '2004-10-12', 1,2),
(11, '鹿杖客', 56, '职员',3750, '2006-10-03', 10,null),
(12, '鹤笔翁', 19, '职员',3750, '2007-05-09', 10,null),
(13, '方东白', 19, '职员',5500, '2009-02-12', null,null),
(14, '张三丰', 88, '销售总监',14000, '2004-10-12', 1,null),
(15, '俞莲舟', 38, '销售',4600, '2004-10-12', 14,null),
(16, '宋远桥', 40, '销售',4600, '2004-10-12', null,null),
(17, '陈友谅', 42, null,2000, '2011-10-12', null,null);

-----内连接查询----
 SELECT emp.name , dept.name  FROM  emp , dept  WHERE  emp.dept_id = dept.id;

SELECT e.name , d.name  FROM  emp  e  , dept  d  WHERE  e.dept_id = d.id;

SELECT e.name , d.name  FROM  emp  e  , dept  d  WHERE  emp.dept_id = dept.id;

-------外连接查询-----
SELECT e.* , d.name  FROM  emp e  LEFT  OUTER  JOIN  dept  d  ON  e.dept_id = d.id;

-------自连接-----
SELECT a.name , b.name  FROM  emp  a    JOIN  emp  b  ON  a.managerid = b.id; 

SELECT a.name  AS  '员工'  ,  b.name  AS  '领导'  FROM  emp  a    JOIN  emp  b  ON  a.managerid = b.id; 

 SELECT a.name AS '员工', b.name  AS '领导'  FROM  emp  a   LEFT  JOIN  emp  b  ON  a.managerid = b.id;