|
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
| 
QQ群⑧：
窥视卡
雷达卡
发表于 2013-12-5 09:18:00
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
千斤顶
显身卡