SQL 7 Implementing Instructor: 张为党 MOC-833B SQL 7 Implementing Instructor: 张为党 Qualification:MCSE/MCDBA/MCT Ph.D Candidate at ISCAS

课程内容 数据库设计理论 实体-关系建模技术 数据库的创建 数据一致性 数据库性能优化 Planning and Creating Index 数据查询 数据库设计的高级技术(View, SP, Trigger)

数据库设计理论-ERA（继续） 什么是实体-关系建模技术 Entity-Relation Approch是于1976年有P.PS.Chen提出的。这种技术使用E-R 图来描述现实世界中的事物（实体），事物的特性（属性），以及事 物之间的相互联系（关系），从而达到为信息系统建立良好的数据模 型的目的。 国际实体关系方法年会(International Conference On the ERA)

问题描述 某培训中心想开发一个管理系统，用来管理MCSE考试 的情况。该系统想实现下面的功能： 能够查考生每门课的成绩 能够查某个考生参加了几门考试 考试通过率 教师教课的情况：能够统计某段时间内老师上过几 次课即可 请你帮助设计该系统

数据库设计理论-ERA 模型 所谓模型是对客观事物、现象、过程或系统的简化描述，是系统部分 属性的模仿。模型是人们对客观系统中感兴趣的事物的抽象表达。 ERA的发展变化正是为了使它能够更正确地描述现实世界，从而成为 数据库设计者的强有力工具。 任何数据库系统都要经过建模这个步骤，无论是有意识地还是无意识 地，建模的好坏直接影响着数据库系统的质量。

数据库设计理论-ERA（继续） 数据库系统模型 数据模型：是对组织数据的框架结构的建模，最后 发展成为数据的存储方式和数据字典中的定义 功能模型：最终发展成为应用程序，是用户的最终 需求。 二者相辅相成，其中数据模型是基础，没有一个良 好的数据模型，无法产生一个高质量的应用。

数据库设计理论-ERA（继续） 数据模型 建模是一种技术，通过使用这种技术可以产生更灵 活、更有效的数据结构，以便应用系统和数据库管 理系统可以最大限度地利用数据资源。 通过建模，最终要实现： 消除冗余数据 消除冗余数据带来的限制 使数据与数据的存取方式无关 避免因为相关带来的危害

数据库设计理论-ERA（继续） 数据模型（继续） 数据模型三要素：DM中包含许多概念，这些概念被 精确地定义用于描述数据库的静态特征、动态特征 和完整性约束。一般而言，DM由三个部分组成： 数据结构：用一系列可以操作的对象来描述系统的静态特征。 与数据内容及类型有关的对象：实体/属性域 与数据之间联系有关的对象：关系

数据库设计理论-ERA（继续） 数据模型（继续） 数据模型三要素： 数据操作。DM中可以定义操作的含义和规则，描述系统的动态特征。Insert/Delete/Update/Select 数据约束条件。 包括实体完整性和参照完整性，主要目的是使数据库系统与它所描述的现实世界在整体上相符合。即在设计时使数据模型正确、真实、有效地反映现实。在运行时保证数据库中的数据值真实地体现现实世界的状态。

数据库设计理论-ERA（继续） 为什么要建立数据模型？ 就像大楼的设计图一样，DM可以使所有的项目参与 者都有一个共同的数据标准 加快开发速度，并且使应用更加有效 是整个开发生命周期的一部分 为设计、开发、维护人员建立了一套共同的文档 方便在数据库间的移植 及早发现问题

数据库设计理论-ERA（继续） 如何建立数据模型？ 根据DM应用的目的不同，可以分为两层： 概念模型(Conceptual Data Model)： 面向现实世界进行建模。 现实世界中的事物经过人脑的抽象加工，提取出对用户有用的信息，经过组织、整理，形成介于现实世界和计算机之间的中间模型。它只关心现实世界中的事物、事物的联系、事物的特征，完全没有与具体实现相关的任何概念。 是系统分析人员、应用开发人员、维护人员和用户之间相互理解的共同语言，是物理模型和应用程序设计的重要基础

数据库设计理论-ERA（继续） 如何建立数据模型？（继续） 物理模型(Physical Data Model) 面向数据库管理系统进行建模。实现概念模型向DBMS的转化的任务。同时加入概念模型中未考虑的因素：Trigger,SP,PK,FK,View, Index等等。

数据库设计理论-ERA（继续） 如何评价一个数据模型？ 一个好的模型应该具有最小定义、完备定义及适应性 。另外，应该容易实现。 最小定义： 如何评价一个数据模型？ 一个好的模型应该具有最小定义、完备定义及适应性 。另外，应该容易实现。 最小定义： 用尽可能少的实体描述尽可能多的对象。 任何数据和信息应该切只能用一种方法进行定义，以便尽可能地消除冗余：消除冗余的实体和冗余的关系。 规范化理论为消除冗余提供了强有力的手段。

数据库设计理论-ERA（继续） 如何评价一个数据模型？（继续） 完备定义：现实世界中所有感兴趣的概念和对象是 否都已经定义在模型之中，如果没有，系统的某些 功能必将无法实现。 适应性：当系统的功能或需求发生变化时，是否能 够做最小的修改以适应新的要求，降低维护费用。

建模中基本问题的讨论 CDM中的基本概念 实体(Entity): DM中要存储信息的对象。我们需要了解 、保存、修改信息的现实世界中的对象都可以作为 CDM中的实体 实例(Instance): 是实体的具体例子，具体实现。 实体是对实例共同性质的总结与抽象 实例是实体的具体体现

建模中基本问题的讨论 CDM中的基本概念（继续） 属性(Attribute): 描述事物的状态与特征 域(Domain): 某个或某些属性的取值范围。 关系(Relation):实体之间有意义的连接。 关系的种类：一对一；一对多；多对一

建模中基本问题的讨论 PDM中的基本概念 表(Table) 表是由CDM中的实体变化而来。表是对实体 的物理实现，记录着关于实体的信息，也记录着实 体之间关系的信息。

建模中基本问题的讨论（继续） PDM中的基本概念（继续） 列，字段(Field) 由CDM中的属性转化而来 起特殊作用的字段：PK, FK，维持着表间的关系 PK:能够唯一地区分不同记录的字段称为主键 FK:满足下列条件的属性或属性组合称为FK 不是表所对应实体的固有属性 是另外一个表的PK FK体现了关系

建模中基本问题的讨论（继续） PDM中的基本概念（继续） 存储过程(Stored Procedure) 效率高 通信量低 可以协助Trigger实施企业规则，由Trigger调用

建模中基本问题的讨论 PDM中的基本概念（继续） 触发器(Trigger) 一个特殊的SP，由特定的事件触发执 行，其作用为： 实施业务规则，如Salary<2000 实体相关完整性： 级联删除/级联修改 限制删除/限制修改 设空值 增加时间开销 嵌套触发、递归触发、死锁

建模中基本问题的讨论 PDM中的基本概念（继续） 视图(View),查看数据的逻辑窗口 支持数据的逻辑独立性，当基础表发生变化时，用户程序不受影响 简化应用程序的开发 数据保密

建模中基本问题的讨论 PDM中的基本概念（继续） 索引(Index) 加快数据检索速度的机制 唯一索引(Unique Index) 每一个索引值只对应着一条记录 非唯一索引(Non-Unique Index)每一个索引值只对应着多条记录 复合索引(Composite Index) 聚簇索引(Clustered Index) 以索引所确定的顺序物理存储 缺点：占用空间；降低更新操作的速度。

规范化理论(Normalization) 不同的人对于相同的东西可以建立不同的模型，如何 衡量模型建立的好坏？换而言之，按照什么原则建立 模型？ 这个原则就是规范化理论

规范化理论(Normalization) 什么是规范化？ 如果关系建立的不好的话，不能很好地反映现实世 界，容易引起各种异常。为了消除这些异常，人们 采用分解的方法，力求使关系的语义单纯化，这就 是所谓关系的规范化 由于关系的规范化的要求不同，出现了不同的范式 ，从1NF,2NF,3NF,BCNF,4NF,直至5NF。 规范化的过程就是一个关系“拆”的过程

规范化理论(Normalization) 1NF(First Normal Function,第一范式） 定义：关系R是1NF，当且仅当所有的基础域仅包含 原子值 1NF要求所有属性的数据均为不可再分的数据项，实 体中的某个属性不能有多个值或者不能有重复的属 性。如果出现这种情况，就可能需要定义一个新的 实体，新的实体由重复的属性构成，新实体与原实 体之间为一对多关系。

规范化理论(Normalization) 1NF(例子） 这个例子违反了1NF，因为“生卒年”属性可以有多个值；出现了重复的属性“名字”，因此需要规范化： 去掉同时拥有多个值的属性，将“生卒年”拆成两个属性：出生日期和去世日期 去掉重复的属性。方法是新增加一个实体 作者 国籍 名字1 名字2 名字3 名字4 生卒年

规范化理论(Normalization) 1NF(例子） 作者 国籍 名字1 名字2 名字3 名字4 生卒年 作者 国籍 出生日期 去世日期 作者姓名 姓名 姓名描述 m 作为一个经验性的标准，如果一个实体的属性个数在1-8个之间是正常的，否则可能需要拆出新的实体

规范化理论(Normalization) 2NF(Second Normal Function，第二范式） 定义：关系R是2NF，当且仅当R是1NF，并且所有非 PK属性完全依赖于PK 2NF要求实体的属性完全依赖于唯一标识符。所谓完 全依赖是指不能存在仅依赖PK一部分的属性，如果 存在，那么这个属性和PK的这一部分应该分离出来 形成一个新的实体，新实体与原实体之间是一对多 的关系。

规范化理论(Normalization) 2NF(例子) 学生成绩信息 学生姓名 课程名称 成绩 任课教师姓名 教师所在系 语义 每位学生所修的每一门课程都有一个成绩 每门课程都只有一个任课老师，但一位老师可以教多门课程 教师中没有重名，且每位老师只属于一个系

规范化理论(Normalization) 2NF(例子) 学生成绩信息 学生姓名 课程名称 成绩 任课教师姓名 教师所在系 该模型的特点 学生姓名和课程名称确定其他属性，为PK 满足1NF，但是不满足2NF，任课教师姓名仅依赖于课程名称

规范化理论(Normalization) 例子所存在的问题 数据冗余太大：一门课程的教师名必须在选这门的所 有学生重复一次；一个系名必须对选该系所开课程的 所有学生重复一次； 更新异常问题(Update anomalies) 修改异常(modification anomaly)：由于冗余，在修改时会导致数据的不一致，如改变一门课的老师 插入异常(Insert anomaly)：由于PK不能为空，如某系有位老师不教课；或者所开的课暂时无人选，则该位老师和所在的系名无法插入。 删除异常(deletion anomaly)：如果学生都退选一门课，则有关这门课的其他数据(教师，开课系)都将被删除。

规范化理论(Normalization) 模型的改进 上述各种异常的产生，主要来自关系的结构，是关系 的结构带来的限制，而不是现实世界的真实反映。 该关系实际反映了三个方面的信息：学生的成绩；教 师信息；系的信息；因此上面的关系应该进一部分解 ，形成两个实体。

规范化理论(Normalization) 模型改进的结果 学生成绩信息 学生姓名 课程名称 成绩 任课教师姓名 教师所在系 学生成绩信息 学生姓名(PK) 课程名称(PK) 成绩 课程信息 课程名称(PK) 任课教师姓名 教师所在系

规范化理论(Normalization) 3NF(3rd Normal Function，第三范式） 定义：关系R是3NF，当且仅当R是2NF，并且所有非PK属性都是非传递地依赖于PK 3NF要求属性之间不存在传递依赖。传递依赖是指实体中的某个属性a依赖于另外一个属性b，而且b有不是PK的一部分。

规范化理论(Normalization) 3NF(例子） 课程信息 课程名称(PK) 任课教师姓名 教师所在系 该关系不满足3NF，因为教师所在系传递依赖于PK 存在冗余和异常 插入异常/删除异常/更新异常

规范化理论(Normalization) 3NF(例子） 课程信息 课程名称(PK) 任课教师姓名 教师所在系 课程信息 课程名称(PK) 任课教师姓名 系信息 系名称

非规范化(Denormalization) 客观世界的复杂多变，影响最终设计结果的因素很多 ，并不是说一个模型的规范化程度越高越好。 有时候为了特定的目的还需要打破规范化，以数据的 冗余和异常来换取速度。这就是非规范化。

数据库的创建

数据库的创建 How Data is stored? Methods of Creating DB Shinking a DB or file Creating filegroups Creating Tables

How Data is stored? 数据库现在直接与文件对应。每一个数据库都对应 着至少一个数据文件(Data file)和日志文件(Log file) 主数据文件(Primary Data file):数据库的起点，存储数 据库对象的定义。后缀为.mdf 辅助数据文件(Secondary Data file)，后缀为.ndf 日志文件，存储数据库的事务日志信息。 初始建立时，拷贝Model库 Data Page = 8K,单行不能跨页

Methods of Creating DB T-SQL语句 SQL-EM

Shrinking DB or file DBCC shinkdatabase DBCC shinkfile Shrinking a DB automatically

Creating filegroups 把数据库的数据文件分成组，方便管理 作用 把某个特定的数据库对象建立在特定的组上 Can backup & restore a specific file group

Creating Tables 表(Table)是最重要的数据库对象 创建表的两个方法 T-SQL Create Table table_name() SQL-EM

数据完整性(Data Integrity)

数据完整性(Data Integrity) 定义 数据完整性的类型 如何保证数据完整性

数据完整性的定义 Data Integrity refers to the consistency and accuracy of data that is stored in a database 一致性 准确性

三种类型的数据完整性 Domain Integrity(域完整性) Entity Integrity(实体完整性) 字段允许取值的范围，以及值的格式 字段是否允许为null值 Entity Integrity(实体完整性) 每个表都需要有PK，并且是PK的那些列不能取相同的值，就 是说必须能够区分出不同的记录 Referential Integrity(引用完整性) PK和FK之间的关系必须被维持 如果记录被引用，则该记录不能被删除，PK不能被修改

如何保证数据完整性 声明完整性(Declaritive Data Integrity) 在表定义时声明 使用约束、缺省值和规则来实现 由SQL Server自动加以保证 过程完整性(Procedure Data Integrity) Client 在Server端使用trigger来实现

声明完整性的实现 约束(Constrints) 缺省值(Defaults) 规则(Rule)

声明完整性的实现-约束 Integrity Constraints provide a means of ensuring made to the database do not result in the loss of data consistency 约束的类型 default check referential primary key unique foreign key

DEFAULT Constraints Apply Only to INSERT Statements Only One DEFAULT Constraint Per Column USE library ALTER TABLE adult ADD CONSTRAINT firstname DEFAULT 'UNKNOWN' FOR firstname

CHECK Constraints Are Used with INSERT and UPDATE Statements Can Reference Other Columns in the Same Table USE library ALTER TABLE adult ADD CONSTRAINT phone_no CHECK (phone_no LIKE '(212)[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]')

PRIMARY KEY Constraints Only One PRIMARY KEY Constraint Per Table Values Must Be Unique Null Values Are Not Allowed Creates a Unique Index on Specified Columns USE library ALTER TABLE member ADD CONSTRAINT PK_member_member_no PRIMARY KEY CLUSTERED (member_no)

UNIQUE Constraints Allow Nulls Allow Multiple UNIQUE Constraints on a Table Defined with One or More Columns Enforced with a Unique Index USE sample_database ALTER TABLE employee ADD CONSTRAINT U_driver_lic_no UNIQUE NONCLUSTERED (driver_lic_no)

FOREIGN KEY Constraints Must Reference a PRIMARY KEY or UNIQUE Constraint USE library ALTER TABLE juvenile ADD CONSTRAINT FK_adult_memberno FOREIGN KEY (adult_memberno) REFERENCES adult(member_no)

Defaults and Rules As Independent Objects They: Are defined once Can be bound to one or more columns or user-defined data types CREATE DEFAULT phone_no_default AS '(000)000-0000' GO sp_bindefault phone_no_default, 'adult.phone_no_col' CREATE RULE statecode_rule AS @statecode in ('IA', 'IL', 'KS', 'MO') GO sp_bindrule statecode_rule, state_type

创建和使用索引

本部分介绍如下内容 1. 为什么要使用索引？ 2. 如何创建索引？ 3. 如何维护索引？

为什么要使用索引？ 数据的存储方式 数据页 数据盘区:1 Extent = 8 Pages 数据的访问方式 表扫描(Table Scan) 基于索引的查询(Index)

是否必须使用索引? 为什么需要建立索引？ 加快数据访问的速度 保证数据的唯一性(unique) 为什么不建立索引？ 增加了数据插入/修改的成本 在具有大量重复数据的列上创建索引没有价值

如果存在索引，SQL Server一定使用吗? 1.Determines Whether an Index Exists and Is Useful 2. Navigates Through the Index 3. Evaluates the Search Value Against Each Key Value and Repeats This Evaluation Until One of Following Occurs: The search value is not greater than or equal to the key value The search value is greater than or equal to the last key value on the index page

索引建立的一般准则 需要建立索引的列 PK/FK frequently searched in ranges frequently accessed in sorted order 不需要建立索引的列 不常使用的列 包含大量重复数据的列 bit, text, image列上不能被建立索引

索引的类型 聚簇索引(Clustered Index) 每表一个 物理顺序存储，在创建时要改变数据的物理存储顺 序，因此应该首先创建 创建时约需要1.2倍的空间 非聚簇索引(Non-Clustered Index) 缺省建立非聚簇索引 当一个Clustered Index建立或删除时，要重新建立 249 NCI/Table

索引的类型（续） 唯一索引(Unique Index) 保证在建立唯一索引的字段上，不同记录之间具有 不同的值 如果存在PK或Unique约束的话，自动建立唯一索引 复合索引(Composite Index) 经常用在一起查询的字段上建立复合索引 复合索引至多包含16个字段 index(c1,c2)与index(c2,c1)是不同的

如何创建索引？ 语法 CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX index_name ON table (column [,...n]) [WITH [PAD_INDEX] [[,] FILLFACTOR = fillfactor] [[,] IGNORE_DUP_KEY] [[,] DROP_EXISTING] [[,] STATISTICS_NORECOMPUTE] ] [ON filegroup]

如何维护索引？ Data Fragmentation 当数据插入/删除/修改时，引起数据碎片 Methods of Managing Fragmentation 重建Clustered Index Rebuild an index and specify a fillfactor value Business Environment Can be good for OLTP environment Can be bad for OLAP Services environment

帮助检查索引的几个工具 Index Tuning Wizard DBCC SHOWCONTIG Whether a table or index is heavily fragmented Whether data and index pages are full

数据查询技术

本部分介绍下面的内容 T-SQL介绍 单表查询技术 多表连接查询 数据的分组与汇总

T-SQL介绍 SQL语言的组成 Data Definition Language Statements(DDL) Create/Alter/Drop Data Manipulation Language Statements(DML) Insert/Delete/Update/select Data Control Language Statements(DCL) grant/deny/revoke Additional Language Elements

Additional Language Elements 变量 使用Declare语句定义 使用Set语句赋值 例如： declare @vname char(20) set @vname = ‘张三’ select * from students where name=@vname

Additional Language Elements(续) 操作符(Operators) 数学运算符 + - * / % 比较符 = > < >= <= <> 字符串连接符 + 如‘abc’+‘def’=‘abcdef’ 逻辑操作符 AND NOT OR

Additional Language Elements(续) 函数 Rowset Fuctions(行集函数)：返回一个集合的函数。该 类函数的返回结果可以象一个表一样被使用。如 OpenQuery,OpenRowset, ContainsTable,FreeTextTable Aggregate Functions(聚集函数)：对一个集合进行操作 ，返回一个值，如求最大，最小，平均、和等 Scalar Fuctions(标量函数):对一个值进行操作，返回一 个值，如select db_name()

Additional Language Elements(续) 执行流程控制语句 begin..end if..else while case case expression {when expression then result}[,…n]

基本数据查询技术 如何从表中提取数据？ 如何格式化提取到的数据？ 如何修改数据？

如何从表中提取数据？ 要解决的问题是： 从哪个表中提取？ 提取哪些记录？ 提取每条记录的哪些字段？

SELECT语句 指定查询哪些字段 使用WHERE子句限定记录 使用FROM子句限定表名 Partial Syntax SELECT [ALL | DISTINCT] <select_list> FROM {<table_source>} [,…n] WHERE <search_condition>

限定字段 employeeid lastname firstname title USE northwind 1 Davolio Nancy Sales Representative 2 Fuller Andrew Vice President, Sales 3 Leverling Janet 4 Peacock Margaret 5 Buchanan Steven Sales Manager 6 Suyama Michael 7 King Robert 8 Callahan Laura Inside Sales Coordinator 9 Dodsworth Anne USE northwind SELECT employeeid, lastname, firstname, title FROM employees

限定记录 employeeid lastname firstname title 5 Buchanan Steven Sales Manager USE northwind SELECT employeeid, lastname, firstname, title FROM employees WHERE employeeid = 5

WHERE 子句的构造 Using Comparison Operators Using String Comparisons Using Logical Operators Retrieving a Range of Values Using a List of Values as Search Criteria Retrieving Unknown Values

Using Comparison Operators lastname city Davolio Seattle Fuller Tacoma Leverling Kirkland Peacock Redmond Callahan USE northwind SELECT lastname, city FROM employees WHERE country = 'USA'

Using String Comparisons companyname GROSELLA-Restaurante Lonesome Pine Restaurant Tortuga Restaurante USE northwind SELECT companyname FROM customers WHERE companyname LIKE '%Restaurant%'

Using Logical Operators productid productname supplierid unitprice 14 Tofu 6 23.25 29 Thüringer Rostbratwurst 12 123.79 62 Tarte au sucre 49.3 USE northwind SELECT productid, productname, supplierid, unitprice FROM products WHERE (productname LIKE 'T%' OR productid = 46) AND (unitprice > 16.00)

Retrieving a Range of Values productname unitprice Chai 18 Chang 19 Aniseed Syrup 10 Genen Shouyu 15.5 Pavlova 17.45 Sir Rodney’s Scones . USE northwind SELECT productname, unitprice FROM products WHERE unitprice BETWEEN 10 AND 20

Using a List of Values as Search Criteria companyname country Tokyo Traders Japan Mayumi’s Formaggi Fortini s.r.l. Italy Pasta Buttini s.r.l. USE northwind SELECT companyname, country FROM suppliers WHERE country IN ('Japan', 'Italy')

Retrieving Unknown Values USE northwind SELECT companyname, fax FROM suppliers WHERE fax IS NULL companyname fax Exotic Liquids NULL New Orleans Cajun Delights Tokyo Traders Cooperativa de Quesos ‘Las Cabras’ .

查询结果的格式化 排序 去掉重复的记录 更改列标题 数据的分组与汇总

排序：Order by 子句 USE northwind SELECT productid, productname, categoryid, unitprice FROM products ORDER BY categoryid, unitprice DESC productid 38 43 2 . 63 8 61 productname Cote de Blaye Ipoh Coffee Chang Vegie-spread Northwoods Cranberry Sauce Sirop d'érable categoryid 1 unitprice 263.5 46 19 43.9 40 28.5

去掉重复的记录：distinct关键字 country Australia USE northwind Brazil Canada Denmark Finland France Germany Italy Japan Netherlands Norway Singapore Spain Sweden UK USA USE northwind SELECT DISTINCT country FROM suppliers ORDER BY country

更改字段标题：AS 关键字 First Last 职工编号: USE northwind Nancy Andrew Janet Margaret Steven Michael Robert Laura Anne Last Davolio Fuller Leverling Peacock Buchanan Suyama King Callahan Dodsworth 职工编号: 1 2 3 4 5 6 7 8 9 USE northwind SELECT firstname AS First, lastname AS Last, employeeid AS ‘职工编号:' FROM employees

数据的分组与汇总 聚集函数 GROUP BY Generating Aggregate Values Within Result Sets COMPUTE 和 COMPUTE BY 子句 找出最大(小)的n个记录

聚集函数 Aggregate function Description AVG Average of values in a numeric expression COUNT Number of values in an expression COUNT (*) Number of selected rows MAX Highest value in the expression MIN Lowest value in the expression SUM Total values in a numeric expression STDEV Statistical deviation of all values STDEVP Statistical deviation for the population VAR Statistical variance of all values VARP Statistical variance of all values for the population

GROUP BY 子句：先分组，再使用聚集函数 USE northwind SELECT productid, SUM(quantity) AS total_quantity FROM orderhist GROUP BY productid USE northwind SELECT productid, orderid, quantity FROM orderhist productid orderid quantity 1 5 10 2 25 3 15 30 productid total_quantity 1 15 2 35 3 45 productid total_quantity 2 35 Only rows that satisfy the WHERE clause are grouped USE northwind SELECT productid, SUM(quantity) AS total_quantity FROM orderhist WHERE productid = 2 GROUP BY productid

使用 HAVING 子句选择你想要的组 productid orderid quantity 1 5 10 2 25 3 15 30 USE northwind SELECT productid, orderid, quantity FROM orderhist USE northwind SELECT productid, SUM(quantity) AS total_quantity FROM orderhist GROUP BY productid HAVING SUM(quantity) >=30 productid orderid quantity 1 5 10 2 25 3 15 30 productid total_quantity 2 35 3 45

使用 COMPUTE 和 COMPUTE BY 子句 USE northwind SELECT productid, orderid, quantity FROM orderhist ORDER BY productid, orderid COMPUTE SUM(quantity) BY productid COMPUTE SUM(quantity) COMPUTE COMPUTE BY productid orderid quantity 1 5 2 10 25 3 15 30 sum 95 sum 15 sum 35 sum 45

找出最…的几条记录 Lists Only the First n Rows of a Result Set Specifies the Range of Values in the ORDER BY Clause Returns “Ties” if WITH TIES Is Used USE northwind SELECT TOP 5 orderid, productid, quantity FROM [order details] ORDER BY quantity DESC USE northwind SELECT TOP 5 WITH TIES orderid, productid, quantity FROM [order details] ORDER BY quantity DESC

数据修改 插入新记录 删除已经存在的记录 修改已经存在的记录

插入新记录 USE northwind INSERT customers (customerid, companyname, contactname, contacttitle, address, city, region, postalcode, country, phone, fax) VALUES ('PECOF', 'Pecos Coffee Company', 'Michael Dunn', 'Owner', '1900 Oak Street', 'Vancouver', 'BC', 'V3F 2K1', 'Canada', '(604) 555-3392', '(604) 555-7293')

删除已经存在的记录 使用DELETE语句来删除 可以删除一条或多条记录 使用Where子句限定删除的范围 记入日志 使用TRUNCATE TABLE语句进行整表删除 删除所有记录 不记日志，速度快

如何修改数据？ 使用WHERE子句限定要修改哪些记录 使用SET关键字来赋值 USE northwind UPDATE products SET unitprice = (unitprice * 1.1)

多表连接查询 多表连接查询 什么是连接(join)? 内连接（Inner Joins） 外连接（Outer Joins） 交叉连接（Cross Joins）f 合并多个查询结果集 union

什么是连接（Join） A Join: Selects Specific Columns from Multiple Tables JOIN keyword specifies that tables are joined and how to join them ON keyword specifies join condition Queries Two or More Tables to Produce a Result Set Use primary and foreign keys as join conditions Use columns common to specified tables to join tables

内连接(Inner Joins) sales buyers Result USE joindb SELECT buyer_name, sales.buyer_id, qty FROM buyers INNER JOIN sales ON buyers.buyer_id = sales.buyer_id sales buyer_id prod_id qty 1 4 3 2 5 15 37 11 1003 buyers buyer_name Adam Barr Sean Chai Eva Corets Erin O’Melia Result

外连接（ Outer Joins） sales buyers Result USE joindb SELECT buyer_name, sales.buyer_id, qty FROM buyers LEFT OUTER JOIN sales ON buyers.buyer_id = sales.buyer_id sales buyer_id prod_id qty 1 4 3 2 5 15 37 11 1003 buyers buyer_name Adam Barr Sean Chai Eva Corets Erin O’Melia Result NULL

交叉连接（ Cross Joins） Result sales buyers USE joindb SELECT buyer_name, qty FROM buyers CROSS JOIN sales Result buyer_name Adam Barr qty 15 5 37 11 1003 Sean Chai Eva Corets ... sales buyer_id prod_id 1 4 3 2 buyers Erin O’Melia

多于两个表的连接 SELECT buyer_name, prod_name, qty FROM buyers JOIN sales ON buyers.buyer_id = sales.buyer_id JOIN produce ON sales.prod_id = produce.prod_id produce prod_id prod_name 1 2 3 4 Apples Pears Oranges Bananas 5 Peaches buyers buyer_id buyer_name Adam Barr Sean Chai Eva Corets Erin O’Melia sales qty 15 37 11 1003 Result

自身连接(Self joining) sales b sales a Result USE joindb SELECT a.buyer_id AS buyer1, a.prod_id, b.buyer_id AS buyer2 FROM sales a JOIN sales b ON a.prod_id = b.prod_id WHERE a.buyer_id > b.buyer_id sales b buyer_id prod_id qty 1 4 3 2 5 15 37 11 1003 sales a Result buyer1 buyer2

合并多个结果集 Use the UNION Operator to Create a Single Result Set from Multiple Queries Each Query Must Have: Similar data types Same number of columns Same column order in select list USE northwind SELECT name = (firstname + ' ' + lastname), city, postalcode FROM employees UNION SELECT companyname, city, postalcode FROM customers

将得到的结果集插入到一个新表中 Using the SELECT INTO Statement Requires unique table name You can create a local or global temporary table Set the select into/bulkcopy database option on in order to create a permanent table USE northwind SELECT productname AS products, unitprice AS price, (unitprice * 1.1) AS tax INTO #pricetable FROM products

高级数据查询技术— 子查询

Overview Introduction to Subqueries Nested Subqueries（嵌套子查询） Correlated Subqueries（相关子查询） Using the EXISTS and NOT EXISTS Keywords（测试记录是 否存在） Modifying Data

Introduction to Subqueries 查询语句A是另外一个SQL语句B的一部分，则A称作是 B的子查询，如：select name from students where studentid in (select studentid from stuent-exam where grade=1000) Why to Use Subqueries To break down a complex query into a series of logical steps To answer a query that relies on the results of an other query Why to Use Joins Rather Than Subqueries SQL Server executes joins faster than subqueries

Using Subqueries Enclose Subqueries in Parentheses Use Only One Expression or Column Name in the select list Use Subqueries in Place of an Expression Cannot Use Subqueries on Columns Containing Text and Image Data Types No Limit to the Levels of Subqueries

Nested Subqueries(嵌套子查询) 嵌套子查询作为外层查询的一部分而存在； 在执行时首先执行子查询，然后再执行外部查询 子查询的执行结果可以是单个值，也可以返回一个值 的集合

Returning a Single Value Subquery Replaces Expression in: select list WHERE clause introduced with a comparison operator USE northwind SELECT orderid, customerid FROM orders WHERE orderdate = (SELECT max(orderdate) FROM orders)

Returning a List of Values Subquery Replaces Expression in: WHERE clause introduced with the IN keyword USE northwind SELECT companyname FROM customers WHERE customerid IN (SELECT customerid FROM orders WHERE orderdate >'1/1/95')

Correlated Subqueries(相关子查询) Inner Query Relies on Information from the Outer Query Use Aliases to Distinguish Between Tables Consider Using Joins

Evaluating a Correlated Subquery Back to Step 1 SELECT orderid, customerid FROM orders or1 WHERE 20 < (SELECT quantity FROM [order details] od WHERE or1.orderid = od.orderid AND od.productid = 23) Outer query passes column values to the inner query 1 Inner query uses that value to satisfy the inner query 2 Inner query returns a value back to the outer query 3 The process is repeated for the next column value of the outer query 4

Using the EXISTS and NOT EXISTS Keywords Use with Correlated Subqueries Determine Whether Data Exists in a List of Values SQL Server Process Outer query tests for the existence of rows Inner query returns TRUE or FALSE Data is not produced USE northwind SELECT lastname, employeeid FROM employees e WHERE EXISTS (SELECT * FROM orders WHERE e.employeeid = orders.employeeid AND orderdate = '9/5/97')

Modifying Data Using the INSERT…SELECT Statement Deleting Rows Based on Other Tables Updating Rows Based on Other Tables

Using the INSERT…SELECT Statement All Rows That Satisfy the SELECT Statement Are Inserted Verify That the Table That Receives New Row Exists Ensure That Data Types Are Compatible Determine Whether Default Values Exist or Whether Null Values Are Allowed USE northwind INSERT customers SELECT substring (firstname, 1, 3) + substring (lastname, 1, 2), lastname, firstname, title, address, city, region, postalcode, country, homephone, NULL FROM employees

Deleting Rows Based on Other Tables Using Additional FROM Clause First FROM clause indicates table to modify Second FROM clause specifies restricting criteria for the DELETE statement delete from [order details] from orders o inner join [order details] od on o.orderid=od.orderid where orderdate = ‘4/14/1998’ Specifying Conditions in the WHERE Clause Subqueries determine which rows to delete delete from [order details] where orderid in (select orderid from orders where orderdate = ‘4/14/1998’)

Updating Rows Based on Other Tables Using the UPDATE Statement Updates a row once Updates columns or variable names that follow the SET keyword Using Joins Uses the FROM clause Using Subqueries Returns a single value for each row

事务(Transactions)和 锁 (Locks)

Overview Introduction to Transactions and Locks Managing Transactions SQL Server Locking Managing Locks

Introduction to Transactions and Locks Transactions Ensure That Multiple Data Modifications Are Processed Together 事务的四个特点： 原子性(Atomity)，一致性(Consistency)， 隔离性(Isolation)，持久性(Durability) Locks Prevent Update Conflicts Transactions are serializable Locking is automatic Locks allow concurrent use of data

Managing Transactions SQL Server Transactions 隐式(Implicit)事务:Insert,Delete,Update语句自动就是一 个事务 显式(explicit)事务/用户定义的事务： begin tran/commit tran/rollback tran Transaction Recovery and Checkpoints Setting the Implicit Transactions Option

Transaction Recovery and Checkpoints Action Required 1 Roll forward None Roll back 2 3 4 Roll forward 5 Roll back Checkpoint System Failure

Setting the Implicit Transactions Option Automatically Starts a Transaction When You Execute Certain Statements(P306) select / insert /delete /update/…. Transaction Must Be Explicitly Completed with COMMIT or ROLLBACK TRANSACTION By Default, Setting Is Off SET IMPLICIT_TRANSACTIONS ON

SQL Server Locking Concurrency Problems Prevented by Locks Lockable Resources Types of Locks Lock Compatibility

Concurrency Problems Prevented by Locks Lost Update(丢失更新） Uncommitted Dependency (Dirty Read，脏读) Inconsistent Analysis (Nonrepeatable Read)（不可重复读 ，读同一条记录，两次读的结果不一样） Phantoms（幽灵数据）

Lockable Resources Item Description RID Row identifier Key Row lock within an index Page Data page or index page Group of pages Database Entire database Table Extent Entire table

Types of Locks Basic Locks Shared（共享锁，也叫“读”锁） Exclusive（排它锁，也叫“写”锁） Special Situation Locks Intent Update Schema Bulk update

Lock Compatibility（锁的相容性） Locks May or May Not Be Compatible with Other Locks Examples（P313) Shared locks are compatible with all locks except exclusive Exclusive locks are not compatible with any other locks Update locks are compatible only with shared locks

Deadlocks To Minimize Deadlocks: Use Resources in the Same Sequence Table B Table A Transaction 1 Deadlock Lock Transaction 2 Message 1205 To Minimize Deadlocks: Use Resources in the Same Sequence Minimize the Number of Steps in a Transaction Avoid Queries That Perform Mass Updates Minimize Indexing

分布式查询 (Distributed Query)

Overview Introduction to Distributed Queries Executing an Ad Hoc Query on a Remote Data Source Setting Up a Linked Server Environment Executing a Query on a Linked Server Executing a Stored Procedure on a Linked Server Modifying Data on a Linked Server Distributing Data

Introduction to Distributed Queries 同时从多个数据库服务器中提取数据 Accessing Remote Data Ad hoc query Linked server query Specifying Where to Process Distributed Queries Local SQL Server Remote OLE DB data source (pass-through query)

Executing an Ad Hoc Query on a Remote Data Source Use the OPENROWSET Function When You Do Not Expect to Use the Data Source Repeatedly Use the OPENROWSET Function to Access Remote Data Without Setting Up a Linked Server SELECT a.* FROM OPENROWSET('SQLOLEDB', 'LONDON1'; 'newcustomer';'mypassword', 'SELECT productid, unitprice FROM northwind.dbo.products ORDER BY unitprice') AS a

Setting Up a Linked Server Environment Local SQL Server Remote SQL Server SQL Server allows access to other data sources Remote servers must be linked to the local computer running SQL Server Remote SQL Server Other Data Sources

Establishing Linked Server Security Local Server Must Log In to Remote Server on Behalf of User If User’s Login Account Exists on Both Servers, It Can Be Used to Log In to Remote Server Map Login Accounts and Passwords Between Servers by Using sp_addlinkedsrvlogin Without Security Delegation, Map Local Login Account to Login Account on the Linked Server EXEC sp_addlinkedsrvlogin @rmtsrvname = AccountingServer, @useself = false, @locallogin = 'Accountwriter', @rmtuser = 'rmtAccountWriter', @rmtpassword = 'financepass'

Executing a Query on a Linked Server Working with Linked Servers Executing Linked Server Queries Executing Pass-Through Queries

Working with Linked Servers Referring to Objects on Linked Servers Full Qualified Object Name: ServerName.DbName.Owner.ObjectName Allowed Transact-SQL Statements SELECT, INSERT, UPDATE, DELETE

Executing Linked Server Queries Use Fully Qualified Names to Reference Objects on Linked Servers Linked Server Queries Are Processed Locally SELECT companyname FROM AccountingServer.northwindremote.dbo.suppliers

Executing Pass-Through Queries Use the OPENQUERY Function to Execute Pass-Through Queries on a Linked Server Use the OPENQUERY Function in a SELECT Statement in Place of a Table Name Use the Result of an OPENQUERY Function as the Target Table of an INSERT, UPDATE, or DELETE Statement SELECT * FROM OPENQUERY (AsiaServer, 'SELECT productid, royalty FROM Northwind.dbo.ProductInfo')

Executing a Stored Procedure on a Linked Server Local Server Remote Server Stored Procedure Call Parameters and Output Stored Procedure Processing User EXEC accounting.master.dbo.sp_helpntgroup

视图(Views)

Overview What Is a View Advantages of Views Defining Views

What Is a View？ 视图是预先定义并存储在数据库中查询语句 title title_no author synopsis 1 2 3 Last of the Mohicans The Village Watch-Tower Poems James Fenimore Cooper Kate Douglas Wiggin Wilfred Owen ~~~ USE library GO CREATE VIEW dbo.TitleView AS SELECT title, author FROM title TitleView title author Last of the Mohicans The Village Watch-Tower Poems James Fenimore Cooper Kate Douglas Wiggin Wilfred Owen User’s View

Advantages of Views Focus the Data for Users Focus on important or appropriate data only Limit access to sensitive data Mask Database Complexity Hide complex database design Simplify complex queries, including distributed queries to heterogeneous data Simplify Management of User Permissions Organize Data for Export to Other Applications

Creating Views Creating a View Restrictions on View Definitions Cannot include ORDER BY clause Cannot reference INTO keyword USE library GO CREATE VIEW dbo.UnpaidFinesView (Member, TotalUnpaidFines) AS SELECT member_no, (sum(fine_assessed-fine_paid)) FROM loanhist GROUP BY member_no HAVING SUM(fine_assessed-fine_paid) > 0 GO

Avoiding Broken Ownership Chains Dependent Objects with Different Owners Example: Maria executes: Pierre executes: maria.view2 lucia.view1 lucia.table1 GRANT SELECT ON view2 TO pierre SELECT * FROM maria.view2

Hiding View Definitions Use the WITH ENCRYPTION Option Do Not Delete Entries in the syscomments Table USE library GO CREATE VIEW dbo.UnpaidFinesView (Member, TotalUnpaidFines) WITH ENCRYPTION AS SELECT member_no, (sum(fine_assessed-fine_paid)) FROM loanhist GROUP BY member_no, fine_assessed, fine_paid HAVING SUM(fine_assessed-fine_paid) > 0 GO

Modifying Data Through Views Cannot Affect More Than One Underlying Table Cannot Be Made to Certain Columns(Calculated,Aggregate) Are Verified if the WITH CHECK OPTION Has Been Specified

Performance Considerations title 1 ~ n 2 3 y 4 5 6 USE library GO CREATE VIEW dbo.LoanableView AS SELECT * FROM CopywideView WHERE loanable = 'y' item 1 ~ n 2 3 y 4 5 6 copy 1 ~ n 2 3 y 4 5 6 CopywideView 1 ~ n 2 3 y 4 5 6 LoanableView 3 ~ ~ ~ y 4 ~ ~ ~ y 6 ~ ~ ~ y SELECT DISTINCT isbn, title, author FROM LoanableView WHERE translation = 'french'

存储过程（ Stored Procedures）

Overview Introduction to Stored Procedures Creating, Executing, and Modifying Stored Procedures Using Parameters in Stored Procedures

Advantages of Stored Procedures Share Application Logic Shield Database Schema Details Provide Security Mechanisms Improve Performance Reduce Network Traffic

Creating Stored Procedures Create in Current Database with the CREATE PROCEDURE Statement Can Nest to 32 Levels Use sp_help to Display Information Use library GO CREATE PROC dbo.overdue_books AS SELECT * FROM dbo.loan WHERE due_date < GETDATE() GO

Executing Stored Procedures Executing a Stored Procedure by Itself Executing a Stored Procedure Within an INSERT Statement EXEC overdue_books INSERT INTO customers EXEC employee_customer

Using Parameters in Stored Procedures Using Input Parameters Executing Stored Procedures with Input Parameters Returning Values with Output Parameters

Using Input Parameters Trap Missing or Invalid Parameter Values First Provide Appropriate Default Values CREATE PROC dbo.find_isbn @title longstring = null, @translation char(8) = 'English' AS IF @title is null BEGIN PRINT "Please provide a title (or partial title) and the translation" PRINT "find_isbn 'Oliver%', 'Japanese'" . . .

Executing Stored Procedures with Input Parameters Passing Values by Reference Passing Values by Position EXEC addadult @firstname = 'Linda', @lastname = 'LaBrie', @street = 'Dogwood Drive', @city = 'Sacramento', @state = 'CA', @zip = '94203' EXEC addadult 'LaBrie', 'Linda', null, 'Dogwood Drive', 'Sacramento', 'CA', '94203', null

Returning Values with Output Parameters CREATE PROCEDURE dbo.mathtutor @m1 smallint, @m2 smallint, @result smallint OUTPUT AS SET @result = @m1 * @m2 DECLARE @answer smallint EXECUTE mathtutor 5, 6, @answer OUTPUT SELECT 'The result is: ' , @answer The result is: 30 Creating Stored Procedure Executing Stored Procedure Results of Stored Procedure

Executing Extended Stored Procedures Increase SQL Server Functionality Are Programmed Using Open Data Services API Can Include C and C++ Features Can Contain Multiple Functions Can Be Called from a Client or SQL Server Can Be Added to the master Database Only EXEC master..xp_cmdshell 'dir c:\mssql7'

触发器（Triggers）

Overview Introduction to Triggers Defining Triggers Examples of Triggers

What Is a Trigger Associated with a Table Invoked Automatically Cannot Be Called Directly Is a Transaction

Uses of Triggers Cascade Changes Through Related Tables in a Database Enforce More Complex Data Integrity Than a CHECK Constraint Maintain Denormalized Data Compare Before and After States of Data Under Modification

Considerations for Using Triggers Triggers Are Reactive; Constraints Are Proactive Constraints Are Checked First Tables Can Have Multiple Triggers for Any Action Table Owners Must Have Permission Cannot Create Triggers on Views or Temporary Tables Triggers Should Not Return Result Sets Triggers Can Handle Multi-Row Actions

Creating Triggers CREATE TRIGGER Statement CREATE TRIGGER member_delete ON member FOR DELETE AS IF EXISTS (SELECT d.member_no FROM loan l INNER JOIN deleted d ON l.member_no = d.member_no ) BEGIN RAISERROR ('Member has Books Checked Out *** Cannot Delete', 10, 1) ROLLBACK TRANSACTION END

How Triggers Work How an INSERT Trigger Works How a DELETE Trigger Works How an UPDATE Trigger Works How Nested Triggers Work Recursive Triggers

How an INSERT Trigger Works INSERT Statement to a Table with an INSERT Trigger Defined INSERT Statement Logged Trigger Actions Executed 1 2 3 loan isbn copy_no title_no mem_no outdate duedate 1 4 3 2 1001 1004 1002 1003 02/13/91 02/14/91 02/27/91 02/28/91 603 11 123 02/15/91 03/17/91 TRIGGER Actions Execute Trigger Code: USE library CREATE TRIGGER loan_insert ON loan FOR INSERT AS UPDATE c SET on_loan = 'Y' FROM copy c INNER JOIN inserted I ON c.isbn = i.sbn and c.copy_no = i.copy_no INSERT statement to a table with an INSERT Trigger Defined INSERT loan VALUES (603, 4, 11, 123, GETDATE(), (GETDATE() + 30)) loan isbn copy_no title_no mem_no outdate duedate 1 4 3 2 1001 1004 1002 1003 02/13/91 02/14/91 02/27/91 02/28/91 loan isbn copy_no title_no mem_no outdate duedate 1 4 3 2 1001 1004 1002 1003 02/13/91 02/14/91 02/27/91 02/28/91 loan isbn copy_no title_no mem_no outdate duedate 1 4 3 2 1001 1004 1002 1003 02/13/91 02/14/91 02/27/91 02/28/91 UPDATE c SET on_loan = 'Y' FROM copy c INNER JOIN inserted I ON c.isbn = isbn and c.copy_no = i.copy_no 603 4 11 123 02/15/91 03/17/91 copy isbn copy_no title_no on_loan 1 4 3 2 1001 1004 1002 Y N 603 11 inserted 603 4 11 123 02/15/91 03/17/91 Insert statement logged

How a DELETE Trigger Works Trigger Actions Execute USE library CREATE TRIGGER loan_delete ON loan FOR DELETE AS UPDATE c SET on_loan = 'N' FROM copy c INNER JOIN deleted d ON c.isbn = d.isbn AND c.copy_no = d.copy.no copy isbn copy_no title_no on_loan 1 4 3 2 1001 1004 1002 Y N DELETE Statement to a Table with a DELETE Statement Defined DELETE Statement Logged Trigger Actions Executed 1 2 3 DELETE Statement to a table with a DELETE Trigger Defined DELETE loan WHERE isbn = 4 AND copy_no = 1 loan isbn copy_no title_no mem_no outdate duedate 1 4 3 2 1001 1004 1002 1003 02/13/91 02/14/91 02/27/91 02/28/91 loan isbn copy_no title_no mem_no outdate duedate 1 4 3 2 1001 1004 1002 1003 02/13/91 02/14/91 02/27/91 02/28/91 loan isbn copy_no title_no mem_no outdate duedate 1 4 3 2 1001 1004 1002 1003 02/13/91 02/14/91 02/27/91 02/28/91 4 1 1004 1001 02/13/91 02/27/91 UPDATE c SET on_loan = 'N' FROM copy c INNER JOIN deleted d ON c.isbn = d.isbn AND c.copy_no = d.copy.no Deleted 4 1 1004 1001 02/13/91 02/27/91 DELETE statement logged

How an UPDATE Trigger Works TRIGGER Actions Execute USE library GO CREATE TRIGGER member_update ON member FOR UPDATE AS IF UPDATE (member_no) BEGIN RAISERROR ('Transaction cannot be processed.\ ***** Member number cannot be modified.', 10, 1) ROLLBACK TRANSACTION Transaction cannot be processed. ***** Member number cannot be modified member member_no lastname firstname middleinitial photograph 10020 10021 10022 10023 Anderson Barr Andrew Bill A R NULL B ~~~ 1234 UPDATE Statement to a Table with an UPDATE Trigger Defined UPDATE Statement Logged as INSERT and DELETE Statements Trigger Actions Executed 1 2 3 UPDATE Statement to a table with an UPDATE Trigger Defined UPDATE member SET member_no = 10021 WHERE member_no = 1234 member member_no lastname firstname middleinitial photograph 10020 10021 10022 10023 Anderson Barr Andrew Bill A R NULL B ~~~ 1234 UPDATE Statement logged as INSERT and DELETE Statements inserted 10021 Barr Andrew R ~~~ deleted 1234 Barr Andrew R ~~~

How Nested Triggers Work Item will be due on 5/22/98 loan_update loan_update trigger executes Executes UPDATE statement on copy table copy_update loan isbn copy_no title_no mem_no outdate duedate 1 4 3 2 1001 1004 1002 1003 02/13/91 02/14/91 02/27/91 02/28/91 copy on_loan Y N copy_update trigger executes Sends message

Recursive Triggers Activating a Trigger Recursively Types of Recursive Triggers Determining Whether to Use Recursive Triggers Use to maintain complex relationships Require recursion termination check in trigger definition

Examples of Triggers Enforcing Data Integrity Enforcing Business Rules

Enforcing Data Integrity CREATE TRIGGER reservation_delete ON loan FOR INSERT AS IF (SELECT r.member_no FROM reservation r JOIN inserted i ON r.member_no = i.member_no AND r.isbn = i.isbn ) > 0 BEGIN DELETE r FROM reservation r INNER JOIN inserted i ON r.member_no = i.member_no AND r.isbn = i.isbn END loan isbn copy_no mem_no 1 4 3 2 7 Inserted reservation log_date 07/14/98 07/12/98 06/07/98 remarks ~~~ Trigger Deletes Row

Enforcing Business Rules Members with Outstanding Loans Cannot Be Deleted IF (Select Count (*) FROM loan INNER JOIN deleted ON loan.member_no = deleted.member_no) > 0 ROLLBACK TRANSACTION DELETE statement executed on member table Trigger code checks the loan table Transaction rolled back member member_no lastname firstname 1 3 4 Anderson Barr Andrew Bill member member_no lastname firstname 1 2 3 4 Anderson Barr Andrew Bill member member_no lastname firstname 1 2 3 4 Anderson Barr Andrew Bill loan isbn copy_no title_no mem_no 1 4 3 2 2 Barr Andrew 2 Barr Andrew 'Transaction cannot be processed' 'This member still has books on loan'

高级文本查询技术

Overview Microsoft Search Service Microsoft English Query

Introduction to Microsoft Search Service Provides Text-based Searches in SQL Server 7.0 Complex searches on unstructured text data for words and phrases More powerful than LIKE operator and wildcards Available in Other Microsoft Products Microsoft Index Server Microsoft Site Server

Microsoft Search Service Architecture Search Service Objects Full-text index Keeps track of significant words used in a table Requires unique key column or primary key Full-text catalog Collection of full-text indexes Microsoft Search Service Functions Indexing Querying

Comparing Full-Text Search Indexes to SQL Server Indexes SQL Server Indexes Full-Text Indexes Stored in database Stored in the file system Several indexes per table Only one full-text index per table Automatically updated Manually updated Not grouped Grouped in full-text catalog Maintained using Maintained using Transact-SQL stored procedures

Defining Full-Text Indexes Enable Database for Full-Text Search, Identify Tables and Columns Request Initial Population of Full-Text Index for a Catalog Refresh Contents of Full-Text Catalog 1 2 3

Maintaining Full-Text Indexes Repopulating Full-Text Indexes Full repopulation Incremental repopulation Noise words reduce size Deactivating Full-Text Indexes Backing Up and Moving Databases Full-text data is stored outside database Must fully repopulate the full-text indexes

Writing Full-Text Queries CONTAINS Search Condition FREETEXT Search Condition CONTAINSTABLE and FREETEXTTABLE Functions

CONTAINS Search Condition Searches on: Inflectional form of a specific word Word or phrase beginning with specific text Word or phrase near another word or phrase One or more specific words and phrases Words or phrases with weighted values SELECT plant_id, common_name, price FROM plants WHERE CONTAINS( *, ' "English Thyme" ' )

FREETEXT Search Condition Searches on Words, Phrases, and Sentences Extracted from Initial Query Less Precise Than CONTAINS SELECT * FROM news_table WHERE FREETEXT (description, ' "The Fulton County Grand Jury said Friday an investigation of Atlanta's recent primary election produced no evidence that any irregularities took place." ')

CONTAINSTABLE and FREETEXTTABLE Functions Features of CONTAINSTABLE and FREETEXTTABLE Return a table Return column that contains full-text key values Rank results USE Northwind GO SELECT FT_TBL.CategoryName, FT_TBL.Description, KEY_TBL.RANK FROM Categories AS FT_TBL INNER JOIN CONTAINSTABLE(Categories, Description, ’beers' ) AS KEY_TBL ON FT_TBL.CategoryID = KEY_TBL.[KEY] ORDER BY KEY_TBL.RANK DESC GO

游标(Cursors)

Overview What Is a Cursor Methods of Calling Cursors Transact-SQL Cursors

What Is a Cursor Points to a Specific Row Retrieves and Modifies Rows Based on Current Position Can Be Sensitive to Changes in Underlying Data

How Transact-SQL Cursors Work @tablename Categories CustomerCustomerDemo CustomerDemographics Customer dtproperties Employees . 2 DECLARE @tablename nvarchar(256) DECLARE tnames_cursor CURSOR FOR SELECT name FROM sysobjects WHERE objectproperty(id, 'IsUserTable')=1 ORDER BY name OPEN tnames_cursor FETCH NEXT FROM tnames_cursor INTO @tablename WHILE (@@FETCH_STATUS = 0) BEGIN EXEC sp_spaceused @tablename END CLOSE tnames_cursor DEALLOCATE tnames_cursor 1 2 3 4 5 @tablename Categories CustomerCustomerDemo CustomerDemographics Customer dtproperties Employees . DECLARE @tablename nvarchar(256) DECLARE tnames_cursor CURSOR FOR SELECT name FROM sysobjects WHERE objectproperty(id, 'IsUserTable')=1 ORDER BY name OPEN tnames_cursor FETCH NEXT FROM tnames_cursor INTO @tablename WHILE (@@FETCH_STATUS = 0) BEGIN EXEC sp_spaceused @tablename END CLOSE tnames_cursor DEALLOCATE tnames_cursor 1 @tablename Categories CustomerCustomerDemo CustomerDemographics Customer dtproperties Employees . 2 3 Results name rows reserved data index_size unused --------------------------------------------------------------- Categories 8 184 KB 112 KB 40 KB 32KB (1 row(s) affected) CustomerCustomerDemo 0 32 KB 8KB 24 KB 0 KB ... 4 5 DECLARE @tablename nvarchar(256) DECLARE tnames_cursor CURSOR FOR SELECT name FROM sysobjects WHERE objectproperty(id, 'IsUserTable')=1 ORDER BY name OPEN tnames_cursor FETCH NEXT FROM tnames_cursor INTO @tablename WHILE (@@FETCH_STATUS = 0) BEGIN EXEC sp_spaceused @tablename END CLOSE tnames_cursor DEALLOCATE tnames_cursor 1 3 4 Results name rows reserved data index_size unused --------------------------------------------------------------- Categories 8 184 KB 112 KB 40 KB 32KB (1 row(s) affected) CustomerCustomerDemo 0 32 KB 8KB 24 KB 0 KB ...

Types of Transact-SQL Cursors Cursor Type Membership Order Values Forward-only Dynamic Static Fixed Keyset-driven

Transact-SQL Cursor Characteristics Scrollability Sensitivity Locking Default Cursor Result Set Characteristics: Forward-only Read-only One row at a time

Transact-SQL Cursor Syntax Statement DECLARE CURSOR Description Defines cursor structure and allocates resources OPEN Populates a declared cursor with a result set FETCH Navigates within a cursor result set CLOSE Releases the current result set DEALLOCATE Removes cursor definition and deallocates resources

Working with Data from Cursors Modifying Data Through Cursors Must be declared FOR UPDATE Cannot be READ ONLY

通过游标修改数据 USE Northwind GO DECLARE abc CURSOR FOR SELECT CompanyName FROM Shippers OPEN abc FETCH NEXT FROM abc UPDATE Shippers SET CompanyName = N'Speedy Express, Inc.' WHERE CURRENT OF abc CLOSE abc DEALLOCATE abc

通过游标删除数据 USE Northwind GO DECLARE abc CURSOR FOR SELECT CompanyName FROM Shippers OPEN abc FETCH NEXT FROM abc Delete Shippers WHERE CURRENT OF abc CLOSE abc DEALLOCATE abc