6.高级查询与脚本

6.1子查询

位于SELECT查询中的SELECT查询。

6.11 标量表达式

select id,val,val-(select avg(val) from tbltest) from tbltest

运行结果

1       25.00        -16.928571

1       35.00        -6.928571

2       23.00        -18.928571

4       12.00        -29.928571

4       52.00        10.071429

6       27.00        -14.928571

6       37.00        -4.928571

8       26.00        -15.928571

9       99.00        57.071429

10     28.00        -13.928571

11     65.00        23.071429

11     48.00        6.071429

13     83.00        41.071429

14     27.00        -14.928571

6.12 创建派生表

select id as topid,val as topvalue,val-(select avg(val) from tbltest) as subvalue from tbltest

6.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.928571

4       12.00        -29.928571

4       52.00        10.071429

6       27.00        -14.928571

6       37.00        -4.928571

8       26.00        -15.928571

10     28.00        -13.928571

14     27.00        -14.928571

6.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.00

20     Sir Rodney's Marmalade 81.00

29     Thüringer Rostbratwurst          123.79

38     Côte de Blaye  263.50

注意CTE必须在后续的查询中才能被使用。

6.3游标

对于SQL来说，游标是指从查询返回的记录集。

6.31 创建与遍历游标

首先声明一个游标类型的变量，变量名不能以@符号开头。游标变量可以在填写游标的SELECT语句的行上声明和定义：

DECLARE curProduct INSENSITIVE CURSOR

FOR SELECT TOP 10 ProductID,ProductName FROM Products

DECLARE @ProID int

DECLARE @ProName nvarchar(40)

 

Open curProduct

FETCH NEXT FROM curProduct INTO @ProID,@ProName

WHILE @@Fetch_Status=0

  BEGIN

    PRINT @ProName

    Fetch NEXT FROM curProduct Into @ProID,@ProName

  END

CLOSE curProduct

DEALLOCATE curProduct

运行结果

Alice Mutton

Aniseed Syrup

Boston Crab Meat

Camembert Pierrot

Carnarvon Tigers

Chai

Chang

Chartreuse verte

Chef Anton's Cajun Seasoning

Chef Anton's Gumbo Mix

7.数据事务

7.1事务简介

7.11 事务类型

• 显式事务：事务中存在显示的BEGIN TRANSACTION语句，后跟一个或多个相关的数据修改语句，并以显式的COMMIT TRANSACTION语句结束。错误检查添加在COMMIT TRANSACTION语句之前。如果操作有误，事务可以通过ROLLBACK TRANSACTION语句来撤销。
• 隐式事务：只要进行数据修改，就隐式地开始一个事务。
• 自动提交事务

7.12 ACID测试

• 原子(Atomic)：事务中的所有步骤和操作都当做原子单元。要么全部成功，要么全部失败。
• 一致(Consistent)：任何事务的输出都是可预测的，所有的操作都遵循一致性原则，并确保数据库内的数据完整性。
• 隔离(Isolated)：任何在事务之前、之中或者之后执行的操作，相关数据都处于一致的状态，而不是处于部分完成的状态。任何用户或者操作查询受事务影响的数据时，都会立即觉察到整个事务被他提交了。
• 持久(Durable)：如果事务成功，数据就写到磁盘上，不会回到它原来的状态。数据不会受系统失败的影响。

7.13 事务日志

事务日志是磁盘上的一个独立文件，用于从所有用户与应用程序处收集所有成功的数据修改请求。

7.2对数据执行CRUD

7.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)

AS

  INSERT INTO tbltest(id,val)

  SELECT @id,@val

return @@Identity

Ins_tbltest 2,28.2

7.22 修改记录

UPDATE命令：列值用SET关键字来修改。

过滤更新：

update tbltest

set val=val*1.2

根据多个表更新数据行

update tb

set val=val*1.2

From tbltest tb

INNER JOIN tbltest2 tb2 on tb.id=tb2.id

WHERE tb.id=4

使用存储过程更新记录

CREATE PROCEDURE spUpd_tbltest

  @id int

 ,@val decimal(9,2)

AS

  update tbltest

  SET id=@id,val=@val

7.23 删除记录

DELETE命令：delete from tbltest where id=1

7.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 vALLAcounts

AS

  SELECT * FROM Accounts      ---(local DATABASE)

UNION ALL

  SELECT * FROM EastCoastServer.SaleDatabase.Accounts  ---(other DATABASE)

 

SELECT TOP 10* FROM vALLAcounts

9.2保护数据

视图提供一个允许用户访问数据的层，但不能通过该层访问敏感数据或者其他数据库对象。通常需要采取的安全措施是，数据库管理员首先锁着所有的表，拒绝任何常规用户访问。然后创建视图，并显式地位所有或有选择的用户公开经过选择的表、列或行。一般情况下视图不提供数据修改。

9.3存储过程

可以实现带参数的视图、返回标量值、维护记录、处理业务逻辑。

存储过程创建完毕后例如sp_Protb,用如下语句进行执行

Exec sp_Protb

处理业务逻辑：主要使用条件逻辑、IF语句、CASE、循环来实现。

select val,case id when 1 then 111 when 2 then 222

when 3 then 333 else 999

End as valtotal

from tbltest

运行结果

27.60   222

17.28   999

74.88   999

32.40   999

44.40   999

31.20   999

118.80  999

33.60   999

78.00   999

57.60   999

99.60   999

32.40   999

21.60   222

27.60   222

9.4用户自定义函数

9.41 标量函数

用于接收任何数量的参数并且返回一个值。将输入参数在括号中声明，后跟返回值表名，且所有语句必须包括在BEGIN…END中。

CREATE FUNCTION fnGetAge(@Bir Datetime,@Today Datetime)

RETURNS INT

AS

  BEGIN

    RETURN DateDIff(day,@Bir,@Today)/365.25

  END

 

SET ANSI_NULLS ON

select dbo.fnGetAge('1/5/1984',GetDate())

运行结果

26

9.42 内嵌表值函数

可以像视图一样返回一个结果集，但是函数可也接收参数。在函数定义中，返回类型被设置为表类型，而RETURN语句则和位于括号内的SELECT查询一起被调用。

ALTER FUNCTION [dbo].[fnTblList](@val decimal)

RETURNS TABLE

AS

RETURN

(

    -- 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)

Go

Create view Myview

AS

  Select Val from MyTable

10.11 可空性

如果没有给这个列提供值，则默认为NULL。NOT NULL实际上市列约束，如果要禁止使用空值，则使用NOT NULL

10.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)

Go

10.13 默认值

DEFAULT语句

CREATE TABLE MyTable

(ID Int identity(100000,-100) Not null,Val varchar(50) null default 888,

Mes varchar(50))

Go

10.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