武汉理工大学-数据库系统综合实验-实验1.2 数据更新实验（仅供参考）

实验前准备：修改employee.csv
由于后续实验中，在连接查询部分需要判断department.departmentID=employee.departmentID
可能会出现1和001无法匹配的情况，建议更改。
故用记事本打开课程资料中的employee.csv，在最后一列数据前加上00，并保存。

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

插入记录

向DBEM数据库的三个基本表中导入实例2的数据记录：

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/department.csv' INTO TABLE department
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';

删除表employee和salary中的第一行记录，然后用SQL语句向表中插入删除掉的数据：

DELETE FROM employee
WHERE employeeID = '000001';

DELETE FROM salary
WHERE employeeID = '000001';

INSERT INTO employee
VALUES('000001','王林','大专','1966-01-23',1,8,'中山路32号',83355668,2);

INSERT INTO salary
VALUES('000001',2100.8,123.09);

更新记录
将表employee中编号020018的记录的部门号改成4：

UPDATE employee
SET departmentID='4'
WHERE employeeID = '020018';

删除记录
删除表employee中编号为102201的职工信息：

DELETE FROM employee
WHERE employeeID = '102201';

删除所有收入大于2500的员工信息：

DELETE FROM employee
WHERE employeeID IN(
SELECT employeeID
FROM salary
WHERE income>2500);

可能出现的错误

导入表数据时报错：ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
原因：没有在指定的地方导入数据

解决办法：MYSQL导入数据出现ERROR 1290 (HY000)…