团队项目----德州扑克数据库设计之草稿版
在本次团队项目中我主要是负责数据库的设计,由于知识能力有限,我一边学习相关知识,一边进行项目的实践练习。
由于能力不足,本项目使用MYSQL创建一个数据库game_db,其中包括四张表,分别是用户登录信息表(accountinfo),用户详细信息表(individual_info),用户分数表(gamescoreinfo),房间信息表(house):
accountinfo表:
属性名称 |
数据类型 |
属性描述 |
UserID |
int |
用户ID |
GameID |
int |
游戏ID |
Account |
Varchar(45) |
账户名 |
Password |
Varchar(45) |
密码 |
Sex |
Varchar(10) |
性别 |
MemberOrder |
Int |
会员等级 |
AccountsInfocol |
Varchar(45) |
账户信息 |
GameLogonTime |
int |
登录时间 |
Individual_info表:
属性名称 |
数据类型 |
属性描述 |
UserID |
int |
用户ID |
Really_name |
Varchar(45) |
真实姓名 |
|
Varchar(45) |
QQ号 |
Phone |
Varcher(45) |
电话号码 |
|
Varchar(45) |
邮箱地址 |
Gamescoreinfo表:
属性名称 |
数据类型 |
属性描述 |
UserID |
int |
用户ID |
Score |
Int |
游戏分数 |
WinCount |
Varchar(45) |
胜利局数 |
LostCount |
Varchar(45) |
输局数 |
DrawCount |
Varchar(45) |
和局数 |
RunCount |
Varchar(45) |
逃跑局数 |
AllLogonTimes |
Varchar(45) |
总共登录时间 |
OnlineTimeCount |
Varchar(45) |
在线次数 |
house表:
属性名称 |
数据类型 |
属性描述 |
UserID |
Int |
用户ID |
Score |
Varchar(45) |
分数 |
houseID |
Varchar(45) |
房间号 |
EnterTime |
Varchar(45) |
进入时间 |
LeaveTime |
Varchar(45) |
离开时间 |
建库,表和插入数据SQL语句:
CREATE SCHEMA `game_db` ;
CREATE TABLE `game_db`.`accountsinfo` (
`UserID` INT NOT NULL AUTO_INCREMENT,
`GameID` INT NOT NULL,
`Account` VARCHAR(45) BINARY NOT NULL,
`Password` VARCHAR(45) BINARY NULL DEFAULT 0,
`Sex` VARCHAR(10) NOT NULL DEFAULT 'nan',
`MemberOrder` INT NOT NULL,
`AccountsInfocol` VARCHAR(45) NOT NULL,
`GameLogonTime` INT UNSIGNED NOT NULL,
PRIMARY KEY (`UserID`),
UNIQUE INDEX `Account_UNIQUE` (`Account` ASC));
SELECT * FROM game_db.accountsinfo;
INSERT INTO `game_db`.`accountsinfo` (`UserID`, `GameID`, `Account`, `Password`, `Sex`, `MemberOrder`, `GameLogonTime`) VALUES ('1', '1', 'LiMing', '123', 'nan', '1', '5');
INSERT INTO `game_db`.`accountsinfo` (`UserID`, `GameID`, `Account`, `Password`, `Sex`, `MemberOrder`, `GameLogonTime`) VALUES ('2', '2', 'Bob', '111', 'nv', '2', '6');
INSERT INTO `game_db`.`accountsinfo` (`UserID`, `GameID`, `Account`, `Password`, `Sex`, `MemberOrder`, `GameLogonTime`) VALUES ('3', '3', 'qinagzai', '222', 'nan', '5', '3');
INSERT INTO `game_db`.`accountsinfo` (`UserID`, `GameID`, `Account`, `Password`, `Sex`, `MemberOrder`, `GameLogonTime`) VALUES ('4', '4', 'jier', '333', 'nv', '4', '2');
INSERT INTO `game_db`.`accountsinfo` (`UserID`, `GameID`, `Account`, `Password`, `Sex`, `MemberOrder`, `AccountsInfocol`, `GameLogonTime`) VALUES ('5', '5', 'lucy', '1234', 'nv', '7', '1', '4');
CREATE TABLE `game_db`.`individual_info` (
`UserID` INT NOT NULL,
`Really_name` VARCHAR(45) NOT NULL,
`QQ` VARCHAR(45) NOT NULL,
`Phone` VARCHAR(45) NOT NULL,
`Email` VARCHAR(45) NOT NULL,
PRIMARY KEY (`UserID`));
SELECT * FROM game_db.individual_info;
INSERT INTO `game_db`.`individual_info` (`UserID`, `Really_name`, `QQ`, `Phone`, `Email`) VALUES ('1', 'AA', '12345677', '13177789865', '89669913@qq.com');
INSERT INTO `game_db`.`individual_info` (`UserID`, `Really_name`, `QQ`, `Phone`, `Email`) VALUES ('2', 'BB', '1223453', '17738724089', '12423453@qq.com');
INSERT INTO `game_db`.`individual_info` (`UserID`, `Really_name`, `QQ`, `Phone`, `Email`) VALUES ('3', 'CC', '45665654', '17890762354', '866753472@qq.com');
INSERT INTO `game_db`.`individual_info` (`UserID`, `Really_name`, `QQ`, `Phone`, `Email`) VALUES ('4', 'DD', '344245345', '13798082376', '7576573@163.com');
INSERT INTO `game_db`.`individual_info` (`UserID`, `Really_name`, `QQ`, `Phone`, `Email`) VALUES ('5', 'EE', '5645665', '197662762', '86964369@qq.com');
CREATE TABLE `game_db`.`gamescoreinfo` (
`UserID` INT NOT NULL AUTO_INCREMENT,
`Score` INT NOT NULL DEFAULT 0,
`WinCount` VARCHAR(45) NOT NULL DEFAULT 0,
`LostCount` VARCHAR(45) NOT NULL DEFAULT 0,
`DrawCount` VARCHAR(45) NOT NULL DEFAULT 0,
`RunCount` VARCHAR(45) NOT NULL DEFAULT 0,
`AllLogonTimes` VARCHAR(45) NOT NULL DEFAULT 0,
`OnlineTimeCount` VARCHAR(45) NOT NULL,
PRIMARY KEY (`UserID`));
SELECT * FROM game_db.gamescoreinfo;
INSERT INTO `game_db`.`gamescoreinfo` (`UserID`, `Score`, `WinCount`, `LostCount`, `DrawCount`, `RunCount`, `AllLogonTimes`, `OnlineTimeCount`) VALUES ('1', '199', '7', '3', '2', '4', '16', '1112');
INSERT INTO `game_db`.`gamescoreinfo` (`UserID`, `Score`, `WinCount`, `LostCount`, `DrawCount`, `RunCount`, `AllLogonTimes`, `OnlineTimeCount`) VALUES ('2', '355', '9', '5', '5', '3', '22', '3323');
INSERT INTO `game_db`.`gamescoreinfo` (`UserID`, `Score`, `WinCount`, `LostCount`, `DrawCount`, `RunCount`, `AllLogonTimes`, `OnlineTimeCount`) VALUES ('3', '678', '4', '1', '4', '3', '12', '3254');
INSERT INTO `game_db`.`gamescoreinfo` (`UserID`, `Score`, `WinCount`, `LostCount`, `DrawCount`, `RunCount`, `AllLogonTimes`, `OnlineTimeCount`) VALUES ('4', '321', '5', '6', '3', '5', '19', '4567');
INSERT INTO `game_db`.`gamescoreinfo` (`UserID`, `Score`, `WinCount`, `LostCount`, `DrawCount`, `RunCount`, `AllLogonTimes`, `OnlineTimeCount`) VALUES ('5', '567', '6', '2', '4', '7', '19', '3456');
CREATE TABLE `game_db`.`house` (
`UserID` INT NOT NULL AUTO_INCREMENT,
`Score` VARCHAR(45) NOT NULL,
`houseID` VARCHAR(45) NOT NULL,
`EnterTime` VARCHAR(45) NOT NULL DEFAULT 0,
`LeaveTime` VARCHAR(45) NOT NULL,
PRIMARY KEY (`UserID`));
SELECT * FROM game_db.house;
INSERT INTO `game_db`.`house` (`UserID`, `Score`, `houseID`, `EnterTime`, `LeaveTime`) VALUES ('1', '123', '1', '12', '345');
数据库运行截图如下:
数据库文件已上传GitHub:https://github.com/sacfwe/GameDB
此版错误较多,请看改进版(http://www.cnblogs.com/qiangzai/p/8035265.html)