mysql基础---查询语句---进价7 子查询
1.子查询
1.含义:出现在其它语句中的select语句,称为子查询或内查询,外部的查询称为主查询或外查询
2.select first_name from employees where department_id in (select department_id from departments where location_id = 1700);---红色为主查询,绿色为子查询
3.按照子查询的结果可分为:
1.标量子查询-----结果集为一行一列
2.列子查询-------结果集为一列多行
3.行子查询-------结果集为一行多列,或者多行多列
4.表子查询-------结果集一般为多行多列
4.按子查询出现的位置分类:
1.select后面-----仅仅支持标量子查询
2.from后面------支持表子查询
3 where或having后面----一般支持标量/列/行子查询
4.exists(存在)后面----支持表子查询
2.where或having后面
1.支持:
1.标量子查询(单行查询)
2.列子查询(一列多行子查询)
3.行子查询(一行多列子查询)
2.特点
1.子查询放在小括号内
2.子查询一般在条件右侧
3.标量子查询,一般搭配着单行操作符使用(单行操作符: <,>,>=,<=,=等)
4.列子查询,一般搭配着多行操作符使用(in【等于列表中的任一个】,any/some【子查询返回的某一个值比较】,all [【子查询返回的所有值比较】
5.子查询的执行优于主查询的执行,主查询的条件用到子查询的结果
案例:
3.标量子查询
1.谁的工资比DavidAustin高
mysql > select concat(first_name,last_name) 员工姓名,salary 工资 from employees where salary > (select salary from employees where concat(frist_name,last_name) = 'DavidAustin');
mysql > select salary from employees where concat(frist_name,last_name) = 'DavidAustin'
2.返回job_id与141号员工相同,salary比143号员工高的员工姓名,job_id和工资
mysql >select concat(first_name,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);
3.返回公司工资最小的员工的姓名,job_id,salary
msyql > select concat(first_name,last_name) 姓名,job_id as 工种编号,salary 工资 from employees where salary = (select min(salary) from employees);
4.查询最低工资大于50号部门最低工资的部门id和其最低工资
mysql > select department_id 部门编号,min(salary) 最低工资 from employees group by department_id having min(salary) > (select min(salary) from employees where department_id = 50);
4.列子查询(一列多行)
案例:
1.返回location_id是1400或1700的部门中的所有员工的姓名
mysql > select concat(last_name,first_name) 员工姓名 from employees where department_id in (select department_id from departments where location_id in (1400,1700));
2.返回其他部门中比job_id为'IT_PROG'部门任一工资低的员工的员工号,姓名,Job_id,salary
mysql > select employee_id 员工号,concat(last_name,first_name) 姓名,job_id 工种号,salary 工资 from employees where salary < any (select salary from employees where job_id = 'IT_PROG') and job_id <> 'IT_PROG';
5.行子查询(结果为一行多列或者多行多列)
案例:
1.查询员工编号最小并且工资最高的员工信息
mysql > select * from employees where employee_id = (select min(employee_id) from employees) and salary = ( select max(salary) from employees);
mysql > select * from employees where(employee_id,salary) = (select min(employee_id),max(salary) from employees);
3.放在select后面---仅仅支持标量子查询
案例:
1.查询每个部门的员工个数
mysql > select d.*,(select count(*) from employees e where e.department_id = d.department_id) 个数 from departments d;
mysql > select d.*,count(e.employee_id) 个数 from employees e right join departments d on e.department_id = d.department_id group by department_id;
2.查询员工号102的部门名
mysql > select department_name 部门名 from employees e right join departments d on e.department_id = d.department_id where e.employee_id = 102;
mysql > select (select department_name 部门名 from employees e inner join departments d on e.department_id = d.department_id where e.employee_id = 102) 部门名;
mysql > select (select department_name 部门名,e.employee_id 员工编号 from employees e inner join departments d on e.department_id = d.department_id where e.employee_id = 102) 部门名;
---这是因为select后面的子查询只能是标量子查询
4.放在from后面----表子查询
案例:
1.查询每个部门的平均工资的工资等级
mysql > select e.avgSalary 平均工资, grade_level 工资等级 from (select avg(salary) avgSalary from employees group by department_id) e inner join job_grades j on e.avgSalary between lowest_sal and highest_sal;
---先黄色部分查出一个表e,然后表e与job_grades表进行内连接
5.exists后面(相关子查询)
案例:
1.select exists(select employee_id from employees);------exists表示是否存在,即子查询是否有值,如果有则为1,否则为0
2.查询有员工名的部门名
mysql > select deparment_name 部门名 from departments d where exists(select * from employees e where e.department_id = d.department_id);
----先查子查询,后查子查询
mysql > select department_name from departments d where d.department_id in (select department_id from employees);
3.查询没有女朋友的男神信息
mysql > select bo.* from boys bo where not exists(select boyfriend_id from beauty b where bo.id = b.boyfriend_id);
mysql > select bo.* from boys bo where bo.id not in (select boyfriend_id from beauty);
案例:查询各部门中工资比本部门平均工资高的员工的员工号,姓名,工资
msyql >select employee_id 员工号,concat(first_name,last_name) 姓名,salary 工资 from employees e inner join (select avg(salary) ag,department_id from employees) avg_de on e.department_id = avg_de.department_id where e.salary > avg_de.ag;