MySQL多表关联查询

吴

吴老二2022-12-11数据库1601 次浏览0 条评论

在一个程序的设计之初，会把数据库的结构进行细分，把关系表的信息分解成多个表，然后在根据关联表查询多个表中的数据组成一个信息表，这样做的好处不但可以解决伸缩性而且针对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;

在 WHERE 子句中写入连接条件。

在表中有相同列时，在列名之前加上表名前缀

关联查询用户表和用户角色表

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

两表关联，左表全部保留，右表关联不上用null表示。

select * from t1 left join t2 on t1.id = t2.id

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

两表关联，右表全部保留，左表关联不上用null表示。

select * from t1 right join t2 on t1.id = t2.id

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

两表关联，保留两表中交集的记录。

select * from t1 inner join t2 on t1.id = t2.id;

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的关联表就介绍到这吧，后期如有新的内容会继续更新，没有了看些其他的吧。

评论需登录后发表（注册并验证邮箱）。

还没有评论，来抢沙发～

MySQL多表关联查询

用户表

用户角色表

角色表

权限表

角色权限表

mysql连接

关联查询用户表和用户角色表

关联查询用户表、用户角色表和角色表

多表连接

笛卡尔集

外连接

左外连接：left join

右外连接： right join

内连接：inner join

其他连接

相关文章

PostgreSQL 18.6 安装记录

ProxySQL 基本排查方法指南

ProxySQL 安装与基本配置指南 —— 实现 MySQL 读写分离

评论 0