SQL小节

1 DateTime类型的字段默认为当前时间：getDate()

 2 sql总存储过程中可以加if
   if(a='')
   begin

      内容

   end

 3 修改数据库名字 exec sp_rename 'sdzbwdb','sxzbtb','database'

 4 select distinct a.id,a.name from student;

   去掉id重复的行

 5 读取数据的时候截取字段的长度：select substring(StuName,0,6) stuname from student

   或者是  select left(title,20) as titles from student从左边提取20个字符串

 6 获取某一个时间段的记录

   select count(*) from t_user where addData between '2010-11-26 00:00:00' and  '2010-11-26       

   23:59:59'

 7 update userInfo   set name=replace(name,'abv',''),如果是ntext则不支持replace，可以执行下面的语句完成update userInfo set name=replace(cast(name AS varchar(8000)),'abv','')

 8 select *,GoodsPrice*Num As Count from tb_Cart     

 9 查询两个表组合的新标的数据: select * from (select p.pub_id,t.title from pub_info as p inner

   join titles as t on p.pub_id=t.pub_id  where t.title like 'Y%') as proTable order by pub_id

   desc                 

10 主键和外键关系一对多：

alter table temp1(层表)
   Add constraint F_userId
   foreign key(userId) references T_User(主表)(id)

11 多表联合查询

    select row_number() over(order by t.registerdate desc) as num,t.occurrenceid,t.occurrencenum2,t.registerdate,b.occtype,t.AMOUNT,t.occtime,t.yr,t.zi,t.hao,t.step as step from occurrence t left join OCC_TYPE b on t.occtypeid=b.occtypeid where occurrenceid in(
select distinct occurrenceid from (
select a.occurrenceid from occ_roadloss a group by a.projid,a.occurrenceid having count(*)>1 union
select distinct occurrenceid from (select a.name,a.carno,a.occurrenceid,count(*) cnt from occ_party a group by a.name,a.carno,a.occurrenceid having count(*)>1) as proTable ) as tb)

group by：结合聚合查询分组 having相当于where作为条件查询  子查询的查时候 as 表名即可

12 要想修改自增列的值，好办，修改字段取消自增列，改好后，在恢复自增列，就ok了！