基于mysql的sakila数据库脚本分析

本例是基于mysql的sakila数据库脚本的复杂查询分析，大家可以去mysql官网上下载此脚本；也可以进入我的资源页进行下载：

关系图如下：

    

 

下面是查询的案例：

 1.查询某部电影的所属类别，语言

SELECT film.title AS '电影名称', category.name AS '种类名称', language.name AS '语言'  FROM film_category 
LEFT JOIN film  ON film_category.`film_id` = film.`film_id`   
LEFT JOIN `category` ON film_category.category_id = category.category_id
LEFT JOIN LANGUAGE ON film.language_id = language.language_id
WHERE film.title = 'ACADEMY DINOSAUR';

2.查询某部电影的演员有哪些

SELECT film.title AS '电影名称', CONCAT(actor.last_name, CONCAT(" ", actor.first_name)) AS '参与演员'
FROM film 
INNER JOIN film_actor
ON film.`film_id` = film_actor.`film_id`
INNER JOIN actor 
ON film_actor.`actor_id` = actor.`actor_id`
WHERE film.title = 'ACADEMY DINOSAUR';

3.查询某部电影的演员人数

SELECT film.title AS '电影名称', COUNT(film_actor.`actor_id`) AS '参演人数' FROM film 
INNER JOIN film_actor ON film.`film_id` = film_actor.`film_id`
WHERE film.title = 'ACADEMY DINOSAUR';

4.查询电影id，库存数

 

SELECT film.`film_id` AS '电影id', COUNT(inventory.`film_id`) AS '库存数'
FROM film, inventory
WHERE film.`film_id` = inventory.`film_id`
GROUP BY film.`film_id`;</