day:11 sql之子查询

一、建表语句：

已知2张基本表：
部门表：dept （部门号，部门名称）;
员工表 emp（员工号，员工姓名，年龄，入职时间，收入，部门号）

CREATE table dept(dept1 VARCHAR(6),dept_name VARCHAR(20)) default charset=utf8;
INSERT into dept VALUES ('101','财务');
INSERT into dept VALUES ('102','销售');
INSERT into dept VALUES ('103','IT技术');
INSERT into dept VALUES ('104','行政');

CREATE table emp (sid VARCHAR(6),name VARCHAR(20),age TINYINT(2),woektime_start VARCHAR(10),incoming SMALLINT(10),dept2 VARCHAR(6))default charset=utf8;
insert into emp VALUES ('1789','张三',35,'1980/1/1',4000,'101');
insert into emp VALUES ('1674','李四',32,'1983/4/1',3500,'101');
insert into emp VALUES ('1776','王五',24,'1990/7/1',2000,'101');
insert into emp VALUES ('1568','赵六',57,'1970/10/11',7500,'102');
insert into emp VALUES ('1564','荣七',64,'1963/10/11',8500,'102');
insert into emp VALUES ('1879','牛八',55,'1971/10/20',7300,'103');
insert into emp VALUES ('1880','老九',55,'1971/10/20',8000,'105');

drop table dept ;

drop table emp ;

select * from dept;

select * from emp ;

二、数据库 图片

dept表

emp表

(一)子查询定义：一个查询中嵌套另一个查询
（二）子查询的分类
（1）标量子查询
（2）列子查询
（3）行子查询
（4）表子查询（运用多）

三、子查询详解

（1）标量子查询（返回一个值）
标量子查询：把一个sql 执行返回的一个值，作为另一个sql的条件，得到的结果是一行一列，一般出现在where之后，
备注：标量子查询允许使用的比较运算符号：

=，！=，>,<,>=,<=，<>

案例：2.财务部门的收入总和；
步骤1：查询财务部门的编号
select dept1 from dept where dept_name='财务';
步骤2：将查询出来的一行一列作为条件，
select sum(incoming) from emp where dept2=(select dept1 from dept where dept_name='财务' );

注意点：判断能不能使用子查询，查看结果是否涉及两个表的字段
如：不能用子查询

可以用子查询

select dept_name from dept where dept1=(

select dept2 from emp where name="张三");

** 2、列子查询（返回的结果是一个列）**
定义：返回的是一列值
注意点：通常在where 之后使用，使用是in 或not in ，不运行使用比较运算符，因为它有多个值
案例：3.It技术部和财务部门入职员工的员工号、
步骤1：
SELECT dept1 from dept where dept_name='财务' or dept_name='销售';

步骤2：SELECT sid from emp where dept2 in (SELECT dept1 from dept where dept_name='财务' or dept_name='销售')

报错：

改成in

3、行子查询
定义：返回的结果是一行多列，一般出现在where 的后面
案例：找出与牛八 年龄和入职时间一样的员工姓名?

步骤1：找老九的多年龄 和入职时间 ：select age,woektime_start from emp where name="老九" ;

步骤2：在emp中找到年里和入职时间相同的数据；
select name from emp where (age,woektime_start) in (select age,woektime_start from emp where name="牛八" ) ;

4、表子查询（重点）
定义：返回的是多行多表 （返回的就是一个表），一般接在from 的后面，返回的是一个表

临时表：as 临时表名
步骤：
（1）select * from （ 合表） where 条件
案例：
案例“select a.name from (
select * from dept INNER JOIN emp on dept.dept1=emp.dept2
) as a WHERE a.dept_name="财务"

案例：
select name ,dept_name from dept INNER JOIN emp on dept.dept1=emp.dept2
where(dept2,age) in (select dept2,max(age) from emp group by dept2)

案例：财务部门的收入总和；
SELECT sum(s.incoming) from (
方法：select * from dept INNER JOIN emp on dept.dept1=emp.dept2 ) s where s.dept_name='财务'

（2）select * from 表1 inner join （ ）
案例：.It技术部和财务部门入职员工的员工号
方法：select sid from emp INNER JOIN (select * from dept where dept_name='财务' or dept_name='IT技术' )c ON emp.dept2=c.dept1