MySQL多表关联查询

avatar 2022年12月11日18:17:45 评论 1,190 次浏览

在一个程序的设计之初，会把数据库的结构进行细分，把关系表的信息分解成多个表，然后在根据关联表查询多个表中的数据组成一个信息表，这样做的好处不但可以解决伸缩性而且针对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的关联表就介绍到这吧，后期如有新的内容会继续更新，没有了看些其他的吧。

 

avatar

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: