SQLServer 编程员指南学习笔记.docx

SQLServer 编程员指南学习笔记.docx

《SQLServer 编程员指南学习笔记.docx》由会员分享，可在线阅读，更多相关《SQLServer 编程员指南学习笔记.docx（25页珍藏版）》请在冰点文库上搜索。

SQLServer编程员指南学习笔记

第三章数据库基础

3.1.4数据库系统

数据库系统狭义的讲是由数据库、数据库管理系统和用户构成。

3.2关系数据库

3.2.1关系模型（RM）

关系模型把世界看做是由实体和联系构成的。

在关系模型中实体通常是以表的形式来表现的。

表的每一行描述实体的一个实例，表的每一列描述实体的一个特征或属性。

所谓联系就是指实体之间的关系，即实体之间的对应关系。

联系可以分为三种：

一对一的联系。

如：

一个人只有一种性别，一个人->性别为一对一的联系。

一对多的联系。

如相同性别的人有许多个，性别->人为一对多的联系。

多对一的关系。

如：

很多人有同一个性别，人->性别为多对一的联系。

（多对多的关系。

如：

学生与选课，一个学生能选多门课程，一个课程也能被很多选中）

3.2.2关系数据库

1.关系数据库管理系统

关系数据库管理系统就是管理关系数据库的计算机软件。

外码：

如果两个关系中具有一个相同的属性或属性组，而且这个相同的属性或属性组在一个关系中是主码，那么在另一个关系中，就称它为外码，作为主码的那个关系表称为主表。

3.3.2索引

索引是根据指定的数据库表建立起来的顺序。

它提供了快速访问数据的途径，并且可监督表的数据，使其索引所向的列中的数据部重复。

3.3.3视图

视图看上去同表似乎一模一样，具有一组命名的字段和数据项，但它其实是一个虚拟的表，在数据库中并不实际存在。

视图是由查询数据库表产生的，它限制了用户能看到和修改的数据。

由此可见，视图可以用来控制用户对数据的访问，并简化数据的显示，即通过视图只显示那些需要的数据信息。

3.3.5缺省值

是当在表中创建列或插入数据时，对没有指定其具体值的列或列数据项赋予事先设定好的值。

3.3.6规则

是对数据库表中的数据信息的限制。

它限定的是表的列。

3.3.7触发器

是一个用户定义的SQL事物命令的集合。

当对一个表进行插入、更改、删除时，这些命令就会自动执行。

3.3.8存储过程

是为完成特定的功能而汇集在一起的一组SQL程序语句。

3.4范式

满足最低要求的是第一范式，一般，数据库只需满足第三范式就行了。

3.4.1第一范式（1NF）

1NF就是指数据库表的每一列都是不可分割的基本数据项。

简而言之，就是无重复的列。

3.4.2第二范式

2NF要求数据库表总的每个实例即每一行必须可唯一的区分。

要求实体的属性完全于主关键字。

简而言之，就是非主属性非部分依赖于主关键字。

3.4.3第三范式

要求一个数据库表中不包含已在其他表中已包含的非主关键字信息。

例如，存在一个部门信息表，其中每个部门有部门编号、部门名称、部门简介等信息。

那么在员工信息表中列出部门编号后就不能将部门信息表的一些非主属性（部门名称、部门简介）再加入员工信息表中。

简而言之，第三范式就是属性不依赖于其他非主属性。

第4章Transact-SQL语言（事物处理查询语句）

4.1.2SQL语言主要由以下几部分组成：

数据定义语言（DDL）：

主要由一些Create、Alter、Drop语句组成，用来创建，修改，删除表、视图、索引、存储过程、触发器、模式、数值域。

数据操纵语言（DML）：

主要由Select、Insert、Update、Delete语句组成。

数据控制语句（DCL）：

主要由grant、revoke、deny、commit、rollback、settransaction组成。

4.3数据类型

4.3.2浮点数据类型

1.REAL数据类型

REAL数据类型可精确到第7位小数

2.FLOAT

FLOAT数据类型可精确到第15位小数。

3.DECIMAL

DECIMAL数据类型可以提供小数所需要的实际存储空间，例如：

decimal（15,5）表示共有15为数，其中整数10位，小数5位。

4.NUMERIC

NUMERIC数据类型与DECIMAL数据类型完全相同。

4.4变量

T-SQL中可以使用两种变量，一种是局部变量，另外一种是全局变量。

4.4.1局部变量

局部变量必须以@开头，而且必须先用DECLARE命令说明后才可以使用。

其说明形式如下：

DECLARE@变量名变量类型

在T-SQL中给变量赋值采用的方法必须是：

SELECT@局部变量=变量值

或者SET@局部变量=变量值

4.4.2全局变量

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

只能使用预先说明及定义的全局变量。

引用全局变量时，必须以@@开头。

局部变量的名称不能与全局变量的名称相同，否则会在应用中出错。

4.5.1注释符

--用于单行注释、

/**/用于多行文字注释

4.5.2运算符

4.位运算符

^：

按位异或

4.5.3通配符

[]表示在某一范围的字符，[0-9]表示0到9之间的字符。

[^]表示不在某一范围的字符，[^0-9]表示不再0-9之间的字符。

4.6流程控制命令

4.6.3CASE

CASE命令有两种语句格式：

CASE<运算式>

WHEN<运算式>THEN<运算式>

[ELSE<运算式>]

END

CASE

WHEN<条件表达式>THEN<运算式>

[ELSE<运算式>]

END

注意的是，执行CASE子句时，只运行第一个匹配的子句。

usepangu

updatee_wage=

case

whenjob_level=’1’thene_wage*1.08

whenjob_level=’2’thene_wage*1.07

elsee_wage*1.05

end

4.6.4WHILE…CONTINUE…BREAK

其语法如下：

WHILE<条件表达式>

BEGIN

<命令行或程序块>

[BREAK]

[CONTINUE]

[命令行或程序块]

END

4.6.5WAITFOR

其语法如下：

WAITFOR{DELAY<’时间’>|TIME<’时间’>|ERROREXIT|PROCESSEXIT|MIRROREXIT}

WAITFOR命令用来暂时停止程序执行，直到所设定的等待时间已过或所设定的时间已到才继续往下执行。

其中“时间”必须为DATETIME类型的数据，但不能包括日期。

DELAY:

用来设定等待的时间，最多可达24小时。

TIME:

用来设定等待结束的时间点。

ERROREXIT：

直到处理非正常中断。

PROCESSEXIT:

直到处理正常或非正常中断。

MIRROREXIT:

直到镜像设备失败。

等待1小时2分零3秒才执行SELECT语句

waitfordelay’01:

02:

03’

select*fromemployee

等到晚上11点零8分后才执行select语句

waitfortime’23:

08:

00’

select*fromemployee

4.6.7RETURN

语法如下：

RETURN（[整数值]）

4.7.12SET

在使用DECLARE命令声明之后，所有的变量都被赋予初值NULL。

需要用SET命令来给变量赋值。

但与SELECT命令不同的是，SET命令一次只能给一个变量赋值。

不过由于SET命令功能更强，且更严密，因此，SQLSERVER推荐使用SET命令来给变量赋值。

4.8.1统计函数

函数的对象或自变量必须包括在圆括号内。

如果函数需要一个以上的自变量，可用逗号隔开各个自变量。

6.STDEV（）

STDEV（）函数返回表达式中所有数据的标准差

7.STDEVP（）

返回总体标准差

degrees（）把弧度转换为角度返回

radians（）把角度转换为弧度返回

EXP（）返回表达式的指数值

LOG（）返回表达式的自然对数值

LOG10（）返回表达式的以10为底的对数值

SQRT（）返回表达式的平方根

CEILING（）返回>=表达式的最小整数

FLOOR（）返回<=表达式的最小整数

ROUND（）返回为integer_expression为精度的四舍五入值。

SIGN（）测试参数的正负号

PI（）返回值为3.14159…

RAND（）用任选的[integer_expression]做种子值得出0-1间的随机浮点数

字符串转换函数

ASCII（）返回字符表达式最左端的ASCII码值。

在ASCII（）函数中，纯数字的字符串可不用’’括起来，但含其他字符的字符串必须用’’括起来使用，否则会出错。

CHAR（）将ASCII码转换为字符

LOWER（）将字符串全部转换为小写

UPPER（）将字符串转换为大写

LTRIM（）把字符串头部的空格去掉

RTRIM（）把字符串尾部的空格去掉

LTRIM和RTRIM可以进行嵌套操作

LEFT（,）返回的子串是从字符串最左边起到第integer_expression个字符的部分。

RIGHT（）返回的子串是从字符串右边第integer_expression个字符起到最后一个字符串的部分。

LEFT和RIGHT也可以进行嵌套操作。

SUBSTRING（）返回的子串是从字符串左边第starting_position个字符起length个字符的部分。

CHARINDEX（<’substring_expression>,）返回字符串中某个子串出现的开始位置。

如果没有发现子串返回0。

PATINDEX（）函数返回字符串某个指定的子串出现的开始位置，其中子串表达式前后必须有百分号%，否则返回0。

它可以使用通配符。

QUOTENAME（）返回被特定字符括起来的字符串。

REPLACE（）返回被替换了指定子串的字符串

replace（,,）用string_expression3替换在string_expression1中的子串string_expression2

IDENTITY（）只在SELECT…INTO语句中使用，用于插入一个identitycolumn列到新表中。

6.4系统指令

execsp_renamedb‘northwind’,’MyDB’。

注意在更改数据库名的时候，要将数据库选项的访问权限改为单个用户，并且断开数据库连接。

看到P137页

压缩数据库，使其为使用的大小占到数据库大小的20%

dbccshinnkdatabasemydb20%

7.14创建临时表

使用createtable来创建局部或者全局临时表，与一般表不同的是，它需要在表名之前增加#，如果是全局临时表，则是在表名前加##

7.2创建和使用约束

7.2.1主关键字约束

constraintconstraint_name

primarykey[clustered|nonclustered]

（column_name1,…）

7.7.2外关键字约束

constraintconstraint_name

foreignkey（column_name1,…）

referencesref_table[（ref_column_name1,…）]

注意临时表不能建立外关键字约束。

7.2.3惟一性约束

constraintconstraint_name

unique[clustered|nonclustered]

（column_name1,…）

7.2.4检查约束

对输入列或整个表中的值设置检验条件，以限制输入值，保证数据库的数据完整性。

7.2.5缺省约束

注意不能再创建表的时候定义缺省约束，只能向已创建好的表中添加缺省约束。

可以在创建完表后，使用altertable语句进行缺省约束的创建，如下所示：

altertablextable

addconstraintde_xnamedefault'xuyin'forxname

7.2.6列约束和表约束

列约束作为列定义的一部分只作用于此列，而表约束作为表定义的一部分，作用于多个列。

以下列子说明：

createtablextable（

xidintnotnull,

xnamechar（8）,

xageintcheck（xage>0）,--列约束

constraintpk_xidprimarykey（xid）,

constraintck_xidcheck（xid>5）—表约束

）

7.3.2用sp_addtype创建用户自定义的数据类型

sp_addtypebirthdaydatetime‘notnull’

注意当使用的系统数据类型有额外说明时，需要用’’将其括起来，如’char（8）’。

删除用户自定义的数据类型

sp_droptypebirthday

8.4.2用dropindex命令删除索引

dropindex‘tablename.indexname’[,…]

该命令不能删除由createtable或altertable命令创建的primarykey或unique约束和索引。

第八章索引小结

索引就是可以加快数据检索的一种结构

第九章数据完整性

数据完整性分为：

数据的实体完整性，域完整性，参照完整性，用户自定义的完整性

9.1.1实体完整性

规定表的每一行在表中是唯一的实体，表中定义的unique,primarykey以及identity都是实体完整性的体现。

9.1.2域完整性

指的是数据库表中的列必须满足某种特定的数据类型或是约束。

表中定义的check,default,foreignkey以及notnull都属于域完整性范畴。

9.1.3参照完整性

指两个表的主关键字和外关键字的数据对应一致。

9.1.4用户定义的完整性

规则、缺省值、约束和触发器都可以帮助用户实现数据完整性。

9.2规则

CHECK约束不能直接作用于用户自定义数据类型。

9.2.1创建规则

1.用createrule命令创建规则

createrulerule_nameascondition_expression

注意conditon_expression子句中的表达式必须以字符@开头

例如：

创建雇佣日期规则hire_date_rule

createrulehire_date_rule

as@hire_date>=’1980-01-01’and@hire_date<=getdate（）

9.2.3规则的绑定与松绑

创建规则后，规则仅仅只是一个存在于数据库中的对象，并未发生作用。

需要将规则与数据库表或用户自定义对象联系起来，才能达到创建规则的目的。

1.用存储过程sp_bindrule绑定规则

sp_bindrule[@rulename]=’rule’

[@objname=]’object_name’

[,’furtureonly’]

[@rulename]=’rule’规则的名称

[@objname=]’object_name’规则绑定的对象

‘furtureonly’仅在绑定规则到用户自定义数据类型时才可以使用。

当指定此选项时使用此用户自定义数据类型的列会应用新规则，而当前已使用此数据类型的列则不受影响。

例如：

绑定规则hire_date_rule到用户自定义数据类型hire_date上

execsp_bindrulehire_date_rule,hire_date,[‘furtureonlu’]

绑定规则my_rule到orders表的order_id

execsp_bindrulemy_rule,’orders.[order_id]’

10章数据查询

11章数据库更新

12章存储过程和触发器

13章游标、视图和自定义函数

14章用户和安全性管理

15章备份

16章复制

17章代理服务

18章DTS数据转换服务

19章SQLSERVER的工具

9.3缺省值

是往用户输入记录时没有指定具体数据的列中自动插入的数据。

1.createdefaultdefault_nameasconstant_expression

2.sp_bindefault绑定缺省值

p208

第10章数据查询

关于select语句中的别名问题：

在返回的查询结果中可用别名代替列的原名。

可用于orderby，但不能用于where,groupby或having子句。

如果数据库的selectinto/bulkcopu选项设置为true/on，则可以用Into子句创建表和临时表，反之，则只能创建临时表。

10.1.6HAVING子句

HAVING子句指定分组搜索条件。

通常与GROUPBY子句一起使用。

WHERE子句作用于表和试图，HAVING子句作用于组。

10.1.9COMPUTE子句

在查询结果的末尾生成一个汇总数据行。

4.使用通配符

-代表一个字节，一个汉字要占用两个字节，所以要使用两个--。

10.2.4查询结果分组

1.使用group子句

使用group子句时要注意，select的内容要么在group子句中，要么在select中的聚合函数中。

2.with{cube|rollup}选项

使用这两个选项可以格外返回按组统计的数据行。

cube生成的结果集显示了所选列中值的所有组合的聚合。

rollup生成的结果集显示了所选列中值的某一层次结果的聚合。

也就是说，cube将返回尽可能多的组合值，也更便于你进行一些总结查询。

注意：

在select语句中where,groupby,having子句和统计函数的执行次序如下：

where子句从数据源中去掉不符合其搜索条件的数据行；groupby子句搜集数据行到各个组中；统计函数为各个组计算统计值；having子句去掉不符合其组搜索条件的各数据行。

在书写sql语句时，需将orderby语句写在最后。

10.3连接查询

10.3.1等值连接查询

可以使用where子句指定连接条件

也可以在from子句中join连接符指定连接条件

fromtable1jointable2ontable1.xx=table2.xx

10.3.4外部连接查询

在外部连接中共，参与连接的表有主从之分，以主表的每行数据区匹配从表的数据列，符合连接条件的数据将直接返回到结果集中，对那些不符合连接条件的列，将被填上null值返回到结果集中（bit类型将返回0）主表在左边称为左外部连接，主表在右边，则称为右外部连接。

10.4嵌套查询

在一个select语句的where子句中或having子句中嵌套另一个select语句的查询称为嵌套查询，又称子查询。

用谓词exists连接子查询

使用EXISTS关键字引入一个子查询时，就相当于进行一次数据是否存在的测试。

它的作用是在where子句中测试子查询返回的行是否存在。

exists实际上不产生任何数据，它只返回true或false值。

注意当我们考虑到实际的效率时，我们应该尽量使用exists语句和连接查询而不用In和嵌套查询。

10.6存储查询结果

10.6.1存储查询结果到表中

如要将查询结果存储到一个表而不是临时表，那么在使用select...into语句前应确定该表的数据库的“selectinto/bulkcopu”选项要设置为“true/on”

execsp_dboption‘dbname’,’selectinto’,true

10.6.2存储查询结果到变量中

declare@firm_namevarchar（50）

declare@firm_accountvarchar（30）

usepangu

select@firm_name=f_name,@firm_account=account_num

fromfirms

wherefirm_id=’10010001’

select@firm_nameasfirm_name,@firm_accountasaccount_num

执行存储过程可以在执行语句之前加关键字exec或execute。

当在一个批（由GO标志批的结束）内执行多个存储过程时，从第二个存储过程开始，必须使用execute选项。

看到245。

第11章数据库更新

以下举几个例子来进行复习以及理解

11.1.2插入单行

usepangu

insertfirms

（firm_id,f_name,f_intro）

values（10070001,’SQL’,’制作数据库软件的公司’）

usepangu

insertfirms

--当表中所有的列均被指定时，可以省略column_list

values（10070001,’SQL’，’制作数据库软件的公司’,100700010007,’00-12345678’,234325,’chengdu’）

11.1.3插入子查询结果

usepangu

insertinrodepartment_info（dept_id,e_num）

selectdept_id,count（*）

fromemployee

groupbydept_id

11.1.4用存储过程插入数据

usepangu

insertintodepartment_info（dept_id,d_wage）

excute（‘selectdept_id,sum（e_wage）

fromemployee

groupbydept_id’）

11.2.2用delete删除数据

体会：

删除订货表前100项纪录中所有产品编号为‘1003002’的产品

usepangu

deleteorders

from（selecttop100*fromoders）asorders_top100

whereorders_top100.p_id=’1003002’

11.2.3truncatetable命令

如果删除表中的所有数据，那么使用truncatetable命令比用delete命令快的多。

因为delete会对所删除的数据在十五处理日志中作纪录，以防止删除失败时可以使用事务处理日志来恢复数据，而truncatetable并不做这方面的工作。

它相当于使用不带where子句的delete命令。

例如：

删除所有的部门信息记录

usepangu

truncatetabledepartment_info

11.3.2更新记录

1.更新一条记录的值

例如：

将部门编号为‘1002’的部门名称改名为‘财务部’

usepangu

updatedepartment

setd_name=’财务部’

wheredept_id=’1002’

2.更新多个记录的值

例如：

将所有员工的工资上涨100元

usepangu

updateemployee

sete_wage=e_wage+100

3.带子查询的更新语句

例如：

将企划部每位员工的工资上涨100元

usepangu

updateemployee

sete_wage=e_wage+100

whereemployee.dept_id=

（selectdepart