use BooksManager
--任务1:统计图书表中每个作者出版了多少本书
select AuthorID as 作者编号,Count(*) as 图书数量
from Books
select AuthorID as 作者编号,CategoryID as 图书类别,Avg(UnitPrice) as 平均价格
from Books
Group By AuthorID,CategoryID
SELECT CustomerID as 会员编号,Sum(Total) as 订单总金额
from Orders
where OrderDate<'2010-02-01'
Group By CustomerID
SELECT OrderID as 订单编号 ,Sum(Quantity) as 订书总量
from OrderDetails
Group By OrderID
HAVING Sum(Quantity)>=50
--任务1:统计图书表中每个作者出版了多少本书
select AuthorID as 作者编号,Count(*) as 图书数量
from Books
Group By AuthorID
注:count(表达式)返回结果集的非空行数
count(*):返回表中所有数据行的记录数。
count(列名):返回指定列非空值个数。
--任务2:统计图书表中不同作者出版的不同类别的图书的平均价格,要求显示图书平均价格在40以上的记录。select AuthorID as 作者编号,CategoryID as 图书类别,Avg(UnitPrice) as 平均价格
from Books
Group By AuthorID,CategoryID
HAVING Avg(UnitPrice)>40
注:aug为平均数值,group by 后跟<字段列数>,group by 后跟<字段列数>可为多个字段,个字段用逗号隔开,其表示按多列进行分组。
having 后跟随条件,
--任务3:统计每个会员订购日期在2010-02-01之前的订单总金额,并要求只显示订单总金额大于6000的。SELECT CustomerID as 会员编号,Sum(Total) as 订单总金额
from Orders
where OrderDate<'2010-02-01'
Group By CustomerID
HAVING Sum(Total) >6000
注:日期应加引号。
--任务4:查询每个订单的订书总量,要求只显示订书量在50本以上的,并按订书量降序排列。SELECT OrderID as 订单编号 ,Sum(Quantity) as 订书总量
from OrderDetails
Group By OrderID
HAVING Sum(Quantity)>=50
ORDER BY OrderID desc
注:desc为降序,asc为升序,distinct为去除重复。like为模糊语句查询,只适用于数据表中字段为字符类型的数据。
如查找作者表内姓王的作者信息
SELECT*from Authors
where AuthorName LIKE '王%'