sql 数据库 多表之间的查询 纯逻辑 共勉

CREATE DATABASE mydb;
SHOW DATABASES ;
ALTER DATABASE mydb CHARACTER SET utf8;
SELECT database();
USE mydb;
DROP DATABASE mydb;
SHOW TABLES ;

#创建用户表: hw_user
DROP TABLE hw_user;
CREATE TABLE hw_user(
  uid INT PRIMARY KEY AUTO_INCREMENT,
  uname VARCHAR(50) NOT NULL ,
  age INT,
  loc CHAR(50)
);
INSERT INTO hw_user (uname, age, loc) VALUES
  ('李小龙',51,'北京'),
  ('张家辉',52,'上海'),
  ('周润发',62,'广州'),
  ('梁家辉',63,'深圳'),
  ('刘青云',48,'沈阳'),
  ('古天乐',39,'杭州'),
  ('张丰毅',28,'杭州'),
  ('梁朝伟',46,'北京'),
  ('陈道明',58,'北京'),
  ('张国荣',47,'香港'),
  ('黄渤',27,'北京'),
  ('吴镇宇',38,'香港'),
  ('周星驰',38,'香港')
;
SELECT *FROM hw_user;

#创建书类型表:type
CREATE TABLE type(
  tid INT PRIMARY KEY AUTO_INCREMENT,
  tname VARCHAR(50)NOT NULL
);

INSERT INTO type (tname) VALUES
  ('言情'),
  ('恐怖'),
  ('玄幻'),
  ('穿越'),
  ('异界'),
  ('校园'),
  ('仙侠'),
  ('武侠'),
  ('网游'),
  ('军事')
;
SELECT *FROM type;

#创建图书表:book
CREATE TABLE book(
  bkid INT PRIMARY KEY AUTO_INCREMENT,
  bkname VARCHAR(50) NOT NULL ,
  author VARCHAR(50) NOT NULL ,
  tid INT NOT NULL ,
  CONSTRAINT book_fk_type FOREIGN KEY (tid) REFERENCES type(tid)
);

INSERT INTO book (bkname, author, tid) VALUES
  ('完美世界','辰东',3),
  ('斗破苍穹','天蚕土豆',3),
  ('校花的贴身高手','鱼人二代',1),
  ('盗墓笔记','南派三叔',2),
  ('凡人修仙传','忘语',7),
  ('诛仙','萧鼎',7),
  ('将夜','猫腻',3),
  ('步步惊心','桐华',4),
  ('琅琊榜','海宴',4),
  ('异界之屠龙传奇','吴三胖子',5),
  ('天龙八部','金庸',8),
  ('狼牙','刘猛',10),
  ('从零开始','雷云风暴',9),
  ('飞升之后','黄埔奇',3)
;
SELECT *FROM book;

#创建评论表:comment
CREATE TABLE comment(
  comid INT NOT NULL  AUTO_INCREMENT PRIMARY KEY ,
  content TEXT NOT NULL ,
  bkid INT NULL ,
  uid INT NULL ,
  datetime DATETIME NOT NULL ,
  CONSTRAINT comment_fk_bkid FOREIGN KEY (bkid)REFERENCES book(bkid),
  CONSTRAINT comment_fi_uid FOREIGN KEY (uid)REFERENCES hw_user(uid)
);
DROP TABLE comment;
INSERT INTO comment (content, bkid, uid, datetime) VALUES
  ('这完美世界本书写的太好啦.',1,3,'2000-01-01 19:00:00'),
  ('天蚕土豆我要给你生土豆.',2,11,'2011-06-04 21:03:10'),
  ('天蚕土豆我要给你生土豆+1.',2,11,'2011-06-04 21:03:11'),
  ('天蚕土豆我要给你生土豆+2.',2,11,'2011-06-04 21:03:12'),
  ('天蚕土豆我要给你生土豆+3.',2,11,'2011-06-04 21:03:13'),
  ('你拿什么给他生?.',2,13,'2011-06-04 22:05:23'),
  ('强推飞升之后!.',14,3,'2013-09-14 02:15:33'),
  ('这是我看过的一本印象很深刻的军事小说!.',12,8,'2016-03-14 12:25:03'),
  ('写的不错,在追!.',7,7,'2016-03-14 12:25:03'),
  ('确实写的不错,我也在追!.',7,6,'2016-03-14 12:26:13'),
  ('哎这风云无忌怎么这么傻!.',14,9,'2013-03-17 14:36:19'),
  ('其实我还是比较喜欢迟伤的!.',14,10,'2018-05-22 00:36:00'),
  ('迟伤三扣谢恩师的时候都快给我看哭了!.',14,5,'2012-12-22 03:46:03'),
  ('还生土豆,小白文一个!.',2,5,'2011-07-22 13:41:13'),
  ('萧炎个烧饼!.',2,5,'2011-07-22 13:42:30'),
  ('不亏是金老师,经典就是经典!.',11,5,'2009-12-12 20:12:31'),
  ('诛仙真是经典啊!.',6,2,'2012-12-12 12:12:12')
;
SELECT *FROM comment;
DROP TABLE comment;

#创建收藏表:u_coll_bk
CREATE TABLE u_coll_bk(
  uid INT NULL ,
  bkid INT NULL ,
  CONSTRAINT u_coll_bkid_fk_uid FOREIGN KEY (uid)REFERENCES hw_user(uid),
  CONSTRAINT u_coll_bkid_fk_bkid FOREIGN KEY (bkid) REFERENCES book(bkid),
  CONSTRAINT uk_ucb UNIQUE (uid,bkid)

);

INSERT INTO u_coll_bk (uid, bkid) VALUES
  (1,2),
  (1,5),
  (1,9),
  (2,6),
  (2,9),
  (2,14),
  (3,1),
  (4,5),
  (5,14),
  (5,2),
  (5,3),
  (6,7),
  (7,7),
  (9,14),
  (13,14),
  (2,11),
  (2,13),
  (4,14),
  (7,1),
  (7,2),
  (7,3),
  (7,5),
  (7,9),
  (7,11),
  (7,14)
;
SELECT *FROM u_coll_bk;
SHOW TABLES ;

#1,查询收藏了两本书以上的用户的姓名与居住的城市
SELECT uname, loc FROM hw_user WHERE uid IN (SELECT uid FROM u_coll_bk GROUP BY uid HAVING count(uid)>2);

#2,查询年龄在三十到六十之间的用户的姓名,评论过的书籍名称
SELECT bkname FROM book WHERE bkid IN (SELECT bkid FROM comment WHERE uid IN (SELECT hw_user.uid FROM hw_user WHERE age BETWEEN 30 AND 60));
SELECT uname, uid FROM hw_user WHERE age BETWEEN 30 AND 60;
SELECT bkname FROM book WHERE bkid=(SELECT bkid FROM comment WHERE book.bkid=comment.bkid GROUP BY uid IN ( SELECT uname FROM hw_user WHERE age BETWEEN 30 AND 60));
SELECT bkname, bkid FROM book WHERE bkid IN (SELECT bkid FROM comment WHERE comment.bkid=book.bkid);
SELECT *FROM book JOIN comment ON book.bkid = comment.bkid;
SELECT *FROM comment JOIN hw_user ON comment.uid = hw_user.uid;
SELECT uname FROM hw_user WHERE uid IN (SELECT uid FROM comment WHERE bkid GROUP BY comment.uid IN (SELECT bkid FROM book WHERE bkname GROUP BY book.bkid)) AND hw_user.age BETWEEN 30 AND 60;

SELECT h.uname, b.bkname FROM hw_user h , book b WHERE h.uid IN (SELECT uid FROM comment WHERE bkid IN (SELECT bkid FROM book WHERE b.bkname=book.bkname)AND h.age BETWEEN 30 AND 60);

#3,每个用户在2016年之前的评论数量

SELECT  uname FROM hw_user  JOIN (SELECT uid,count(uid) FROM comment WHERE datetime<'2016-01-01 00:00:00' GROUP BY uid)AS new ON hw_user.uid=new.uid;

SELECT uname,uid FROM hw_user GROUP BY uid;
SELECT uid, count(uid) FROM comment WHERE datetime<'2016-01-01 00:00:00'GROUP BY uid;

SELECT h.uname, c.uid, count(c.uid) FROM hw_user h , comment c WHERE h.uid IN (SELECT uid FROM comment WHERE c.datetime<'2016-01-01 00:00:00' AND c.datetime=comment.datetime);

#4,查询刘青云评论过的书籍id及评论的次数
SELECT uid,uname FROM hw_user WHERE uname='刘青云';
SELECT bkid, count(bkid) FROM comment WHERE uid=(SELECT uid FROM hw_user WHERE uname='刘青云')GROUP BY bkid;
#5,查询收藏了书籍飞升之后的用户且年龄大于用户平均年龄5岁的用户的详细信息.
SELECT uid FROM comment WHERE bkid=(SELECT bkid FROM book WHERE bkname='飞升之后');
SELECT *FROM hw_user WHERE uid IN (SELECT uid FROM comment WHERE bkid=(SELECT bkid FROM book WHERE bkname='飞升之后'));
SELECT *FROM hw_user WHERE uid IN (SELECT uid FROM u_coll_bk WHERE bkid = (SELECT bkid FROM book WHERE bkname='飞升之后'))AND hw_user.age > (SELECT avg(age) + 5 FROM hw_user);