题目
文件
sql文件
密码：1234
结构图
具体步骤
1. 分别创建以上3张数据表。
CREATE TABLE bmb (
部门编号 INT PRIMARY KEY,
部门名称 VARCHAR(20) NOT NULL
);
CREATE TABLE ygb (
工号 VARCHAR(10) PRIMARY KEY,
姓名 VARCHAR(20) NOT NULL,
性别 VARCHAR(2) NOT NULL,
出生日期 DATE NOT NULL,
职务 VARCHAR(20) NOT NULL,
部门编号 INT NOT NULL,
FOREIGN KEY (部门编号) REFERENCES bmb (部门编号)
);
CREATE TABLE gzb (
发放日期 DATE NOT NULL,
工号 VARCHAR(10) NOT NULL,
工资 DECIMAL(10,2) NOT NULL,
PRIMARY KEY (发放日期, 工号),
FOREIGN KEY (工号) REFERENCES ygb (工号)
);
2. 向数据表中分别插入以上数据。
INSERT INTO bmb VALUES
(1001, '办公室'),
(1012, '业务一部');
INSERT INTO ygb VALUES
('J0001', '张合业', '男', '1962-5-15', '总经理', 1001),
('J0092', '黄晓丽', '女', '1970-2-24', '部门经理', 1012),
('J0215', '张圆', '女', '1978-5-22', '职员', 1012),
('J0329', '陈业军', '男', '1981-5-3', '职员', 1012);
INSERT INTO gzb VALUES
('2014-9-5', 'J0001', 6215.68),
('2014-9-5', 'J0092', 4500.50),
('2014-9-5', 'J0215', 3215.89),
('2014-10-12', 'J0001', 6686.45),
('2014-10-12', 'J0092', 4868.52),
('2014-10-12', 'J0215', 3421.27);
3. 查询所有男员工信息。
SELECT * FROM ygb WHERE 性别 = '男';
4. 查询所有员工的工号和姓名。
SELECT 工号, 姓名 FROM ygb;
5. 查询职务为“总经理”的员工名单。
SELECT * FROM ygb WHERE 职务 = '总经理';
6. 查询在1980年之前出生的员工名单，并按照出生日期进行降序排列。
SELECT * FROM ygb WHERE 出生日期 < '1980-01-01' ORDER BY 出生日期 DESC;
7. 查询所有姓“张”的员工名单。
SELECT * FROM ygb WHERE 姓名 LIKE '张%';
8. 查询姓名中第2个字为“业”的员工名单。
SELECT * FROM ygb WHERE 姓名 LIKE '_业%';
9. 统计所有员工人数。
SELECT COUNT(*) FROM ygb;
10. 统计所有发放的最高工资以及最低工资。
SELECT MAX(工资), MIN(工资) FROM gzb;
11. 统计2014-9-5发放的平均工资及工资总额。
SELECT AVG(工资), SUM(工资) FROM gzb WHERE 发放日期 = '2014-9-5';
12. 分组统计男、女员工各自的人数。
SELECT 性别, COUNT(*) FROM ygb GROUP BY 性别;
13. 分组统计各个员工的工号及平均工资。
SELECT 工号, AVG(工资) FROM gzb GROUP BY 工号;
14. 查询“业务一部”所有员工的名单。
SELECT * FROM ygb WHERE 部门编号 = (SELECT 部门编号 FROM bmb WHERE 部门名称 = '业务一部');
15. 查询工号为“J0092”员工的工资情况，字段包括：工号、姓名、职务、发放日期、工资。
SELECT ygb.工号, ygb.姓名, ygb.职务, gzb.发放日期, gzb.工资
FROM ygb INNER JOIN gzb ON ygb.工号 = gzb.工号
WHERE ygb.工号 = 'J0092';
16. 新建一个bmjl，新表的结构与ygb一致，内容包括职务为“部门经理”的员工名单。
CREATE TABLE bmjl AS
SELECT * FROM ygb WHERE 职务 = '部门经理';
17. 在2014-10-12所发放的工资中，将“业务一部”所有员工的工资加500元。
UPDATE gzb SET 工资 = 工资 + 500
WHERE 发放日期 = '2014-10-12' AND 工号 IN (SELECT 工号 FROM ygb WHERE 部门编号 = (SELECT 部门编号 FROM bmb WHERE 部门名称 = '业务一部'));
18. 创建视图V_view1：列出员工的工号、姓名、职务、发放日期、工资。
CREATE VIEW V_view1 AS
SELECT ygb.工号, ygb.姓名, ygb.职务, gzb.发放日期, gzb.工资
FROM ygb INNER JOIN gzb ON ygb.工号 = gzb.工号;
19. 在视图V_view1中查询2014-10-12所发放的工资情况。
SELECT * FROM V_view1 WHERE 发放日期 = '2014-10-12';
20. 创建一个存储过程U_proc1：其功能是显示出部门编号是1012部门的所有员工信息。然后执行该存储过程。
delimiter $$
CREATE PROCEDURE U_proc1()
BEGIN
SELECT * FROM ygb WHERE 部门编号 = 1012;
END;
$$
delimiter ;
CALL U_proc1();
21. 创建一个带有输入参数的存储过程U_proc2：其功能是通过一个给定的工号，显示出该员工的工资发放情况，字段包括：工号、姓名、性别、发放日期、工资。然后执行该存储过程。
delimiter $$
CREATE PROCEDURE U_proc2(IN p_gh VARCHAR(10))
BEGIN
SELECT ygb.工号, ygb.姓名, ygb.性别, gzb.发放日期, gzb.工资
FROM ygb INNER JOIN gzb ON ygb.工号 = gzb.工号
WHERE ygb.工号 = p_gh;
END;
$$
delimiter ;
CALL U_proc2('J0092');
22. 创建一个带有输入参数和输出参数的存储过程U_proc3：其功能是通过一个给定的工号，统计出该员工的平均工资，并通过输出参数进行返回。然后执行该存储过程。
delimiter $$
CREATE PROCEDURE U_proc3(IN p_gh VARCHAR(10), OUT p_avg DECIMAL(10,2))
BEGIN
SELECT AVG(工资) INTO p_avg FROM gzb WHERE 工号 = p_gh;
END;
$$
delimiter ;
CALL U_proc3('J0092', @avg);
SELECT @avg;