MYSQL基础上机练习题（四）表连接

柔光的暖阳◎ 2022-07-13 12:43 175阅读 0赞

### 一、实验目的： ###

表连接–进行多表查询

### 二、实验内容： ###

对MYSQL基础上机练习题（二）中所输入的数据连接表查询

### 三、题目： ###

1.  使用连接谓词  
    （1）进行等值连接，查询全部员工的个人信息以及薪酬情况  
    （2）进行自然连接，查询全部员工的个人信息以及薪酬情况  
    （3） 查询工资水平在2500以上的员工号、姓名、教育、收入、支出、盈余  
    （4）查询工资水平在2500以上的员工号、姓名、教育、收入、支出、盈余、所属部门
2.  使用JOIN连接  
    （1）进行等值连接，查询全部员工的个人信息以及薪酬情况  
    （2）进行自然连接，查询全部员工的个人信息以及薪酬情况  
    （3） 查询工资水平在2500以上的员工号、姓名、教育、收入、支出、盈余  
    （4）查询工资水平在2500以上的员工号、姓名、教育、收入、支出、盈余、所属部门  
    （5）查询所有员工的员工号，名字，薪酬，支出（左连接）  
    （6）查询所有部门的员工情况（右连接）  
    （7）查询所有部门的所有员工的薪酬情况（全连接）  
    （8）查询所有部门所有员工的名字

### 四、代码： ###

1.  使用连接谓词  
    （1）进行等值连接，查询全部员工的个人信息以及薪酬情况  
    等值连接就是把两个表中有对应的行列示，对应第二大题第（1）小题的方式，出现的结果都一样

SELECT * FROM Employees, Salary
    WHERE Employees.EmployeeID = Salary.EmployeeID

在这种等值连接的情况下，两个表的相同列名会同时出现  
![Mysql 等值连接][Mysql]  
（2）进行自然连接，查询全部员工的个人信息以及薪酬情况  
自然连接的情况下，两个表的相同列名不会同时出现，因此使用连接谓词时要选择性地选择列名，对应第二大题第（2）小题的方式，使用JOIN会使代码更简洁

SELECT Employees.*, InCome, OutCome, ActInCome
    FROM Employees, Salary
    WHERE Employees.EmployeeID = Salary.EmployeeID

![Mysql 自然连接][Mysql 1]  
（3） 查询工资水平在2500以上的员工号、姓名、教育、收入、支出、盈余  
使用连接谓词的时候，存在多个条件选择时，需要用AND关键词，对应第二大题第（3）小题的方式，使用JOIN会减少运算次数

SELECT Employees.EmployeeID, Name, Education, InCome, OutCome, ActInCome
    FROM Employees, Salary
    WHERE Employees.EmployeeID = Salary.EmployeeID
    AND InCome > 2500

![Mysql 两表连接][Mysql 2]  
（4）查询工资水平在2500以上的员工号、姓名、教育、收入、支出、盈余、所属部门  
同上一题，多个选择条件用AND连接，但这种方式会使运算慢，对应第二大题第（4）小题的方式，使用JOIN会使运算更快

SELECT Employees.EmployeeID, Name, Education, InCome, OutCome, ActInCome, DepartmentName
    FROM Employees, Salary, Departments
    WHERE Employees.EmployeeID = Salary.EmployeeID
    AND Employees.DepartmentID = Departments.DepartmentID
    AND InCome > 2500

![Mysql 多表连接][Mysql 3]  
2. 使用JOIN连接  
（1）进行等值连接，查询全部员工的个人信息以及薪酬情况  
对应第一大题第（1）小题的方式，结果一致

SELECT * FROM Employees JOIN Salary
    ON Employees.EmployeeID = Salary.EmployeeID

![Mysql 等值连接][Mysql 4]  
（2）进行自然连接，查询全部员工的个人信息以及薪酬情况  
对应第一大题第（2）小题的方式，NATURAL JOIN更简洁

SELECT * FROM Employees NATURAL JOIN Salary

![Mysql 自然连接][Mysql 5]  
（3） 查询工资水平在2500以上的员工号、姓名、教育、收入、支出、盈余  
对应第一大题第（3）小题的代码，减少运算次数

SELECT Employees.EmployeeID, Name, Education, InCome, OutCome, ActInCome FROM Employees NATURAL JOIN Salary
    WHERE InCome > 2500

![Mysql 自然连接][Mysql 6]  
（4）查询工资水平在2500以上的员工号、姓名、教育、收入、支出、盈余、所属部门  
对应第一大题第（4）小题的方式，三表连接的时候代码更简洁，运算次数减少

SELECT Employees.EmployeeID, Name, Education, InCome, OutCome, ActInCome, DepartmentName
    FROM Employees NATURAL JOIN Salary NATURAL JOIN Departments
    WHERE InCome > 2500

![Mysql 多表连接][Mysql 7]  
（5）查询所有员工的员工号，名字，薪酬，支出（左连接）  
为了显示左连接和右连接的区别，在Employees表中增加一行数据  
![Mysql 左连接][Mysql 8]  
增加数据后，Salary表不更新，这样会导致Salary表中没有该新增员工的数据

SELECT Employees.EmployeeID, Name, InCome, OutCome
    FROM Employees LEFT JOIN Salary
    ON Employees.EmployeeID = Salary.EmployeeID

![Mysql 左连接][Mysql 9]  
会发现左连接会列示全部Employees（即左表）中的所有行，当Salary（即右表）中不存在对应左表的数据时，会显示NULL  
（6）查询所有部门的员工情况（右连接）  
为了显示左连接和右连接的区别，在Departments表中增加一行数据  
![Mysql 右连接][Mysql 10]  
增加数据后，Employees表不更新，这样会导致Employees表中没有该新增员工的数据

SELECT DepartmentName, EmployeeID, Name
    FROM Employees RIGHT JOIN Departments
    ON Employees.DepartmentID = Departments.DepartmentID

![Mysql 右连接][Mysql 11]  
会发现右连接会列示全部Departments（即右表）中的所有行，当Employees（即左表）中不存在对应左表的数据时，会显示NULL  
（7）查询所有部门的所有员工的薪酬情况（全连接）  
全连接顾名思义，会把左右表的行全部列示，当有不对应的行时，会分别显示NULL  
有两种方法选择连接的列名  
第一种是使用<表名>.<列名>

SELECT DepartmentName, Name, InCome
    FROM Employees 
    LEFT JOIN Salary
    ON Employees.EmployeeID = Salary.EmployeeID 
    RIGHT JOIN Departments
    ON Employees.DepartmentID = Departments.DepartmentID

第二种是使用表名 USING(列名)，可以少书写起码两次的列名

SELECT DepartmentName, Name, InCome
    FROM Employees 
    LEFT JOIN Salary USING(EmployeeID)
    RIGHT JOIN Departments USING(DepartmentID)

![Mysql全连接][Mysql 12]  
![Mysql全连接][Mysql 13]  
（8）查询所有部门所有员工的名字  
两表的全连接需要用UNION进行连接

SELECT DepartmentName, Name
    FROM Employees
    LEFT JOIN Departments USING(DepartmentID)
    UNION
    SELECT DepartmentName, Name
    FROM Employees
    RIGHT JOIN Departments USING(DepartmentID)

![Mysql][Mysql 14]

[Mysql]: /images/20220713/f2a200bd999a4c9ba8d9896af032b7ac.png
[Mysql 1]: /images/20220713/aab5ba21219849bdbfc437889f373676.png
[Mysql 2]: /images/20220713/f76c788415b740a38db0e0344c9daee2.png
[Mysql 3]: /images/20220713/4f21c73cbf774659ac6fb3d0d653c3eb.png
[Mysql 4]: /images/20220713/11bc482642084330aecab44fc0f8043f.png
[Mysql 5]: /images/20220713/b702ec8d75ea4c59af0a793db8540295.png
[Mysql 6]: /images/20220713/8f73fbdfc8a0456ea9226516374f5fa3.png
[Mysql 7]: /images/20220713/babeb5d381ce4aa893c3f897fa1df040.png
[Mysql 8]: /images/20220713/668df5d2912a407095fc1afa4056df8b.png
[Mysql 9]: /images/20220713/d8f1cfdc253249f48f78a4048d70c0e8.png
[Mysql 10]: /images/20220713/bf8e38d6f65f41c9908558885df23e55.png
[Mysql 11]: /images/20220713/a3e881d894bb42719a0d480902fdd3ce.png
[Mysql 12]: /images/20220713/84cb52434f3049839d742e2d5b7cf730.png
[Mysql 13]: /images/20220713/92a1e15158974028bdf2b6c1041820a3.png
[Mysql 14]: /images/20220713/419c52688bc2498ab4abf480556f2a88.png