SQL语句练习附带答案

首先创建数据库filmclub

DROP DATABASE IF EXISTS filmclub;

CREATE DATABASE filmclub;

USE filmclub;

CREATE TABLE category
(
 cid INT PRIMARY KEY AUTO_INCREMENT,
 cname VARCHAR(20)
)CHARSET=utf8;
CREATE TABLE film
(
 fid INT PRIMARY KEY AUTO_INCREMENT,
 fname VARCHAR(20),
 director VARCHAR(20), 
 price DECIMAL(10,2),
 showtime DATE,
 cid INT,
 FOREIGN KEY(cid) REFERENCES category(cid)
)CHARSET=utf8;
CREATE TABLE USER
(
 uid INT PRIMARY KEY AUTO_INCREMENT,
 uname VARCHAR(20),
 birthday DATE,
 gender VARCHAR(10),
 address VARCHAR(20),
 cellphone CHAR(11)
)CHARSET=utf8;
CREATE TABLE user_category
(
  uid INT,
  cid INT,
  FOREIGN KEY(uid) REFERENCES USER(uid),
  FOREIGN KEY(cid) REFERENCES category(cid),
  PRIMARY KEY(uid,cid)
)CHARSET=utf8;

CREATE TABLE emp
(
  eid INT PRIMARY KEY AUTO_INCREMENT,
  ename VARCHAR(20),
  gender VARCHAR(10),
  hiredate DATE,
  sal DECIMAL(10,2),
  address VARCHAR(20)
)CHARSET=utf8;

CREATE TABLE sal_grade
(
  gid INT PRIMARY KEY AUTO_INCREMENT,
  minsal DECIMAL(10,2),
  maxsal DECIMAL(10,2)
)CHARSET=utf8;


CREATE TABLE orders
(
  eid INT,
  uid INT,
  fid INT,
  num INT,
  odate DATE
)CHARSET=utf8;


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');


SELECT * FROM category;
SELECT * FROM film;
SELECT * FROM USER;
SELECT * FROM USER_category;
SELECT * FROM emp;
SELECT * FROM sal_grade;
SELECT * FROM orders;

表关系图如下：

题目如下：

1.查询一共有多少部电影

2.查询电影价格最低不小于100的类型

3.查询所有电影的名字，以及电影对应的类型名

4.查询所有每个员工的销售总额

5.查询所有员工的名字，以及员工的工资级别

6.查询每种类型各有多少部电影

7.查询每个用户多少岁.

8.查询历年来每个月份各多少订单

9.查询2016年每个月的订单数

10.查询每个会员购买过多少种电影（不考虑数量，即使购买了多个相同电影也算作一种）

11.查询每个会员，各买过多少盘DVD

12. 查询价格大于所有电影平均价的电影

13. 查询价格大于‘喜剧’平均价的电影

14. 查询价格大于所属类型平均价的电影

15. 查询有回头客的员工

16. 查询销售额最高的员工

17. 查询回头客最多的员工

18. 查询喜欢的类型，与‘刘德华’完全一样的客户

19. 查询每种类型的最低价

20. 查询每种类型中，价格在前2位的电影

21. 查询不喜欢‘喜剧’类型的用户中，每个住址住多少人

答案如下，仅供参考~

# 1.查询一共有多少部电影
select count(1) as '一共有多少部电影'
from film;

# 2.查询电影价格最低不小于100的类型
select c.cname
from film
         left join category c on c.cid = film.cid
where price >= 100;

# 3.查询所有电影的名字，以及电影对应的类型名
select c.cname, film.fname
from film
         left join category c on c.cid = film.cid;

# 4.查询所有每个员工的销售总额
select t.ename, sum(t.price)
from (select e.ename, f.price * o.num price
      from emp e,
           orders o,
           film f
      where e.eid = o.eid
        and o.fid = f.fid) t
group by t.ename;

# 5.查询所有员工的名字，以及员工的工资级别
SELECT emp.ename, sal_grade.gid
FROM emp
         JOIN sal_grade ON emp.sal BETWEEN sal_grade.minsal AND sal_grade.maxsal;


# 6.查询每种类型各有多少部电影
SELECT category.cname, COUNT(film.fid) AS movie_count
FROM category
         LEFT JOIN film ON category.cid = film.cid
GROUP BY category.cname;
-- 7.查询每个用户多少岁.
select u.uname Name, concat(timestampdiff(year, u.birthday, now()), '周岁') age, now(), curdate()
from user u;

-- 8.查询历年来每个月份各多少订单
SELECT YEAR(odate) AS year, MONTH(odate) AS month, COUNT(*) AS order_count
FROM orders
GROUP BY YEAR(odate), MONTH(odate)
ORDER BY YEAR(odate), MONTH(odate);
-- 9.查询2016年每个月的订单数
SELECT MONTH(odate) AS month, COUNT(*) AS order_count
FROM orders
WHERE YEAR(odate) = 2016
GROUP BY MONTH(odate)
ORDER BY MONTH(odate);

-- 10.查询每个会员，各买过多少种电影(不算数量，如果买1号电影，买了10盘DVD，算1个)
SELECT USER.uid, USER.uname, COUNT(DISTINCT orders.fid) AS movie_count
FROM USER
         JOIN orders ON USER.uid = orders.uid
GROUP BY USER.uid, USER.uname;

-- 11.查询每个会员，各买过多少盘DVD
SELECT USER.uid, USER.uname, SUM(orders.num) AS dvd_count
FROM USER
         JOIN orders ON USER.uid = orders.uid
GROUP BY USER.uid, USER.uname;

-- 12. 查询价格大于所有电影平均价的电影
SELECT *
FROM film
WHERE price > (
    SELECT AVG(price)
    FROM film
);

-- 13. 查询价格大于‘喜剧’平均价的电影
SELECT *
FROM film
WHERE price > (
    SELECT AVG(film.price)
    FROM film
             INNER JOIN category ON film.cid = category.cid
    WHERE category.cname = '喜剧'
);

-- 14. 查询价格大于所属类型平均价的电影
SELECT film.*
FROM film
         JOIN category ON film.cid = category.cid
WHERE film.price > (
    SELECT AVG(film.price)
    FROM film
    WHERE film.cid = category.cid
);
-- 15. 查询有回头客的员工
select distinct e.eid, e.ename
from orders
         left join emp e on orders.eid = e.eid
group by eid, uid
having count(uid) > 1;

-- 16. 查询销售额最高的员工
SELECT emp.eid, emp.ename, SUM(film.price * orders.num) AS total_sales
FROM emp
         JOIN orders ON emp.eid = orders.eid
         JOIN film ON orders.fid = film.fid
GROUP BY emp.eid, emp.ename
ORDER BY total_sales DESC
LIMIT 1;

-- 17. 查询回头客最多的员工
SELECT emp.eid, emp.ename, COUNT(DISTINCT orders.uid) AS num_return_customers
FROM emp
         JOIN orders ON emp.eid = orders.eid
GROUP BY emp.eid, emp.ename
ORDER BY num_return_customers DESC
LIMIT 1;

-- 18. 查询喜欢的类型，与‘刘德华’完全一样的客户
select *
from (select u.uid, u.uname, group_concat(uc.cid order by uc.cid) cid
      from user u,
           user_category uc
      where u.uid = uc.uid

        and u.uname != '刘德华'
      GROUP BY u.uid, u.uname) t1,
     (select group_concat(uc.cid order by uc.cid) cid
      from user u,
           user_category uc
      where u.uid = uc.uid
        and u.uname = '刘德华')
         t2
where t1.cid = t2.cid;


-- 19. 查询每种类型的最低价
SELECT category.cname, MIN(film.price) AS min_price
FROM category
         JOIN film ON category.cid = film.cid
GROUP BY category.cname;

-- 20. 查询每种类型中，价格在前2位的电影
SELECT cname,
       fname,
       price
FROM (SELECT c.cname,
             f.fname,
             f.price,
             ROW_NUMBER() OVER ( PARTITION BY c.cid ORDER BY f.price DESC ) AS row_num
      FROM film f,
           category c
      WHERE f.cid = c.cid) AS result
WHERE row_num <= 2;
-- 21. 查询不喜欢‘喜剧’类型的用户中，每个住址个多少人
SELECT address, COUNT(*) AS person_count
FROM USER
WHERE uid NOT IN (
    SELECT uid
    FROM user_category
    WHERE cid = (
        SELECT cid
        FROM category
        WHERE cname = '喜剧'
    )
)
GROUP BY address;