本文主要是介绍MySQL-sql99语法-等值连接,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
![image](/upload/202205/24/202205240220128767.png)
![image](/upload/202205/24/202205240220129719.png)
1.等值连接
案例1:查询员工名、部门名
# 案例1:查询员工名、部门名
SELECT `last_name`,`department_name`
FROM `employees` e
INNER JOIN `departments` d
ON e.`department_id`=d.`department_id`
![image](/upload/202205/24/202205240220130657.png)
案例2:查询名字中包含e的员工名和工种号(添加筛选)
# 案例2:查询名字中包含e的员工名和工种号(添加筛选)
SELECT `last_name`,`job_title`
FROM `employees` e
INNER JOIN `jobs` j
ON e.`job_id`=j.`job_id`
WHERE e.last_name LIKE '%e%'
![image](/upload/202205/24/202205240220131765.png)
案例3:查询部门个数>3的城市名和部门个数(添加分组+筛选)
# 案例3:查询部门个数>3的城市名和部门个数(添加分组+筛选)
SELECT `city`,COUNT(*) 部门个数
FROM `departments` d
INNER JOIN `locations` l
ON d.`location_id`=l.`location_id`
GROUP BY city
HAVING COUNT(*) > 3;
![image](/upload/202205/24/202205240220132702.png)
案例4:查询哪个部门的部门员工个数>3的部门名和员工个数,并按个数降序(排序)
# 案例4:查询哪个部门的部门员工个数>3的部门名和员工个数,并按个数降序(排序)
select d.`department_name`,count(*) 个数
from `departments` d
inner join `employees` e
on d.`department_id`=e.`department_id`
group by d.`department_name`
having 个数 > 3
order by 个数 desc;
![image](/upload/202205/24/202205240220133655.png)
案例5:查询员工名、部门名、工种名,并按部门名降序(三表连接)
# 案例5:查询员工名、部门名、工种名,并按部门名降序(三表连接)
SELECT `last_name`,`department_name`,`job_title`
FROM `departments` d
INNER JOIN `employees` e
ON d.`department_id`=e.`department_id`
INNER JOIN `jobs` j
ON e.`job_id`=j.`job_id`
ORDER BY `department_name` DESC;
![image](/upload/202205/24/202205240220134748.png)
![image](/upload/202205/24/202205240220135856.png)
这篇关于MySQL-sql99语法-等值连接的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!