mysql 创建数据库、表结构。后面关于mysql的博文可能会用到这个结构
/*=================
删除数据库
===================*/
DROP DATABASE IF EXISTS wechatplugin;
/*=================
#创建数据库
===================*/
#create database wechatplugin default charset utf8
CREATE DATABASE wechatplugin DEFAULT CHARSET utf8 COLLATE utf8_general_ci ;
/*======================
打开数据库
========================*/
USE wechatPlugin;
/*==============================================================*/
/* DBMS name: MySQL 5.0 */
/* Created on: 2016/11/18 20:46:58 */
/*==============================================================*/
DROP TABLE IF EXISTS PLUGIN;
DROP TABLE IF EXISTS plugin_Company;
DROP TABLE IF EXISTS plugin_WechatVersion;
DROP TABLE IF EXISTS plugin_companyPlugins;
DROP TABLE IF EXISTS plugin_person;
DROP TABLE IF EXISTS plugin_pluginVersion;
DROP TABLE IF EXISTS plugin_userLog;
DROP TABLE IF EXISTS plugin_userPlugins;
/*==============================================================*/
/* Table: plugin */
/*==============================================================*/
CREATE TABLE PLUGIN
(
pluginName VARCHAR(32) NOT NULL,
pluginDesc VARCHAR(255) NOT NULL,
funDesc VARCHAR(255),
dayFreeCount INT DEFAULT 5,
PRIMARY KEY (pluginName)
);
/*==============================================================*/
/* Table: plugin_Company */
/*==============================================================*/
CREATE TABLE plugin_Company
(
companyName VARCHAR(32) NOT NULL,
companyDesc VARCHAR(255),
firstUseTime DATETIME,
PRIMARY KEY (companyName)
);
/*==============================================================*/
/* Table: plugin_WechatVersion */
/*==============================================================*/
CREATE TABLE plugin_WechatVersion
(
wechatVersion VARCHAR(32) NOT NULL,
wechatDesc VARCHAR(255),
downlaodLink VARCHAR(255),
isSupport TINYINT,
PRIMARY KEY (wechatVersion)
);
/*==============================================================*/
/* Table: plugin_companyPlugins */
/*==============================================================*/
CREATE TABLE plugin_companyPlugins
(
companyName VARCHAR(32),
pluginName VARCHAR(32),
buyClientCount INT NOT NULL
);
UPDATE plugin_companyplugins SET pluginName = TRIM(pluginName) ;
/*==============================================================*/
/* Table: plugin_person */
/*==============================================================*/
CREATE TABLE plugin_person
(
alias VARCHAR(32) NOT NULL,
companyName VARCHAR(32),
wechatVersion VARCHAR(32),
Imei VARCHAR(32),
uin VARCHAR(32),
firstUseTime DATETIME,
isBuy TINYINT,
isWechatLogin TINYINT,
userDesc VARCHAR(128),
PRIMARY KEY (alias)
);
/*==============================================================*/
/* Table: plugin_pluginVersion */
/*==============================================================*/
CREATE TABLE plugin_pluginVersion
(
pluginName VARCHAR(32) NOT NULL,
wechatVersion VARCHAR(32) NOT NULL,
downlaodLink VARCHAR(255),
configLink VARCHAR(128),
PRIMARY KEY (pluginName, wechatVersion)
);
/*==============================================================*/
/* Table: plugin_userLog */
/*==============================================================*/
CREATE TABLE plugin_userLog
(
pluginName VARCHAR(32),
wechatVersion VARCHAR(32),
alias VARCHAR(32),
logType INT ,
logInfo VARCHAR(255),
tag VARCHAR(32)
);
/*==============================================================*/
/* Table: plugin_userPlugins */
/*==============================================================*/
CREATE TABLE plugin_userPlugins
(
alias VARCHAR(32),
pluginName VARCHAR(32),
trailCount INTEGER DEFAULT 5
);
ALTER TABLE plugin_companyPlugins ADD CONSTRAINT FK_Reference_9 FOREIGN KEY (pluginName)
REFERENCES PLUGIN (pluginName) ON DELETE RESTRICT ON UPDATE RESTRICT;
ALTER TABLE plugin_companyPlugins ADD CONSTRAINT FK_pluginsTocompany FOREIGN KEY (companyName)
REFERENCES plugin_Company (companyName) ON DELETE RESTRICT ON UPDATE RESTRICT;
ALTER TABLE plugin_person ADD CONSTRAINT FK_Reference_14 FOREIGN KEY (wechatVersion)
REFERENCES plugin_WechatVersion (wechatVersion) ON DELETE RESTRICT ON UPDATE RESTRICT;
ALTER TABLE plugin_person ADD CONSTRAINT FK_Reference_8 FOREIGN KEY (companyName)
REFERENCES plugin_Company (companyName) ON DELETE RESTRICT ON UPDATE RESTRICT;
ALTER TABLE plugin_pluginVersion ADD CONSTRAINT FK_Reference_10 FOREIGN KEY (pluginName)
REFERENCES PLUGIN (pluginName) ON DELETE RESTRICT ON UPDATE RESTRICT;
ALTER TABLE plugin_pluginVersion ADD CONSTRAINT FK_Reference_11 FOREIGN KEY (wechatVersion)
REFERENCES plugin_WechatVersion (wechatVersion) ON DELETE RESTRICT ON UPDATE RESTRICT;
ALTER TABLE plugin_userLog ADD CONSTRAINT FK_Reference_12 FOREIGN KEY (alias)
REFERENCES plugin_person (alias) ON DELETE RESTRICT ON UPDATE RESTRICT;
ALTER TABLE plugin_userLog ADD CONSTRAINT FK_Reference_13 FOREIGN KEY (pluginName, wechatVersion)
REFERENCES plugin_pluginVersion (pluginName, wechatVersion) ON DELETE RESTRICT ON UPDATE RESTRICT;
ALTER TABLE plugin_userPlugins ADD CONSTRAINT FK_Reference_15 FOREIGN KEY (alias)
REFERENCES plugin_person (alias) ON DELETE RESTRICT ON UPDATE RESTRICT;
ALTER TABLE plugin_userPlugins ADD CONSTRAINT FK_Reference_16 FOREIGN KEY (pluginName)
REFERENCES PLUGIN (pluginName) ON DELETE RESTRICT ON UPDATE RESTRICT;