实验三数据查询语言 一、基本查询
使用相应SQL语句，完成如下操作： （1）查询所有用户的用户ID和姓名。 select uid,name from [user]
（2）查询注年龄最小3位用户的用户ID,姓名和年龄。
select top 3(uid),name,datediff(yyyy,birthday,getdate()) age from [user]
order by birthday desc
（3）查询库存小于50本的所有书目信息。 select* from book where stock<50
（4）查询清华大学出版社的所有书目信息。 select* from book
where press='清华大学出版社'
（5）查询价格在50-100元的所有的书名。 select title from book
where price>50 and price<100
（6）查询姓“张”或“王”或“李”且单名的学生的情况。 select* from [user]
where name like'张%'or name like'李%'or name like'王%'and len(name)=2
（7）查询所有书目的书名、出版社及价格，要求出版社升序排列，
出版社相同时，按价格从高到底进行排序。
select title,press,price from book
order by press,price desc
（8）查询所有有交易记录的用户ID。 select oid from [order] where state= 4 二、数据汇总
使用相应SQL语句，完成如下操作：
（1）查询理工类书目的最高价格、最低价格及平均价格。 select
max(price)max_price,min(price)min_price,avg(price)avg_price from book
where category = 1
（2）查询所有理工类书目的库存总量。 select sum(stock) from book
where category = 1
（3）查询‘1001’号图书被订购的总次数。 select sum(quantity) from orderbook where bookid = 1001
（4）查询不同状态订单的数量。 select state,count(state) from [order] group by state
（5）查询各类别数据的库存总量。 select sum(stock) from book
group by category
（6）查询被订购2次以上（含2次）的图书编号、订购次数，并按照订购次数从高到低进行排序。
select bookid,sum(quantity) from orderbook group by bookid having count(*)>=2 order by sum(quantity)desc 三、连接查询
使用相应SQL语句，完成如下操作： （1）列出全部用户的用户ID、姓名和状态。 select uid,name,userstate from [user],userstate where
[user].state
=
http://www..com/doc/8114624627.html,id
（2）查询购买过‘1001’号图书的用户名、性别及购买时间，并按照购买时间降序排列。
select name,sex,ordertime from [user],[order],orderbook where [user].uid
order by ordertime desc
（3）查询性别为‘男’且购买过社科类图书的用户ID、用户名及状态。
select uid,name,sex,userstate from [user],userstate where [user].uid in (
select [user]
orderbook.bookid
='1001'and
[order].oid
=
orderbook.orderid and [order].state ='4'and [order].[user] =
from [order]
where state='4'and [order].oid in (select orderid from orderbook where bookid in (select bid from book
where category ='2' )))and 或者：
select uid,name,sex,userstate
from [user],userstate,book,orderbook,[order] where [user].sex ='1' and book.category ='2' and [order].state ='4'
and orderbook.bookid = book.bid and orderbook.orderid =[order].oid
and [user].state = http://www..com/doc/8114624627.html,id and [order].[user] = [user].uid
（4）查询价格在37元以上（含37元）且被购买过2次以上的图书名称、价格、出版社及购买次数，并按照购买次数降序排列。
写不出
（5）查询用户ID为‘102’的所有订单号、下单日期及状态。 select oid,[user],ordertime,orderstate from [order],orderstate where [order].[user] ='102' and [order].state = orderstate.osid
（6）查询订单状态为已付款（未完成）的所有订单的订单号、下单用户、书目名称、书目类别、数量信息，并按照订单号排序。
[user].sex
='1'and
[user].state
=
http://www..com/doc/8114624627.html,id
select oid,name,title,category.category,stock from [order],orderbook,[user],book,category where [order].state ='3' and [order].[user] = [user].uid and orderbook.orderid = [order].oid and orderbook.bookid = book.bid and book.category = category.cid order by oid
（看到付款未完成的就一张订单，需要排序？） 四、子查询
（1）查询订购次数在平均次数以上的图书编号、图书名称、图书价格及订购次数，并按订购次数排序。
（2）查询至少包含‘2014003’号订单包含的书目的订单号、下单用户、下单日期及
订单状态。
select distinct orderid,ordertime,name,orderstate from orderbook,[order],[user],orderstate where orderbook.bookid in(
select bookid from orderbook
where orderbook.orderid ='2014004' )
and [order].[user] =[user].uid and orderstate.osid = [user].state and orderbook.orderid = [order].oid
（3）查询购买过清华大学出版社的书籍的所有人的信息。select distinct [user].*
from [user],[order] where [order].oid in (
select orderid
from book,orderbook
where book.press ='清华大学出版社' and book.bid = orderbook.bookid )
and [order].[user] = [user].uid
（4）查询与王丽购买过同种书籍的所有人的信息。Select* from [user] where uid in (select [user] from [order] where oid in (select orderid from orderbook where bookid in( select bookid
from [user],[order],orderbook where [user].name ='王丽' and [user].uid = [order].[user] and [orderbook].orderid = [order].oid )));
（5）找出每个客户超过他购买的书的平均价格的书目信息。 （6）查询购买张三购买了的全部书籍的客户信息。select [user].*
from [user] where uid in (select [user] from [order] where oid in (select orderid from orderbook
where bookid in (select bookid from orderbook where orderid in (Select oid from [order] where [user] in (Select uid from [user]
where name='张三'))))); 五、集合查询
（1）查询性别为‘男’且购买过社科类图书或性别为‘女’且购买过理工类图书的用户ID、用户名及状态。
（2）查询性别为‘男’且购买过社科类图书的用户ID、用户名及状态。Select distinct uid,name,state
from [user],category,book where sex=1
and category.category ='社会科学类' and category.cid = book.category
（3）查询购买过社科类图书但不包含下单次数为1次的的用户ID、用户名及状态。
Select distinct uid,name,state from [user],category,book,[order], where category.category ='社会科学类' and category.cid = book.category group by [order].user having count(*)>=2
因篇幅问题不能全部显示，请点此查看更多更全内容