实例T-SQL 杂项(你会获益良多的)
7、链接服务器, OPENROWSET函数
/**//***************************************
功能： 如何查询远程服务器对象。
作者：
创建时间： 2007-08-01
修改时间： 2007-08-01
备注：
引用; 关键字：链接服务器, OPENROWSET函数
****************************************/
--方法一:使用链接服务器,固定.
USE [master]
GO
DECLARE @SqlServer VARCHAR(50)
SET @SqlServer=N'IpAddress'
EXEC master.dbo.sp_addlinkedserver @server = N'LINK_TEST', @srvproduct=@SqlServer,
@provider=N'SQLOLEDB', @datasrc=@SqlServer, @provstr=N'UID=login;PWD=password;'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'LINK_TEST',@useself=N'False',@locallogin=N'sa',@rmtuser=N'login',@rmtpassword='password'
EXEC master.dbo.sp_serveroption @server=N'LINK_TEST', @optname=N'rpc', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'LINK_TEST', @optname=N'rpc out', @optvalue=N'true'
--EXEC master.dbo.sp_dropserver @server = 'server',@droplogins = 'droplogins'
SELECT TOP 1 * FROM link_test.logincard.dbo.zldept --测试查询
--修改链接服务器表中的列,需指定链接名称.
EXEC ('ALTER TABLE YourDBName.dbo.TableA ADD Field varchar(5);') at YourLinkServerName
--方法二:使用OPENROWSET函数替代(临时性)
-- SQL Login
SELECT TOP 1 a.* FROM OPENROWSET('SQLOLEDB','ipAddress';'Login';'password',Northwind.dbo.orders )a
-- Integrierte Sicherheit
SELECT * FROM OPENROWSET('SQLOLEDB',
'Server=ipaddress;database=dbname;TRUSTED_CONNECTION=YES;',
'SELECT top 1 * FROM txcard.dbo.zldept')
--或使用 OPENDATASOURCE 连接方式
-- Integrierte Sicherheit
SELECT TOP 1 * FROM OPENDATASOURCE('SQLOLEDB',
'Data Source=192.168.117.20;Initial Catalog=txcard;Integrated Security=SSPI;').Northwind.dbo.orders
-- SQL Login
SELECT * FROM OPENDATASOURCE('SQLOLEDB',
'Data Source=itrainbo2000;Initial Catalog=Northwind;User ID=student;Password=#student#;').Northwind.dbo.orders
更多导入导出数据查看: http://blog.csdn.net/zhou__zhou/archive/2007/06/08/1644638.aspx
6、对一个值取反，如性别男=1，女=0,实现方式，可以使用case when ...，以下救命使用异或^
5、在SQL2005中，如何强制删除扩展存储过程xp_cmdshell
-- 1. 得到 master 库的目录位置(记下这个目录)
SELECT
LEFT(physical_name, LEN(physical_name) - 10)
FROM master.sys.database_files
WHERE type = 0
-- 2. 操作系统中打开上述目录，找到如下两个文件
mssqlsystemresource.mdf
mssqlsystemresource.ldf
将他们复制一份，改名为：
_mssqlsystemresource.mdf
_mssqlsystemresource.ldf
-- 3. 回到 sql server, 执行下面的语句（语句中的目录要改成步骤1中查到的目录）
USE master
-- 将下面的文件目录修改为你自己的
CREATE DATABASE _sys_resource
ON(
FILENAME = N'D:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData_mssqlsystemresource.mdf'
)
LOG ON(
FILENAME = N'D:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData_mssqlsystemresource.ldf'
)
FOR ATTACH
GO
USE _sys_resource
DROP PROC sys.xp_cmdshell
GO
EXEC sp_detach_db N'_sys_resource'
-- 要重启 sql 才生效，下面这句让 sql 停止
SHUTDOWN
GO
-- 4. 回到步骤 2 的目录，将下面两个文件改名以做备份
mssqlsystemresource.mdf
mssqlsystemresource.ldf
-- 5. 将下面两个文件改名，去掉前面的 _
_mssqlsystemresource.mdf
_mssqlsystemresource.ldf
-- 6. 重新启动 sql server, 再执行 xp_cmdshell, 你会发现找不到这个存储过程了
1、为什么UNION ALL比UNION更快
解：你如果还记得，union操作是将两个数据集合并在一起。它不会产生 重复或者多余的行。为了完成这样的效果，需要对这两张表执行SORT操作。这个SORT操作明显是计算密集的，并且会使用大量的内存。相反，UNION ALL仅仅是将两个集合的数据任意的合并在一起，而不管行是否重复。
2、INTERSECT和UNION的区别?
答：intersect是两个查询都有的非重复值(交集), union是两个查询结果的所有不重复值(并集)
3、如何将存储过程结果插入到临时表。
DECLARE @path VARCHAR(1000)
SET @path='c:\'
DECLARE @a TABLE(str0 VARCHAR(100))
INSERT INTO @a(str0) EXEC xp_subdirs 'c:\'
SELECT * FROM @a WHERE ISDATE(str0)=1
技巧：需要先知道存储过程返回的表结果结构，并预先创建相同的临时表结果。
4、SQL中的GUEST用户有什么用的？
guest 用户帐户允许没有用户帐户的登录访问数据库。当满足下列所有条件时，登录采用 guest 用户的标识：
1)登录有访问 Microsoft® SQL Server™ 实例的权限，但没有通过自己的用户帐户访问数据库的权限。
2)数据库中含有 guest 用户帐户。
可以将权限应用到 guest 用户，就如同它是任何其它用户帐户一样。可以在除 master 和 tempdb 外（在这两个数据库中它必须始终存在）的所有数据库中添加或删除 guest 用户。默认情况下，新建的数据库中没有 guest 用户帐户。
例如，若要将 guest 用户帐户添加到名为 Accounts 的数据库中，请在 SQL 查询分析器中运行下列代码：
USE Accounts
GO
EXECUTE sp_grantdbaccess guest
13--计算员工最近的生日的日期. 以Northwind数据库为例.
SELECT firstname,lastname,BirthDate,CASE WHEN DATEADD(yy,DATEDIFF(yy,BirthDate,GETDATE()),BirthDate)<=getdate() THEN DATEADD(yy,DATEDIFF(yy,BirthDate,GETDATE()),BirthDate) ELSE DATEADD(yy,DATEDIFF(yy,BirthDate,GETDATE())-1,BirthDate)END [最近一次生日] FROM Employees
--思路: 需要先得到今年的生日日期,如果获取的生日日期大于当前日期,就获取最近一次的生日日期(去年的生日日期,谁让生日一年一次呢.),如果小于等于当前日期,那这次就是最近的生日日期了.
--注意: 因为闰年的关系,二月的生日可能会在差异.
12、按汉字笔画排序
SELECT * FROM [YouTable]
ORDER BY [字段] COLLATE Chinese_PRC_Stroke_CI_AS_KS
11,从这一示例了解解决问题的新想法.
/**//*有一成绩表,数据如下:
Name Course Mark
---------- ---------- ----------------------
李四 数学 90
李四 语文 76
王五 英语 100
王五 语文 81
张三 数学 75
张三 语文 81
需求:查询出每门课都大于80分的学生姓名
结果:王五
*/
-- 方式一: not exists
SELECT name FROM @test a WHERE NOT EXISTS (SELECT 1 FROM @test WHERE a.Course=Course AND mark<80)
-- 另一个思路
SELECT NAME FROM @test GROUP BY name HAVING count(*)=sum(case when mark>=80 then 1 else 0 end)
10,全角半角转换函数. CREATE FUNCTION dbo.ufnConvert(
@str NVARCHAR(4000), --要转换的字符串
@flag bit --转换标志,0转换成半角,1转换成全角
)RETURNS NVARCHAR(4000)
/**//*******************************
函数功能: 半角全角转换函数.
创建日期: 2007-09-28
使用示例:
declare @s1 varchar(8000)
select @s1='中 ２-３４５６a７８ＳＴＵＶabｎ中国ｏｐｗｘｙｚ'
select dbo.ufnconvert(@s1,0),dbo.ufnconvert(@s1,1)
引用: 网摘.
*/
AS
BEGIN
DECLARE @pat nvarchar(8),@step int,@i int,@spc int
IF @flag=0
SELECT @pat=N'%[！-～]%',@step=-65248,
@str=REPLACE(@str,N' ',N' ')
ELSE
SELECT @pat=N'%[!-~]%',@step=65248,
@str=REPLACE(@str,N' ',N' ')
SET @i=PATINDEX(@pat COLLATE LATIN1_GENERAL_BIN,@str)
WHILE @i>0
SELECT @str=REPLACE(@str, SUBSTRING(@str,@i,1), NCHAR(UNICODE(SUBSTRING(@str,@i,1))+@step)),@i=PATINDEX(@pat COLLATE LATIN1_GENERAL_BIN,@str)
RETURN(@str)
END
GO
--结果:
半角 全角
中 2-3456,,,,,a78S,TUVabn中国opwxyz 中 ２－３４５６，，，，，ａ７８Ｓ，ＴＵＶａｂｎ中国ｏｐｗｘｙｚ
9、删除A表中只有id不同的相同记录。条件，保留id最大的记录。
DECLARE @t TABLE(id INT ,str1 VARCHAR(10),str2 VARCHAR(10),str3 VARCHAR(10))
INSERT INTO @t
SELECT 1,'11','22','33'
UNION
SELECT 3,'11','22','33'
UNION
SELECT 2,'11','22','33'
UNION
SELECT 4,'11','22','33'
--NOT EXISTS
SELECT * FROM @t a WHERE NOT EXISTS(SELECT id FROM @t WHERE a.id<id AND str1=a.[str1])
--NOT IN
SELECT * FROM @t where id NOT in
(select a.id from @t a, @t b
where a.str1 = b.str1 and a.ID < b.ID)
8、删除A表中B表不存在的记录
delete from a where not exists ( select * from b where a.Keyid=b.Keyid )
7、如何用SQL语句创建一个自动编号的字段
CREATE TABLE mytable(obj_id INT IDENTITY(1,1) NOT NULL)
6、重新生成ID增值列（以旧的ID列为条件）
select id,(id-(select min(id) from a)+1) as new_id from a
5、随机排序表查询结果。使用newid()函数
这种方法是要对整个表扫描，然后产生一个计算列再排序的，最好百万记录表作这样的操作，否则会很慢的。
SELECT * FROM Table ORDER BY NEWID()
4、检验数据库是否存在并做进一步操作。其它对象可举一反三，
比如表:IF object_id('[dbo].[Table]','U') IS NOT NULL
IF DB_ID(N'DBName')IS NOT NULL
PRINT 'DB exists'
ELSE
PRINT 'DB not exists'或: IF EXISTS (select * from master..sysdatabases where name = 'DBName)
PRINT 'DB exists'
ELSE
PRINT 'DB not exists'
3、删除表中的所有记录后,标识还是从1开始
--方法１：
truncate Table YourTable --這樣不但將數據刪除，而且可以重新置位identity屬性的字段。
--方法２：
Delete From YourTable
dbcc checkident(YourTable,reseed,0) --重新置位identity屬性的字段，讓其下個值從1開始
2、查看表结构
EXEC sp_mshelpcolumns 'object_Name'更多参考未公开存储过程
1、重命名列:
sp_rename 'a表.列名b','列名改为C','column' 更多参考联机帮助：更改用户创建对象的名称 sp_rename
0、为数据库表设计时选择最合理的数据类型。
例需求如下：定义一个新表来跟踪用户从在线商店购买商品的行为。要为每个列考虑合适的名字、数据类型、以及可空性选项。由于对存储有严格需求，所以尽量使用保守的数据类型。以下是表的设计准则：
* 定义1个单一列作为顾客的名字。允许1到200个字符且必须填写，一些顾客可能使用外国名。
* 需要4个列来保存顾客的地址、城市、州与邮政编码。所有的地址与城市只包含美国本土名字，而且这些列中没有一个是必须要有值的。地址列应该允许最多保存200个字符，城市应该保存100个字符，州永远是一个2字节的缩写，而邮政编码长度总是5个字节的。
* 需要1个列来保存商品的购买数量，这个列将保存整数值，最大为10 000，而且必须将新记录自动设置为1。
* 需要1个列来存储所支付的金额，这是必须要填写的字段。存储的值最高为$100 000.
我的需求分析：虽然习惯上认为应该要定义主键列，但需求并没有指定这一点。CustomerName列被定义为nvarchar类型的，这样就可以使用国际字符集储存Unicode字符了。虽然这样做将会使得存储需求加倍，但是却最可能高的效率符合了需求。
Address0 与City列不需要Unicode能力，所以它们使用 varchar 数据类型。State列使用固定长度的类型，这是因为与varchar类型有关的开销至少是2字节，即使这个列是空值。ZipCode列要么定义为 char(5)，要么定义为varchar(5)类型，使用char类型保存小一点的值可以稍微提升性能，不过即使是空值，char也要占用5个字节的存 储空间。虽然在这个例子中使用char类型可能更实际一些，但需求要求我们减少存储空间。SmallMoney类型虽然并不常用，不过它能减少存储空间， 并且满足最大值的需要。所以题解如下：
CREATE TABLE MyTable(CustomerName nVARCHAR(200) NOT NULL
,Address0 VARCHAR(200) NULL
,City VARCHAR(100) NULL
,State0 CHAR(2) NULL
,ZipCode VARCHAR(5)
,Quantity INT NOT NULL DEFAULT 1
,Price SmallMoney NOT NULL
,CONSTRAINT ck_state0 CHECK (state0 IN ('WA','OR'))
,CONSTRAINT ck_Quantity CHECK (Quantity BETWEEN 1 AND 10000)
,CONSTRAINT ck_price CHECK (price BETWEEN $0 AND $100000))
DECLARE @tb TABLE (sex INT ,num2 INT)
INSERT INTO @tb SELECT 1,9
INSERT INTO @tb SELECT 0,0
INSERT INTO @tb SELECT 0,1
INSERT INTO @tb SELECT 1,9
INSERT INTO @tb SELECT 0,1
INSERT INTO @tb SELECT 1,1
INSERT INTO @tb SELECT 0,9
INSERT INTO @tb SELECT 1,1
INSERT INTO @tb SELECT 0,9
INSERT INTO @tb SELECT 1,1
SELECT sex,sex取反=sex^1,num2,num2取反=num2^9 FROM @tb
本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/zhou__zhou/archive/2007/07/24/1705545.aspx
