MySQL刷题-牛客网 Day6

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

本题的关键在于子查询 ，难度不大，直接给出两种题解：

方法一（用子查询）

SELECT f.title, f.description 
FROM film AS f
WHERE f.film_id IN (SELECT fc.film_id 
                    FROM film_category AS fc
                    WHERE fc.category_id IN (SELECT c.category_id 
                                             FROM category AS c
                                             WHERE c.name = 'Action'))

方法二（不用子查询）

SELECT f.title,f.description
FROM film AS f 
INNER JOIN film_category AS fc 
ON f.film_id = fc.film_id
INNER JOIN category AS c 
ON c.category_id = fc.category_id
WHERE c.name = 'Action'

获取select * from employees对应的执行计划

EXPLAIN SELECT * FROM employees

创建一个actor表

CREATE TABLE actor 
(actor_id smallint(5) NOT NULL,
 first_name varchar(45) NOT NULL,
 last_name varchar(45) NOT NULL,
 last_update timestamp NOT NULL DEFAULT (datetime('now','localtime')),
 PRIMARY KEY (actor_id))

这里主要在于主键的设置，有两种形式，一是在改行末尾加上PRIMARY KEY ，二是在表的最后一行设置PRIMARY KEY (actor_id)。其次，要说明一下获取系统默认时间的语句为datetime(‘now’,‘localtime’)。