【MySQL经典案例】sql命令练习（EMP表、WOEKS表、COMP表）

数据库设计：商业集团人事管理系统

概述

设计一个商业集团的人事管理系统，该系统将通过三个基本表来管理职工、工作和公司的信息。使用SQL语言来创建这些表，并进行必要的查询操作。

表结构设计

职工表：EMP

工作表：WORKS

公司表：COMP

SQL语句实现

创建表

CREATE TABLE EMP (
    E VARCHAR(10),
    ENAME VARCHAR(50),
    AGE INT,
    SEX VARCHAR(10),
    ECITY VARCHAR(50)
);

CREATE TABLE WORKS (
    E VARCHAR(10),
    C VARCHAR(10),
    SALARY DECIMAL(10, 2)
);

CREATE TABLE COMP (
    C VARCHAR(10),
    CANME VARCHAR(50),
    CITY VARCHAR(50),
    MGR_E VARCHAR(10)
);

插入数据

-- 向EMP表插入数据
INSERT INTO EMP VALUES ('E001', '张三', 45, 'W', '北京');
INSERT INTO EMP VALUES ('E002', '李四', 52, 'W', '上海');
INSERT INTO EMP VALUES ('E003', '王五', 48, 'M', '广州');
INSERT INTO EMP VALUES ('E004', '赵六', 55, 'W', '深圳');
INSERT INTO EMP VALUES ('E005', '钱七', 40, 'M', '杭州');
INSERT INTO EMP VALUES ('E006', '孙八', 35, 'M', '成都');
INSERT INTO EMP VALUES ('E007', '周九', 50, 'M', '重庆');
INSERT INTO EMP VALUES ('E008', '吴十', 42, 'W', '武汉');
INSERT INTO EMP VALUES ('E009', '郑十一', 58, 'M', '西安');
INSERT INTO EMP VALUES ('E010', '王十二', 47, 'M', '南京');
-- 
-- 向WORKS表插入数据
INSERT INTO WORKS VALUES ('E001', 'C001', 5000.00);
INSERT INTO WORKS VALUES ('E002', 'C002', 6000.00);
INSERT INTO WORKS VALUES ('E003', 'C003', 7000.00);
INSERT INTO WORKS VALUES ('E004', 'C004', 8000.00);
INSERT INTO WORKS VALUES ('E005', 'C005', 9000.00);
INSERT INTO WORKS VALUES ('E006', 'C006', 5000.00);
INSERT INTO WORKS VALUES ('E007', 'C007', 6000.00);
INSERT INTO WORKS VALUES ('E008', 'C008', 7000.00);
INSERT INTO WORKS VALUES ('E009', 'C009', 8000.00);
INSERT INTO WORKS VALUES ('E010', 'C010', 9000.00);

-- 向COMP表插入数据
INSERT INTO COMP VALUES ('C001', '联华公司', '北京', 'E001');
INSERT INTO COMP VALUES ('C002', '华联公司', '上海', 'E002');
INSERT INTO COMP VALUES ('C003', '新华公司', '广州', 'E003');
INSERT INTO COMP VALUES ('C004', '联华分公司', '深圳', 'E004');
INSERT INTO COMP VALUES ('C005', '华联分公司', '杭州', 'E005');
INSERT INTO COMP VALUES ('C006', '新华分公司', '成都', 'E006');
INSERT INTO COMP VALUES ('C007', '联华子公司', '重庆', 'E007');
INSERT INTO COMP VALUES ('C008', '华联子公司', '武汉', 'E008');
INSERT INTO COMP VALUES ('C009', '新华子公司', '西安', 'E009');
INSERT INTO COMP VALUES ('C010', '联华集团', '南京', 'E010');

查询操作

1. 检索超过50岁的男职工的工号和姓名。

SELECT E, ENAME FROM EMP WHERE AGE > 50 AND SEX = 'M';

2. 检索每个职工的兼职公司数和总工资。

SELECT E, COUNT(C) AS NUM, SUM(SALARY) AS SUM_SALARY
FROM WORKS
GROUP BY E;

3. 检索联华公司低于本公司职工平均工资的所有职工的工号和姓名。

SELECT
    e.E, e.ENAME
FROM
    works w
    JOIN comp c ON w.C = c.C
    JOIN emp e ON w.E = e.E
WHERE
    c.CANME = '联华公司'
    AND w.SALARY < (
        SELECT AVG(w2.SALARY)
        FROM works w2
        JOIN comp c2 ON w2.C = c2.C
        WHERE c2.CANME = '联华公司'
    )

4. 检索职工人数最多的公司的编号和名称。

SELECT C, CANME
FROM COMP
WHERE (SELECT COUNT(*) FROM WORKS WHERE WORKS.C = COMP.C) =
    (SELECT MAX(cnt) FROM (SELECT COUNT(*) AS cnt FROM WORKS GROUP BY C) AS tmp);
);

5. 检索平均工资高于联华公司平均工资的公司编号和名称。

SELECT C, CANME
FROM COMP
WHERE (SELECT AVG(SALARY) FROM WORKS WHERE C = COMP.C) >
    (SELECT AVG(SALARY) FROM WORKS WHERE C = (SELECT C FROM COMP WHERE CANME = '联华公司'));

6. 为联华公司的职工加薪5%。

UPDATE works
SET SALARY = SALARY * 1.05
WHERE C = (SELECT C FROM COMP WHERE CANME = '联华公司');

7. 在表WORKS中删除年龄大于60岁的职工记录。

DELETE FROM works WHERE E IN (SELECT E FROM EMP WHERE AGE > 60);
SELECT * FROM works

8. 建立一个有关女职工的视图emp_woman，并检索每一个女职工的总工资。

CREATE VIEW emp_woman AS
SELECT E.E, E.ENAME, W.C, C.CANME, W.SALARY
FROM EMP E
JOIN WORKS W ON E.E = W.E
JOIN COMP C ON W.C = C.C
WHERE E.SEX = 'W';

SELECT E, SUM(SALARY) AS TOTAL_SALARY
FROM emp_woman
GROUP BY E;