第6章 实现SQL Server 2000数据完整性

6.1 完整性的概念（《数据库应用》P39）

  关系完整性：关系模型中数据的正确性、一致性和有效性。

  关系完整性包括实体完整性、参照完整性、用户定义的完整性。

  实体完整性（Entity Integrity）：主码不能为空。

  参照完整性（Reference Integrity）：

一个关系中的外码值或者为空，或者为被参照关系中的一个主码值。

例2-15 客户关系和订单关系（《数据库应用》P40）

  订单（订单号，客户号，雇员号，订单日期）

  客户（客户号，姓名，性别，联系电话，联系地址）

  用户定义的完整性（User - defined Integrity）：

对关系中的属性的取值所作出的限定。

6.2 约束的类型

6.2.1 建立表结构（《数据库应用》P75）

  语句格式：

  CREATE TABLE [数据库名.]<基本表名>

         (<列定义>,...[,<表级完整性约束>,...])

 1）列级完整性约束

  DEFAULT<常量表达式>：默认值约束。

  NULL/NOT NULL：空值/非空值约束。

  PRIMARY KEY：主码约束。

  UNIQUE：单值约束。注明该列取值互不相同。

  REFERENCES<父表名>(<主码>)：外码约束。

注明该列为外码，并给出对应的父表及父表中被参照的主码。

  CHECK(<逻辑表达式>)：检查约束。如“性别='男'or 性别='女'”

 2）表级完整性约束在所有列定义后给出。

  PRIMARY KEY(<列名>,...)主码约束。注明一个或同时多个列为主码。

  UNIQUE：单值约束。注明一个或同时若干个列为单值。

  FOREIGN KEY(<列名>,...)REFERENCES<父表名>(<主码列名>,...)

外码约束。注明一个或同时多个列为外码，

并给出对应的父表及父表中被参照的主码中的所有列。

  CHECK(<逻辑表达式>)：检查约束。

注明每行中一个或若干个列在取值上必须满足的条件。

如，check(工龄<年龄)。

 语句举例：

  create table 学生（

      学号 char(7) primary key,

      姓名 char(6) not null unique,

      性别 char(2) not null check(性别='男'or 性别='女'),

      出生日期 datetime check(出生日期<'1993-12-31'),

      专业 char(10),

      年级 int check(年级>=1 and 年级<=4)

    )

6.3 约束的创建（《数据库应用》P272）

 * 约束的用途是限制用户输入到表中的数据的值的范围。

 1.PRIMARY KEY 主码约束

 例1：创建表级主码约束

  create table 学生（

      学号 char(7) not null,

      姓名 char(6) not null,

      constraint PK_学生表 primary key(学号)

    )

 例2：创建列级主码约束

  create table 学生（

      学号 char(7) not null,

      constraint PK_学生表 primary key nonclustered,

      姓名 char(6) not null,

    )

   聚集索引（clustered）：按照索引键值重新排序。

   非聚集索引（nonclustered）：不按照索引键值排序。

 2.FOREIGN KEY 外码约束

   某列的取值必须参照另一个表的主码值。

 3.Default 默认约束

 4. UNIQUE：单值约束。对主码外的其它字段单值约束。

 5.CHECK 检查约束

 6.4 查看约束的定义（《数据库应用》P272-10）

  使用系统存储过程查看约束的定义：

  sp_helpconstraint 表名

 6.5 删除约束（《数据库应用》P275-9）

  alter table 表名

        drop constraint 约束名

 6.6 使用规则（《数据库应用》P275）

  1.使用 Transact SQL 创建和删除规则

    create rule 规则名 as 条件表达式

    drop 规则名

例1：创建学生的年龄必须在一定范围的规则 Age_rule。

    create rule Age_rule

    as @MyAge between 17 and 28

    @MyAge：局部变量

例2：创建图书定价必须在一定范围的规则图书定价_rule。

    create rule 图书定价_rule

    as @My图书定价 between 10 and 100

  2.使用 Transact SQL 绑定规则

    sp_bindrule 规则名,'表名.列名'

例：将规则与“学生表”中的年龄字段绑定

    sp_bindrule Age_rule,'学生表.年龄'

  3.使用企业管理器管理规则

    企业管理器的树型结构—右击规则—新建规则—

  规则名：如，图书定价_rule

  规则条件表达式：如，@My图书定价 between 10 and 100

  —确定。

    双击规则名—修改、绑定规则。

 6.7 使用默认（《数据库应用》P276）

  1.使用 Transact SQL 创建和删除默认

    create default 默认名 as 默认值

    drop 默认名

例1：创建一个默认日期值 def_data。

    create default def_data

    as '2005-01-01'

例2：创建图书定价默认值 图书定价_default。

    create default 图书定价_default as 33

  2.使用 Transact SQL 绑定默认

    sp_binddefault 默认名,'表名.列名'

例：将默认与“图书记录表”中的图书定价字段绑定

    sp_binddefault 图书定价_default,'图书记录表.图书定价'

  3.使用企业管理器管理默认

    企业管理器的树型结构—右击默认—新建默认—

默认名、默认条件表达式—确定。

    双击默认名—修改、绑定默认。

 6.8 数据完整性强制选择方法

 6.9 案例中的完整性实现

第7章 索引及其应用

 7.1 索引的基础知识

 1.数据表包含字段多，搜索扫描慢。

   索引表包含索引列，如，编号列，搜索简单的编号列快，

   根据索引表中存储的记录指针到数据表中找出相应记录。

 2.索引的分类

   聚集索引（clustered）：按照索引键值重新排序。

   非聚集索引（nonclustered）：不按照索引键值排序。

7.2 索引的操作

7.2.2 使用 SQL 企业管理器创建索引（P129）

  SQL 企业管理器 ==》选择数据库和表 ==》右击表 ==》

所有任务 ==》管理索引 ==》[New]按钮 ==》新索引名、索引关键字。

例如：创建图书记录表的图书定价索引。

选择数据库和表 ==》右击图书记录表 ==》所有任务 ==》管理索引

==》[New]按钮 ==》索引名：tsdj_index、索引关键字：图书定价 ==》

索引选项：聚集索引 ==》编辑 SQL 语句

CREATE CLUSTERED

INDEX [tsdj_index] ON [dbo].[图书记录表] ([图书定价])

WITH DROP_EXISTING ON [PRIMARY]

 ==》执行 ==》关闭。

7.3.1 使用 SQL 企业管理器更改索引名（P131）

例如：更改图书记录表的图书定价索引名。

选择数据库和表 ==》右击图书记录表 ==》所有任务 ==》管理索引

==》[Edit]按钮 ==》索引名：tsdj_ind。

7.4 使用 SQL 企业管理器删除索引（P132）

例如：删除图书记录表的图书定价索引。

选择数据库和表 ==》右击图书记录表 ==》所有任务 ==》管理索引

==》[Delete]按钮。

 7.5 设置索引的选项

 FillFactor 选项：

  在线事务处理使用低填充度，数据库存储使用高填充度。

 Sorted_Data_Reorg 选项：

  清除字段排序，可以减少创建索引的时间。

 7.6 索引的分析与维护（P134）

 DBCC ShowContig 语句

  显示表的数据和索引信息。

 DBCC ShowContig (table_id[,index_id])

例：打开 wssdk.mdb，显示图书记录表的数据和索引信息

 DBCC ShowContig (图书记录表,图书定价_index)

 DBCC DBreIndex 语句

  重建表的一个或多个索引。

 7.5 案例中的索引

第8章 数据的基本操作

8.1 数据的添加、修改和删除

  添加、修改和删除表中的记录。

8.1.1 插入部分数据（P108）

  应该指出插入数据的列名。

  举例：把《Flash5 网页设计》加入图书记录表中

  Insert Into

  图书记录表1(图书号,图书名称,作者,图书定价,出版社)

  values('55','Flash5 网页设计','李冬春等',36,'冶金工业')

8.1.2 修改表中的数据（P109）

 * Update 语句

   Set子句：指定要更新的列，

   Where子句：指定要更新的行。

   语法：

   UPDATE table_name

   SET columnname1=value1[,columnname2=value2]...

   WHERE search_condition

   举例：

   把图书类别为“大专教材”的行，图书定价改为 0.9*图书定价。

   Update 图书记录表1

   Set 图书定价=0.9*图书定价

   Where 图书类别='大专教材'

8.1.3 删除表中的数据（P110）

 * Delete 语句

   DELETE FROM table_name

   WHERE condition

   举例：删除图书记录表中图书类别为“大专教材”的行。

   Delete from 图书记录表1 Where 图书类别='大专教材'

8.2 简单查询

 SELECT 语句（P55）

 查询数据库中已有的数据

   SELECT 语句的基本语法格式：

   SELECT 字段列表

   [INTO 检索结果存储的新表]

   FROM 查询的表名

   [WHERE 搜索条件]

   [GROUP BY 分组表达式]

   [HAVIGN 搜索表达式]

   [ORDER BY 排序表达式[ASC|DESC] （P63）

 * 按要求查询图书记录表

   按图书名称、作者、出版社、图书类别

   进行分类查询、综合查询和按关键字搜索。

图书记录表

Access wssdk2k.rar SQL wssdk_Data.rar wssdk_Log.rar

8.2.1 选择列（P56）

 1.选择所有列

    select * from 图书记录表

        所有列

 2.选择特定列

   select 图书号,图书名称,图书定价,图书类别 from 图书记录表

 3.计算列

  select 图书名称,'会员价'=0.9*图书定价 from 图书记录表

 4.改变列标题：新列名=旧列名（P58）

  select tsmc=图书名称,tsdj=图书定价 from 图书记录表

8.2.3 WHERE 子句（P59）

  列出“大专教材”类的图书（准确查询用“=”号）。

   select 图书号,图书名称,图书定价 from 图书记录表 Where 图书类别='大专教材'

                 列名              表源            条件

 1.比较运算符

  列出不是“大专教材”类的图书。

   select * from 图书记录表 Where 图书类别<>'大专教材'

 2.范围运算符

  列出“图书定价”在30元~35元之间的图书。

   select * from 图书记录表 Where 图书定价 between $30 and $35

 3.列表运算符

  列出“大专教材”和“科学技术”类的图书。

   select * from 图书记录表 Where 图书类别 in('大专教材','科学技术')

 4.匹配符（P62）

   用类似 like 和“%”匹配任意字符，进行模糊查询。

 如，查找图书名称为'程序设计'结尾的图书。

   select * from 图书记录表 Where 图书名称 like '%计%'

   select * from 图书记录表 Where 图书名称 like '%程序设计'

   select * from 图书记录表 Where 图书名称 like '计算机%'

   select * from 图书记录表 Where 出版社 like '%大学'

 * SQL Server 2000中，使用选择 SELECT 语句查询检索数据

  工具栏—显示/隐藏SQL窗格，[！]运行SQL。

8.2.4 建立查询（P112）

 * SQL 核心语句：SELECT、UPDATE、INSERT、DELETE。

 1.打开 SQL 查询分析器两种方法：

   开始 ==》程序 ==》SQL 程序组 ==》SQL 查询分析器。

   SQL 企业管理器 ==》工具 ==》SQL 查询分析器。

 2.查询分析器工具栏

 [分析查询]、[执行查询]、

 打开数据库列表下拉钮选择数据库，如 wssdk。

 3.用有数据的数据库文件复盖无数据的数据库框架

 1）SQL 企业管理器中预先建无数据的数据库框架，

    如，E:\本地站点\mdb\wssdk_data.mdf wssdk_log.ldf。

 2）下载有数据的数据库压缩文件 wssdk_Data.rar wssdk_Log.rar，

   解压到 E:\本地站点\mdb1\ 中得 wssdk_data.mdf wssdk_log.ldf。

 3）关闭 SQL 服务管理器，用有数据的数据库文件复盖无数据的数据库

框架。

   若不带日志文件 wssdk_log.ldf，则系统自动创建新日志文件。

 4.执行连接数据库，如 wssdk

 1）SQL 企业管理器中预先不建数据库框架，

   下载 wssdk_Data.rar wssdk_Log.rar 文件，

   解压到 E:\本地站点\mdb\ 中得 wssdk_data.mdf wssdk_log.ldf。

 2）把数据库连接到 SQL Server 环境中后进行编辑。

    打开 SQL Server 2000 查询分析器，

   在查询窗口中复制粘贴执行连接数据库的代码 LJsjk.sql。

exec sp_attach_db 'wssdk',

'E:\本地站点\mdb\wssdk_Data.mdf',

'E:\本地站点\mdb\wssdk_Log.ldf'

 3）分析查询、执行查询，

 4）企业管理器 ==》树窗口 ==》可找到已经连接上的 wssdk

 5.查询分析器编辑 Transact-SQL 语句；

   SELECT *

   FROM 图书记录表

   WHERE 图书类别='大专教材'

   按工具栏[执行]按钮，执行查询和显示结果；

   用[文件]==》[另存为]保存查询结果。

8.2.5 分布式查询（P118）

  对象连接嵌入库（OLEDB）作外部数据接口：

  提供程序 PROVIDER：MICROSOFT.JET.OLEDB.4.0;

  数据源 DATA SOURCE：

  影射站点根目录（如，E:/本地站点/）后的路径。

  如 Server.MapPath ("/mdb/wssdk.mdb")。

  set cn=Server.CreateObject("ADODB.Connection")

  cn.open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;

  DATA SOURCE="+Server.MapPath ("/mdb/wssdk.mdb")

8.3 连接查询（P118）

 * 以指定表中的列作为连接条件同时从多个表中检索数据。

 * FROM 子句中的 ANSI 连接形式

   SELECT 表名.列名,表名.列名,……

   FROM{表名[连接形式]JOIN 表名 ON 搜索条件}

   WHERE [搜索条件]

   连接形式：内链接、无限制链接、外链接。

 * WHER 子句的 SQL Server 连接形式

   SELECT 表名.列名,表名.列名,……

   FROM 表名,表名

   WHERE {表名.列名 连接运算符 表名.列名}

   连接运算符：=， >， <， >=， <=， <>。

例：以用户资料表的用户姓名与用户留言表的留言用户相等

为连接条件，把用户资料表、用户留言表连接在一起。

单值 distinct。（自然连接《数据库应用》P90）

   SELECT distinct

   X.用户姓名,X.购书密码,X.联系电话,Y.留言主题,Y.电子邮件

   FROM 用户资料表 X,用户留言表 Y

   WHERE X.用户姓名=Y.留言用户

例：以姓名为连接条件，

把用户资料表、用户留言表连接生成用户资料留言表。

  SELECT distinct

  X.用户姓名,X.购书密码,X.联系电话,Y.留言主题,Y.电子邮件

  INTO 用户资料留言表

  FROM 用户资料表 X,用户留言表 Y

  WHERE X.用户姓名=Y.留言用户

例：只连接“林木森”的记录。

  WHERE X.用户姓名=Y.留言用户 and X.用户姓名='林木森'

8.3.1 内连接（JOIN）（P119）

  两个表连接生成包含满足连接条件行的第三个表。

例：以用户资料表的用户姓名'林木森'为连接条件，

把用户资料表、用户留言表连接在一起。

   SELECT distinct

   X.用户姓名,X.购书密码,X.联系电话,Y.留言主题,Y.电子邮件

   FROM 用户资料表 X join 用户留言表 Y

   ON X.用户姓名='林木森'

   WHERE X.用户姓名=Y.留言用户

8.3.2 无限制连接（CROSS JOIN）（P120）

  两个表连接生成包含所有行的第三个表。

例：以用户资料表的用户姓名'林木森'为连接条件，

把用户资料表、用户留言表连接在一起。

   SELECT distinct

   X.用户姓名,X.购书密码,X.联系电话,Y.留言主题,Y.电子邮件

   FROM 用户资料表 X cross join 用户留言表 Y

   WHERE X.用户姓名=Y.留言用户

8.3.3 外链接（OUTER JOIN）（P121）

  left OUTER JOIN、right OUTER JOIN、full OUTER JOIN

  两个表连接生成第三个表，

  包含一表满足连接条件的行和另一表全部行的。

例：以用户资料表的用户姓名'林木森'为连接条件，

把用户资料表、用户留言表连接在一起。

   SELECT distinct

   X.用户姓名,X.购书密码,X.联系电话,Y.留言主题,Y.电子邮件

   FROM 用户资料表 X left outer join 用户留言表 Y

   ON X.用户姓名='林木森'

   WHERE X.用户姓名=Y.留言用户

8.3.4 自连接（P122）

  把同一表中的某些行连接起来，便于查询同一表中的相同信息。

同一表指定两次，要用两个别名。（《数据库应用》P95）

例：从用户留言表查询出留言至少两次的留言用户，单值 distinct。

   SELECT distinct X.留言用户,X.留言主题

   FROM 用户留言表 X,用户留言表 Y

   WHERE X.留言用户=Y.留言用户 and X.留言主题<>Y.留言主题

8.4 子查询（P124）

  * 查询中再嵌套子查询。

    SELECT 可以嵌套子查询 SELECT、INSERT、UPDATE、DELETE。

 * 举例：从“图书记录表”中检索出

   大专教材类图书定价高于此类图书平均定价的记录。

   SELECT 图书号，图书定价

   FROM 图书记录表

   WHERE 图书类别='大专教材' AND 图书定价 >

   (SELECT AVG(图书定价) FROM 图书记录表

   WHERE 图书类别='大专教材')

 8.5 案例应用举例