mysql 多表连接的

 多表连接查询的练习

创建所需要的表

-- 创建部门表
CREATE TABLE dept (
    deptno INT PRIMARY KEY,
    dname VARCHAR(15),
    loc VARCHAR(50)
);


-- 导入部门表信息
-- (10,'ACCOUNTING','NEW YORK');
-- (20,'RESEARCH','DALLAS');
-- (30,'SALES','CHICAGO');
-- (40,'OPERATIONS','BOSTON');
INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO dept VALUES (20,'RESEARCH','DALLAS');
INSERT INTO dept VALUES (30,'SALES','CHICAGO');
INSERT INTO dept VALUES (40,'OPERATIONS','BOSTON');

SELECT * FROM dept;

-- 创建工资等级表
CREATE TABLE salgrade (
    grade INT,
    losal INT,
    hisal INT
);

-- 导入工资等级信息
-- (1,700,1200);
-- (2,1201,1400);
-- (3,1401,2000);
-- (4,2001,3000);
-- (5,3001,9999);
INSERT INTO salgrade VALUES (1,700,1200);
INSERT INTO salgrade VALUES (2,1201,1400);
INSERT INTO salgrade VALUES (3,1401,2000);
INSERT INTO salgrade VALUES (4,2001,3000);
INSERT INTO salgrade VALUES (5,3001,9999);

SELECT * FROM salgrade;

　　-- 1.查询各部门经理的详细信息

-- 1.查询各部门经理的详细信息

-- 方法一：笛卡尔积

select  ename , empno,job ,sal
from  emp1,dept
where emp1.deptno = dept.deptno and ename = 'manager';

-- 方法二：交叉连接

select  ename ,empno , job ,sal
from emp1  cross join  dept  on  emp1.deptno = dept.deptno  and  ename = 'manager';


-- 方法三：内连接
select ename ,empno , job , sal
from emp1 inner join dept on emp1.deptno = dept.deptno  and  ename = 'manager';

-- 2.查询每位员工的工号、姓名、职位、应发工资（sal+comm）和工资等级

select  empno, ename ,job , sal+comm , grade 
from emp1 , salgrade
where  sal +comm between losal and hisal;

# 左连接

select empno ,ename , job ,sal+comm,grade
from emp1 left join salgrade on sal+comm between losal and hisal;


-- 3.查询所有管理者姓名及其下属员工姓名（自连接：通过别名，将同一张表视为多张表）

select  e2.name,e1.name
from emp1  as e1 inner join emp1 as e2 on mgr = e2.empno;

-- 4.查询各部门平均工资和员工数:部门名称，平均工资和员工数

select avg(sal),count(empno),dname
from dept left join emp1 on dept,feptno = emp1.deptno
group by dept.deptno;


-- 5.查询各地区员工的平均工龄


select loc,(datediff(now(),hiredate)/365)  平均工龄
from  dept  left join emp1 on dept.deptno= emp1.deptno
group by loc;

　　子查询

-- 标量子查询：
-- 查询f_price大于均价的水果信息

select * from fruit having f_price > avg (f_price);

-- 行子查询
-- 查询和f_price大于20的水果名称相同且被同一客户购买过的的水果信息
# 1、先查询价格大于20的水果有哪些客户买过

select  f_name , s_name
from fruit
where f_price >20;

#2、查询和第一步中计算的到的信息相同的水果

select f_name
from fruit
where (f_name,s_name) in
(select f_name,s_name
from fruit
where f_price >20);


-- 列子查询：
-- 查询购买过f_price小于10的客户购买过的水果信息

#1、先查询购买过价格小于10的水果的客户有哪些

select s_name
from fruit
where f_price <10
group by s_name;    

或者
select distinct s_name    #distinct 去除重复值
from fruit
where f_price<10;


#2、在查询这些客户购买过那些水果

select  *
from fruit
where  s_name in (
select  distinct s_name
from fruit
where f_price <10);


-- 查询f_price大于任意f_price在10到20之间的水果信息

# 1、查询出价格在10到20之间的水果有哪些   # 查询什么在什么之中的时候用  any

select f_price
from fruit 
where f_price between 10 and 20;


#2、价格大于这些水果的水果信息

select *
from fruit 
where f_price any (
select f_price
from fruit 
where f_price between 10 and 20
);


-- 查询f_price大于所有f_price在10到20之间的水果信息    全部大于用all
#1、先查询价格在10到20时间的水果价格
select * from fruit where f_price between 10 and 20;

#2、查询大于上面值的最大值

select *
from fruit
where f_price >all(select f_price from fruit where f_price between 10 and 20);


-- exists子查询  ，exists  后面的结构返回（true  ，false）无实际意义
# 查询是否有大于20的，有则查询全部，否则截至查询
select * 
from  fruit
where exists (select * from fruit where f_price > 30);

#注意；  当有大于30的值时执行查询全部   否则输出空




-- 表子查询
-- 查询在购买了f_price小于20的水果的客户中购买过三种水果的客户

select s_name ,count(f_name)
from fruit
where  s_name  in(
select  ditinct s_name
from fruit
where f_price<20
)
group by s_name
having  count(f_name) = 3;


-- 子查询练习
-- 1.查询工资高于所有员工平均工资的员工信息

#1、查询所有员工的平均工资
 
#所有员工平均工资
select avg(sal) from emp1;

select *
from emp1
where sal>(select avg(sal) from emp1);

-- 2.查询和smith同部门同领导的员工信息

select deptno,mgr
from emp1
where ename = 'smith';

select *
from emp1
where (deptno,mgr) =
	(
	select deptno,mgr
	from emp1
	where ename = 'smith');


-- 3.查询所有上层管理者的详细信息
select distinct mgr from emp1;

select *
from emp1
where empno in (select distinct mgr from emp1);
-- 4.工资大于同职位的平均工资的员工信息
select avg(sal) 平均工资,job from emp1 group by job;

select *
from emp1  left join(select avg(sal) 平均工资,job from emp1 group by job) as e on emp1.job=e.job
where emp1.sal > e.平均工资;

-- 5.查询上层管理者的职位



-- 6.查询工资等级处于第4的员工姓名及工资

-- 7.每个部门薪水最高的员工信息
select max(sal) 最高,deptno from emp1 group by deptno;

select *
from emp1 left join (select max(sal) 最高,deptno from emp1 group by deptno) a
on emp1.deptno = a.deptno
where emp1.sal = a.最高;

-- 8.工资等级处于第四的员工姓名及工资
select ename,sal,grade
from emp1,salgrade
where sal between losal and hisal and grade = 4;

select ename ,sal
from emp1,(select losal,hisal from salgrade where grade=4) a
where sal between losal and hisal;