2. 技术
    3. SqlServer2008实例13 UPDATE

    SqlServer2008实例13 UPDATE

    技术2022-07-11  87

    目录

    1.更新一行

    2.根据FROM和WHERE子句更新新行

    3.更新大值数据类型的列

    4.使用OPENROWSET和BULK插入或更新图片文件

    5.在维护SQL Server事务控制时向文件系统存储非结构化数据

    启用和配置 FILESTREAM

    6.“就地”赋值并修改数据库值

    1.更新一行

        这个示例通过指定SpecialOfferID来更新一行，SpecialOfferID是表的主键。在实施这个更新之前，我们先查询计划修改的行：

    use adventureworks go SELECT DiscountPct FROM Sales.SpecialOffer WHERE SpecialOfferID=10

    现在实施修改操作：

    UPDATE Sales.SpecialOffer SET DiscountPct=0.15 WHERE SpecialOfferID=10 SELECT DiscountPct FROM Sales.SpecialOffer WHERE SpecialOfferID=10

    2.根据FROM和WHERE子句更新新行

        在这个示例中，假设Production. Product表中有一个产品“Full-Finger Gloves，M"，这个产品每一位顾客最多只允许购买两个。为了达到这个查询的目的，任何购物车中只要这个产品的数量超过两个，都需要立即调整为所要求的限制：

    use adventureworks go UPDATE Sales.ShoppingCartItem SET Quantity=2,ModifiedDate=GETDATE() FROM Sales.ShoppingCartItem c INNER JOIN Production.Product p ON c.ProductID=p.ProductID WHERE p.Name='Full-Finger Gloves,M' AND c.Quantity>2

    3.更新大值数据类型的列

          SQL Server在其早期版本中就引入了新的大值数据类型，旨在代替不推荐的text、ntext和imaga数据类型。这些新的数据类型包括：

    varchar(max)，保存非Unicode的变长数据；nvarchar(max)，保存Unicode的变长数据：varbinary(max)，保存变长的二进制数据口

        这些数据类型能存储高达2^31-1字节的数据。     旧的text和image数据类型的一个主要缺点是，需要使用诸如WRITETEXT和UPDATETEXT等独立的函数来操作image/text数据。使用新的大值数据类型，就可以直接使用常规的INSERT和UPDATE操作。     插入大值数据类型的语法和普通的插入没有什么两样。但是，对于更新大值数据类型，UPDATE命令包含了一个.WRITE方法：

    UPDATE <table_or_view_name> SET column_name=.WRITE(expression,@offset,@Length) FROM <table_source> WHERE <search_condition> use adventureworks go CREATE TABLE dbo.RecipeChapter ( ChapterID int NOT NULL, Chapter varchar(max) NOT NULL ) go INSERT dbo.RecipeChapter (ChapterID,Chapter) VALUES (1,'At the begging of each chapter you will notice that basic concept first.') --更新刚插入的行，在现有的兔子最后添加一个句子 UPDATE RecipeChapter SET Chapter .WRITE('In addition to the basic,this chapter will also provide recipes that can be used in your day to day development and administrator.', NULL,NULL) WHERE ChapterID=1 --把“day to day”替换成一个单词“daily” UPDATE RecipeChapter SET Chapter .Write('daily',181,10) WHERE ChapterID=1 --返回结果 SELECT chapter FROM RecipeChapter WHERE ChapterID=1

    4.使用OPENROWSET和BULK插入或更新图片文件

           SQL Scrver 2005和SQL Server 2008，可以结合使用UPDATE和OPENROhISET来把图片导入表中。我们能使用OPENROWSET把文件导入到某一行的某一列。本技巧中会用到的OPENROWSET的基本语法：

    OPENROWSET (BULK 'data_file',SINGLE_BLOG|SINGLE_CLOB|NCLOB) 参数描述data_file指定要读取的文件的名字和路径SINGLE_BLOB指定SINGLE_ BLOB对象是导入到varbiniary(max)数据类型中SINGLE_CLOB指定SINGLE_CLOB是导入ACSCII数据到varchar(max)数据类型中SINGLE_NCLOB指定SINGLE_NCLOB是导入到varchar(max) Unicode数据类型中 use adventureworks go --创建一个用于存储BMP图片文件的新表 CREATE TABLE dbo.StockBmps ( StockBmpID Int NOT NULL, bmp varbinary(max) NOT NULL ) go --向表中插入一个包含图片文件的行 INSERT dbo.StockBmps (StockBmpID,bmp) SELECT 1,BulkColumn FROM OPENROWSET(BULK 'J:\test\4.jpg',SINGLE_BLOB) AS x --使用一个查询查看表中的这行 SELECT bmp FROM StockBmps WHERE StockBmpID=1

    use adventureworks go --创建一个用于存储BMP图片文件的新表 CREATE TABLE dbo.StockBmps ( StockBmpID Int NOT NULL, bmp varbinary(max) NOT NULL ) go --向表中插入一个包含图片文件的行 INSERT dbo.StockBmps (StockBmpID,bmp) SELECT 1,BulkColumn FROM OPENROWSET(BULK 'J:\test\4.jpg',SINGLE_BLOB) AS x --使用一个查询查看表中的这行 SELECT bmp FROM StockBmps WHERE StockBmpID=1 --更新一个现有的BMP文件，将其修改成另外一BMP文件 UPDATE dbo.StockBmps SET bmp=( SELECT BulkColumn FROM OPENROWSET(BULK 'J:\test\clock.png',SINGLE_BLOB) as x ) WHERE StockBmpID=1

    5.在维护SQL Server事务控制时向文件系统存储非结构化数据

        SQL Server 2008引入了FILESTREAM新属性，可以将它应用到varbinary (max)数据类型上。使用FILESTREAM存储值到文件系统时可以超过2GB的限制，还可以带来SQL Server的对文件关系处理的优点，虽然实际上文件还是存储在文件系统中的。BACKUP和RESTORE操作不仅能对数据库数据进行维护，也可以对保存在文件系统中的文件进行维护，因此可以控制存储结构化和非结构化数据的应用程序的端到端数据的可复原性。FILESTREAM把NT文件系统流的性能优点与SQL Server事务一致性能力完美地结合到了一起。       T-SQL用来定义用来FILESTREAM属性并可以用来处理数据，而在应用程序实施实际的读写操作时应该首选的是Win32流API(具体来说使用OpenSqlFilestream API)。尽管演示Win32和可应用API的实现超出了本书范围，但我会在本技巧中一步步介绍如何设置具有FILESTREAM属性的数据和表，如何插入新行，以及使用查询来获得调用OpenSqlFilestream API时必要的路径和事务标记信息。        必须在Windows和SQL Server范围内都配置FILESTREAM．要在Windows范围内启用FILESTREAM并且定义相关的文件共享，需要使用SQL Scrvcr配置管理器．而要在SQL Server实例级别启用FILESTREAM，需要使用sp_configure和frlestream access level选项．      

    启用和配置 FILESTREAM

            在开始使用 FILESTREAM 之前，必须在 SQL Server 数据库引擎实例中启用 FILESTREAM。 本主题说明了如何使用 SQL Server 配置管理器来启用 FILESTREAM。

    在 “开始” 菜单中，依次指向 “所有程序” 、 SQL Server 2019 (15.x)、 “配置工具” ，然后单击 “SQL Server 配置管理器” 。

    在服务列表中，右键单击“SQL Server 服务” ，然后单击“打开” 。

    在“SQL Server 配置管理器” 管理单元中，找到要在其中启用 FILESTREAM 的 SQL Server 实例。

    右键单击该实例，然后单击“属性” 。

    在 “SQL Server 属性” 对话框中，单击 “FILESTREAM” 选项卡。

    选中“针对 Transact-SQL 访问启用 FILESTREAM” 复选框。

    如果要在 Windows 中读取和写入 FILESTREAM 数据，请单击“针对文件 I/O 流访问启用 FILESTREAM” 。 在 “Windows 共享名” 框中输入 Windows 共享的名称。

    如果远程客户端必须访问存储在此共享中的 FILESTREAM 数据，请选择 “允许远程客户端针对 FILESTREAM 数据启用流访问” 。

    单击“应用” 。

    在 SQL Server Management Studio中，单击 “新建查询” 以显示查询编辑器。

    在查询编辑器中，输入以下 Transact-SQL 代码：

    SQLCopy

    EXEC sp_configure filestream_access_level, 2 RECONFIGURE

    单击“执行” 。

    重新启动 SQL Server 服务。

          为了确认SQL Server实例中是否配置了FILESTREAM，可以使用SERVERPROPERTY函数和3个不同属性来验证这个设置，这3个属性分别是文件流共享的文件共享名称、相关效果值和实际配置的值：

    use adventureworks go SELECT SERVERPROPERTY('FilestreamShareName') ShareName, SERVERPROPERTY('FilestreamEffectiveLevel') EffectiveLevel, SERVERPROPERTY('FilestreamConfiguredLevel') ConfiguredLevel

    use master go --创建一个新数据库，它有一个包含FILESTREAM数据的文件组 CREATE DATABASE PhotoRepository ON PRIMARY ( Name=N'PhotoRepository', FILENAME=N'E:\test\MDF\PhotoRepository.mdf', SIZE=3048KB, FILEGROWTH=1024KB ), FILEGROUP FS_PhotoRepository CONTAINS FILESTREAM ( NAME='FG_PhotoRepository', FILENAME='E:\test\FILESTREAM' ) LOG ON ( NAME='PhotoRepository_log', FILENAME=N'E:\test\LDF\PhotoRepository_log.ldf', SIZE=1024KB, FILEGROWTH=10% ) GO --创建一个用来存储书籍封面图片的新表 Use PhotoRepository go CREATE TABLE dbo.BookPhoto ( BookPhotoID uniqueidentifier ROWGUIDCOL NOT NULL PRIMARY KEY, BookPhotoNM varchar(50) NOT NULL, BookPhotoFile varbinary(max) FILESTREAM ) --插入一个新行，使用OPENROWSET导入文件 INSERT dbo.BookPhoto (BookPhotoID,BookPhotoNM,BookPhotoFile) SELECT NEWID(), 'SQL Server 2008 Transact-SQL Recipes cover', BulkColumn FROM OPENROWSET(BULK 'F:\test\1.jpg',SINGLE_BLOB) as x

    说明：代码运行前，要建立相关文件夹

    6.“就地”赋值并修改数据库值

        除了标准的等于(=)运算符，SQL Server 2008还引入了新的、既可以赋值又可以修改数据值的复合赋值运算符。这些运算符与你在C和Java语言中看到的相似。新的赋值运算符包括以下几个：     +=（加，赋值）；     -=(减，赋值)；     *=（乘，赋值）；     /=（除，赋值）；     I=（位或，赋值）；     ^=(位异或，赋值)；     &=（位与，赋值）；     %=（取模，赋值）i  

    use AdventureWorks go --创建一个新表并填充一些数据 CREATE TABLE HumanResources.EmployeePayScale ( EmployeePayScaleId int not null Primary KEy IDENTITY(1,1), BasePayAMT numeric(9,2) not NULL, ModifiedDate datetime not null default GETDATE() ) go --多行插入 INSERT HumanResources.employeepayScale (BasePayAMT) VALUES (30000.00), (40000.00), (50000.00), (60000.00) --复查指定的薪级行的初始值 SELECT BasePayAMT FROM HumanResources.EmployeePayScale WHERE EmployeePayScaleID=4 use AdventureWorks go UPDATE HumanResources.employeepayscale SET BasePayAMT+=10000 WHERE EmployeePayScaleID=4 SELECT BasePayAMT FROM HumanResources.EmployeePayScale WHERE EmployeePayScaleID=4

     

    Processed: 0.011, SQL: 9