时间戳

首页 > 解决方案 > SQL 中的多对多关系。不能得到多个结果

sql - SQL 中的多对多关系。不能得到多个结果

问题描述

我正在设计一个简单的电影数据库。关键是要有 2 M:N 的关系。到目前为止，我已经将它设计为“中央基础”数据库，它看起来像这样。

GENRES(ID,GENRE_TYPE)- Many to many
ACTORS (ID, FULL_NAME, DOB) - many to many 
DESCRIPTIONS(ID,DESCRIPTION)
DIRECTORS(ID, FULL_NAME, DOB)
PROFITS(ID, AMOUNT)
BUDGETS(ID, AMOUNT)
MOVIES(ID, TITLE, LENGTH, YEAR, GENRE, CAST, BUDGET, PROFIT, DIRECTED_BY)** 
*those two are created to redefine M:N*
**MOVIE_GENRES(ID, MOVIE_ID, GENRE_ID)
MOVIE_CAST(ID, MOVIE_ID, ACTOR_ID)**

我的问题是，当我这样做时，我每部电影只有 1 个演员，或者每部电影只有一个流派。关键是每部电影有 2 个演员，每部电影有 2 或 1 个流派。

由于我是 sql 领域的新手，请帮助我。我正在使用 Oracle Live SQL。

qlq

标签： sqloracledatabase-designmany-to-many

解决方案

为流派创建一个表：

CREATE TABLE Genres (
  ID   NUMBER(8,0)
       GENERATED ALWAYS AS IDENTITY
       CONSTRAINT Genres__ID__PK PRIMARY KEY,
  NAME VARCHAR2(30)
       CONSTRAINT Genres__Name__NN NOT NULL
       CONSTRAINT Genres__Name__U  UNIQUE
);

您可以将示例数据插入其中：

INSERT INTO Genres ( Name )
SELECT 'Comedy'       FROM DUAL UNION ALL
SELECT 'Horror'       FROM DUAL UNION ALL
SELECT 'Period Drama' FROM DUAL UNION ALL
SELECT 'Documentary'  FROM DUAL

以及包含特定于该人的数据的人员表：

CREATE TABLE People (
  ID            NUMBER(8,0)
                GENERATED ALWAYS AS IDENTITY
                CONSTRAINT People__ID__PK PRIMARY KEY,
  FIRST_NAME    VARCHAR2(200)
                CONSTRAINT People__fn__nn NOT NULL,
  LAST_NAME     VARCHAR2(200)
                CONSTRAINT People__ln__nn NOT NULL,
  DATE_OF_BIRTH DATE
                CONSTRAINT People__dob__nn NOT NULL
);

这些人可以在电影中扮演不同的角色，所以创建一个表格来描述一个人可以扮演的角色：

CREATE TABLE Roles (
  ID   NUMBER(8,0)
       GENERATED ALWAYS AS IDENTITY
       CONSTRAINT Roles__ID__PK PRIMARY KEY,
  ROLE VARCHAR2(30)
       CONSTRAINT Roels__Role__NN NOT NULL
       CONSTRAINT Roles__Role__U  UNIQUE
);

例如：

INSERT INTO Roles ( Role )
SELECT 'Actor'    FROM DUAL UNION ALL
SELECT 'Director' FROM DUAL UNION ALL
SELECT 'Producer' FROM DUAL;

这样，您无需为电影中的每个角色创建一个新表。

然后，您可以创建一个表，其中仅包含特定于电影的数据（与任何其他表无关）：

CREATE TABLE Movies (
  ID            NUMBER(8,0)
                GENERATED ALWAYS AS IDENTITY
                CONSTRAINT Movies__ID__PK PRIMARY KEY,
  TITLE         VARCHAR2(300)
                CONSTRAINT Movies__title__nn NOT NULL,
  LENGTH        INTERVAL DAY TO SECOND,
  RELEASE_DATE  DATE,
  BUDGET        NUMBER(12,2),
  PROFIT        NUMBER(12,2)
);

然后，您可以创建描述表之间关系的表：

CREATE TABLE Movie_People (
  ID            NUMBER(8,0)
                GENERATED ALWAYS AS IDENTITY
                CONSTRAINT Movie_People__ID__PK PRIMARY KEY,
  Movie_ID      NUMBER(8,0)
                CONSTRAINT Movie_People__Movie_ID__NN NOT NULL
                CONSTRAINT Movie_People__Movie_ID__FK REFERENCES Movies( ID ),
  Role_ID       NUMBER(8,0)
                CONSTRAINT Movie_People__Role_ID__NN NOT NULL
                CONSTRAINT Movie_People__Role_ID__FK REFERENCES Roles( ID ),
  Person_ID     NUMBER(8,0)
                CONSTRAINT Movie_People__Person_ID__NN NOT NULL
                CONSTRAINT Movie_People__Person_ID__FK REFERENCES People( ID ),
  CONSTRAINT Movie_People__M_R_P__U UNIQUE( Movie_ID, Role_ID, Person_ID )
);

和

CREATE TABLE Movie_Genres (
  ID            NUMBER(8,0)
                GENERATED ALWAYS AS IDENTITY
                CONSTRAINT Movie_Genres__ID__PK PRIMARY KEY,
  Movie_ID      NUMBER(8,0)
                CONSTRAINT Movie_Genres__Movie_ID__NN NOT NULL
                CONSTRAINT Movie_Genres__Movie_ID__FK REFERENCES Movies( ID ),
  Genre_ID      NUMBER(8,0)
                CONSTRAINT Movie_Genres__Genre_ID__NN NOT NULL
                CONSTRAINT Movie_Genres__Genre_ID__FK REFERENCES Genres( ID ),
  CONSTRAINT Movie_Genres__M_G__U UNIQUE( Movie_ID, Genre_ID )
);

然后，如果您想将电影的演员添加到数据库中：

INSERT INTO Movie_People (
  Movie_ID,
  Role_ID,
  Person_ID
) VALUES (
  ( SELECT ID FROM Movies WHERE title = 'The Muppet Christmas Carol' ),
  ( SELECT ID FROM Roles  WHERE role  = 'Actor' ),
  ( SELECT ID FROM People WHERE first_name = 'Michael' AND last_name = 'Caine' )
)

如果您想查找具有多种类型的电影，例如：

SELECT Movie_ID
FROM   Movie_Genres
WHERE  Genre_ID IN (
  SELECT ID
  FROM   Genres
  WHERE  Name IN ( 'Comedy', 'Horror' )
)
GROUP BY Movie_ID
HAVING COUNT( Genre_ID ) = 2;

推荐阅读