常用Sql语句

1.Sql插入语句得到自动生成的递增ID值

   insert into Table1(Name,des,num) values('lp','base',10)

   select @@identity as 'ID'

2.实现是1或0显示为男或女

   select name,sex=

　　case Sex

　　　　when '1' then '男'

　　　　when '0' then '女'

　　end

　from Tablename

3.嵌套子查询

　select a,b,c from Table1 where a In (select a from Table2)

4.显示文章、提交人和最后回复时间

   select a.title,a.username,b.adddate from tablename a,(select max(adddate) adddate from tablename  where tablename.title = a.title)b

5.随机提取记录

　select top 10 * from tablename order by nameId()

6.在同一表内找相同属性的记录

   select userId form Accounts_users where UserName is not null group by userId having count(*)>1

7.查询类别所有的产品对应数据

   select categoryName,ProductName from categories left join Products on categories.categoryId = products.categoryId

8.按范围查询编号在2到5之间的用户信息

   select * from UserValue where UserId between 2 and 5

9.日程安排提前5分钟提醒

   select * from Table where datediff(minute,getdate(),开始时间) < 5

10.得出某日期所在月份的最大天数

　select DAY (dateadd(dd,-DAY('2008-02-13'),dateAdd(mm,1,'2008-02-13'))) as 'DayNumber'

11.按姓氏笔画排序

  select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as

12.模糊查询

  select * from tablename where column1 like '[A-M]%' 首字母在A-M之间的记录

  select * from tablename where column1 like '[^C]%'  首字母不为C的记录

13.复制表结构

　select * into b from a where a where 1<>1

   或select top 0 * into [b] from [a]

14.复制表数据

    insert into b (name,des,num) select name,des num form A

15.编辑一个列

  增加列:  alter table Table1 add username varchar(30) not null default ' '

  修改列:  alter table Table1 alter column username varchar(40)

  删除列:  alter table Table1 drop column username

16.修改时间字段的小时部分（23点修改21点）

　update Ad_Browse_200611-1 set browstime = '2006-11-01 21'+substring(convert(varchar(30),browsetime,8),3,7) where DatePart(Hour,browsetime) = 23