[SQL] - Many to Many relationship

譬如一个 banner 可以有多种 theme，一个 theme 可以属于多种 banner，banner 和 theme 之间的关係及属于多对多。

以此纪录设计多对多关係的 tables，并利用 JOIN 取得关联资料。

CREATE TABLE banners (  id INT PRIMARY KEY,  name VARCHAR(50),  image_url VARCHAR(50),  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,);CREATE TABLE themes (  id INT PRIMARY KEY,  name VARCHAR(50),  color VARCHAR(50),  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);CREATE TABLE banner_theme (  banner_id INT,  theme_id IINT,  PRIMARY KEY (banner_id, theme_id),  FOREIGN KEY (banner_id) REFERENCES banners(id) ON DELETE CASCADE,  FOREIGN KEY (theme_id) REFERENCES themes(id) ON DELETE CASCADE,);INSERT INTO banners (id, name, image_url) VALUES (1, 'Banner 1', 'http://example.com/banner1.jpg');INSERT INTO banners (id, name, image_url) VALUES (2, 'Banner 2', 'http://example.com/banner2.jpg');INSERT INTO themes (id, name, color) VALUES (1, 'Theme 1', 'red');INSERT INTO themes (id, name, color) VALUES (2, 'Theme 2', 'blue');INSERT INTO banner_theme (banner_id, theme_id) VALUES (1, 1);INSERT INTO banner_theme (banner_id, theme_id) VALUES (1, 2);INSERT INTO banner_theme (banner_id, theme_id) VALUES (2, 2);SELECT banners.name, themes.name, themes.colorFROM bannersJOIN banner_theme ON banners.id = banner_theme.banner_idJOIN themes ON banner_theme.theme_id = themes.id;