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 ('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 教程
