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
两表内连接的结果