自学MYSQL 笔记5（基础篇）

子查询

SQL语句中嵌套select语句，称谓嵌套查询，又称子查询

select * from t1 where column1 = ( select column1 from t2);

可分为：标量子查询（子查询结果为单个值），列子查询（子查询结果为一列），行子查询（子查询结果为一行），表子查询（子查询结果为多行多列）

标量子查询

子查询返回的结果是单个值（数字、字符串、日期等）。
常用操作符：-  <  >  >  >=  <  <=

-- 查询销售部所有员工信息
select id from dept where name = '销售部';
select * from emp where dept_id = 4;
select * from emp where dept_id = (select id from dept where name = '销售部');
-- 查询在方东白入职之后的员工信息
select entrydate from emp where name = '方东白';
select * from emp where entrydate > '2009-02-12';
select * from emp where entrydate >  (select entrydate from emp where name = '方东白');

列子查询

返回的结果是一列（可以是多行）

常用操作符：

操作符	描述
IN	在指定的集合范围内，多选一
NOT IN	不在指定的集合范围内
ANY	子查询返回列表中，有任意一个满足即可
SOME	与ANY等同，使用SOME的地方都可以使用ANY
ALL	子查询返回列表的所有值都必须满足

-- 查询销售部和市场部的所有员工信息
select id from dept where name = '销售部' or name = '市场部';
select * from emp where dept_id in (2,4);
select * from emp where dept_id in (select id from dept where name = '销售部' or name = '市场部');
-- 查询比财务部所有人工资都高的员工信息
select id from dept where name = '财务部';
select salary from emp where dept_id = 3;
select salary from emp where dept_id = (select id from dept where name = '财务部');
select * from emp where salary > all (select salary from emp where dept_id = (select id from dept where name = '财务部'));
-- 查询比研发部任一员工工资高的员工信息
select id from dept where name = '研发部';
select salary from emp where dept_id = 1;
select salary from emp where dept_id = (select id from dept where name = '研发部');
select * from emp where salary > any (select salary from emp where dept_id = (select id from dept where name = '研发部'));

行子查询

返回的结果是一行（可以是多列）。
常用操作符：=, <, >, IN, NOT IN

-- 查询与张无忌的薪资及直属领导相同的员工信息
select salary , managerud from emp where name = '张无忌';
select * from emp where salary = 12500 and managerud = 1;
select * from emp where (salary ,managerud) = (12500,1);
select * from emp where (salary ,managerud) = (select salary , managerud from emp where name = '张无忌');

表子查询

返回的结果是多行多列
常用操作符：IN

-- 查询与鹿杖客，宋远桥的职位和薪资相同的员工信息
select job , salary from emp where name = '鹿杖客' or name = '宋远桥';
select * from emp where (job , salary) in (select job , salary from emp where name = '鹿杖客' or name = '宋远桥');
-- 查询入职日期为2006-01-01之后的员工信息及其部门信息
select * from emp where entrydate > '2006-01-01';
select e.* , d.* from (select * from emp where entrydate > '2006-01-01') e left join dept d on e.dept_id = d.id;

数据见自学MYSQL 笔记4（基础篇）

案例

数据：emp表，dept表和以下

create table salgrade(
    grade int,
    losal int,
    hisal int
) comment '薪资等级表';
insert into salgrade values (1,0,3000),(2,3001,5000),(3,5001,8000),(4,8001,10000),
                            (5,10001,15000),(6,15001,20000),(7,20001,25000),(8,25001,30000);

-- 练习
-- 查询员工的姓名，年龄，职位，部门信息（隐式内连接）
select e.name , e.age , e.job , d.name from emp e ,dept d where e.dept_id=d.id;
-- 查询年龄小于30的员工的姓名，年龄，职位，部门信息（显示内连接）
select e.name , e.age , e.job , d.name from emp e inner join dept d on e.dept_id = d.id where e.age < 30;
-- 查询拥有员工的部门ID，部门名称
select distinct d.id , d.name from emp e , dept d where e.dept_id = d.id;
-- 查询所有年龄大于40的员工，及其归属的部门；如果员工没有分配部门，也需要显示出来
select e.* , d.name from  emp e left outer join dept d on e.dept_id = d.id where e.age > 40;
-- 查询所有员工的工资等级
select e.*,s.grade from emp e , salgrade s where e.salary >= s.losal and e.salary <= s.hisal;
select e.*,s.grade from emp e , salgrade s where e.salary between s.losal and s.hisal;
-- 查询研发部所有员工的信息表及工资等级
select e.*, s.grade
from emp e,
     salgrade s,
     dept d
where e.dept_id = d.id
  and (e.salary between s.losal and s.hisal)
  and d.name = '研发部';
-- 查询研发部的平均工资
select avg(e.salary) from emp e , dept d where  e.dept_id = d.id and d.name = '研发部';
-- 查询工资比灭绝高的员工信息
select salary from emp where name = '灭绝';
select * from emp where salary > 8500;
select * from emp where salary > (select salary from emp where name = '灭绝');
-- 查询比平均薪资高的员工信息
select * from emp where salary > (select avg(emp.salary) from emp);
-- 查询低于本部门平均工资的员工信息
select avg(e1.salary) from emp e1 where e1.dept_id = 1;
select avg(e1.salary) from emp e1 where e1.dept_id = 2;
select * from emp e2 where e2.salary < (select avg(e1.salary) from emp e1 where e1.dept_id = e2.dept_id);
select * , (select avg(e1.salary) from emp e1 where e1.dept_id = e2.dept_id) from emp e2 where e2.salary < (select avg(e1.salary) from emp e1 where e1.dept_id = e2.dept_id);
-- 查询所有部门信息，并统计部门员工人数
select id , name from dept;
select count(*) from emp where dept_id = 1;
select d.id ,d.name , (select count(*) from emp e where e.dept_id = d.id) as '人数' from dept d;