三表连接查询

1.题目描述

牛客网 30.使用子查询的方式找出属于Action分类的所有电影对应的title,description吗

2.创建表格及其字段

2.1 创建film表

CREATE TABLE IF NOT EXISTS film (
film_id smallint(5)  NOT NULL DEFAULT '0',
title varchar(255) NOT NULL,
description text,
PRIMARY KEY (film_id));
INSERT INTO film VALUES(1,'ACADEMY DINOSAUR','A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies');
INSERT INTO film VALUES(2,'ACE GOLDFINGER','A Astounding Epistle of a Database Administrator And a Explorer who must Find a Car in Ancient China');
INSERT INTO film VALUES(3,'ADAPTATION HOLES','A Astounding Reflection of a Lumberjack And a Car who must Sink a Lumberjack in A Baloon Factory');

2.2 创建film_category表

CREATE TABLE film_category  (
film_id  smallint(5)  NOT NULL,
category_id  tinyint(3)  NOT NULL, `last_update` timestamp);
INSERT INTO film_category VALUES(1,1,'2006-02-14 21:07:09');
INSERT INTO film_category VALUES(2,1,'2006-02-14 21:07:09');
INSERT INTO film_category VALUES(3,6,'2006-02-14 21:07:09');

2.3 创建category表

CREATE TABLE category  (
category_id  tinyint(3)  NOT NULL ,
name  varchar(25) NOT NULL, `last_update` timestamp,
PRIMARY KEY ( category_id ));
INSERT INTO category VALUES(1,'Action','2006-02-14 20:46:27');
INSERT INTO category VALUES(2,'Animation','2006-02-14 20:46:27');
INSERT INTO category VALUES(3,'Children','2006-02-14 20:46:27');
INSERT INTO category VALUES(4,'Classics','2006-02-14 20:46:27');
INSERT INTO category VALUES(5,'Comedy','2006-02-14 20:46:27');
INSERT INTO category VALUES(6,'Documentary','2006-02-14 20:46:27');

3.方法

方法1:两次left join连接 (左连接，左表是老大，要保留左表的所有行.)

#方法1
SELECT f.title, f.description
FROM film AS f
LEFT JOIN film_category AS fc ON fc.film_id = f.film_id
LEFT JOIN category AS c ON c.category_id = fc.category_id
WHERE c.name = 'Action'

方法2:

select f.title,f.description
from film f
join film_category fc on f.film_id=fc.film_id

where fc.category_id=(
    select category_id
    from category
    where name = 'Action'
)

方法3:

#方法1
select title, description
from film F, category C, film_category FC
where
(
FC.film_id = F.film_id and C.category_id = FC.category_id and name = 'Action'
)

3种方法最终的结果为

4.补充内连接：内连接是找两表的交集

select * 
from film_category AS fc inner join category AS c 
on fc.category_id = c.category_id

两表内连接的结果