SQL 经验总结

总结日常工作中使用SQL遇到的坑和技巧，一是加深印象，二是方便回顾。会不定期更新。

坑
1、多表联查时要使用表名，如果两个表的有列名相同的情况你没有加别名，那么sql编译器就不知道使用哪个列。这时进行查询会得到不可预知的结果，别问我是怎么知道的，啊……多么痛的领悟~~

2、使用SQL变量是要进行初始化，否则你在赋值的时候会出现错误

3、多表联查时ON的条件一定是两张表的列值做对比(A.Status=B.Status)，如果是一张表的列的条件（A.Status=0），则可能会出现多余的数据。如果想用A.Statusw=0这要的条件，则可以先在A表中过滤，然后在和B表联查

4、尚未注册OLE DB 访问接口"Microsoft.ACE.OLEDB.12.0"。 尚未注册OLE DB 访问接口"Microsoft.ACE.OLEDB.12.0"。
   需要安装Microsoft Access Database Engine 软件

5、SQL Server 阻止了对组件 'Ad Hoc Distributed Queries' 的 STATEMENT  'OpenRowset/OpenDatasource' 的访问，因为此组件已作为此服务器安全配置的一部分而被关闭。系统管理员可以通过使用 sp_configure 启用 'Ad Hoc Distributed Queries'。

EXEC sys.sp_configure @configname = 'Show Advanced Options', @configvalue = 1
RECONFIGURE
EXEC sys.sp_configure @configname = 'Ad Hoc Distributed Queries', @configvalue = 1
RECONFIGURE

注:记得使用完时关闭

6、无法初始化链接服务器“(null)”的 OLE DB 访问接口“Microsoft.ACE.OLEDB.12.0”的数据源对象。
(1)控制面板/管理工具/服务/SQL Server (SQLEXPRESS)  --> 右键 属性 --> 登录 --> 设置： 登录身份=本地系统账户  -->“允许服务与桌面交互”勾上 –>  重启SQL Server 服务
(2) 修改ACE接入参数

USE [master]    
GO    
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1    
GO    
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1    
GO   

 

技巧

0、列操作

增加字段

ALTER TABLE 表名 ADD 字段 类型 NOT NULL/NULL DEFAULT 默认值

修改字段

ALTER TABLE 表名 ALTER COLUMN 字段 类型 NOT NULL/NULL DEFAULT 默认值

修改字段约束

ALTER TABLE 表名 DROP CONSTRAINT 约束名 --先删除约束
ALTER TABLE 表名 ADD CONSTRAINT 约束名称 [primary key(l列名)，unique(列名)，default(默认值) for 列名，check(条件)，foreign key(列名) references 外键表(列名)]

1、替换列值中特殊字符的sql：

UPDATE T_Doc SET C_Name=REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(DOC_Name,' ',''),CHAR(13),''),CHAR(10),''),CHAR(13)+CHAR(10),''),CHAR(9),'')

char(13)是回车符
char(10)是换行符
char(9)是水平制表符

2、跨库分布式查询
OPENDATASOURCE   

SELECT *FROM OPENDATASOURCE('SQLOLEDB','Data Source=服务器名称;User ID=用户名;Password=密码').Northwind.dbo.Categories

链接服务器

exec sp_addlinkedserver  '链接服务器名称','','SQLOLEDB','远程服务器名或ip地址' 
exec sp_addlinkedsrvlogin '链接服务器名称','false',null,'用户名','密码' 
select * from 链接服务器名称.数据库名.dbo.表名 

 

3、导入Excel表数据

EXEC sys.sp_configure @configname = 'Show Advanced Options', @configvalue = 1
RECONFIGURE
EXEC sys.sp_configure @configname = 'Ad Hoc Distributed Queries', @configvalue = 1
RECONFIGURE

SELECT * INTO 表名
FROM OpenDataSource( 'Microsoft.ACE.OLEDB.12.0', 
'Extended properties=Excel 12.0;Data Source="Excel文件路径";')...[sheet1$]--sheet名称
order by ROW_NUMBER() over(order by getdate()) desc 

--关闭
EXEC sys.sp_configure @configname = 'Ad Hoc Distributed Queries', @configvalue = 0
RECONFIGURE
EXEC sys.sp_configure @configname = 'Show Advanced Options', @configvalue = 0
RECONFIGURE

4、Sql Server 获取EXEC（动态SQL）执行后的返回值
使用sp_executesq，它支持对 Transact-SQL 字符串中指定的任何参数的参数值进行替换，但是 EXECUTE 语句不支持。

exec sp_executesql @coungtSql,N'@Count INT OUTPUT', @totalCount OUTPUT
select @totalCount