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

一、实验目的：

表连接–进行多表查询

二、实验内容：

对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, SalaryWHERE Employees.EmployeeID = Salary.EmployeeID

在这种等值连接的情况下，两个表的相同列名会同时出现

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

SELECT Employees.*, InCome, OutCome, ActInComeFROM Employees, SalaryWHERE Employees.EmployeeID = Salary.EmployeeID

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

SELECT Employees.EmployeeID, Name, Education, InCome, OutCome, ActInComeFROM Employees, SalaryWHERE Employees.EmployeeID = Salary.EmployeeIDAND InCome > 2500

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

SELECT Employees.EmployeeID, Name, Education, InCome, OutCome, ActInCome, DepartmentNameFROM Employees, Salary, DepartmentsWHERE Employees.EmployeeID = Salary.EmployeeIDAND Employees.DepartmentID = Departments.DepartmentIDAND InCome > 2500

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

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

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

SELECT * FROM Employees NATURAL JOIN Salary

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

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

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

SELECT Employees.EmployeeID, Name, Education, InCome, OutCome, ActInCome, DepartmentNameFROM Employees NATURAL JOIN Salary NATURAL JOIN DepartmentsWHERE InCome > 2500

（5）查询所有员工的员工号，名字，薪酬，支出（左连接）
为了显示左连接和右连接的区别，在Employees表中增加一行数据

增加数据后，Salary表不更新，这样会导致Salary表中没有该新增员工的数据

SELECT Employees.EmployeeID, Name, InCome, OutComeFROM Employees LEFT JOIN SalaryON Employees.EmployeeID = Salary.EmployeeID

会发现左连接会列示全部Employees（即左表）中的所有行，当Salary（即右表）中不存在对应左表的数据时，会显示NULL
（6）查询所有部门的员工情况（右连接）
为了显示左连接和右连接的区别，在Departments表中增加一行数据

增加数据后，Employees表不更新，这样会导致Employees表中没有该新增员工的数据

SELECT DepartmentName, EmployeeID, NameFROM Employees RIGHT JOIN DepartmentsON Employees.DepartmentID = Departments.DepartmentID

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

SELECT DepartmentName, Name, InComeFROM Employees LEFT JOIN SalaryON Employees.EmployeeID = Salary.EmployeeID RIGHT JOIN DepartmentsON Employees.DepartmentID = Departments.DepartmentID

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

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

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

SELECT DepartmentName, NameFROM EmployeesLEFT JOIN Departments USING(DepartmentID)UNIONSELECT DepartmentName, NameFROM EmployeesRIGHT JOIN Departments USING(DepartmentID)