使用两个表的Id创建一个表

-- 创建 Users 表
CREATE TABLE Users (
    UserId INT PRIMARY KEY,
    UserName VARCHAR(255) NOT NULL
);

-- 创建 Roles 表
CREATE TABLE Roles (
    RoleId INT PRIMARY KEY,
    RoleName VARCHAR(255) NOT NULL
);

-- 创建 UserRoles 关联表
CREATE TABLE UserRoles (
    UserId INT,
    RoleId INT,
    PRIMARY KEY (UserId, RoleId),
    FOREIGN KEY (UserId) REFERENCES Users(UserId),
    FOREIGN KEY (RoleId) REFERENCES Roles(RoleId)
);

-- 插入用户数据
INSERT INTO Users (UserId, UserName) VALUES (1, 'Alice');
INSERT INTO Users (UserId, UserName) VALUES (2, 'Bob');

-- 插入角色数据
INSERT INTO Roles (RoleId, RoleName) VALUES (1, 'Admin');
INSERT INTO Roles (RoleId, RoleName) VALUES (2, 'User');

-- 插入关联数据
INSERT INTO UserRoles (UserId, RoleId) VALUES (1, 1); -- Alice 是 Admin
INSERT INTO UserRoles (UserId, RoleId) VALUES (1, 2); -- Alice 也是 User
INSERT INTO UserRoles (UserId, RoleId) VALUES (2, 2); -- Bob 是 User

-- 查询所有用户及其角色
SELECT u.UserName, r.RoleName
FROM Users u
JOIN UserRoles ur ON u.UserId = ur.UserId
JOIN Roles r ON ur.RoleId = r.RoleId;