常用 sql

-------------------------------查询处理-----------------------------------

Select Name from UILogDB..sysobjects 
where xtype='u' 
and name like 'ui_visit_20%' 
order by name --查询数据库表名

select top 10 * from tablename order by rand() --随机读取10条连续的记录
select top 10 * from tablename order by NEWID() --随机读取10条不连续的记录

select * from uer_jx_test as t
order by (select sum(Pv) from uer_jx_test where City=t.City) DESC, Pv DESC -- 二次排序查询



-------------------------------表字段处理---------------------------------

exec sp_rename 'jx_count_pageid.date','Date','column'; --修改表字段名

alter table iptocountry add  Province nvarchar(2000); --增加新字段

alter table ipgroup drop column f4; --删除表字段

alter table uer_page_track alter column date nvarchar(20); --修改表字段类型

alter table uer_jx_test ADD CONSTRAINT Pk_id  primary key (id) --增加主键


-------------------------------表创建处理---------------------------------
create table jx_count_pageid
(Id int identity(1,1),
date datetime,
Page_Id nvarchar(10),
Pv int,Visits int,Visitors int,PRIMARY KEY(Id) ); --创建表

---------------------------------判断处理---------------------------------

if   object_id('tempdb..#temptable') is not null  --判断表是否存在数据库
	print 'exist' 
else 
	print'not exist'

---------------------------------转换格式---------------------------------

convert(varchar(10),字段 or 字符串,120) --转换成smalldate 年-月-日 型


---------------------------------创建索引---------------------------------

create index ui_visit_index_20100301 on  ui_visit_20100301(user_id)   --创建索引

DROP INDEX ui_visit_index_20100301 ON ui_visit_20100301;              --删除索引



---------------------------------常用函数---------------------------------

SELECT DATEADD(mm,DATEDIFF(mm,0,'20100405'),0)                        --查询每月一天


-----------------------------------排序-----------------------------------

order by isnumeric(name)

--------------------------------系统存储过程------------------------------

declare @sql nvarchar(500)                                            --通过系统存储过程返回统计数
declare @char nvarchar(200)
declare @TOTAL_COUNT int

set @char='uer_search_key_20101201'

           set @sql = 'select @count = count(*) from '+@char
    execute sp_executesql  
          @sql2, 
          N'@count int output', 
          @TOTAL_COUNT output 

--------------------------------查询表空间------------------------------

exec sp_spaceused 'dbo.ui_visit_ex_20110619'