武汉理工大学-数据库系统综合实验-实验1.3 数据查询实验(仅供参考)
实验前准备:恢复上次实验数据在本次实验中,理论上,需要恢复上次1.2数据更新实验修改的数据,恢复成课程资料实例2里的样子。恢复方法不唯一删除更改过的两个表DELETE FROM employee;DELETE FROM salary;重新导入数据LOAD DATA INFILE 'C:/dataset2/employee.csv' INTO TABLE employeeCHARACTER SET
实验前准备:恢复上次实验数据
在本次实验中,理论上,需要恢复上次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 数据更新实验
划掉部分
![Logo](https://devpress.csdnimg.cn/e61278577bc94c83b8dd32b496fe4711.jpg)
为武汉地区的开发者提供学习、交流和合作的平台。社区聚集了众多技术爱好者和专业人士,涵盖了多个领域,包括人工智能、大数据、云计算、区块链等。社区定期举办技术分享、培训和活动,为开发者提供更多的学习和交流机会。
更多推荐
所有评论(0)