连接查询用到的数据表及命令,供参考:
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;