Mysql数据库基础第二章:(六)连接查询

·  阅读 4165

本文已参与「新人创作礼」活动,一起开启掘金创作之路

Mysql数据库基础系列

软件下载地址 提取码:7v7u 数据下载地址 提取码:e6p9

一、基本语法

又称多表查询,当查询的字段分别来自于不同表时,就会用到连接查询。连接查询有sql92和sql99语法,sql92标准仅仅支持内连接,因此本文主要介绍sql99语法。

1.1sql99语法主要如下:

select coloun
from table1 连接类型
join table 2
on 连接条件
复制代码

1.2主要分类:

内连接:inner
外连接:
	左外:left outer
	右外:right outer
	全外(用的较少)
交叉连接:cross(用的较少)相当于做了一个笛卡尔乘积
复制代码

在这里插入图片描述

图1 内连接

在这里插入图片描述

图2 右外连接 在这里插入图片描述 图3 左外连接 在这里插入图片描述图4 全外连接

二、内连接

内连接可以添加排序、分组、筛选等 筛选条件

2.1基本语法

select column
from table 1
inner join table 2
on 连接条件
复制代码

2.2 分类:

  • 等值连接
# 1 查询员工名、部门名
# 其中员工名在employees表,部门名在departments表
SELECT last_name,department_name
FROM departments d
JOIN  employees e
ON e.`department_id` = d.`department_id`;

#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%';

#3. 查询部门个数>3的城市名和部门个数,(添加分组+筛选)
SELECT city, COUNT(*) 部门个数 
FROM departments d
INNER	JOIN locations l
ON d.`location_id` = l.`location_id`
GROUP BY city
HAVING 部门个数 > 3;
#4.查询员工名、部门名、工种名,并按部门名降序(添加三表连接)

SELECT last_name,department_name,job_title
FROM employees e
INNER JOIN departments d ON e.`department_id`=d.`department_id`
INNER JOIN jobs j ON e.`job_id` = j.`job_id`

ORDER BY department_name DESC;
复制代码
  • 非等值连接
#1. 查询工资级别的个数>20的个数,并且按工资级别降序
 SELECT COUNT(*),grade_level
FROM employees e
 JOIN job_grades g
 ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`
 GROUP BY grade_level
 HAVING COUNT(*)>20
 ORDER BY grade_level DESC;
复制代码
  • 自连接
#1.查询员工的名字、上级的名字
 SELECT e.last_name,m.last_name
 FROM employees e
 JOIN employees m
 ON e.`manager_id`= m.`employee_id`;

#2.查询姓名中包含字符k的员工的名字、上级的名字 
 SELECT e.last_name,m.last_name
 FROM employees e
 JOIN employees m
 ON e.`manager_id`= m.`employee_id`
 WHERE e.`last_name` LIKE '%k%';
复制代码

三、 外连接

用于查询一个表中有,另一个表没有的记录 外连接的查询结果为主表中的所有记录 如果从表中有和它匹配的,则显示匹配的值 如果从表中没有和它匹配的,则显示null 外连接查询结果=内连接结果+主表中有而从表没有的记录 全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的

3.1 左外连接

# 查询没有员工的部门的信息
 SELECT d.*,e.employee_id
 FROM departments d
 LEFT OUTER JOIN employees e
 ON d.`department_id` = e.`department_id`
 WHERE e.`employee_id` IS NULL;
复制代码

3.2 右外连接

 SELECT d.*,e.employee_id
 FROM employees e
 RIGHT OUTER JOIN departments d
 ON d.`department_id` = e.`department_id`
 WHERE e.`employee_id` IS NULL;
复制代码

上述两种方法得到结果一致,部门信息在departments表中,departments表没有employee_id,这时我们想要的结果是部门信息+员工id,因此,如果使用左外连接,则departments为主表,右外连接则employees为主表。

四、练习

#1.显示所有员工的姓名,部门号和部门名称。

SELECT last_name,d.department_id,department_name
FROM employees e,departments d
WHERE e.`department_id` = d.`department_id`;


#2.查询90号部门员工的job_id和90号部门的location_id

SELECT job_id,location_id
FROM employees e,departments d
WHERE e.`department_id`=d.`department_id`
AND e.`department_id`=90;



#3.	选择所有有奖金的员工的 last_name , department_name , location_id , city

SELECT last_name , department_name , l.location_id , city
FROM employees e,departments d,locations l
WHERE e.department_id = d.department_id
AND d.location_id=l.location_id
AND e.commission_pct IS NOT NULL;


#4.选择city在Toronto工作的员工的
last_name , job_id , department_id , department_name 

SELECT last_name , job_id , d.department_id , department_name 
FROM employees e,departments d ,locations l
WHERE e.department_id = d.department_id
AND d.location_id=l.location_id
AND city = 'Toronto';



#5.查询每个工种、每个部门的部门名、工种名和最低工资


SELECT department_name,job_title,MIN(salary) 最低工资
FROM employees e,departments d,jobs j
WHERE e.`department_id`=d.`department_id`
AND e.`job_id`=j.`job_id`
GROUP BY department_name,job_title;



#6.查询每个国家下的部门个数大于2的国家编号

SELECT country_id,COUNT(*) 部门个数
FROM departments d,locations l
WHERE d.`location_id`=l.`location_id`
GROUP BY country_id
HAVING 部门个数>2;



#7、选择指定员工的姓名,员工号,以及他的管理者的姓名和员工号,结果类似于下面的格式
employees	Emp#	manager	Mgr#
kochhar		101	    king	100

SELECT e.last_name employees,e.employee_id "Emp#",m.last_name manager,m.employee_id "Mgr#"
FROM employees e,employees m
WHERE e.manager_id = m.employee_id
AND e.last_name='kochhar';
复制代码
分类:
开发工具
标签:
收藏成功!
已添加到「」, 点击更改