用户-角色多对多查询sql
多对多是常见的对应关系:在开发中 用户和角色就属于对多对多关系:对应查询sql为:
第一步导入数据:
导入user表:
/*
Navicat Premium Data Transfer
Source Server : 本地数据库
Source Server Type : MySQL
Source Server Version : 50726
Source Host : localhost:3306
Source Schema : laboratory
Target Server Type : MySQL
Target Server Version : 50726
File Encoding : 65001
Date: 12/02/2020 21:06:46
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` varchar(11) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`user_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL,
`password` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = MyISAM CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1', 'admin', '123456');
INSERT INTO `user` VALUES ('2', 'alex', '123456');
SET FOREIGN_KEY_CHECKS = 1;
导入角色表:
/*
Navicat Premium Data Transfer
Source Server : 本地数据库
Source Server Type : MySQL
Source Server Version : 50726
Source Host : localhost:3306
Source Schema : laboratory
Target Server Type : MySQL
Target Server Version : 50726
File Encoding : 65001
Date: 12/02/2020 21:07:11
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for role
-- ----------------------------
DROP TABLE IF EXISTS `role`;
CREATE TABLE `role` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`role_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of role
-- ----------------------------
INSERT INTO `role` VALUES (1, 'admin');
INSERT INTO `role` VALUES (2, 'user');
SET FOREIGN_KEY_CHECKS = 1;
导入用户-角色表:
/*
Navicat Premium Data Transfer
Source Server : 本地数据库
Source Server Type : MySQL
Source Server Version : 50726
Source Host : localhost:3306
Source Schema : laboratory
Target Server Type : MySQL
Target Server Version : 50726
File Encoding : 65001
Date: 12/02/2020 21:07:01
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for user_role
-- ----------------------------
DROP TABLE IF EXISTS `user_role`;
CREATE TABLE `user_role` (
`user_id` int(11) NOT NULL,
`role_id` int(11) NULL DEFAULT NULL
) ENGINE = MyISAM CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = Fixed;
-- ----------------------------
-- Records of user_role
-- ----------------------------
INSERT INTO `user_role` VALUES (1, 1);
INSERT INTO `user_role` VALUES (2, 2);
INSERT INTO `user_role` VALUES (1, 2);
SET FOREIGN_KEY_CHECKS = 1;
根据用户id查询用户role
SELECT u.id 用户编号,u.user_name 用户名,
r.id 角色编号 ,r.role_name
FROM role r
INNER JOIN user_role ur ON r.id=ur.role_id
INNER JOIN user u ON u.id=ur.user_id
WHERE u.id=1;