团队项目----德州扑克数据库设计之草稿版

　　在本次团队项目中我主要是负责数据库的设计，由于知识能力有限，我一边学习相关知识，一边进行项目的实践练习。

　　

　　由于能力不足，本项目使用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)	真实姓名
QQ	Varchar(45)	QQ号
Phone	Varcher(45)	电话号码
Email	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）

转载于:https://www.cnblogs.com/qiangzai/p/8032833.html