mysql子查询连接
-
数据库1
-
数据库2
-
含义:出现在其他语句中的select语句,称为子查询或者内查询外部的查询语句,称为:主查询或者外查询
-
分类:
1、按子查询出现的位置:
select后面: 仅仅支持标量子查询 from后面: 支持表子查询 where或having后面: 标量子查询 列子查询 行子查询 exists后面:(相关子查询) 表子查询
2、按结果集的行列数不同:
标量子查询(结果集一行一列) 列子查询(结果集一列多行) 行子查询(结果集一行多列) 表子查询(结果集一般为多行多列)
where或者having后面
-
常跟的结果集
1、标量子查询(单行子查询)
2、列子查询(多行子查询)
3、行子查询(多列子查询) -
特点:
1.子查询放在括号内
2.子查询一般放在条件的右侧
3.标量自查询一般搭配着单行操作符使用
< > != = <> <= >=
列子查询一般搭配着多行操作符来使用、
in any/some all4.子查询的执行顺序要优先于主查询执行,主查询的条件使用到了子查询的条件
标量子查询(一行一列)
-
使用在where后面的子查询
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 employee_id =141 #(2)143号员工的salary SELECT job_id FROM employees WHERE employee_id =141 #(3)查询job_id与(1)相同,salary>(2)的员工姓名,job_id和工资 SELECT last_name,job_id,salary FROM employees WHERE salary>( SELECT salary FROM employees WHERE employee_id=143 ) AND job_id =( SELECT job_id FROM employees WHERE employee_id =141 );
3、返回公司工资最少的员工的last_name,job_id和salard
#(1)查询公司最低的工资 SELECT MIN(salary) FROM employees #(2)查询员工的last_name,job_id和salary ,并且符合salary=(1)----->在这里用in也可以 SELECT last_name,job_id,salary FROM employees WHERE salary=( SELECT MIN(salary) FROM employees );
-
使用在Having后面的子查询
4、查询最低工资大于 50号部门最低工资 的部门id和其最低工资
#(1)查询50号部门的最低工资 SELECT MIN(salary) FROM employees WHERE department_id = 50 #(2)查询min(salary)>(1) 的部门id和其最低工资 SELECT department_id,MIN(salary) FROM employees GROUP BY department_id HAVING MIN(salary)>( SELECT MIN(salary) FROM employees WHERE department_id = 50 );
列子查询(多行子查询)
-
理解in/not in
in:只要符合其中一个条件就可以—>a in (10,20,30)(a等于后面的其中一个条件就可以)
等价于a=any(10,20,30)
not in:只要不符合任意一个条件就可以—>a in (10,20,30)(a不等于后面的其中任意一个条件就可以)
等价于a <>ALL(10,20,30) -
理解any/some
any:只要符合其中一个条件就可以—>a > (10,20,30)(a只要大于后面的其中一个就可以)即大于最小值即可(min代替) -
理解all
all:要符合任意一个条件就可以—>a > (10,20,30)(a要大于后面的任意一个)即大于最大值即可(max代替)
1、返回location_id是1400或1700的部门中的所有员工姓名
去重的目的就是location_id=1400或者1700的部门可能有多个
#(1)查询location_id是1400或1700的部门(最好去重)
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN (1400,1700)
#(2)查询department_id 只要复合 (1)条件的所有员工姓名
SELECT last_name
FROM employees
WHERE department_id IN (
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN (1400,1700)
);
2、返回其它工种中比job_id为’IT_pRoG’部门任一工资低的员工的:工号、姓名、job_id 以及salary
即其他工种的工资低于job_id为’IT_pRoG’部门任一工资(即小于最大工资)
#(1)job_id为'IT_pRoG'部门的工资
SELECT DISTINCT salary
FROM employees
WHERE job_id='IT_pRoG'
#(2)查询员工的:工号、姓名、job_id 以及salary并且salary< (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< (
SELECT MAX(salary)
FROM employees
WHERE job_id='IT_pRoG'
)AND job_id <> 'IT_pRoG';
3、返回其它部门中比job_id为IT_pRoG’部门所有工资都低的员工的员工号、姓名、job_id 以及salary
和(2)题中的唯一不同是所有和任一(即小于最小工资即可)
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';
行子查询(多列子查询)用的较少
1、查询员工编号最小并且工资最高的员工信息
#行子查询的案例
SELECT *
FROM employees
#将多个字段当成是一个字段来进行使用
WHERE (salary,employee_id)=(
SELECT MAX(salary),MIN(employee_id)
FROM employees
);
#---------等价于-----------
#员工编号最小
SELECT MIN(employee_id)
FROM employees
#工资最高
SELECT MAX(salary)
FROM employees
# 查询结果
SELECT *
FROM employees
WHERE employee_id=(
SELECT MIN(employee_id)
FROM employees
) AND salary=(
SELECT MAX(salary)
FROM employees
);
放到select后面
- 仅仅支持标量子查询
1、查询每个部门的员工个数
#1.一共有多少部门
SELECT COUNT(*)
FROM departments
GROUP BY department_id;
#2.总查询结果(即使部门人数为0页显示出来)
SELECT d.*,(
SELECT COUNT(*)
FROM employees e
WHERE d.`department_id`=e.department_id
) 个数
FROM departments d;
#3.统计部门人数(按照部门分组)只显示有数量>0的
SELECT COUNT(*) #部门有人的员工个数
FROM employees e,departments d
WHERE e.`department_id`=d.`department_id`
GROUP BY e.`department_id`;
2、查询员工号=102的部门名
#连接查询
SELECT d.department_name
FROM employees e,departments d
WHERE e.`department_id`=d.department_id AND e.`employee_id`=102;
#使用内连接进行查询:
SELECT d.department_name
FROM employees e
INNER JOIN departments d
ON e.`department_id`=d.department_id
WHERE e.`employee_id`=102;
# 使用子查询
SELECT e.`employee_id`,(
SELECT department_name
FROM departments d
WHERE e.`department_id`=d.department_id
) 部门名
FROM employees e
WHERE e.`employee_id`=102;
from后面
- 将子查询放到from后面(将子查询充当一个新的表)
1、查询每个部门的平均工资的工资等级
#1、查询每个部门的平均工资
SELECT AVG(salary)
FROM employees e
GROUP BY e.`department_id`;
#注意:
#将我们查询到的结果集充当一张表,要求新表必须起别名
#拿着新表的结果集中的平均工资字段去匹配工资等级
SELECT ag_dep.*,jd.grade_level
FROM (
SELECT AVG(salary) ag,e.department_id
FROM employees e
GROUP BY e.`department_id`
) ag_dep
INNER JOIN job_grades jd
ON ag_dep.ag BETWEEN jd.`lowest_sal` AND jd.`highest_sal`;
exists后面
- 是否存在的意思
SELECT EXISTS(SELECT last_name FROM employees); #存在为1 SELECT EXISTS(SELECT last_name FROM employees WHERE salary = 300000); #不存在为0
1、查询有员工的部门名
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 department_id
FROM employees e
);
# 使用连接查询进行查询(内连接)
SELECT DISTINCT department_name
FROM departments d
INNER JOIN employees e
ON d.`department_id` = e.`department_id`
WHERE e.`employee_id` IS NOT NULL
2、查询没有女朋友的男神信息
#exists方式
SELECT bo.*
FROM boys bo
WHERE NOT EXISTS(
SELECT b.`boyfriend_id`
FROM beauty b
WHERE bo.id=b.`boyfriend_id`
);
# in的方式
SELECT bo.*
FROM boys bo
WHERE bo.`id` NOT IN(
SELECT boyfriend_id
FROM beauty
);
#外连接方式
SELECT bo.*
FROM boys bo
LEFT OUTER JOIN beauty b
ON bo.`id`=b.`boyfriend_id`
WHERE b.`boyfriend_id` IS NULL
练习
1、查询和zlotkey相同部门的员工姓名和工资
#(1)先查询zlotkey的部门
SELECT department_id
FROM employees
WHERE last_name = 'zlotkey';
#(2)in和=都可以
SELECT last_name,salary
FROM employees
WHERE department_id IN (
SELECT department_id
FROM employees
WHERE last_name = 'zlotkey'
);
#(内连接)
SELECT e.`last_name`,e.salary
FROM employees e
INNER JOIN (
SELECT department_id
FROM employees
WHERE last_name = 'zlotkey'
) temp
ON temp.department_id=e.department_id;
2、查询工资比公司平均工资高的员工的员工号,姓名和工资。
#(1)查询公司的平均工资
SELECT AVG(salary)
FROM employees
#(2)总查询
SELECT employee_id,last_name,salary
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees
);
3、查询各部门中工资比本部门平均工资高的员工的员工号,姓名和工资
# (1)查询各部门的平均工资
SELECT AVG(salary),department_id
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
# (2)将新查询的表作为一个新表进行内连接查询
SELECT e.department_id,e.employee_id,e.last_name,e.salary
FROM employees e
INNER JOIN (
SELECT AVG(salary) ag,department_id
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
) md
ON e.department_id = md.department_id
WHERE e.salary > md.ag;
4、查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
#(1)查询姓名中包含字母u员工所在部门
SELECT DISTINCT department_id
FROM employees
WHERE last_name LIKE '%u%';
# (2)总查询
SELECT employee_id,last_name
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 DISTINCT department_id
FROM departments
WHERE location_id = 1700
#(2)总查询
SELECT employee_id
FROM employees
WHERE department_id IN (
SELECT DISTINCT department_id
FROM departments
WHERE location_id = 1700
);
6、查询管理者是King的员工姓名和工资(自连接最好起别名)
#(1)首先查询king的employee_id
SELECT DISTINCT employee_id
FROM employees
WHERE last_name = 'K_ing'
#(2)总查询
SELECT last_name,salary
FROM employees
WHERE manager_id IN (
SELECT DISTINCT employee_id
FROM employees
WHERE last_name = 'K_ing'
);
7、查询工资最高的员工的姓名,要求first_name和last_name显示为一列,列名为姓.名
#(1)查询最高工资
SELECT MAX(salary)
FROM employees
#(2)总查询
SELECT CONCAT(first_name,last_name) '姓.名'
FROM employees
WHERE salary = (
SELECT MAX(salary)
FROM employees
);