8.5 子查询-----参考黑马程序员

 

 子查询用到的数据，供参考：
 

-----部门表----
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,'程序员鼓励师',5500,'2004-10-12',2,1),
(7,'灭绝',60,'财务总监',8500,'2002-09-12',1,3),
(8,'周芷若',19,'会计',48000,'2006-06-02',7,3),
(9,'丁敏君',23,'出纳',5800,'2009-05-13',7,3),
(10,'赵敏',20,'市场部总监',12500,'2004-10-12',1,2),
(11,'鹿杖客',56,'职员',6000,'2006-10-03',10,2),
(12,'鹤笔翁',19,'职员',6000,'2004-10-12',10,2),
(13,'方东白',19,'职员',6000,'2009-02-12',10,2),
(14,'张三丰',88,'销售总监',14000,'2004-10-12',1,4),
(15,'俞莲舟',38,'销售',5500,'2004-10-12',14,4),
(16,'宋远桥',40,'销售',5500,'2004-10-12',14,4),
(17,'陈友谅',42,NULL,5000,'2011-10-12',NULL,NULL);

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

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