面试-MySQL(二)
文章目录
题目一
图书表(book)
主键(book_id), 书名(book_name), 作者(writer),出版社(output),价格(price)
读者表(reader)
主键(reader_id), 姓名(name),性别(sex),院系(company)
借阅表(borrow)
借阅人(reader_id), 借阅书本(book_id),借阅时间(borrow_date)
求”ABC”出版社图书的最高单价、最低单价、平均单价。
select max(price), min(price), avg(price) from book where output = "ABC";
求”信息系”当前借阅图书的读者人数
select count(distinct reader_id) as '人数' from borrow where reader_id in (
select reader_id from reader where company = "信息系"
);
求出各个出版社图书的最高价格、最低价格和总册数
select output,max(price) as 'MAX',min(price) as 'MIN',count(*) as 'NUM' from book group by output;
查询在2006这一年借过书的读者id、姓名及院系。
select * from reader where reader_id in (
select reader_id from borrow where
#方式一
borrow_date >= '2006-01-01 00:00:00' and borrow_date <= '2006-12-31 23:59:59'
#方式二
borrow_date like '2006%'
#方式三
year(borrow_date) = 2006
)
题目二
有学生表如下:
course name score
语文 小明 80
数学 小明 10
英语 小明 91
语文 小红 70
数学 小红 90
英语 小红 81
请编写一条sql语句,查询出如下格式的数据(行转列):
姓名 语文 数学 英语
小明 80 10 91
小红 70 90 81
select name as "姓名",
(select score from student where course = '语文' and name = t.name) as "语文",
(select score from student where course = '数学' and name = t.name) as "数学",
(select score from student where course = '英语' and name = t.name) as "英语"
from student t group by name;