CSDN博客

img AntingZ

基本SQL语句(一)

发表于2004/6/24 23:17:00  2393人阅读

1.  Insert Into
USE pubs
INSERT INTO MyBooks SELECT title_id, title, type FROM titles WHERE type = 'mod_cook'

2. COALESCE
返回其参数中第一个非空表达式。
COALESCE(expression1,...n) 与此 CASE 函数等价:
CASE
   WHEN (expression1 IS NOT NULL) THEN expression1
   ...
   WHEN (expressionN IS NOT NULL) THEN expressionN
   ELSE NULL

3.TRUNCATE TABLE
语法:TRUNCATE TABLE name
TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同:二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快,且使用的系统和事务日志资源少。
DELETE 语句每次删除一行,并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据,并且只在事务日志中记录页的释放。
TRUNCATE TABLE 删除表中的所有行,但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值,请改用 DELETE。如果要删除表定义及其数据,请使用 DROP TABLE 语句。
对于由 FOREIGN KEY 约束引用的表,不能使用 TRUNCATE TABLE,而应使用不带 WHERE 子句的 DELETE 语句。由于 TRUNCATE TABLE 不记录在日志中,所以它不能激活触发器。
TRUNCATE TABLE 不能用于参与了索引视图的表。

4.汇总数据
① 用 CUBE 汇总数据
②用 ROLLUP 汇总数据
在生成包含小计和合计的报表时,ROLLUP 运算符很有用。ROLLUP 运算符生成的结果集类似于 CUBE 运算符所生成的结果集。
CUBE 和 ROLLUP 之间的区别在于:
   CUBE 生成的结果集显示了所选列中值的所有组合的聚合。
   ROLLUP 生成的结果集显示了所选列中值的某一层次结构的聚合。
ROLLUP 操作的结果集具有类似于 COMPUTE BY 所返回结果集的功能;然而,ROLLUP 具有下列优点:
   ROLLUP 返回单个结果集;COMPUTE BY 返回多个结果集,而多个结果集会增加应用程序代码的复杂性。
   ROLLUP 可以在服务器游标中使用;COMPUTE BY 不可以。
   有时,查询优化器为 ROLLUP 生成的执行计划比为 COMPUTE BY 生成的更为高效。

③用 COMPUTE 和 COMPUTE BY 汇总数据
提供 COMPUTE 和 COMPUTE BY 是为了向后兼容。
COMPUTE 所生成的汇总值在查询结果中显示为分离的结果集

5.SET ROWCOUNT
使 Microsoft? SQL Server? 在返回指定的行数之后停止处理查询。
语法:SET ROWCOUNT { number | @number_var }
建议将当前使用 SET ROWCOUNT 的 DELETE、INSERT 和 UPDATE 语句重新编写为使用 TOP 语法。对于在远程表和本地及远程分区视图上执行的 INSERT、UPDATE 和 DELETE 语句,忽略 SET ROWCOUNT 选项设置
若要关闭该选项(以便返回所有的行),请将 SET ROWCOUNT 指定为 0。
例:想把A表中的第13行的flag字段中的原值"汽车"改成"餐饮"
set rowcount 13
declare @i int
set @i=0
update A表 set @i=@i+1,flag=case @i when 13 then '餐饮' else flag end

6.

---------------------------------------------------------
以上来源:Sql Server2000 帮助


以下来源:CSDN及其它网站(没有明确标注来源的均摘自CSDN论坛提问区)
---------------------------------------------------------

101.如何得到某个字段的默认值?
select column_default from INFORMATION_SCHEMA.columns where table_name='table2' and column_name='column2'

102. 列出SQL SERVER 所有表,字段名,主键,类型,长度,小数位数等信息
SELECT 
   (case when a.colorder=1 then d.name else '' end)表名,
       a.colorder 字段序号,
       a.name 字段名,
       (case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) 标识,
       (case when (SELECT count(*)
       FROM sysobjects
       WHERE (name in
                 (SELECT name
                FROM sysindexes
                WHERE (id = a.id) AND (indid in
                          (SELECT indid
                         FROM sysindexkeys
                         WHERE (id = a.id) AND (colid in
                                   (SELECT colid
                                  FROM syscolumns
                                  WHERE (id = a.id) AND (name = a.name))))))) AND
              (xtype = 'PK'))>0 then '√' else '' end) 主键,
       b.name 类型,
       a.length 占用字节数,
       COLUMNPROPERTY(a.id,a.name,'PRECISION') as 长度,
       isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as 小数位数,
       (case when a.isnullable=1 then '√'else '' end) 允许空,
       isnull(e.text,'') 默认值,
       isnull(g.[value],'') AS 字段说明    
FROM  syscolumns  a left join systypes b
on  a.xtype=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 sysproperties g
on a.id=g.id AND a.colid = g.smallid 
order by a.id,a.colorder
(来源:http://dev.csdn.net/article/18/18624.shtm  
标题:收藏几段SQL语句和存储过程     lihonggen0 [收藏] )

103. 根据表中数据生成insert语句的存储过程
CREATE   proc spGenInsertSQL (@tablename varchar(256))
as
begin
  declare @sql varchar(8000)
  declare @sqlValues varchar(8000)
  set @sql =' ('
  set @sqlValues = 'values (''+'
  select @sqlValues = @sqlValues + cols + ' + '','' + ' ,@sql = @sql + '[' + name + '],'
    from
        (select case
                  when xtype in (48,52,56,59,60,62,104,106,108,122,127)                               
                       then 'case when '+ name +' is null then ''NULL'' else ' + 'cast('+ name + ' as varchar)'+' end'
                  when xtype in (58,61)
                       then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'cast('+ name +' as varchar)'+ '+'''''''''+' end'
                 when xtype in (167)
                       then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'replace('+ name+','''''''','''''''''''')' + '+'''''''''+' end'
                  when xtype in (231)
                       then 'case when '+ name +' is null then ''NULL'' else '+'''N'''''' + ' + 'replace('+ name+','''''''','''''''''''')' + '+'''''''''+' end'
                  when xtype in (175)
                       then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'cast(replace('+ name+','''''''','''''''''''') as Char(' + cast(length as varchar)  + '))+'''''''''+' end'
                  when xtype in (239)
                       then 'case when '+ name +' is null then ''NULL'' else '+'''N'''''' + ' + 'cast(replace('+ name+','''''''','''''''''''') as Char(' + cast(length as varchar)  + '))+'''''''''+' end'
                  else '''NULL'''
                end as Cols,name
           from syscolumns 
          where id = object_id(@tablename)
        ) T
  set @sql ='select ''INSERT INTO ['+ @tablename + ']' + left(@sql,len(@sql)-1)+') ' + left(@sqlValues,len(@sqlValues)-4) + ')'' from '+@tablename
  --print @sql
  exec (@sql)
end
GO
(来源:http://dev.csdn.net/article/18/18624.shtm  
标题:收藏几段SQL语句和存储过程     lihonggen0 [收藏] 感谢playyuer)

104. 根据表中数据生成insert语句的存储过程2
CREATE proc proc_insert (@tablename varchar(256))
as
begin
       set nocount on
       declare @sqlstr varchar(4000)
       declare @sqlstr1 varchar(4000)
       declare @sqlstr2 varchar(4000)
       select @sqlstr='select ''insert '+@tablename
       select @sqlstr1=''
       select @sqlstr2=' ('
       select @sqlstr1= ' values ( ''+'
       select @sqlstr1=@sqlstr1+col+'+'',''+' ,@sqlstr2=@sqlstr2+name +',' from (select case
--     when a.xtype =173 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.length*2+2)+'),'+a.name +')'+' end'
       when a.xtype =104 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(1),'+a.name +')'+' end'
       when a.xtype =175 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end'
       when a.xtype =61  then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'convert(varchar(23),'+a.name +',121)'+ '+'''''''''+' end'
       when a.xtype =106 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.xprec+2)+'),'+a.name +')'+' end'
       when a.xtype =62  then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(23),'+a.name +',2)'+' end'
       when a.xtype =56  then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(11),'+a.name +')'+' end'
       when a.xtype =60  then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(22),'+a.name +')'+' end'
       when a.xtype =239 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end'
       when a.xtype =108 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.xprec+2)+'),'+a.name +')'+' end'
       when a.xtype =231 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end'
       when a.xtype =59  then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(23),'+a.name +',2)'+' end'
       when a.xtype =58  then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'convert(varchar(23),'+a.name +',121)'+ '+'''''''''+' end'
       when a.xtype =52  then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(12),'+a.name +')'+' end'
       when a.xtype =122 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(22),'+a.name +')'+' end'
       when a.xtype =48  then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(6),'+a.name +')'+' end'
--     when a.xtype =165 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.length*2+2)+'),'+a.name +')'+' end'
       when a.xtype =167 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end'
       else '''NULL'''
       end as col,a.colid,a.name
       from syscolumns a where a.id = object_id(@tablename) and a.xtype <>189 and a.xtype <>34 and a.xtype <>35 and  a.xtype <>36
       )t order by colid
       select @sqlstr=@sqlstr+left(@sqlstr2,len(@sqlstr2)-1)+') '+left(@sqlstr1,len(@sqlstr1)-3)+')'' from '+@tablename
--  print @sqlstr
       exec( @sqlstr)
       set nocount off
end
GO
(来源:http://dev.csdn.net/article/18/18624.shtm  
标题:收藏几段SQL语句和存储过程     lihonggen0 [收藏] 感谢Sky_blue )

105.破解加密存储过程
CREATE  PROCEDURE sp_decrypt(@objectName varchar(50))
AS
begin
set nocount on
--CSDN:j9988 copyright:2004.04.15
--V3.1
--破解字节不受限制,适用于SQLSERVER2000存储过程,函数,视图,触发器
--修正上一版视图触发器不能正确解密错误
--发现有错,请E_MAIL:CSDNj9988@tom.com
begin tran
declare @objectname1 varchar(100),@orgvarbin varbinary(8000)
declare @sql1 nvarchar(4000),@sql2 varchar(8000),@sql3 nvarchar(4000),@sql4 nvarchar(4000)
DECLARE  @OrigSpText1 nvarchar(4000),  @OrigSpText2 nvarchar(4000) , @OrigSpText3 nvarchar(4000), @resultsp nvarchar(4000)
declare  @i int,@status int,@type varchar(10),@parentid int
declare @colid int,@n int,@q int,@j int,@k int,@encrypted int,@number int
select @type=xtype,@parentid=parent_obj from sysobjects where id=object_id(@ObjectName)

create table  #temp(number int,colid int,ctext varbinary(8000),encrypted int,status int)
insert #temp SELECT number,colid,ctext,encrypted,status FROM syscomments  WHERE id = object_id(@objectName)
select @number=max(number) from #temp
set @k=0

while @k<=@number
begin
if exists(select 1 from syscomments where id=object_id(@objectname) and number=@k)
begin
if @type='P'
set @sql1=(case when @number>1 then 'ALTER PROCEDURE '+ @objectName +';'+rtrim(@k)+' WITH ENCRYPTION AS '
                          else 'ALTER PROCEDURE '+ @objectName+' WITH ENCRYPTION AS '
                          end)

if @type='TR'
begin
declare @parent_obj varchar(255),@tr_parent_xtype varchar(10)
select @parent_obj=parent_obj from sysobjects where id=object_id(@objectName)
select @tr_parent_xtype=xtype from sysobjects where id=@parent_obj
if @tr_parent_xtype='V'
begin
set @sql1='ALTER TRIGGER '+@objectname+' ON '+OBJECT_NAME(@parentid)+' WITH ENCRYPTION INSTERD OF INSERT AS PRINT 1 '
end
else
begin
set @sql1='ALTER TRIGGER '+@objectname+' ON '+OBJECT_NAME(@parentid)+' WITH ENCRYPTION FOR INSERT AS PRINT 1 '
end

end
if @type='FN' or @type='TF' or @type='IF'
set @sql1=(case @type when 'TF' then
'ALTER FUNCTION '+ @objectName+'(@a char(1)) returns @b table(a varchar(10)) with encryption as begin insert @b select @a return end '
when 'FN' then
'ALTER FUNCTION '+ @objectName+'(@a char(1)) returns char(1) with encryption as begin return @a end'
when 'IF' then
'ALTER FUNCTION '+ @objectName+'(@a char(1)) returns table with encryption as return select @a as a'
end)

if @type='V'
set @sql1='ALTER VIEW '+@objectname+' WITH ENCRYPTION AS SELECT 1 as f'

set @q=len(@sql1)
set @sql1=@sql1+REPLICATE('-',4000-@q)
select @sql2=REPLICATE('-',8000)
set @sql3='exec(@sql1'
select @colid=max(colid) from #temp where number=@k
set @n=1
while @n<=CEILING(1.0*(@colid-1)/2) and len(@sQL3)<=3996
begin
set @sql3=@sql3+'+@'
set @n=@n+1
end
set @sql3=@sql3+')'
exec sp_executesql @sql3,N'@Sql1 nvarchar(4000),@ varchar(8000)',@sql1=@sql1,@=@sql2

end
set @k=@k+1
end

set @k=0
while @k<=@number
begin

if exists(select 1 from syscomments where id=object_id(@objectname) and number=@k)
begin
select @colid=max(colid) from #temp where number=@k
set @n=1

while @n<=@colid
begin
select @OrigSpText1=ctext,@encrypted=encrypted,@status=status FROM #temp  WHERE colid=@n and number=@k

SET @OrigSpText3=(SELECT ctext FROM syscomments WHERE id=object_id(@objectName) and colid=@n and number=@k)
if @n=1
begin
if @type='P'
SET @OrigSpText2=(case when @number>1 then 'CREATE PROCEDURE '+ @objectName +';'+rtrim(@k)+' WITH ENCRYPTION AS '
                       else 'CREATE PROCEDURE '+ @objectName +' WITH ENCRYPTION AS '
                       end)


if @type='FN' or @type='TF' or @type='IF'
SET @OrigSpText2=(case @type when 'TF' then
'CREATE FUNCTION '+ @objectName+'(@a char(1)) returns @b table(a varchar(10)) with encryption as begin insert @b select @a return end '
when 'FN' then
'CREATE FUNCTION '+ @objectName+'(@a char(1)) returns char(1) with encryption as begin return @a end'
when 'IF' then
'CREATE FUNCTION '+ @objectName+'(@a char(1)) returns table with encryption as return select @a as a'
end)

if @type='TR'
begin

if @tr_parent_xtype='V'
begin
set @OrigSpText2='CREATE TRIGGER '+@objectname+' ON '+OBJECT_NAME(@parentid)+' WITH ENCRYPTION INSTEAD OF INSERT AS PRINT 1 '
end
else
begin
set @OrigSpText2='CREATE TRIGGER '+@objectname+' ON '+OBJECT_NAME(@parentid)+' WITH ENCRYPTION FOR INSERT AS PRINT 1 '
end

end

if @type='V'
set @OrigSpText2='CREATE VIEW '+@objectname+' WITH ENCRYPTION AS SELECT 1 as f'

set @q=4000-len(@OrigSpText2)
set @OrigSpText2=@OrigSpText2+REPLICATE('-',@q)
end
else
begin
SET @OrigSpText2=REPLICATE('-', 4000)
end
SET @i=1

SET @resultsp = replicate(N'A', (datalength(@OrigSpText1) / 2))

WHILE @i<=datalength(@OrigSpText1)/2
BEGIN

SET @resultsp = stuff(@resultsp, @i, 1, NCHAR(UNICODE(substring(@OrigSpText1, @i, 1)) ^
                                (UNICODE(substring(@OrigSpText2, @i, 1)) ^
                                UNICODE(substring(@OrigSpText3, @i, 1)))))
 SET @i=@i+1
END
set @orgvarbin=cast(@OrigSpText1 as varbinary(8000))
set @resultsp=(case when @encrypted=1
                    then @resultsp
                    else convert(nvarchar(4000),case when @status&2=2 then uncompress(@orgvarbin) else @orgvarbin end)
               end)
print @resultsp

set @n=@n+1

end

end
set @k=@k+1
end

drop table #temp
rollback tran
end
----------------------
回复人: zjcxc(邹建)
蒋教师的解密存储过程很不错啊,如果你要解密所有的存储过程,这样就搞定了:

 --对所有的存储过程解密
 declare tb cursor for
 select name from sysobjects where xtype='P' and status>0
 
 declare @name sysname
 open tb
 fetch next from tb into @name
 while @@fetch_status=0
 begin
  print '/*-------存储过程 ['+@name+'] -----------*/'
  exec sp_decrypt @name
  fetch next from tb into @name
 end
 close tb
 deallocate tb

106.把表的一个字段改成自动编号,并且保持其原来的值不变

--你可以考虑用下面的存储过程来实现,但我建议你还是在企业管理器修改.
/*--将表中的某个字段转换成标识字段,并保留原来的值
 注意,因为要删除原表,所以,如果表和其他表的关联,这些关联要重新创建
--邹建 2003.12--*/
/*--调用示例
 exec p_setid '表名','要转换的字段名'
--*/

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_setid]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 drop procedure [dbo].[p_setid]
GO

CREATE PROC P_SETID
@tbname sysname, --要处理的表名
@fdname sysname  --要转换为标识字段的字段名
as
declare @s1 varchar(8000),@s2 varchar(8000),@tmptb sysname
select @s1='',@s2='',@tmptb='[tmp_'+@tbname+'_bak]'
select @s1=@s1+',['+name+']'
 +case name when @fdname then '=identity(bigint,1,1)' else '' end
 ,@s2=@s2+',['+name+']'
from syscolumns where object_id(@tbname)=id
select @s1=substring(@s1,2,8000),@s2=substring(@s2,2,8000)
exec('select top 0
'+@s1+' into '+@tmptb+' from ['+@tbname+']
set identity_insert
'+@tmptb+' on
insert into
'+@tmptb+'('+@s2+') select '+@s2+' from ['+@tbname+']
set identity_insert
'+@tmptb+' off
')
exec('drop table ['+@tbname+']')
exec sp_rename @tmptb,@tbname
go

--使用测试

--创建测试的表
create table 表(编号 bigint,姓名 varchar(10))
insert into 表
select 1,'张三'
union all select 2,'李四'
union all select 4,'王五'
go

--调用存储过程,将编号字段改为标识字段
exec p_setid '表','编号'
go

--显示处理结果
select * from 表

--显示是否修改成功
select name from syscolumns
where object_id('表')=id and status=0x80
go

--删除测试
drop table 表
(来源:
http://community.csdn.net/Expert/topic/2731/2731806.xml?temp=.3456232

107.


 

阅读全文
0 0

相关文章推荐

img
取 消
img