MySQL查询练习一（多表）_mysql 多表查询练习题

CREATE DATABASE filmclub;
USE filmclub;
 
CREATE TABLE category (
  cid INT PRIMARY KEY AUTO_INCREMENT,
  cname VARCHAR(20)
);
 
CREATE TABLE film (
  fid INT PRIMARY KEY AUTO_INCREMENT,
  fname VARCHAR(20),
  director VARCHAR(20), # Director
  price DECIMAL(10,2),
  showtime DATE,
  cid INT,
  FOREIGN KEY(cid) REFERENCES category(cid)
);
 
CREATE TABLE USER (
  uid INT PRIMARY KEY AUTO_INCREMENT,
  uname VARCHAR(20),
  birthday DATE,
  gender ENUM('男','女'),
  address VARCHAR(20),
  cellphone CHAR(11)
);
 
CREATE TABLE user_category (
  uid INT,
  cid INT,
  FOREIGN KEY(uid) REFERENCES USER(uid),
  FOREIGN KEY(cid) REFERENCES category(cid),
  PRIMARY KEY(uid,cid)
);
 
CREATE TABLE emp (
  eid INT PRIMARY KEY AUTO_INCREMENT,
  ename VARCHAR(20),
  gender ENUM('男','女'),
  hiredate DATE,
  sal DECIMAL(10,2),
  address VARCHAR(20)
);
 
CREATE TABLE sal_grade (
  gid INT PRIMARY KEY AUTO_INCREMENT,
  minsal DECIMAL(10,2),
  maxsal DECIMAL(10,2)
);
 
CREATE TABLE orders (
  eid INT,
  uid INT,
  fid INT,
  num INT,
  odate DATE
);
 
INSERT INTO category VALUES(NULL, '喜剧');
INSERT INTO category VALUES(NULL, '动作');
INSERT INTO category VALUES(NULL, '悬疑');
INSERT INTO category VALUES(NULL, '恐怖');
INSERT INTO category VALUES(NULL, '科幻');
INSERT INTO category VALUES(NULL, '战争');
INSERT INTO category VALUES(NULL, '爱情');
INSERT INTO category VALUES(NULL, '灾难');
 
INSERT INTO film VALUES(NULL, '天下无贼', '冯小刚', 50, '2008-12-12', 1);
INSERT INTO film VALUES(NULL, '功夫', '周星驰', 150, '2009-12-12', 2);
INSERT INTO film VALUES(NULL, '大话西游', '周星驰', 20, '2012-3-12', 3);
INSERT INTO film VALUES(NULL, '我不是潘金莲', '冯小刚', 30, '2007-5-31', 1);
INSERT INTO film VALUES(NULL, '道士下山', '陈凯歌', 40, '2004-8-9', 8);
INSERT INTO film VALUES(NULL, '火锅英雄', '陈凯歌', 60, '2011-11-11', 7);
INSERT INTO film VALUES(NULL, '寻龙诀', '冯小刚', 100, '2007-7-7', 7);
INSERT INTO film VALUES(NULL, '老炮儿', '陈凯歌', 80, '2005-9-2', 1);
INSERT INTO film VALUES(NULL, '我是证人', '周星驰', 90, '2010-10-5', 2);
INSERT INTO film VALUES(NULL, '叶问', '冯小刚', 120, '2012-6-3', 3);
 
INSERT INTO USER VALUES(NULL, '刘欢', '1950-1-1', '男', '北大街', '13312345678');
INSERT INTO USER VALUES(NULL, '张学友', '1955-2-3', '男', '南大街', '13312345676');
INSERT INTO USER VALUES(NULL, '刘嘉玲', '1970-11-21', '女', '北大街', '13312345675');
INSERT INTO USER VALUES(NULL, '李嘉欣', '1988-9-3', '女', '南大街', '13312345673');
INSERT INTO USER VALUES(NULL, '刘德华', '1953-2-11', '男', '北大街', '13312345672');
INSERT INTO USER VALUES(NULL, '张国立', '1999-12-31', '男', '东大街', '13312345671');
INSERT INTO USER VALUES(NULL, '张国荣', '1988-3-23', '男', '西大街', '13312345670');
INSERT INTO USER VALUES(NULL, '刘建国', '1970-6-22', '男', '西大街', '13312345679');
 
INSERT INTO user_category VALUES(1, 2);
INSERT INTO user_category VALUES(1, 3);
INSERT INTO user_category VALUES(2, 8);
INSERT INTO user_category VALUES(3, 1);
INSERT INTO user_category VALUES(3, 5);
INSERT INTO user_category VALUES(3, 7);
INSERT INTO user_category VALUES(2, 1);
INSERT INTO user_category VALUES(1, 7);
INSERT INTO user_category VALUES(8, 8);
INSERT INTO user_category VALUES(8, 7);
INSERT INTO user_category VALUES(5, 7);
INSERT INTO user_category VALUES(5, 3);
INSERT INTO user_category VALUES(2, 6);
INSERT INTO user_category VALUES(7, 1);
INSERT INTO user_category VALUES(7, 2);
INSERT INTO user_category VALUES(7, 3);
 
INSERT INTO emp VALUES(NULL, '郭靖', '男', '2002-2-3', 2500, '东大街');
INSERT INTO emp VALUES(NULL, '黄蓉', '女', '2003-12-3', 5500, '东大街');
INSERT INTO emp VALUES(NULL, '杨幂', '女', '2002-2-8', 8500, '西大街');
INSERT INTO emp VALUES(NULL, '刘诗诗', '女', '2004-4-12', 6500, '南大街');
 
INSERT INTO sal_grade VALUES(NULL, 1000, 2000);
INSERT INTO sal_grade VALUES(NULL, 2001, 4000);
INSERT INTO sal_grade VALUES(NULL, 4001, 5000);
INSERT INTO sal_grade VALUES(NULL, 5001, 7000);
INSERT INTO sal_grade VALUES(NULL, 7001, 9000);
 
INSERT INTO orders VALUES(1, 2, 10, 1, '2016-11-11');
INSERT INTO orders VALUES(2, 3, 8, 2, '2016-2-21');
INSERT INTO orders VALUES(3, 7, 10, 1, '2016-3-21');
INSERT INTO orders VALUES(1, 1, 7, 1, '2016-10-15');
INSERT INTO orders VALUES(1, 8, 3, 1, '2016-2-17');
INSERT INTO orders VALUES(1, 7, 1, 1, '2016-5-18');
INSERT INTO orders VALUES(4, 1, 1, 1, '2016-5-7');
INSERT INTO orders VALUES(4, 2, 1, 1, '2016-5-9');
INSERT INTO orders VALUES(4, 3, 1, 1, '2016-11-10');
INSERT INTO orders VALUES(3, 5, 10, 1, '2016-5-11');
INSERT INTO orders VALUES(2, 1, 1, 1, '2016-7-12');
INSERT INTO orders VALUES(2, 1, 1, 2, '2016-7-13');
INSERT INTO orders VALUES(2, 1, 1, 3, '2016-7-14');
INSERT INTO orders VALUES(2, 1, 9, 5, '2016-8-19');
INSERT INTO orders VALUES(2, 1, 8, 3, '2016-8-4');
INSERT INTO orders VALUES(2, 2, 5, 1, '2016-8-6');
INSERT INTO orders VALUES(2, 2, 6, 1, '2016-1-22');
INSERT INTO orders VALUES(2, 2, 1, 1, '2016-1-11');
INSERT INTO orders VALUES(2, 6, 3, 1, '2016-2-17');
INSERT INTO orders VALUES(2, 6, 8, 2, '2016-3-12');

下面为本人所用数据库版本

1.建立数据库和数据表

 category表：

 emp表：

 film表：

 order表：

 sal_grade表：

 sal_grade_copy表：

user表：

 user_category表：

2.查询价格比2号类型所有电影平均价还低的电影

语句：

#查询价格比2号类型所有电影平均价还低的电影
select fname,price from film where price<(select avg(price) from film where cid=2);

运行结果：

3.查询张学友喜欢的类型中所有电影的最高价

语句：
 

#3.查询张学友喜欢的类型中所有电影的最高价
select uname,max(price)  from film 
join category on film.cid=category.cid
join user_category on category.cid=user_category.cid
join `user` on `user`.uid=user_category.uid
where `user`.uname='张学友' ;

运行结果：

4.查询所有电影，以及电影对应的类型名，要求显示出所有类型名

语句：

#4.查询所有电影，以及电影对应的类型名，要求显示出所有类型名
SELECT fname,cname from film 
join  category on category.cid=film.cid;

运行结果：

5.查询价格大于天下无贼的电影中，每部类型各多少电影

语句：

#5.查询价格大于天下无贼的电影中，每部类型各多少电影
 
select category.cname ,count(category.cid) from film
 join category on film.cid=category.cid
 WHERE price>(select price from film where film.fname='天下无贼')
 GROUP BY category.cid
 ;

运行结果：

6.查询那些员工给那些会员卖了哪些电影

语句：

#6.查询那些员工给那些会员卖了哪些电影
select ename as '员工姓名',uname as '会员', fname as '电影名', num as '数量' from orders
join emp on emp.eid=orders.eid
join film on film.fid=orders.fid
join  `user` on `user`.uid=orders.uid;

结果：

7.查询有回头客的员工
 

语句：

#7.查询有回头客的员工
select ename as '有回头客的员工姓名' ,uname as '回头客姓名',count(orders.uid)from orders
join emp on emp.eid=orders.eid
join film on film.fid=orders.fid
join  `user` on `user`.uid=orders.uid
group by orders.uid,orders.eid
HAVING count(orders.uid)>1 ;

查询结果：

8.查询回头客超过一个的员工（表子查询在mysql中必须起别名，否则报错，orcale中则不需要别名）
 

9.查询价格比所有喜剧类型的平均价还高的电影
 

语句：

#9.查询价格比所有喜剧类型的平均价还高的电影
#查询所有喜剧类型的平均价
select f1.fid,f1.fname,f1.director,f1.price,f1.showtime,c1.cname
 from film as f1
 join category as c1 on c1.cid=f1.cid 
where price>
(SELECT avg(price) from film as f 
join category as c on  f.cid=c.cid
where c.cname='喜剧');

结果：

 10.查询电影所属类型的平均价（相关子查询）
 

语句：

#10.查询电影所属类型的平均价（相关子查询）
select cname as 电影类型,(select avg(price) from film where film.cid=c1.cid) as 平均价格 from category as c1 

结果：

11.查询价格比1号类型所有电影价格都高的电影

语句:

select f1.fid,f1.fname,f1.director,f1.price,f1.showtime,c1.cname
 from film as f1
 join category as c1 on c1.cid=f1.cid 
where price>
(SELECT max(price) from film as f 
join category as c on  f.cid=c.cid
where c.cid=1);

结果：

12.查询上映日期比天下无贼晚的电影中，每个导演各多少部电影

 语句：

#12.查询上映日期比天下无贼晚的电影中，每个导演各多少部电影
#1.查询天下无贼上映日期
select showtime from film where fname='天下无贼';
#2.查询上映日期比天下无贼晚的电影
select f1.fid,f1.fname,f1.director,f1.price,f1.showtime,c1.cname
 from film as f1
 join category as c1 on c1.cid=f1.cid 
 where DATEDIFF(f1.showtime,(select showtime from film where fname='天下无贼'
))>0;
#3.查询上映日期比天下无贼晚的电影中，每个导演各多少部电影
select r1.director as 导演姓名 ,count(r1.director) as 电影数量 from
(select f1.fid,f1.fname,f1.director,f1.price,f1.showtime,c1.cname
 from film as f1
 join category as c1 on c1.cid=f1.cid 
 where DATEDIFF(f1.showtime,(select showtime from film where fname='天下无贼'
))>0) as r1
GROUP BY r1.director;

1.运行结果： 

2.运行结果：

 3.运行结果：

13.查询哪些用户都喜欢哪些类型

语句：

#13.查询哪些用户都喜欢哪些类型
SELECT uname as 用户名,cname as 喜欢的电影类型 from `user` as u
join user_category as uc on u.uid=uc.uid
join category as c on c.cid=uc.cid;

结果：

14.查询喜欢类型超过2种的用户

语句：

#查询喜欢类型超过2种的用户
SELECT uname  from `user` as u
join user_category as uc on u.uid=uc.uid
join category as c on c.cid=uc.cid
GROUP BY u.uid 
having count(uc.cid)>2;

运行结果：

 

15.查询从来没有被喜欢过的类型

 语句：

#15.查询从来没有被喜欢过的类型
select cname from 
(select cname,uname from category as c1 
 left join user_category as uc on uc.cid=c1.cid
 left join `user` as u on u.uid=uc.uid
 GROUP BY uc.uid,uc.cid) as r1
where uname is null;

运行结果：

16.查询喜欢了所有类型的用户

语句即运行结果：

17.查询每个销售人员的销售总额

语句：

#17.查询每个销售人员的销售总额
SELECT ename as 销售人员姓名,sum(num*price) as 销售总额 from emp as e 
join orders as o on  o.eid=e.eid
join film as f on f.fid=o.fid
GROUP BY o.eid;

运行结果：

18.查询每个电影各被卖出多少张

语句：

#18.查询每个电影各被卖出多少张
select fname as 电影名,sum(num) as 数量
 from orders as o
join film as f on f.fid=o.fid
GROUP BY o.fid;

运行结果：

19.查询上映日期比周星驰导演所有电影都晚的电影中，每种类型电影的最低价
 

语句：

#19.查询上映日期比周星驰导演所有电影都晚的电影中，每种类型电影的最低价
#1.查到周星驰导演的所有电影上映时间最晚的。
SELECT showtime from film where director='周星驰' ORDER BY showtime DESC limit 1;
#2.查询上映日期比周星驰导演所有电影都晚的电影
SELECT * from film
 where DATEDIFF(showtime,(SELECT showtime from film where director='周星驰' ORDER BY showtime DESC limit 1))>0;
#3.
select fname,min(price),cname from (SELECT * from film
 where DATEDIFF(showtime,(SELECT showtime from film where director='周星驰' ORDER BY showtime DESC limit 1))>0)
 as r2 join category as c1 on c1.cid=r2.cid
 GROUP BY r2.cid;

1.运行结果：

2.运行结果：

3.运行结果：

20.查询在闰年上映的电影
 

运行结果：

#20查询在闰年上映的电影
#条件
select * from film WHERE
DATEDIFF(CONCAT(year(showtime),'-3-1'),CONCAT(year(showtime),'-2-1'))=29;

运行结果：

21.查询上映日期中，月份比日期大的电影
 

语句：

#21.查询上映日期中，月份比日期大的电影
select * from film where MONTH(showtime)>DAY(showtime);

运行结果：

22.查询价格与类型都与‘天下无贼’相同的电影

语句：

# 22.查询价格与类型都与‘天下无贼’相同的电影
SELECT fname from  film as f1 ,(SELECT price,cid from film where fname='天下无贼') as r1 
where 
 r1.price=f1.price AND
 r1.cid=f1.cid;

运行结果：

 22.查询最受欢迎的类型
 

语句:

#22.查询最受欢迎的类型
SELECT cname, SUM(num) from orders as o
join film as f on f.fid=o.fid
join category as c on c.cid=f.cid
GROUP BY c.cid
ORDER BY SUM(num) desc
limit 1;

运行结果：

 

23.查询每个用户各花了多少钱

语句：

#23.查询每个用户各花了多少钱
select uname as 用户,sum(price*num) AS 花销  from orders as o join `user` as u on u.uid=o.uid
join film as f on f.fid =o.fid
GROUP BY u.uid;

运行结果：