Mysql_数据库基础综合练习-公司系统项目_练习

题目

公司人力资源信息系统项目

为了方便管理要求创建一下数据库及数据表：

创建数据库（Company）
部门表（RankInfo）
员工信息表（Employee）

数据库表关系及表结构的介绍

表关系介绍

部门表（RankInfo）

字段名	说明	类型	长度	可否为空	约束
RankID	职级编号	int		否	主键自增长，标识种子和标识增量均为1
RankName	职级名称	nvarchar	20	否
RankLevel	职级等级	nvarchar	2	否

员工信息表（Employee）

字段名	说明	类型	长度	可否为空	约束
EmployeelD	员工编号	int		否	主键自增长，标识种子和标识增量均为1
Name	员工姓名	nvarchar	20	否
Sex	性别	nvarchar	2	否
Age	年龄	int
RankID	职级编号	int			外键
Salary	月基本工资	Decimal(12,2)
Address	工作地	nvarchar	50
Country	国籍	nvarchar	20		默认值:中国

1.添加数据(使用单行和多行两种添加方式),如下所示:

因工作需要,将贺小涵的工作地调到南京
删除王小林员工的所有信息
删除职级编号为6的数据
分别查询两张表中所有数据
查询全部员工的职级编号，要求职级编号唯一(没有重复)
查询所有员工的姓名、月基本工资、以及增长10%之后的月基本工资
查询级别编号为3的职级信息
查询基本工资不低于8000的员工信息
查询职级名称不为“工程师”的所有职级信息
查询年龄大于25岁，且月基本工资高于6000的员工姓名/性别/年龄及基本工资
查询月基本工资高于7000或者职级名称为“技术员”的员工信息并以中文字段显示
查询所有职级信息，按照职级编号升序排列
查询全部员工的员工号、姓名、职级编号和月基本工资信息，按月基本工资降序排列
查询所有员工，要求按职级编号升序排列，若职级编号相同，按照月基本工资降序排列
查询月基本工资最高的5名员工
按月基本工资降序排列，显示前20%的员工
查询员工姓“王”的员工信息
查询职员的工作地点在武汉或南京的职员信息
查询月基本工资在5000 – 10000之间的职员信息
查询年龄在“20”到“25”岁之间的员工信息
计算职员月基本工资总额
查询最高月基本工资公司职员
查询公司职员的最低月基本工资
查询公司年龄最大的职员
查询公司职员月基本工资的平均值
统计公司职员的总人数
统计统计年龄大于28的公司职员的人数
统计员工月基本工资超过10000元的员工数
查询公司职员总人数、平均月基本工资、最高月基本工资和最低月基本工资
查询职级为3的职员人数、平均月基本公司、最高月基本工资和最低月基本工资
按职级分组计算员工数、月基本工资平均值和月基本工资总额，按员工数升序排列，如果员工数相同，则按月平均工资升序排列
查询每个职级的员工的最高月基本工资，按职级号升序排列
获取不同职级的员工在不同城市工作的人数，按员工职级升序排列
获取在不同城市工作的男女员工人数，按性别升序排列，如果性别相同，则按员工人数升序排列
查询每个职级的员工最高月基本工资大于8000 的职级工资信息（显示职级号和最高月基本工资），按职级号升序排列
用内联接查询员工姓名、职级号、职级名和月基本工资
使用左外联接查询员工姓名、职级号和职级名和月基本工资
使用右外联接查询员工姓名、职级号和职级名和月基本工资

解答（仅供参考，不一定对）

CREATE DATABASE IF NOT EXISTS Company;
USE Company;

CREATE TABLE IF NOT EXISTS RankInfo (
    RankID INT NOT NULL AUTO_INCREMENT COMMENT '职级编号',
    RankName NVARCHAR(20) NOT NULL COMMENT '职级名称',
    RankLevel NVARCHAR(2) NOT NULL COMMENT '职级等级',
    PRIMARY KEY (RankID)
);

CREATE TABLE IF NOT EXISTS Employee (
    EmployeeID INT NOT NULL AUTO_INCREMENT COMMENT '员工编号 ',
    Name NVARCHAR(20) NOT NULL COMMENT '员工姓名 ',
    Sex NVARCHAR(2) NOT NULL COMMENT '性别',
    Age INT COMMENT '年龄',
    RankID INT COMMENT '职级编号',
    Salary DECIMAL(12,2) COMMENT '月基本工资',
    Address NVARCHAR(50) COMMENT '工作地',
    Country NVARCHAR(20) DEFAULT '中国' COMMENT '国籍',
    PRIMARY KEY (EmployeeID),
    FOREIGN KEY (RankID) REFERENCES RankInfo(RankID)
);

-- 1. 添加数据(使用单行和多行两种添加方式),如下所示:
INSERT INTO RankInfo (RankName, RankLevel) VALUES ('总经理', 'A');
INSERT INTO RankInfo (RankName, RankLevel) VALUES ('总监', 'B');
INSERT INTO RankInfo (RankName, RankLevel) VALUES ('经理', 'C');
INSERT INTO RankInfo (RankName, RankLevel) VALUES ('主管', 'E');
INSERT INTO RankInfo (RankName, RankLevel) VALUES ('组长', 'F');
INSERT INTO RankInfo (RankName, RankLevel) VALUES ('行政', 'G');
INSERT INTO RankInfo (RankName, RankLevel) VALUES ('技术员', 'H');
INSERT INTO RankInfo (RankName, RankLevel) VALUES ('工程师', 'H');

INSERT INTO Employee (`Name`, `Sex`, `Age`, `RankID`, `Salary`, `Address`) VALUES 
('陈小芳', '男', 29, 1, 18000.00, '湖北'),
('冯哒哒', '女', 21, 7, 5200.00, '南京'),
('龚小旋', '女', 26, 3, 8000.00, '北京'),
('海小洋', '男', 30, 1, 21000.00, '湖北'),
('贺小涵', '男', 29, 5, 5500.00, '南京'),
('王二狗', '女', 22, 5, 5600.00, '南京'),
('王小胜', '男', 28, 2, 7000.00, '湖北'),
('徐娇娇', '女', 25, 4, 7500.00, '湖北'),
('颜小玉', '男', 27, 4, 7000.00, '湖北'),
('易小飞', '男', 30, 2, 15000.00, '湖北'),
('余小兰', '女', 23, 7, 2500.00, '湖北'),
('张伟', '男', 18, 7, NULL, '深圳');

-- 2. 因工作需要,将贺小涵的工作地调到南京
UPDATE Employee SET Address = '南京' WHERE Name = '贺小涵';
-- 3. 删除王小林员工的所有信息
DELETE FROM Employee WHERE Name = '王小林';
-- 4. 删除职级编号为 6 的数据
DELETE FROM RankInfo WHERE RankID = 6;
-- 5. 分别查询两张表中所有数据
SELECT * FROM RankInfo;
SELECT * FROM Employee;
-- 6. 查询全部员工的职级编号，要求职级编号唯一(没有重复)
SELECT DISTINCT RankID FROM Employee;
-- 7. 查询所有员工的姓名、月基本工资、以及增长 10%之后的月基本工资
SELECT Name, Salary, Salary * 1.1 AS NewSalary FROM Employee;
-- 8. 查询级别编号为 3 的职级信息
SELECT * FROM Employee WHERE RankID = '3';
-- 9. 查询基本工资不低于 8000 的员工信息
SELECT * FROM Employee WHERE Salary >= 8000;
-- 10.查询职级名称不为“工程师”的所有职级信息
SELECT * FROM RankInfo WHERE RankName != '工程师';
-- 11.查询年龄大于 25 岁，且月基本工资高于 6000 的员工姓名/性别/年龄及基本工资
SELECT Name, Sex, Age, Salary FROM Employee WHERE Age > 25 AND Salary > 6000;
-- 12.查询月基本工资高于 7000 或者职级名称为“技术员”的员工信息并以中文字段显示
SELECT * FROM Employee WHERE Salary > 7000 OR RankID = (SELECT RankID FROM RankInfo WHERE RankName = '技术员');
-- 13.查询所有职级信息，按照职级编号升序排列
SELECT * FROM RankInfo ORDER BY RankID ASC;
-- 14.查询全部员工的员工号、姓名、职级编号和月基本工资信息，按月基本工资降序排列
SELECT EmployeeID, Name, RankID, Salary FROM Employee ORDER BY Salary DESC;
-- 15.查询所有员工，要求按职级编号升序排列，若职级编号相同，按照月基本工资降序排列
SELECT * FROM Employee ORDER BY RankID ASC, Salary DESC;
-- 16.查询月基本工资最高的 5 名员工
SELECT * FROM Employee ORDER BY Salary DESC LIMIT 5;
-- 17.按月基本工资降序排列，显示前 20%的员工
-- SELECT * FROM Employee ORDER BY Salary DESC LIMIT (SELECT COUNT(*) * 0.2 FROM Employee);
-- 18.查询员工姓“王”的员工信息
SELECT * FROM Employee WHERE Name LIKE '王%';
-- 19.查询职员的工作地点在武汉或南京的职员信息
SELECT * FROM Employee WHERE Address IN ('武汉', '南京');
-- 20.查询月基本工资在 5000 - 10000 之间的职员信息
SELECT * FROM Employee WHERE Salary BETWEEN 5000 AND 10000;
-- 21.查询年龄在“20”到“25”岁之间的员工信息
SELECT * FROM Employee WHERE Age BETWEEN 20 AND 25;
-- 22.计算职员月基本工资总额
SELECT SUM(Salary) AS 基本工资总额 FROM Employee;
-- 23.查询最高月基本工资公司职员
SELECT MAX(Salary) AS 最高月基本工资 FROM Employee;
-- 24.查询公司职员的最低月基本工资
SELECT MIN(Salary) AS 最低月基本工资 FROM Employee;
-- 25.查询公司年龄最大的职员
SELECT * FROM Employee ORDER BY Age DESC LIMIT 1;
-- 26.查询公司职员月基本工资的平均值
SELECT AVG(Salary) AS 平均基本工资 FROM Employee;
-- 27.统计公司职员的总人数
SELECT COUNT(*) AS 总人数 FROM Employee;
-- 28.统计统计年龄大于 28 的公司职员的人数
SELECT COUNT(*) AS 人数 FROM Employee WHERE Age > 28;
-- 29.统计员工月基本工资超过 10000 元的员工数
SELECT COUNT(*) AS 人数 FROM Employee WHERE Salary > 10000;
-- 30.查询公司职员总人数、平均月基本工资、最高月基本工资和最低月基本工资
SELECT 
    COUNT(*) AS 总人数,
    AVG(Salary) AS 平均月基本工资,
    MAX(Salary) AS 最高月基本工资,
    MIN(Salary) AS 最低月基本工资
FROM Employee;
-- 31.查询职级为 3 的职员人数、平均月基本公司、最高月基本工资和最低月基本工资
SELECT 
    COUNT(*) AS 人数,
    AVG(Salary) AS 平均月基本工资,
    MAX(Salary) AS 最高月基本工资,
    MIN(Salary) AS 最低月基本工资
FROM 
    Employee
WHERE 
    RankID = 3;
-- 32.按职级分组计算员工数、月基本工资平均值和月基本工资总额，按员工数升序排列，如果员工数相同，则按月平均工资升序排列
SELECT 
    RankID,
    COUNT(*) AS 人数,
    AVG(Salary) AS 平均月基本工资,
    SUM(Salary) AS 月基本工资总额
FROM 
    Employee
GROUP BY 
    RankID
ORDER BY 
    月基本工资总额 ASC, 
    平均月基本工资 ASC;
-- 33.查询每个职级的员工的最高月基本工资，按职级号升序排列
SELECT 
    RankID, 
    MAX(Salary) AS 最高月基本工资
FROM Employee
GROUP BY RankID
ORDER BY RankID ASC;
-- 34.获取不同职级的员工在不同城市工作的人数，按员工职级升序排列
SELECT 
    RankID, 
    Address, 
    COUNT(*) AS 人数
FROM Employee
GROUP BY RankID, Address
ORDER BY RankID ASC;
-- 35.获取在不同城市工作的男女员工人数，按性别升序排列，如果性别相同，则按员工人数升序排列
SELECT 
    Sex, 
    Address, 
    COUNT(*) AS EmployeeCount
FROM Employee
GROUP BY Sex, Address
ORDER BY Sex ASC, EmployeeCount ASC;
-- 36.查询每个职级的员工最高月基本工资大于 8000 的职级工资信息（显示职级号和最高月基本工资），按职级号升序排列
SELECT 
    RankID, 
    MAX(Salary) AS 最高月基本工资
FROM Employee
GROUP BY RankID
HAVING MAX(Salary) > 8000
ORDER BY RankID ASC;
-- 37.用内联接查询员工姓名、职级号、职级名和月基本工资
SELECT 
    e.Name, 
    e.RankID, 
    r.RankName, 
    e.Salary
FROM Employee e
JOIN RankInfo r ON e.RankID = r.RankID;
-- 38.使用左外联接查询员工姓名、职级号和职级名和月基本工资
SELECT 
    e.Name, 
    e.RankID, 
    r.RankName, 
    e.Salary
FROM Employee e
LEFT JOIN RankInfo r ON e.RankID = r.RankID;
-- 39.使用右外联接查询员工姓名、职级号和职级名和月基本工资
SELECT 
    e.Name, 
    e.RankID, 
    r.RankName, 
    e.Salary
FROM Employee e
RIGHT JOIN RankInfo r ON e.RankID = r.RankID;

温馨提示：本文最后更新于2024-05-13 18:39:41，某些文章具有时效性，若有错误或已失效，请在下方留言或联系站长。

本站未注明转载的文章均为原创，并采用 CC BY-NC-SA 4.0 授权协议，转载请注明来源，谢谢！

分享是一种美德，当你分享时请你附带上本文链接。

THE END