使用sql server设计和实现员工管理的管理系统并给出sql代码。该系统主要的功能如下: 1、 员工基本信息的管理:员工基本信息的录入、修改、删除和查询功能。2、 部门信息的管理:部门信息的录入、修改、删除和查询功能。3、 请假信息管理:实现员工请假信息的录入、修改、删除和查询功能 4、 考勤信息管理:迟到、早退、旷工等信息的录入、修改、删除和查询功能。5、 加班信息管理:加班信息的录入、修改、删除和查询功能。6、 工资信息管理:工资信息的录入、修改、删除和查询功能。
时间: 2023-07-10 18:07:46 浏览: 85
创建员工基本信息表:
```sql
CREATE TABLE Employee (
EmpID INT PRIMARY KEY,
EmpName VARCHAR(50) NOT NULL,
Gender CHAR(1) NOT NULL,
Birthday DATE NOT NULL,
Phone VARCHAR(20) NOT NULL,
Address VARCHAR(100) NOT NULL,
DeptID INT NOT NULL,
CONSTRAINT fk_Dept FOREIGN KEY(DeptID) REFERENCES Department(DeptID)
);
```
创建部门信息表:
```sql
CREATE TABLE Department (
DeptID INT PRIMARY KEY,
DeptName VARCHAR(50) NOT NULL,
ManagerID INT NOT NULL,
CONSTRAINT fk_Manager FOREIGN KEY(ManagerID) REFERENCES Employee(EmpID)
);
```
创建请假信息表:
```sql
CREATE TABLE Leave (
LeaveID INT PRIMARY KEY,
EmpID INT NOT NULL,
StartDate DATE NOT NULL,
EndDate DATE NOT NULL,
Reason VARCHAR(100) NOT NULL,
CONSTRAINT fk_Employee FOREIGN KEY(EmpID) REFERENCES Employee(EmpID)
);
```
创建考勤信息表:
```sql
CREATE TABLE Attendance (
AttID INT PRIMARY KEY,
EmpID INT NOT NULL,
AttDate DATE NOT NULL,
AttType VARCHAR(10) NOT NULL,
CONSTRAINT fk_Emp FOREIGN KEY(EmpID) REFERENCES Employee(EmpID)
);
```
创建加班信息表:
```sql
CREATE TABLE Overtime (
OvertimeID INT PRIMARY KEY,
EmpID INT NOT NULL,
OvertimeDate DATE NOT NULL,
Hours INT NOT NULL,
CONSTRAINT fk_Employee1 FOREIGN KEY(EmpID) REFERENCES Employee(EmpID)
);
```
创建工资信息表:
```sql
CREATE TABLE Salary (
SalaryID INT PRIMARY KEY,
EmpID INT NOT NULL,
SalaryAmount FLOAT NOT NULL,
PayDate DATE NOT NULL,
CONSTRAINT fk_Employee2 FOREIGN KEY(EmpID) REFERENCES Employee(EmpID)
);
```
插入部门信息:
```sql
INSERT INTO Department (DeptID, DeptName, ManagerID)
VALUES
(1, 'Sales', 1),
(2, 'Marketing', 2),
(3, 'Engineering', 3),
(4, 'Human Resources', 4);
```
插入员工基本信息:
```sql
INSERT INTO Employee (EmpID, EmpName, Gender, Birthday, Phone, Address, DeptID)
VALUES
(1, 'John Smith', 'M', '1980-01-01', '888-888-8888', '123 Main St', 1),
(2, 'Mary Johnson', 'F', '1985-06-15', '999-999-9999', '456 Elm St', 2),
(3, 'Tom Lee', 'M', '1982-03-21', '777-777-7777', '789 Oak St', 3),
(4, 'Jane Doe', 'F', '1978-11-17', '666-666-6666', '101 Maple St', 4);
```
插入考勤信息:
```sql
INSERT INTO Attendance (AttID, EmpID, AttDate, AttType)
VALUES
(1, 1, '2021-07-01', '迟到'),
(2, 1, '2021-07-02', '正常'),
(3, 2, '2021-07-01', '正常'),
(4, 2, '2021-07-02', '早退'),
(5, 3, '2021-07-01', '正常'),
(6, 3, '2021-07-02', '正常'),
(7, 4, '2021-07-01', '迟到'),
(8, 4, '2021-07-02', '旷工');
```
插入加班信息:
```sql
INSERT INTO Overtime (OvertimeID, EmpID, OvertimeDate, Hours)
VALUES
(1, 1, '2021-07-05', 2),
(2, 2, '2021-07-06', 3),
(3, 3, '2021-07-07', 4),
(4, 4, '2021-07-08', 5);
```
插入工资信息:
```sql
INSERT INTO Salary (SalaryID, EmpID, SalaryAmount, PayDate)
VALUES
(1, 1, 5000, '2021-07-31'),
(2, 2, 6000, '2021-07-31'),
(3, 3, 7000, '2021-07-31'),
(4, 4, 8000, '2021-07-31');
```
查询员工基本信息:
```sql
SELECT * FROM Employee;
```
查询部门信息:
```sql
SELECT * FROM Department;
```
查询请假信息:
```sql
SELECT * FROM Leave;
```
查询考勤信息:
```sql
SELECT * FROM Attendance;
```
查询加班信息:
```sql
SELECT * FROM Overtime;
```
查询工资信息:
```sql
SELECT * FROM Salary;
```
相关推荐
![zip](https://img-home.csdnimg.cn/images/20210720083736.png)
![zip](https://img-home.csdnimg.cn/images/20210720083736.png)
![zip](https://img-home.csdnimg.cn/images/20210720083736.png)
![-](https://csdnimg.cn/download_wenku/file_type_column_c1.png)
![-](https://csdnimg.cn/download_wenku/file_type_column_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)