MySQL-实验-单表、多表数据查询和嵌套查询

目录

目录

0.简单子查询

（1）带比较运算符的子查询

（2）关键字子查询

1.多表查询

2.多表查询及统计分组

3.子查询

4.多表子查询

---

0.简单子查询

（1）带比较运算符的子查询

在右侧编辑器补充代码，查询大于所有平均年龄的员工姓名与年龄。

我们为你提供了tb_emp表，数据如下：

id	name	age
1	Mary	23
2	Allen	21
3	kevin	25
4	Tom	33
5	Nancy	28

select name,age from tb_emp where age>=
(select avg(age) from tb_emp);

（2）关键字子查询

 我们为你提供了如下数据表： tb_salary表数据：

id	position	salary
1	Java	8000
2	Java	8400
3	Java	9000
4	Python	6500
5	Python	10000

根据提供的数据，在右侧编辑器中补充代码：

1. 查询薪资表中比Java最高工资高的所有员工职位名称和薪资；

select `position`,salary 
from tb_salary 
where salary>ALL(select salary from tb_salary where `position`='Java'); 

1. 查询薪资表中比Java最低工资高的所有员工职位名称和薪资；

select `position`,salary 
from tb_salary 
where salary>ANY(select salary from tb_salary where `position`='Java');

1. 查询薪资表中职位为Java的所有员工职位名称和薪资。

select `position`,salary from tb_salary where `position`='Java';

1.多表查询

打开library数据库

第一题 根据读者(reader)和借阅(borrow)数据表，查询王颖珊的借阅记录，

包括条形码txm、借阅日期jyrq、还书日期hsrq

select txm,jyrq,hsrq 
from reader,borrow 
where reader.dzzh=borrow.dzzh and reader.xm='王颖珊';

第二题  根据图书(book)和借阅(borrow)数据表，查询李白全集被借阅的情况：

包括读者证号dzzh、借阅日期jyrq、还书日期hsrq

select dzzh,jyrq,hsrq 
from book,borrow 
where book.txm=borrow.txm and book.sm='李白全集';

第三题 根据读者(reader)、图书(book)和借阅(borrow)数据表查询没有被归还的借阅信息：

包括读者证号dzzh、姓名xm、电话dhhm、条形码txm、书名sm、借阅日期jyrq

提示：通过isnull(表达式)可以判断表达式是否NULL值

select reader.dzzh,xm,dhhm,book.txm,sm,jyrq 
from reader,book,borrow 
where reader.dzzh=borrow.dzzh and book.txm=borrow.txm and isnull(hsrq);

2.多表查询及统计分组

根据数据表图书、读者和借阅，实现多表查询及对查询分组统计

为了完成本关任务，你需要掌握：

第一题  统计每本书借阅的次数,显示书名和借阅次数（借阅次数命名为jycs),按借阅次数降序排列,借阅次数相同的按书名降序排列

（提示：borrow数据表的一条数据对应一次借阅）

-- 方法1
select sm,jycs 
from book inner join 
     (select txm,count(jyrq)jycs from borrow group by txm)a 
on book.txm=a.txm order by jycs desc,sm desc;

-- 方法2
select sm,count(sm)as jycs 
from borrow left join book on book.txm=borrow.txm 
group by sm 
order by jycs desc,sm desc;

第二题  统计借阅次数在2次以上的图书的借阅的次数,显示书名和借阅次数，按借阅次数降序排列,借阅次数相同的按书名降序排列

-- 方法1
select sm,jycs from book inner join
    (select txm,count(jyrq)jycs from borrow group by txm)a
on book.txm=a.txm and jycs>=2  order by jycs desc,sm desc;

-- 方法2
select sm,count(sm) as jycs  from borrow left join book on book.txm=borrow.txm 
group by sm having(jycs>=2)
order by jycs desc,sm desc;

第三题  统计每个出版社的图书的借阅次数,显示出版社的名称和借阅次数，按借阅次数降序排列,借阅次数相同的按出版社降序排列

-- 方法1
select cbs,count(cbs)jycs 
from borrow left join book
on book.txm=borrow.txm 
group by cbs
order by jycs desc,cbs desc;

--方法2
select cbs,count(cbs) as jycs 
from borrow left join book on book.txm=borrow.txm 
group by cbs
order by jycs desc,cbs desc;

第四题  统计每位读者借阅的次数,显示姓名和借阅次数,按借阅次数降序排列，借阅次数相同的按姓名降序排列

-- 方法1
select  xm,jycs from reader inner join
(select dzzh,count(jyrq)jycs from borrow group by dzzh)a
on reader.dzzh=a.dzzh order by jycs desc,xm desc;

-- 方法2
select xm,count(xm)jycs from borrow left join reader on borrow.dzzh=reader.dzzh 
group by xm
order by jycs desc,xm desc;

第五题  统计研究生读者借阅的次数,显示姓名和借阅次数，按借阅次数降序排列，借阅次数相同的按姓名降序排列

-- 方法1 
select xm,jycs from reader inner join
 (select dzzh,count(jyrq)jycs from borrow group by dzzh)a 
 on reader.dzzh=a.dzzh and sf='研究生' order by jycs desc,xm desc;

-- 方法2
select xm,count(xm) as jycs 
from borrow left join reader on borrow.dzzh=reader.dzzh 
where sf='研究生'
group by xm
order by jycs desc,xm desc;

注意：order by <表达式1>,<表达式2> 表示首先按第一个表达式的值排序，第一个表达式的值相同的再按第二个表达式的值排序

3.子查询

第一题 查询与李白全集同一个出版社的图书的书名（不包括李白全集）

-- 方法1
select sm from book 
where cbs=(select cbs from book where sm='李白全集') and sm<>'李白全集';

-- 方法2
select sm from book where cbs="上海古籍出版社" and sm!="李白全集";

第二题 查询高于图书的平均售价(sj)的图书的书名和售价

select sm,sj from book where sj>=(select avg(sj) from book);

第三题 查询售价最高的图书的条形码、书名和售价

select txm,sm,sj from book where sj=(select max(sj) from book);

第四题 查询售价最低的图书的条形码、书名和售价

select txm,sm,sj from book where sj=(select min(sj) from book);

4.多表子查询

第一题 查询曾经借过图书的读者的读者证号和姓名

-- 方法1
select distinct dzzh,xm 
from reader inner join borrow using(dzzh) order by dzzh;

-- 方法2
select dzzh,xm from reader where reader.dzzh in (select dzzh from borrow);

第二题 查询曾经没有被借阅的图书的条形码和书名

select txm,sm from book where txm not in(select txm from borrow);

第三题 查询与孙思旺借过相同图书的读者的读者证号和姓名，按读者证号升序排列

-- 方法1
select distinct reader.dzzh,xm 
from reader,borrow 
where reader.dzzh=borrow.dzzh and xm<>'孙思旺' and txm in
    (select txm from reader,borrow 
    where reader.dzzh=borrow.dzzh and xm='孙思旺');


-- 方法2
select dzzh,xm 
from reader 
where reader.dzzh in 
    (select dzzh from borrow 
    where txm in (select txm from borrow 
                  where borrow.dzzh=(select dzzh from reader 
                                     where xm='孙思旺')
                  )
     ) 
and dzzh!='006' order by dzzh asc;

第四题 查询借阅过李白全集的读者所借过的其他图书的书名 按书名升序排列

-- 方法1
select distinct sm 
from book,borrow 
where book.txm=borrow.txm and sm<>'李白全集' and dzzh in 
     (select dzzh from book,borrow where sm='李白全集' and book.txm=borrow.txm)
order by sm;

-- 方法2
select sm from book 
where book.txm in 
    (select txm from borrow
     where borrow.dzzh in (select dzzh from borrow 
                           where borrow.txm=(select txm from book 
                                             where sm="李白全集")
                           )
     )
and sm!="李白全集" order by sm asc;