新增和修改员工的SQL语句

员工表结构假设

在编写SQL语句之前，我们先假设一个基本的员工表结构：

CREATE TABLE employees (
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    department_id INT,
    position VARCHAR(50),
    salary DECIMAL(10, 2),
    hire_date DATE,
    email VARCHAR(100),
    phone VARCHAR(20),
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

以下是员工表结构的可视化表示：

新增员工 (INSERT语句)

基本语法

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

完整字段插入

INSERT INTO employees (employee_id, name, department_id, position, salary, hire_date, email, phone)
VALUES (1, '张三', 3, '软件工程师', 12000.00, '2023-01-15', '[email protected]', '13800138000');

部分字段插入（不包含自增主键）

INSERT INTO employees (name, department_id, position, salary, hire_date, email, phone)
VALUES ('李四', 2, '产品经理', 15000.00, '2022-11-20', '[email protected]', '13900139000');

批量插入多个员工

INSERT INTO employees (name, department_id, position, salary, hire_date, email, phone)
VALUES 
('王五', 1, '数据分析师', 13000.00, '2023-03-10', '[email protected]', '13700137000'),
('赵六', 3, '前端开发', 11000.00, '2023-04-05', '[email protected]', '13600136000'),
('钱七', 2, 'UI设计师', 10000.00, '2023-05-20', '[email protected]', '13500135000');

从其他表插入数据

假设有一个临时表 new_employees 包含新员工数据：

INSERT INTO employees (name, department_id, position, salary, hire_date, email, phone)
SELECT name, department_id, position, salary, hire_date, email, phone
FROM new_employees
WHERE hire_date >= '2023-01-01';

修改员工信息 (UPDATE语句)

基本语法

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

修改单个员工信息

UPDATE employees
SET position = '高级软件工程师', salary = 15000.00
WHERE employee_id = 1;

修改多个字段

UPDATE employees
SET position = '高级产品经理', 
    salary = 18000.00,
    department_id = 1
WHERE employee_id = 2;

使用子查询更新

UPDATE employees
SET salary = salary * 1.1  -- 工资增加10%
WHERE department_id = (
    SELECT department_id 
    FROM departments 
    WHERE department_name = '研发部'
);

基于条件更新多个记录

UPDATE employees
SET salary = salary * 1.05  -- 工资增加5%
WHERE position LIKE '%工程师%' AND hire_date < '2022-01-01';

使用CASE语句进行条件更新

UPDATE employees
SET salary = 
    CASE 
        WHEN position LIKE '%高级%' THEN salary * 1.1
        WHEN position LIKE '%中级%' THEN salary * 1.05
        ELSE salary * 1.02
    END
WHERE department_id = 3;

高级应用

INSERT...ON DUPLICATE KEY UPDATE (MySQL特有)

当插入记录时如果主键或唯一键冲突，则执行更新操作：

INSERT INTO employees (employee_id, name, department_id, position, salary, hire_date, email, phone)
VALUES (1, '张三', 3, '软件工程师', 12000.00, '2023-01-15', '[email protected]', '13800138000')
ON DUPLICATE KEY UPDATE
    name = VALUES(name),
    department_id = VALUES(department_id),
    position = VALUES(position),
    salary = VALUES(salary),
    hire_date = VALUES(hire_date),
    email = VALUES(email),
    phone = VALUES(phone);

MERGE语句 (Oracle, SQL Server等数据库支持)

MERGE INTO employees e
USING (SELECT 1 AS employee_id, '张三' AS name, 3 AS department_id, '高级软件工程师' AS position, 15000.00 AS salary FROM dual) new_emp
ON (e.employee_id = new_emp.employee_id)
WHEN MATCHED THEN
    UPDATE SET 
        e.name = new_emp.name,
        e.department_id = new_emp.department_id,
        e.position = new_emp.position,
        e.salary = new_emp.salary
WHEN NOT MATCHED THEN
    INSERT (employee_id, name, department_id, position, salary, hire_date, email, phone)
    VALUES (new_emp.employee_id, new_emp.name, new_emp.department_id, new_emp.position, new_emp.salary, '2023-01-15', '[email protected]', '13800138000');

使用事务确保数据一致性

BEGIN TRANSACTION;

-- 更新员工表
UPDATE employees
SET department_id = 4
WHERE employee_id = 1;

-- 更新部门统计信息
UPDATE departments
SET employee_count = employee_count + 1
WHERE department_id = 4;

UPDATE departments
SET employee_count = employee_count - 1
WHERE department_id = 3;

COMMIT;
-- 如果出现错误，使用 ROLLBACK 回滚

最佳实践和注意事项

1. 安全性考虑

   • 使用参数化查询或预处理语句防止SQL注入
   • 限制数据库用户的权限，遵循最小权限原则

2. 性能考虑

   • 批量操作时，尽量使用批量INSERT而不是单条循环插入
   • 在UPDATE语句中，确保WHERE子句使用了适当的索引

3. 数据完整性

   • 在重要操作中使用事务确保数据一致性
   • 注意外键约束，避免更新导致的数据不一致

4. 错误处理

   • 检查受影响的行数，确认操作是否成功
   • 捕获并适当处理数据库异常

5. 可读性和维护性

   • 使用有意义的变量名和表名
   • 复杂查询添加适当的注释