sql server 多对多in

原创

mob649e815b5994 2024-10-20 06:40:12 ©著作权

©著作权归作者所有：来自51CTO博客作者mob649e815b5994的原创作品，请联系作者获取转载授权，否则将追究法律责任

SQL Server多对多关系实现及查询

如果你刚入行，对数据库的多对多关系不太了解，不用担心，今天我将带你了解如何在SQL Server中实现多对多关系并进行查询，特别是使用IN操作符进行数据过滤。

一、流程概述

实现多对多关系的步骤通常分为以下几个部分：

步骤	描述
1. 创建表	创建表示多个实体的表（如用户表和课程表）。
2. 创建连接表	创建连接表，该表用于连接两个实体表，以实现多对多关系。
3. 插入数据	向实体表和连接表中插入数据。
4. 查询数据	使用`IN`操作符查询多对多关系中的数据。

二、每一步的实现

1. 创建表

首先，我们需要创建两个实体表：Users和Courses，并为它们定义一些基本字段。

CREATE TABLE Users (
    UserID INT PRIMARY KEY,
    UserName NVARCHAR(100)
);

CREATE TABLE Courses (
    CourseID INT PRIMARY KEY,
    CourseName NVARCHAR(100)
);

Users表：存储用户的信息，包含用户ID和用户名。
Courses表：存储课程的信息，包含课程ID和课程名称。

2. 创建连接表

然后，创建一个连接表UserCourses，用于连接Users和Courses表，表示哪些用户选修了哪些课程。

CREATE TABLE UserCourses (
    UserID INT,
    CourseID INT,
    PRIMARY KEY (UserID, CourseID),
    FOREIGN KEY (UserID) REFERENCES Users(UserID),
    FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);

UserCourses表：每一行表示一个用户和他们选修的课程，通过UserID和CourseID进行连接。

3. 插入数据

接下来，我们需要向这几个表中插入一些数据。

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

-- 插入课程数据
INSERT INTO Courses (CourseID, CourseName) VALUES (101, 'Math');
INSERT INTO Courses (CourseID, CourseName) VALUES (102, 'Science');

-- 插入用户与课程的关系
INSERT INTO UserCourses (UserID, CourseID) VALUES (1, 101);
INSERT INTO UserCourses (UserID, CourseID) VALUES (1, 102);
INSERT INTO UserCourses (UserID, CourseID) VALUES (2, 101);

插入用户：向Users表插入了两个用户Alice和Bob。
插入课程：向Courses表插入了两个课程Math和Science。
插入关系：用户Alice选修了Math和Science课程，Bob选修了Math课程。

4. 查询数据

现在我们可以通过IN操作符查询哪个用户选修了哪些课程。例如，查询选修Math的所有用户：

SELECT UserName 
FROM Users 
WHERE UserID IN (SELECT UserID FROM UserCourses WHERE CourseID = 101);

SELECT UserName ...：从Users表中选择用户的名称。
WHERE UserID IN (...)：通过子查询筛选选修"Math"课程的用户。

三、状态图

下面是一个状态图，说明多对多关系的状态转换。

stateDiagram
    [*] --> UsersCreated
    UsersCreated --> CoursesCreated
    CoursesCreated --> UserCoursesCreated
    UserCoursesCreated --> DataInserted
    DataInserted --> DataQueried

四、旅行图

在表格中的查询过程就如同旅行一样，我们也可以使用旅行图来表示查询流向。

journey
    title SQL Server多对多关系查询过程
    section 创建表
      创建Users表: 5: Users表成功创建
      创建Courses表: 5: Courses表成功创建
    section 创建连接表
      创建UserCourses表: 5: UserCourses表成功创建
    section 插入数据
      插入用户数据: 5: 用户数据插入成功
      插入课程数据: 5: 课程数据插入成功
      插入用户与课程关系: 5: 关系数据插入成功
    section 查询数据
      查询选修Math的用户: 5: 查查返回Alice和Bob