MySQL单表查询练习题

单标查询练习题

1. 查询XSBOOK数据库的XS表中计算机专业同学的借书证号，姓名和借书量。
2. 查询XS表中的所有列。
3. 查询XS表中计算机系同学的借书证号、姓名和借书量，结果中各列的标题分别指定为cardno、name和cnt。
4. 列出XS表中的借书证号、姓名及10减去借书量的还能借书数。
5. 对JY表只选择借书证号列，消除结果集中的重复行。
6. 从BOOK表中查找前3行的书名、价格。
7. 查询XSBOOK数据库XS表中借书数在三本以上的学生的借书证号，姓名，专业名。
8. 查询XSBOOK数据库XS表中出生时间在1982年8月8日以后出生的学生信息。
9. 查询BOOK表中“人民邮电出版社”的书名、价格打8折后将列名改为“新价格”。
   10.查询XSBOOK数据库XS表中计算机专业、借书量在五本以下的学生姓名和借书证号。
   11.查询BOOK表中不是人民邮电出版社也不是清华出版社的书的ISBN、书名和作者。
   12.查询XSBOOK数据库XS表中出生时间在1980-6-1与1982-12-31之间的学生的借书证号，姓名,性别,出生时间。
   13.查询BOOK表中价格在20-50之间且库存量不在2-5之间的图书书名,价格,库存量，复本量。
   14.查询XSBOOK数据库XS表中专业名为‘计算机’‘信息工程’‘英语’‘自动化’的学生的情况。
   15.查询XSBOOK数据库XS表中借书证号不是‘10000001’‘10000002’，且借书量在2和5之间的学生信息。
   16.查询XSBOOK数据库XS表中姓‘王’且单名的学生情况。
   17.查询XSBOOK数据库XS表中名字的第2个字为‘小’的学生情况。
   18.查询XSBOOK数据库XS表中姓名以李开头，第二个字符是小或宏的学生信息。
   19.查询XSBOOK数据库XS表中专业名尚不定的学生情况。
   20.将XS表中的计算机专业学生按出生时间先后进行升序排列。
   21.将XS表中的计算机专业学生按借书量降序排列。
   22.查询BOOK表中价格最高的前三本书的书名、出版社和价格。
   23.查询BOOK表中“机械工业出版社”的书的书名，价格，库存量，按价格进行降序排列，价格相等的按库存量升序排列。
   24.统计(查询)XS表中的计算机专业学生借书的平均数，最大借书数，最小借书数。
   25.统计(查询)book表中各出版社的图书总册数与库存总册数。
   26.统计(查询)XS表中各专业的学生人数。
   27.统计各专业的借书量，显示专业名和总借书量，并按专业名降序排序。
   28.查询每本图书的ISBN以及相应的借该书人数。
   29.查询XS表中每个专业的男生人数，女生人数、总人数及学生总人数。
   30.查询XS表中男生数大于等于2的专业名，学生人数。
   31.查询XS表中男生数或女生数不少于2的专业名以及学生人数。
   32.查询从BOOK表中，按“出版社”分组，查询该出版社书的平均价格和总复本数，并按“总复本数”升序显示，平均价格小于50。
   33.查询BOOK表中书名包含WEB的书名，出版社，作者，价格。
   以下为上面练习题的答案

select xs_bookn,xs_name,xs_num from xs;

SHOW columns FROM xs;

select xs_bookn as cardo,xs_name as `name`,xs_num as cnt  from xs where xs_object = "计算机科学与技术"

select xs_bookn,xs_name,(10-xs_num) as '还能借书数' from xs

select distinct isbm from jy;

select * from book LIMIT 3;

select xs_bookn,xs_name,xs_object from xs where xs_num>3;

select * from xs where UNIX_TIMESTAMP(xs_brdate) > UNIX_TIMESTAMP('1982-08-08')

select book_name,(book_price * 0.8) as '新价格' from book ;

select xs_bookn,xs_name from xs where xs_object = "计算机科学与技术" and xs_num < 5;

select book_isbn,book_name,book_body from book where book_chuban <> "人民邮电出版社";

select * from xs where unix_timestamp(xs_brdate) >unix_timestamp('1980-06-01') and unix_timestamp(xs_brdate) < unix_timestamp('1982-12-31')
select * from xs where unix_timestamp(xs_brdate) between unix_timestamp('1980-06-01') and unix_timestamp('1982-12-31')

select book_name,book_price,book_repertory,book_copy from book where  book_price between 20 and 50 and book_repertory not between 2 and 5

select * from xs where xs_object in ('计算机','信息工程','英语','自动化');

select * from xs where xs_bookn not in ('10000001','10000002') and xs_num between 2 and 5;

select * from xs where xs_name like "王%";

select * from xs where xs_name like "_小_";

select * from xs where xs_name like "李宏%" || xs_name like "李小%" ;

select * from xs where xs_object = "";
select * from xs where xs_boject is null;

select * from xs order by xs_num desc;

select max(book_price)as '最高价格',book_name,book_price,book_chuban from book ;

select book_name,book_price,book_repertory from book
where book_chuban = "机械工业出版社" order by book_price desc , book_repertory asc;

select (round(sum(xs_num)/count(xs_name)))as '学生借书平均数',max(xs_num) as '最大借书量',min(xs_num) as '最小借书量'
from xs where xs_object = "计算机";

select sum(book_repertory) as '各出版社的库存数',book_chuban as '出版社' from book group by book_chuban;

select count(xs_name) as "各专业的人数",xs_object as '专业' from xs group by xs_object; 

select sum(xs_num) as '各专业借书量',xs_object as '专业名'  from xs group by xs_object DESC;

select count(isbm) as '借书数',isbm as '图书编码' from jy inner join book on jy.isbm = book.book_isbn group by isbm;

select count(*) as '人数',xs_sex as '性别',xs_object as '专业' from xs group by xs_object,xs_sex;
/*30 查询XS表中男生数大于等于2的专业名，学生人数*/
select count(*)  as '人数',xs_object from xs where xs_sex = '男' group by xs_object

/* 31.查询XS表中男生数或女生数不少于2的专业名以及学生人数*/

/*32.查询从BOOK表中，按“出版社”分组，查询该出版社书的平均价格和总复本数，并按“总复本数”升序显示，平均价格小于50。*/

select book_chuban,book_name,book_body,book_price from book where book_name like '%WEB%';