530 lines
13 KiB
Markdown
530 lines
13 KiB
Markdown
### 语法结构
|
||
|
||
```sql
|
||
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;
|
||
```
|
||
|
||
|
||
|
||
---
|
||
|
||
### 表结构与数据
|
||
|
||
```sql
|
||
-- 创建数据库
|
||
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` 表中。
|
||
|
||
```sql
|
||
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和新的工资作为参数，更新该员工的工资。
|
||
|
||
```sql
|
||
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作为参数，删除该员工的记录。
|
||
|
||
```sql
|
||
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作为参数，返回该部门所有员工的详细信息。
|
||
|
||
```sql
|
||
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作为参数，返回该职位所有员工的详细信息。
|
||
|
||
```sql
|
||
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作为参数，返回该部门的平均工资。
|
||
|
||
```sql
|
||
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作为参数，返回该职位的平均工资。
|
||
|
||
```sql
|
||
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`，接受一个工资阈值作为参数，返回所有工资高于该阈值的员工。
|
||
|
||
```sql
|
||
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作为参数，返回该员工的工龄（以年为单位）。
|
||
|
||
```sql
|
||
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作为参数，返回该部门的员工总数。
|
||
|
||
```sql
|
||
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作为参数，返回该职位的员工总数。
|
||
|
||
```sql
|
||
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作为参数，返回该员工的姓名、工资、部门名称和职位名称。
|
||
|
||
```sql
|
||
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作为参数，返回该部门的最高工资。
|
||
|
||
```sql
|
||
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作为参数，返回该职位的最低工资。
|
||
|
||
```sql
|
||
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作为参数，返回该员工在公司中的工资排名。
|
||
|
||
```sql
|
||
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作为参数，返回该部门所有员工的工资总和。
|
||
|
||
```sql
|
||
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作为参数，返回该职位所有员工的工资总和。
|
||
|
||
```sql
|
||
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`，接受年份作为参数，返回该年份入职的所有员工。
|
||
|
||
```sql
|
||
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作为参数，返回该部门工资的分布情况（如最高、最低、平均工资）。
|
||
|
||
```sql
|
||
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作为参数，返回该职位工资的分布情况（如最高、最低、平均工资）。
|
||
|
||
```sql
|
||
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);
|
||
```
|
||
|