-
SQL code
-
基本函数： 1 . len ()不取字符串最后的空格个数，但会取字符串中间的空格个数。 datalength ()取所有的，包括空格个数 2 . reverse ()将()里面的字符反过来 3 . replicate (a,n)将a重复n次 4 . space (n)返回n个空格 5 . stuff (a,n1,n2,b)从n1起往a字符串中插入b字符串，n2是要覆盖的个数。如果为0，则只是插入，不覆盖a中的字符。否之。 6 . raiserror ( '' , 16 , 1 ) 抛出消息 7 . substring (a,i,j) 取a字符串中从i起，共j个长度的字符。 charindex (a,m) 得到字符串a在字符串m中的起始位置 8 . isnull ()函数 isnull (a，b) 如果列a为空，则用值b来代替，但并不改变其在数据库中的值。 删除R1表中R2的数据 delete r1 where checksum( * ) in ( select checksum( * ) from r2) 9 . exists ()函数 如果()中查询语句返回行数为0，则为false，否之则为true. 往a表中添加一列a01 alter table a add a01 int 修改列 alter table pp alter column a varchar ( 50 ) 更改字段名 exec sp_rename ' 表名.字段名 ' , ' 新字段名 ' , ' column ' ALTER TABLE 表名 DISABLE TRIGGER 触发器名 -- 禁止触发器 ALTER TABLE 表名 ENABLE TRIGGER 触发器名 -- 允许触发器 EXEC sp_MSforeachtable ' ALTER TABLE ? DISABLE TRIGGER ALL ' -- 禁用数据库中所有表的所有触发器 EXEC sp_MSforeachtable ' ALTER TABLE ? ENABLE TRIGGER ALL ' -- 启用数据库中所有表的所有触发器 -- 清除所有表主键 EXEC sp_MSforeachtable ' declare @PkName varchar(100) select @PkName=name from sysobjects where xtype= '' Pk '' and parent_obj=object_id( '' ? '' ); exec( '' alter table ? drop constraint '' +@PkName) ' go -- 修改所有表的字段为某字段： EXEC sp_MSforeachtable ' exec sp_rename '' ?.收款日期 '' , '' 发生日期 ''' -- 将数据库中所有表的收款日期改为发生日期 修改text类型字段的值： create table os(SoftIntro text ,ID int ) insert into os select ' aaa ' , 1004 union all select ' bbb ' , 1003 union all select ' ccc ' , 1002 declare @ptr binary ( 16 ) select @ptr = textptr (SoftIntro) from os where id = 1002 updatetext os.SoftIntro @ptr null 0 ' dddd ' select * from os 替换text类型字段的值 create table t(Cbody ntext ) insert into t select ' 324234234深圳罗湖324324 ' insert into t select ' 深圳罗湖324324 ' declare @ptrval binary ( 16 ), @star int declare cur cursor for select textptr (Cbody), charindex ( ' 深圳罗湖 ' ,cbody) - 1 from t where cbody like ' %深圳罗湖% ' open cur fetch next from cur into @ptrval , @star while ( @@fetch_status = 0 ) begin declare @sql nvarchar ( 1000 ) set @sql = ' UPDATETEXT t.cbody @ptrval ' + ltrim ( @star ) + ' ' + ltrim ( len ( ' 深圳罗湖 ' )) + ' N '' 深圳福田 ''' exec sp_executesql @sql ,N ' @ptrval binary(16) ' , @ptrval fetch next from cur into @ptrval , @star end close cur deallocate cur -- 创建表 create table 表(a1 varchar ( 10 ),a2 char ( 2 )) -- 为表添加描述信息 EXECUTE sp_addextendedproperty N ' MS_Description ' , ' 人员信息表 ' , N ' user ' , N ' dbo ' , N ' table ' , N ' 表 ' , NULL , NULL -- 为字段a1添加描述信息 EXECUTE sp_addextendedproperty N ' MS_Description ' , ' 姓名 ' , N ' user ' , N ' dbo ' , N ' table ' , N ' 表 ' , N ' column ' , N ' a1 ' -- 更新表中列a1的描述属性： EXEC sp_updateextendedproperty ' MS_Description ' , ' 字段1 ' , ' user ' ,dbo, ' table ' , ' 表 ' , ' column ' ,a1 -- 删除表中列a1的描述属性： EXEC sp_dropextendedproperty ' MS_Description ' , ' user ' ,dbo, ' table ' , ' 表 ' , ' column ' ,a1 -- 删除测试 drop table 表 取5以下随机的数字 select convert ( varchar , ceiling ( rand () * 5 )) 将数字转换成百分比： select rtrim ( cast ( 2 * 100 / 10 as decimal ( 5 , 2 ))) + ' % ' cast ( 2 * 100 / 10 as decimal ( 5 , 2 )) 这个是将2 * 100 / 10转换成5位且小数位为2位的浮点小数， 11 .乘积： declare @s table (id float ) insert into @s select 2 insert into @s select 3 insert into @s select 2.5 select exp ( sum ( log (id))) from @s 12 .like的用法 查出记录中的字符串包含1， 2 ， 3 ，4的记录 Create Table TEST (share varchar ( 100 )) Insert TEST Select ' 1,2,24 ' Union All Select ' 2,23,56 ' Union All Select ' 6,10,11 ' Union All Select ' 3,4,15 ' Union All Select ' 6,29,31 ' GO Select * From TEST Where ' , ' + share + ' , ' Like ' %,[1-4],% ' 13 ，按条件分类排序 select brand from bra order by case when brand = ' 飞利浦 ' then '' else brand end // 将brand为飞利浦的排在最上面 14 .给返回的记录加上一个行数 ( 1 )当没有自增的id列时： select bh = identity ( int , 1 , 1 ) ,proname,price into temp from product select ' 第 ' + cast (bh as varchar ) + ' 个 ' , proname,price from temp ( 2 )当有自增的id列时 select bh,name,price from ( SELECT bh = ( SELECT COUNT ( 1 ) FROM product WHERE id > a.id) + 1 , * FROM product a ) t order by bh -- -SQL2005启用 openrowset/opendataset exec sp_configure ' show advanced options ' , 1 reconfigure exec sp_configure ' Ad Hoc Distributed Queries ' , 1 reconfigure -- 启用xp_cmdshell -- 允许配置高级选项 EXEC sp_configure ' show advanced options ' , 1 GO -- 重新配置 RECONFIGURE GO -- 启用xp_cmdshell EXEC sp_configure ' xp_cmdshell ' , 1 GO -- 重新配置 RECONFIGURE GO -- 启动远程服务器的MSDTC服务 exec master..xp_cmdshell ' isql /S"10.128.34.22" /U"sa" /P"123456" /q"exec master..xp_cmdshell '' net start msdtc '' ,no_output" ' ,no_output -- 启动本机的MSDTC服务 exec master..xp_cmdshell ' net start msdtc ' ,no_output -- -远程链接： 1 、创建远程链接服务器，然后进行查询 exec sp_addlinkedserver ' HJZX_SYN ' , '' , ' SQLOLEDB ' , ' 10.128.34.22 ' exec sp_addlinkedsrvlogin ' HJZX_SYN ' , ' false ' , null , ' sa ' , ' 123456 ' go select * from HJZX_SYN.数据库名.dbo.表名 -- -删除连接 exec sp_dropserver ' MyLink ' , ' droplogins ' -- -查询 select * from sysservers 2 、 select * from openrowset ( ' msdasql ' , ' driver={sql server};server=10.124.20.10;uid=ncc2008;pwd=ncc2008 ' ,hjzx4.dbo.t_p_order) AS a 3 、 select * from opendatasource ( ' sqloledb ' , ' Data Source=10.124.20.10;User ID=ncc2008;Password=ncc2008 ' ).hjzx4.dbo.t_p_order 如： alter database 数据库名 COLLATE Chinese_PRC_CI_AS 不区分大小写， 而 alter database 数据库名 COLLATE Chinese_PRC_CS_AS 使之区分大小写。 导入excel数据： select * into # from OPENROWSET ( ' microsoft.jet.oledb.4.0 ' , ' Excel 5.0;hdr=yes;database=d:/1月安排.xls ' ,准考证信息$) insert into 表名 select * from OpenDataSource ( ' Microsoft.Jet.OLEDB.4.0 ' , ' Data Source=f:/Test.xls;User ID=Admin;Password=;Extended properties=Excel 8.0 ' )... [ Sheet1$ ] office2007 SELECT * FROM OPENDATASOURCE ( ' Microsoft.ACE.OLEDB.12.0 ' , ' Data Source="f:/aa.xls";User ID=admin;Password=;Extended properties=Excel 5.0 ' )... [ sheet1$ ] -- --bcp: - t列分割符，默认是以制表符(/t)分割 - r行分割符，默认是以换行符(/n)分割 导入： in EXEC master..xp_cmdshell ' bcp pruduct in d:/wsp.txt -c -t. -r/n ' 导出： -- 导出全表数据out EXEC master..xp_cmdshell ' bcp pruduct out d:/wsp.txt -c -Usa -Psa ' -- 导出查询结果queryout EXEC master..xp_cmdshell ' bcp "select * from pruduct where part_id like '' 80% '' " queryout d:/wsp.txt -c -t, -Usa -Psa ' -- 导入文本文档： BULK INSERT os FROM ' c:/d.txt ' WITH ( FIELDTERMINATOR = ' , ' , -- 列以逗号隔开 ROWTERMINATOR = ' /n ' -- 行以换行符隔开 ) -- -osql,执行sql命令 exec master..xp_cmdshell ' osql -U sa -P sa -i d:/tt.txt ' -- 用SQL语句备份、还原数据库 BACKUP DATABASE test -- 这里的test指的是数据库名称 TO disk = ' c:/backup.bak ' -- 这里指名的数据库路径(backup.bak为备份文件名) WITH FORMAT, NAME = ' Full Backup of MyNwind ' -- 这个是备注，无所谓。。随便写。 RESTORE DATABASE jz1 -- 所被恢复的数据库名称 FROM disk = ' c:/backup.bak --本地硬盘路径(backup.bak为备份文件名) GO --SQL语句分离、附加： --分离 sp_detach_db ' zetian ' --附加 EXEC sp_attach_db @dbname = N ' zetian ' , @filename1 = N ' C:/Inetpub/wwwroot/zetian/数据库/zetian.mdf ' , @filename2 = N ' C:/Inetpub/wwwroot/zetian/数据库/zetian_log.ldf ' --分解字咐 declare @a table(A varchar(20), B varchar(20), C varchar(20), D varchar(20)) insert @a select ' a1 ' , ' b1 ' , ' c1 ' , ' d1 / da ' union all select ' a2 ' , ' b2 ' , ' c2 ' , ' d22 / da / da22 ' union all select ' a3 ' , ' b3 ' , ' c3 ' , ' d3 ' declare @t table( id int identity(1,1),e int) insert @t select top 500 1 from syscolumns select a,b,c,substring(d+ ' / ' ,id,charindex( ' / ' ,d+ ' / ' ,id+1)-id) X from @a a,@t b where substring( ' / ' +d,id,1)= ' / ' --设置约束 CREATE TABLE jobs ( min_lvl int NOT NULL CHECK (min_lvl > = 10) ) 判断该文件是否存在： DECLARE @err INT,@fso INT,@fleExists BIT,@file VARCHAR(100) SET @file= ' d:/aaa.txt ' EXEC @err=sp_OACreate ' Scripting.FileSystemObject ' ,@fso OUTPUT EXEC @err=sp_OAMethod @fso, ' FileExists ' ,@fleExists OUTPUT,@file EXEC @err = sp_OADestroy @fso IF @fleExists=0 PRINT ' " ' + @file + ' " not exists ' ELSE exec( ' exec xp_cmdshell '' del ' +@file+ '''' ) --存在则删除 设置级联： alter table 表名 constraint FK_employee foreign key (外键字段) references 主表(主键字段)ON UPDATE CASCADE 设置默认值： alter table 表名 constraint FK_employee default 默认值 for 字段
1963
07-08
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
