8.3 多表查询概述---参考黑马程序员

用到的数据表与命令：

------部门表---
CREATE TABLE dept(
id INT AUTO_INCREMENT  PRIMARY KEY,
name VARCHAR(50) not null 
);

INSERT INTO dept (id, name) 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,2),
(12, '鹤笔翁', 19, '职员',3750, '2007-05-09', 10,2),
(13, '方东白', 19, '职员',5500, '2009-02-12', 10,2),
(14, '张三丰', 88, '销售总监',14000, '2004-10-12', 1,4),
(15, '俞莲舟', 38, '销售',4600, '2004-10-12', 14,4),
(16, '宋远桥', 40, '销售',4600, '2004-10-12', 14,4),
(17, '陈友谅', 42, null,2000, '2011-10-12', 1,null);

-- ---添加外键---

ALTER TABLE emp ADD CONSTRAINT fk_emp_dept_id FOREIGN KEY (dept_id) REFERENCES
dept(id)  ON UPDATE  CASCADE  ON DELETE  CASCADE;

----单表查询---
SELECT  * FROM  emp;
SELECT  *FROM dept;

-----多表查询----

SELECT * FROM emp,dept;