帮我写一份mysql员工管理系统
时间: 2023-06-11 12:10:03 浏览: 72
好的,下面是一个简单的 MySQL 员工管理系统的代码示例:
首先,需要创建两个表,一个是 `employees` 表,用于存储员工信息,另一个是 `departments` 表,用于存储部门信息。
```sql
CREATE TABLE employees (
id INT(11) AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
email VARCHAR(50) NOT NULL,
phone VARCHAR(20) NOT NULL,
hire_date DATE NOT NULL,
salary DECIMAL(10,2) NOT NULL,
department_id INT(11) NOT NULL,
FOREIGN KEY (department_id) REFERENCES departments(id)
);
CREATE TABLE departments (
id INT(11) AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL
);
```
然后,需要插入一些初始数据:
```sql
INSERT INTO departments (name) VALUES
('Sales'),
('Marketing'),
('Engineering'),
('Finance'),
('Human Resources');
INSERT INTO employees (name, email, phone, hire_date, salary, department_id) VALUES
('John Smith', 'john.smith@example.com', '555-1234', '2020-01-01', 50000.00, 1),
('Jane Doe', 'jane.doe@example.com', '555-5678', '2020-02-01', 60000.00, 2),
('Bob Johnson', 'bob.johnson@example.com', '555-9012', '2020-03-01', 70000.00, 3),
('Alice Brown', 'alice.brown@example.com', '555-3456', '2020-04-01', 80000.00, 4),
('Tom Wilson', 'tom.wilson@example.com', '555-7890', '2020-05-01', 90000.00, 5);
```
接下来,可以开始编写查询语句来实现员工管理系统的各种功能。
1. 显示所有员工信息
```sql
SELECT e.id, e.name, e.email, e.phone, e.hire_date, e.salary, d.name AS department
FROM employees e
JOIN departments d ON e.department_id = d.id;
```
2. 根据员工姓名搜索
```sql
SELECT e.id, e.name, e.email, e.phone, e.hire_date, e.salary, d.name AS department
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE e.name LIKE '%John%';
```
3. 根据部门名称搜索
```sql
SELECT e.id, e.name, e.email, e.phone, e.hire_date, e.salary, d.name AS department
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE d.name LIKE '%Sales%';
```
4. 按薪资排序
```sql
SELECT e.id, e.name, e.email, e.phone, e.hire_date, e.salary, d.name AS department
FROM employees e
JOIN departments d ON e.department_id = d.id
ORDER BY e.salary DESC;
```
5. 添加新员工
```sql
INSERT INTO employees (name, email, phone, hire_date, salary, department_id)
VALUES ('Mary Johnson', 'mary.johnson@example.com', '555-2345', '2020-06-01', 100000.00, 3);
```
6. 更新员工信息
```sql
UPDATE employees SET salary = 80000.00 WHERE id = 1;
```
7. 删除员工信息
```sql
DELETE FROM employees WHERE id = 5;
```
以上是一个简单的 MySQL 员工管理系统的代码示例,可以根据实际需求进行修改和优化。
相关推荐
![rar](https://img-home.csdnimg.cn/images/20210720083606.png)
![zip](https://img-home.csdnimg.cn/images/20210720083736.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)