学习链接：https://blog.csdn.net/qq_43119297/article/details/82559687

1：创建book表，并插入数据

CREATE TABLE book( bookID VARCHAR(20) PRIMARY KEY , bookName VARCHAR(50) DEFAULT NULL );INSERT INTO book VALUES(1,‘java基础‘),(2,‘javaweb‘),(3,‘JDBC‘),(4,‘HTML高级‘);

2:创建loan表

CREATE TABLE loan( uid INT, bookID VARCHAR(32), lnum INT, PRIMARY KEY(uid,bookID) );INSERT INTO loan VALUES(1,1,8),(2,4,2),(3,3,3),(4,2,7),(5,1,1),(2,3,10),(2,2,3),(3,1,5);

3.创建user表，并插入数据

 CREATE TABLE USER( uid INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(20) UNIQUE, age INT, sex VARCHAR(2) );INSERT INTO USER VALUES (NULL,‘周瑜‘,22,‘男‘),(NULL,‘小乔‘,18,‘女‘),(NULL,‘甄宓‘,23,‘女‘),(NULL,‘曹操‘,30,‘男‘),(NULL,‘貂蝉‘,26,‘女‘); 

4，问题

– 1.查询年龄小于 25的学生姓名、学生年龄– 2.查询年龄在18-22之间（包含18和22）的学生信息–3. 统计学生表中男女的数量分别是多少– 4.查询学生的总人数、平均年龄、最小年龄– 5.查询借阅过图书的每个学生ID借阅的总次数– 6.查询图书名称包含”java”的图书数量–7. 查询借阅总次数大于50的学生学号–8. 查询借阅了’HTML高级’的学生学号–9. 查询没有借阅过图书的学生学号、学生姓名– 10.查询借阅过’javaWeb’图书的学生学号、借阅次数– 11.查询年龄比”周瑜”大的所有学生姓名、学生年龄– 12.查询年龄最大的学生ID、学生姓名，学生年龄–13. 查询借阅了图书的学生姓名、借阅的不同图书总数、借阅所有图书总次–14. 查询被借阅的每本图书的图书名称、借阅总次数– 15.查询借阅次数最多的图书ID和借阅的总次数--------------------- 

5，答案

1：SELECT username,ageFROM USERWHERE age<25;2：SELECT username,ageFROM USERWHERE age BETWEEN 18 AND 22;3：SELECT sex,COUNT(username) 数量FROM USERGROUP BY sex;4：SELECT COUNT(uid) 总人数,AVG(age) 平均年龄,MIN(age) 最小年龄FROM USER;5：SELECT uid,COUNT(lnum)借阅次数FROM loanGROUP BY uid;6：SELECT SUM(b.`lnum`) 总数FROM book a,loan bAND bookname LIKE "java%";7：SELECT uidFROM loanGROUP BY uid HAVING SUM(lnum)>5;8： SELECT user.`uid` FROM book,USER,loan WHERE loan.`bookID`=book.`bookID` AND loan.`uid` =user.`uid` AND book.`bookName`=‘HTML高级‘;9： SELECT user.`uid`,user.`username` FROM book,USER,loan WHERE loan.`bookID`=book.`bookID` AND loan.`uid` =user.`uid` AND book.`bookName`NOT IN (‘HTML高级‘,‘JDBC‘,‘javaweb‘,‘java基础‘);--------------------- 10：SELECT user.`uid`,SUM(loan.`lnum`) 次数 FROM book,USER,loan WHERE loan.`bookID`=book.`bookID` AND loan.`uid` =user.`uid` AND book.`bookName`=‘javaWeb‘ GROUP BY book.`bookName`;11： SELECT username,ageWHERE username=‘周瑜‘) t WHERE a.`age`>t.b;12：SELECT uid, username,age FROM USER a , (SELECT MAX(age) b FROM USER ) t WHERE a.`age`=t.b;13： SELECT a.`username`,s.e 借阅的不同图书总数,s.b 借阅所有图书总次 FROM USER a,(SELECT d.`uid` k,SUM(d.`lnum`) e , COUNT(d.`bookID`) b FROM loan d GROUP BY uid ) s WHERE a.`uid`=s.k;14： SELECT book.bookname 书名,n.v 借阅次数 FROM book,(SELECT bookid s,SUM(lnum) v FROM loan GROUP BY bookid) n WHERE n.s=book.`bookID`;15：SELECT bookID,MAX(n.a) FROM (SELECT bookid,SUM(lnum) a FROM loan GROUP BY bookid) n;