MYSQL_DQL语言的学习（3）

DQL（数据查询）语言的学习（3）

注意：以上为employees表、departments表、locations表、job_grades表、jobs表*

目录

1. 子查询
   1.1 where或having后面
   1.2 select后面
   1.3 from后面
   1.4 exists后面

1、子查询

含义：出现在其他语句中的select语句，称为子查询或内查询。
注意：外面的语句可以是insert、update、delete、select等，一般select作为外面语句较多。
外面如果为select语句，则此语句称为外查询或主查询

分类：
1、按子查询出现的位置：
select后面：
仅仅支持标量子查询
from后面：
支持表子查询
where或having后面 ☆
标量子查询
列子查询
行子查询
exists后面（相关子查询）
表子查询

2、按结果集的行列数不同：
标量子查询（结果集只有一行一列）
列子查询（结果集只有一列多行）
行子查询（结果集有一行多列）
表子查询（结果集一般为多行多列）

**

一、where或having后面

**
1、 标量子查询（单行子查询）
2、 列子查询（多行子查询）
3、 行子查询（多列多行）
特点：
（1） 子查询放在小括号内
（2） 子查询一般放在条件的右侧
（3） 标量子查询，一般搭配着单行操作符使用【< > = <= >= <>】
列子查询，一般搭配着多行操作符使用
in，any/some,all
（4）子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果

#标量子查询
案例1：谁的工资比Abel高
1、查询Abel的工资

select salary from employees where last_name='Abel';

2、查询员工的信息，满足salary>1结果

select * from employees where salary>(select salary from employees where last_name='Abel');

案例2：返回job_id与141号员工相同，salary比143号员工多的员工 姓名，job_id和工资
1、查询141号员工的job_id

select job_id from employees where employees_id=141;

2、查询143号员工的salary

select salary from employees where employees_id=143;

3、查询员工的姓名，job_id和工资，要求job_id=1并且salary>2

select name,job_id,salary 
from employees 
where salary>( select salary from employees where employees_id=143) 
and job_id=( select job_id from employees where employees_id=141);

案例3：返回公司工资最少的员工的last_name,job_id和salary
1、查询公司的最低工资

select min(salary) from employees;

2、查询last_name,job_id和salary，要求salary=1

select last_name,job_id,salary 
from employees 
where salary=（select min(salary) from employees）;

案例4：查询最低工资大于50号部门最低工资的部门id和其最低工资
1、查询50号部门的最低工资

select min(salary) from employees where department_id=50;

2、 查询每个部门的最低工资

select min(salary),department_id 
from employees 
group by department_id;

3、在2基础上筛选，满足min(salary)>1

select min(salary),department_id 
from employees 
group by department_id 
having min(salary)>（select min(salary) from employees where department_id=50）;

注意：非法使用标量子查询的情况
1、 子查询的结果为多个结果，不是一个结果
2、 子查询的筛选条件有误

#列子查询（多行子查询）
操作符 含义
IN/NOT_IN ： 等于列表中的任意一个
ANY|SOME ： 和子查询返回的某一个值比较
ALL： 和子查询返回的所有值比较

案例1：返回location_id是1400或1700的部门中的所有员工姓名
1、查询location_id是1400或1700的部门编号

select distinct department_id 
from employees 
where location_id in（1400,1700）;

2、查询所有员工姓名，部门号是1

select name 
from employees 
where department_id in( select department_id from employees where location_id in(1400,1700));

案例2：返回其他工种中比job_id为’IT_PROG’部门任一工资低的员工的员工号、姓名、job_id以及salary
1、查询job_i为为’IT_PROG’的工资

select distinct salary from employees where job_id='IT_PROG';

2、 查询每个部门的工资比1小的信息

select employee_id,last_name,job_id,salary 
from employees 
where salary < any(select distinct salary from employees where job_id='IT_PROG') 
and job _id <>'IT_PROG';

或

select employee_id,last_name,job_id,salary 
from employees 
where salary < max(select max(salary) from employees where job_id='IT_PROG')
and job _id <>'IT_PROG';

案例3：返回其他工种中比job_id为’IT_PROG’部门所有工资低的员工的员工号、姓名、job_id以及salary

select employee_id,last_name,job_id,salary 
from employees 
where salary < all(select distinct salary from employees where job_id= 'IT_PROG') 
and job _id <>'IT_PROG';

或

select employee_id,last_name,job_id,salary 
from employees 
where salary < (select min(salary) from employees where job_id='IT_PROG') 
and job _id <>'IT_PROG';

#行子查询（结果集一行多列或多行多列）
案例：查询员工编号最小且工资最高的员工信息
方法一、

select * from employees 
where (employee_id,salary)=(select min(employee_id),max(salary) from employees);

方法二、
1、查询员工编号最小

select min(employee_id) from employees ;

2、查询工资最高的员工

select max(salary) from employees;

3、查询1、2合并条件的员工信息

select * from employees 
where employee_id=( select min(employee_id) from employees)
and salary=( select max(salary) from employees);

**

#二、select后面

**
案例1：查询每个部门的员工个数

select d.*,(select count(*) from employees where e.department_id=d.department_id) '个数'
from departments d;

案例2：查询员工工号=102的部门名
方法一：

select deparment_name 
from employees e,department d 
where e.employees_id=102 and e.department_id = d.department_id;

方法二：

select (select department_name from departemnts d inner join employees e on d.department_id=e.department_id where e.employees_id=102 );

#三、from后面

注意：将子查询结果充当一张表，要求必须起别名

案例：查询每个部门的平均工资的工资等级
1、查询每个部门的平均工资

select avg(salary),department_id 
from employees 
group by department_id;

2、（2）连接（1）的结果集合job_grades表，筛选条件平均工资 between lowest_sal and highest_sal

select avg_d.*,g.grade_level 
from (select avg(salary) ag,department_id 
from employees group by department_id) avg_d 
inner join job_grades g 
on avg_d.ag between lowest_sal and hightest_sal;

#四、exists后面（相关子查询）

语法： exists（完整的查询语句）
结果： 1或0

select exists(select employee_id from employees);

案例1：查询有员工的部门号
方式一 exists：

select department_name 
from departments d 
where exists(select * from employees e where e.department_id=d.department_id);

方式二in：

select department_name 
from departments d 
where d.department_id in(select * from employees);

案例2：查询没有女朋友的男生信息

方式一：
select b.* from boys b where b.id not in(select * from girls);
方式二：
select b.* from boys b where not exists(select * from girls g where b.id=g.boy_id);

练习：

案例1：查询和zlotkey相同部门的员工姓名和工资
1、查询zlotkey的所在部门

select department_id from employees where last_name='zlotkey';

2、查询部门号=1的员工姓名和工资

select last_name,salary 
from employees 
where department_id=( select department_id from employees where last_name='zlotkey');

案例2：查询工资比公司平均工资高的员工的员工号，姓名和工资
1、查询公司平均工资

select avg(salary) avg_sal from employees;

2、查询salary>1的员工号，姓名和工资

select employee_id,last_name,salary 
from employees where salary>( select avg（salary）avg_sal from employees);

案例3：查询各部门中工资比本部门平均工资高的员工的员工号，姓名和工资
1、查询各部门的平均工资

select avg(salary),department_id 
from employees 
group by department_id;

2、2连接1结果集合employees表，进行筛选

select e.department_id,e.last_name,e.salary 
from (select avg(salary) ag,department_id from employees group by department_id) avg_dep 
inner join employees e on avg_dep.department_id=e.department_id
where salary> avg_dep.ag;

案例4：查询姓名中包含字母u的员工在相同部门的员工的员工号和姓名
1、查询姓名中包含字母u的员工部门号

select distinct department_id 
from employees 
where last_name like '%u%';

2、查询与部门号=1中任意一个的员工姓名和工号

select last_name,employee_id 
from employees where department_id in (select distinct department_id from employees where last_name like '%u%');

案例5：查询在部门的location_id为1700的部门工作的员工的员工号
1、查询location_id为1700的部门

select department_id from departments where location_id=1700;

2、查询部门号等于1中的任意一个的员工号

select employees_id 
from employees 
where department_id = any(select department_id from departments where location_id=1700);

案例6：查询管理者是King的员工姓名和工资
1、查询姓名为King的员工号

select employee_id from employees where last_name='King';

2、查询哪个员工的manger_id=1;

select employee_id,salary 
from employees 
where manger_id in(select employee_id from employees where last_name=’King’);

案例7：查询工资最高的员工的姓名，要求first_name和last_name显示为一列，列名为姓.名

select concat(frist_name，last_name) '姓.名' 
from employees where salary=(select max(salary) from employees);