day09:子查询

子查询：出现在其它语句中的select语句，称为子查询或内查询。外部的查询语句称为主查询或外查询

分类：

按子查询出现的位置：

　　　　select 后面（仅仅支持标量子查询）、from后面（支持表子查询）、where或having后面（标量子查询，列子查询，行子查询）、exists后面{相关子查询}（表子查询）

按结果集的行列数不同：

　　　　标量子查询：也称单行子查询（结果集只有一行一列）

　　　　列子查询：也称多行子查询（结果集只有一列多行）

　　　　行子查询（结果集有一行多列、多行多列）

　　　　表子查询（结果集一般为多行多列）

一、where或having后面

1）、标量子查询（单行子查询）

2）、列子查询（多行子查询）

3）、行子查询（多行多列）

特点：

　　a、子查询放在小括号内

　　b、子查询一般放在条件的右侧

　　c、标量子查询，一般搭配着单行操作符使用  >      <      >=      <=    =   <>。列子查询，一般搭配着多行操作符使用   in  、   any/some、  all

　　d、子查询的执行优于主查询执行，主查询的条件用到了子查询的结果

新建：员工表和部门表

#员工表
create table employee(
id int(55),
ename varchar(55),
sex varchar(55),
leaderid int(55),
pay int(55),
departmentid int(55)
);

#部门表
create table department(
id int(55),
dname varchar(55)
);

分别向表中添加数据：

insert into employee(id,ename,sex,leaderid,pay,departmentid) values(101,'张三','男','104',6999,201901);
insert into employee(id,ename,sex,leaderid,pay,departmentid) values(102,'李四','男','104',5999,201901);
insert into employee(id,ename,sex,leaderid,pay,departmentid) values(103,'王五','男','102',4999,201902);
insert into employee(id,ename,sex,leaderid,pay,departmentid) values(104,'赵六','男','101',4999,201903);
insert into employee(id,ename,sex,leaderid,pay,departmentid) values(105,'小强','男','101',3999,201901);
insert into employee(id,ename,sex,leaderid,pay,departmentid) values(106,'老王','男','102',3999,201902);
insert into employee(id,ename,sex,leaderid,pay,departmentid) values(107,'王二','男','101',2999,201903);

insert into department(id,dname) values(201901,'技术部');
insert into department(id,dname) values(201902,'市场部');
insert into department(id,dname) values(201903,'后勤部');

 

  1、标量子查询

查询谁的工资比王二高？

1）、查询王二的工资

select  pay  from  employee  where ename='王二'; 

2）、查询员工的信息，满足pay > (1)结果

select * from employee where pay >
(
select pay from employee where ename='王二'
);

非法使用子查询：就是说子查询的结果不是一行或一列

　2、列子查询（多行子查询）

查询部门名称是技术部或市场部的部门中的所有员工的姓名

1）、查询部门名称是技术部或市场部的部门编号

select  id  from  department  where  dname  in("技术部","市场部");

2)、查询员工姓名，要求部门编号是（1）列中中的某一个

select ename from employee where departmentid in 
(
select id from department where dname in("技术部","市场部")
);

　3、行子查询（结果集一行多列或多行多列）

查询员工编号最低并且工资最高的员工的信息

select * from employee where

id=(select min(id) from employee) and

pay=(select max(pay) from employee);

select  *  from employee  where (id,pay)=(select min(id),max(pay) from employee);

二、select后面

查询每个部门的员工个数

select d.dname,(

select count(*) from employee e where e.departmentid=d.id

) from department d;