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

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

系统功能说明

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

数据库分析与设计:

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

数据库的概念设计

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

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

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

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

数据库实现

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

表空间

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

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
 ;

数据表

数据表的创建

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

-- 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
);

表中数据的插入

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','授权白名单');

数据的修改和删除

update USERS set "Users_Level"=1 where "Users_Id"='10000005';

delete from Goods where "Goods_Id"='ces08';

索引和视图

索引的创建

-- 以购买的物品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;

视图的创建

-- 查询订单信息
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";

数据的查询

– 查询用户信息

--查询拥有白名单的玩家
select * from users where "Users_White"=1;

--查询交易记录,以交易编号为顺序
select * from Transaction_Log order by"Tr_Id";

– 视图查询

-- 库存视图
select * from vm_gd_num;

-- 订单视图
select * from vm_Tr_info;

-- 订单视图
select * from vw_Users_Buy;

PL/SQL程序块

存储过程的定义

--添加用户的存储过程
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;

函数的定义

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;

触发器的定义

--序列
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;

新增用户功能

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

--输入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。

select good_check('ces01') from dual;

用户权限和角色管理

-- 创建用户 McswAdmin
 CREATE USER McswAdmin
   IDENTIFIED BY pwd_Mc123
   PROFILE "DEFAULT"
   DEFAULT TABLESPACE RSGLspace1
   ;

ALTER USER McswAdmin QUOTA 30M ON McswDb;

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

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;

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

set oracle_sid=orcl
exp userid= McswAdmin/pwd_Mc123 direct=y  grants=y file=C:\CBackUp\McswDb.dmp;

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏数据和云

举一反三-分区裁剪作用的“新”发现

作者介绍 ? 赵勇 云和恩墨北区技术工程师 专注于SQL审核和优化相关工作。曾经服务的客户涉及金融保险、电信运营商、政府、生产制造等行业。 分区裁剪的定义 分区...

28210
来自专栏扎心了老铁

Elasticsearch-sql 用SQL查询Elasticsearch

Elasticsearch的查询语言(DSL)真是不好写,偏偏查询的功能千奇百怪,filter/query/match/agg/geo各种各样,不管你是通过封装...

1.2K4
来自专栏数据和云

循序渐进:Oracle 12c新特性Sharding技术解读

引言 数据库构架设计中主要有 Shared Everthting、Shared Nothing 和 Shared Disk: Shared Everthting...

4617
来自专栏北京马哥教育

MySQL 5.7原生JSON格式支持

在MySQL与PostgreSQL的对比中,PG的JSON格式支持优势总是不断被拿来比较。其实早先MariaDB也有对非结构化的数据进行存 储的方案,称为dyn...

3436
来自专栏帘卷西风的专栏

MySQL存储过程where条件执行失败的问题

      前几天对服务器实体做了属性缓存机制,当时测试也没有出现大的问题,昨天有人跟我说,登陆的时候角色等级显示错误,我复测了一下,发现不只是等级错误,进入...

862
来自专栏杨建荣的学习笔记

system表空间不足的问题分析(二) (r8笔记第5天)

今天收到一条不太起眼的报警邮件,大体内容是某个表空间的空间有些紧张了。大体内容如下: Tablesapce: CMBI_SNZG_DATA: 92.2% [W...

3437
来自专栏携程技术中心

干货 | 一个MySQL 5.7 分区表性能下降的案例分析

作者简介 姜宇祥,2012年加入携程,10年数据库核心代码开发经验,相关开发涉及达梦,MySQL数据库。现致力于携程MySQL的底层研发,为特殊问题定位和处理提...

4747
来自专栏L宝宝聊IT

索引、视图、存储过程和触发器的应用

1678
来自专栏idba

insert 语句加锁机制

之前的文章里面总结了很多死锁案例,其实里面有几篇文章对于insert加锁流程表述的不准确,而且微信公众号又无法修改,所以通过本文重新梳理insert...

1293
来自专栏企鹅号快讯

从商用到开源:15个维度,全面剖析DB2与MySQL数据库的差异

编辑手记 MySQL是目前最流行的开源数据库,由于其部署方便,运维简单,被广泛用于互联网的各个领域。随着整体IT架构的变更,传统的金融,电信业务,也逐渐走上从商...

2379

扫码关注云+社区

领取腾讯云代金券