create database mydb1;
use mydb1;
alter database mydb1 character set utf8mb4;
-- 创建部门表
create table if not exists dept(
  deptno varchar(20) primary key ,  -- 部门号
  name varchar(20) -- 部门名字
);

-- 创建员工表
create table if not exists emp(
  eid varchar(20) primary key , -- 员工编号
  ename varchar(20), -- 员工名字
  age int,  -- 员工年龄
  dept_id varchar(20)  -- 员工所属部门
);
insert into dept values('1001','研发部');
insert into dept values('1002','销售部');
insert into dept values('1003','财务部');
insert into dept values('1004','人事部');

insert into emp values('1','乔峰',20, '1001');
insert into emp values('2','段誉',21, '1001');
insert into emp values('3','虚竹',23, '1001');
insert into emp values('4','阿紫',18, '1001');
insert into emp values('5','扫地僧',85, '1002');
insert into emp values('6','李秋水',33, '1002');
insert into emp values('7','鸠摩智',50, '1002'); 
insert into emp values('8','天山童姥',60, '1003');
insert into emp values('9','慕容博',58, '1003');
insert into emp values('10','丁春秋',71, '1005');

-- 交叉连接查询(会产生m*n条数据）
-- 查询每个部门的所属员工
-- 查询研发部和销售部的所属员工
-- 查询每个部门的员工数,并升序排序  1)分组  2)count数量  3)排序
-- 查询人数大于等于3的部门，并按照人数降序排序  1)分组 2)数人数 3)大于3人的部门 4)降序


-- 查询研发部20岁以下的员工信息,包括员工号、员工名字，年龄，部门名字
select deptno from dept where name='研发部';

-- 子查询
select eid,ename,age,deptno,name from emp,dept where dept.deptno=emp.dept_id and dept_id=(select deptno from dept where name='研发部'
) and age<20;

-- 查询年龄大于1003部门所有年龄的员工信息
select * from emp where age>(select max(age) from emp where dept_id='1003');
select * from emp where age>all(select age from emp where dept_id='1003');

select deptno from dept;
select * from emp where dept_id !=all(select deptno from dept);

select * from emp where dept_id not in(select deptno from dept);


-- any some
select * from emp where age>(select min(age) from emp where dept_id='1003');

select * from emp where age>any(select age from emp where dept_id='1003');
select * from emp where age>some(select age from emp where dept_id='1003');


-- in 判断某只是否在集合里
select deptno from dept where name in('研发部','销售部');

select * from emp,dept where dept.deptno=emp.dept_id and name in('研发部','销售部');

select * from emp where dept_id in(select deptno from dept where name in('研发部','销售部'));

select * from emp where dept_id='1003'
-- 查询大于60岁 有则输出 
select * from emp a where exists(select * from emp where a.age>60)

select * from emp a where exists(select * from dept b where a.dept_id = b.deptno);