Sql 多对多关系

多对多关系的典型设计就是图书表和作者表，一本图书可以有多个作者，而一个作者可以写多本图书，两者的关系通过一张中间外键表维护

--创建基表CREATE TABLE [dbo].[Book] --图书表([ID] [int] NOT NULL IDENTITY(1, 1),[Name] [nvarchar] (max) COLLATE Chinese_PRC_CI_AS NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GOALTER TABLE [dbo].[Book] ADD CONSTRAINT [PK_dbo.Book] PRIMARY KEY CLUSTERED  ([ID]) ON [PRIMARY]GOCREATE TABLE [dbo].[Author] --作者表([ID] [int] NOT NULL IDENTITY(1, 1),[Name] [nvarchar] (max) COLLATE Chinese_PRC_CI_AS NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GOALTER TABLE [dbo].[Author] ADD CONSTRAINT [PK_dbo.Author] PRIMARY KEY CLUSTERED  ([ID]) ON [PRIMARY]GOCREATE TABLE [dbo].[BookAuthor] --图书作者关联表([Book_ID] [int] NOT NULL,[Author_ID] [int] NOT NULL) ON [PRIMARY]GOALTER TABLE [dbo].[BookAuthor] ADD CONSTRAINT [PK_dbo.BookAuthor] PRIMARY KEY CLUSTERED  ([Book_ID], [Author_ID]) ON [PRIMARY]GOCREATE NONCLUSTERED INDEX [IX_Book_ID] ON [dbo].[BookAuthor] ([Book_ID]) ON [PRIMARY]GOCREATE NONCLUSTERED INDEX [IX_Author_ID] ON [dbo].[BookAuthor] ([Author_ID]) ON [PRIMARY]GOALTER TABLE [dbo].[BookAuthor] ADD CONSTRAINT [FK_dbo.BookAuthor_dbo.Author_Author_ID] FOREIGN KEY ([Author_ID]) REFERENCES [dbo].[Author] ([ID]) ON DELETE CASCADEGOALTER TABLE [dbo].[BookAuthor] ADD CONSTRAINT [FK_dbo.BookAuthor_dbo.Book_Book_ID] FOREIGN KEY ([Book_ID]) REFERENCES [dbo].[Book] ([ID]) ON DELETE CASCADEGO--插入数据DECLARE @CurBookID INTINSERT INTO dbo.Book(Name) VALUES('book_a')SET @CurBookID = SCOPE_IDENTITY();DECLARE @CurAuthorID INTINSERT INTO dbo.Author(Name) VALUES('john')SET @CurAuthorID = SCOPE_IDENTITY()INSERT INTO dbo.BookAuthor(Book_ID, Author_ID) VALUES(@CurBookID, @CurAuthorID)INSERT INTO dbo.Author(Name) VALUES('linda')SET @CurAuthorID = SCOPE_IDENTITY()INSERT INTO dbo.BookAuthor(Book_ID, Author_ID) VALUES(@CurBookID, @CurAuthorID)INSERT INTO dbo.Book(Name) VALUES('book_b')SET @CurBookID = SCOPE_IDENTITY();INSERT INTO dbo.BookAuthor(Book_ID, Author_ID) VALUES(@CurBookID, @CurAuthorID)--查询数据SELECT * FROM dbo.BookSELECT * FROM dbo.AuthorSELECT * FROM dbo.BookAuthor