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;CREATETABLE 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.idCREATE 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;
