职工库，职工EMP表，部门DEPT表，相关查询与操作

项目背景介绍：本次的数据库设计主要涉及到职工信息、各部门信息的相关查询与操作。在 MySQL 中，如果你需要在 UPDATE 语句中使用子查询，并且这个子查询引用了正在被更新的表（在这个例子中是 EMP 表），你会遇到错误 1093。为了解决这个问题，你可以使用一个临时表或者派生表来间接地引用这个表。

任务概述：职工库，职工EMP表，部门DEPT表，相关查询与操作

具体关系数据库如下：

已知一个关系数据库：职工库的模式如下：

职工EMP（职工号，姓名，工资，所在部门）

部门DEPT（部门号，部门名，部门经理的职工号）

请用SQL语句完成以下功能： 

（1）、使用查询分析器建表，要求如下：

　　A、每个表的主外码。

　　B、职工姓名和部门名不能为空。

　　C、工资取800到5000之间整数。

（2）、插入如下数据：

职工（E01，汤姆，4000，D02

　　E02，丹尼，3000，D02

　　E03，约韩，1000，D01

　　E04，迪克，5000,  D03

    E05,  比尔，3500，D04）

部门（D01，人事，E04

　　　D02，信息，E01

　　　D03，销售，E05

　　　D04，财务，E06）

创建表

#建表
CREATE TABLE DEPT (  
    部门号 CHAR(3) PRIMARY KEY,  
    部门名 VARCHAR(50) NOT NULL,  
    部门经理的职工号 CHAR(3)
);  
  
CREATE TABLE EMP (  
    职工号 CHAR(3) PRIMARY KEY,  
    姓名 VARCHAR(50) NOT NULL,  
    工资 INT CHECK (工资 BETWEEN 800 AND 5000),  
    所在部门 CHAR(3),  
    FOREIGN KEY (所在部门) REFERENCES DEPT(部门号)  
);

插入数据

#插入数据
INSERT INTO DEPT VALUES ('D01', '人事', 'E04');  
INSERT INTO DEPT VALUES ('D02', '信息', 'E01');  
INSERT INTO DEPT VALUES ('D03', '销售', 'E05');  
INSERT INTO DEPT VALUES ('D04', '财务', 'E06');  
  
INSERT INTO EMP VALUES ('E01', '汤姆', 4000, 'D02');  
INSERT INTO EMP VALUES ('E02', '丹尼', 3000, 'D02');  
INSERT INTO EMP VALUES ('E03', '约韩', 1000, 'D01');  
INSERT INTO EMP VALUES ('E04', '迪克', 5000, 'D03');  
INSERT INTO EMP VALUES ('E05', '比尔', 3500, 'D04');

预览：

（3）、查找出哪些工资高于其所在部门经理工资的职工的姓名及其工资。

SELECT E.姓名, E.工资  
FROM EMP E  
JOIN DEPT D ON E.所在部门 = D.部门号  
WHERE E.工资 > (SELECT 工资 FROM EMP WHERE 职工号 = D.部门经理的职工号);

查询结果：

（4）、将D01号部门经理的工资改为该部门所有职工的平均工资。

特别注意：

在 MySQL 中，如果你需要在 UPDATE 语句中使用子查询，并且这个子查询引用了正在被更新的表（在这个例子中是 EMP 表），你会遇到错误 1093。为了解决这个问题，你可以使用一个临时表或者派生表来间接地引用这个表。

以下是一个修改后的 SQL 语句，它使用了一个派生表来避免直接引用 EMP 表：

UPDATE EMP  
SET 工资 = (  
    SELECT avg_salary  
    FROM (  
        SELECT AVG(工资) AS avg_salary  
        FROM EMP  
        WHERE 所在部门 = 'D01'  
    ) AS avg_salaries  
)  
WHERE 职工号 IN (  
    SELECT 部门经理的职工号  
    FROM DEPT  
    WHERE 部门号 = 'D01'  
);

（5）、新增加一个广告部门，编号为D06。

INSERT INTO DEPT VALUES ('D06', '广告', NULL);

（6）、将工资低于平均工资的职工工资增加300元。

特别注意：

在 MySQL 中，由于你不能直接在 UPDATE 语句的 WHERE 子句中使用对同一个表的子查询，你需要采用另一种方法来绕过这个限制。

一个常见的解决方案是使用一个临时表或者一个派生表（即一个子查询在 FROM 子句中作为一个临时表）。

然而，对于你的具体需求，最简单的方法可能是先计算平均工资，然后将其存储在一个变量中，接着使用这个变量来执行 UPDATE 操作。

这段代码首先计算了 EMP 表中所有员工的平均工资，并将其存储在名为 @avg_salary 的 MySQL 用户定义变量中。然后，它使用这个变量来找出工资低于平均值的员工，并将他们的工资增加 300。

这种方法避免了直接在 UPDATE 语句中使用子查询，从而避免了 MySQL 的限制，并且能够正确地执行所需的工资更新操作。

-- 计算平均工资并存储在变量中  
SET @avg_salary = (SELECT AVG(工资) FROM EMP);  
  
-- 使用变量来更新工资  
UPDATE EMP  
SET 工资 = 工资 + 300  
WHERE 工资 < @avg_salary;