常用SQL语句记录

1. 根据现有表架构创建相同的新表架构, 将[mobileTaste  ]表的结构导成一个新表[taste]，新表数据全空

--将[mobileTaste  ]表的结构导成一个新表[taste]，新表数据全空
select 	tastID, tasteName, orderBy, flag, remark into taste from dbo.mobileTaste  where 1=2

--mysql 根据已有的表创建新表
create table taste as select * from mobilestate where 1=-1

2. 使用临时表, 进行大数据查询

--借助临时表, 作为中转来进行大数据查询
DROP table #TB
SELECT * INTO #TB FROM OrderInfo

select * from #TB

3. 获取汉字拼音首字母的函数

create function [dbo].[fn_ChineseToSpell](@strChinese varchar(500)='') 
returns varchar(500) 
as 
begin /*函数开始*/ 
     declare @strLen int,@return varchar(500),@i int 
     declare @n int,@c char(1),@chn nchar(1)  
     select @strLen=len(@strChinese),@return='',@i=0 
     while @i<@strLen 
     begin /*while循环开始*/
             select @i=@i+1,@n=63,@chn=substring(@strChinese,@i,1) 
             if @chn>'z'/*原理:“字符串排序以及ASCII码表”*/                
                 select @n = @n +1,@c =case chn when @chn then char(@n) else @c end from(select top 27 * from (select chn = '吖' union all select '八' union all select '嚓' union all select '咑' union all select '妸'  union all select '发'  union all select '旮'  union all select '铪'  union all select '丌' /*because have no 'i'*/ union all select '丌' union all select '咔' union all select '垃' union all select '嘸' union all select '拏' union all select '噢' union all select '妑' union all select '七' union all select '呥' union all select '仨' union all select '他' union all select '屲' /*no 'u'*/ union all select '屲' /*no 'v'*/ union all select '屲' union all select '夕' union all select '丫' union all select '帀' union all select @chn) as a  order by chn COLLATE Chinese_PRC_CI_AS ) as b  
             else
                 set @c=@chn
             set @return=@return+@c  
     end /*while循环结束*/  
     return(@return)  
end /*函数实现结束*/

--使用方法
select dbo.[fn_ChineseToSpell]('字符串排序') 

4. 随机取前N条

SELECT TOP 4 * FROM productinfo ORDER BY NEWID()

5. 按拼音排序

SELECT productName FROM dbo.productinfo ORDER BY productName COLLATE Chinese_PRC_CS_AS_KS_WS

6. 无视锁的查询,即使当前表已经被加锁, 也要将数据查询出来

SELECT * from orderBuyDetail with (nolock)

 7. 生成指定长度的随机数

SELECT right(str(rand(),15,11),11)

--15  rand()的长度, 包含前面的0.的总长度
--11 小数位数的长度. 小数点后面的长度

 8. 获取当前库中所有表名

  select top 1000 
  ROW_NUMBER() OVER (ORDER BY a.object_id) AS No,  
  a.name AS 表名, 
  isnull(g.[value],'-') AS 说明 
from 
  sys.tables a left join sys.extended_properties g 
  on (a.object_id = g.major_id AND g.minor_id = 0) 

9. 获取指定表的架构信息

 SELECT 
    表名       = case when a.colorder=1 then d.name else '' end,
    表说明     = case when a.colorder=1 then isnull(f.value,'') else '' end,
    字段序号   = a.colorder,
    字段名     = a.name,
    标识       = case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end,
    主键       = case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (
                     SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then '√' else '' end,
    类型       = b.name,
    占用字节数 = a.length,
    长度       = COLUMNPROPERTY(a.id,a.name,'PRECISION'),
    小数位数   = isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
    允许空     = case when a.isnullable=1 then '√'else '' end,
    默认值     = isnull(e.text,''),
    字段说明   = isnull(g.[value],'')
FROM 
    syscolumns a
left join 
    systypes b 
on 
    a.xusertype=b.xusertype
inner join 
    sysobjects d 
on 
    a.id=d.id  and d.xtype='U' and  d.name<>'dtproperties'
left join 
    syscomments e 
on 
    a.cdefault=e.id
left join 
sys.extended_properties   g 
on 
    a.id=G.major_id and a.colid=g.minor_id  
left join
sys.extended_properties f
on 
    d.id=f.major_id and f.minor_id=0
where 
    d.name='orderInfo'    --如果只查询指定表,加上此红色where条件，tablename是要查询的表名；去除红色where条件查询说有的表信息
order by 
    a.id,a.colorder