SQLServer学习笔记11.docx

SQLServer学习笔记11.docx

《SQLServer学习笔记11.docx》由会员分享，可在线阅读，更多相关《SQLServer学习笔记11.docx（129页珍藏版）》请在冰豆网上搜索。

SQLServer学习笔记11

SQLServer2005学习笔记【经典】

第1章SQLServer2005关系数据库简介

1.1关系数据库

最常用的数据模型有层次模型（HierarchicalModel）、网状模型（NetworkModel）和关系模型（RelationalModel）。

•关系模型就是用二维表格结构来表示实体及实体之间联系的模型。

1.1.1关系模型的基本概念

关系（Relation）：

一个关系对应一张二维表，每个关系有一个关系名。

在SQLServer中，一个关系就是一个表文件。

元组（Tuple）：

二维表中水平方向的一行，有时也叫做一条记录。

属性（Attribute）：

表格中的一列，相当于记录中的一个字段。

关键字（Key）：

可唯一标识元组的属性或属性集，也称为关系键或主码。

域（Domain）：

属性的取值范围，如性别的域是（男，女）。

分量：

每一行对应的列的属性值，即元组中的一个属性值。

关系模式：

对关系的描述，一般表示为：

关系名（属性1，属性2，……属性n）。

1.1.2关系模型的性质

（1）关系中不允许出现相同的元组。

因为数学上集合中没有相同的元素，而关系是元组的集合，所以作为集合元素的元组应该是唯一的。

（2）关系中元组的顺序（即行序）是无关紧要的，在一个关系中可以任意交换两行的次序。

因为集合中的元素是无序的，所以作为集合元素的元组也是无序的。

根据关系的这个性质，可以改变元组的顺序使其具有某种排序，然后按照顺序查询数据，可以提高查询速度。

（3）关系中属性的顺序是无关紧要的，即列的顺序可以任意交换。

交换时，应连同属性名一起交换，否则将得到不同的关系。

（4）同一属性名下的各个属性值必须来自同一个域，是同一类型的数据。

（5）关系中各个属性必须有不同的名字，不同的属性可来自同一个域，即它们的分量可以取自同一个域。

（6）关系中每一分量必须是不可分的数据项，或者说所有属性值都是原子的，是一个确定的值，而不是值的集合。

1.1.3关系数据库完整性

1、实体完整性（EntityIntegrity）

•实体完整性是指主关系键的值不能为空或部分为空。

2、参照完整性（Referentialintegrity）

•如果关系R2的外部关系键X与关系R1的主关系键相符,则X的每个值或者等于R1中主关系键的某一个值，或者取空值。

3、域完整性

•域完整性是针对某一具体关系数据库的约束条件。

它反映某一具体应用所涉及的数据必须满足的语义要求。

1.1.4关系数据库的规范化

•第一范式（1NF）：

元组中每一个分量都必须是不可分割的数据项

•第二范式（2NF）：

不仅满足第一范式，而且所有非主属性完全依赖于其主码

•第三范式（3NF）：

不仅满足第二范式，而且它的任何一个非主属性都不传递于任何主关键字

第3章Transact-SQL语言

3.1数据定义语言

数据定义语言（DDL）是指用来定义和管理数据库以及数据库中各种对象的语句，这些语句包括CREATE、ALTER和DROP等。

在SQLServer2005中，数据库对象包括表、视图、触发器、存储过程、规则、默认、用户自定义的数据类型等。

这些对象的创建、修改和删除等都可以通过使用CREATE，ALTER，DROP等语句来完成。

例3-1创建数据库表

/*下面的例子将创建表S。

*/

CREATETABLES

（

snochar（10）NOTNULL/*学号字段*/

CONSTRAINTPK_snoPRIMARYKEYCLUSTERED/*主键约束*/

CHECK（snolike'31300501[0-9][0-9]'）/*检查约束*/,

snamechar（8）NULL,/*姓名字段*/

sexchar

（2）NULL,/*性别字段*/

ageintNULL,/*年龄字段*/

deptvarchar（20）NULL/*系别字段*/

）

例3-2修改S表，增加一个班号列

程序清单如下：

ALTERTABLES

ADD

CLASS_NOCHAR（6）

例3-3　删除S表

程序清单如下：

•DROPtableS

3.2数据操纵语言（DML）

数据操纵语言是指用来查询、添加、修改和删除数据库中数据的语句，这些语句包括SELECT、INSERT、UPDATE、DELETE等。

将在第6章详细讲解SELECT语句的语法及实例。

3.2.2　INSERT语句

INSERT语句用于向数据库表或者视图中加入一行数据。

INSERT语句的语法形式如下：

INSERT[INTO]table_or_view[（column_list）]VALUES（data_values）

其中，table_or_view是指要插入新记录的表或视图；column_list是可选项，指定待添加数据的列；VALUES子句指定待添加数据的具体值。

列名的排列顺序不一定要和表定义时的顺序一致。

但当指定列名表时VALUES子句值的排列顺序必须和列名表中的列名排列顺序一致，个数相等，数据类型一一对应。

在进行数据插入操作时须注意以下几点：

（1）必须用逗号将各个数据分开，字符型数据要用单引号括起来。

（2）INTO子句中没有指定列名，则新插入的记录必须在每个属性列上均有值，且VALUES子句中值的排列顺序要和表中各属性列的排列顺序一致。

（3）将VALUES子句中的值按照INTO子句中指定列名的顺序插入到表中。

（4）对于INTO子句中没有出现的列，则新插入的记录在这些列上将取空值，如上例的SCORE即赋空值。

但在表定义时有NOTNULL约束的属性列不能取空值。

例3-5创建SC表（学生选课表），并向SC表中插入一条选课记录（’S7’,’C1’）。

程序清单如下：

CREATETABLESC

（

snochar（10）NOTNULL,

cnochar

（2）NULL,/*课程编号字段*/

scorenumerical（4,1）NULL/*成绩字段*/

）

Go

INSERTINTOSC（sno,cno）VALUES（'3130050101','c1'）

Go

下面是插入与列顺序不同的数据的例子。

例3-6使用column_list及VALUES列表显式地指定将被插入每个列的值。

程序清单如下：

CREATETABLET1

（column_1int,

column_2varchar（30））

Go

INSERTT1（column_2,column_1）VALUES（'Thisisatest',1）

插入多行数据的语法格式为：

INSERTINTOtable_or_view[（column_list）]子查询

例3-7求出各位学生的平均成绩，把结果存放在新表AVGSCORE中。

程序清单如下：

/*首先建立新表AVGSCORE，用来存放学号和学生的平均成绩。

*/

CREATETABLEAVGSCORE

（SNOCHAR（10）,

AVGSCORESMALLINT）

Go

/*利用子查询求出SC表中各位学生的平均成绩，把结果存放在新表AVGSCORE中。

*/

INSERTINTOAVGSCORE

SELECTSNO,AVG（SCORE）

FROMSC

GROUPBYSNO

3.2.3　UPDATE语句

UPDATE语句用于修改数据库表或视图中特定记录或者字段的数据，其语法形式如下：

UPDATEtable_or_viewSET=[,=>]…

[WHERE]

其中：

table_or_view是指要修改的表或视图；SET子句给出要修改的列及其修改后的值，其中column为要修改的列名，expression为其修改后的值；WHERE子句指定待修改的记录应当满足的条件，WHERE子句省略时，则修改表中的所有记录。

例3-8　一个带有WHERE条件的修改语句。

程序清单如下：

useadventureworks

go

updateperson.address

setcity='Boston'whereaddressid=1

例3-9将所有学生年龄增加1岁

程序清单如下：

UPDATESSETAGE=AGE+1

例3-12创建把讲授C5课程的教师的工资增加100元。

程序清单如下：

/*T表（教师基本情况表）的结构为T（TNO,TN,SEX,AGE,PROF,SAL,DEPT）分别表示教师的编号，姓名，性别，年龄，职称，工资，系别。

TC表（教师授课表）的结构为TC（TNO,CNO）分别表示教师的编号，课程编号。

*/

UPDATETSETSAL=SAL+100

WHERETNOIN

（SELECTT.TNOFROMT,TC

WHERET.TNO=TC.TNOANDTC.CNO='C5'）

/*通过连接查询找到讲授C5课程的教师编号。

*/

3.2.4　DELETE语句

使用DELETE语句可以删除表中的一行或多行记录，其语法格式为：

DELETEFROMtable_or_view[WHERE]

其中：

table_or_view是指要删除数据的表或视图；WHERE子句指定待删除的记录应当满足的条件，WHERE子句省略时，则删除表中的所有记录。

下面是删除一行记录的例子。

例3-14删除张益琳教师的记录。

程序清单如下：

DELETEFROMTWHERETN=’张益琳’

下面是删除多行记录的例子。

例3-15删除所有教师的授课记录。

程序清单如下：

DELETEFROMTC

执行此语句后，TC表即为一个空表，但其定义仍存在数据字典中。

例3-16删除李明同学选课的记录。

DELETEFROMSCWHERESNO=

（SELECTSNOFROMSWHERESN=’李明’）

3.3数据控制语言

数据控制语言（DCL）是用来设置或更改数据库用户或角色权限的语句，包括GRANT，DENY，REVOKE等语句。

在默认状态下，只有sysadmin，dbcreator，db_owner或db_securityadmin等人员才有权力执行数据控制语言。

3.3.1　GRANT语句

1、语句权限与角色的授予

•SQL语言使用GRANT语句为用户授予语句权限的语法格式为：

GRANT<语句权限>|<角色>[,<语句权限>|<角色>]…

TO<用户名>|<角色>|PUBLIC[,<用户名>|<角色>]…

[WITHADMINOPTION]

•其语义为：

将指定的语句权限授予指定的用户或角色。

其中：

（1）PULBIC代表数据库中的全部用户；

（2）WITHADMINOPTION为可选项，指定后则允许被授权的用户将指定的系统特权或角色再授予其他用户或角色。

1、语句权限与角色的授予

•例3-17　给用户Mary和John以及WindowsNT组Corporate\BobJ授予多个语句权限。

程序清单如下：

GRANTCREATEDATABASE,CREATETABLE

TOMary,John,[Corporate\BobJ]

•例3-18为用户ZhangYiLin授予CREATETABLE的语句权限。

程序清单如下：

GRANTCREATETABLE

TOZhangYiLin

2、对象权限与角色的授予

数据库管理员拥有系统权限，而作为数据库的普通用户，只对自己创建的基本表、视图等数据库对象拥有对象权限。

如果要共享其他的数据库对象，则必须授予他一定的对象权限。

同语句权限的授予类似，SQL语言使用GRANT语句为用户授予对象权限，其语法格式为：

GRANTALL|<对象权限>[（列名[,列名]…）][,<对象权限>]…ON<对象名>

TO<用户名>|<角色>|PUBLIC[,<用户名>|<角色>]…

[WITHADMINOPTION]

其语义为：

将指定的操作对象的对象权限授予指定的用户或角色。

其中：

（1）ALL代表所有的对象权限。

（2）列名用于指定要授权的数据库对象的一列或多列。

如果不指定列名，被授权的用户将在数据库对象的所有列上均拥有指定的特权。

实际上，只有当授予INSERT、UPDATE权限时才需指定列名。

（3）ON子句用于指定要授予对象权限的数据库对象名，可以是基本表名、视图名等。

（4）WITHADMINOPTION为可选项，指定后则允许被授权的用户将权限再授予其他用户或角色。

例3-19　在权限层次中授予对象权限。

首先，给所有用户授予SELECT权限，然后，将特定的权限授予用户Mary，John和Tom。

程序清单如下：

GRANTSELECT

ONs

TOpublic

GO

GRANTINSERT,UPDATE,DELETE

ONs

TOMary,John,Tom

GO

例3-20将查询T表和修改教师职称的权限授予USER3，并允许将此权限授予其他用户。

程序清单如下：

GRANTSELECT,UPDATE（PROF）

ONTTOUSER3

WITHADMINOPTION

上例中，USER3具有此对象权限，并可使用GRANT命令给其他用户授权，如下例，USER3将此权限授予USER4：

GRANTSELECT,UPDATE（PROF）

ONT

TOUSER4

3.3.2　REVOKE语句

REVOKE语句是与GRANT语句相反的语句，它能够将以前在当前数据库内的用户或者角色上授予或拒绝的权限删除，但是该语句并不影响用户或者角色从其他角色中作为成员继承过来的权限

1、语句权限与角色的收回

数据库管理员可以使用REVOKE语句收回语句权限，其语法格式为：

REVOKE<语句权限>|<角色>[,<语句权限>|<角色>]…

FROM<用户名>|<角色>|PUBLIC[,<用户名>|<角色>]…

•例：

收回用户ZHANGYILIN所拥有的CREATETABLE的语句权限。

REVOKECREATETABLE

FROMZHANGYILIN

2、对象权限与角色的收回

所有授予出去的权力在必要时都可以由数据库管理员和授权者收回，收回对象权限仍然使用REVOKE语句，其语法格式为：

REVOKE<对象权限>|<角色>[,<对象权限>|<角色>]…

FROM<用户名>|<角色>|PUBLIC[,<用户名>|<角色>]…

例3-21收回用户USER1对C表的查询权限。

程序清单如下：

REVOKESELECT

ONC

FROMUSER1

例3-22收回用户USER3查询T表和修改教师职称的权限。

程序清单如下：

REVOKESELECT,UPDATE（PROF）

ONT

FROMUSER3

在上例中，USER3将对T表的权限授予了USER4，在收回USER3对T表的权限的同时，系统会自动收回USER4对T表的权限。

例3-23　首先从public角色中收回SELECT权限，然后，收回用户Mary，John和Tom的特定权限。

程序清单如下：

USEpubs

GO

REVOKESELECTONsFROMpublic

GO

REVOKEINSERT,UPDATE,DELETE

ONs

FROMMary,John,Tom

3.3.3　DENY语句

DENY语句用于拒绝给当前数据库内的用户或者角色授予权限，并防止用户或角色通过其组或角色成员继承权限。

否定语句权限的语法形式为：

DENYALL|<语句权限>|<角色>[,<语句权限>|<角色>]…

TO<用户名>|<角色>|PUBLIC[,<用户名>|<角色>]…

否定对象权限的语法形式为：

DENYALL|<对象权限>[（列名[,列名]…）][,<对象权限>]…ON<对象名>

TO<用户名>|<角色>|PUBLIC[,<用户名>|<角色>]…

例3-24　首先给public角色授予SELECT权限，然后，拒绝用户Mary，John和Tom的特定权限。

程序清单如下：

USEpubs

GO

GRANTSELECT

ONs

TOpublic

GO

DENYSELECT,INSERT,UPDATE,DELETE

ONs

TOMary,John,Tom

3.4系统存储过程

系统存储过程是SQLServer系统创建的存储过程，它的目的在于能够方便地从系统表中查询信息，或者完成与更新数据库表相关的管理任务或其他的系统管理任务。

系统存储过程可以在任意一个数据库中执行。

系统存储过程创建并存放于系统数据库master中，并且名称以sp_或者xp_开头。

一些系统过程只能由系统管理员使用，而有些系统过程通过授权可以被其他用户使用。

系统存储过程的部分示例如下：

•sp_addtype：

用于定义一个用户定义数据类型；

•sp_configure：

用于管理服务器配置选项设置；

•xp_sendmail：

用于发送电子邮件或寻呼信息；

•sp_stored_procedures：

用于返回当前数据库中的存储过程的清单；

•sp_help：

用于显示参数清单和其数据类型；

•sp_depends：

用于显示存储过程依据的对象或者依据存储过程的对象；

•sp_helptext：

用于显示存储过程的定义文本；

•sp_rename：

用于修改当前数据库中用户对象的名称。

3.5.2变量

变量是一种语言中必不可少的组成部分。

Transact-SQL语言中有两种形式的变量，一种是用户自己定义的局部变量，另外一种是系统提供的全局变量。

1.局部变量

局部变量是一个能够拥有特定数据类型的对象，它的作用范围仅限制在程序内部。

局部变量被引用时要在其名称前加上标志“＠”，而且必须先用DECLARE命令定义后才可以使用。

定义局部变量的语法形式如下：

DECLAER{@local_variabledata_type}[…n]

其中，参数@local_variable用于指定局部变量的名称，变量名必须以符号@开头，并且局部变量名必须符合SQLServer的命名规则。

参数data_type用于设置局部变量的数据类型及其大小。

data_type可以是任何由系统提供的或用户定义的数据类型。

但是，局部变量不能是text，ntext或image数据类型。

使用DECLARE命令声明并创建局部变量之后，会将其初始值设为NULL，如果想要设定局部变量的值，必须使用SELECT命令或者SET命令。

其语法形式为：

SET{{@local_variable=expression}或者SELECT{@local_variable=expression}[,...n]

其中，参数@local_variable是给其赋值并声明的局部变量，参数expression是任何有效的SQLServer表达式。

1.局部变量

例3-26　创建一个@myvar变量，然后将一个字符串值放在变量中，最后输出@myvar变量的值。

程序清单如下：

DECLARE@myvarchar（20）

select@myvar='Thisisatest'

SELECT@myvar

GO

例3-27　通过查询给变量赋值。

程序清单如下：

USEadventureworks

GO

DECLARE@rowsint

SET@rows=（SELECTCOUNT（*）FROMhumanresources.employee）

2.全局变量

除了局部变量之外，SQLServer系统本身还提供了一些全局变量。

全局变量是SQLServer系统内部使用的变量，其作用范围并不仅仅局限于某一程序，而是任何程序均可以随时调用。

全局变量通常存储一些SQLServer的配置设定值和统计数据。

用户可以在程序中用全局变量来测试系统的设定值或者是Transact-SQL命令执行后的状态值。

在使用全局变量时应该注意以下几点：

（1）全局变量不是由用户的程序定义的，它们是在服务器级定义的。

（2）用户只能使用预先定义的全局变量。

（3）引用全局变量时，必须以标记符“@@”开头。

（4）局部变量的名称不能与全局变量的名称相同，否则会在应用程序中出现不可预测的结果。

例3-29　显示到当前日期和时间为止试图登录SQLServer的次数。

程序清单如下：

SELECTGETDATE（）AS'当前的时期和时间',

@@CONNECTIONSAS'试图登录的次数'

3.5.3运算符

运算符是一些符号，它们能够用来执行算术运算、字符串连接、赋值以及在字段、常量和变量之间进行比较。

在SQLServer2005中，运算符主要有以下六大类：

算术运算符、赋值运算符、位运算符、比较运算符、逻辑运算符和字符串串联运算符。

1．算术运算符

算术运算符可以在两个表达式上执行数学运算，这两个表达式可以是数字数据类型分类的任何数据类型。

算术运算符包括加（+）、减（－）、乘（*）、除（/）和取模（%）。

2．赋值运算符

Transact-SQL中只有一个赋值运算符，即（=）。

赋值运算符使我们能够将数据值指派给特定的对象。

另外，还可以使用赋值运算符在列标题和为列定义值的表达式之间建立关系。

3．位运算符

位运算符使我们能够在整型数据或者二进制数据（image数据类型除外）之间执行位操作。

此外，在位运算符左右两侧的操作数不能同时是二进制数据。

表3-1列出了所有的位运算符及其含义。

运算符

含义

&（按位AND）

按位AND（两个操作数）

|（按位OR）

按位OR（两个操作数）

^（按位互斥OR）

按位互斥OR（两个操作数）

4．比较运算符

比较运算符亦称为关系运算符，用于比较两个表达式的大小或是否相同，其比较的结果是布尔值，即TRUE（表示表达式的结果为真）、FALSE（表示表达式的结果为假）以及UNKNOWN。

除了text，ntext或image数据类型的表达式外，比较运算符可以用于所有的表达式。

5．逻辑运算符

逻辑运算符可以把多个逻辑表达式连接起来。

逻辑运算符包括AND、OR和NOT等运算符。

逻辑运算符和比较运算符一样，返回带有TRUE或FALSE值的布尔数据类型。

三个运算符的优先级别为：

NOT，AND，OR。

6．字符串串联运算符

字符串串联运算符允许通过加号（+）进行字符串串联