有三个表:电影表,演员表,电影演员关系表(一个演员演出多部电影,一部电影由多个演员演出)
请编写一个查询,查询没有演出’人月神话‘的所有演员名称,演员名称降序排序。
备注:注意 表名小写
以下是数据:
##CREATE DATABASE test6 DEFAULT CHARACTER SET utf8;
/*打开数据库 */
##use test6;
CREATE TABLE film(
film_no smallint(5) not null PRIMARY KEY comment '电影编号',
title VARCHAR(100) NOT NULL comment '电影名称'
);
CREATE TABLE actor(
actor_no smallint(5) not null PRIMARY KEY comment '演员编号',
name VARCHAR(20) NOT NULL comment '演员名称',
country VARCHAR(10) comment '演员国籍'
);
CREATE TABLE film_actor(
film_no smallint(5) comment '电影编号',
actor_no smallint(5) comment '演员编号',
description VARCHAR(100) comment '角色描述',
primary key (film_no, actor_no),
foreign key (film_no) references film(film_no) ,
foreign key (actor_no) references actor(actor_no)
);
insert into film values(356, '人月神话');
insert into film values(22, '鲲鹏');
insert into film values(422, '小平小道');
insert into actor values(12, 'A', 'china');
insert into actor values(15, 'B', 'china');
insert into actor values(134, 'C', 'America');
insert into actor values(147, 'D', 'china');
insert into film_actor values(356, 147, 'pure girl ');
insert into film_actor values(356, 12, 'tried father ');
insert into film_actor values(422, 12, 'loser');
insert into film_actor values(22, 12, 'brave man');