mysql学习：复杂查询

首先用以下语句创建表或加入数据：

create table students(id int(4) ,name varchar(20),birth date,primary key(id));
insert into students values(5,'e','1999-07-26');

create table borrows(wid int(4) ,bid int(4));
insert into borrows values(1,1);

create table books(id int(4) auto_increment,name varchar(20),primary key(id));
insert into books values(1,'aaa');
insert into books values(0,'bbb');

学生表，书籍表，借阅表分别如下：

mysql> select * from students;
+----+------+------------+
| id | name | birth      |
+----+------+------------+
|  1 | a    | 2021-09-22 |
|  2 | b    | 2000-01-02 |
|  3 | c    | 2002-04-24 |
|  4 | d    | 2021-10-27 |
|  5 | e    | 1999-07-26 |
|  6 | h    | 1999-07-26 |
|  7 | g    | 2009-12-08 |
|  8 | m    | 2000-07-21 |
+----+------+------------+
8 rows in set (0.00 sec)

mysql> select * from books;
+----+------+
| id | name |
+----+------+
|  1 | aaa  |
|  2 | bbb  |
|  3 | ccc  |
|  4 | ddd  |
|  5 | eee  |
|  6 | fff  |
+----+------+
6 rows in set (0.00 sec)

mysql> select * from borrows;
+------+------+
| wid  | bid  |
+------+------+
|    1 |    1 |
|    1 |    5 |
|    2 |    4 |
|    3 |    1 |
|    4 |    3 |
|    5 |    2 |
|    6 |    1 |
+------+------+
7 rows in set (0.00 sec)

查询借阅书籍aaa的学生：

mysql> select s.id sid,s.name sname from students s,borrows w,books b
    -> where s.id=w.wid and w.bid=b.id and b.name='aaa' group by sid;

+----+------+------------+
| id | name | birth      |
+----+------+------------+
|  1 | a    | 2021-09-22 |
|  3 | c    | 2002-04-24 |
|  6 | h    | 1999-07-26 |
+----+------+------------+
3 rows in set (0.00 sec)

mysql> select * from students where id in(select distinct wid from borrows 
where bid in(select id from books where name = 'aaa'));

+-----+-------+
| sid | sname |
+-----+-------+
|   1 | a     |
|   3 | c     |
|   6 | h     |
+-----+-------+
3 rows in set (0.00 sec)

查询没有人借阅的书：

mysql> select * from books where name  in(select distinct name from books
    -> where id not in(select bid from borrows));
    
+----+------+
| id | name |
+----+------+
|  6 | fff  |
+----+------+
1 row in set (0.00 sec)

mysql> select * from books where name not in(select b.name from borrows w, books b where w.bid=b.id);

+----+------+
| id | name |
+----+------+
|  6 | fff  |
+----+------+
1 row in set (0.00 sec)

查询和a有共同读书爱好的人：

mysql> select * from students where id in
    -> (select wid from borrows where bid in(select bid from borrows where wid=1));
    
+----+------+------------+
| id | name | birth      |
+----+------+------------+
|  1 | a    | 2021-09-22 |
|  3 | c    | 2002-04-24 |
|  6 | h    | 1999-07-26 |
+----+------+------------+
3 rows in set (0.00 sec)

查询最流行的人名：（没有最流行的=_=)

mysql> select name,count(*) cnt from students group by name
    -> order by cnt desc;
    
+------+-----+
| name | cnt |
+------+-----+
| b    |   1 |
| c    |   1 |
| d    |   1 |
| e    |   1 |
| h    |   1 |
| g    |   1 |
| m    |   1 |
| a    |   1 |
+------+-----+
8 rows in set (0.00 sec)

查询最流行的书籍：

mysql> select * from books where id in(
    -> select bid from borrows group by bid order by count(*)desc limit 1);
//达咩！老老实实用=吧

mysql> select * from books where id =(
    -> select bid from borrows group by bid order by count(*)desc limit 1);
    
+----+------+
| id | name |
+----+------+
|  1 | aaa  |
+----+------+
1 row in set (0.00 sec)