总览

database

详细设计

  • 用户表
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
#
# Structure for table "user"
#

DROP TABLE IF EXISTS user;
CREATE TABLE user (
	user_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '用户ID',
	user_name VARCHAR(32) NOT NULL UNIQUE COMMENT '用户名',
	user_pwd VARCHAR(64) NOT NULL COMMENT '用户密码',
	create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
	update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='用户表';

#
# Data for table "user"
# 

INSERT INTO user (user_name,user_pwd) VALUES ('Admin', '6yhn7ujm8ik,');
INSERT INTO user (user_name,user_pwd) VALUES ('Ray', '123456');
INSERT INTO user (user_name,user_pwd) VALUES ('Kagarise', 'abcdef');
INSERT INTO user (user_name,user_pwd) VALUES ('Euphonium', '6yhn7ujm8ik,');
  • 角色表
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
#
# Structure for table "role"
#

DROP TABLE IF EXISTS role;
CREATE TABLE role (
	role_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '角色ID',
	role_name VARCHAR(32) NOT NULL UNIQUE COMMENT '角色名',
	create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
	update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='角色表';

#
# Data for table "role"
#

INSERT INTO role (role_name) VALUES ('Admin');
INSERT INTO role (role_name) VALUES ('User');
  • 用户角色表
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
#
# Structure for table "user_role"
#

DROP TABLE IF EXISTS user_role;
CREATE TABLE user_role (
	user_role_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '用户角色ID',
	user_id INT NOT NULL COMMENT '用户ID',
	role_id INT NOT NULL COMMENT '角色ID',
	create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
	update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='用户角色表';

#
# Data for table "user_role"
#

INSERT INTO user_role (user_id, role_id) VALUES (1, 1);
INSERT INTO user_role (user_id, role_id) VALUES (2, 2);
INSERT INTO user_role (user_id, role_id) VALUES (3, 2);
INSERT INTO user_role (user_id, role_id) VALUES (4, 2);
  • 权限表
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
#
# Structure for table "permission"
#

DROP TABLE IF EXISTS permission;
CREATE TABLE permission (
	permission_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '权限ID',
	permission_name VARCHAR(32) NOT NULL UNIQUE COMMENT '权限名',
	create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
	update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='权限表';

#
# Data for table "permission"
#

INSERT INTO permission (permission_name) VALUES ('user:curd');
INSERT INTO permission (permission_name) VALUES ('role:curd');
INSERT INTO permission (permission_name) VALUES ('permission:curd');
INSERT INTO permission (permission_name) VALUES ('member:curd');
INSERT INTO permission (permission_name) VALUES ('store:curd');
INSERT INTO permission (permission_name) VALUES ('sale:curd');
  • 角色权限表
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
#
# Structure for table "role_permission"
#

DROP TABLE IF EXISTS role_permission;
CREATE TABLE role_permission (
	role_permission_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '角色权限ID',
	role_id INT NOT NULL COMMENT '角色ID',
	permission_id INT NOT NULL COMMENT '权限ID',
	create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
	update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='角色权限表';

#
# Data for table "role_permission"
#

INSERT INTO role_permission (role_id, permission_id) VALUES (1, 1);
INSERT INTO role_permission (role_id, permission_id) VALUES (1, 2);
INSERT INTO role_permission (role_id, permission_id) VALUES (1, 3);
INSERT INTO role_permission (role_id, permission_id) VALUES (1, 4);
INSERT INTO role_permission (role_id, permission_id) VALUES (1, 5);
INSERT INTO role_permission (role_id, permission_id) VALUES (1, 6);
INSERT INTO role_permission (role_id, permission_id) VALUES (2, 1);
INSERT INTO role_permission (role_id, permission_id) VALUES (2, 2);
INSERT INTO role_permission (role_id, permission_id) VALUES (2, 3);
  • 会员表
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
#
# Structure for table "member"
#

DROP TABLE IF EXISTS member;
CREATE TABLE member (
	member_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '会员ID',
	member_name VARCHAR(32) NOT NULL UNIQUE COMMENT '会员名',
	member_point INT NOT NULL DEFAULT 0 COMMENT '会员积分',
	create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
	update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='会员表';

#
# Data for table "member"
#

INSERT INTO member (member_name) VALUES ('member01');
INSERT INTO member (member_name) VALUES ('member02');
INSERT INTO member (member_name) VALUES ('member03');
  • 商品表
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
#
# Structure for table "goods"
#

DROP TABLE IF EXISTS goods;
CREATE TABLE goods (
	goods_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '商品ID',
	goods_name VARCHAR(32) NOT NULL UNIQUE COMMENT '商品名',
	goods_num INT NOT NULL COMMENT '商品数量',
	goods_price DOUBLE NOT NULL COMMENT '商品价格',
	create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
	update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='商品表';

#
# Data for table "goods"
#

INSERT INTO goods (goods_name, goods_num, goods_price) VALUES ('198', 100, 198);
INSERT INTO goods (goods_name, goods_num, goods_price) VALUES ('328', 100, 328);
INSERT INTO goods (goods_name, goods_num, goods_price) VALUES ('648', 100, 648);
  • 订单表
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
#
# Structure for table "sale"
#

DROP TABLE IF EXISTS sale;
CREATE TABLE sale (
	sale_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '订单ID',
	sale_value DOUBLE NOT NULL COMMENT '订单金额',
	create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
	update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='订单表';

#
# Data for table "sale"
#
  • 订单商品表
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
#
# Structure for table "sale_goods"
#

DROP TABLE IF EXISTS sale_goods;
CREATE TABLE sale_goods (
	sale_info_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '订单商品ID',
	sale_id INT NOT NULL COMMENT '订单ID',
	goods_id INT NOT NULL COMMENT '商品ID',
	sale_num INT NOT NULL COMMENT '销售数量',
	create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
	update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='订单商品表';

#
# Data for table "sale_goods"
#

样例查询

  • 查询用户角色
1
2
3
SELECT user_name, role_name FROM user
JOIN user_role ON user.user_id = user_role.user_id
JOIN role ON user_role.role_id = role.role_id;
  • 查询角色权限
1
2
3
SELECT role_name, permission_name FROM role
JOIN role_permission ON role.role_id = role_permission.role_id
JOIN permission ON role_permission.permission_id = permission.permission_id;
  • 查询用户ID的权限名
1
2
3
4
5
6
7
8
SELECT permission_name from permission
WHERE permission_id IN(
	SELECT permission_id FROM role_permission
	WHERE role_id IN(
		SELECT role_id FROM user_role
		WHERE user_id = 1
	)
);