网吧前台管理系统
网吧前台管理系统
后台数据库的设计与实现
步骤1.分析与设计
1. 数据处理需求描述
网吧上网是当代人类生活休闲娱乐的方式之一，随着时代的发展、科技的变换，人们的生活方式也随之改变，对比以前“物以稀为贵”的景象，现在许多网吧为了获客也推出自家各种五花八门的服务，给这一行业增添了不少色彩。但随着上网的人越来越多，以及各种服务的花式衬托，网吧管理作为其中必不可少的一环也不断地更新迭代。经过小组讨论和分析，希望能够结合现有现实场景模拟一套网吧前台管理系统，以更好地响应用户需求。
网吧机房管理系统设计说明：
主要面向用户分为两种：一种是面向广大群众的普通用户；一种是面向网吧员工的管理员概念（此处对系统后台管理员不作额外阐述）
网吧用户
网吧用户可自行享有登录注册、上下机、信息查询、结账等基本功能，可根据设定的会员等级享有其他的更优质的服务
网吧管理员
网吧管理员可对网吧信息、机器信息、顾客信息进行相应的维护操作，其中包括机器基本信息维护、上下机管理、充值、查询等。此外，通过设定不同的网吧管理员权限以控制权限分级，更好地协同工作
2.概念结构设计
2.1 分ER图
小组成员根据自己负责的系统用户身份画出系统分E-R图，参考如下
网吧用户
网吧管理员
2.2全局ER图
小组成员一起将分E-R图集成为全局E-R图，消除各种冲突，去掉不合理的实体、属性和联系。整合图示如下：
2.3系统功能概述
网吧用户
个人信息管理
查询个人信息、修改账户密码；查看个人账单和上机记录，对个人账号进行管理
上下机操作
查询上下机记录，执行上下机操作；生成关联账单操作记录
网吧管理员
机器管理
对网吧机器进行管理，查看网吧机器基本信息，进行相关参数维护，管理并维护机器使用状态
参数维护
对网吧相关的基本参数信息进行维护，例如计费标准（上网计费、商品计费等相关服务），通过图表视图的方式整合展示给客户
客户信息维护
对客户信息和账号信息进行管理，例如账单、流水、客户基本信息，帮助客户完成充值、扣除、账单生成等操作。
上下机管理
结合客户实际需求进行上下机管理，实现资源灵活调配
黑名单管理
对黑名单用户进行管理
3.逻辑结构设计
3.1设计关系模式
根据全局E-R图设计关系模式。
网吧管理员信息表（管理员ID，账号，账号名称，密码，等级划分，员工姓名，员工身份证号，出生日期 ，性别，备注信息）；
机器信息表（机器ID，机器编号，机器名称，机器参数，机器位置，机器当前使用状态）；
计费参数表（参数id ，计费标准，备注）；
黑名单管理表（记录ID，姓名，身份证号， 性别，生日，历史记录）；
管理机器信息（管理员ID，机器ID）；
管理计费参数（管理员ID，参数ID）；
管理黑名单（管理员ID，黑名单ID）；
管理网吧用户（管理员ID，网吧用户ID）；
网吧用户信息表（用户ID ，用户账号，用户名，密码，关联绑定客户信息，临时状态标识，记录创建时间，记录修改时间，记录创建者，记录修改者）；
客户信息表（客户ID，客户姓名，身份证号码，客户性别，出生日期，客户备注，用户等级，关联账户ID，记录创建时间，记录修改时间，记录创建者，记录修改者 ）；
账户信息表（账户ID，账户余额，记录创建时间，记录修改时间，记录创建者，记录修改者）；
用户上下机（用户ID，上下机记录ID）；
用户信息绑定（用户ID，客户ID）；
客户账号绑定（客户ID，账户ID，流水记录ID）；
客户账单绑定（客户ID，账账单ID，流水记录ID）；
上下机记录表（记录id，操作用户账号，使用机器编号，操作类型，操作时间，操作备注，记录创建时间）；
账号流水表（流水id ， 流水编号，操作类型，记录创建时间，记录创建者）；
账号记录表（账单id，账单编号，账单内容，账单状态，结账方式，关联客户，备注，记录创建时间 ，创建者）；
3.2对关系模式进行规范化
主码相同的表合并；分析所有关系模式是否3NF，不是3NF的要规范化为3NF。
网吧管理员信息表（管理员ID，账号，账号名称，密码，等级划分，员工姓名，员工身份证号，出生日期 ，性别，备注信息）；
客户信息表（客户ID，客户姓名，身份证号码，客户性别，出生日期，客户备注，用户等级，关联账户ID，记录创建时间，记录修改时间，记录创建者，记录修改者 ）；
网吧用户信息表（用户ID ，用户账号，用户名，密码，关联绑定客户信息，临时状态标识，记录创建时间，记录修改时间，记录创建者，记录修改者）；
账户信息表（账户ID，账户余额，记录创建时间，记录修改时间，记录创建者，记录修改者）；
机器信息表（机器账号，机器编号，机器名称，机器参数，机器位置，机器当前使用状态，记录创建时间，记录修改时间，记录创建者，记录修改者）；
计费参数表（参数id ，计费标准，备注，记录创建时间，记录修改时间，记录创建者，记录修改者）；
上下机记录表（记录id，操作用户账号，使用机器编号，操作类型，操作时间，操作备注，记录创建时间）；
账号流水表（流水id ， 流水编号，操作类型，记录创建时间，记录创建者）；
账号记录表（账单id，账单编号，账单内容，账单状态，结账方式，关联客户，备注，记录创建时间 ，创建者）；
黑名单管理表（记录ID，姓名，身份证号， 性别，生日，历史记录，记录创建时间，记录修改时间，记录创建者，记录修改者）；
3.3视图设计
通过设计视图构建数据展示，控制数据访问权限，此处结合场景需求设计基于不同类型用户的视图，简要构建了下述视图说明，其说明如下所示
网吧用户视图
a.网吧客户基本详情：以客户表为主表，查找该客户绑定的用户、账号以及相关的访问记录
b.阶段数据统计：筛选一段时间内客户的来访情况和消费情况
管理员用户视图
a.数据访问权限控制：考虑不同的员工职责不同，此处限定不同的管理员拥有对客户不同的访问权限，可设定多个不同的客户视图，一一对应
b.数据统计：结合实际场景统计客户上网情况
4.数据库物理设计
4.1数据表结构设计
针对上述逻辑结构设计阶段的每个关系模式列出详细的表结构（表名、列名、列数据类型、列的约束要求、列的描述等）。具体数据表结构设计参考如下：
admin：网吧管理员信息表
admin：网吧管理员信息表 用于存储网吧管理员信息，其中admin_id作为主键，其表结构设定参考如下所示
| 列名 | 数据类型 | 约束 | 描述 |
|---|---|---|---|
| admin_id | varchar(40) | PRIMARY KEY，随机生成 | 管理员ID |
| admin_account | varchar(40) | not null | 账号 |
| admin_name | varchar(40) | not null | 账号名称 |
| admin_pwd | varchar(40) | not null | 密码 |
| admin_class | tinyint(4) | 根据不同的员工属性划分管理员等级 ，限定其数据访问权限 | 等级划分 |
| 管理关联员工信息 | |||
| emp_name | varchar(50) | not null | 员工姓名 |
| emp_id_card | varchar(50) | not null | 员工身份证号 |
| emp_birthday | date | / | 出生日期 |
| gender | tinyint(4) | / | 性别 |
| admin_descr | varchar(200) | / | 备注信息 |
说明
根据不同的员工职责划分管理员账号权限，限定系统功能访问模块和数据访问权限，提升系统使用的安全和可靠性
customer：客户信息表
customer：客户信息表 用于存储客户信息，其中cust_id作为主键，其表结构设定参考如下所示
| 列名 | 数据类型 | 约束 | 描述 |
|---|---|---|---|
| cust_id | varchar(40) | PRIMARY KEY，随机生成 | 客户ID |
| cust_name | varchar(40) | not null | 客户姓名 |
| id_card | varchar(50) | not null | 身份证号码 |
| gender | tinyint(4) | not null，取值：男、女 | 客户性别 |
| birthdate | date | / | 出生日期 |
| cust_descr | text | / | 客户备注 |
| cust_class | tinyint(4) | not null， 0-普通用户; 1-VIP1； 2-VIP2； 3-VIP3; ......扩展 | 用户等级 |
| account_id | int(11) | fk | （fk-关联账户信息）关联账户ID |
| create_time | datetime | not null | 记录创建时间 |
| modify_time | datetime | not null | 记录修改时间 |
| create_by | varchar(40) | not null | 创建者 |
| modify_by | varchar(40) | not null | 修改者 |
wb_user：网吧用户表
user：网吧用户 用于存储用户账号信息，其中uid作为主键，其表结构设定参考如下所示
| 列名 | 数据类型 | 约束 | 描述 |
|---|---|---|---|
| uid | varchar(40) | PRIMARY KEY，随机生成 | 用户ID |
| uaccount | varchar(40) | not null、UNIQUE | 用户账号（num） |
| uname | varchar(40) | not null | 用户名 |
| password | varchar(50) | not null | 密码 |
| cust_id | varchar(40) | fk | （fk-账号绑定用户）关联绑定客户信息 |
| temp_flag | tinyint(4) | not null 0-永久账号； 1-临时账号 | 临时状态标识 |
| user_state | tinyint(4) | not null 1-正常使用； 2-账号封禁 | 账号状态 |
| create_time | date | not null | 记录创建时间 |
| modify_time | date | not null | 记录修改时间 |
| create_by | varchar(40) | not null | 创建者 |
| modify_by | varchar(40) | not null | 修改者 |
说明
一个客户可关联开通多个账号信息（具体规则由网吧管理员进行设定），设定临时状态标识用于区分临时账号概念，这类临时账号没有特定绑定的客户，由网吧管理员进行维护
account：账户信息表
account：账户信息表 用于存储客户关联的账户信息，其中account_id作为主键，其表结构设定参考如下所示
| 列名 | 数据类型 | 约束 | 描述 |
|---|---|---|---|
| account_id | varchar(40) | PRIMARY KEY，随机生成 | 账户ID |
| amount | num | default：0，not null | 账户余额 |
| create_time | date | not null | 记录创建时间 |
| modify_time | date | not null | 记录修改时间 |
| create_by | varchar(40) | not null | 创建者 |
| modify_by | varchar(40) | not null | 修改者 |
computer：机器信息表
computer：机器管理表 用于存储电脑机器信息，其中account_id作为主键，其表结构设定参考如下所示
| 列名 | 数据类型 | 约束 | 描述 |
|---|---|---|---|
| cid | varchar(40) | PRIMARY KEY，随机生成 | 机器账号 |
| cnum | varchar(40) | unique | 机器编号 |
| cname | varchar(40) | default：0，not null | 机器名称 |
| c_param | varchar(1000) | / | 机器参数 （机器基本参数信息，可通过json进行维护） |
| c_location | varchar(40) | / | 机器位置 |
| current_state | not null 0：弃用 1：闲置中 2：使用中 | 机器当前使用状态 | |
| create_time | date | not null | 记录创建时间 |
| modify_time | date | not null | 记录修改时间 |
| create_by | varchar(40) | not null | 创建者 |
| modify_by | varchar(40) | not null | 修改者 |
charge_param：计费参数表
charge_param：计费参数表 用于存储计费参数信息，其中charge_id作为主键，其表结构设定参考如下所示
| 列名 | 数据类型 | 约束 | 描述 |
|---|---|---|---|
| charge_id | varchar(40) | PRIMARY KEY，随机生成 | 参数id |
| charge_standard | varchar(1000) | / | 计费标准（通过json方式存储） |
| charge_descr | varchar(1000) | / | 备注 |
| create_time | datet | not null | 记录创建时间 |
| modify_time | date | not null | 记录修改时间 |
| create_by | varchar(40) | not null | 创建者 |
| modify_by | varchar(40) | not null | 修改者 |
oper_cp_record：上下机记录表
oper_cp_record：上下机记录表 用于存储上下机记录信息，其中record_id作为主键，其表结构设定参考如下所示
| 列名 | 数据类型 | 约束 | 描述 |
|---|---|---|---|
| record_id | varchar(40) | PRIMARY KEY，随机生成 | 记录id |
| oper_uid | varchar(40) | fk | 操作用户账号 |
| oper_cid | varchar(40) | fk | 使用机器编号 |
| oper_type | tinyint(4) | 1-上机； 2-下机 | 操作类型 |
| oper_time | datetime | not null | 操作时间 |
| oper_descr | varchar(200) | / | 操作备注 |
| create_time | date | not null | 记录创建时间 |
account_flow：账号流水记录表
account_flow：账号流水记录表 用于存储客户的账号信息（充值、扣费记录维护），其中flow_id作为主键，其表结构设定参考如下所示
| 列名 | 数据类型 | 约束 | 描述 |
|---|---|---|---|
| flow_id | varchar(40) | PRIMARY KEY，随机生成 | 流水id |
| flow_num | varchar(40) | unique | 流水编号 |
| oper_type | tinyint(4) | not null 0：处理中 1：充值； 2：扣除 | 操作类型 |
| oper_amount | number | not null | 交易金额 |
| account_id | varchar(40) | fk | fk-关联账号id |
| create_time | date | not null | 记录创建时间 |
| create_by | varchar(40) | not null | fk-创建者 |
bill_record：账单记录详情表
bill_record：账单记录详情表 用于存储顾客的消费记录，其中bill_id作为主键，其表结构设定参考如下所示
| 列名 | 数据类型 | 约束 | 描述 |
|---|---|---|---|
| bill_id | varchar(40) | PRIMARY KEY，随机生成 | 账单id |
| bill_num | varchar(40) | unique | 账单编号 |
| bill_content | varchar(1000) | / | 账单内容（通过json数据存储） |
| bill_state | tinyint | not null 0：废弃 1：待结账； 2：已结清 | 账单状态 |
| charge_method | tinyint | 0-默认（待客户选择） 1-现金结账； 2-会员扣除； 3-第三方支付 | 结账方式 |
| relate_cust | varchar(40) | fk | 关联客户 |
| bill_descr | varchar(1000) | / | 备注 |
| create_time | date | not null | 记录创建时间 |
| create_by | varchar(40) | not null | fk-创建者 |
说明
如果是已有的顾客信息则将账单绑定到相关的客户账号，如果是临时的客户则通过备注的方式留存客户信息
black_list：黑名单管理表
black_list：黑名单记录表 用于存储黑名单客户信息，其中data_id作为主键，其表结构设定参考如下所示
| 列名 | 数据类型 | 约束 | 描述 |
|---|---|---|---|
| data_id | varchar(40) | PRIMARY KEY，随机生成 | id |
| name | varchar(40) | unique | 姓名 |
| id_card | varchar(40) | not null | 身份证号 |
| gender | tinyint(4) | not null | 性别 |
| birthday | datetime | / | 生日 |
| state | tinyint(4) | 1-封禁中； 2-已解除 | 状态 |
| descr | varchar(2000) | 历史记录（通过json数据存储） | |
| create_time | datetime | not null | 记录创建时间 |
| modify_time | datetime | not null | 记录修改时间 |
| create_by | varchar(40) | not null | fk-创建者 |
| modify_by | varchar(40) | not null | fk-修改者 |
说明
可配合现行的安全策略，设定黑名单机制，针对一些特殊客户群体采取相应的防范机制
4.2索引设计
结合系统设计，此处通过创建相关索引以提升数据库查询效率
网吧用户：
针对常用筛选关键字创建联合索引（用户账号、姓名）
网吧管理员：
针对数据统计常用筛选关键字创建相应的索引
步骤2.数据库实施
5.数据库实施
5.1创建数据库、创建表
数据库、数据表创建
5.2创建视图
通过设计视图构建数据展示，控制数据访问权限，此处结合场景需求设计基于不同类型用户的视图，简要构建了下述视图说明，其说明如下所示
网吧用户视图
a.网吧客户基本详情：以客户表为主表，查找该客户绑定的用户、账号以及相关的访问记录
create view u_more_view as
select
wu.uid "账号ID",
wu.uaccount "用户账号",
wu.uname "用户名称",
cust.cust_name "客户姓名",
cust.id_card "客户IDCard",
ac.amount "账户余额",
af.flow_num "关联账户流水记录编号",
ocr.oper_time "上机时间",
br.bill_num "关联账单编号"
from wb_user wu
left join customer cust on cust.cust_id = wu.cust_id
left join account ac on ac.account_id = cust.account_id
left join account_flow af on af.account_id = ac.account_id
left join oper_cp_record ocr on ocr.oper_uid = wu.uid
left join bill_record br on br.bill_num = cust.cust_id
;
b.阶段数据统计：筛选一段时间内客户的来访情况和消费情况
create view u_charge_view as
select
wu.uid "账号ID",
wu.uname "用户名称",
cust.cust_name "客户姓名",
af.flow_num "关联账户流水记录编号",
case af.oper_type
when '1' then '充值'
when '2' then '扣除'
else '无效状态' end "操作类型",
af.create_time "操作时间",
creator.admin_name "操作人"
from wb_user wu
left join customer cust on cust.cust_id = wu.cust_id
left join account ac on ac.account_id = cust.account_id
left join account_flow af on af.account_id = ac.account_id
left join admin creator on creator.admin_id = af.create_by
;
管理员用户视图
a.数据访问权限控制：考虑不同的员工职责不同，此处限定不同的管理员拥有对客户不同的访问权限，可设定多个不同的客户视图，一一对应（可以从已有视图中摘取视图字段）
-- 初始化视图
create view u_more_en_view as
select
wu.uid "uid",
wu.uaccount "uaccount",
wu.uname "uname",
cust.cust_name "custName",
cust.id_card "idCard",
ac.amount "amount",
af.flow_num "flowNum",
ocr.oper_time "operTime",
br.bill_num "billNum"
from wb_user wu
left join customer cust on cust.cust_id = wu.cust_id
left join account ac on ac.account_id = cust.account_id
left join account_flow af on af.account_id = ac.account_id
left join oper_cp_record ocr on ocr.oper_uid = wu.uid
left join bill_record br on br.bill_num = cust.cust_id
;
-- 限定字段（控制数据访问权限）视图
create view u_more_en_la_view as
select
uv."uid",
uv."uaccount",
uv."uname",
uv."custName",
uv."idCard",
uv."amount"
from u_more_en_view uv
;
b.数据统计：结合实际场景统计客户上网情况
-- -- 用户上网统计（在u_more_en_view视图的基础上构建）
create view u_surf_chart_view as
select
uv.*,tbOcr."surfCount"
from u_more_en_view uv
left join (
select oper_uid "operUid",COUNT(record_id) "surfCount"
from oper_cp_record
where oper_type='1'
group by oper_uid)tbOcr on tbOcr."operUid" = uv."uid"
;
-- 统计每台电脑的上机记录以及电脑属性详情
create view cp_surf_chart_view as
select
cp.cnum "cnum",
cp.cname "cname",
cp.c_location "cLocation",
cp.current_state "currentState",
tbOcr."operCount" "operCount"
from computer cp
left join (
select ocr.oper_cid "operCid",COUNT(ocr.record_id) "operCount"
from oper_cp_record ocr
where oper_type='1'
group by oper_cid
)tbOcr on tbOcr."operCid" = cp.cid
;
5.3创建索引
结合系统设计，此处通过创建相关索引以提升数据库查询效率
网吧用户：
针对常用筛选关键字创建联合索引（用户账号、姓名）
--为wb_user表（用户账号、姓名）创建联合索引IDX_user_mix
create index IDX_user_mix
on wb_user (uaccount,uname);
-- 查看创建的索引
sp_helpindex wb_user;
网吧管理员
针对索引视图构建相应的索引
-- 构建索引视图（绑定到指定架构）
create view cp_simp_view with schemabinding as
select
cp.cnum "cnum",
cp.cname "cname",
cp.c_location "cLocation",
cp.current_state "currentState"
from dbo.computer cp
;
--为cp_surf_chart_view视图（电脑编号、名称）创建唯一聚集索引IDX_cp_simp_view_mix
create unique clustered index IDX_cp_simp_view_mix
on cp_simp_view(cnum,cname);
-- 查看创建的索引
sp_helpindex wb_user;
步骤3.数据库拟运行
6.数据库运行
根据系统的功能需求，模拟用户对数据库进行操作，写出相应的增删改查SQL语句。
SQL语句至少包含10个知识点，如：单表查询、分组查询、排序查询、条件查询、子查询、多表连接查询、添加数据、修改数据、删除数据、查询视图等
为了进一步了解系统功能的运行，此处从系统功能的维度阐述相关实现，sql语句涉及的语法规则参考如下说明（亦或是在模拟运行中简单进行说明）
| 模拟操作说明 | sql语句说明 |
|---|---|
| 基本数据模拟查询涉及单表操作 | 单表查询 |
| 根据场景模拟条件查询操作： between、like、and、or、in、 order by、desc（asc）等 | 条件查询（6种条件都要列出） |
| 分组统计相关（展示统计视图） | 分组查询 （至少2例，包含group by子句或having子句） |
| 检索数据相关根据场景需求进行排序操作 | 排序查询 |
| 关联数据查找模拟多表关联查询 | 子查询、多表连接查询（至少有一个三张表连接的例子） |
| 基本数据维护相关涉及 | 添加数据、修改数据 |
| 数据访问权限控制 | 查询视图 |
操作说明
基于现有系统设计，此处操作分别模拟网吧用户和网吧管理员进行操作，结合上述实验要求模拟数据库操作说明如下
网吧用户：用户的模拟运行
个人信息管理
以“u001”的用户身份进行模拟操作
查询个人信息（基本信息、关联账号信息、上网记录、账户流水等）
- sql语句
-- 查看个人账号信息
select
wu.uid "账号ID",
wu.uaccount "用户账号",
wu.uname "用户名称",
cust.cust_name "客户姓名",
cust.id_card "客户IDCard",
ac.amount "账户余额",
af.flow_num "关联账户流水记录编号"
from wb_user wu
left join customer cust on cust.cust_id = wu.cust_id
left join account ac on ac.account_id = cust.account_id
left join account_flow af on af.account_id = ac.account_id
where uid='u001';
- sql语句
-- 以视图的方式进行筛选，直接定位到个人的所有信息
select *
from u_more_view;
修改账户密码
- sql语句
update wb_user
set password = 'xxxxxx'
where uid = 'u001';
查看个人账单和上机记录
- sql语句
-- 上下机记录查看
select
wu.uid,
wu.uname,
wu.uaccount,
ocr.oper_time,
case ocr.oper_type
when '1' then '上机'
when '2' then '下机'
else '其他状态' end "operTypeDescr",
ocr.oper_descr "operDescr"
from wb_user wu
left join oper_cp_record ocr on ocr.oper_uid = wu.uid
- sql语句
-- 账单记录查看
select
wu.uid "账号ID",
wu.uaccount "用户账号",
wu.uname "用户名称",
cust.cust_name "客户姓名",
af.flow_num "关联账户流水记录编号",
case af.oper_type
when '0' then '处理汇总'
when '1' then '充值'
when '2' then '扣除'
else '其他状态' end "交易类型",
af.oper_amount "交易金额"
from wb_user wu
left join customer cust on cust.cust_id = wu.cust_id
left join account ac on ac.account_id = cust.account_id
left join account_flow af on af.account_id = ac.account_id
where uid='u001'
上下机操作
查询上下机记录，执行上下机操作；生成关联账单操作记录
上下机操作
- sql语句
insert into
oper_cp_record(record_id,oper_uid,oper_cid,oper_type,oper_time,oper_descr,create_time)
values('r0003','u001','cp002','1','2021-11-12','第二次上机','2021-11-12');
insert into
oper_cp_record(record_id,oper_uid,oper_cid,oper_type,oper_time,oper_descr,create_time)
values('r0004','u001','cp002','2','2021-11-12','下机','2021-11-12');
用户自动充值100
- sql语句
-- 插入账户流水
insert into
account_flow(flow_id,flow_num,oper_type,oper_amount,account_id,create_time,create_by)
values('f003','f003','1','100','ac001','2021-11-12','a002');
-- 更新账号余额
update account
set amount = amount+100
where account_id = 'ac001'
下机后生成计费账单并处理
- sql语句
-- 根据客户需求生成账单内容(初始化账单记录)
insert into
bill_record(bill_id,bill_num,bill_content,bill_state,charge_method,relate_cust,bill_descr,create_time,create_by)
values('b001','b001','账单消费记录详情说明','1','0','cust001','待客户确认','2021-11-12','a002');
-- 待用户确认后更新账单状态(采用现金支付的方式进行结算)
update bill_record
set bill_state = '2',charge_method='1',bill_descr='客户已采用现金方式结清账单'
where bill_id = 'b001'
-- 查询生成的账单记录
select * from bill_record where bill_id='b001'
网吧管理员：用户的模拟运行
以“a001”的管理员身份进行模拟普通经营操作，借助“a002”的管理员身份进行黑名单管理操作（权限分级控制模拟）
模拟场景
模拟场景描述：
客户C君前来上网，管理员需查找关联的用户信息的记录，发现当前用户没有开设账号，为其开设正好并分配机器供其使用，正常模拟网吧的整个运作流程
- sql语句
-- a.查询C君资料
select * from customer where cust_name = 'C君'
-- b.未查找到关联的C君信息，为其开设账号
insert into
account(account_id,amount,create_time,create_by)
values('ac002','0','2021-11-12','a001');
insert into
customer(cust_id,cust_name,id_card,gender,birthdate,cust_descr,cust_class,account_id,create_time,create_by)
values('cust002','C君','xxxxxx','1','1997-03-19','新客户','0','ac002','2021-11-12','a001');
-- 创建成功检查客户信息是否正常录入
select cust.cust_name "客户姓名",ac.amount "账户余额"
from customer cust
left join account ac on ac.account_id = cust.account_id
where cust.cust_name = 'C君';
- sql语句
-- c.客户充值1000，荣升我吧2级客户
-- 插入账户流水
insert into
account_flow(flow_id,flow_num,oper_type,oper_amount,account_id,create_time,create_by)
values('f004','f004','1','1000','ac002','2021-11-12','a001');
-- 更新账号余额
update account
set amount = amount+1000
where account_id = 'ac002';
-- 更新客户等级
update customer
set cust_class = '2',modify_time='2021-11-12',create_by='a001'
where account_id = 'ac002';
-- 查看客户信息
select
cust.cust_name "客户姓名",
case cust_class
when '0' then '普通用户'
when '1' then 'VIP1'
when '2' then 'VIP2'
when '3' then 'VIP3'
else '扩展中' end "客户等级",
ac.amount "账户余额"
from customer cust
left join account ac on ac.account_id = cust.account_id
where cust.cust_name = 'C君';
- sql语句
-- d.客户进行上机操作，管理员为其创建登陆账号并分配机器
-- 创建新账号（u002-默认密码000000）
insert into
wb_user(uid,uaccount,uname,password,cust_id,temp_flag,create_time,create_by)
values('u002','u002','C君','000000','cust_002','1','2021-11-12','a001');
-- 查看空闲机器并为客户分配机器信息
select * from computer where current_state = '1'
update computer set current_state = '2' where cid = 'cp003'
-- 用户上机
insert into
oper_cp_record(record_id,oper_uid,oper_cid,oper_type,oper_time,oper_descr,create_time)
values('r0005','u002','cp003','1','2021-11-12','上机ing','2021-11-12');
-- 查看客户状态
select
wb.uname,
wb.uaccount,
cp.cname,
case cp.current_state
when '0' then '弃用'
when '1' then '闲置中'
when '2' then '使用中'
else '扩展中' end "电脑状态"
from oper_cp_record ocr
left join computer cp on cp.cid = ocr.oper_cid
left join wb_user wb on wb.uid = ocr.oper_uid
where oper_uid = 'u002'
and oper_type = '1'
- sql语句
-- e.客户下机并使用卡结账
-- 新增下机操作记录
insert into
oper_cp_record(record_id,oper_uid,oper_cid,oper_type,oper_time,oper_descr,create_time)
values('r0006','u002','cp003','2','2021-11-12','下机','2021-11-12');
-- 插入账户流水（支出50）
insert into
account_flow(flow_id,flow_num,oper_type,oper_amount,account_id,create_time,create_by)
values('f004','f004','2','50','ac002','2021-11-12','a001');
-- 新增账单记录
insert into
bill_record(bill_id,bill_num,bill_content,bill_state,charge_method,relate_cust,bill_descr,create_time,create_by)
values('b002','b002','本次上机共消费50元','2','2','cust002','已结清','2021-11-12','a001');
-- 更新账号余额
update account
set amount = amount-50
where account_id = 'ac002'
-- 查看客户信息
网吧经营
机器管理
对网吧机器进行管理，查看网吧机器基本信息，进行相关参数维护，管理并维护机器使用状态
- sql语句
-- 录入一批新机器信息
insert into computer(cid,cnum,cname,c_param,c_location,current_state,create_time,create_by)
values('cp003','cp003','新电脑003','参数列表说明','南区001','1','2021-11-11','a001');
insert into computer(cid,cnum,cname,c_param,c_location,current_state,create_time,create_by)
values('cp004','cp004','新电脑004','参数列表说明','南区002','1','2021-11-11','a001');
insert into computer(cid,cnum,cname,c_param,c_location,current_state,create_time,create_by)
values('cp005','cp005','新电脑005','参数列表说明','南区003','1','2021-11-11','a001');
- sql语句
-- cp004电脑故障需返厂，更新电脑状态
update computer
set current_state = '0' ,modify_time='2021-11-13',modify_by='a001'
where cid='cp004'
参数维护
对网吧相关的基本参数信息进行维护，例如计费标准（上网计费、商品计费等相关服务），通过图表视图的方式整合展示给客户
- sql语句
insert into charge_param(charge_id,charge_standard,charge_descr,create_time,create_by)
values('prod001','上网标准：10/h','备注','2016-10-11','a001');
insert into charge_param(charge_id,charge_standard,charge_descr,create_time,create_by)
values('prod002','上网标准：500/月（不限时）','备注','2016-10-11','a001');
insert into charge_param(charge_id,charge_standard,charge_descr,create_time,create_by)
values('prod003','上网标准：3000/年（不限时）','备注','2016-10-11','a001');
insert into charge_param(charge_id,charge_standard,charge_descr,create_time,create_by)
values('prod004','商品标准：零食（实时价）','备注','2016-10-11','a001');
- sql语句
-- 参数录入错误：删除后重新录入
delete from charge_param where charge_id = 'prod'
- sql语句
-- 为吸引客流量，限时提供免费零食
update charge_param
set charge_standard = '商品标准：零食（限时免费）',charge_descr = '大酬宾',modify_time = '2021-11-11',modify_by='a001'
where charge_id = 'prod004'
客户信息维护、上下机管理
对客户信息和账号信息进行管理，例如账单、流水、客户基本信息，帮助客户完成充值、扣除、账单生成等操作。结合客户实际需求进行上下机管理，实现资源灵活调配
说明：针对客户信息维护相关可从用户的角度去发散，一些客户上下机、账单流水、充值扣除等操作都是由管理员角色辅助操作完成，具体内容也可参考“用户模拟操作”及基本场景模拟中的中的一些说明，记录的创建、修改者均设定为管理员ID，此处不作额外的赘述
数据统计、检索相关
- sql语句
-- 检索客户姓名中含有'A'或者账号中含有‘C’关键字的客户在202111月份的上机记录，并按照用户名称升序、操作时间降序进行排序
select
wu.uid,
wu.uname,
wu.uaccount,
cust.cust_name,
ocr.oper_time,
case ocr.oper_type
when '1' then '上机'
when '2' then '下机'
else '其他状态' end "operTypeDescr",
ocr.oper_descr "operDescr"
from wb_user wu
left join oper_cp_record ocr on ocr.oper_uid = wu.uid
left join customer cust on cust.cust_id = wu.cust_id
where 1=1
and (cust.cust_name like '%A%' or wu.uname like '%C%')
and ocr.oper_time between '2021-11-01' and '2021-11-30'
and ocr.oper_type in ('1')
order by cust.cust_name asc,ocr.oper_time desc
- sql语句
-- 统计上网次数超过1次的用户账号信息（group by、having相关）
select
wu.uid "账号id",
wu.uaccount "账号",
wu.uname "账号名称",
tbOcr."surfCount" "上机次数"
from wb_user wu
inner join (
select oper_uid "operUid",COUNT(record_id) "surfCount"
from oper_cp_record
where oper_type='1'
group by oper_uid
having COUNT(record_id)>1
)tbOcr on tbOcr."operUid" = wu.uid
黑名单管理
以“a002”的身份对黑名单用户进行管理（模拟权限分级限制概念，后续可考虑扩展角色权限分级管理控制，此处仅仅只是模拟概念）
- sql语句
-- 录入黑名单客户信息
insert into
black_list(data_id,name,id_card,gender,birthday,state,descr,create_time,create_by)
values('bl001','C君','xxxxxx','1','1997-03-19','1','C君因在网吧寻衅滋事被纳入我吧黑名单管理机制，账号封禁3日','2021-11-12','a002');
-- 对C君账号进行封禁
update wb_user
set user_state = '2',modify_time='2021-11-12',modify_by='a002'
where uid = 'u002';
- sql语句
-- 解除客户黑名单管控
update black_list
set state = '2',descr = '解禁',modify_time='2021-11-15',modify_by='a002'
where data_id = 'bl001';
-- 对C君账号进行解禁
update wb_user
set user_state = '1',modify_time='2021-11-15',modify_by='a002'
where uid = 'u002';
步骤4.数据库管理与维护
7.数据库维护
模拟数据库后台管理员，创建各数据库用户及角色，对数据库用户/角色进行授权、收权等，并可以考虑进行数据库的备份和恢复操作。请对操作结果进行截图，并作简要说明。
说明
此处参考模拟数据库后台管理员进行操作，通过SSMS进行可视化界面控制，实现数据库用户角色创建：授权/收权、数据库备份/恢复操作模拟
7.1创建数据库用户及角色
- 操作步骤说明
a.连接服务器
(服务器名称可以是主机ip或者是主机名称，初始化在安装sql server时配置了相关用户，则可通过windows身份验证的方式登录)
b.创建数据库用户
根据场景需求为指定用户构建相应的数据库角色和关联架构，此处根据小组成员角色创建两个数据库用户，并限定了其拥有“网吧前台管理系统”的访问权限