13 KiB
13 KiB
语法结构
DELIMITER //
CREATE PROCEDURE GetEmployeeCountByDept (
IN dept_id INT,
OUT employee_count INT
)
BEGIN
SELECT COUNT(*) INTO employee_count
FROM employee
WHERE dept_id = dept_id;
END //
DELIMITER ;
IF condition THEN
-- 条件为真时执行的代码
ELSEIF another_condition THEN
-- 另一个条件为真时执行的代码
ELSE
-- 所有条件都不满足时执行的代码
END IF;
CASE expression
WHEN value1 THEN
-- 当 expression = value1 时执行的代码
WHEN value2 THEN
-- 当 expression = value2 时执行的代码
ELSE
-- 当 expression 不匹配任何值时执行的代码
END CASE;
CASE
WHEN condition1 THEN
-- 当 condition1 为真时执行的代码
WHEN condition2 THEN
-- 当 condition2 为真时执行的代码
ELSE
-- 当所有条件都不满足时执行的代码
END CASE;
表结构与数据
-- 创建数据库
CREATE DATABASE company CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- 使用数据库
USE company;
-- 创建 department 表
CREATE TABLE department (
dept_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '部门ID，主键',
dept_name VARCHAR(100) NOT NULL COMMENT '部门名称',
location VARCHAR(100) NOT NULL COMMENT '部门所在地'
);
-- 创建 job 表
CREATE TABLE job (
job_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '职位ID，主键',
job_title VARCHAR(100) NOT NULL COMMENT '职位名称',
min_salary DECIMAL(10, 2) NOT NULL COMMENT '最低工资',
max_salary DECIMAL(10, 2) NOT NULL COMMENT '最高工资'
);
-- 创建 employee 表
CREATE TABLE employee (
emp_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '员工ID，主键',
emp_name VARCHAR(100) NOT NULL COMMENT '员工姓名',
salary DECIMAL(10, 2) NOT NULL COMMENT '员工工资',
hire_date DATE NOT NULL COMMENT '入职日期',
dept_id INT COMMENT '部门ID，外键',
job_id INT COMMENT '职位ID，外键',
FOREIGN KEY (dept_id) REFERENCES department(dept_id),
FOREIGN KEY (job_id) REFERENCES job(job_id)
);
INSERT INTO department (dept_name, location) VALUES
('HR', 'New York'),
('IT', 'San Francisco'),
('Finance', 'Chicago'),
('Marketing', 'Los Angeles'),
('Sales', 'Boston'),
('Operations', 'Houston'),
('Research', 'Seattle'),
('Customer Support', 'Austin');
INSERT INTO job (job_title, min_salary, max_salary) VALUES
('Manager', 60000.00, 100000.00),
('Developer', 50000.00, 80000.00),
('Analyst', 45000.00, 70000.00),
('Designer', 40000.00, 65000.00),
('Sales Executive', 35000.00, 60000.00),
('HR Specialist', 40000.00, 70000.00),
('Finance Analyst', 50000.00, 75000.00),
('Marketing Coordinator', 45000.00, 70000.00),
('Operations Manager', 55000.00, 90000.00),
('Research Scientist', 60000.00, 95000.00),
('Customer Support Representative', 35000.00, 55000.00);
INSERT INTO employee (emp_name, salary, hire_date, dept_id, job_id) VALUES
('Alice', 70000.00, '2020-01-15', 1, 1),
('Bob', 65000.00, '2019-03-22', 2, 2),
('Charlie', 55000.00, '2018-07-30', 2, 3),
('David', 60000.00, '2021-05-10', 3, 4),
('Eva', 50000.00, '2017-11-05', 4, 5),
('Frank', 75000.00, '2016-09-12', 5, 1),
('Grace', 48000.00, '2022-02-20', 1, 2),
('Henry', 52000.00, '2021-08-15', 2, 3),
('Ivy', 58000.00, '2020-04-10', 3, 4),
('Jack', 62000.00, '2019-12-01', 4, 5),
('Katie', 53000.00, '2021-07-25', 5, 6),
('Leo', 67000.00, '2018-05-18', 6, 7),
('Mona', 49000.00, '2020-11-30', 7, 8),
('Nina', 71000.00, '2019-02-14', 8, 9),
('Oscar', 56000.00, '2022-03-05', 1, 10),
('Paul', 63000.00, '2021-09-12', 2, 11),
('Quincy', 54000.00, '2020-06-20', 3, 1),
('Rachel', 59000.00, '2019-08-15', 4, 2),
('Steve', 68000.00, '2018-12-10', 5, 3),
('Tina', 51000.00, '2022-01-22', 6, 4),
('Uma', 72000.00, '2021-04-18', 7, 5),
('Victor', 57000.00, '2020-07-30', 8, 6),
('Wendy', 64000.00, '2019-10-05', 1, 7),
('Xander', 50000.00, '2022-05-12', 2, 8),
('Yara', 69000.00, '2021-03-25', 3, 9),
('Zack', 55000.00, '2020-09-15', 4, 10),
('Amy', 61000.00, '2019-06-20', 5, 11),
('Brian', 74000.00, '2018-04-10', 6, 1),
('Cathy', 52000.00, '2022-07-18', 7, 2),
('Derek', 67000.00, '2021-12-01', 8, 3);
练习题
练习题 1: 插入新员工
创建一个存储过程 add_employee，接受员工姓名、工资、入职日期、部门ID和职位ID作为参数，并将其插入到 employee 表中。
DELIMITER $$
DROP PROCEDURE IF EXISTS add_employee;
CREATE PROCEDURE add_employee(IN emp_name VARCHAR(100), IN salary DECIMAL(10,2), IN hire_date DATE, IN dept_id INT, IN job_id INT)
BEGIN
INSERT INTO employee()
VALUES(DEFAULT, emp_name, salary, hire_date, dept_id, job_id);
END $$
DELIMITER ;
CALL add_employee('李泽龙', 6666, '2025-3-20', 1, 1);
练习题 2: 更新员工工资
创建一个存储过程 update_employee_salary，接受员工ID和新的工资作为参数，更新该员工的工资。
DELIMITER $$
DROP PROCEDURE IF EXISTS update_employee_salary;
CREATE PROCEDURE update_employee_salary(IN emp_id INT, IN salary DECIMAL(10, 2))
BEGIN
UPDATE employee e
SET e.salary = salary
WHERE e.emp_id = emp_id;
END $$
DELIMITER ;
CALL update_employee_salary(1, 60000);
练习题 3: 删除员工
创建一个存储过程 delete_employee，接受员工ID作为参数，删除该员工的记录。
DELIMITER $$
DROP PROCEDURE IF EXISTS delete_employee;
CREATE PROCEDURE delete_employee(IN id INT)
BEGIN
DELETE FROM employee
WHERE emp_id = id;
END $$
DELIMITER ;
CALL delete_employee(1);
练习题 4: 查询部门员工
创建一个存储过程 get_department_employees，接受部门ID作为参数，返回该部门所有员工的详细信息。
DELIMITER $$
DROP PROCEDURE IF EXISTS get_department_employees;
CREATE PROCEDURE get_department_employees(IN id INT)
BEGIN
SELECT e.*
FROM employee e
WHERE e.dept_id = id;
END $$
DELIMITER ;
CALL get_department_employees(1);
练习题 5: 查询职位员工
创建一个存储过程 get_job_employees，接受职位ID作为参数，返回该职位所有员工的详细信息。
DELIMITER $$
DROP PROCEDURE IF EXISTS get_job_employees;
CREATE PROCEDURE get_job_employees(IN id INT)
BEGIN
SELECT e.*
FROM employee e
WHERE e.job_id = id;
END $$
DELIMITER ;
CALL get_job_employees(1);
练习题 6: 计算部门平均工资
创建一个存储过程 calculate_avg_salary_by_dept，接受部门ID作为参数，返回该部门的平均工资。
DELIMITER $$
DROP PROCEDURE IF EXISTS calculate_avg_salary_by_dept;
CREATE PROCEDURE calculate_avg_salary_by_dept(IN id INT)
BEGIN
SELECT AVG(e.salary)
FROM employee e
WHERE e.dept_id = id;
END $$
DELIMITER ;
CALL calculate_avg_salary_by_dept(1);
练习题 7: 计算职位平均工资
创建一个存储过程 calculate_avg_salary_by_job，接受职位ID作为参数，返回该职位的平均工资。
DELIMITER $$
DROP PROCEDURE IF EXISTS calculate_avg_salary_by_job;
CREATE PROCEDURE calculate_avg_salary_by_job(IN id INT)
BEGIN
SELECT AVG(e.salary)
FROM employee e
WHERE e.job_id = id;
END $$
DELIMITER ;
CALL calculate_avg_salary_by_job(1);
练习题 8: 查询高薪员工
创建一个存储过程 get_high_salary_employees，接受一个工资阈值作为参数，返回所有工资高于该阈值的员工。
DELIMITER $$
DROP PROCEDURE IF EXISTS get_high_salary_employees;
CREATE PROCEDURE get_high_salary_employees(IN sal DECIMAL(10, 2))
BEGIN
SELECT e.*
FROM employee e
WHERE e.salary > sal;
END $$
DELIMITER ;
CALL get_high_salary_employees(50000);
练习题 9: 查询员工工龄
创建一个存储过程 get_employee_years_of_service，接受员工ID作为参数，返回该员工的工龄（以年为单位）。
DELIMITER $$
DROP PROCEDURE IF EXISTS get_employee_years_of_service;
CREATE PROCEDURE get_employee_years_of_service(IN id INT)
BEGIN
SELECT -TIMESTAMPDIFF(YEAR,NOW(),e.hire_date)
FROM employee e
WHERE e.emp_id = id;
END $$
DELIMITER ;
CALL get_employee_years_of_service(1);
练习题 10: 查询部门员工总数
创建一个存储过程 get_department_employee_count，接受部门ID作为参数，返回该部门的员工总数。
DELIMITER $$
DROP PROCEDURE IF EXISTS get_department_employee_count;
CREATE PROCEDURE get_department_employee_count(IN id INT)
BEGIN
SELECT COUNT(*)
FROM employee e
WHERE e.dept_id = id;
END $$
DELIMITER ;
CALL get_department_employee_count(1);
练习题 11: 查询职位员工总数
创建一个存储过程 get_job_employee_count，接受职位ID作为参数，返回该职位的员工总数。
DELIMITER $$
DROP PROCEDURE IF EXISTS get_job_employee_count;
CREATE PROCEDURE get_job_employee_count(IN id INT)
BEGIN
SELECT COUNT(*)
FROM employee e
WHERE e.job_id = id;
END $$
DELIMITER ;
CALL get_job_employee_count(1);
练习题 12: 查询员工详细信息
创建一个存储过程 get_employee_details，接受员工ID作为参数，返回该员工的姓名、工资、部门名称和职位名称。
DELIMITER $$
DROP PROCEDURE IF EXISTS get_employee_details;
CREATE PROCEDURE get_employee_details(IN emp_id INT)
BEGIN
SELECT e.emp_name, e.salary, d.dept_name, j.job_title
FROM employee e, department d, job j
WHERE e.emp_id = emp_id AND e.dept_id = d.dept_id AND e.job_id = j.job_id;
END $$
DELIMITER ;
CALL get_employee_details(1);
练习题 13: 查询部门最高工资
创建一个存储过程 get_department_max_salary，接受部门ID作为参数，返回该部门的最高工资。
DELIMITER $$
DROP PROCEDURE IF EXISTS get_department_max_salary;
CREATE PROCEDURE get_department_max_salary(IN id INT)
BEGIN
SELECT MAX(e.salary)
FROM employee e
WHERE e.dept_id = id;
END $$
DELIMITER ;
CALL get_department_max_salary(1);
练习题 14: 查询职位最低工资
创建一个存储过程 get_job_min_salary，接受职位ID作为参数，返回该职位的最低工资。
DELIMITER $$
DROP PROCEDURE IF EXISTS get_job_min_salary;
CREATE PROCEDURE get_job_min_salary(IN id INT)
BEGIN
SELECT MIN(e.salary)
FROM employee e
WHERE e.dept_id = id;
END $$
DELIMITER ;
CALL get_job_min_salary(1);
练习题 15: 查询员工工资排名
创建一个存储过程 get_employee_salary_rank，接受员工ID作为参数，返回该员工在公司中的工资排名。
DELIMITER $$
DROP PROCEDURE IF EXISTS get_employee_salary_rank;
CREATE PROCEDURE get_employee_salary_rank(IN emp_id INT)
BEGIN
SELECT COUNT(*) + 1
FROM employee e
WHERE e.salary > (
SELECT e.salary
FROM employee e
WHERE e.emp_id = emp_id
);
END $$
DELIMITER ;
CALL get_employee_salary_rank(2);
练习题 16: 查询部门工资总和
创建一个存储过程 get_department_total_salary，接受部门ID作为参数，返回该部门所有员工的工资总和。
DELIMITER $$
DROP PROCEDURE IF EXISTS get_department_total_salary;
CREATE PROCEDURE get_department_total_salary(IN id INT)
BEGIN
SELECT SUM(e.salary)
FROM employee e
WHERE e.dept_id = id;
END $$
DELIMITER ;
CALL get_department_total_salary(1);
练习题 17: 查询职位工资总和
创建一个存储过程 get_job_total_salary，接受职位ID作为参数，返回该职位所有员工的工资总和。
DELIMITER $$
DROP PROCEDURE IF EXISTS get_job_total_salary;
CREATE PROCEDURE get_job_total_salary(IN id INT)
BEGIN
SELECT SUM(e.salary)
FROM employee e
WHERE e.job_id = id;
END $$
DELIMITER ;
CALL get_job_total_salary(1);
练习题 18: 查询员工入职年份分布
创建一个存储过程 get_employees_by_hire_year，接受年份作为参数，返回该年份入职的所有员工。
DELIMITER $$
DROP PROCEDURE IF EXISTS get_employees_by_hire_year;
CREATE PROCEDURE get_employees_by_hire_year(IN y INT)
BEGIN
SELECT *
FROM employee e
WHERE YEAR(e.hire_date) = y;
END $$
DELIMITER ;
CALL get_employees_by_hire_year(2020);
练习题 19: 查询部门工资分布
创建一个存储过程 get_department_salary_distribution，接受部门ID作为参数，返回该部门工资的分布情况（如最高、最低、平均工资）。
DELIMITER $$
DROP PROCEDURE IF EXISTS get_department_salary_distribution;
CREATE PROCEDURE get_department_salary_distribution(IN id INT)
BEGIN
SELECT MAX(e.salary), MIN(e.salary), AVG(e.salary)
FROM employee e
WHERE e.dept_id = id;
END $$
DELIMITER ;
CALL get_department_salary_distribution(1);
练习题 20: 查询职位工资分布
创建一个存储过程 get_job_salary_distribution，接受职位ID作为参数，返回该职位工资的分布情况（如最高、最低、平均工资）。
DELIMITER $$
DROP PROCEDURE IF EXISTS get_job_salary_distribution;
CREATE PROCEDURE get_job_salary_distribution(IN id INT)
BEGIN
SELECT MAX(e.salary), MIN(e.salary), AVG(e.salary)
FROM employee e
WHERE e.job_id = id;
END $$
DELIMITER ;
CALL get_job_salary_distribution(1);