mysql子查询连接

最新推荐文章于 2024-04-24 18:09:39 发布

CodeGuide

最新推荐文章于 2024-04-24 18:09:39 发布

阅读量847

点赞数

分类专栏： Mysql

本文链接：https://blog.csdn.net/SYJ_1835_NGE/article/details/90726432

版权

Mysql 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

mysql子查询连接

数据库1
数据库2
含义：出现在其他语句中的select语句，称为子查询或者内查询外部的查询语句，称为：主查询或者外查询

分类：

1、按子查询出现的位置：

	select后面：
		仅仅支持标量子查询
	from后面：
		支持表子查询
	where或having后面：
		标量子查询
		列子查询
		行子查询
	exists后面：（相关子查询）
	表子查询

2、按结果集的行列数不同：

	标量子查询（结果集一行一列）
	列子查询(结果集一列多行)
	行子查询（结果集一行多列）
	表子查询(结果集一般为多行多列)

where或者having后面

常跟的结果集

1、标量子查询（单行子查询）
2、列子查询（多行子查询）
3、行子查询（多列子查询）
特点：

1.子查询放在括号内
2.子查询一般放在条件的右侧
3.标量自查询一般搭配着单行操作符使用
< > != = <> <= >=
列子查询一般搭配着多行操作符来使用、
in any/some all

4.子查询的执行顺序要优先于主查询执行，主查询的条件使用到了子查询的条件

标量子查询（一行一列）

使用在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
);

全部教程是本人自己在学习过程中的总结和练习，都很基础写下来的目的主要是方便自己和他人的复习理解，也消除了笔记写下找不到的缺点。如有需要什么笔记或者文档可以留言。

CodeGuide

关注

0
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
mysql子查询连接

mysql子查询连接数据库1数据库2含义：出现在其他语句中的select语句，称为子查询或者内查询外部的查询语句，称为：主查询或者外查询分类：1、按子查询出现的位置： select后面：仅仅支持标量子查询 from后面：支持表子查询 where或having后面：标量子查询列子查询行子查询 exists后面：（相关子查询）表子...
复制链接

扫一扫