多表查询

创建几张表,表之间有关系。

用户表

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 ('123456', 'be5b9922e3392dc42153f9c9870b7e78', '2022-06-05 12:12:59', '三禾', 'Idq29*0uAY');
INSERT INTO `sys_admin` VALUES ('263561', '8df16f91f1139510d0266791ce78acde', '2022-05-31 23:26:52', '胡歌', 'Qa#8uA!KwC');
INSERT INTO `sys_admin` VALUES ('茶凡', 'c7e9768beca397fb4db195b2204b01ec', '2022-06-08 20:33:44', 'Matrix', '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, '123456', 2);
INSERT INTO `t_user_role` VALUES (3, '茶凡', 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 子句中写入连接条件。

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

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

SELECT s.user_id,s.username,t.roleid FROM `sys_admin` s,t_user_role t WHERE s.user_id = t.userid;

--------------------------------------------------
结果：
123456		三禾	    2
15681026356	胡歌	     1
茶凡		  Matrix	1
--------------------------------------------------

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

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;
	
--------------------------------------------------
结果：
15681026356	胡歌	    1	admin
茶凡	      Matrix	1	admin
123456	    三禾	    2	user
--------------------------------------------------

多表连接

连接 n个表,至少需要 n-1个连接条件。 例如：连接 三个表，至少需要两个连接条件。

笛卡尔集

两表关联，把左表的列和右表的列通过笛卡尔积的形式表达出来。

外连接

左外连接：left join

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

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

右外连接： right join

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

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

内连接：inner join

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

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

其他连接

博客借鉴(3条消息) MySQL中表连接方式_渣一个的博客-CSDN博客_mysql表连接方式

博客借鉴：尚硅谷mysql 教程