实验内容及完成情况:
一、 需求分析
设计一个简单的游戏数据库。可以为用户提供登录游戏、游戏充值、参与游戏活动的功能。并记录这些行为发生的数据。可以对这些数据进行查询、增加、修改、删除。并记录用户在游戏中产生的与游戏相关的数据,例如游戏角色等内容。
二、 概念设计——ER图
三、 逻辑设计——关系模式
四、 SQL代码
1、 建表
/1用户表/
id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,`name` char(20),account char(20),money DOUBLE,`password` char(20),identity char(10),type char(10),`creat_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,`update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP)engine = innodb,default charset utf8;/*2游戏类型表*/CREATE TABLE game_type(id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,`name` char(10),`creat_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,`update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP)engine = innodb,default charset utf8;/*3在线表*/CREATE TABLE online(id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,`status` char(4),user_id INTEGER,game_id INTEGER,FOREIGN KEY(user_id) REFERENCES user (id)on DELETE set null on UPDATE CASCADE,FOREIGN KEY(game_id) REFERENCES game (id)on DELETE set null on UPDATE CASCADE,`creat_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,`update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP)engine = innodb,default charset utf8;/*4游戏表*/CREATE TABLE game(id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,introduce char(40),type_id INTEGER ,`name` char(10),FOREIGN KEY(type_id) REFERENCES game_type (id)on DELETE set null on UPDATE CASCADE,`creat_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,`update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP)engine = innodb,default charset utf8;/*5活动表*/CREATE TABLE activity(id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,`name` char(10),introduce char(40),game_id INTEGER,FOREIGN KEY(game_id) REFERENCES game (id)on DELETE set null on UPDATE CASCADE,`creat_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,`update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP)engine = innodb,default charset utf8;/*6用户道具表*/CREATE TABLE user_props(id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,num INTEGER,user_id INTEGER,props_id INTEGER,FOREIGN KEY(user_id) REFERENCES user (id)on DELETE set null on UPDATE CASCADE,FOREIGN KEY(props_id) REFERENCES props (id)on DELETE set null on UPDATE CASCADE,`creat_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,`update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP)engine = innodb,default charset utf8;/*7道具表*/CREATE TABLE props(id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,`function` char(20),/*功能*/`name` char(20),`how_get` char(20),`pay` char(20),game_id INTEGER,FOREIGN KEY(game_id) REFERENCES game (id)on DELETE set null on UPDATE CASCADE,`creat_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,`update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP)engine = innodb,default charset utf8;/*8用户数据表*/CREATE TABLE user_data(id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,game_part INTEGER, /*游戏关卡*/recode DOUBLE, /*成绩记录*/user_id INTEGER,game_id INTEGER,FOREIGN KEY(user_id) REFERENCES user (id)on DELETE set null on UPDATE CASCADE,FOREIGN KEY(game_id) REFERENCES game (id)on DELETE set null on UPDATE CASCADE,`creat_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,`update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP)engine = innodb,default charset utf8;/*9充值表*/CREATE TABLE recharge(id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,money DOUBLE,user_id INTEGER,game_id INTEGER,FOREIGN KEY(user_id) REFERENCES user (id)on DELETE set null on UPDATE CASCADE,FOREIGN KEY(game_id) REFERENCES game (id)on DELETE set null on UPDATE CASCADE,`creat_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,`update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP)engine = innodb,default charset utf8;/*用户活动表*/CREATE TABLE user_activity(id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,user_id INTEGER,activity_id INTEGER UNSIGNED,FOREIGN KEY(user_id) REFERENCES `user` (id)on DELETE set null on UPDATE CASCADE,FOREIGN KEY(activity_id) REFERENCES activity (id)on DELETE set null on UPDATE CASCADE,`creat_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,`update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP)engine = innodb,default charset utf8;
2、 SQL查询
SELECT COUNT(*) login_num from `online` WHERE TO_DAYS(creat_time) = TO_DAYS(NOW());/*当天所有人的登录人数*/SELECT COUNT(*) `online_num` FROM `online` WHERE `status` = 1 AND game_id = 1;/*当前游戏1的在线人数*/SELECT sum(money) FROM recharge WHERE game_id = 1 AND TO_DAYS(creat_time) = TO_DAYS(NOW());/*游戏1当天的付款总额*/SELECT `user`.type `类型`,COUNT(*) 数量 FROM `online`,`user` WHERE `user`.id = `online`.user_id AND TO_DAYS(`online`.creat_time) = TO_DAYS(NOW())GROUP BY `user`.type;/*付费与非付费的用户登录统计*/SELECT COUNT(*) 人数 FROM user_activity,`online` WHERE `online`.user_id = user_activity.user_id AND `online`.game_id = 1;/*登录了游戏1且参与了活动*/
3、 存储过程和函数
/*用户充值函数*/CREATE PROCEDURE charge(in cuser_id INTEGER,in cmoney DOUBLE,in cgame_id INTEGER)DETERMINISTICBEGININSERT INTO recharge(user_id,money,game_id) VALUE (cuser_id,cmoney,cgame_id);UPDATE `user` set user.money = user.money + cmoney WHERE user.id = cuser_id;UPDATE `user` SET `user`.type = 1 WHERE `user`.id = cuser_id;/*充值后设为付费用户*/ENDCALL charge(2,100,1);/*查询给定用户的充值次数*/CREATE PROCEDURE user_charge_time(in cuser_id INTEGER,out time INTEGER)BEGINSELECT COUNT(*) into time FROM recharge WHERE user_id = cuser_id;ENDCALL user_charge_time(1,@time);SELECT @time;
五、 测试结果
1、 创建语句测试结果:
成功创建
2、 查询语句测试结果:
当天登录游戏1的人数
游戏1的当前在线人数
用户1的充值总金额
1为充值用户,0为非充值用户
的统计结果。
当天登录了游戏A的并且参与了活动的人数
3、 存储过程函数测试结果:
调用充值函数:
充值记录:
用户记录:
测试成功,刚才第三个用户为非充值用户。