SQL经典题目

SHOW DATABASES ;
USE jdbc;
DROP DATABASE jdbc;
CREATE DATABASE jdbc CHARACTER SET utf8;
DROP TABLE stuscore;
CREATE table stuscore(
name VARCHAR(20),
subject VARCHAR(20),
score INT,
stuid INT

);
INSERT into stuscore VALUES
(‘张三’,’数学’,89,1),
(‘张三’,’语文’,80,1),
(‘张三’,’英语’,70,1),
(‘李四’,’数学’,90,2),
(‘李四’,’语文’,70,2),
(‘李四’,’英语’,80,2);

SELECT *FROM stuscore;

A1

SELECT stuid ,name,avg(score) FROM stuscore GROUP BY stuid;

A2

SELECT stuid ,name ,subject,score FROM stuscore GROUP BY subject
ORDER BY score ASC ;

A3

SELECT subject,avg(score)FROM stuscore GROUP BY subject;

B1

B2

SELECT subject ,a.score bujige ,b.score liang,c.score you FROM stuscore s,
stuscore a,stuscore b,stuscore c WHERE a.score<60 GROUP BY a.subject AND
60 <= b.score<=80 GROUP BY b.subject AND
c.score>= 81 GROUP BY c.s;

CREATE table stuscore1(
name VARCHAR(20),
kecheng VARCHAR(20),
fenshu INT
);

INSERT into stuscore1 VALUES
(‘张三’,’语文’,81),
(‘张三’,’数学’,75),
(‘李四’,’语文’,76),
(‘李四’,’数学’,90),
(‘王五’,’语文’,81),
(‘王五’,’数学’,100),
(‘王五’,’英语’,90);
SELECT * FROM stuscore1;

SELECT name FROM stuscore1 WHERE fenshu > 80 GROUP BY name ;

SELECT database();
SHOW DATABASES ;
DROP DATABASE jdbc;
USE sys;
CREATE TABLE hw_user(
uid INT PRIMARY KEY AUTO_INCREMENT,
uname VARCHAR(50) NOT NULL,
age INT,
loc CHAR(50)
);
INSERT INTO hw_user VALUES
(NULL, ‘李小龙’, 51, ‘北极’),
(NULL, ‘张家辉’, 52, ‘上海’),
(NULL, ‘周润发’, 62, ‘广州’),
(NULL, ‘梁家辉’, 63, ‘深圳’),
(NULL, ‘刘青云’, 48, ‘沈阳’),
(NULL, ‘古天乐’, 39, ‘杭州’),
(NULL, ‘张丰毅’, 28, ‘杭州’),
(NULL, ‘梁朝伟’, 46, ‘北京’),
(NULL, ‘陈道明’, 58, ‘北京’),
(NULL, ‘张国荣’, 47, ‘香港’),
(NULL, ‘黄渤’, 27, ‘北京’),
(NULL, ‘吴镇宇’, 38, ‘香港’),
(NULL, ‘周星驰’, 38, ‘香港’);
ALTER TABLE hw_user CHANGE loc loc VARCHAR(50)
CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT ”;

SELECT *FROM hw_user;
CREATE TABLE type1 (
tid INT,
tname VARCHAR(50)
);
INSERT INTO type1 VALUES
(NULL, ‘言情’),
(NULL, ‘恐怖’),
(NULL, ‘玄幻’),
(NULL, ‘穿越’),
(NULL, ‘异界’),
(NULL, ‘校园’),
(NULL, ‘仙侠’),
(NULL, ‘武侠’),
(NULL, ‘网游’),
(NULL, ‘军事’);
ALTER TABLE type1 CHANGE tname tname VARCHAR(50)
CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT ”;
SELECT *FROM type1;

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

CREATE TABLE comment (
comid INT,
content VARCHAR(50),
bkid INT,
uid INT,
dateltime DATE
);

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

ALTER TABLE comment CHANGE content content VARCHAR(50)
CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT ”;

SELECT *FROM comment;

CREATE TABLE u_coll_bkid (
uid INT,
bkid INT
);
INSERT INTO u_coll_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_bkid;

1,查询收藏了两本书以上的用户的姓名与居住的城市.

SELECT uname,loc FROM hw_user WHERE uid IN (SELECT uid FROM u_coll_bkid GROUP BY uid HAVING count(u_coll_bkid.uid) >=2);

2,查询年龄在三十到六十之间的用户的姓名,评论过的书籍名称

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

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

SELECT hw_user.uname ,cu FROM (SELECT uid,count(uid) cu FROM comment WHERE dateltime<’2016-01-01 00:00:00’
GROUP BY uid) AS rdt INNER JOIN hw_user ON hw_user.uid = rdt.uid;

4,查询刘青云评论过的书籍id及评论的次数

SELECT bkid,count(content) FROM comment WHERE uid = 5 GROUP BY bkid;

5,查询收藏了书籍飞升之后的用户且年龄大于用户平均年龄5岁的用户的详细信息.

SELECT * FROM hw_user WHERE uid IN (SELECT uid FROM u_coll_bkid WHERE bkid
= (SELECT bkid FROM book2 WHERE bkname = ‘飞升之后’)) AND uid
IN (SELECT uid FROM hw_user WHERE age > (SELECT avg(age)+5 FROM hw_user));