SQL Server的事务处理与高级查询
6.高级查询与脚本6.1子查询位于SELECT查询中的SELECT查询。6.11 标量表达式select id,val,val-(select avg(val) from tbltest) from tbltest运行结果1 25.00 -16.9285711 35.00 -6.9285712 23.00 -18.9285714 12.00 -29.9285714 52.00 10.0714296 27.00 -14.9285716 37.00 -4.9285718 26.00 -15.9285719 99.00 57.07142910 28.00 -13.92857111 65.00 23.07142911 48.00 6.07142913 83.00 41.07142914 27.00 -14.9285716.12 创建派生表select id as topid,val as topvalue,val-(select avg(val) from tbltest) as subvalue from tbltest6.13 使用IN()函数select id as topid,val as topvalue,val-(select avg(val) from tbltest) as subvalue from tbltest where id in (2,4,6,8,10,12,14)运行结果2 23.00 -18.9285714 12.00 -29.9285714 52.00 10.0714296 27.00 -14.9285716 37.00 -4.9285718 26.00 -15.92857110 28.00 -13.92857114 27.00 -14.9285716.14 使用EXISTS()和NOT EXISTS()函数用于在子查询有返回记录时返回外查询中的一行。在查询中通常使用星号而不是使用列名。但是在子查询中，它仅用于让查询引擎测试试行时是否存在，而不会浪费系统资源。select id as topid,val as topvalue,val-(select avg(val) from tbltest) as subvalue from tbltest where exists(select id from tbltest where id in (2,4,6,8,10,12,14))6.2 Common Table Expressions(CTE)CTE是一个只存在于内存中的子查询，不需要特殊的权限，也不需要物理空间操作。CTE与传统的子查询不同，它是一个已命名的对象，可以像表那样重用和引用。CTE需要在查询脚本中被使用之前进行定义，其定义形式是：用WITH开头，后跟一列放在括号中的输出列，之后是关键字AS和一个放在括号中的完整的SELECT语句。WITH HighPrice (ProductID,ProductName,UnitPrice) AS( SELECT ProductID,ProductName,UnitPrice From Products WHERE UnitPrice>80)SELECT * From HighPrice运行结果9 Mishi Kobe Niku 97.0020 Sir Rodney's Marmalade 81.0029 Thüringer Rostbratwurst 123.7938 C?te de Blaye263.50注意CTE必须在后续的查询中才能被使用。6.3游标对于SQL来说，游标是指从查询返回的记录集。6.31 创建与遍历游标首先声明一个游标类型的变量，变量名不能以@符号开头。游标变量可以在填写游标的SELECT语句的行上声明和定义：DECLARE curProduct INSENSITIVE CURSORFOR SELECT TOP 10 ProductID,ProductName FROM ProductsDECLARE @ProID intDECLARE @ProName nvarchar(40)
Open curProductFETCH NEXT FROM curProduct INTO @ProID,@ProNameWHILE @@Fetch_Status=0BEGIN PRINT @ProName Fetch NEXT FROM curProduct Into @ProID,@ProNameENDCLOSE curProductDEALLOCATE curProduct运行结果Alice MuttonAniseed SyrupBoston Crab MeatCamembert PierrotCarnarvon TigersChaiChangChartreuse verteChef Anton's Cajun SeasoningChef Anton's Gumbo Mix7.数据事务7.1事务简介7.11 事务类型
[*]显式事务：事务中存在显示的BEGIN TRANSACTION语句，后跟一个或多个相关的数据修改语句，并以显式的COMMIT TRANSACTION语句结束。错误检查添加在COMMIT TRANSACTION语句之前。如果操作有误，事务可以通过ROLLBACK TRANSACTION语句来撤销。
[*]隐式事务：只要进行数据修改，就隐式地开始一个事务。
[*]自动提交事务
7.12 ACID测试
[*]原子(Atomic)：事务中的所有步骤和操作都当做原子单元。要么全部成功，要么全部失败。
[*]一致(Consistent)：任何事务的输出都是可预测的，所有的操作都遵循一致性原则，并确保数据库内的数据完整性。
[*]隔离(Isolated)：任何在事务之前、之中或者之后执行的操作，相关数据都处于一致的状态，而不是处于部分完成的状态。任何用户或者操作查询受事务影响的数据时，都会立即觉察到整个事务被他提交了。
[*]持久(Durable)：如果事务成功，数据就写到磁盘上，不会回到它原来的状态。数据不会受系统失败的影响。
7.13 事务日志事务日志是磁盘上的一个独立文件，用于从所有用户与应用程序处收集所有成功的数据修改请求。7.2对数据执行CRUD7.21 添加记录INSERT…Values语句：提供一个列名的列表，之后是放在括号中的值的列表，用于在表中插入一行。INSERT INTO tbltest (id,val) values (2,18)INSERT…SELECT：通过使用SELECT语句来提供值。INSERT INTO tbltest (id,val)SELECT '2','23'插入多个记录（2008中新增）INSERT INTO tbltest (id,val) values (2,18)，(3,4)插入其他表中的行：insert into tbltest2 (id,va2)select id,val from tbltest使用存储过程管理插入操作CREATE PROCEDURE Ins_tbltest@id int,@val decimal(9,2)ASINSERT INTO tbltest(id,val)SELECT @id,@valreturn @@IdentityIns_tbltest 2,28.27.22 修改记录UPDATE命令：列值用SET关键字来修改。过滤更新：update tbltestset val=val*1.2根据多个表更新数据行update tbset val=val*1.2From tbltest tbINNER JOIN tbltest2 tb2 on tb.id=tb2.idWHERE tb.id=4使用存储过程更新记录CREATE PROCEDURE spUpd_tbltest@id int ,@val decimal(9,2)ASupdate tbltestSET id=@id,val=@val7.23 删除记录DELETE命令：delete from tbltest where id=17.24 用MERGE命令自动完成插入、更新和删除操作(SQL2008新增)8.高级功能8.1数据的透视第一范式：一个实体不应包含重复类型的特性。着意味着类似的值不应在同一行的多列上重复出现。PIVOT和UNPIVOT操作符：8.2全文索引和近似匹配SQLSERVER为BLOB类型提供了三种不同的实现方法，包括Text,nText和Image。但它们并不支持索引和排序功能。8.3 Microsoft搜索服务音索匹配：9.T-SQL编程对象9.1联合视图单机视图创建比较简单，联合视图将异地服务器联合工作，来解决业务问题。CREATE VIEW vALLAcountsASSELECT * FROM Accounts ---(local DATABASE)UNION ALLSELECT * FROM EastCoastServer.SaleDatabase.Accounts---(other DATABASE)
SELECT TOP 10* FROM vALLAcounts9.2保护数据视图提供一个允许用户访问数据的层，但不能通过该层访问敏感数据或者其他数据库对象。通常需要采取的安全措施是，数据库管理员首先锁着所有的表，拒绝任何常规用户访问。然后创建视图，并显式地位所有或有选择的用户公开经过选择的表、列或行。一般情况下视图不提供数据修改。9.3存储过程可以实现带参数的视图、返回标量值、维护记录、处理业务逻辑。存储过程创建完毕后例如sp_Protb,用如下语句进行执行Exec sp_Protb处理业务逻辑：主要使用条件逻辑、IF语句、CASE、循环来实现。select val,case id when 1 then 111 when 2 then 222when 3 then 333 else 999End as valtotalfrom tbltest运行结果27.60 22217.28 99974.88 99932.40 99944.40 99931.20 999118.8099933.60 99978.00 99957.60 99999.60 99932.40 99921.60 22227.60 2229.4用户自定义函数9.41 标量函数用于接收任何数量的参数并且返回一个值。将输入参数在括号中声明，后跟返回值表名，且所有语句必须包括在BEGIN…END中。CREATE FUNCTION fnGetAge(@Bir Datetime,@Today Datetime)RETURNS INTASBEGIN RETURN DateDIff(day,@Bir,@Today)/365.25END
SET ANSI_NULLS ONselect dbo.fnGetAge('1/5/1984',GetDate())运行结果269.42 内嵌表值函数可以像视图一样返回一个结果集，但是函数可也接收参数。在函数定义中，返回类型被设置为表类型，而RETURN语句则和位于括号内的SELECT查询一起被调用。ALTER FUNCTION .(@val decimal)RETURNS TABLEASRETURN( -- Add the SELECT statement with parameter references here SELECT * from tbltest where tbltest.val=@val)9.43 多语句表值函数10.创建和管理数据库对象10.1数据定义语言
对象类型名字
表Employee
视图vwOpsEmployee
存储过程spInsertEmployee
函数fnNewEmployee
触发器trVerifyEmployee
检查约束chPhoneNumber
外键约束fkSalesEmployeeLink
主键约束pkEmployeeID
默认dfRegion
簇索引clRegionID
非簇索引ncLastName
CREATE TABLE MyTable(ID Int Not null,Val varchar(50) null)GoCreate view MyviewASSelect Val from MyTable10.11 可空性如果没有给这个列提供值，则默认为NULL。NOT NULL实际上市列约束，如果要禁止使用空值，则使用NOT NULL10.12 标识符IDENTITY：给某个列自动赋予数字值。语法格式为IDENTITY[(seed,increment)].种子(seed)与增量(increment)值并没有被限制为1，也没有被限制为整数。支持IDENTITY属性的数据类型有tinyInt,smallInt,int,bigInt,decimal与numeric.CREATE TABLE MyTable(ID Int identity(1000000,-100) Not null,Val varchar(50) null)Go10.13 默认值DEFAULT语句CREATE TABLE MyTable(ID Int identity(100000,-100) Not null,Val varchar(50) null default 888,Mes varchar(50))Go10.14 约束
约束类型说明
NotNull(非空)确保列有一个已定义的非空值
Primary Key(主键)对行的标识强制要求唯一性，不接受空值
Check(主键)根据列的质来验证行。使用一个与WHERE语句后所接内容类似的子句来标识可接受的值
Unique(唯一)要求列中的每个值都是唯一，可接受空值
Foreign Key(外键)根据相关表的主键检查列的值，来强制遵循所引用完整性规则
主键约束：CREATE TABLE MyTable(ID Int Not null CONSTRAINT pkMyTable PRIMARY KEY,Val varchar(50) null default 888,Mes varchar(50))Go
醉过之后。突然好想你、、、 一个人炫耀什么，说明内心缺少什么 莪的花樣年華丶到頭來-只昰庸人自擾而已。▂_ 个性签名：一个人越在意的地方，就是最令他自卑的地方 伤感个性签名：习惯一个人在那街角徘徊然后慢慢回忆你是如何远去。
页:
[1]