在一个程序的设计之初,会把数据库的结构进行细分,把关系表的信息分解成多个表,然后在根据关联表查询多个表中的数据组成一个信息表,这样做的好处不但可以解决伸缩性而且针对mysql的表结构的设计可以减少后期很多的工作量。下面看一下创建的表
用户表
SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for sys_admin -- ---------------------------- DROP TABLE IF EXISTS `sys_admin`; CREATE TABLE `sys_admin` ( `user_id` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '登录账号', `password` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '登录密码', `datetime` datetime(0) NULL DEFAULT NULL COMMENT '添加时间\r\n', `username` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '管理员姓名', `salt` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '盐', PRIMARY KEY (`user_id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact; -- ---------------------------- -- Records of sys_admin -- ---------------------------- INSERT INTO `sys_admin` VALUES ('wolf27w', 'be5b9922e3392dc42153f9c9870b7e78', '2022-11-05 12:12:59', '三禾', 'Idq29*0uAY'); INSERT INTO `sys_admin` VALUES ('263561', '8df16f91f1139510d0266791ce78acde', '2022-11-31 23:26:52', '胡歌', 'Qa#8uA!KwC'); INSERT INTO `sys_admin` VALUES ('wulaoer', 'c7e9768beca397fb4db195b2204b01ec', '2022-11-08 20:33:44', '吴老二', 'KmXcY)jNOO'); SET FOREIGN_KEY_CHECKS = 1;
用户角色表
SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for t_user_role -- ---------------------------- DROP TABLE IF EXISTS `t_user_role`; CREATE TABLE `t_user_role` ( `id` int(6) NOT NULL, `userid` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `roleid` int(6) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact; -- ---------------------------- -- Records of t_user_role -- ---------------------------- INSERT INTO `t_user_role` VALUES (1, '263561', 1); INSERT INTO `t_user_role` VALUES (2, 'wolf27w', 2); INSERT INTO `t_user_role` VALUES (3, 'wulaoer', 1); SET FOREIGN_KEY_CHECKS = 1;
角色表
SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for t_role -- ---------------------------- DROP TABLE IF EXISTS `t_role`; CREATE TABLE `t_role` ( `id` int(6) NOT NULL AUTO_INCREMENT, `name` varchar(60) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact; -- ---------------------------- -- Records of t_role -- ---------------------------- INSERT INTO `t_role` VALUES (1, 'admin'); INSERT INTO `t_role` VALUES (2, 'user'); INSERT INTO `t_role` VALUES (3, 'product'); SET FOREIGN_KEY_CHECKS = 1;
权限表
SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for t_perms -- ---------------------------- DROP TABLE IF EXISTS `t_perms`; CREATE TABLE `t_perms` ( `id` int(6) NOT NULL AUTO_INCREMENT, `name` varchar(80) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `url` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact; -- ---------------------------- -- Records of t_perms -- ---------------------------- INSERT INTO `t_perms` VALUES (1, 'user:*:*', NULL); INSERT INTO `t_perms` VALUES (2, 'product:*:01', NULL); INSERT INTO `t_perms` VALUES (3, 'order:*:*', NULL); SET FOREIGN_KEY_CHECKS = 1;
角色权限表
SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for t_role_perms -- ---------------------------- DROP TABLE IF EXISTS `t_role_perms`; CREATE TABLE `t_role_perms` ( `id` int(6) NOT NULL, `roleid` int(6) NULL DEFAULT NULL, `permsid` int(6) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact; -- ---------------------------- -- Records of t_role_perms -- ---------------------------- INSERT INTO `t_role_perms` VALUES (1, 1, 1); INSERT INTO `t_role_perms` VALUES (2, 1, 2); INSERT INTO `t_role_perms` VALUES (3, 2, 1); INSERT INTO `t_role_perms` VALUES (4, 3, 2); INSERT INTO `t_role_perms` VALUES (5, 1, 3); SET FOREIGN_KEY_CHECKS = 1;
mysql连接
SELECT table1.COLUMN, table2.COLUMN FROM table1, table2 WHERE table1.column1 = table2.column2;
关联查询用户表和用户角色表
mysql> SELECT s.user_id,s.username,t.roleid FROM `sys_admin` s,t_user_role t WHERE s.user_id = t.userid; +---------+----------+--------+ | user_id | username | roleid | +---------+----------+--------+ | wolf27w | 三禾 | 2 | | 263561 | 胡歌 | 1 | | wulaoer | 吴老二 | 1 | +---------+----------+--------+ 3 rows in set (0.01 sec)
关联查询用户表、用户角色表和角色表
mysql> SELECT -> s.user_id, -> s.username, -> tur.roleid, -> tr.name -> FROM -> `sys_admin` s -> JOIN t_user_role tur ON s.user_id = tur.userid -> JOIN t_role tr ON tur.roleid = tr.id; +---------+----------+--------+-------+ | user_id | username | roleid | name | +---------+----------+--------+-------+ | 263561 | 胡歌 | 1 | admin | | wulaoer | 吴老二 | 1 | admin | | wolf27w | 三禾 | 2 | user | +---------+----------+--------+-------+ 3 rows in set (0.00 sec)
多表连接
连接 n个表,至少需要 n-1个连接条件。 例如:连接 三个表,至少需要两个连接条件。
两表关联,把左表的列和右表的列通过笛卡尔积的形式表达出来。
左外连接:left join
mysql> select * from sys_admin left join t_user_role on sys_admin.user_id = t_user_role.userid; +---------+----------------------------------+---------------------+----------+------------+------+--------+--------+ | user_id | password | datetime | username | salt | id | userid | roleid | +---------+----------------------------------+---------------------+----------+------------+------+--------+--------+ | 263561 | 8df16f91f1139510d0266791ce78acde | 2022-11-31 23:26:52 | 胡歌 | Qa#8uA!KwC | 1 | 263561 | 1 | | wolf27w | be5b9922e3392dc42153f9c9870b7e78 | 2022-11-05 12:12:59 | 三禾 | Idq29*0uAY | 2 | wolf27w | 2 | | wulaoer | c7e9768beca397fb4db195b2204b01ec | 2022-11-08 20:33:44 | 吴老二 | KmXcY)jNOO | 3 | wulaoer | 1 | +---------+----------------------------------+---------------------+----------+------------+------+--------+--------+ 3 rows in set (0.00 sec)
右外连接: right join
mysql> mysql> select * from sys_admin right join t_user_role on sys_admin.user_id = t_user_role.userid; +---------+----------------------------------+---------------------+----------+------------+----+--------+--------+ | user_id | password | datetime | username | salt | id | userid | roleid | +---------+----------------------------------+---------------------+----------+------------+----+--------+--------+ | wolf27w | be5b9922e3392dc42153f9c9870b7e78 | 2022-11-05 12:12:59 | 三禾 | Idq29*0uAY | 2 | wolf27w | 2 | | 263561 | 8df16f91f1139510d0266791ce78acde | 2022-11-31 23:26:52 | 胡歌 | Qa#8uA!KwC | 1 | 263561 | 1 | | wulaoer | c7e9768beca397fb4db195b2204b01ec | 2022-11-08 20:33:44 | 吴老二 | KmXcY)jNOO | 3 | wulaoer | 1 | +---------+----------------------------------+---------------------+----------+------------+----+--------+--------+ 3 rows in set (0.00 sec)
内连接:inner join
mysql> select * from sys_admin inner join t_user_role on sys_admin.user_id = t_user_role.userid; +---------+----------------------------------+---------------------+----------+------------+----+--------+--------+ | user_id | password | datetime | username | salt | id | userid | roleid | +---------+----------------------------------+---------------------+----------+------------+----+--------+--------+ | wolf27w | be5b9922e3392dc42153f9c9870b7e78 | 2022-11-05 12:12:59 | 三禾 | Idq29*0uAY | 2 | wolf27w | 2 | | 263561 | 8df16f91f1139510d0266791ce78acde | 2022-11-31 23:26:52 | 胡歌 | Qa#8uA!KwC | 1 | 263561 | 1 | | wulaoer | c7e9768beca397fb4db195b2204b01ec | 2022-11-08 20:33:44 | 吴老二 | KmXcY)jNOO | 3 | wulaoer | 1 | +---------+----------------------------------+---------------------+----------+------------+----+--------+--------+ 3 rows in set (0.00 sec)
mysql的关联表就介绍到这吧,后期如有新的内容会继续更新,没有了看些其他的吧。
您可以选择一种方式赞助本站
支付宝扫一扫赞助
微信钱包扫描赞助
赏