25 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	commentID,
  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=销售部;
-- b 根据销售部部门ID，查询员工信息
select  *  from  staff where staff. dept_id=4;
select  *  from  staff where staff. dept_id=(select id  from  dept where name=销售部);

--  案例2.查询在"张三丰”入职之后的员工信息
-- a.查询在"张三丰”入职日期
select  wokeday from staff where name=张三丰;
-- b查询在"张三丰”入职之后的员工信息
select * from staff where wokeday>(select  wokeday from staff where name=张三丰);

案例1

案例2