SQLServer编程员指南学习笔记

1、学习好资料欢迎下载第三章数据库基础3.1.4数据库系统数据库系统狭义的讲是由数据库、数据库管理系统和用户构成。3.2关系数据库321关系模型（RM）关系模型把世界看做是由实体和联系构成的。 在关系模型中实体通常是以表的形式来表现的。表的每一行描述实体的一个实例，表的每一 列描述实体的一个特征或属性。所谓联系就是指实体之间的关系，即实体之间的对应关系。联系可以 分为三种：一对一的联系口：一个人只有一种性别，一个人- 性别为一对一的 联系。一对多的联系。如相同性别的人有许多个，性别-人为一对多的联系。 多对一的关系。女口：很多人有同一个性别，人-性别为多对一的联系。（多对多的关系。如：学生与选课，

2、一个学生能选多门课程，一个课 程也能被很多选中）322关系数据库1关系数据库管理系统关系数据库管理系统就是管理关系数据库的计算机软件。外码：如果两个关系中具有一个相同的属性或属性组， 而且这个相同 的属性或属性组在一个关系中是主码， 那么在另一个关系中，就称它 为外码，作为主码的那个关系表称为主表。332索引索引是根据指定的数据库表建立起来的顺序。它提供了快速访问数据的途径，并且可监督表的数据，使其索引所向的列中的数据部重复。3.3.3视图视图看上去同表似乎一模一样，具有一组命名的字段和数据项，但它 其实是一个虚拟的表，在数据库中并不实际存在。视图是由查询数据 库表产生的，它限制了用户能看到和

3、修改的数据。由此可见，视图可 以用来控制用户对数据的访问，并简化数据的显示，即通过视图只显 示那些需要的数据信息。3.3.5缺省值是当在表中创建列或插入数据时，对没有指定其具体值的列或列数据 项赋予事先设定好的值。3.3.6规则是对数据库表中的数据信息的限制。它限定的是表的列。3.3.7触发器是一个用户定义的SQL事物命令的集合。当对一个表进行插入、更 改、删除时，这些命令就会自动执行。3.3.8存储过程是为完成特定的功能而汇集在一起的一组 SQL程序语句。3.4范式满足最低要求的是第一范式，一般，数据库只需满足第三范式就行了。3.4.1 第一范式（1NF）1NF就是指数据库表的每一列都是不可

4、分割的基本数据项。简而言之，就是无重复的列。342第二范式2NF要求数据库表总的每个实例即每一行必须可唯一的区分。要求实 体的属性完全于主关键字。简而言之，就是非主属性非部分依赖于主 关键字。3.4.3第三范式要求一个数据库表中不包含已在其他表中已包含的非主关键字信息。 例如，存在一个部门信息表，其中每个部门有部门编号、部门名称、 部门简介等信息。那么在员工信息表中列出部门编号后就不能将部门 信息表的一些非主属性（部门名称、部门简介）再加入员工信息表中。 简而言之，第三范式就是属性不依赖于其他非主属性。第4章Transact-SQL语言（事物处理查询语句）4.1.2 SQL语言主要由以下几部分

5、组成：数据定义语言（DDL）:主要由一些 Create Alter、Drop语句组成，用来创建，修改，删除表、视图、索引、存储过程、触发器、模式、数值域。数据操纵语言（DML）:主要由Select、Insert、Update Delete语句 组成。数据控制语句（DCL）:主要由 grant、revoke、deny、commit、rollback、 set tra nsacti on 组成。4.3数据类型432浮点数据类型1. REAL数据类型REAL数据类型可精确到第7位小数2. FLOATFLOAT数据类型可精确到第15位小数。3. DECIMALDECIMAL数据类型可以提供小数所需要的

6、实际存储空间，例如： decimal(15,5)表示共有15为数，其中整数10位，小数5位。4. NUMERICNUMERIC数据类型与DECIMAL数据类型完全相同。4.4变量T-SQL中可以使用两种变量，一种是局部变量，另外一种是全局变量。4.4.1局部变量局部变量必须以开头，而且必须先用DECLARE命令说明后才可以 使用。其说明形式如下：DECLARE 变量名变量类型在T-SQL中给变量赋值采用的方法必须是：SELECT 局部变量二变量值或者SET 局部变量二变量值4.4.2全局变量全局变量不是由用户的程序定义的，它们是在服务器级定义的。只能 使用预先说明及定义的全局变量。引用全局变量

7、时，必须以开头。 局部变量的名称不能与全局变量的名称相同，否则会在应用中出错。4.5.1注释符-用于单行注释、/*/用于多行文字注释4.5.2运算符4.位运算符八：按位异或4.5.3通配符表示在某一范围的字符，0-9表示0到9之间的字符。八表示不在某一范围的字符，A0-9表示不再0-9之间的字符。4.6流程控制命令4.6.3 CASECASE命令有两种语句格式：CASE运算式WHENv运算式THEN运算式ELSE 运算式ENDCASEWHEN条件表达式THEN运算式 ELSE 运算式END注意的是，执行CASE子句时，只运行第一个匹配的子句。use panguupdate e_wage=cas

8、ewhe n job_level二the n e_wage*1.08whe n job_level=2the n e_wage*1.07else e_wage*1.05end464 WHILE CONTINUE BREAK其语法如下：WHILEv条件表达式BEGIN命令行或程序块BREAKCONTINUE命令行或程序块END4.6.5 WAITFOR其语法如下：WAITFORDELAY 时间 |TIME 时 间、|ERROREXIT|PROCESSEXIT|MIRROREXITWAITFOR命令用来暂时停止程序执行，直到所设定的等待时间已过或所设定的时间已到才继续往下执行。其中“时间”必须为D

9、ATETIME类型的数据，但不能包括日期。DELAY:用来设定等待的时间，最多可达 24小时。TIME:用来设定等待结束的时间点。ERROREXIT :直至U处理非正常中断。PROCESSEXIT直到处理正常或非正常中断。MIRROREXIT:直到镜像设备失败。等待1小时2分零3秒才执行SELECT语句waitfor delay 1:02:03select *from employee等到晚上11点零8分后才执行select语句waitfor time 3:08:00select *from employee467 RETURN语法如下：RETURN（整数值）4.7.12 SET在使用DECL

10、ARE命令声明之后，所有的变量都被赋予初值NULL。需要用SET命令来给变量赋值。但与 SELECT命令不同的是，SET 命令一次只能给一个变量赋值。不过由于SET命令功能更强，且更严密，因此，SQL SERVER推荐使用SET命令来给变量赋值。4.8.1统计函数函数的对象或自变量必须包括在圆括号内。如果函数需要一个以上的自变量，可用逗号隔开各个自变量。6.STDEV()STDEV()函数返回表达式中所有数据的标准差7.STDEVP()返回总体标准差degrees(把弧度转换为角度返回radia ns()把角度转换为弧度返回EXP()返回表达式的指数值LOG()返回表达式的自然对数值LOG10

11、()返回表达式的以10为底的对数值SQRT()返回表达式的平方根CEILING()返回 =表达式的最小整数FLOOR()返回 =表达式的最小整数ROUND()返回为integer_expression为精度的四舍五入值。SIGN()测试参数的正负号PI()返回值为3.14159RAND()用任选的integer_expression做种子值得出0-1间的随机浮点 数字符串转换函数ASCII()返回字符表达式最左端的 ASCII码值。在ASCII()函数中，纯数字的字符串可不用 舌起来，但含其他字符的 字符串必须用舌起来使用，否则会出错。CHAR()将ASCII码转换为字符LOWER()将字符串

12、全部转换为小写UPPER()将字符串转换为大写LTRIM()把字符串头部的空格去掉RTRIM()把字符串尾部的空格去掉LTRIM和RTRIM可以进行嵌套操作LEFT(,vin teger_expressi on返回的子串是从字 符串最左边起到第integer_expression个字符的部分。RIGHT()返回的子串是从字符串右边第in teger_expression个字符起到最后一个字符串的部分。LEFT和RIGHT也可以进行嵌套操作。SUBSTRING。返回的子串是从字符串左边第 starting_position个字符 起length个字符的部分。CHARINDEX(v substri

13、ng_expression,返回字符串中某个子串出现的开始位置。如果没有发现子串返回0。PATINDEX()函数返回字符串某个指定的子串出现的开始位置，其中子串表达式前后必须有百分号 ，否则返回0。它可以使用通配符。QUOTENAME()返回被特定字符括起来的字符串。REPLACE()返回被替换了指定子串的字符串replace(,)用 string_expression3 替换在 string_expression1 中 的子串 stri ng_expressi on2IDENTITY()只在SELECTINTO语句中使用，用于插入一个identity column列到新表中。6.4系统指令e

14、xec sp_renamedbHorthwindMyDB 注意在更改数据库名的时候， 要将数据库选项的访问权限改为单个用户，并且断开数据库连接。看到P137页压缩数据库，使其为使用的大小占到数据库大小的20%dbcc shi nn kdatabase mydb 20%7.14创建临时表使用create table来创建局部或者全局临时表，与一般表不同的是，它 需要在表名之前增加#，如果是全局临时表，则是在表名前加 #7.2创建和使用约束7.2.1主关键字约束con stra int con strai nt_n ameprimary keyclustered| non clustered(co

15、lu mn_n ame1 )7.7.2外关键字约束con stra int con strai nt_n ameforeig n key(colu mn_n ame1,) refere nces ref_table(ref_colu mn_n ame1厂) 注意临时表不能建立外关键字约束。723惟一性约束con stra int con strai nt_n ameuniq ueclustered |non clustered(colu mn_n amel,)7.2.4检查约束对输入列或整个表中的值设置检验条件，以限制输入值，保证数据库的数据完整性。7.2.5缺省约束注意不能再创建表的时候定义

16、缺省约束，只能向已创建好的表中添加 缺省约束。可以在创建完表后，使用alter table语句进行缺省约束的创建，如下 所示：alter table xtableadd con stra int de_x name default xu yin for xn ame7.2.6列约束和表约束列约束作为列定义的一部分只作用于此列，而表约束作为表定义的一 部分，作用于多个列。以下列子说明：create table xtable(xid int not n ull, xn ame char(8),xage int check(xage0),-列约束con stra int pk_xid primary

17、 key(xid),con stra int ck_xid check(xid5)表约束)732用sp_addtype创建用户自定义的数据类型sp_addtype birthday datetime not null注意当使用的系统数据类型有额外说明时，需要用将其括起来，如 char( 8)删除用户自定义的数据类型sp_droptype birthday842用drop index命令删除索引drop index tablename.indexname，该命令不能删除由create table或 alter table命令创建的primary key或 un ique约束和索引。第八章索引小结

18、索引就是可以加快数据检索的一种结构第九章数据完整性数据完整性分为：数据的实体完整性，域完整性，参照完整性，用户自定义的完整性9.1.1实体完整性规定表的每一行在表中是唯一的实体，表中定义的unique,primary key 以及identity都是实体完整性的体现。9.1.2域完整性指的是数据库表中的列必须满足某种特定的数据类型或是约束。表中定义的check,default,foreign key以及not null都属于域完整性范畴。9.1.3参照完整性指两个表的主关键字和外关键字的数据对应一致。9.1.4用户定义的完整性规则、缺省值、约束和触发器都可以帮助用户实现数据完整性。9.2规则C

19、HECK约束不能直接作用于用户自定义数据类型。9.2.1创建规则1. 用create rule命令创建规则create rule rule_ name as con diti on _expressi on注意conditon_expression子句中的表达式必须以字符 开头例如：创建雇佣日期规则 hire_date_rulecreate rule hire_date_ruleas hire_date=1980-01-01 ndhire_date二getdate()9.2.3规则的绑定与松绑创建规则后，规则仅仅只是一个存在于数据库中的对象， 并未发生作 用。需要将规则与数据库表或用户自定义对

20、象联系起来，才能达到创建规则的目的。1. 用存储过程sp_bindrule绑定规则sp_bi ndrulerule name二ruleobjn ame= object_ name,Nurture only rulename二rule规则的名称objname= object_name 规贝U绑定的对象furtureo nly 仅在绑定规则到用户自定义数据类型时才可以使用。当 指定此选项时使用此用户自定义数据类型的列会应用新规则，而当前已使用此数据类型的列则不受影响。例如：绑定规则hire_date_rule到用户自定义数据类型hire_date上exec sp_b in drule hire_d

21、ate_rule,hire_date,urtureo nlu 绑定规贝S my_rule 至U orders表的 order_idexec sp_b in drule my_rule：orders.order_id10章数据查询11章数据库更新12章存储过程和触发器13章游标、视图和自定义函数14章用户和安全性管理15章备份16章复制17章代理服务 18章DTS数据转换服务19章SQL SERVER的工具9.3缺省值是往用户输入记录时没有指定具体数据的列中自动插入的数据。1. create default defau lt_n ame as con sta nt_expressi on2. s

22、p_bi ndefault绑定缺省值p208第10章数据查询关于select语句中的别名问题：在返回的查询结果中可用别名代替列的原名。可用于 order by,但不能用于 where,group by或 having 子句。 如果数据库的select into/bulk copu选项设置为true/on,则可以用Into 子句创建表和临时表，反之，贝卩只能创建临时表。10.1.6 HAVING 子句HAVING子句指定分组搜索条件。通常与GROUP BY子句一起使用。WHERE子句作用于表和试图，HAVING子句作用于组。10.1.9 COMPUTE 子句在查询结果的末尾生成一个汇总数据行。4

23、.使用通配符-代表一个字节，一个汉字要占用两个字节，所以要使用两个-。10.2.4查询结果分组1. 使用group子句使用group子句时要注意，select的内容要么在group子句中，要么 在select中的聚合函数中。2. with cube|rollup选项使用这两个选项可以格外返回按组统计的数据行。cube生成的结果集 显示了所选列中值的所有组合的聚合。rollup生成的结果集显示了所 选列中值的某一层次结果的聚合。 也就是说，cube将返回尽可能多的 组合值，也更便于你进行一些总结查询。注意：在select语句中where,group by,having子句和统计函数的执行次序如下

24、：where子句从数据源中去掉不符合其搜索条件的数据行； group by子句搜集数据行到各个组中；统计函数为各个组计算统计 值；having子句去掉不符合其组搜索条件的各数据行。在书写sql语句时，需将order by语句写在最后。10.3连接查询10.3.1等值连接查询可以使用where子句指定连接条件也可以在from子句中join连接符指定连接条件from table1 join table2 on table1.xx二table2.xx1034外部连接查询在外部连接中共，参与连接的表有主从之分，以主表的每行数据区匹 配从表的数据列，符合连接条件的数据将直接返回到结果集中，对那些不符合连

25、接条件的列，将被填上null值返回到结果集中（bit类型将返回0）主表在左边称为左外部连接，主表在右边，则称为右外部 连接。10.4嵌套查询在一个select语句的where子句中或having子句中嵌套另一个 select 语句的查询称为嵌套查询，又称子查询。用谓词exists连接子查询使用EXISTS关键字引入一个子查询时，就相当于进行一次数据是否 存在的测试。它的作用是在where子句中测试子查询返回的行是否存 在。exists实际上不产生任何数据，它只返回 true或false值。注意当我们考虑到实际的效率时，我们应该尽量使用exists语句和连接查询而不用In和嵌套查询。10.6存储

26、查询结果10.6.1存储查询结果到表中如要将查询结果存储到一个表而不是临时表，那么在使用select.nto 语句前应确定该表的数据库的“ select into/bulk copu”选项要设置为“ true/on”exec sp_dboption dbnameselect intorue10.6.2存储查询结果到变量中declare firm_ name varchar(50)declare firm_acco unt varchar(30)use panguselect firm _n ame=f_ name,firm_acco un t=acco unt_numfrom firmswhe

27、re firmd= 10010001select firm_ name as firm_ name,firm_acco unt as acco unt_num执行存储过程可以在执行语句之前加关键字exec或execute当在一个批(由GO标志批的结束)内执行多个存储过程时，从第二个存储过程开始，必须使用execute选项。看到245。第11章数据库更新以下举几个例子来进行复习以及理解11.1.2插入单行use panguin sert firms(firmd,f_ name,f_i ntro)values(10070001QL制作数据库软件的公司use panguin sert firms-当

28、表中所有的列均被指定时，可以省略column_listvalues(10070001QL，制作数据库软件的公司：1007000100070-12345678,234325hengdu11.1.3插入子查询结果use panguin sert inro departme nt_in fo(dept_id,e_ num)select dept_id,co un t(*)from employeegroup by dept_id11.1.4用存储过程插入数据use panguin sert into departme nt_in fo(dept_id,d_wage)excute(elect dept

29、_id,sum(e_wage)from employeegroup by dept_id11.2.2用delete删除数据体会：删除订货表前100项纪录中所有产品编号为1003002的产 品use pangudelete ordersfrom(select top 100 *from oders)as orders_top100where orders_top100.p_id=100300211.2.3 trun cate table命 令如果删除表中的所有数据，那么使用truncate table命令比用delete命 令快的多。因为delete会对所删除的数据在十五处理日志中作纪录， 以防

30、止删除失败时可以使用事务处理日志来恢复数据，而trun catetable并不做这方面的工作。它相当于使用不带where子句的delete命令。例如：删除所有的部门信息记录use pangutrun cate table departme nt_info 11.3.2更新记录1. 更新一条记录的值例如：将部门编号为 1002的部门名称改名为财务部use panguupdate departme ntset d_name=财务咅Bwhere dept_id=10022. 更新多个记录的值例如：将所有员工的工资上涨100元use panguupdate employeeset e_wage=e_w

31、age+1003带子查询的更新语句例如：将企划部每位员工的工资上涨 100元use panguupdate employeeset e_wage=e_wage+100where employee.dept_id二(select departme nt.dept_idfrom departme ntwhere d_name=企戈U咅B 11.3.3大量数据更新使用update更新数据时，会将被更新的原数据存放到事务处理日志 中。如果所更新的表特别大，贝y有可能再命令尚未执行完，就将事务 处理日志填满了。这是，sql server会生成错误信息，并将更新过的数 据返回原样。解决此问题有两种方法：一

32、种是加大事务吃力日志的存 储空间，但这似乎不大合算；另一种是分解更新语句的操作过程，并 及时清理事务处理日志。例如，将更新命令分解为两个命令，在其间 插入backup log命令将事务处理日志清除。例如：将订货表中的订购数量全部设为1000use panguupdate ordersset o_qua ntity=1OOOwhere o_date 1999-1-111.4事务11.4.1事务的由来由于delete或update命令对数据库进行更新时一次只能操作一个表， 会带来数据库数据的不一致的问题。例如，要在部门表中删除后勤表， 那么在员工表中部门为后勤部的员工也应相应删除，但是由于一些原因

33、，员工表中的数据并没有及时修改，就会带来数据库中数据处于不一致状态。因此，必须保证这两条 delete语句同时执行。为解决类似的问题，数据库系统通常都引入了事务(transaction)的概念。1142事务的概念事务时一种机制，是一个操作序列，它包含了一组数据库操作命令， 所有的命令作为一个整体一起向系统提交或撤销操作请求，即要么都 执行，要么都不执行。事务是一个不可分割的工作逻辑单元。通常在程序中用begin transaction命令来标识一个事务的开始，用committran sation命令标识事务结束。体会下面的例子。例如：删除后勤部declare tra nsacti on_n a

34、me varchar(32)select tra nsactio n_n ame=my_tra nsactio n_delet6begi n tran sacti on tra nsactio n_n amegouse pangugodelete from departme ntwhere dept_id二012godelete from employeewhere dept_id二012 go commit tran sacti on my_tra nsactio n_deletego11.5 锁11.5.2锁的类别I. 从数据库系统的角度来看锁分为以下三种类型：独占锁：独占锁锁定的资源只允

35、许进行锁定操作的程序使用， 其他任 何对它的操作均不会被接受。但当对象上有其他锁存在时，无法对其 加独占锁。独占锁一直到事务结束才能被释放。共享锁共享锁锁定的资源可以被其他用户读取，但其他用户不能修改它。更新锁是为了防止死锁而设立的。当sql server准备更新数据时，首先对数 据对象作更新锁锁定，这样数据将不能被修改，但可以读取。等到进 行更新数据操作时，会自动将更新锁换为独占锁。但当对象上有其他 锁存在时，无法对其作更新锁锁定。II. 5.3隔离级别在SQL SERVER中，隔离级是指一个事务和其他事务的隔离程度， 即指定了数据库如何保护（锁定）那些当前正在被其他用户或服务器 请求使用的

36、数据。隔离级别需要使用set命令来设定，其语法如下：set tra nsacti on isolati on levelread committed|read un committed|repeatable read|serializablep265第12章存储过程和触发器12.1.2存储过程的优点1存储过程允许标准组件式编程可在创建后，对其多次调用，方便修改也不影响应用程序源代码2. 存储过程能够实现较快的执行速度3. 存储过程能够减少网络流量4. 存储过程可被作为一种安全机制来充分利用注意：存储过程的返回值只是指明执行是否成功，在调用存储过程前一定要有关键字execo当创建存储过程时，需要

37、确定存储过程的三个组成部分：所有的输入参数以及传给调用者的输出参数被执行的针对数据库的操作语句，包括调用其他存储过程的语句。返回给调用者的状态值，以指明调用时成功还是失败。注意体会以下这个例子，在该例子中即存在输入参数也存在输出参数use pubs go if exists(select n ame from sysobjectswhere n ame=titles_sum and type二p)drop procedure titles_sumgouse pubsgocreate procedure title_sum stor_id char(4),sum smallincb utput

38、asselect ord_nu m,ord_date,payterms,title_id,qtyfrom saleswhere stor_id=stor_idselect sum=sum(qty)from sales go-然后调用该存储过程declare totalqua smalli ntexecutetitle_sum 7131,totalquaoutputif totalqua100 select 销售信息二销售等级为 1销售量为+rtrim(cast(totalqua as varchar(20)12.3管理存储过程12.3.1查看存储过程存储过程被创建后，它的名字存储在系统表sys

39、objects中；源代码存放在系统表syscomments中。可以使用sp_helptext存储过程名称查看存储过程的源代码12.5触发器概述12.5.1触发器的概念及作用触发器是一种特殊类型的存储过程。主要是通过事件进行触发而被执 行的，不同于存储过程，存储过程时可以通过存储过程名字而被直接 调用。当对某一表进行诸如 updatensert,delete这些操作时，sql server 就会自动执行触发器所定义的sql语句。触发器的主要作用就是其能够实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性。除此以外，触发器还有其他许多不同的功能：1. 强化约束：实现比check语句更为复杂

40、的约束2. 跟踪变化：可以侦测数据库内的操作，从而不允许数据库中未经许可的指定更新和变化。3. 级联运行4. 存储过程的调用12.5.2触发器的种类 l.after触发器：只有执行某一操作(insert,update,delete之后，才被触发，只能定义在表上。可以针对同一操作定义多个触发。2.insert触发器：表示并不执行其所定义的操作 (insert,update,delete , 而仅是执行触发器本身，既可定义在表上又可定义在视图上。 对同一 操作只能定义一个in stead of触发器。注意：由于truncate table语句没有被记入日志，所以该语句不能触发delete型触发器。12.7触发器的原理每个触发器有两个特殊的表：插入表和删除表。12.7.1插入表的功能对一个定义了插入类型触发器的表来讲，一旦对该表执行了插入操作，那么对向该表插入的所有行来说，都有一个对应的副本存放到插 入表中。即插入表就是用来存储向原表插入的内容。12.7.2删除表的功能对一个定义了删除类型触发器的表来讲，一旦对该表执行了删除操 作，则将所有的删除行存放至删除表中。更新操作时分两步的，即先 将更新的内