Mybatis学习之数据库

运行sql语句创建数据库
/*
Navicat MySQL Data Transfer

Source Server : admin
Source Server Type : MySQL
Source Server Version : 80023
Source Host : localhost:3306
Source Schema : mybatis

Target Server Type : MySQL
Target Server Version : 80023
File Encoding : 65001

Date: 06/08/2021 23:49:47
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

---

– Table structure for account

---

DROP TABLE IF EXISTS account;
CREATE TABLE account (
ID int NOT NULL COMMENT ‘编号’,
UID int NULL DEFAULT NULL COMMENT ‘用户编号’,
MONEY double NULL DEFAULT NULL COMMENT ‘金额’,
PRIMARY KEY (ID) USING BTREE,
INDEX FK_Reference_8(UID) USING BTREE,
CONSTRAINT FK_Reference_8 FOREIGN KEY (UID) REFERENCES user (id) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

---

– Records of account

---

INSERT INTO account VALUES (1, 41, 1000);
INSERT INTO account VALUES (2, 45, 1000);
INSERT INTO account VALUES (3, 50, 2000);
INSERT INTO account VALUES (4, 50, 5000);

---

– Table structure for role

---

DROP TABLE IF EXISTS role;
CREATE TABLE role (
ROLE_ID int NOT NULL COMMENT ‘编号’,
ROLE_NAME varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT ‘角色名称’,
ROLE_DESC varchar(60) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT ‘角色描述’,
PRIMARY KEY (ROLE_ID) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

---

– Records of role

---

INSERT INTO role VALUES (1, ‘院长’, ‘管理整个学院’);
INSERT INTO role VALUES (2, ‘总裁’, ‘管理整个公司’);
INSERT INTO role VALUES (3, ‘校长’, ‘管理整个学校’);

---

– Table structure for user

---

DROP TABLE IF EXISTS user;
CREATE TABLE user (
id int NOT NULL AUTO_INCREMENT,
username varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT ‘用户名称’,
birthday datetime NULL DEFAULT NULL COMMENT ‘生日’,
sex char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT ‘性别’,
address varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT ‘地址’,
PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 54 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

---

– Records of user

---

INSERT INTO user VALUES (41, ‘老王’, ‘2021-02-27 17:47:08’, ‘男’, ‘北京’);
INSERT INTO user VALUES (42, ‘小5王’, ‘2021-03-02 15:09:37’, ‘女’, ‘陕西’);
INSERT INTO user VALUES (43, ‘小2王’, ‘2021-03-04 11:34:34’, ‘女’, ‘南昌’);
INSERT INTO user VALUES (45, ‘播客’, ‘2021-03-04 12:04:06’, ‘男’, ‘南京’);
INSERT INTO user VALUES (46, ‘8王’, ‘2021-03-07 17:37:26’, ‘男’, ‘北京’);
INSERT INTO user VALUES (50, ‘mybatis update user’, ‘2021-07-30 00:08:11’, ‘女’, ‘成都’);
INSERT INTO user VALUES (54, ‘牛牛’, ‘2021-07-30 00:07:23’, ‘男’, ‘成都’);

---

– Table structure for user_role

---

DROP TABLE IF EXISTS user_role;
CREATE TABLE user_role (
UID int NOT NULL COMMENT ‘用户编号’,
RID int NOT NULL COMMENT ‘角色编号’,
PRIMARY KEY (UID, RID) USING BTREE,
INDEX FK_Reference_10(RID) USING BTREE,
CONSTRAINT FK_Reference_10 FOREIGN KEY (RID) REFERENCES role (ROLE_ID) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT FK_Reference_9 FOREIGN KEY (UID) REFERENCES user (id) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

---

– Records of user_role

---

INSERT INTO user_role VALUES (41, 1);
INSERT INTO user_role VALUES (45, 1);
INSERT INTO user_role VALUES (41, 2);

SET FOREIGN_KEY_CHECKS = 1;

一键转到→Mybatis学习日志一