--========================================--类型转换函数cast和convert--========================================--定义变量declare @a int--赋值set @a = 9--类型转换print cast(@a as decimal(10,5))
---declare @d datetimeset @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.authorsselect 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 titlesgroup by type
--where 和 havingselect type,avg(price) from titleswhere price >19group by type
select type,avg(price) from titlesgroup by typehaving avg(price) >19
--汇总--例：统计出所有书的总价格和总的预付款项select sum(price) ,sum(advance) from titles--明细+汇总select title,price ,advance from titlescompute sum(price) ,sum(advance)--分类汇总select type,price ,advance from titlesorder by typecompute sum(price) ,sum(advance) by type
---------------------------------------------------------------------------------------------
