楠槡

Oracle数据库项目——MC服务器管理系统

2017-12-10

数据库设计内容,分为:系统功能说明,数据库分析与设计,数据库实现。本数据库系统提供的功能用于项目MC-Servers-Web,详情内容参见MC-Services-Web

系统功能说明

游戏管理系统,针对在线游戏进行用户管理,游戏外部购买道具等服务。
【注册用户系统】:用户在网站上注册,经管理员授权(白名单)后可以登录游戏,管理员添加用户等功能。
【用户管理系统】:对游戏用户的权限进行管理,对违规游戏用户进行管理。
【用户信息记录】:对注册用户的信息进行记录管理。
【游戏道具购买系统】:弥补游戏内部的交易,直接通过在线商城服务,通过在线充值,或者奖励的虚拟货币对游戏道具进行交易,初期设计只满足管理员上架商品,用户购买,后续增加玩家间交易系统。
【管理员上线道具】:管理员上线道具,添加库存,标记价格,设置物品对应的游戏物品ID。
【玩家购买道具】:在线购买,生成订单交易记录,扣除虚拟货币,经管理员审查后在游戏中添加游戏物品。
【交易记录】:对交易进行记录,方便随时检查回溯。
【用户操作记录】:对敏感操作进行记录。

数据库分析与设计:

给出应用系统的数据库部分的分析和设计。以图示和表的形式完成数据库分析和设计。

数据库的概念设计

绘制系统的数据库部分的E—R模型,并用文字辅以说明。

ER图

  • WebInfo 站点信息 站点标题等内容
  • Operating_Log 操作记录 对用户管理,商品添加等重要操作做记录
  • User 用户表 用户各类信息
  • Transaction_Log 交易订单信息
  • Goods 商品表 商品相关信息

    数据库的逻辑设计

    关系模型图

    数据库的物理设计

    以表单的形式给出数据库中的具体表的结构以及字段的说明

    WI
    user
    goods
    tr
    opl

    文件位置

    数据库中数据文件,控制文件,日志文件,参数文件的存取路径
    数据文件:'%ORACLE_HOME%\database\McswDb.dbf'

数据库实现

在SQLPLUS环境中输入PL/SQL代码,创建数据库中的各个对象

表空间

表空间的创建(注意:数据表空间和索引表空间分别建立在不同磁盘上)

1
2
3
4
5
6
7
CREATE TABLESPACE McswDb
DATAFILE '%ORACLE_HOME%\database\McswDb.dbf'
SIZE 100M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 300M
LOGGING
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO
;

数据表

数据表的创建

注意要有表中的约束的定义,以及表间的约束的定义。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
-- WebInfo 站点信息表
CREATE TABLE WebInfo
(
"WI_No_id" VARCHAR2(4) NOT NULL,
"WI_Site_title" VARCHAR2(32) NOT NULL,
"WI_Site_info" VARCHAR2(128) NOT NULL,
"WI_Show_text" VARCHAR2(256),
"WI_Show_img1" VARCHAR2(128) NOT NULL,
"WI_Show_img2" VARCHAR2(128),
"WI_Show_img3" VARCHAR2(128),
"WI_Show_img4" VARCHAR2(128),
PRIMARY KEY ("WI_No_id") VALIDATE
);
-- Users 用户信息表
CREATE TABLE Users
(
"Users_Id" VARCHAR2(8) NOT NULL,
"Users_Level" NUMBER(1) NOT NULL,
"Users_Name" VARCHAR2(32) NOT NULL,
"Users_Pwd" VARCHAR2(16) NOT NULL,
"Users_Email" VARCHAR2(64),
"Users_Sex" NUMBER(1) NOT NULL,
"Users_Birthday" DATE,
"Users_Cash" NUMBER(6,2) NOT NULL,
"Users_Avator" VARCHAR2(128) NOT NULL,
"Users_White" NUMBER(1) NOT NULL,
"Users_Del" NUMBER(1) NOT NULL,
PRIMARY KEY ("Users_Id") VALIDATE
);
-- Goods 物品信息表
CREATE TABLE Goods
(
"Goods_Id" VARCHAR2(8) NOT NULL,
"Goods_Info" VARCHAR2(256) NOT NULL,
"Goods_Img" VARCHAR2(128) NOT NULL,
"Goods_Nums" NUMBER(4) NOT NULL,
"Goods_Game_id" VARCHAR2(8) NOT NULL,
"Goods_Del" NUMBER(1) NOT NULL,
PRIMARY KEY ("Goods_Id") VALIDATE
);
-- Transaction_Log 订单/交易记录
CREATE TABLE Transaction_Log
(
"Tr_Id" VARCHAR2(8) NOT NULL,
"Tr_Num" NUMBER(2) NOT NULL,
"Users_Id" VARCHAR2(8) NOT NULL,
"Goods_Id" VARCHAR2(8) NOT NULL,
"Op_Check" NUMBER(1) NOT NULL,
"Check_Id" VARCHAR2(8) NOT NULL,
PRIMARY KEY ("Tr_Id") VALIDATE,
FOREIGN KEY ("Users_Id") REFERENCES "USERS" ("Users_Id") VALIDATE,
FOREIGN KEY ("Goods_Id") REFERENCES "GOODS" ("Goods_Id") VALIDATE
);
-- Operating_Log 操作记录
CREATE TABLE Operating_Log
(
"Op_Id" VARCHAR2(8) NOT NULL,
"Users_Id" VARCHAR2(8) NOT NULL,
"Op_Info" VARCHAR2(256),
PRIMARY KEY ("Op_Id") VALIDATE,
FOREIGN KEY ("Users_Id") REFERENCES "USERS" ("Users_Id") VALIDATE
);

表中数据的插入

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
insert into WebInfo values(1,'MikaCraft','公益 · 休闲 · 无收费','高度自由开放的沙盒世界,友爱互助,共同发展。定期投票更换周目,更多更新玩法,和朋友一起分享。 ','http://mdzz2333.cn/images/pic01.jpg','http://mdzz2333.cn/images/pic02.jpg','http://mdzz2333.cn/images/pic03.jpg','http://mdzz2333.cn/images/pic04.jpg');
insert into Users values('10008888','2','Admin','Pwd_123','admin@admin.com',1,to_date('2002-08-26','yyyy-mm-dd'),0,'https://ns96.com/2017/08/21/PostsDemo/mcavator.png',0,0);
insert into Users values('10000001','0','Qwerr','Pwd_123','qwerr@admin.com',1,to_date('2002-08-26','yyyy-mm-dd'),0,'https://ns96.com/2017/08/21/PostsDemo/mcavator.png',0,0);
insert into Users values('10000002','0','ASDQf','Pwd_123','ASDQf@admin.com',1,to_date('2002-08-26','yyyy-mm-dd'),0,'https://ns96.com/2017/08/21/PostsDemo/mcavator.png',0,0);
insert into Users values('10000003','0','dqwqw','Pwd_123','dqwqw@admin.com',1,to_date('2002-08-26','yyyy-mm-dd'),0,'https://ns96.com/2017/08/21/PostsDemo/mcavator.png',0,0);
insert into Users values('10000004','0','qsadw','Pwd_123','qsadw@admin.com',1,to_date('2002-08-26','yyyy-mm-dd'),0,'https://ns96.com/2017/08/21/PostsDemo/mcavator.png',0,0);
insert into Users values('10000005','0','awefq','Pwd_123','awefq@admin.com',1,to_date('2002-08-26','yyyy-mm-dd'),0,'https://ns96.com/2017/08/21/PostsDemo/mcavator.png',0,0);
insert into Users values('10000006','0','zxcaw','Pwd_123','zxcaw@admin.com',1,to_date('2002-08-26','yyyy-mm-dd'),0,'https://ns96.com/2017/08/21/PostsDemo/mcavator.png',0,0);
insert into Users values('10000007','0','zxcqw','Pwd_123','zxcqw@admin.com',1,to_date('2002-08-26','yyyy-mm-dd'),0,'https://ns96.com/2017/08/21/PostsDemo/mcavator.png',0,0);
insert into Users values('10000008','0','gdere','Pwd_123','gdere@admin.com',1,to_date('2002-08-26','yyyy-mm-dd'),0,'https://ns96.com/2017/08/21/PostsDemo/mcavator.png',0,0);
insert into Users values('10000009','0','qw123','Pwd_123','qw123@admin.com',1,to_date('2002-08-26','yyyy-mm-dd'),0,'https://ns96.com/2017/08/21/PostsDemo/mcavator.png',0,0);
insert into Goods values('ces01','ces01','ces01',999,'10000001',0);
insert into Goods values('ces02','ces02','ces02',999,'10000002',0);
insert into Goods values('ces03','ces03','ces03',999,'10000003',0);
insert into Goods values('ces04','ces04','ces04',999,'10000004',0);
insert into Goods values('ces05','ces05','ces05',999,'10000005',0);
insert into Goods values('ces06','ces06','ces06',999,'10000007',0);
insert into Goods values('ces07','ces07','ces07',999,'10000006',0);
insert into Goods values('ces08','ces08','ces08',999,'10000008',0);
insert into Goods values('ces09','ces09','ces09',999,'10000009',0);
insert into Goods values('ces10','ces10','ces10',999,'10000010',0);
insert into Transaction_Log values ('Trd00001',3,'10000001','ces03',1,'10008888');
insert into Transaction_Log values ('Trd00002',3,'10000001','ces03',1,'10008888');
insert into Transaction_Log values ('Trd00003',3,'10000001','ces03',1,'10008888');
insert into Transaction_Log values ('Trd00004',3,'10000001','ces03',1,'10008888');
insert into Transaction_Log values ('Trd00005',3,'10000001','ces03',1,'10008888');
insert into Transaction_Log values ('Trd00006',3,'10000001','ces03',1,'10008888');
insert into Transaction_Log values ('Trd00007',3,'10000001','ces03',1,'10008888');
insert into Transaction_Log values ('Trd00008',3,'10000001','ces03',1,'10008888');
insert into Transaction_Log values ('Trd00009',3,'10000001','ces03',1,'10008888');
insert into Transaction_Log values ('Trd00010',3,'10000001','ces03',1,'10008888');
insert into Operating_Log values('10008888','10000001','授权白名单');

数据的修改和删除

1
2
3
update USERS set "Users_Level"=1 where "Users_Id"='10000005';
delete from Goods where "Goods_Id"='ces08';

索引和视图

索引的创建

1
2
3
4
5
6
7
8
-- 以购买的物品ID建立索引
create INDEX Tr_idSy on Transaction_Log("Goods_Id") TABLESPACE McswDb;
-- 以用户级别建立索引
create INDEX Us_lvSy on Users("Users_Level") TABLESPACE McswDb;
-- 以物品数量建立索引
create INDEX Gd_giSy on Goods("Goods_Game_id") TABLESPACE McswDb;

视图的创建

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 查询订单信息
create or replace view vw_Users_Buy
as
select "Users_Id","Goods_Id","Tr_Num"
from Transaction_Log
order by "Tr_Id";
-- 查询用户的订单
create or replace view vm_Tr_info
as
select "Users_Id","Goods_Id","Tr_Num"
from Transaction_Log
order by "Users_Id";
-- 查库存
create or replace view vm_gd_num
as
select "Goods_Id","Goods_Nums","Goods_Game_id"
from Goods
order by "Goods_Id";

数据的查询

– 查询用户信息

1
2
3
4
5
--查询拥有白名单的玩家
select * from users where "Users_White"=1;
--查询交易记录,以交易编号为顺序
select * from Transaction_Log order by"Tr_Id";

– 视图查询

1
2
3
4
5
6
7
8
-- 库存视图
select * from vm_gd_num;
-- 订单视图
select * from vm_Tr_info;
-- 订单视图
select * from vw_Users_Buy;

PL/SQL程序块

存储过程的定义

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
--添加用户的存储过程
CREATE OR REPLACE PROCEDURE user_insert
(u_id IN Users.Users_Id%TYPE,
u_lv IN Users.Users_Level%TYPE,
u_name IN Users.Users_Name%TYPE,
u_pwd IN Users.Users_Pwd%TYPE,
u_em IN Users.Users_Email%TYPE,
u_sex IN Users.Users_Sex%TYPE,
u_cash IN Users.Users_Cash%TYPE,
u_Ava IN Users.Users_Avator%TYPE,
u_Wi IN Users.Users_White%TYPE,
u_de IN Users.Users_Del%TYPE
)
IS
BEGIN
insert into Users('Users_Id',Users_Level,'Users_Name','Users_Pwd','User_Email',Users_Sex,Users_Cash,'Users_Avator',Users_White,Users_Del)
values(u_id,u_lv,u_name,u_pwd,u_em,u_sex,u_cash,u_Ava,u_Wi,u_de);
commit;
END user_insert;

函数的定义

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE OR REPLACE FUNCTION good_check
(f_goodsid Goods."Goods_Id"%type
)
RETURN Goods."Goods_Game_id"%TYPE
AS
v_g_rname Goods."Goods_Game_id"%TYPE;
BEGIN
select "Goods_Game_id" into v_g_rname FROM Goods
where "Goods_Id"=f_goodsid;
RETURN v_g_rname ;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('没有找到对应游戏物品!');
END good_check;

触发器的定义

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
--序列
create sequence Users_sequence
increment by 1
start with 10000020
nomaxvalue
nocycle
nocache
--触发器
CREATE TRIGGER Users_idadd
BEFORE INSERT ON Users
FOR EACH ROW
WHEN(NEW."Users_Id" is null) --当输入ID为空时启动触发器
BEGIN
select Users_sequence.nextval into :new."Users_Id" from sys.dual;
END;

新增用户功能

使用存储过程,添加用户信息。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
--输入Users表的各个字段信息
declare
u_id IN Users.Users_Id%TYPE,
u_lv IN Users.Users_Level%TYPE,
u_name IN Users.Users_Name%TYPE,
u_pwd IN Users.Users_Pwd%TYPE,
u_em IN Users.Users_Email%TYPE,
u_sex IN Users.Users_Sex%TYPE,
u_cash IN Users.Users_Cash%TYPE,
u_Ava IN Users.Users_Avator%TYPE,
u_Wi IN Users.Users_White%TYPE,
u_de IN Users.Users_Del%TYPE
begin
u_id:=&id;
u_lv:=&lv;
v_name:=&name;
u_pwd:=&pwd;
u_em:=&em;
u_sex:=&sex;
u_cash:=&cash;
u_Ava:=&ava;
u_Wi:=&wi;
u_de:=&de;
user_insert(u_id,u_lv,v_name,u_pwd,u_em,u_sex,u_cash,u_Ava,u_Wi,u_de);
DBMS_OUTPUT.PUT_LINE('用户添加成功!');
end;
/

检查商品的对应游戏物品ID

管理员检查交易信息或确认交易信息时,使用该函数查询并获取游戏ID。

1
select good_check('ces01') from dual;

用户权限和角色管理

1
2
3
4
5
6
7
8
-- 创建用户 McswAdmin
CREATE USER McswAdmin
IDENTIFIED BY pwd_Mc123
PROFILE "DEFAULT"
DEFAULT TABLESPACE RSGLspace1
;
ALTER USER McswAdmin QUOTA 30M ON McswDb;

当前数据库管理用户 McswAdmin 授予权限:

1
2
3
4
GRANT create session,create table TO McswAdmin;
GRANT create view TO McswAdmin;
GRANT CREATE ANY PROCEDURE TO McswAdmin;
GRANT CREATE ANY TRIGGER, create ANY sequence TO McswAdmin;

数据的备份和恢复,导入和导出。

1
2
set oracle_sid=orcl
exp userid= McswAdmin/pwd_Mc123 direct=y grants=y file=C:\CBackUp\McswDb.dmp;
Tags: Other
使用支付宝打赏
使用微信打赏

若你觉得我的文章对您有帮助,点击上方按钮请我喝杯咖啡☕

若文章中存在问题,或您有任何意见和疑问,均可与我联系

扫描二维码,分享此文章 📱