26 SQL ——列子查询

create table dept
  (
  id int primary key auto_increment,
  name  varchar(15)
  )
 comment '部门';

insert into dept(id, name) values (1,'研发部'),
                                  (2,'市场部'),
                                  (3,'财务部'),
                                  (4,'销售部'),
                                  (5,'总经办'),
                                  (6,'人事部');

create table  staff
(
 id int			primary key  auto_increment	comment'ID',
  name    varchar(15)       comment'姓名',
  age        int            comment'年龄',
  job    varchar(15)        comment  '工作',
  salary int                comment   '薪资',
  wokeday  date              comment    '工作时间',
 mange       int            comment   '直属领导Id',
 dept_id    int             comment   '部门Id'
)comment '员工';

 insert into  staff ( id,name,age,job,salary,wokeday,mange,dept_id)values(1,'金庸',66,'总裁',3000,'2000-1-1',null,5),
														            	(2,'张无忌',34,'项目经理',3000,'2013-1-1', 1,1),
															            (3,'杨逍',13,'开发',     3000,'2021-1-1',   2,1),
															            (4,'纬一路',45,'开发',3450,'2024-1-2',      2,1),
															            (5,'常遇春',34,'开发',3020,    '2016-1-1',  3,1),
															            (6,'小赵',23,'程序员鼓励师',3560,'2015-1-1', 2,1),
															            (7,'灭绝',26,'财务总监',3780,'2013-1-1',     1,3),
														            	(8,'抽纸若',47,'会计',3230,'2023-1-1',       7,3),
															            (9,'金马',38,'出纳',3780,'2012-1-1',         7,3),
															            (10,'谢逊',56,'市场部总监',3034,'2010-1-1',   1,2),
															            (11,'大师',59,'职员',3550,'2009-1-1',       10,2),
															            (12,'白毛',50,'职员',3890,'2008-1-1',       10,2),
															            (13,'红爪',27,'职员',3220,'2007-1-1',       10,2),
															            (14,'龙珠',34,'销售总监',4000,'2001-1-1',     1,4),
                                                                        (15,'宋远桥',45,'销售总监',5000,'2002-1-1',   14,4),
                                                                        (16,'天王例',89,'销售总监',8900,'2003-1-1',   14,4),
                                                                        (17,'张三丰',53,'销售总监',6700,'2004-1-1',  1,null);


alter table  staff add constraint fk_staff_dept_id foreign key  (dept_id) references dept(id) ;-- 建立外键链接

-- 列子查询(结果为一列多行)
-- 1.查询"销售部"和"市场部"的所有员工信息'
-- a.查询“销售部”和"市场部”的部门ID
select id from dept where name='销售部' or  name='市场部';
-- b.根据部门ID，查询员工信息
select * from staff where dept_id in (2,4);
select * from staff where dept_id in (select id from dept where name='销售部' or  name='市场部');


-- 2.查询比财务部所有人工资都高的员工信息（满足多个条件即可）
-- a  查询财务部所有人工资
select  id from dept where name='财务部';
select  salary from staff where dept_id=3;

select  salary from staff where dept_id=(select  id from dept where name='财务部');

select * from staff where  salary>all (select  salary from staff where dept_id=(select  id from dept where name='财务部'));


-- 3.查询比研发部其中任意一人工资高的员工信息
-- a  查询研发部所有人工资
select  id from dept where name='研发部';
select  salary from staff where dept_id=1;
select  salary from staff where dept_id=(select  id from dept where name='研发部');

-- b.比研发部其中任意一人工资高的员工信息(满足一个条件即可)
select * from  staff where  salary>any (select  salary from staff where dept_id=(select  id from dept where name='研发部'));