单标查询练习题

  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%';