（持续更新）实用数据库语句

[$BW0U i
|+u0 

:H(M-e@Et)?01、查询大于某个时间的数据，需要转换时间格式

select * from myeglobal.dbo.t_order where  51Testing软件测试网M#]z"?bw2Y?
         orderdate > convert(datetime,'2007-08-13 ')

c/moV*Y h6w1N02、按月查询订单的总数

select count (*)  as '5月' from t_order where month(OrderDate)=5 and year(OrderDate)=2007

3、按月统计2007年的所有订单51Testing软件测试网8E'@:C5G;h$W#O

select months,count(1) from (select  distinct month(orderdate)as months from t_order) as t151Testing软件测试网Io,@W'y6M+T/S
left join  t_order 51Testing软件测试网WCB"bUF,B/f
on month(t_order.orderdate)=months
i(T QXqyU0f0where year(orderdate)=200751Testing软件测试网2oP"` b
^.m,Z;If
group by months51Testing软件测试网8o/s9G;^C P"?

4、更新订单，用到有两个函数：dateadd()  、getdate()

update myeglobal.dbo.t_order set ōrderstatus=0 ,finishdate = null,[delete]=null,
K'E~P/b$ps(a i0  ōrderdate=dateadd(day,-15,getdate()) where ōrderid='070824000900'
/x2M%eU;j#w4c0

b%If1yoMk+V~05、删除订单的ordernote51Testing软件测试网xpGm
kUs"l

-\ sW;GB)d
l0IF EXISTS(select * from myeglobal.dbo.t_ordernote where ōrderid='070824000900')51Testing软件测试网t`)^O@2E+p b
BEGIN 51Testing软件测试网4U't3f2nuiFQ4B~
 delete from myeglobal.dbo.t_ordernote where ōrderid='070824000900'51Testing软件测试网 P*[ e-|%]JI~?x
END

[yb1sJ@ uql06、replace函数51Testing软件测试网{'\$H*C4T-[3SJ6m

0Aoyi7k7]0update dbo.T_HomeShowProduct51Testing软件测试网a&x O#Eic
set ImagePath=replace(ImagePath,'www.myeglobal.com','deploy:91')

9F[
I;qTC"_07、全文索引 contains() 的应用51Testing软件测试网*zh/e"\Z3T1p

%Wb hfFb/U0select top 10 * from t_users where  contains(username,'"*seller*"') order by username desc；51Testing软件测试网G4XWF(T
使用like查询的话等价于：51Testing软件测试网]:w$L:q1{
select top 10 * from t_users where  username like 'seller%'  order  by username desc ;51Testing软件测试网jQ]!J8G:UB1z

8、查询有10个以上的订单并且总额达到10000的商店
8P4m0V,teP5BG!H0select shopid,totalnumber=count(*),totalprice=sum([sum]) from t_order 51Testing软件测试网 AU&e(g
[y)E4?-~
  group by shopid having count(*)>10 and sum([sum])>10000 order by shopid 51Testing软件测试网&Km IZA M
此表中的sum字段要加上中括号以跟关键字区别开来，group by 和 order by 同时使用，having 的使用