Mybatis—创建Mybatis案例表

创建User表

CREATE TABLE `user` (
  `id` int(11) NOT NULL auto_increment,
  `username` varchar(32) NOT NULL COMMENT 用户名称,
  `birthday` datetime default NULL COMMENT 生日,
  `sex` char(1) default NULL COMMENT 性别,
  `address` varchar(256) default NULL COMMENT 地址,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert  into `user`(`id`,`username`,`birthday`,`sex`,`address`) values (41,老王,2018-02-27 17:47:08,男,北京),(42,小二王,2018-03-02 15:09:37,女,北京金燕龙),(43,小二王,2018-03-04 11:34:34,女,北京金燕龙),(45,传智播客,2018-03-04 12:04:06,男,北京金燕龙),(46,老王,2018-03-07 17:37:26,男,北京),(48,小马宝莉,2018-03-08 11:44:00,女,北京修正);

创建Account表

DROP TABLE IF EXISTS `account`;

CREATE TABLE `account` (
  `ID` int(11) NOT NULL COMMENT 编号,
  `UID` int(11) default NULL COMMENT 用户编号,
  `MONEY` double default NULL COMMENT 金额,
  PRIMARY KEY  (`ID`),
  KEY `FK_Reference_8` (`UID`),
  CONSTRAINT `FK_Reference_8` FOREIGN KEY (`UID`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


insert  into `account`(`ID`,`UID`,`MONEY`) values (1,41,1000),(2,45,1000),(3,41,2000);

创建Role表

DROP TABLE IF EXISTS `role`;

CREATE TABLE `role` (
  `ID` int(11) NOT NULL COMMENT 编号,
  `ROLE_NAME` varchar(30) default NULL COMMENT 角色名称,
  `ROLE_DESC` varchar(60) default NULL COMMENT 角色描述,
  PRIMARY KEY  (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert  into `role`(`ID`,`ROLE_NAME`,`ROLE_DESC`) values (1,院长,管理整个学院),(2,总裁,管理整个公司),(3,校长,管理整个学校);

创建user_role表

DROP TABLE IF EXISTS `user_role`;

CREATE TABLE `user_role` (
  `UID` int(11) NOT NULL COMMENT 用户编号,
  `RID` int(11) NOT NULL COMMENT 角色编号,
  PRIMARY KEY  (`UID`,`RID`),
  KEY `FK_Reference_10` (`RID`),
  CONSTRAINT `FK_Reference_10` FOREIGN KEY (`RID`) REFERENCES `role` (`ID`),
  CONSTRAINT `FK_Reference_9` FOREIGN KEY (`UID`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert  into `user_role`(`UID`,`RID`) values (41,1),(45,1),(41,2);