SCAU2021数据库综合性实验报告

实验内容及完成情况：
一、 需求分析
设计一个简单的游戏数据库。可以为用户提供登录游戏、游戏充值、参与游戏活动的功能。并记录这些行为发生的数据。可以对这些数据进行查询、增加、修改、删除。并记录用户在游戏中产生的与游戏相关的数据，例如游戏角色等内容。
二、 概念设计——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)
DETERMINISTIC
	BEGIN
		INSERT 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;/*充值后设为付费用户*/
	END

	
	CALL charge(2,100,1);
	/*查询给定用户的充值次数*/
CREATE PROCEDURE user_charge_time(in cuser_id INTEGER,out time INTEGER)
	BEGIN
		SELECT COUNT(*) into time FROM recharge WHERE user_id = cuser_id;
	END
	
	CALL user_charge_time(1,@time);
	SELECT @time;

五、 测试结果
1、 创建语句测试结果：

成功创建
2、 查询语句测试结果：

当天登录游戏1的人数

游戏1的当前在线人数

用户1的充值总金额

1为充值用户，0为非充值用户
的统计结果。

当天登录了游戏A的并且参与了活动的人数
3、 存储过程函数测试结果：
调用充值函数：

充值记录：

用户记录：

测试成功，刚才第三个用户为非充值用户。