MySQL基础2

MySQL-学习笔记2

进阶7 子查询

一、类型

1、按照子查询的查询记录数分类

• 标量子查询
• 列子查询
• 行子查询

2、按照子查询的位置分类

• 跟在where 或者 having 后面的子查询
• 跟在selec后面的查询
• 跟在from后面的查询

二、特点

• 子查询放在小括号内
• 标量子查询，一般搭配着单行操作符使用> < >= <= = <>
• 列子查询一般搭配着多行操作符使用 in any some all

三、例子

（一）where 或 having后的子查询

1、标量子查询
eg.1返回公司工资最少的员工的last_name,job_id 和salary

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

eg.2查询员工号=102的部门名

select department_name as 部门名
from departments d
where d.department_id = (SELECT department_id
						 from employees
					     where employee_id = 102) ;

2、列子查询

eg.1 查询谁的工资比Abel高

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

eg.2 返回job_id 与141号员工相同，salary 比143号员工多的员工姓名、job_id和工资。

select last_name, job_id, salary
from employees
where job_id = (
	select job_id
	from employees
	where employee_id = 141)
and salary > (
	select salary
	from employees
	where employee_id =143);

eg.3 查询location_id 是1400或1700的部门的员工姓名

select last_name
from employees
where department_id IN (selectdistinct department_id
						from departments
						where location_id IN (1400,1700));

eg.4 查询其他部门中比job_id为’it_prog’部门任一工资低的员工的：工号、姓名、job_id以及salary

#方法一
select employee_id, last_name, job_id, salary
from employees
where salary < (select MAX(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 < ANY(
	SELECT DISTINCT salary
	FROM employees
	WHERE job_id  = 'IT_PROG'
)
AND job_id <> 'IT_PROG';

（二）select后面的子查询

eg.1查询每个部门的员工个数

select d.*,(select COUNT(*)
			from employees e
			where e.department_id = d.`department_id`) as 个数
from departments d;

(三) From 后面的子查询

eg1.查询每个部门平均工资的等级

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

三、子查询的练习

– 查询与Zlotkey相同部门的员工姓名和工资

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

– 查询工资比公司平均工资高的员工工号、姓名、和工资

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

– 查询各部门中比部门平均工资高的员工工号、姓名、和工资

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

– 查询与姓名中含有u的员工在同一个部门的员工的员工工号和姓名

select e.employee_id, e.last_name
from employees e
where department_id in (select disticnt department_id
						from employees
						where last_name like "%u%");

– 查询所在部门的location_id为1700的员工的员工号和员工姓名

select e.employee_id, e.last_name
from employees e 
where department_id in (select distinct department_id
					    from departments
						where location_id = 1700);

– 查询管理者是K_ing的员工姓名和工资

select last_name, salary
from employees
where manager_id in (select employee_id
					 from employees
					 where last_name = 'K_ing');

– 查询工资最高的员工的姓名

select concat(last_name,' ',first_name) 姓名
from employees 
where salary = (select max(salary) from employees);

进阶8 联合查询

– 功能：将多条查询的结果合并放到一起
– 关键字：union
– 语法：

查询1
union all 
查询2
...

– 特点：
1、要求多条查询语句的列数一致
2、union 默认去重，union all可以保留所有查询结果

– 例子

select c_id, c_name, c_gender
from cn
where c_gender = 'male'
union all
select
select u_id, u_name, u_gender
from un
where u_gender = 'male'