|
|
-- 文件名：data_update_scripts.sql
|
|
|
-- 这是为员工考勤管理系统编写的更新操作（UPDATE）和删除操作（DELETE）SQL脚本
|
|
|
|
|
|
-- 1. 更新操作（UPDATE）
|
|
|
|
|
|
-- 更新员工“孙晓”的职位为“高级软件工程师”
|
|
|
UPDATE Employee
|
|
|
SET Position = '高级软件工程师'
|
|
|
WHERE Name = '孙晓';
|
|
|
|
|
|
-- 将“杨洋”的入职日期更新为“2024-06-01”
|
|
|
UPDATE Employee
|
|
|
SET HireDate = '2024-06-01'
|
|
|
WHERE Name = '杨洋';
|
|
|
|
|
|
-- 更新部门ID为‘D003’的部门主管为‘刘晨’
|
|
|
UPDATE Department
|
|
|
SET ManagerID = 'E008'
|
|
|
WHERE DepartmentID = 'D003';
|
|
|
|
|
|
-- 更新员工“赵丽”的联系方式为“12345678911”
|
|
|
UPDATE Employee
|
|
|
SET ContactInfo = '12345678911'
|
|
|
WHERE Name = '赵丽';
|
|
|
|
|
|
-- 修改“2024-12-02”至“2024-12-06”期间的所有请假记录状态为“已批准”
|
|
|
UPDATE LeaveRequest
|
|
|
SET RequestStatus = '已批准'
|
|
|
WHERE StartDate BETWEEN '2024-12-02' AND '2024-12-06';
|
|
|
|
|
|
-- 更新2024年12月1日员工“王强”的考勤状态为“迟到”
|
|
|
UPDATE Attendance
|
|
|
SET AttendanceStatus = '迟到'
|
|
|
WHERE EmployeeID = 'E003' AND AttendanceDate = '2024-12-01';
|
|
|
|
|
|
-- 修改“张伟”在“2024-06-01”到“2024-06-30”期间的所有假期天数为3天
|
|
|
UPDATE LeaveRequest
|
|
|
SET LeaveDays = 3
|
|
|
WHERE EmployeeID = 'E001' AND StartDate BETWEEN '2024-06-01' AND '2024-06-30';
|
|
|
|
|
|
-- 将“刘晨”所有加班时长更新为0
|
|
|
UPDATE Attendance
|
|
|
SET OvertimeHours = 0
|
|
|
WHERE EmployeeID = 'E008';
|
|
|
|
|
|
-- 为员工“赵丽”设置新的密码
|
|
|
UPDATE UserLogin
|
|
|
SET Password = 'new_password123'
|
|
|
WHERE EmployeeID = 'E004';
|
|
|
|
|
|
-- 2. 删除操作（DELETE）
|
|
|
|
|
|
-- 删除员工“李娜”的所有考勤记录
|
|
|
DELETE FROM Attendance
|
|
|
WHERE EmployeeID = 'E002';
|
|
|
|
|
|
-- 删除所有状态为“已拒绝”的假期申请记录
|
|
|
DELETE FROM LeaveRequest
|
|
|
WHERE RequestStatus = '已拒绝';
|
|
|
|
|
|
-- 删除所有职位为“技术总监”的员工记录
|
|
|
DELETE FROM Employee
|
|
|
WHERE Position = '技术总监';
|
|
|
|
|
|
-- 删除部门为“市场部”的所有员工
|
|
|
DELETE FROM Employee
|
|
|
WHERE DepartmentID = 'D004';
|
|
|
|
|
|
-- 删除2024年1月1日到2024年1月31日期间所有请假记录
|
|
|
DELETE FROM LeaveRequest
|
|
|
WHERE StartDate BETWEEN '2024-12-01' AND '2024-12-05';
|
|
|
|
|
|
-- 删除所有考勤记录为“早退”的记录
|
|
|
DELETE FROM Attendance
|
|
|
WHERE AttendanceStatus = '早退';
|
|
|
|
|
|
-- 删除所有2024年12月1日到2024年12月15日已批准的年假记录
|
|
|
DELETE FROM LeaveRequest
|
|
|
WHERE LeaveType = '年假' AND RequestStatus = '已批准' AND StartDate BETWEEN '2024-12-01' AND '2024-12-15';
|
