鱼常游而忘飞

星期二, 三月 24, 2009

SQL Server分页SQL

sql server 2005

WITH OrderedOrders AS
(SELECT *,
ROW_NUMBER() OVER (order by [id])as RowNumber　　--id是用来排序的列
FROM table_info ) --table_info是表名
SELECT *
FROM OrderedOrders
WHERE RowNumber between 50 and 60;

http://www.ljnid.cn/?id=145

SELECT TOP 页大小 *
FROM
Users
WHERE
(ID NOT IN (SELECT TOP (页大小*(页数-1)) ID FROM Users ORDER BY ID DESC))
ORDER BY
ID DESC

http://blog.csdn.net/leizhipan/archive/2006/10/01/1313508.aspx
重要参考页：
http://icansoft.blog.51cto.com/268543/54310

标签： SQL, SQL Server

星期二, 十一月 11, 2008

SQL Server中使用正則表達式

这两天有个需求，需要在数据库中判断字符串的格式，于是从网上搜集了一些资料，整理了一下。

下面这个是一个自定义函数，用户可以调用这个函数判断指定的字符串是否符合正则表达式的规则.
CREATE FUNCTION dbo.find_regular_expression
(
@source varchar(5000), --需要匹配的源字符串
@regexp varchar(1000), --正则表达式
@ignorecase bit = 0 --是否区分大小写，默认为false
)
RETURNS bit --返回结果0-false，1-true
AS
BEGIN

--0（成功）或非零数字（失败），是由 OLE 自动化对象返回的 HRESULT 的整数值。
DECLARE @hr integer

--用于保存返回的对象令牌，以便之后对该对象进行操作
DECLARE @objRegExp integer DECLARE @objMatches integer

--保存结果
DECLARE @results bit

/*
创建 OLE 对象实例,只有 sysadmin 固定服务器角色的成员才能执行 sp_OACreate,并确定机器中有VBScript.RegExp类库
*/
EXEC @hr = sp_OACreate 'VBScript.RegExp', @objRegExp OUTPUT
IF @hr <> 0 BEGIN
SET @results = 0
RETURN @results
END
/*
以下三个分别是设置新建对象的三个属性。下面是'VBScript.RegExp'中常用的属性举例：
Dim regEx,Match,Matches '建立变量。
Set regEx = New RegExp '建立一般表达式。
regEx.Pattern= patrn '设置模式。
regEx.IgnoreCase = True '设置是否区分大小写。
regEx.Global=True '设置全局可用性。
set Matches=regEx.Execute(string) '重复匹配集合
RegExpTest = regEx.Execute(strng) '执行搜索。
for each match in matches '重复匹配集合
RetStr=RetStr &"Match found at position "
RetStr=RetStr&Match.FirstIndex&".Match Value is '"
RetStr=RetStr&Match.Value&"'."&vbCRLF Next
RegExpTest=RetStr

*/
EXEC @hr = sp_OASetProperty @objRegExp, 'Pattern', @regexp
IF @hr <> 0 BEGIN
SET @results = 0
RETURN @results
END
EXEC @hr = sp_OASetProperty @objRegExp, 'Global', false
IF @hr <> 0 BEGIN
SET @results = 0
RETURN @results
END
EXEC @hr = sp_OASetProperty @objRegExp, 'IgnoreCase', @ignorecase
IF @hr <> 0 BEGIN
SET @results = 0
RETURN @results
END
--调用对象方法
EXEC @hr = sp_OAMethod @objRegExp, 'Test', @results OUTPUT, @source
IF @hr <> 0 BEGIN
SET @results = 0
RETURN @results
END
--释放已创建的 OLE 对象
EXEC @hr = sp_OADestroy @objRegExp
IF @hr <> 0 BEGIN
SET @results = 0
RETURN @results
END
RETURN @results
END

下面是一个简单的测试sql语句，可以直接在查询分析器中运行。
DECLARE @intLength AS INTEGER
DECLARE @vchRegularExpression AS VARCHAR(50)
DECLARE @vchSourceString as VARCHAR(50)
DECLARE @vchSourceString2 as VARCHAR(50)
DECLARE @bitHasNoSpecialCharacters as BIT

-- 初始化变量
SET @vchSourceString = 'Test one This is a test!!'
SET @vchSourceString2 = 'Test two This is a test'

-- 我们的正则表达式应该类似于
-- [a-zA-Z ]{}
-- 如： [a-zA-Z ]{10} ... 一个十字符的字符串

-- 获得字符串长度
SET @intLength = LEN(@vchSourceString)

-- 设置完整的正则表达式
SET @vchRegularExpression = '[a-zA-Z ]{' + CAST(@intLength as varchar) + '}'

-- 是否有任何特殊字符
SET @bitHasNoSpecialCharacters = dbo.find_regular_expression(@vchSourceString, @vchRegularExpression,0)

PRINT @vchSourceString
IF @bitHasNoSpecialCharacters = 1 BEGIN
PRINT 'No special characters.'
END ELSE BEGIN
PRINT 'Special characters found.'
END

PRINT '**************'

-- 获得字符串长度
SET @intLength = LEN(@vchSourceString2)

-- 设置完整的正则表达式
SET @vchRegularExpression = '[a-zA-Z ]{' + CAST(@intLength as varchar) + '}'

-- 是否有任何特殊字符
SET @bitHasNoSpecialCharacters = dbo.find_regular_expression(@vchSourceString2, @vchRegularExpression,0)

PRINT @vchSourceString2
IF @bitHasNoSpecialCharacters = 1 BEGIN
PRINT 'No special characters.'
END ELSE BEGIN
PRINT 'Special characters found.'
END
GO

http://blog.csdn.net/weir55/archive/2008/04/28/2337096.aspx

标签： RegExp, SQL, SQL Server

星期日, 五月 04, 2008

sql server 远程访问

在数据库的各种应用程序开发中，连接数据库是数据库应用程序开发的第一步，同时也是最重要的一步。而对于不同的数据库他们的连接模式各有不同，对应的连接串也不同。
程序员可能都有这样的经历，有时不知道连接数据库所需要的连接串究竟如何写或者经常写错而导致不能正确访问数据库。当然很多编程工具能够通过可视化的界面直接产生正确的连接字符串，但字符串中各个参数的具体含义也不清楚，经常混淆。本文就针对大部分常用数据库列举出不同连接方法所需要的连接字符串并加以说明，以便程序员参考！

Sql Server
· ODBC
o 标准连接（Standard Security）:
"Driver={SQL Server};Server=Aron1;Database=pubs;Uid=sa;Pwd=asdasd;"
1）当服务器为本地时Server可以使用(local);
"Driver={SQL Server};Server=(local);Database=pubs;Uid=sa;Pwd=asdasd;"
2）当连接远程服务器时，需指定地址、端口号和网络库
"Driver={SQL Server};Server=130.120.110.001;Address=130.120.110.001,1052;Network=dbmssocn;
Database=pubs;Uid=sa;Pwd=asdasd;"
注：Address参数必须为IP地址，而且必须包括端口号
o 信任连接（Trusted connection）: （Microsoft Windows NT 集成了安全性）
"Driver={SQL Server};Server=Aron1;Database=pubs;Trusted_Connection=yes;"
或者
"Driver={SQL Server};Server=Aron1;Database=pubs; Uid=;Pwd=;"
o 连接时弹出输入用户名和口令对话框:
Conn.Properties("Prompt") = adPromptAlways
Conn.Open "Driver={SQL Server};Server=Aron1;DataBase=pubs;"
· OLE DB, OleDbConnection (.NET)
o 标准连接（Standard Security）:
"Provider=sqloledb;Data Source=Aron1;Initial Catalog=pubs;User Id=sa;Password=asdasd;"
o 信任连接（Trusted connection）:
"Provider=sqloledb;Data Source=Aron1;Initial Catalog=pubs;Integrated Security=SSPI;"
(如果连接一个具体的已命名SQLServer实例,使用Data Source=Servere NameInstance Name;但仅适用于 SQLServer2000)例如：”Provider=sqloledb;Data Source=MyServerNameMyInstanceName;Initial Catalog=MyDatabaseName;User Id=MyUsername;Password=MyPassword;”
o 连接时弹出输入用户名和口令对话框:
Conn.Provider = "sqloledb"
Conn.Properties("Prompt") = adPromptAlways
Conn.Open "Data Source=Aron1;Initial Catalog=pubs;"
o 通过IP地址连接:
"Provider=sqloledb;Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=pubs;User ID=sa;Password=asdasd;"
(DBMSSOCN=TCP/IP代替Named Pipes, Data Source的末尾是需要使用的端口号(缺省为1433))
· SqlConnection (.NET)
o 标准连接（Standard Security）:
"Data Source=Aron1;Initial Catalog=pubs;User Id=sa;Password=asdasd;"
或者
"Server=Aron1;Database=pubs;User ID=sa;Password=asdasd;Trusted_Connection=False"
(这两个连接串的结果相同)
o 信任连接（Trusted connection）:
"Data Source=Aron1;Initial Catalog=pubs;Integrated Security=SSPI;"
或者
"Server=Aron1;Database=pubs;Trusted_Connection=True;"
(这两个连接串的结果相同)
(可以用serverNameinstanceName代替Data Source，取值为一个具体的SQLServer实例,但仅适用于 SQLServer2000)
o 通过IP地址连接:
"Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=pubs;User ID=sa;Password=asdasd;"
(DBMSSOCN=TCP/IP代替Named Pipes, Data Source的末尾是需要使用的端口号(缺省为1433))
o SqlConnection连接的声明:
C#:
using System.Data.SqlClient;
SqlConnection SQLConn = new SqlConnection();
SQLConn.ConnectionString="my connectionstring";
SQLConn.Open();

VB.NET:
Imports System.Data.SqlClient
Dim SQLConn As SqlConnection = New SqlConnection()
SQLConn.ConnectionString="my connectionstring"
SQLConn.Open()
· Data Shape
o MS Data Shape
"Provider=MSDataShape;Data Provider=SQLOLEDB;Data Source=Aron1;Initial Catalog=pubs;User ID=sa;Password=asdasd;"
· 更多
o 如何定义使用哪个协议
§ 举例:
"Provider=sqloledb;Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=pubs;User ID=sa;Password=asdasd;"
名称网络协议库
dbnmpntw Win32 Named Pipes
dbmssocn Win32 Winsock TCP/IP
dbmsspxn Win32 SPX/IPX
dbmsvinn Win32 Banyan Vines
dbmsrpcn Win32 Multi-Protocol (Windows RPC)
§ 重要提示
当通过SQLOLEDB提供者进行连接时使用以下语法：
Network Library=dbmssocn
但通过MSDASQL提供者进行连接时使用以下语法：
Network=dbmssocn
o 所有SqlConnection连接串属性
§ 下表显示了ADO.NET SqlConnection对象的所有连接串属性. 其中大多数的属性也在ADO中使用.所有属性和描述来自于msdn.
名称缺省值描述
Application Name 应用程序名称或者当没有提供应用程序时为.Net SqlClient数据提供者
AttachDBFilename或者extended properties或者Initial File Name 主要文件的名字，包括相关联数据库的全路径。数据库名字必须通过关键字'database'来指定。
Connect Timeout或者Connection Timeout 15 在中止连接请求，产生错误之前等待服务器连接的时间（以秒为单位）
Connection Lifetime 0 当一个连接返回到连接池，当前时间与连接创建时间的差值，如果时间段超过了指定的连接生存时间，此连接就被破坏。它用于聚集设置中在运行服务器和准备上线的服务器之间强制负载平衡。
Connection Reset 'true' 当连接从连接池移走时决定是否重置数据库连接。当设置为'false'时用于避免获得连接时的额外服务器往复代价。
Current Language SQL Server语言记录名称
Data Source或Server或Address或Addr或Network Address 要连接的SQL Server实例的名字或者网络地址
Enlist 'true' 为真时，连接池自动列出创建线程的当前事务上下文中的连接。
Initial Catalog或Database 数据库名
Integrated Security或者Trusted_Connection 'false' 连接是否为信任连接。其取值为'true'， 'false'和'sspi'（等于'true'）.
Max Pool Size 100 连接池中允许的最大连接数
Min Pool Size 0 连接池中允许的最小连接数
Network Library或Net 'dbmssocn' 网络库用于建立与一个 SQL Server实例的连接。值包括dbnmpntw (命名管道), dbmsrpcn (多协议), dbmsadsn (Apple Talk), dbmsgnet (VIA), dbmsipcn (共享内存) 和 dbmsspxn (IPX/SPX), 和 dbmssocn (TCP/IP). 所连接的系统必须安装相应的动态链接库。如果你没有指定网络，当你使用一个局部的服务器 (例如, "." 或者 "(local)"),将使用共享内存
Packet Size 8192 与 SQL Server的一个实例通讯的网络包字节大小
Password-或Pwd SQL Server帐户登录口令
Persist Security Info 'false' 设置为'false',当连接已经打开或者一直处于打开状态时，敏感性的安全信息（如口令）不会返回作为连接的一部分信息。
Pooling 'true' 为真时,从合适的连接池中取出SQLConnection对象,或者必要时创建SQLConnection对象并把它增加到合适的连接池中。
User ID SQL Server登录用户
Workstation ID the local computer name 连接到SQL Server的工作站名称
§ 注意：
使用分号分隔每个属性
如果一个名字出现多于两次，在连接串中的最后一次出现的值将被使用。
如果你通过在应用中由用户输入字段的值来构建连接串，你必须保证用户不会通过用户值里的另一个值插入到一个额外的属

性来改变连接串。

http://topic.csdn.net/t/20041223/10/3669416.html

标签： ADO, SQL Server, 远程访问

星期五, 四月 18, 2008

Oracle与SQL Server的“相对论”

本文的作者Sean McCown是一位资深的数据库工作者，在数据库行业摸爬滚打15年了。目前的工作是解答数据库相关的评论。以下是Sean McCown在解答评论过程中所感所悟。

　　Sean McCown发表在博客中的内容如下：

　　我实在对社区里的有些评论不敢苟同(有的评论的水平很差)，更多的是希望快速回答，完成工作。

　　我首先要说的是一件令我非常烦恼的事情。我已经在数据库行业摸爬滚打15年了。虽说不是对数据库的所有的知识都一清二楚，但是我对某些知识还是数一数二的。因此，你们不要一而再，再而三的提醒我，“SQL”和“SQL Server”之间是有区别的。你想说的区别我已经略知一二了。诚然在网上post很长的文章的时候，最好还是能够让你的听众清楚的明白你在说什么。当我谈到Oracle 和 SQL Server的时候,我应该可以将SQL Server简写为SQL，不用每次都写出它的全部拼写。如果有时候让你感到困惑，我对此深表歉意，但是以后你应该记住，很好的理解我所指的对象。很清楚，我不是在谈论SQL语言。甚至有一个家伙说过我应该写Microsoft SQL Server。这样诚然不错……但是，当我说到Oracle的时候，我也没有看到你们有谁感到迷惑。Oracle是一个公司的名字。他们生产了很多的产品。为什么没有人通过Email告诉我我应该称之为Oracle Database 11g呢?

　　我想你们已经知道了我缩写所指吧，不用每次都写全称吧。好了，这就是我在进入正题前所要讲的，真是一吐为快啊。

　　现在进入正题。你们当中有一些人给出了非常清楚、明晰、深思熟虑的评论。而其他人一些人没有说出实质性的内容，还进行漫骂。这也没有关系，我不会对后者做过多的关注。

　　其中非常优秀的评论来自从事Oracle工作的人士。这就不奇怪他们写的评论很有深度。他们是一群非常聪明的群体，他们能够证明自己的观点正确，而不是漫骂。

　　下面一幕是我又觉非常有意思的。如果我们在MS(微软公司的简称)平台上布置一项任务，我们就很有可能得到差不多的解决方案，这个现象得到了很多人的同意，他们也是这么认为的。如果我们在Oracle平台上布置与MS(微软公司的简称)平台相同的任务的话，很难找到答案。并不是说这里没有该任务的解决方案，只是不像MS平台下那么容易得到而已。

　　也许有许多人认为这只是我的一家之言，因为他们在Oracle Database 9i/10g/11g上从来没有遇到寻求帮助的麻烦。也许我们都是对的。这取决于你的经历了。事实上，我身边有一群IT工作者，在自己的blog上post自己所遇到的麻烦，他们中大多数同意我的观点。这些人大多都是一些杂货店的数据库管理员。

　　但是，我想要声明的是虽然有许多人不承认Oracle数据库学习资源很难找到，但是事实上就是如此，MS学习资源就是更加容易获得。

　　有人或许就要问我，为什么我不去OTN(Oracle 技术网)那里呆着。事实上，我就在那里，但是，伙计，你要知道的是，我已经厌倦了坐在电脑前面，要挑选如此多的网页找到需要回答的问题。如果坐下来观看视频的话，岂不是一件愉快的事情。我个人觉得视频资源更好。因此我喜欢视频培训。但是问题来了，几乎没有提供商来做Oracle新产品(Oracle Database 9i/10g/11g)的视频培训。事实上，到目前为止，我还不知道有任何的公司在做Oracle培训。

　　拿AppDev举例子来说。他们有非常棒的培训，但是他们只是做MS公司的产品。甚至其它做视频培训的提供商对Oracle也择道而行。这是什么原因了?这就是我一开始讨论的关键所在。难道是没有人用Oracle?我们知道这肯定是不正确的。难道是Oracle数据库管理员天生就知道 Oracle所有的知识?当让也不是。难道是由于Oracle社区的存在使得培训提供商选择离开Oracle这个产品?事实上Oracle也没有生产出比提供商更好的培训教程。

　　难道主要原因是Oracle大多用在Unix/Linux操作系统上，因此Oracle的技术人员更加喜欢读文档而不是观看视频?这对吗?这些Oracle的技术人员都是用的命令行操作，因此不能忍受使用图形界面的软件，即使是学习这样的事情?的确，这已经成为一种习惯，或者说是一种氛围。这个发现是从Oracle社区里找到的。另外，你所需要的相关信息在Oracle社区里并不是没有，而是不容易发现而已。

　　就拿这次我安装Data Guard的经历来说，我需要OBE(oracle示例系列)。问题是那些OBE(oracle示例系列)是在Linux环境下使用的，而我需要的是在Windows下使用的。官方网站上的一些链接并不是跳转的很合理，如果有微小的错误存在，更本就不会有任何提示让你后退到原来的路径。另外，有一些阅读信息必须严格执行，而一些则就很随便了，OBE(oracle示例系列)并没有给你任何引导，那种OBE(oracle示例系列)对应哪个平台。最后，我好不容易找到了一个是适合Windows的。但是，并不是很好。因此，你也不要问我如何完成OBE(oracle示例系列)。回过头来，该想想为什么没有对Oracle培训的一种需求，形式内容更加丰富的需求呢?

　　仍然还有一些人说我很愚蠢，因为Oracle比SQL更加容易做技术支持。

　　在这里我也不想找更多的例子了。只需要比较如何备份Oracle和备份SQL就知道谁的资料好找。

　　另外我还要给出管理表空间(tablespace) 的例子，说到这里，肯定有人说管理表空间(tablespace)太easy了，因为他们一直就是在干这种事情，一条简单的命令就ok了。是的，对这些人来说很简单，正好证明了我说的观点：孰能生巧。在SQL数据库中，我不用去管理表空间(tablespace)(或者文件组filegroups)，因为它们是自动增长的。唯一一次我需要管理文件组filegroups是由于我设定了增长范围，而后来增长溢出了，或者我磁盘空间不够了。在Oracle中，如果我将块大小(Block Size)设置错误的话，那么结果就只能变得很大，我必须去管理它，因为那些表格将会比我想象的还要大。现在在11g有一个bigfile参数，最近才有的。

　　数据库平台已经很明显在人们心目中形成了各自的印象，不管MS采取什么样的措施，他们不会改变些那些觉得MS是三流产品的人的看法。这已经是一个事实了。至于说那个更好，就主要取决于你拿他们做什么了。我发现SQL更加容易组织，运行和构建应用程序。对于普通人来说，Oracle太复杂了，不但是看看一本书，一个CD就能上手的。

　　但是，但你看Oracle的 Data Guard时，它就比SQL反射法要先进很多了。

　　好了，已经写了足够多了。至少我觉得是很多了。需要声明的是，我并不是说Oracle平台的坏话。但是，对于用户来说，它的确不太容易掌握。

http://news.csdn.net/n/20080417/115263.html

标签： Oracle, SQL Server

星期四, 四月 17, 2008

SQLSERVER数据库全文索引示例

一个完整的SQL SERVER数据库全文索引示例。（以pubs数据库为例）

一、首先，介绍利用系统存储过程创建全文索引的具体步骤：
1) 启动数据库的全文处理功能 (sp_fulltext_database)
2) 建立全文目录 (sp_fulltext_catalog)
3) 在全文目录中注册需要全文索引的表 (sp_fulltext_table)
4) 指出表中需要全文索引的列名 (sp_fulltext_column)
5) 为表创建全文索引 (sp_fulltext_table)
6) 填充全文目录 (sp_fulltext_catalog)

二、示例，以对pubs数据库的title和notes列建立全文索引，之后使用索引查询title列或notes列中包含有datebase 或computer字符串的图书名称：
在这之前，需要安装Microsoft Search服务，启动SQL server全文搜索服务

user pubs --打开数据库
go
--检查数据库pubs是否支持全文索引，如果不支持则使用sp_fulltext_database 打开该功能
if(select databaseproperty('pubs','isfulltextenabled'))=0
execute sp_fulltext_database 'enable'

--建立全文目录FT_PUBS
execute sp_fulltext_catalog 'FT_pubs','create'

--为title表建立全文索引数据元
execute sp_fulltext_table 'title','create','FT_pubs','UPKCL_titleidind'

--设置全文索引列名
execute sp_fulltext_column 'title','title','add'
execute sp_fulltext_column 'title','notes','add'

--建立全文索引，activate，是激活表的全文检索能力，也就是在全文目录中注册该表
execute sp_fulltext_table 'title','activate'

--填充全文索引目录
execute sp_fulltext_catalog 'FT_pubs','start_full'
go

--检查全文目录填充情况
While fulltextcatalogproperty('FT_pubs','populateStatus') <>0
begin
--如果全文目录正处于填充状态，则等待30秒后再检测一次
waitfor delay '0:0:30'
end

三、全文目录填充完成后，即可使用全文目录检索

select * from title
where CONTAINS(title,'database')
or CONTAINS(title,'computer')
or CONTAINS(notes,'database')
or CONTAINS(notes,'database')

四、以下介绍一下全文操作类的系统存储过程

过程名称：sp_fulltext_service
执行权限：serveradmin或系统管理员
作用：设置全文搜索属性

过程名称：sp_fulltext_catalog
执行权限：db_owner及更高角色成员
作用：创建和删除一个全文目录，启动或停止一个全文目录的索引操作

过程名称：sp_fulltext_database
执行权限：db_owner角色成员
作用：初始化全文索引或删除数据库中所有全文目录

过程名称：sp_fulltext_table
执行权限：db_ddladnmin或db_owner角色成员
作用：将一个表标识为全文索引表或非全文索引表

过程名称：sp_fulltext_column
执行权限：db_ddladnmin角色成员
作用：指出一个全文索引表中的那些列假如或退出全文索引

http://blog.chinaunix.net/u/10889/showart.php?id=529099

标签： Full Text Search, SQL Server

星期一, 四月 07, 2008

Oracle/MS SQL Server的数据库多表关联更新UPDATE与多表更新

一条Update更新语句是不能更新多张表的，除非使用触发器隐含更新。而表的更新操作中，在很多情况下需要在表达式中引用要更新的表以外的数据。我们先来讨论根据其他表数据更新你要更新的表

一、MS SQL Server 多表关联更新

sql server提供了update的from 子句，可以将要更新的表与其它的数据源连接起来。虽然只能对一个表进行更新，但是通过将要更新的表与其它的数据源连接起来，就可以在update的表达式中引用要更新的表以外的其它数据。
一般形式：

update A SET 字段1=B表字段表达式, 字段2=B表字段表达式 from B WHERE 逻辑表达式

例如：

UPDATE dbo.Table2
SET dbo.Table2.ColB = dbo.Table2.ColB + dbo.Table1.ColB
FROM dbo.Table2
INNER JOIN dbo.Table1
ON (dbo.Table2.ColA = dbo.Table1.ColA);

实际更新的操作是在要更新的表上进行的，而不是在from子句所形成的新的结果集上进行的

二、Oracle 多表关联更新

Oracle没有update from语法，可以通过两种实现方式：

1、利用子查询：
update A
SET 字段1=（select 字段表达式 from B WHERE ...）,
字段2=（select 字段表达式 from B WHERE ...）
WHERE 逻辑表达式

UPDATE多个字段两种写法：

写法一：

UPDATE table_1 a
SET col_x1 = (SELECT b.col_y1, b.col_y2 FROM table_2 b WHERE b.col_n = a.col_m),
col_x2 = (SELECT b.col_y2 FROM table_2 b WHERE b.col_n = a.col_m)
WHERE EXISTS (SELECT * FROM table_2 b WHERE b.col_n = a.col_m)

或

UPDATE table_1 a
SET col_x1 = (SELECT b.col_y1, b.col_y2 FROM table_2 b WHERE b.col_n = a.col_m),
col_x2 = (SELECT b.col_y2 FROM table_2 b WHERE b.col_n = a.col_m)
WHERE a.col_m=(SELECT b.col_n FROM table_2 b WHERE b.col_n = a.col_m)

写法二：

UPDATE table_1 a
SET (col_x1, col_x2) = (SELECT b.col_y1, b.col_y2 FROM table_2 b WHERE b.col_n = a.col_m)
WHERE EXISTS (SELECT * FROM table_2 b WHERE b.col_n = a.col_m);

或

UPDATE table_1 a
SET (col_x1, col_x2) = (SELECT b.col_y1, b.col_y2 FROM table_2 b WHERE b.col_n = a.col_m)
WHERE a.col_m=(SELECT b.col_n FROM table_2 b WHERE b.col_n = a.col_m)

注意：

1. 对于子查询的值只能是一个唯一值，不能是多值。
2. 子查询在绝大多数情况下，最后面的where EXISTS子句是重要的，否则将得到错误的结果。且where EXISTS子句可用另一方法代替，如上。

2、利用视图：

UPDATE (SELECT A.NAME ANAME,B.NAME BNAME FROM A,B WHERE A.ID=B.ID)
SET ANAME=BNAME;

注意：

1. 对于视图更新的限制：
如果视图基于多个表的连接，那么用户更新（update）视图记录的能力将受到限制。除非update只涉及一个表且视图列中包含了被更新的表的整个主键，否则不能更新视图的基表。

另外，Oracle中的Delete的from子句也没有多表联接的功能，只能通过子查询的方式来做：
delete from 表A where exists (select * from 表B where 表A.empid=表B.empid)
delete from 表A where 表A.empid in (select empid from 表B)

三、oracle视图多表更新

在oracle中通常如果视图的数据源来自单表则该视图可以进行更新。而如果视图数据源来自两个以上表时这个视图是不可更新的。但有时候为了操作的方便我们更希望能够对多表视图也进行更新。

这时候我们可以通过建立更新触发器来替代该视图原有更新以达到多表更新的效果

例如：

3.1 创建测试数据表
--===================================================
--创建测试表
--===================================================
Drop Table t1;
Drop Table t2;
create table t1
( t11 numeric(28),t12 varchar2(20));
create table t2
( t11 numeric(28),t22 varchar2(20));

3.2 多表视图范例
--===================================================
--创建测试视图
--===================================================
create Or Replace view t as
select T1.t11 f1 ,T1.t12 f2 ,T2.t22 f3
from T1,T2
Where T1.t11=T2.t11;

3.3 多表视图触发器范例
--===================================================
--创建视图的替代触发器
--===================================================
Create Or Replace Trigger Trg_InsUpdDel_t
Instead Of Insert or update or delete
on t
for each row
Declare
begin
If Inserting Then
Insert Into t1 (t11,t12) Values (:New.f1,:New.f2);
Insert Into t2 (t11,t22) Values (:New.f1,:New.f3);
elsif Updating Then
Update t1 set t11=:New.f1,t12=:New.f2 where t11=:New.f1;
Update t2 set t11=:New.f1,t22=:New.f3 where t11=:New.f1;
elsif Deleting then
Delete from t1 where t11=:Old.f1;
Delete from t2 where t11=:Old.f1;
End if;
end;
如此即实现多表可更新视图的定义工作。

但要注意当视图进行重新编译的时候这个触发器会失效需要重建。

http://hi.baidu.com/suofang/blog/item/249c067b40c8def00ad187aa.html

标签： Oracle, SQL, SQL Server

星期三, 四月 02, 2008

SQL Server判断阻塞发生在某张表上

1. 使用sp_lock进行察看，如果输出信息 locktype 中包含 -blk 字符，说明当前表 table_id 的 page 上的锁对其他进程造成了阻塞
2. 通过察看 master..syslocks 系统表
例
id dbid page type spid class fid context row loid
----------- ------ ----------- ------ ------ ------------------------------ ------ ------- ------ -----------
512004855 6 0 3 13 Non Cursor Lock 0 0 0 26
512004855 6 0 4 12 Non Cursor Lock 0 0 0 24
512004855 6 0 3 10 Non Cursor Lock 0 0 0 20
512004855 6 793 261 13 Non Cursor Lock 0 8 0 26

从spid可知道当前阻塞及被阻塞的进程，并且从type字段可以知道锁的类型，见下表：
1 0x1 排它表锁
2 0x2 共享表锁
3 0x3 排它意图锁
4 0x4 共享意图锁
5 0x5 排它页锁
6 0x6 共享页锁
7 0x7 更新页锁
8 0x8 排它行锁
9 0x9 共享行锁
10 0xA 更新行锁
11 0xB 共享下一键锁
256 0x100 锁在阻塞另一个进程
512 0x200 请求锁

所以你可以通过下面的查询获得阻塞表信息
select b.name as dbname,a.id as table_id,page,spid from syslocks a,sysdatabases b
where b.dbid = a.dbid
and a.type & 256 = 256

你也可以写成一个存储过程，不过一定要在master中执行。

这样更好：

1. 使用sp_who进行察看，如果输出信息中blk字段不为0，则表明有点问题，问题与blk对应的值有关系。
fid spid status loginame origname hostname blk dbname cmd
------ ------ ------------ ------------ ------------ ---------- ----- ---------- ----------------
......
0 32 recv sleep c7mon c7mon e3500 0 c7db BULK INSERT
......
0 37 recv sleep c7mon c7mon e3500 0 c7db AWAITING COMMAND
0 38 lock sleep c7mon c7mon e3500 32 c7db DROP TABLE
0 40 recv sleep c7mon c7mon e3500 0 c7db BULK INSERT

或：使用sp_lock进行察看，如果输出信息 locktype 中包含 -blk 字符，说明当前表 table_id 的 page 上的锁对其他进程造
成了阻塞
fid spid locktype table_id page row dbname class context
------ ------ ---------------------------- ----------- ----------- ------ --------------- ------------------------------ ----------------------------
......
0 31 Ex_intent 1821965567 0 0 c7db Non Cursor Lock Fam dur
0 32 Ex_intent-blk 1569440665 0 0 c7db Non Cursor Lock Fam dur
0 33 Ex_intent 42483230 0 0 c7db Non Cursor Lock Fam dur

2. 通过察看 master..syslocks 系统表
例
id dbid page type spid class fid context row loid
----------- ------ ----------- ------ ------ ------------------------------ ------ ------- ------ -----------
512004855 6 0 3 13 Non Cursor Lock 0 0 0 26
512004855 6 0 4 12 Non Cursor Lock 0 0 0 24
512004855 6 0 3 10 Non Cursor Lock 0 0 0 20
512004855 6 793 261 13 Non Cursor Lock 0 8 0 26

从spid可知道当前阻塞及被阻塞的进程，并且从type字段可以知道锁的类型，见下表：
1 0x1 排它表锁
2 0x2 共享表锁
3 0x3 排它意图锁
4 0x4 共享意图锁
5 0x5 排它页锁
6 0x6 共享页锁
7 0x7 更新页锁
8 0x8 排它行锁
9 0x9 共享行锁
10 0xA 更新行锁
11 0xB 共享下一键锁
256 0x100 锁在阻塞另一个进程
512 0x200 请求锁

所以你可以通过下面的查询获得阻塞表信息:

select b.name as tablename,page,spid
from master..syslocks a , sysobjects b
where b.id=a.id and spid=blk_value （目前来看是32）
如：
tablename page spid
------------------------------ ----------- ------
t20030228 0 32

也就是说32 BULK INSERT 和 38 DROP TABLE都是在处理 t20030228 表，导致s锁。

http://www.chinaunix.net/jh/21/9547.html

标签： SQL Server

SQL Server 2000 Collection排序规则

函数调用实例：

select dbo.fun_getbh('中华人民共和国'),dbo.fun_getbh('中華人民共和國')　

执行结果：笔划总数分别为39和46，简繁体都行。

当然，你也可以把上面“UNION　ALL”内的汉字和笔划改存在固定表内，在汉字列建CLUSTERED INDEX，列排序规则设定为：

Chinese_PRC_Stroke_CS_AS_KS_WS

这样速度更快。假如你用的是BIG5码的操作系统，你得另外生成汉字，方法一样。但有一点要记住：这些汉字是通过SQL语句Select出来的，不是手工输入的，更不是查字典得来的，因为新华字典究竟不同于UNICODE字符集，查字典的结果会不正确。　　

用排序规则的特性得到汉字拼音首字母

用得到笔划总数相同的方法，我们也可以写出求汉字拼音首字母的函数。如下：

create  function  fun_getPY(@str  nvarchar(4000))
returns  nvarchar(4000)
as
begin
declare  @word  nchar(1),@PY  nvarchar(4000)
set  @PY=''
while  len(@str)>0
begin
set  @word=left(@str,1)
--假如非汉字字符，返回原字符
set  @PY=@PY (case  when  unicode(@word)  between  19968  and  19968 20901
then  (select  top  1  PY  from  (
select  'A'  as  PY,N'驁'  as  word
union  all  select  'B',N'簿'
union  all  select  'C',N'錯'
union  all  select  'D',N'鵽'
union  all  select  'E',N'樲'
union  all  select  'F',N'鰒'
union  all  select  'G',N'腂'
union  all  select  'H',N'夻'
union  all  select  'J',N'攈'
union  all  select  'K',N'穒'
union  all  select  'L',N'鱳'
union  all  select  'M',N'旀'
union  all  select  'N',N'桛'
union  all  select  'O',N'漚'
union  all  select  'P',N'曝'
union  all  select  'Q',N'囕'
union  all  select  'R',N'鶸'
union  all  select  'S',N'蜶'
union  all  select  'T',N'籜'
union  all  select  'W',N'鶩'
union  all  select  'X',N'鑂'
union  all  select  'Y',N'韻'
union  all  select  'Z',N'咗'
)  T 
where  word>=@word  collate  Chinese_PRC_CS_AS_KS_WS 
order  by  PY  ASC)  else  @word  end)
set  @str=right(@str,len(@str)-1)
end
return  @PY

http://www.itzhe.cn/html/sql/MsSQL/20080312/108251_5.html
end

函数调用实例：

select dbo.fun_getPY('中华人民共和国'),dbo.fun_getPY('中華人民共和國')

结果都是：ZHRMGHG

大家假如有爱好，可以使用相同的方法，扩展得到汉字全拼的函数，甚至你还可以得到全拼的读音声调，不过全拼分类大多了。得到全拼最好还是用对照表，两万多汉字搜索速度显然很快，另外，用对照表还可以充分利用表的索引。