武汉理工大学-数据库系统综合实验-实验1.3 数据查询实验（仅供参考）

实验前准备：恢复上次实验数据

在本次实验中，理论上，需要恢复上次1.2数据更新实验修改的数据，恢复成课程资料实例2里的样子。
恢复方法不唯一
删除更改过的两个表

DELETE FROM employee;

DELETE FROM salary;

重新导入数据

LOAD DATA INFILE 'C:/dataset2/employee.csv' INTO TABLE employee
CHARACTER SET gbk
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n';

LOAD DATA INFILE 'C:/dataset2/salary.csv' INTO TABLE salary
CHARACTER SET gbk
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n';

实验任务（参考课程资料）

基本查询任务
查询每个雇员的姓名、地址和联系电话:

SELECT name,address,phone
FROM employee;

查询employee表中部门号和性别，要求使用DISTINCT消除重复行:

SELECT DISTINCT departmentID,gender
FROM employee;

查询所有财务部的员工的姓名和联系电话:

SELECT  name,phone
FROM employee
WHERE departmentID = '1';

查询employee表中女雇员的地址和电话，使用AS子句将结果中各列的标题指定为“地址”和“电话”:

SELECT  address AS 地址,phone AS 电话
FROM employee
WHERE gender = 0;

查询财务部雇员的最高和最低实际收入:

SELECT  MAX(income)
FROM salary
WHERE employeeID  IN(
SELECT employeeID
FROM  employee
WHERE departmentID = '1');

SELECT  MIN(income)
FROM salary
WHERE employeeID  IN(
SELECT employeeID
FROM  employee
WHERE departmentID = '1');

找出所有收入在2000到3000元之间的员工编号:

SELECT employeeID
FROM salary
WHERE income BETWEEN 2000 AND 3000;

子查询的使用
查找在财务部工作的员工的情况:

SELECT *
FROM employee
WHERE departmentID =(
SELECT departmentID
FROM  department
WHERE departName = '财务部');

用子查询方法查找所有收入在2500元以下的雇员的情况:

SELECT *
FROM employee
WHERE employeeID  IN(
SELECT employeeID
FROM  salary
WHERE income < 2500);

连接查询的使用
查询每个雇员的情况及其薪水情况:

SELECT employee.*, salary.income 
FROM employee, salary 
WHERE employee.employeeID=salary.employeeID;

使用内连接查询名字为“王林”的员工所在部门:

SELECT departName 
FROM department 
JOIN employee 
ON department.departmentID=employee.departmentID
WHERE name = '王林';

查找财务部收入在2000元以上的雇员姓名和薪水详情:

SELECT employee.name, salary.income 
FROM employee, salary 
WHERE employee.employeeID=salary.employeeID 
AND salary.income>2000
AND employee.departmentID='001';

GROUP BY、ORDER BY和LIMIT子句的使用
查询employee中男性和女性的人数:

SELECT gender, count(*) AS '人数'
FROM employee 
GROUP BY gender;

查找员工数超过2人的部门名称和员工数量:

SELECT(department.departName),COUNT(*)AS '人数'
FROM employee,department
WHERE employee.departmentID=department.departmentID 
GROUP BY employee.departmentID
HAVING COUNT(*)>2;

将employee表中的员工号码由大到小排列:

SELECT * 
FROM employee 
ORDER BY employeeID DESC;

返回employee表中前5位员工的信息:

SELECT * 
FROM employee 
ORDER BY employeeID
LIMIT 5;

可能出现的错误

查找员工数超过2人的部门名称和员工数量时显示：Empty set
原因：查找信息为空
解决办法：实验1.2 数据更新实验
划掉部分