;
;
;
;
;
;
CREATE DATABASE `qq_project` ;
USE `qq_project`;
DROP TABLE IF EXISTS `administrator`;
CREATE TABLE `administrator` (
`id` int(11) NOT NULL,
`Name` varchar(20) DEFAULT NULL,
`password` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `Name` (`Name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `chatinfo`;
CREATE TABLE `chatinfo` (
`cno` bigint(2) NOT NULL,
`csendqq` bigint(5) DEFAULT NULL,
`creceiveqq` bigint(5) DEFAULT NULL,
`cdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`tno` bigint(3) DEFAULT NULL,
PRIMARY KEY (`cno`),
KEY `fk__chatinfo_csendqq__userinfo_qq` (`csendqq`),
KEY `fk__chatinfo_creceiveqq__userinfo_qq` (`creceiveqq`),
KEY `fk__chatinfo_tno__text_tno` (`tno`),
CONSTRAINT `fk__chatinfo_creceiveqq__userinfo_qq` FOREIGN KEY (`creceiveqq`) REFERENCES `userinfo` (`qq`) ON DELETE CASCADE,
CONSTRAINT `fk__chatinfo_csendqq__userinfo_qq` FOREIGN KEY (`csendqq`) REFERENCES `userinfo` (`qq`) ON DELETE CASCADE,
CONSTRAINT `fk__chatinfo_tno__text_tno` FOREIGN KEY (`tno`) REFERENCES `text` (`tno`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `dinnertable`;
CREATE TABLE `dinnertable` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`tableName` varchar(20) DEFAULT NULL,
`tableStatus` int(11) DEFAULT '0',
`orderDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `tableName` (`tableName`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `food`;
CREATE TABLE `food` (
`id` int(11) NOT NULL,
`foodName` varchar(20) DEFAULT NULL,
`foodType_id` int(11) DEFAULT NULL,
`price` decimal(5,2) DEFAULT NULL,
`VIPprice` decimal(5,2) DEFAULT NULL,
`remark` varchar(200) DEFAULT NULL,
`img` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `foodtype`;
CREATE TABLE `foodtype` (
`id` int(11) NOT NULL,
`typeName` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `friends`;
CREATE TABLE `friends` (
`Fno` bigint(2) NOT NULL,
`fqq` bigint(5) DEFAULT NULL,
`fsno` bigint(2) NOT NULL,
`fdate` datetime DEFAULT NULL,
`Fstatus` bigint(2) DEFAULT NULL,
`qq` bigint(5) DEFAULT NULL,
PRIMARY KEY (`Fno`),
KEY `fk__friends_fqq__userinfo_qq` (`fqq`),
KEY `fk__friends_fsno__subgroup_sno` (`fsno`),
KEY `fk__friends_qq__userinfo_qq` (`qq`),
CONSTRAINT `fk__friends_fqq__userinfo_qq` FOREIGN KEY (`fqq`) REFERENCES `userinfo` (`qq`) ON DELETE CASCADE,
CONSTRAINT `fk__friends_fsno__subgroup_sno` FOREIGN KEY (`fsno`) REFERENCES `subgroup` (`sno`) ON DELETE CASCADE,
CONSTRAINT `fk__friends_qq__userinfo_qq` FOREIGN KEY (`qq`) REFERENCES `userinfo` (`qq`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `grouptable`;
CREATE TABLE `grouptable` (
`gno` bigint(5) NOT NULL,
`gname` varchar(20) NOT NULL,
`gdate` datetime DEFAULT NULL,
PRIMARY KEY (`gno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `login`;
CREATE TABLE `login` (
`Ino` bigint(5) NOT NULL,
`Lip` varchar(20) DEFAULT NULL,
`Iport` bigint(5) DEFAULT NULL,
`Idate` datetime DEFAULT NULL,
`Istatus` bigint(1) DEFAULT NULL,
`Iqq` bigint(5) NOT NULL,
PRIMARY KEY (`Ino`),
KEY `fk__login_Iqq__userinfo_qq` (`Iqq`),
CONSTRAINT `fk__login_Iqq__userinfo_qq` FOREIGN KEY (`Iqq`) REFERENCES `userinfo` (`qq`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `orderdetail`;
CREATE TABLE `orderdetail` (
`id` int(11) NOT NULL,
`orderId` int(11) DEFAULT NULL,
`food_id` int(11) DEFAULT NULL,
`foodCount` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `orders`;
CREATE TABLE `orders` (
`id` int(11) NOT NULL,
`table_id` int(11) DEFAULT NULL,
`orderDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`totalPrice` decimal(5,2) DEFAULT NULL,
`orderStatus` int(11) DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `subgroup`;
CREATE TABLE `subgroup` (
`sno` bigint(2) NOT NULL,
`sname` varchar(20) NOT NULL,
`sdate` datetime DEFAULT CURRENT_TIMESTAMP,
`qq` bigint(5) NOT NULL,
PRIMARY KEY (`sno`),
KEY `fk__subgroup_qq__userinfo_qq` (`qq`),
CONSTRAINT `fk__subgroup_qq__userinfo_qq` FOREIGN KEY (`qq`) REFERENCES `userinfo` (`qq`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `text`;
CREATE TABLE `text` (
`tno` bigint(3) NOT NULL,
`tcontext` varchar(200) DEFAULT NULL,
`tfonttype` varchar(10) DEFAULT NULL,
`tfontsize` bigint(5) DEFAULT NULL,
`tfontcolor` varchar(5) DEFAULT NULL,
PRIMARY KEY (`tno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `user_group`;
CREATE TABLE `user_group` (
`ugno` bigint(2) NOT NULL,
`qq` bigint(5) NOT NULL,
`gno` bigint(5) NOT NULL,
PRIMARY KEY (`ugno`),
KEY `fk__user_group_qq__userinfo_qq` (`qq`),
KEY `fk__user_group_gno__grouptable_gno` (`gno`),
CONSTRAINT `fk__user_group_gno__grouptable_gno` FOREIGN KEY (`gno`) REFERENCES `grouptable` (`gno`) ON DELETE CASCADE,
CONSTRAINT `fk__user_group_qq__userinfo_qq` FOREIGN KEY (`qq`) REFERENCES `userinfo` (`qq`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `userinfo`;
CREATE TABLE `userinfo` (
`qq` bigint(5) NOT NULL,
`pwd` varchar(10) NOT NULL,
`sign` varchar(30) DEFAULT NULL,
`photoID` bigint(2) DEFAULT NULL,
`nickname` varchar(10) NOT NULL,
`sex` char(2) NOT NULL,
`birthday` datetime DEFAULT NULL,
`constellation` varchar(60) DEFAULT NULL,
`bloodType` char(10) DEFAULT NULL,
`diploma` varchar(10) DEFAULT NULL,
`telephone` varchar(15) DEFAULT NULL,
`email` varchar(20) DEFAULT NULL,
`address` varchar(20) DEFAULT NULL,
PRIMARY KEY (`qq`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
