qxgl.sql

包括所有开发文档以及数据库文件

/*==============================================================*/
/* DBMS name:      MySQL 5.0                                    */
/* Created on:                              */
/*==============================================================*/


drop table if exists action;

drop table if exists group_action;

drop table if exists group_role;

drop table if exists groups;

drop table if exists operatelog;

drop table if exists role;

drop table if exists role_action;

drop table if exists school;

drop table if exists school_user;

drop table if exists user;

drop table if exists user_group;

drop table if exists user_role;

/*==============================================================*/
/* Table: action                                                */
/*==============================================================*/
create table action
(
   actionId             int,
   actionName           varchar(50),
   actionDescription    varchar(50),
   primary key (actionId)
)ENGINE=InnoDB DEFAULT CHARSET=GBK;

/*==============================================================*/
/* Table: group_action                                          */
/*==============================================================*/
create table group_action
(
   GAId                 int auto_increment,
   groupId              int,
   actionId             int,
   primary key (GAId)
)ENGINE=InnoDB DEFAULT CHARSET=GBK;

/*==============================================================*/
/* Table: group_role                                            */
/*==============================================================*/
create table group_role
(
   GRId                 int auto_increment,
   groupId              int,
   roleId               int,
   primary key (GRId)
)ENGINE=InnoDB DEFAULT CHARSET=GBK;

/*==============================================================*/
/* Table: groups                                                */
/*==============================================================*/
create table groups
(
   groupId              int,
   groupName            varchar(50),
   groupDescription     varchar(50),
   primary key (groupId)
)ENGINE=InnoDB DEFAULT CHARSET=GBK;

/*==============================================================*/
/* Table: operatelog                                            */
/*==============================================================*/
create table operatelog
(
   logId                int auto_increment,
   operateContent       varchar(500),
   operator             varchar(50),
   operateTime          varchar(50),
   primary key (logId)
)ENGINE=InnoDB DEFAULT CHARSET=GBK;

/*==============================================================*/
/* Table: role                                                  */
/*==============================================================*/
create table role
(
   roleId               int,
   roleName             varchar(50),
   roleDescription      varchar(50),
   primary key (roleId)
)ENGINE=InnoDB DEFAULT CHARSET=GBK;

/*==============================================================*/
/* Table: role_action                                           */
/*==============================================================*/
create table role_action
(
   RAId                 int auto_increment,
   roleId               int,
   actionId             int,
   primary key (RAId)
)ENGINE=InnoDB DEFAULT CHARSET=GBK;

/*==============================================================*/
/* Table: school                                                */
/*==============================================================*/
create table school
(
   schoolId             int auto_increment,
   schoolName           varchar(100),
   schoolAddress        varchar(500),
   primary key (schoolId)
)ENGINE=InnoDB DEFAULT CHARSET=GBK;

/*==============================================================*/
/* Table: school_user                                           */
/*==============================================================*/
create table school_user
(
   USId                 int auto_increment,
   userId               int,
   schoolId             int,
   primary key (USId)
)ENGINE=InnoDB DEFAULT CHARSET=GBK;

/*==============================================================*/
/* Table: user                                                  */
/*==============================================================*/
create table user
(
   userId               int auto_increment,
   userName             varchar(50),
   password             varchar(50),
   parentName           varchar(50),
   area                 varchar(100),
   realName             varchar(50),
   phone                varchar(50),
   email                varchar(50),
   createTime           varchar(50),
   masterName           varchar(50),
   schoolName           varchar(50),
   primary key (userId)
)ENGINE=InnoDB DEFAULT CHARSET=GBK;

/*==============================================================*/
/* Table: user_group                                            */
/*==============================================================*/
create table user_group
(
   UGId                 int auto_increment,
   groupId              int,
   userId               int,
   primary key (UGId)
)ENGINE=InnoDB DEFAULT CHARSET=GBK;

/*==============================================================*/
/* Table: user_role                                             */
/*==============================================================*/
create table user_role
(
   URId                 int auto_increment,
   userId               int,
   roleId               int,
   primary key (URId)
)ENGINE=InnoDB DEFAULT CHARSET=GBK;

alter table group_action add constraint FK_FK_AG foreign key (actionId)
      references action (actionId) on delete cascade on update restrict;

alter table group_action add constraint FK_FK_GA foreign key (groupId)
      references groups (groupId) on delete cascade on update restrict;

alter table group_role add constraint FK_FK_GR foreign key (groupId)
      references groups (groupId) on delete cascade on update restrict;

alter table group_role add constraint FK_FK_RG foreign key (roleId)
      references role (roleId) on delete cascade on update restrict;

alter table role_action add constraint FK_FK_AR foreign key (actionId)
      references action (actionId) on delete cascade on update restrict;

alter table role_action add constraint FK_FK_RA foreign key (roleId)
      references role (roleId) on delete cascade on update cascade;

alter table school_user add constraint FK_FK_SU foreign key (schoolId)
      references school (schoolId) on delete cascade on update restrict;

alter table school_user add constraint FK_FK_US foreign key (userId)
      references user (userId) on delete cascade on update restrict;

alter table user_group add constraint FK_FK_GU foreign key (groupId)
      references groups (groupId) on delete cascade on update restrict;

alter table user_group add constraint FK_FK_UG foreign key (userId)
      references user (userId) on delete cascade on update restrict;

alter table user_role add constraint FK_FK_RU foreign key (roleId)
      references role (roleId) on delete cascade on update restrict;

alter table user_role add constraint FK_FK_UR foreign key (userId)
      references user (userId) on delete cascade on update restrict;