开发中常用的sql语句

原创

兴想事成 2022-01-10 11:42:58 ©著作权

文章标签 sql导入Excel sql语句导出Excel xml导入sqlserver linq server的使用 xml 文章分类 代码人生

©著作权归作者所有：来自51CTO博客作者兴想事成的原创作品，请联系作者获取转载授权，否则将追究法律责任

1.导入Excel进数据库,以及导出Excel

本人使用的是 sqlserver2008R2,sqlserver 2005以上版本应该都支持

--先调用sp_configure配置

-----打开
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure
go
--关闭
exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure 
go

--调用sp_configure配置
EXEC sp_configure 'show advanced options', 1
GO

--调用sp_configure配置
EXEC sp_configure 'show advanced options', 1
GO
--更新配置信息
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO
exec sp_configure 'Ole Automation Procedures',0
reconfigure

--系统管理员可以通过使用 sp_configure 启用 'Ad Hoc Distributed Queries'。有关启用 'Ad Hoc Distributed Queries' 的详细信息，请参阅 SQL Server 联机丛书中的 "外围应用配置器"。

--------------------下面是正式代码,经过测试----------------------------------------------------------

导出Excel

exec master..xp_cmdshell 'bcp " select * from SongDB..AppTB" queryout d:\af.xls -c -U "sa" -P "sa"'

--如果接受数据导入的表已经存在

insert into 表 select * from 
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Excel 5.0;HDR=YES;DATABASE=c:\test.xls',sheet1$)

--如果导入数据并生成表

select * into 表 from 
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Excel 5.0;HDR=YES;DATABASE=c:\test.xls',sheet1$)

注意:导入 Excel版本的问题

--导入 97-2003格式 *.xls

insert into QQFindGreen select * from 

OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;IMEX=2;DATABASE=D:\qqfindgreen.xls',[Sheet1$])

--导入2007以上格式 *.xlsx

insert into TableDownload select * from 
OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=YES;DATABASE=D:\QQ上剩下的表单.xlsx',sheet1$)

--注明: HDR=YES 这个会把Excel的第一行当做表头子来使用,如果你想导入纯数据,请将 HDR=YES改为HDR=NO

--2、在SQL SERVER里往Excel插入数据:
-- ======================================================

--T-SQL代码：

INSERT INTO OPENDATASOURCE('Microsoft.JET.OLEDB.4.0', 'Extended Properties=Excel 8.0;Data source=C:\training\inventur.xls')...[Sheet1$] 
(bestand, produkt) VALUES (20, 'Test')

导出Excel

exec master..xp_cmdshell 'bcp " select top(10) Sid,SongName,Singer from SongDB..SongTB" queryout d:\ah.xls -c -U "sa" -P "sa"'

--Excel里面数据的更新

update OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;hdr=yes;database=d:\songModel.xls;','select * from [download$]')
set NoName1='2000' where 批次 not in(select top(10) 批次 from OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;IMEX=2;DATABASE=d:\songModel.xls',[download$]))

--------------------------------------------------------------------------------------------------------------------------------------------
--结论:sql 对于Excel 不支持删除操作
delete from OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;DATABASE=d:\songModel.xls',download$) where 批次='2'
/*
链接服务器"(null)"的 OLE DB 访问接口 "MICROSOFT.JET.OLEDB.4.0" 返回了消息 "该 ISAM 不支持在链接表中删除数据。"。
消息 7345，级别 16，状态 1，第 1 行
链接服务器 "(null)" 的 OLE DB 访问接口 "MICROSOFT.JET.OLEDB.4.0" 无法从表 "download$" 删除数据。出现可恢复的、特定于提供程序的错误，如 RPC 失败。

另外特别说明一点:sql语句可以更新Excel表里面的内容,但是不能删除里面的东西,这个是因为要考虑Excel的安全性

如果要操作删除Excel里面的行,例如删除表头子,可以使用C#代码来删除

纯sql语句完成不了这一功能,要是你们有sql语句可以直接删除Excel表结构的,请指教我,谢谢.

/*分割线********************************************************************************************************/

3.使用 Link Server

使用linq server,你可以访问别的数据库,比如说,你又两个以上的数据库,但是你程序(C#里面的配置文件connectionString)配置的字符串,肯定不会随意改变

两个数据库,就有连个字符串,也不方便随意切换来,切换去,甚至在 sqlserver里面查数据的时候,如果两个表的数据是有关联的

这时候,使用linq server,让你省去了不少麻烦

--开启 Link Server

exec sp_addlinkedserver 'srv_lnk','','SQLOLEDB','127.0.0.1'
exec sp_addlinkedsrvlogin 'srv_lnk','false',null,'sa','sa'
go

--关闭link server

exec sp_dropserver 'srv_lnk','droplogins'
go
select distinct A.Sid,A.SongName,A.Singer from AllOORest A,srv_lnk.SongDB.dbo.AlbumSong B

4.得到表的所有信息

--得到表的列的所有信息

select
c.name as [字段名],t.name as [字段类型]
,convert(bit,c.IsNullable) as [可否为空]
,convert(bit,case when exists(select 1 from sysobjects where xtype='PK' and parent_obj=c.id and name in (
select name from sysindexes where indid in(
select indid from sysindexkeys where id = c.id and colid=c.colid))) then 1 else 0 end) 
as [是否主键]
,convert(bit,COLUMNPROPERTY(c.id,c.name,'IsIdentity')) as [自动增长]
,c.Length as [占用字节] 
,COLUMNPROPERTY(c.id,c.name,'PRECISION') as [长度]
,isnull(COLUMNPROPERTY(c.id,c.name,'Scale'),0) as [小数位数]
,ISNULL(CM.text,'') as [默认值]
,isnull(ETP.value,'') AS [字段描述]
--,ROW_NUMBER() OVER (ORDER BY C.name) AS [Row]
from syscolumns c
inner join systypes t on c.xusertype = t.xusertype 
left join sys.extended_properties ETP on ETP.major_id = c.id and ETP.minor_id = c.colid and ETP.name ='MS_Description' 
left join syscomments CM on c.cdefault=CM.id
where c.id = object_id('DownloadTB')

5.有时候,使用 % _,通配符进行查找数据的时候,可能不是很方便

我们想使用 C#里面的 contains()函数这样的功能

解决方法是建立全文本索引,我从网上找来的方法,原处已经忘记了,感谢那位网友的热情分享

use SongDB --打开数据库
go
--检查pubs是否支持全文索引，如果不支持全文索引，则使用sp_fulltext_datebase打开该功能
if (select databaseproperty ('SongDB','IsFulltextEnables'))=0
execute sp_fulltext_database 'enable'
--建立全文目录FT_pubs
execute sp_fulltext_catalog 'FT_pubs','create'
--为titles表建立全文索引数据元
execute sp_fulltext_table 'app','FT_pubs','PK_app'
--设置全文索引列名
execute sp_fulltext_column 'app','sid','add'
--建立全文索引
execute sp_fulltext_table 'FT_pubs','activate'
--填充全文索引目录
execute sp_fulltext_catalog 'FT_pubs','start_full'
GO

------------------------------------------------------------------------------------

GO
--检查全文目录填充情况
WHILE FulltextCatalogProperty('FT_pubs','PopulateStatus')<>0
BEGIN
--如果全文目录正处于填充状态，则等待30秒后再检测一次
WAITFOR DELAY '0:0:30'
END

--------------------------------------------------------------------------------------

6.我想还有一些功能,是很多人想用的,比如导入xml数据

从本质上来讲 xml数据是比Excel数据要友好一点,因为 xml数据,你可以使用

linq to xml,以及使用文件读写xml的方式来访问,但是为了不用那么麻烦

自己去解析 xml,然后拼接sql语句来进行insert into,本人一度曾经这么做过

因为在网上没发现什么能够直接拿来用的.

我的数据都是从百万级数据库上导出的xml数据,都是几十兆大小到几百兆大小不等

如果直接放在sql语句里面当做xml变量,我试过,打开文件不是一般的长...

最好的办法是,只要知道它的结构,以及xml文件所存放在磁盘上的物理路径就好了

实现的方法如下

首先要自己创建一个表名

CREATE TABLE tb_OldTB(
歌曲名称 nvarchar(200),
表演者 nvarchar(200),
歌曲类别 nvarchar(50),
歌手类别 nvarchar(50),
音源存放路径 nvarchar(120)
)

go

然后是导入xml文件

DECLARE @h int,
@doc xml
SELECT @doc=BulkColumn FROM OPENROWSET (BULK 'I:\NewExcel\514273.xml', SINGLE_BLOB) AS xmlData 
EXECUTE sp_xml_preparedocument @h OUTPUT, @doc
insert into tb_OldTB select * from OPENXML(@h, '/data/row',2) WITH tb_OldTB 
EXECUTE sp_xml_removedocument @h
go