基本函数

最新推荐文章于 2021-03-22 20:48:10 发布

xiaoyyhn

最新推荐文章于 2021-03-22 20:48:10 发布

阅读量713

点赞数

分类专栏： sql 文章标签： table insert 数据库 database properties excel

sql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

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 字段