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;
运行结果: