JOIN 表联结（二）

这次教程，介绍的还是 JOIN概念。
数据库包含了movie , actor and casting三张表。

数据表介绍

练习题

1.1962 年的电影们

例子：查询——所有年份是 1962 年的电影，显示 id,title

SELECT id, title
FROM movie
WHERE yr=1962

2.《公民凯恩》哪年上映？

SELECT yr
FROM movie 
WHERE title='Citizen Kane'

3.星际迷航系列电影

SELECT id, title, yr
FROM movie
WHERE title LIKE '%Star Trek%'
ORDER BY yr

4.演员 Glenn Close的 id

SELECT id
FROM actor
WHERE name='Glenn Close'

5.电影《卡萨布兰卡》的 id

SELECT id
FROM movie
WHERE title='Casablanca'

Get_to_the_point（猝不及防的补充知识点）

movie数据库里的JOIN表联结

针对电影数据库，常见的问题都涵盖了三张表的数据内容，诸如「哪个演员在哪部电影里出现了？」、「哪部电影怎么怎么样」。可以使用嵌入SELECT声明来回答上述问题。

也可以使用JOIN，通过两张表里共同的域值（的表格来连接，比如actor表里的 id指向casting表里的actorid，连接之后的表格涵盖了两个表格的所有属性。

示例——联结2张表

SELECT * FROM casting JOIN actor
          ON casting.actorid=actor.id
  WHERE actor.name='John Hurt'

示例——联结3张表

SELECT * FROM
   movie JOIN casting ON movie.id=movieid
         JOIN actor   ON actorid=actor.id
  WHERE actor.name='John Hurt'

注意：有时，我们指定 field 时，使用 field 的名称actorid，有时，我们使用casting.actorid,如果 field 的名称不是唯一的，有重复的 field 名称时，必须包含表格的名称。

6.电影《卡萨布兰卡》的卡司（演员）名单？

SELECT name FROM 
movie JOIN casting ON (movie.id=movieid)
           JOIN actor  ON (actorid=actor.id)
WHERE movieid=11768

7.电影《Alien》的卡司（演员）名单？

SELECT name FROM 
movie JOIN casting ON (movie.id=movieid)
           JOIN actor  ON (actorid=actor.id)
WHERE title='Alien'

8.演员Harrison Ford参演的电影名单？

SELECT title FROM 
movie JOIN casting ON (movie.id=movieid)
           JOIN actor  ON (actorid=actor.id)
WHERE name='Harrison Ford'

9.演员Harrison Ford做为配角的电影名单？

SELECT title FROM 
movie JOIN casting ON (movie.id=movieid)
           JOIN actor  ON (actorid=actor.id)
WHERE name='Harrison Ford'
AND ord!=1

10.演员Harrison Ford做为配角的电影名单？

SELECT title, name 
FROM movie 
JOIN casting ON (movie.id=movieid)
JOIN actor  ON (actorid=actor.id)
WHERE yr=1962
AND ord=1

更难一些的题目

11.John Travolta档期满满的年份

SELECT yr,COUNT(title) FROM
  movie JOIN casting ON movie.id=movieid
         JOIN actor   ON actorid=actor.id
where name='John Travolta'
GROUP BY yr
HAVING COUNT(title)=(SELECT MAX(c) FROM
(SELECT yr,COUNT(title) AS c FROM
   movie JOIN casting ON movie.id=movieid
         JOIN actor   ON actorid=actor.id
 where name='John Travolta'
 GROUP BY yr) AS t
)

12.Julie Andrews参演电影中的主演名单？

SELECT title,name FROM movie
JOIN casting ON (movie.id=movieid AND ord=1)
JOIN actor   ON (actorid=actor.id)
WHERE movie.id IN(
                            SELECT movieid FROM casting
                            WHERE actorid IN (
                             SELECT id FROM actor
                             WHERE name='Julie Andrews')
)

注意：使用 subquery 子查询

13.主演 30 部电影以上的演员？

SELECT name FROM actor
JOIN casting ON (id = actorid AND (SELECT COUNT(ord) FROM casting WHERE actorid = actor.id AND ord=1)>=30)
GROUP BY name

14.1978年的电影，谁的演员最多？

SELECT title, COUNT(actorid)
FROM movie JOIN casting ON (id=movieid)
WHERE yr = 1978
GROUP BY title 
ORDER BY COUNT(actorid) DESC, title

15.所有与 Art Garfunkel 共事过的电影人？

SELECT name
FROM   casting 
             JOIN actor ON (actorid = id)
WHERE  movieid IN (SELECT movieid
                     FROM   casting 
                            JOIN actor 
                              ON actorid = id
                     WHERE  name = 'Art Garfunkel')
       AND name <> 'Art Garfunkel'