数据库课程设计

数据库实验报告

小实验：SQL基本操作

实验一：搭建环境

成功搭建mysql环境

实验二

create database Demo;
create table Demo.customer(customid varchar(17) primary key,name varchar(10),sex varchar(2),age int(4),xfg dec(10,2),address varchar(50),joindate date,memo varchar(100));

实验三

create table Demo.student(id varchar(17),name varchar(10),sex varchar(2),age integer,score numeric(6,2),joindate date);
insert into Demo.student(id,name,sex,age,score,joindate) 
values("A0001","赵一","男",20,580.00,date('2020-9-1')),("B0002","钱二","女",19,540.00,date('2021-9-1')),
("C0003","孙三","男",21,555.50,date('2020-8-30')),("D0004","李四","男",22,480.00,date('2021-9-2')),
("E0005","周五","女",20,495.50,date('2022-7-30')),("F0006","吴六","男",19,435.00,date('2021-9-1'));
select * from Demo.student where age>=20 and score<500;
select year(joindate),count(id) from Demo.student group by year(joindate); 

实验四

create table Demo.test(id varchar(10) primary key,name varchar(20),age int(4));
drop table Demo.test;
create table Demo.test(id varchar(10) primary key,name varchar(20),age int(4));
alter table Demo.test change name test_name varchar(21);
alter table Demo.test add email varchar(100);
create index idx_name on Demo.test(test_name,email);
drop index idx_name on Demo.test;
create index idx_id on Demo.test(id desc);
drop index idx_id on Demo.test;

实验五

CREATE TABLE Demo.cc (
    Cc1 VARCHAR(50),
    Cc2 INT,
    Cc3 DECIMAL(10, 2),
    Cc4 VARCHAR(100)
);
INSERT INTO Demo.cc (Cc1, Cc2, Cc3, Cc4) VALUES
('赵一', 20, 580.00, '重邮宿舍12-3-5'),
('钱二', 19, 540.00, '南福苑5-2-9'),
('孙三', 21, 555.50, '学生新区21-5-15'),
('李四', 22, 480.00, '重邮宿舍8-2-22'),
('周五', 20, 495.50, '学生新区23-4-8'),
('吴六', 19, 435.00, '南福苑2-5-12');
SET SQL_SAFE_UPDATES = 0;
UPDATE Demo.cc SET cc3 = cc3 + 5 WHERE cc2 <= 20;
DELETE FROM Demo.cc WHERE cc2 >= 20 AND cc3 >= 500;

实验六

drop table Demo.student;
CREATE TABLE Demo.Student (
    ID VARCHAR(20) PRIMARY KEY,
    Name VARCHAR(10),
    Age INT,
    Department VARCHAR(30)
);
drop table Demo.Course;
CREATE TABLE Demo.Course (
    CourseID VARCHAR(15) PRIMARY KEY,
    CourseName VARCHAR(30),
    CourseBefore VARCHAR(15)
);
CREATE TABLE Demo.Choose (
    ID VARCHAR(20),
    CourseID VARCHAR(30),
    Score DEC(5,2),
    PRIMARY KEY (ID, CourseID),
    FOREIGN KEY (ID) REFERENCES Student(ID),
    FOREIGN KEY (CourseID) REFERENCES Course(CourseID)
);

INSERT INTO Demo.Student (ID, Name, Age, Department) VALUES
('00001', '张三', 20, '计算机系'),
('00002', '李四', 19, '计算机系'),
('00003', '王五', 21, '计算机系');
INSERT INTO Demo.Course (CourseID, CourseName, CourseBefore) VALUES
('C1', '计算机引论', NULL),
('C2', 'PASCAL语言', 'C1'),
('C3', '数据结构', 'C2');
INSERT INTO Demo.Choose (ID, CourseID, Score) VALUES
('00001', 'C1', 95),
('00001', 'C2', 80),
('00001', 'C3', 84),
('00002', 'C1', 80),
('00002', 'C2', 85),
('00003', 'C1', 78),
('00003', 'C3', 70);

select ID,Name from Demo.Student where Department='计算机系';
SELECT Student.ID, Name, CourseName, Score FROM Demo.Student,Demo.Course,Demo.Choose where Student.ID=Choose.ID;

select ID,Score from Demo.Choose where Choose.CourseID="C1" and Score < (select Score from Demo.Choose,Demo.Student where Choose.ID=Student.ID and Student.Name="张三" and Choose.CourseID="C1"); 

select Choose.ID from Demo.Choose  
where Choose.CourseID="C2" and Choose.ID 
in (select Choose.ID from Choose where Choose.CourseID="C3"); 

实验七

GRANT ALL PRIVILEGES ON Demo.* TO 'DCL'@'localhost';
grant create on *.* to 'DCL'@'localhost';
grant insert on DCLDemo.* to 'DCL'@'localhost';
grant delete on DCLDemo.* to 'DCL'@'localhost';
grant update on DCLDemo.* to 'DCL'@'localhost';
grant select on DCLDemo.* to 'DCL'@'localhost';
revoke all privileges on *.* from 'DCL'@'localhost';

实验八

CREATE TABLE Demo.Exam (
  id VARCHAR(17),
  name VARCHAR(10),
  sex VARCHAR(2),
  age INT,
  score NUMERIC(6, 2),
  address VARCHAR(50),
  memo VARCHAR(100)
);
INSERT INTO Demo.Exam (id, name, sex, age, score, address, memo) VALUES
('A0001', '赵一', '男', 20, 580.00, '重邮宿舍12-3-5', '学习委员'),
('B0002', '钱二', '女', 19, 540.00, '南福苑5-2-9', '班长'),
('C0003', '孙三', '男', 21, 555.50, '学生新区21-5-15', '优秀共青团员'),
('D0004', '李四', '男', 22, 480.00, '重邮宿舍8-2-22', '暂无相关信息'),
('E0005', '周五', '女', 20, 495.50, '学生新区23-4-8', '暂无相关信息'),
('F0006', '吴六', '男', 19, 435.00, '南福苑2-5-12', '暂无相关信息');
CREATE INDEX IndexScore ON Demo.Exam(score);

CREATE TABLE Demo.jm (
  Jmbm VARCHAR(255) NOT NULL,
  Jmhz VARCHAR(255) NOT NULL,
  Jmbz VARCHAR(255) NOT NULL,
  PRIMARY KEY (Jmbm)
);
CREATE TABLE Demo.zjm (
  Zjmbm VARCHAR(255) NOT NULL,
  Zjmhz VARCHAR(255) NOT NULL,
  Jmbm VARCHAR(255) NOT NULL,
  Zjmbz VARCHAR(255) NOT NULL,
  PRIMARY KEY (Zjmbm),
  FOREIGN KEY (Jmbm) REFERENCES jm(Jmbm)
);
CREATE TABLE Demo.dhshow (
  Dhh VARCHAR(255) NOT NULL,
  Sl1 INT NOT NULL,
  Sl3 INT NOT NULL,
  Sl39 VARCHAR(255) NOT NULL,
  Sl40 VARCHAR(255) NOT NULL,
  PRIMARY KEY (Dhh),
  FOREIGN KEY (Sl40) REFERENCES zjm(Zjmbm)
);
INSERT INTO Demo.jm (Jmbm, Jmhz, Jmbz) VALUES ('J001', '拉萨局', 'L');
INSERT INTO Demo.jm (Jmbm, Jmhz, Jmbz) VALUES ('J002', '阿里局', 'A');
INSERT INTO Demo.zjm (Zjmbm, Zjmhz, Jmbm, Zjmbz) VALUES ('Z001', '拉萨子局', 'J001', 'L1');
INSERT INTO Demo.zjm (Zjmbm, Zjmhz, Jmbm, Zjmbz) VALUES ('Z002', '阿里子局', 'J002', 'A1');
INSERT INTO Demo.dhshow (Dhh, Sl1, Sl3, Sl39, Sl40) VALUES ('1234567890', 100, 50, '540100', 'Z001');
INSERT INTO Demo.dhshow (Dhh, Sl1, Sl3, Sl39, Sl40) VALUES ('0987654321', 200, 75, '540100', 'Z001');
INSERT INTO Demo.dhshow (Dhh, Sl1, Sl3, Sl39, Sl40) VALUES ('1122334455', 0, 600, '542500', 'Z002');
INSERT INTO Demo.dhshow (Dhh, Sl1, Sl3, Sl39, Sl40) VALUES ('2233445566', 150, 100, '542500', 'Z002');
SELECT AVG(Sl1) AS AvgLongDistanceCharge FROM Demo.dhshow group by Sl39 having Sl39 = '540100';
SELECT Dhh FROM Demo.dhshow WHERE Sl39 = '542500' AND Sl3 > 1000 AND Sl1 > 0;

综合实验：实现一个小型数据库管理系统

一、实验题目：员工管理系统

二、实验环境

• 前端：Bootstrap，html，css
• 后端：Django，python
• 数据库：mysql

三、实验目的

通过使用MySQL作为数据库存储，Python作为后端语言，以及Django作为Web框架，设计和实现一个员工管理系统，学习如何管理员工信息，包括添加、删除、修改和查询等功能。熟练掌握基于 B/S 架构的数据库系统的设计和管理，通过使用Bootstrap、HTML和CSS，学习如何设计和实现用户友好的Web界面，包括布局、样式和交互设计，熟练使用Django框架，学习如何快速构建Web应用程序，包括URL映射、视图处理、模板渲染等，熟练使用Django的ORM访问数据库，熟练运用 MySQL 数据库。

四、实验思路

1.需求分析

以管理员用户身份从登陆界面进入数据库管理系统，实现对公司员工的增加、删除、修改、查询，以及公司部门、员工薪资、出勤情况的增加、删除、修改、查询。对于登陆用户，可以显示自己的基本信息以及修改密码和个人信息。

2.E-R图

五、实验步骤

1.设计数据库

数据库：employee_manage

(1)ems_employee数据表

主要字段名称	数据类型	长度	主外键	解释
id	int		P	员工ID
name	varchar	100		姓名
gender	varchar	1		性别
age	varchar	10		年龄
position	varchar	100		职位
department_name	varchar	100		部门名
total_salary	decimal	(10,2)		总薪水
phone	varchar	11		电话
email	varchar	128		邮箱
password	varchar	128		密码
is_admin	tinyint	1		是否为管理员

（2）ems_department数据表

字段名称	数据类型	长度	主外键	解释
id	int		P	部门号
department_name	varchar	100		部门名
manager_id	int			部门管理员ID
manager_name	varchar	100		部门管理员姓名
description	varchar	100		部门描述
member_num	int			部门人数

（3）ems_salary数据表

字段名称	数据类型	长度	主外键	解释
id	int		P	编号
employee_id	int			员工ID
name	varchar	100		姓名
base_salary	decimal	(10,2)		基本薪资
bonus	decimal	(10,2)		奖金/罚金
total_salary	decimal	(10,2)		实发薪资

（4）ems_attendance数据表

字段名称	数据类型	长度	主外键	解释
id	int		P	编号
employee_id	int			员工ID
name	varchar	100		姓名
date	date			日期
attendance_status	varchar	10		出勤状态
work_hours	decimal	(5,2)		工作时长

2.初始化数据库

• 在MySQL中运行ems.sql，建立数据库和数据表，主要内容如下：

CREATE DATABASE  IF NOT EXISTS `employee_manage` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
USE `employee_manage`;

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `ems_attendance`
--

DROP TABLE IF EXISTS `ems_attendance`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `ems_attendance` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `date` date NOT NULL,
  `attendance_status` varchar(50) NOT NULL,
  `work_hours` decimal(5,2) NOT NULL,
  `employee_id` int DEFAULT NULL,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `ems_department`
--

DROP TABLE IF EXISTS `ems_department`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `ems_department` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `department_name` varchar(100) DEFAULT NULL,
  `description` longtext,
  `manager_id` int DEFAULT NULL,
  `member_num` int DEFAULT NULL,
  `manager_name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `department_name` (`department_name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `ems_employee`
--

DROP TABLE IF EXISTS `ems_employee`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `ems_employee` (
  `last_login` datetime(6) DEFAULT NULL,
  `email` varchar(128) NOT NULL,
  `password` varchar(128) NOT NULL,
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `gender` varchar(1) DEFAULT NULL,
  `age` varchar(10) DEFAULT NULL,
  `position` varchar(100) DEFAULT NULL,
  `department_name` varchar(100) DEFAULT NULL,
  `total_salary` decimal(10,2) DEFAULT NULL,
  `phone` varchar(11) DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL,
  `is_staff` tinyint(1) NOT NULL,
  `is_admin` tinyint(1) NOT NULL,
  `is_superuser` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `ems_salary`
--

DROP TABLE IF EXISTS `ems_salary`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `ems_salary` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `base_salary` decimal(10,2) NOT NULL,
  `bonus` decimal(10,2) NOT NULL,
  `total_salary` decimal(10,2) NOT NULL,
  `employee_id` int DEFAULT NULL,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

• 也可通过在 Django 的 models.py 文件中设计模型，新建employee_manage数据库后通过在 Django 目录使用以下两条命令初始化数据表

python manage.py makemigrations
python manage.py migrate

models.py

from django.contrib.auth.models import AbstractBaseUser, PermissionsMixin, BaseUserManager
from django.db import models
# Create your models here.


class EmployeeManager(BaseUserManager):
    def create_user(self, email, password=None, **extra_fields):
        if not email:
            raise ValueError('The Email field must be set')
        email = self.normalize_email(email)
        user = self.model(email=email, **extra_fields)
        user.set_password(password)
        user.save()
        return user

    def create_superuser(self, email, password=None, **extra_fields):
        extra_fields.setdefault('is_superuser', True)
        extra_fields.setdefault('is_admin', True)
        extra_fields.setdefault('is_staff', True)
        return self.create_user(email, password, **extra_fields)


# 雇员模型
class Employee(AbstractBaseUser, PermissionsMixin):
    email = models.EmailField(max_length=128, unique=True, default='user@123.com')
    password = models.CharField(max_length=128, default='123456')
    GENDER_CHOICES = (
        ('M', '男'),
        ('F', '女'),
    )
    id = models.AutoField(primary_key=True, verbose_name='员工ID')
    name = models.CharField(max_length=100, null=True, blank=True, verbose_name='姓名')
    gender = models.CharField(max_length=1, choices=GENDER_CHOICES, null=True, blank=True, verbose_name='性别')
    age = models.CharField(max_length=10, verbose_name='年龄', null=True, blank=True)
    position = models.CharField(max_length=100, null=True, blank=True, verbose_name='职位')
    department_name = models.CharField(max_length=100, null=True, blank=True, verbose_name='部门')
    total_salary = models.DecimalField(max_digits=10, decimal_places=2, verbose_name='总薪资', blank=True, null=True)
    phone = models.CharField(max_length=11, null=True, blank=True, verbose_name='联系电话')

    is_active = models.BooleanField(default=True)
    is_staff = models.BooleanField(default=False)
    is_admin = models.BooleanField(default=False)
    is_superuser = models.BooleanField(default=False)

    objects = EmployeeManager()

    USERNAME_FIELD = 'email'
    REQUIRED_FIELDS = []

    def __str__(self):
        return self.email

    def has_perm(self, perm, obj=None):
        return self.is_admin or self.is_superuser

    def has_module_perms(self, app_label):
        return self.is_admin or self.is_superuser


# 部门模型
class Department(models.Model):
    manager_id = models.IntegerField(null=True, blank=True, verbose_name='部门管理员ID')
    manager_name = models.CharField(max_length=100, null=True, blank=True, verbose_name='部门管理员')
    department_name = models.CharField(max_length=100, null=True, blank=True, unique=True, verbose_name='部门')
    member_num = models.IntegerField(null=True, blank=True, verbose_name='部门人数')
    description = models.TextField(null=True, blank=True, verbose_name='部门描述')

    def __str__(self):
        return self.department_name


# 薪水模型
class Salary(models.Model):
    employee_id = models.IntegerField(null=True, blank=True, verbose_name='员工ID')
    name = models.CharField(max_length=100, null=True, blank=True, verbose_name='姓名')
    base_salary = models.DecimalField(max_digits=10, decimal_places=2, verbose_name='基本工资')
    bonus = models.DecimalField(max_digits=10, decimal_places=2, default=0, verbose_name='奖金')
    total_salary = models.DecimalField(max_digits=10, decimal_places=2, verbose_name='总薪资')

    def __str__(self):
        return f"Salary record for employee"


class Attendance(models.Model):
    employee_id = models.IntegerField(null=True, blank=True, verbose_name='员工ID')
    name = models.CharField(max_length=100, null=True, blank=True, verbose_name='姓名')
    date = models.DateField()
    attendance_status = models.CharField(max_length=50, choices=(
        ('Present', '正常'),
        ('Late', '迟到'),
        ('Early', '早退'),
        ('Absent', '请假'),
        # 更多出勤状态...
    ))
    work_hours = models.DecimalField(max_digits=5, decimal_places=2)

• 在pycharm中用DB Browser将后端与数据库连接，在pycharm中的配置如下图

3.后台程序管理设计

3.1索引页面

• 用pycharm打开项目后进入Django目录，用python manage.py runserver启动服务，在浏览器输入127.0.0.1:8000进入索引界面，点击链接跳转到登录页面

• 路由在urls.py中

path("",views.index,name='index')

• 后端代码主要在views.py中

def index(request):
    return render(request, 'index.html')

3.2登录页面

管理员用户名：admin@123.com
管理员密码：123456

普通用户名：user@wqw.com
普通用户密码：123456

• 输入后成功跳转到主页

• urls.py

path("login/",views.user_login,name='login')

• 后端代码主要为views.py的user_login函数，对输入的用户名和密码进行验证并判断是否为管理员，用户名密码错误则报错，若为管理员则跳转到后台主页，若为普通用户则跳转到用户主页

def user_login(request):
    if request.method == "POST":
        email = request.POST['email']
        password = request.POST['password']
        user = authenticate(email=email, password=password)
        if user and is_admin(user):
            login(request, user)
            request.session['username'] = email
            return redirect('admin_home')
        elif user:
            login(request, user)
            request.session['username'] = email
            return redirect(reverse('user_home', args=[user.id]))
        else:
            return render(request, 'login.html', {'error': 'Invalid credentials'})
    return render(request, 'login.html')

3.3主页

3.3.1用户主页

• 在用户主页可以看到用户个人信息，可通过右上角折叠栏修改账号密码、退出登录，点击编辑可以跳转到编辑用户界面

• 在编辑用户界面可以修改用户密码以及个人信息等，成功编辑后会更新数据库中的用户记录并回到用户主页

• 修改账号密码，修改后会更新数据库中的用户记录并跳转到登录页面，需重新登录

• urls.py

path("user_home/<int:user_id>/",views.user_home,name='user_home'),
path("user_home/<int:user_id>/edit/", views.user_edit, name='user_edit'),
path("admin_home/<int:user_id>/change_admin_password/", views.change_admin_password, name='change_admin_password')

• views.py

#用户主页
@login_required
def user_home(request, user_id):
    user = Employee.objects.get(id=user_id)
    return render(request, 'user_home.html', {'user': user})


#编辑用户
@login_required
def user_edit(request, user_id):
    user = Employee.objects.get(id=user_id)
    if request.method == 'POST':
        #更新数据库中的记录
        user.name = request.POST['name']
        user.gender = request.POST['gender']
        user.age = request.POST['age']
        user.phone = request.POST['phone']
        if not user.name or not user.gender or not user.age or not user.phone:
            return render(request, 'user_edit.html', {'error': '有字段为空'})
        user.save()
        return redirect(reverse('user_home', args=[user.id]))
    return render(request, 'user_edit.html', {"user": user})


#修改账号密码
@login_required
def change_user_password(request, user_id):
    user = Employee.objects.get(id=user_id)
    if request.method == 'POST':
        #更新数据库中的记录
        if not request.POST['email'] or not request.POST['password']:
            return render(request, 'change_user_password.html', {'error': '用户名或密码为空'})
        user.email = request.POST['email']
        user.password = request.POST['password']
        user.password = make_password(user.password)
        user.save()
        return redirect("login")
    return render(request, 'change_user_password.html', {"user": user})

3.3.2后台主页

• 后台主页中显示了管理员的相关信息，可修改管理员账号密码，也可点击编辑可修改管理员个人信息，还可跳转到对员工信息、公司部门信息、员工薪资、出勤情况的管理界面

• 编辑管理员界面，成功编辑后更新数据库中的管理员记录并回到后台主页

• 修改管理员账号密码，修改后会更新数据库中的管理员记录并跳转到登录页面，需重新登录

• urls.py

path("admin_home/",views.admin_home,name='admin_home'),
path("user_home/<int:user_id>/edit/", views.user_edit, name='user_edit'),
path("admin_home/<int:user_id>/change_admin_password/", views.change_admin_password, name='change_admin_password')

• views.py

#后台主页
@user_passes_test(is_admin)
@login_required
def admin_home(request):
    manager = Employee.objects.get(id=1)
    return render(request, 'admin_home.html', {'manager': manager})


#编辑管理员信息
@user_passes_test(is_admin)
@login_required
def admin_edit(request, employee_id):
    employee = get_object_or_404(Employee, id=employee_id)
    if request.method == "POST":
        #更新数据库中的记录
        employee.email = request.POST['email']
        employee.name = request.POST['name']
        employee.password = request.POST['password']
        employee.password = make_password(employee.password)
        employee.gender = request.POST['gender']
        employee.age = request.POST['age']
        employee.department_name = request.POST['department']
        employee.position = request.POST['position']
        employee.phone = request.POST['phone']
        if (not employee.email or not employee.password or not employee.name or not employee.gender or not employee.age
                or not employee.position or not employee.phone or not employee.department_name):
            return render(request, 'admin_edit.html', {'error': '所有字段都是必填的'})
        if employee.department_name != request.POST['department']:
            employee.department_name = request.POST['department']
            try:
                department = Department.objects.get(manager_id=employee.id)
                if department:
                    department.manager_id = None
                    department.manager_name = None
                    department.save()
            except Department.DoesNotExist:
                pass
        employee.save()
        return redirect('admin_home')
    return render(request, 'admin_edit.html', {'employee': employee})


#修改账号密码
@user_passes_test(is_admin)
@login_required
def change_admin_password(request, user_id):
    manager = Employee.objects.get(id=user_id)
    if request.method == 'POST':
        #更新数据库中的记录
        if not request.POST['email'] or not request.POST['password']:
            return render(request, 'change_admin_password.html', {'error': '用户名或密码为空'})
        manager.email = request.POST['email']
        manager.password = request.POST['password']
        manager.password = make_password(manager.password)
        manager.save()
        return redirect("login")
    return render(request, 'change_admin_password.html')

3.4员工管理

• 在后台主页点击员工管理可跳转到员工管理界面，可对员工信息进行增加、删除、修改、查询

• 添加员工，输入员工信息，成功添加后会在数据库中添加相应记录并回到员工管理界面

• 删除员工，通过输入员工ID选择要删除的员工，删除相应记录后回到员工管理界面

• 修改员工信息，成功修改后会更新数据库中相应记录并回到员工管理界面

• 查询员工，输入员工ID后，若相应的员工存在则显示相应的员工信息

• urls.py

path("employee_info/",views.employee_info,name='employee_info'),
path("employee_info/add/",views.employee_add,name='employee_add'),
path("employee_info/delete/",views.employee_delete,name='employee_delete'),
path("employee_info/<int:employee_id>/edit/",views.employee_edit,name='employee_edit'),
path("employee_info/search/",views.employee_search,name='employee_search'),
path("<int:employee_id>/info_list/",views.info_list,name='info_list')

• views.py

#添加员工
@user_passes_test(is_admin)
@login_required
def employee_add(request):
    if request.method == "POST":
        #在数据库中添加记录
        email = request.POST['email']
        password = request.POST['password']
        name = request.POST['name']
        gender = request.POST['gender']
        age = request.POST['age']
        position = request.POST['position']
        department_name = request.POST['department']
        phone = request.POST['phone']
        if not email or not name or not age or not position or not department_name or not phone or not password:
            return render(request, 'department_add.html', {'error': '所有字段都是必填的'})
        employee = Employee(
            email=email,
            password=password,
            name=name,
            gender=gender,
            age=age,
            position=position,
            department_name=department_name,
            phone=phone
        )
        employee.password = make_password(employee.password)
        employee.is_staff = True
        employee.save()
        # 重定向到员工信息页面
        return redirect('employee_info')
    return render(request, 'employee_add.html')


#删除员工
@user_passes_test(is_admin)
@login_required
def employee_delete(request):
    if request.method == "POST":
        user_id = request.POST['id']
        if(user_id == "1"):
            return render(request, 'employee_delete.html', {'error': '不能删除管理员'})
        employee = get_object_or_404(Employee, id=user_id)
        try:
            department = Department.objects.get(manager_id=employee.id)
            if department:
                department.manager_id = None
                department.manager_name = None
                department.save()
        except Department.DoesNotExist:
            pass
        salaries = Salary.objects.filter(employee_id=employee.id)
        if len(salaries) > 0:
            salaries.delete()
        else:
            pass
        attendances = Attendance.objects.filter(employee_id=employee.id)
        if len(attendances) > 0:
            attendances.delete()
        else:
            pass
        employee.delete()
        return redirect('employee_info')
    return render(request, 'employee_delete.html')


#编辑员工
@user_passes_test(is_admin)
@login_required
def employee_edit(request, employee_id):
    employee = get_object_or_404(Employee, id=employee_id)
    if request.method == "POST":
        #更新数据库中的记录
        employee.email = request.POST['email']
        employee.password = request.POST['password']
        employee.password = make_password(employee.password)
        employee.name = request.POST['name']
        employee.gender = request.POST['gender']
        employee.age = request.POST['age']
        employee.department_name = request.POST['department']
        employee.position = request.POST['position']
        employee.phone = request.POST['phone']
        if (not employee.email or not employee.name or not employee.gender or not employee.age or not employee.position
                or not employee.phone or not employee.department_name or not employee.password):
            return render(request, 'employee_edit.html', {'error': '所有字段都是必填的'})
        if employee.department_name != request.POST['department']:
            employee.department_name = request.POST['department']
            try:
                department = Department.objects.get(manager_id=employee.id)
                if department:
                    department.manager_id = None
                    department.manager_name = None
                    department.save()
            except Department.DoesNotExist:
                pass
        employee.save()
        return redirect('employee_info')
    return render(request, 'employee_edit.html')


#查找员工
@user_passes_test(is_admin)
@login_required
def employee_search(request):
    if request.method == "POST":
        user_id = request.POST['id']
        employee = get_object_or_404(Employee, id=user_id)
        return redirect('info_list', employee_id=employee.id)
    return render(request, 'employee_search.html')


@user_passes_test(is_admin)
@login_required
def info_list(request, employee_id):
    employee = get_object_or_404(Employee, id=employee_id)
    return render(request, 'info_list.html', {'employee': employee})


#员工管理界面
@user_passes_test(is_admin)
@login_required
def employee_info(request):
    employees = Employee.objects.exclude(id=1)
    return render(request, "employee_info.html", {'employees': employees})

3.5部门管理

• 点击部门管理可跳转到部门管理界面，可对公司部门信息进行添加、删除、修改、查询

• 添加部门，添加成功后回到部门管理界面

• 编辑部门，修改部门信息，成功修改后回到部门管理界面

• 删除部门，输入部门ID删除部门，并将在这个部门的员工信息一起删除，删除成功回到部门管理界面

• 查询部门，输入部门ID后，若相应的部门存在则显示相应的部门信息

• urls.py

path("department_info/",views.department_info,name='department_info'),
path("department_info/add/",views.department_add,name='department_add'),
path("department_info/delete/",views.department_delete,name='department_delete'),
path("department_info/<int:department_id>/edit/",views.department_edit,name='department_edit'),
path("department_info/search/",views.department_search,name='department_search'),
path("<int:department_id>/department_info_list/", views.department_info_list, name='department_info_list')

• views.py

#部门管理界面
@user_passes_test(is_admin)
@login_required
def department_info(request):
    departments = Department.objects.all()
    #统计各部门人数并保存到数据库中
    department_counts = Employee.objects.values('department_name').annotate(count=Count('department_name'))
    for department_count in department_counts:
        for department in departments:
            if department_count['department_name'] == department.department_name:
                department.member_num = department_count['count']
                department.save()
                break
    return render(request, "department_info.html", {'departments': departments})


#添加部门
@user_passes_test(is_admin)
@login_required
def department_add(request):
    if request.method == "POST":
        #在数据库中添加记录
        manager_id = request.POST['manager_id']
        manager_name = request.POST['manager']
        department_name = request.POST['department_name']
        description = request.POST['description']
        if not department_name or not manager_id or not manager_name:
            return render(request, 'department_add.html', {'error': '除了部门描述外其他字段必填'})
        department = Department(
            manager_id=manager_id,
            manager_name=manager_name,
            department_name=department_name,
            description=description,
        )
        department.save()
        return redirect('department_info')
    return render(request, 'department_add.html')


#删除部门
@user_passes_test(is_admin)
@login_required
def department_delete(request):
    if request.method == "POST":
        department_id = request.POST['id']
        department = get_object_or_404(Department, id=department_id)
        employees = Employee.objects.filter(department_name=department.department_name)
        if len(employees) > 0:
            employees.delete()
        else:
            pass
        department.delete()
        return redirect('department_info')
    return render(request, 'department_delete.html')


#编辑部门
@user_passes_test(is_admin)
@login_required
def department_edit(request, department_id):
    department = get_object_or_404(Department, id=department_id)
    if request.method == "POST":
        #更新数据库中的记录
        department.manager_id = request.POST['manager_id']
        department.manager_name = request.POST['manager_name']
        department.department_name = request.POST['department_name']
        department.description = request.POST['description']
        if not department.manager_id or not department.manager_name or not department.department_name:
            return render(request, 'department_add.html', {'error': '除了部门描述外其他字段必填'})
        department.save()
        return redirect('department_info')
    return render(request, 'department_edit.html')


#查询部门
@user_passes_test(is_admin)
@login_required
def department_search(request):
    if request.method == "POST":
        department_id = request.POST['id']
        department = get_object_or_404(Department, id=department_id)
        return redirect('department_info_list', department_id=department.id)
    return render(request, 'department_search.html')


@user_passes_test(is_admin)
@login_required
def department_info_list(request, department_id):
    department = get_object_or_404(Department, id=department_id)
    return render(request, 'department_info_list.html', {'department': department})

3.6薪资管理

• 点击员工薪资后可跳转到薪资管理界面，可对员工薪资信息进行添加、删除、修改、查询

• 添加记录，添加成功后回到薪资管理界面

• 删除记录，输入记录编号，删除成功后回到薪资管理界面

• 编辑记录，编辑完成后回到薪资管理界面

• 查询记录，输入记录编号，若记录存在则显示相应记录信息

• urls.py

path("salary_info/",views.salary_info,name='salary_info'),
path("salary_info/add/",views.salary_add,name='salary_add'),
path("salary_info/delete/",views.salary_delete,name='salary_delete'),
path("salary_info/search/",views.salary_search,name='salary_search'),
path("<int:salary_employee_id>/salary_info_list/", views.salary_info_list, name='salary_info_list'),
path("salary_info/<int:salary_id>/edit/",views.salary_edit,name='salary_edit')

• views.py

#薪资管理界面
@user_passes_test(is_admin)
@login_required
def salary_info(request):
    salaries = Salary.objects.all()
    return render(request, "salary_info.html", {'salaries': salaries})


#添加薪资记录
@user_passes_test(is_admin)
@login_required
def salary_add(request):
    if request.method == "POST":
        #在数据库中添加记录
        employee_id = request.POST['employee_id']
        employee_name = request.POST['employee_name']
        base_salary = request.POST['base_salary']
        bonus = request.POST['bonus']
        if not employee_id or not employee_name or not base_salary:
            return render(request, 'department_add.html', {'error': '除了奖金外其他字段必填'})
        total_salary = float(bonus) + float(base_salary)
        salary = Salary(
            employee_id=employee_id,
            name=employee_name,
            base_salary=base_salary,
            bonus=bonus,
            total_salary=total_salary
        )
        salary.save()
        try:
            employee = Employee.objects.get(id=employee_id)
            if employee:
                employee.total_salary = total_salary
                employee.save()
        except Employee.DoesNotExist:
            return render(request, 'salary_add.html', {'error': '员工不存在'})
        return redirect('salary_info')
    return render(request, 'salary_add.html')


#删除记录
@user_passes_test(is_admin)
@login_required
def salary_delete(request):
    if request.method == "POST":
        employee_id = request.POST['id']
        salaries = Salary.objects.filter(employee_id=employee_id)
        if len(salaries) > 0:
            salaries.delete()
        else:
            return render(request, 'salary_delete.html', {'error': '无薪资记录'})
        return redirect('salary_info')
    return render(request, 'salary_delete.html')


#查询记录
@user_passes_test(is_admin)
@login_required
def salary_search(request):
    if request.method == "POST":
        employee_id = request.POST['id']
        salaries = Salary.objects.filter(employee_id=employee_id)
        try:
            salary = salaries.first()
        except Salary.DoesNotExist:
            salary = None
        return redirect('salary_info_list', salary_employee_id=salary.employee_id)
    return render(request, 'salary_search.html')


@user_passes_test(is_admin)
@login_required
def salary_info_list(request, salary_employee_id):
    salaries = Salary.objects.filter(employee_id=salary_employee_id)
    return render(request, 'salary_info_list.html', {'salaries': salaries})


#编辑记录
@user_passes_test(is_admin)
@login_required
def salary_edit(request, salary_id):
    salary = Salary.objects.get(id=salary_id)
    if request.method == "POST":
        #更新数据库中记录
        salary.employee_id = request.POST['employee_id']
        salary.name = request.POST['employee_name']
        salary.base_salary = request.POST['base_salary']
        salary.bonus = request.POST['bonus']
        salary.total_salary = float(salary.base_salary) + float(salary.bonus)
        if not salary.base_salary or not salary.name or not salary.employee_id:
            return render(request, 'salary_edit.html', {'error': '有的字段不能为空'})
        salary.save()
        return redirect('salary_info')
    return render(request, 'salary_edit.html')

3.7考勤管理

• 点击出勤情况会跳转到考勤管理界面，可对员工出勤情况进行添加、删除、修改、查询

• 添加出勤记录，添加成功后回到出勤情况界面

• 删除出勤记录，输入员工ID，成功删除后回到出勤情况界面

• 编辑出勤记录，编辑成功后回到出勤情况界面

• 查询出勤记录，输入员工ID，若员工存在则显示相应记录信息

前端网页界面设计

• index.html—索引界面
• login.html—登录界面
  • my-login.css—登录界面样式
• user_home.html—用户主页
• user_edit.html—编辑用户界面
• change_user_password.html—修改用户账号密码界面
• admin_home.html—后台主页
• admin_edit.html—编辑管理员界面
• change_admin_password.html—修改管理员账号密码界面
• employee_info.html—员工管理界面
• employee_add.html—添加员工界面
• employee_delete.html—删除员工界面
• employee_edit.html—编辑员工界面
• employee_search.html—查找员工界面
• info_list.html—显示查找的员工信息界面
• department_info.html—部门管理界面
• department_add.html—添加部门界面
• department_delete.html—删除部门界面
• department_edit.html—编辑部门界面
• department_search.html—查找部门界面
• department_info_list.html—显示要查找的部门信息界面
• salary_info.html—薪资管理界面
• salary_add.html—添加薪资记录界面
• salary_delete.html—删除薪资记录界面
• salary_edit.html—编辑薪资记录界面
• salary_search.html—查找薪资记录界面
• salary_info_list.html—显示要查找的薪资记录界面
• attendance_info.html—出勤管理界面
• attendance_add.html—添加出勤记录界面
• attendance_delete.html—删除出勤记录界面
• attendance_edit.html—编辑出勤记录界面
• attendance_search.html—查找出勤记录界面
• attendance_info_list—显示查找的出勤记录界面

六、实验心得

本次数据库课程设计让我对数据库管理系统的开发有了更深入的理解和实践，通过使用 MySQL、Python 和 Django，我成功地设计和实现了一个简单的员工管理系统，包括员工信息、部门信息、薪资信息和考勤情况的添加、删除、修改、查找功能。这次实验让我体会到 Django 是一个功能强大的 Web 框架，它提供了许多内置功能，例如用户认证、数据库 ORM、模板系统等，这让我能够更快地开发系统，并减少代码量。在前端展示上，我使用了 Bootstrap 框架来设计用户界面，它提供了丰富的样式和组件，让我能够快速构建美观且易用的界面。当然，在开发过程中，我遇到很多问题，比如用户认证、权限管理、数据关联等，通过上网查阅资料，我最终解决了这些问题，积累了很多开发经验。总而言之，此次课程设计带给我很多收获，为以后对数据库管理系统的进一步学习打下基础。