SQL数据库汇总和日期

--========================================
--类型转换函数cast和convert
--========================================
--定义变量
declare @a int
--赋值
set @a = 9
--类型转换
print cast(@a as decimal(10,5))

---
declare @d datetime
set @d = '2008-3-3'
select cast(@d as varchar(20))
select convert(varchar(20),@d,20)

--字符串函数
print ascii('a')
print char(97)

select lower(au_lname + ' . '+ au_fname) from dbo.authors
select upper(au_lname + ' . '+ au_fname) from dbo.authors

select right('abcde',3)

select charindex('abc','qqabcde')

select reverse('abcdef')

select len('abcdef')

--日期函数
select getdate()

select  year('2008-3-3')
select  month('2008-3-3')
select  day('2008-3-3')

select datediff(day,'2008-3-3','2008-5-1')

--select datepart(dw,'2008-3-3')
select datename(dw,'2008-3-3')

--例：按图书类别分组统计图书的平均价格
select type,price from titles

select type,avg(price) from titles
group by type

--where 和 having
select type,avg(price) from titles
where price >19
group by type

select type,avg(price) from titles
group by type
having avg(price) >19

--汇总
--例：统计出所有书的总价格和总的预付款项
select sum(price) ,sum(advance) from titles
--明细+汇总
select title,price ,advance from titles
compute sum(price) ,sum(advance)
--分类汇总
select type,price ,advance from titles
order by type
compute sum(price) ,sum(advance) by type

 

 

---------------------------------------------------------------------------------------------