MySQL005：子查询案例

注意：一般在子查询中，程序先运行在嵌套在最内层的语句，再运行外层。因此在写子查询语句时，可以先测试下内层的子查询语句是否输出了想要的内容，再一层层往外测试，增加子查询正确率。否则多层的嵌套使语句可读性很低。

案例：
创建电影俱乐部数据库

CREATE DATABASE film_club

1. 创建类型表: film_type
   t_id 主键 自增长
   t_name 不能为空

CREATE TABLE film_type(   
         t_id INT PRIMARY KEY AUTO_INCREMENT,
         t_name VARCHAR(20) NOT NULL
         )

2. 创建电影表: film
   f_id 主键 自增长, 让自增长从1001开始
   f_name 字符串，20列，默认值为“天将雄狮”
   f_price 定点类型，总共5列，小数2列，不能为空
   f_actor 枚举类型，演员只能是 李连杰,成龙,吴京,甄子丹,杨紫琼
   t_id 外键，依赖于类型表的主键，要有级联删除和级联修改功能

CREATE TABLE film( 
       f_id  INT PRIMARY KEY AUTO_INCREMENT,
       f_name VARCHAR(20) DEFAULT'天将雄狮',
       f_price DECIMAL(5,2) NOT NULL,
       f_actor ENUM('李连杰','成龙','吴京','甄子丹','杨紫琼'),
       t_id INT 
       )
       ALTER TABLE film ADD CONSTRAINT fk_student_cid FOREIGN KEY(t_id) REFERENCES film_type(t_id)ON  DELETE  CASCADE  ON  UPDATE  CASCADE
       ALTER TABLE film AUTO_INCREMENT=1001

3. 添加8个类型， 10部电影作为测试数据

 INSERT INTO film_type VALUES(DEFAULT,'热血'),(DEFAULT,'战争'),(DEFAULT,'科幻'),(DEFAULT,'喜剧');
 INSERT INTO film_type VALUES(DEFAULT,'动作'),(DEFAULT,'历史'),(DEFAULT,'古装'),(DEFAULT,'魔幻');
 SELECT * FROM film_type
 INSERT INTO film VALUES(DEFAULT,'流浪地球',59.89,'吴京',3);
 INSERT INTO film VALUES(DEFAULT,'太极张三丰',32.5,'李连杰',5);
 INSERT INTO film VALUES(DEFAULT,'十二生肖',49.0,'成龙',5);
 INSERT INTO film VALUES(DEFAULT,'战狼2',65.89,'吴京',2);
 INSERT INTO film VALUES(DEFAULT,'叶问',64.2,'甄子丹',6);
 INSERT INTO film VALUES(DEFAULT,'敢死队3',72.89,'李连杰',1);
 INSERT INTO film VALUES(DEFAULT,'神话',48.5,'成龙',8);
 INSERT INTO film VALUES(DEFAULT,'战狼1',54,'吴京',3);
 INSERT INTO film VALUES(DEFAULT,'卧虎藏龙',79.5,'杨紫琼',5);
 INSERT INTO film VALUES(DEFAULT,'明日帝国',51.7,'杨紫琼',4);

4. 完成以下CRUD操作：

5. 查询 李连杰 出演的所有电影

SELECT * FROM film WHERE f_actor ='李连杰'

2. 将类型为“喜剧”的电影，价格加价5元（子查询）

UPDATE film SET f_price=f_price+5 WHERE t_id=(SELECT t_id FROM film_type WHERE t_name='喜剧')

3. 将类型为“玄幻”的电影，类型改为“科幻”（子查询）
   注意：主表修改的时候，从表不能嵌套查，解决办法是加一个别名就好了。

UPDATE film_type SET t_name='科幻' WHERE t_id=
(
SELECT t_id FROM 
(SELECT t_id FROM film WHERE t_id=
 (SELECT t_id FROM film_type WHERE t_name='玄幻') )a) 

4. 查询价格比5号电影高的电影

SELECT * FROM film WHERE f_price>(SELECT f_price FROM film WHERE f_id=1005)

5. 将吴京出演的电影的类型改为“动作”（子查询）

UPDATE film SET t_id=(SELECT t_id FROM film_type WHERE t_name='动作')
WHERE f_actor='吴京'

6. 查询演员为成龙并且价格小于3号电影的电影（子查询）

SELECT * FROM film WHERE f_actor='成龙' AND f_price<(SELECT f_price FROM film WHERE f_id=1003)

7. 查询演员为杨紫琼或成龙或吴京的电影。

SELECT * FROM film WHERE f_actor IN ('杨紫琼','成龙','吴京')

8. 删除所有类型为“爱情”的电影(子查询)

DELETE FROM film WHERE t_id=(SELECT t_id FROM film_type WHERE t_name='爱情')

9. 将甄子丹的电影的价格改为5号电影的价格的2倍
   注意：主表修改的时候，从表不能嵌套查，解决办法是加一个别名就好了。
   如下如果不加别名，

UPDATE film SET f_price=2*(SELECT f_price FROM(SELECT f_price FROM film WHERE f_id=1005))WHERE f_actor='甄子丹'

就会出现以下错误：

这句话的意思是说每个派生出来的表都必须有一个自己的别名

正确代码：

UPDATE film SET f_price=2*(SELECT f_price FROM 
(SELECT f_price FROM film WHERE f_id=1005) a) 
WHERE f_actor='甄子丹'

10. 查询价格比所有电影的平均价格还低的电影（超纲）
    AVG就是求平均数的意思。

SELECT * FROM film WHERE f_price <
(SELECT AVG(f_price) FROM film)