本例是基于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`;
</