请写出新增和修改员工的SQL语句。

lightbulb

题型摘要

SQL中新增员工使用INSERT语句，修改员工使用UPDATE语句。INSERT语句可以插入单条或多条记录，也可以从其他表选择数据插入。UPDATE语句通过WHERE条件指定要更新的记录，可更新一个或多个字段。高级应用包括MySQL的ON DUPLICATE KEY UPDATE和Oracle/SQL Server的MERGE语句，可实现"存在即更新，不存在即插入"的逻辑。重要操作应使用事务确保数据一致性，同时注意SQL注入防护、性能优化和数据完整性。

新增和修改员工的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)
);

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

--- title: 员工表结构 --- erDiagram employees ||--o{ departments : "belongs to" employees { int employee_id PK "员工ID" varchar name "姓名" int department_id FK "部门ID" varchar position "职位" decimal salary "薪资" date hire_date "入职日期" varchar email "电子邮箱" varchar phone "电话号码" } departments { int department_id PK "部门ID" varchar department_name "部门名称" }

新增员工 (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', 'zhangsan@example.com', '13800138000');

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

INSERT INTO employees (name, department_id, position, salary, hire_date, email, phone)
VALUES ('李四', 2, '产品经理', 15000.00, '2022-11-20', 'lisi@example.com', '13900139000');

批量插入多个员工

INSERT INTO employees (name, department_id, position, salary, hire_date, email, phone)
VALUES 
('王五', 1, '数据分析师', 13000.00, '2023-03-10', 'wangwu@example.com', '13700137000'),
('赵六', 3, '前端开发', 11000.00, '2023-04-05', 'zhaoliu@example.com', '13600136000'),
('钱七', 2, 'UI设计师', 10000.00, '2023-05-20', 'qianqi@example.com', '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', 'zhangsan@example.com', '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', 'zhangsan@example.com', '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 回滚