联表查询介绍

随时随地阅读更多技术实战干货，获取项目源码、学习资料，请关注源代码社区公众号(ydmsq666)

CREATE TABLE t_employee(
id INT PRIMARY KEY AUTO_INCREMENT,
eName VARCHAR(20),
job VARCHAR(20),
deptid INT,
CONSTRAINT fk_deptid FOREIGN KEY(deptid) REFERENCES t_dept(id)
)DEFAULT CHARSET=utf8;
CREATE	TABLE t_dept(
id INT PRIMARY KEY AUTO_INCREMENT,
dName VARCHAR(20)
)DEFAULT CHARSET=utf8;

INSERT INTO t_dept(dName) VALUES('市场部');
INSERT INTO t_dept(dName) VALUES('财务部');
INSERT INTO t_dept(dName) VALUES('技术部');
INSERT INTO t_dept(dName) VALUES('拓展部');
INSERT INTO t_employee(eName,job,deptid) VALUES('张天','经理',1);
INSERT INTO t_employee(eName,job,deptid) VALUES('李典','职员',1);
INSERT INTO t_employee(eName,job,deptid) VALUES('王双','职员',1);
INSERT INTO t_employee(eName,job,deptid) VALUES('典韦','职员',1);

INSERT INTO t_employee(eName,job,deptid) VALUES('李渊','经理',2);
INSERT INTO t_employee(eName,job,deptid) VALUES('何元庆','经理',2);
INSERT INTO t_employee(eName,job,deptid) VALUES('秦琼','职员',2);
INSERT INTO t_employee(eName,job,deptid) VALUES('尉迟恭','职员',2);

INSERT INTO t_employee(eName,job,deptid) VALUES('赵匡胤','经理',3);
INSERT INTO t_employee(eName,job,deptid) VALUES('石守信','经理',3);
INSERT INTO t_employee(eName,job,deptid) VALUES('赵光义','职员',3);
INSERT INTO t_employee(eName,job,deptid) VALUES('杨继业','职员',3);
INSERT INTO t_employee(eName,job,deptid) VALUES('八贤王','职员',3);
INSERT INTO t_employee(eName,job) VALUES('陈近南','职员');

SELECT * FROM t_employee;
SELECT * FROM t_dept;
-- 内联接：是指使用比较运算符根据每个表共有的列的值匹配两个表中的行
-- 查询员工表的所有信息，同时将部门id 替换为部门名称
SELECT e.id,eName,e.job ,d.dName FROM t_employee e JOIN t_dept d ON e.deptid=d.id

-- 左外联接：包含LEFT JOIN前面一个表的所有记录
SELECT e.id,eName,e.job ,d.dName FROM t_employee e LEFT JOIN t_dept d ON e.deptid=d.id

-- 右外联接：包含LEFT JOIN后面一个表的所有记录
SELECT e.id,eName,e.job ,d.dName FROM t_employee e RIGHT JOIN t_dept d ON e.deptid=d.id

CREATE TABLE t_em(
id INT PRIMARY KEY AUTO_INCREMENT,
emName VARCHAR(20),
headId INT
)DEFAULT CHARSET=utf8;
INSERT INTO t_em(emName,headId)VALUES('boss',NULL);
INSERT INTO t_em(emName,headId)VALUES('张三',1);
INSERT INTO t_em(emName,headId)VALUES('李四',1);
INSERT INTO t_em(emName,headId)VALUES('王五',2);
INSERT INTO t_em(emName,headId)VALUES('赵六',3);
SELECT * FROM  t_em
-- 自联接，表和本身表做联接查询
SELECT e1.emName,e2.emName FROM t_em e1 LEFT JOIN t_em e2 ON e1.headId=e2.id;