1 1.SQL Server数据库基础  2   3 1-1:使用数据库的必要性  4     a.可以结构化存储大量的数据信息，方便用户进行有效的检索和访问。  5     b.可以有效地保持数据信息的一致性、完整性，降低数据冗余。  6     c.可以满足应用的共享和安全方面的要求。  7     d.数据库技术能够方便智能化地分析，产生新的有用信息。  8 1-2:DBMS(数据库管理系统)的发展史  9     a.萌芽阶段--文件系统 10     b.初级阶段--第一代数据库 11     c.中级阶段--第二代数据库 12     d.高级阶段--新一代数据库 13 1-3:当今数据库简介 14     a.SQL Server简介 15         (1)企业版（SQL Server 2005 Enterprise Edition） 16         (2)标准版（SQL Server 2005 Standard Edition） 17         (3)工作组版（SQL Server 2005 Workgroup Edition） 18         (4)开发版（SQL Server Developer Edition） 19         (5)评估版（SQL Server Evaluation Edition） 20      b.Oracle简介 21         Oracle是一种大型的数据库，用于大型的网站开发使用，它的配置、管理、系统维护成为了一种专门的技术，涉及到比较多的、系统的专业知识。 22     c.DB2简介 23         DB2数据库核心又称为DB2通用服务器，不但可以运行于多种操作系统之上，而且它根据相应的平台环境做了调整和优化，以便能够达到更好的性能。其它数据库如：SyBase、MySQL等等，在不同的行业、不同的应用场合也有一定的应用。 24 1-4:数据库的基本概念 25     a.实体和记录（实体是所有客观存在的，不同的数据体现了不同的实体。数据库当中的每一行又叫做一条记录） 26     b.数据库和数据库表（不同的记录组织在一起，形成了数据库的"表"。表是实体的集合，是用来存储具体的数据的。关系，通过建、类型、规则、权限、约束、触发器等抽象概念来表达。通常说"把数据存到数据库当中"，其实就是存到"数据库的表当中"） 27     c.数据库系统和数据库管理系统（数据库管理系统【DBMS】是一种系统软件，由一个互相关联的数据集合和一组访问数据的程序构成。这个数据集合通常称为数据库。 数据库系统【DBS】是一个实际可运行的软件系统，可以对系统提供的数据进行存储维护和应用，它是有存储介质、处理对象和管理系统共同组成的集合体。它通常由软件、数据库和数据库管理员组成。） 28     d.数据冗余（Redundance）【数据的重复性】和数据完整性（Integrality）【数据的准确性】【有些时候还是可以有少量数据的冗余现象】 29 1-5:SQL Server 2005管理器 30     SQL Server数据库按用途可分为：系统数据库和用户数据库 31     Microsoft SQL Server提供以下数据库： 32         (1)Master数据库(1.所有的登录账户和系统配置设置2.所有其他的数据库及数据库文件的位置3.SQL Server的初始化信息) 33         (2)Tempdb数据库(在SQL Server每次启动时重新创建) 34         (3)Model数据库(Model数据库用作在系统上创建所有数据库的模板) 35         (4)Msdb数据库(Msdb数据库提供SQL Server代理程序调度警报、作业以及记录操作时使用) 36     a.新建一个数据库连接（Window省份验证和SQL Server身份验证） 37     b.新建数据库登录名 38 1-6:创建和管理SQL Server数据库 39     a.创建数据库 40         (1)数据库文件(Database File) 41         (2)事务日志文件(Transaction Log File) 42         (3)文件组(File Group)【主文件组（Primary File Group）和此文件组（Secondary File Group）】 43     b.数据库管理和维护 44         (1)分离和附加数据库（数据库启动的时候，数据库文件时不可以粘贴和复制的） 45         (2)设置数据库状态（属于脱机状态的数据库，文件是可以复制和粘贴的，点击联机即可联机） 46         (3)收缩数据库 47         (4)删除数据库（谨慎操作） 48 2.SQL Server数据库表管理 49  50 2-1:表的几个基本概念 51     a.数据完整性（可靠性、准确性） 52         (1)实体完整性约束【行】（索引、唯一约束、主键约束或标识列属性） 53         (2)域完整性约束【列】（数据类型、检查约束、输入格式、外键约束、默认值、非空约束） 54         (3)引用完整性约束（在输入或删除数据行时，引用完整性约束用来保持表之间已定义的关系 如:主表和子表） 55         (4)自定义完整性约束（用户自定义完整性用来定义特定的规则。如果不满足要求则不能插入，这个时候就需要使用数据库的规则、存储过程或者触发器对象来进行约束） 56      b.主键(Primary Key)和外键(Foreign Key) 57         (1)主键 58             表中有一列或几列组合的值能用来唯一地标识表中的每一行，这样的一列或者多列组合叫做表的主键。一个表只能有一个主键，主键约束确保了表中的行是唯一的；尽管表中可以没有主键，但是通常情况下应当为表设置一列为主键。如果两列或多列组合起来唯一地标识表中的每一行，则该主键也叫做"组合键";在选择哪列为主键时应该考虑连个原则：最少性和稳定性。 59         (2)外键 60             "外键"就是用来达到这个目的的，它是对应主键而言的，就是"子表"中对应于"主表"的列，在子表中称为外键或者引用键，它的值要求与主表的主键或者唯一键相对应，外键用来强制引用完整性。一个表可以有多个外键。 61 2-2:建立数据库表 62      63     a.在SQL Server Management Studio中建立数据库表 64     b.确定列的数据类型 65         (1)二进制数据类型 66             binary varbinary image 67         (2)文本数据类型 68             char varchar nchar nvarchar text ntext 69         (3)日期和时间 70             datetime 71         (4)数字数据 72             int smallint tinyint bigint (float real) 73         (5)货币数据类型 74             Money 75         (6)Bit数据类型 76             Bit 77     c.是否允许为空值（如果该列允许为空，则在输入数据行的时候，这一项可以不输入） 78     d.建立主键 79     e.默认值 80     f.标识列(数据属于数字类型int，标识种子，标识增量，不重复) 81 2-3:建立表间关系 82 2-4:建立检查约束 83 2-5:导入和导出数据 84 2-6:删除表（谨慎小心） 85  86 3.SQL Server数据管理 87  88 3-1:SQL简介 89     a.SQL和T-SQL（T-SQL【Transact-SQL是标准的SQL强化版】） 90     b.T-SQL的组成 91         (1)DML(数据操作语言):用来查询、插入、删除、修改数据库中的数据 92         (2)DCL(数据控制语言):用来控制数据库组件的存取许可、存取权限等等 93         (3)DDL(数据定义语言):用来建立数据库、数据库对象和定义其列大部分是以Create开头的命令，如：CreateTable、CreateView及Drop Table等等 94 3-2:T-SQL中的条件表达式和逻辑运算符 95     a.条件表达式 96         (1)常量:表示单个指定数据值的符合(如：字符，数字、字) 97         (2)列名（表当中列的名称，表达式中仅允许使用列的名称） 98         (3){一元运算符}:仅有一个操作数的运算符 99         (4){二元运算符}:将两个操作数组合执行操作的运算符100             = > < >= <= <>不等于 !非101         (5)通配符:通配符经常与like关键字一起配合使用102             ‘_‘一个字符  如：A Like ‘C_‘103             %  任意长度的字符串 如：B Like ‘CO_%‘104             [] 括号中所指定范围内的一个字符 如：C like ‘9W0[1-2]‘105             [^] 不在括号中所指定范围内的任意一个字符 如：106 D LIKE like ‘9WO[^1-2]‘107             如：TelCode LIKE ‘13[5-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]‘108     b.逻辑表达式(IN 关键字用来限制范围)109         T-SQL支持的逻辑运算符有And、Or和Not110         如：Not (付款方式=‘信用卡‘) OR (信用卡 in (‘牡丹卡‘,‘金汇卡‘,‘龙卡‘,‘阳光卡‘))111 112 3-3:使用T-SQL插入数据113     a.语法：insert [into] <表名> [列名] values <值列表>114     b.一次性插入多行数据115         (1)通过Insert Select语句将现有表中的数据添加到新表中116             inset into TongXunLu (姓名，地址，电子邮件)117             select SName,SAddress,SEmail118             from Students119         (2)通过Select Into语句将现有数据添加到新表中120         select Students.SName,Students.SAddress,Students.SEmail121         into TongXunLu122         from Students123         语法：124         select identity(数据类型,标识种子,表示增量) as 列名125         into 新表126         from 原始表127         修改为：128         select Students.SName,Students.SAddress,Students.SEmail,identity(int,1,1)129         as StudentID130         into TongXunLu131         from Students132         (3)通过Union关键字合并数据进行插入133         insert Students (SName,SGrade,SSex)134         select ‘王军‘,7,1, union135         select ‘李四‘,3,6, union136         select ‘张三‘,4,1, union137         select ‘小红‘,7,2138 139 3-4:使用T-SQL更新数据140 141     语法：update <表名> set <列名=更新值> [where <更新条件>]142     如：143     update Students144     set SAddress="四川成都"145     where SAddress="上海"146 147 3-5:使用T-SQL删除数据148     a.使用Delete删除数据149         语法：delete from <表名> [where <删除条件>]150         如：151         delete from Students152         where SName=‘张青裁‘153         错误做法：154         如：155         delete SAddress from Students156 157     b.使用Truncate Table删除数据(清空重置【顺序】)158         TRUNCATE TABLE Students159      Truncate Table的执行速度比Delete快而且占用的资源更少160 161 4.数据查询（一）162 163 4-1:T-SQL查询基础164     a.查询和记录集165     b.使用Select语句进行查询166     语法：167     select <列名>168     from <表名>169     [where <查询条件表达式>]170     [order by <排序的列名> [ASC或DESC]]171         (1)查询所有的数据行和列172             select *from Students173         (2)查询部分行列---条件查询174             Select SCode,SName,SAddress175             from Students176             where SAddress=‘河南新乡‘177         (3)在查询中使用列名178             select SCode as 学员编号,SName as 学员姓名179             from Students180             where SAddress <> ‘河南新乡‘181             (兼容性，字符串结果为字符串，数值型结果为数值型)182             select FirstName+‘.‘+LastName as ‘姓名‘183             from Employee184 185             select ‘姓名‘=FirstName+‘.‘LastName186             from Employee187         (4)查询空行188             select SName from Students where SEmail IS NULL189         (5)在查询中使用常量列190             select 姓名=SName,SAddress=地址,‘河北兴隆‘ as 学校名称191              from Students192         (6)查询返回限制的行数193             select Top 5 SName,SAddress194             from Students where SSex=0195     196 197 4-2:查询排序198     如：199     select StudentID as 学员编号,(Score*0.9+5) as 综合成绩200     from Score201     where (Score*0.9+5)>60202     order by Score203 4-3:在查询中使用函数204     a.字符串函数205     b.日期函数206     c.数学函数207     d.系统函数208 209 4-4:数据查询的案例分析（1）210 211 5.数据查询(二)212 213 5-1:模糊查询214     a.使用Like进行模糊查询215         select *from Students where SName LiKE ‘张%‘216     b.使用Between在某个范围内进行查询(使用NOT去反)217         select *from Sales where ord_date not between ‘1992-8-1‘218     c.使用In在列举值内进行查询219     如：220     select SName as 学员姓名 from Students where SAddress in(‘北京‘,‘广州‘,‘上海‘)221     order by SAddress222 223 5-2:SQL Server中的聚合函数224     a.Sum(select sum(ytd_sales) from titles where type=‘business‘)225     b.Avg(select avg(Score) as 平均成绩 from Score where Score>=60)226     c.Max和Min(select avg(Score) as 平均成绩,max(Score) as 最高分,min(Score) as 最低分 from Score where Score>=60)227     d.Count(select count(*) as 及格人数 from Score where Score>=60)228 229 5-3:分组查询230     a. 使用Group By进行分组查询231     如：232     select CourseID,avg(Score) as 课程平均成绩233     from Score234     group by CourseID,CourseID235     b.使用Having子句进行分组筛选236     如：237     select StudentID as 学员编号,CourseID as 内部测试，avg(Score) as 内部测试平均成绩 from Score238     group by StudentID,CourseID239     having count(Score)>1240     241     Where ---->Group By---->Having242 243 5-4:多表联接查询244     a.多表联接查询的分类245         (1)内联接(Inner join)246         (2)外联接247             左外联接（Left Join或Left Outer Join） 右外联接(Right Join或Right Outer Join) 完整外联接(Full Join或Full Outer Join)248         (3)交叉联接(交叉联接返回左表中的所有行，左表中的所有行再一一组合，相当于两个表"相乘")249     b.内联接查询250         (1)在Where子句中指定联接条件251         (2)在From子句中使用Join..on252     c.外联接查询253         (1)左外联接查询254         (2)右外联接查询255 256 5-5：数据查询的案例分析(2)257     a.查询一张表中的奇数行和偶数行258     b.银行卡恢复259 260 261 262 6.数据库的设计263 264 6-1:为什么需要规范的数据库设计265     a.什么是数据库的设计(将数据库中的数据对象以及这些数据对象之间关系，进行规划和结构化的过程)266     数据库的设计目的：减少数据冗余，维护数据的完整性（减少复杂程度）267     b.数据库设计非常重要268         (1)效率高    (2)便于进一步扩展    (3)使得应用程序开发变得更容易269 270 6-2:设计数据库的步骤271     a.需求分析阶段    b.概要设计阶段    c.详细设计阶段    d.代码实现阶段272     (收集信息、标识对象、标识每个对象需要存储的详细信息、标识对象之间的关系)273 274 6-3:绘制E-R(实体-关系)图275     a.实体-关系模型(箭头指向1方向、矩形代表实体集、椭圆代表属性、鳞形代表关系集、直线用来连接属性和实体或关系集)276     (实体、属性、关系、映射基数【一对一、一对多、多对一、多对多】、实体关系图)277     b.如何将E-R图转化为表(多多关系建立表关系是通常拆分为一多关系)278 6-4:数据规范化279     a.设计问题(减少数据冗余，维护数据的完整性（减少复杂程度）)280     【信息重复、更新异常、插入异常（无法表示某些信息）、删除异常（丢失有用的信息）】281     b.规范设计（三大范式）282     【第一范式：目标是确保每列的原子性、都不可再分的最小数据单元（过细、过于复杂、需求）】283     【第二范式：如果满足1NF，并且除了主键以外的其他列，都依赖于该主键。一个表描述一件事情（组合键）】284     【第三范式：如果一个关系满足2NF，并且除了主键以外的其他列都不直接依赖于主键列（低-高、列表拆分、关联性【连动性 会变化、规律性、拆分 完整性 效率 包含性）】285         (1)是否满足第一范式286         (2)是否满足第二范式287         (3)是否满足第三范式288         【通常情况下，为了操作方便可以适当增加数据冗余现象】289 290 291     c.规范化和性能的关系292     【既要考虑三大范式，避免数据的冗余和各种数据操作异常，又要考虑数据访问性能】293     【通常情况下，为了操作方便可以适当增加数据冗余现象】294 295 296 7.数据库的实现297     298 7-1:T-SQL语句回顾（注意：删除和更新，一定要记住加条件！！！！）299     a.添加数据300         语法：insert [into] 表名 (字段1，字段2，。。。) values (值1，值2，。。)301         insert into stuInfo(stuName,stuNo,stuAge) values (‘张三‘,‘s2234‘,22)302     b.修改数据303         语法：update 表名 set 字段1=值1，字段2=值2，。。where (条件)304         update stuInfo set stuAge=25 where stuName=‘张三‘305     c.查询数据306         语法：select 字段1，字段2，。。from 表名 where (条件) order by 字段名307         select stuName,stuNo from stuInfo where stuAge<25 order by stuNo308     d.删除数据309         语法：delete from 表名 where (条件)310         delete from stuInfo where stuAge<20311 312 7-2:使用SQL语句创建和删除数据库(主数据文件:*.mdf、次要数据文件:*.ndf、日志文件:*ldf)313     a.创建数据库314     语法：315     create database 数据库名316     on [primary]317     (318     <数据文件参数>[,..n]  [<文件组参数>]319     )320     [log on]321     (322     {<日志文件参数>  [,..n]}323     )324     go325     文件参数语法如下：326     ([name=逻辑文件名，]327      filename=物理文件名328      [,SIZE=大小]329      [,maxsize={最大容量|unlimited}]330      [,filegrowth=增长量])  [,..n]331     文件组的语法如下：332     filegroup 文件组名 <文件参数> {,..n}333     b.删除数据库334     语法：335     drop database 数据库名336     扩展：337      remove file 文件名338      modify file (name=文件名,filegrowth=50MB)   【modify修改】339      例如：340      use master341      go342      if exists(select *from sysdatabases where name=‘stuDB‘)343      drop database stuDB344      create database stuDB345      on(346      ...347      )348      log on349      (350      ...351      )352      go353 354     【select db_id(stuDB)355            (object_id)356     if db_id(‘stuDB‘) is not null357     drop database stuDB】358     359 7-3:使用SQL语句创建和删除表360     a.创建表361     create table 表名362     (363     字段1 数据类型 列的特征，364     字段2 数据类型 列的特征，365     ...366     )367     go368     例如：369     use stuDB370     go371     create table stuInfo372     (373     stuName varchar(20) not null,--学员姓名374     stuNo char(6) not null--学号375     )376     go377     b.删除表378     drop table 表名379     例如：drop table stuInfo380     【381     use stuDB382     go383     if exists(select *from sysobjects where name=‘stuInfo‘)384     drop table stuInfo385     create table stuInfo386     (387     ...388     )389     go    390     】391     (sysdatabases 数据库、sysobjects 表，视图，存储过程、sysindexs 索引)392 7-4：使用SQL语句创建和删除约束393     【主键约束 Primary Key constraint、唯一约束 Unique Constraint、检查约束 Check Constraint、默认约束 Default Constraint、外键约束 Foreign Key Constraint】394     a.添加约束395     语法：396     alert table 表名397     add constraint 约束名 约束类型 具体的约束说明398     例如：399     --添加主键约束400     alert table stuInfo401     add constraint PK_stuNo primary key (stuNo)402     go403     --唯一约束404     alert table stuInfo405     add constraint UQ_stuID unique (stuID)406     go407     --默认约束408     alert table stuInfo409     add constraint DF_stuAddress default (‘地址不详‘) for stuAddress410     go411     --检查约束412     alert talbe stuInfo413     add constraint CK_stuAge check(stuAge between 15 and 40)414     go415     --外键约束416     alert table stuMarks417     add constraint FK_stuNo418     foreign key (stuNo) references stuInfo (stuNo)419     go420     b.删除约束421     语法：422     alert table 表名423     drop constraint 约束名424     例如：425     alert talble stuInfo426     drop constraint DF_stuAddress427     例如：428     alert table 表名429     drop column 字段名430     例如：431     alert table 表名432     drop column 字段名 数据类型433     434 435 7-5:使用SQL语句创建登录436     【第一关：登录到服务器，第二关：访问到数据库，第三关：表单，授权（增、删、改、查）】437     a.创建账户（二种身份验证：SQL身份验证和Windows身份验证）438     语法：439     Windows身份：exec sp_grantlogin ‘Windows域名\域账户‘440     SQL身份：exec sp_addlogin ‘账户名‘,‘密码‘441     例如：442     --添加Windows登录账户443     exec sp_grantlogin ‘jbtraining\s26301‘444     --添加SQL登录账户445     exec sp_addlogin ‘zhangsan‘,‘1234‘446     b.创建数据用户447     语法：448     exec sp_grantdbaccess ‘登录账户‘,‘数据库用户‘449     例如：450     --在stuDB数据库中添加两个用户451     use stuDB452     go453     exec sp_grantdbaccess ‘jbtraining/s26301‘,‘s26301DBUser‘454     --S26301DBUser为数据库用户名455     exec sp_grantdbaccess ‘zhangsan‘,‘zhangsanDBUser‘456     c.给数据库用户授权(增、删、改、差、创建表)457     语法：458     grant 权限 [on 表名] to 数据库用户459     例如：460     use stuDB461     go462     --为zhangsanDBUser分配对表stuInfo的select、insert、update权限463     grant select ,insert,update on stuInfo to zhangsanDBUser464     --为S26301DBUser分配创建表的权限465     grant create table to S26301DBUser466     扩展：（表约束的禁用）467     --1.在已有的数据表中加约束，但不影响原有数据468     alert table 表名469     with NoCheck470     add constraint 约束名     check (stuAge Between 15 and 40)471     --2.对已有约束，进行临时禁用472     步骤一：alert table 表名473         NoCheck constraint 约束名474     步骤二：alert table 表名475     步骤三：check Constraint 约束名      【批量数据使用】476 477 478 8.T-SQL编程479     480 8-1:使用变量481     a.局部变量482     语法：set @variable_name=value或select @variable_name=value483     声明变量:declare @variable_name DataType484     例如：declare @name varchar(8) 485           declare @seat int486     b.全局变量(@@error、@@identity、@@servicename、@@version)487 8-2:输出语句488     语法：489     print 局部变量或字符串490     select 局部变量 as 自定义列名491     例如：492     print ‘服务器的名称：‘+@@servername493     select @@servername as 服务器名称494     print ‘当前错误‘+@@error495     print ‘当前错误号‘+convert(varchar(5),@@error)496 497     insert into stuInfo(stuName,stuNo,stuSex,stuAge) values(‘梅超风‘,‘s25318‘,‘女‘,‘23‘)498     --如果大于0，表示上一条语句执行有错误499     print ‘当前错误号‘+convert(varchar(5),@@error)500     print ‘刚才报名的学员，座位号为：‘+convert(varchar(5),@@identity)501 502 8-3:逻辑控制语句503     504     a.if-else条件语句        if(条件)505     语法：                     begin506     if(条件)                        语句1507       语句或语句块                    语句2508     else                         ...509       语句或语句块                 end510                     else511                              ...512     b.while循环语句513     语法：514     while(条件)515         语句或语句块516             [break]517     c.Case多分支语句518     语法：519     case520         when 条件1 then 结果1521         when 条件2 then 结果2522         [else 其他结果]    523     end524 525 8-4:批处理语句(提高效率)526     概念：它是一条或多条SQL语句的集合，SQL Server将批处理语句编译成一个可执行单元，此单元为执行计划。527 528 529 9.高级查询530 531 9-1:简单子查询532     例如：533     declare @age int 534     select @age=stuAge from stuInfo where stuName=‘李斯文‘535     select *from stuInfo where stuAge>@age536     go537 538 539     select *from stuInfo540     where stuAge>(select stuAge from stuInfo where stuName=‘李斯文‘)541     go542     语法：543     select ...from 表1 where 字段1>(子查询)544 545     例如:546     select stuName from stuInfo inner join stuMarks547     on stuInfo.stuNo=stuMarks.stuNo where writtenExam=60548     go549 550 551     select stuName from stuInfo552     where stuNo=(select stuNo from stuMarks where writtenExam=60)553     go554 555 556 9-2:IN和NOT IN子查询557     558     子查询(结果)    条件来源多表： 子查询    要的信息来源多表：表联接 【条件我要来源于多表】559     子查询不仅可存在于结果，也可以是条件 (<,>,=,>=【一行一列】) (in【多行一列】) (exists【多行多列，无条件】)560     老技术替换新技术        分页主键唯一    表联接（存在主外键关系）    子查询（等值联接）561     在复杂运算中，如果中间查询结果在后面需要使用即可以保存为一张临时表#562     例如：563     select stuName from stuInfo564     where stuNo not in (select stuNo from stuMarks)565     go566 567     568     select stuName from stuInfo569     where stuNo in(select stuNo from stuMarks)570     go571 572 9-3:EXISTS和NOT EXISTS子查询573     语法：574     if exists(子查询)575     语句576     例如：577     if exists(select *from sysdatabases where name=‘stuDB‘)578     drop database stuDB579     create database stuDB580 581 9-4:T-SQL语句的综合应用(P74页)582 583 584 10.事务、索引和视图585     586 10-1:事务587     事务的应用场景：当一个事务需要两条或两条以上SQL Server语句完成时，可以使用事务保证其完整性（要不执行完成，否则，全不执行）588     a.为什么需要事务（如：银行转账）589     b.什么是事务590         事务是作为单个逻辑工作单元执行的一系列操作（原子性、一致性、隔离性、持久性）591     c.如何创建事务592         开始事务：begin transaction    提交事务：commit transaction  回滚事务：rollback transaction593         显示事务：用begin transaction指定  隐式事务：设置set implicit_transactions on 直到关闭594         多态SQL语句当成一个事务595         自动提交事务596     在一个事务中：前面的SQL语句会影响到后面的SQL语句执行（前面SQL语句结果，可以被条件SQL语句所访问）597     在一个事务中设置回滚点（save） rollback回滚    事务间可以嵌套其它事务（外层管理内层事务)【P88页】598 599 10-2:索引600 601     a.什么是索引602     在现在，索引是唯一能够提高检索速度（高效） 当出现第一排序列与第二排序列时，只有在满足第一排序列才能够进行下一排序列603     索引页、索引、唯一索引、主键索引、聚集索引，非聚集索引604     注意：在创建唯一约束的时候，就会默认创建唯一索引，尽管唯一索引有助于找到信息，但为了获得最佳性能，建议使用主键约束605           一个表只能创建一个聚集索引，但可以有多个非聚集索引，设置某列为主键，该列就默认为聚集索引和主键索引（基于主键索引、排序）606           聚集索引查询速度要快于非聚集索引607     b.如何创建索引608         (1)使用Microsoft SQL Management Studio 创建索引609         (2)使用T-SQL语句创建索引610         语法：611         create [unique] [clustered|nonclustered] index index_name612         on table_name (column_name[,column_name]...)613         [with614             fillfactor=x615         ]616         其中：unique指定唯一索引、clustered，nonclustered指定是聚集索引还是非聚集索引、fillfactor表示填充因子（少），指定一个0-100的值，该值只是索引页填满的空间所占得百分比。（方便更新）617         例如：618         --指定索引按索引,ix_stuMarks_writtenExam查询619         select *from stuMarks620         (index=ix_stuMarks_writtenExam)621         where writtenExam between 60 and 90622         (使用索引可加快数据检索速度)（缺点：更新速度慢，查询速度快）623 624 10-3:视图625 626     a.什么是视图627     视图操作的通常用三种操作：628     筛选表中的行、防止未经许可的用户访问敏感数据（安全性）、将多个物理数据表抽象为一个逻辑数据表（降低复杂度、简化数据库的结构）629     b.如何创建视图630     增加三级权限体系，下次可分配到视图和存储过程631         (1)使用Microsoft SQL Server Manager Studio 创建视图632         (2)使用T-SQL语句创建视图633         语法：634         create view view_name        视图：只存放SQL语句（select 一条语句）    635         as                存在业务逻辑   as开头    go结束636             <select 语句>            637         go                638         639 640         select *from 表名641             视图        (可删除操作多表联查有要求)642             结果集        （更新操作，更新到基表）643 644         例如：645         --使用视图：视图是一个虚拟表，可以像物理表一样打开646         select *from view view_stuMarks647         --创建视图：查看学员的成绩情况648         create view view_stuInfo_stuMarks649         as650             select 姓名=stuName,学号=stuInfo.stuNo,笔试成绩=writtenExam,机试成绩=labExam,平均分=(writtenExam+labExam)/2651             from stuInfo left join stuMarks on stuInfo.stuNo=stuMarks.stuNo652         go653 654 655 11-1:存储过程656 657     a.什么是存储过程658     查询分析器、批处理go远远低于存储过程效率、编译，优化、执行速度更快，只执行一次、安全性P94页、最后可分配到存储过程659     使用存储过程的几个优点：660         (1)允许模块化程序设计（可理解所有方法使用，业务逻辑封装存储过程）661         (2)允许更快执行（数据与数据交互时，当数据需大量使用的时候，写入存储过程中）662         (3)减少网络流量（仅反馈信息）663         (4)可作为安全机制使用（系统存储过程、用户自定义的存储过程）664     b.常用的系统存储过程665         通常以：sp开头的为系统存储过程，xp开头的为doc命令创建的存储过程666         注意：在数据库中，通常不可以修改数据库名称，但可以通过在新建查询中调用sp_renamedb系统存储过程来修改。667         语法:668         exec xp_cmdshell doc命令 [no_output]669         670 671 11-2:用户定义的存储过程672     a.创建不带参数的存储过程673     语法：674     create proc[edure] 存储过程名675         [{@参数1 数据类型}{=默认值] [output],676          ...,677         {@参数n 数据类型} [=默认值] [output]678         ]679     b.创建带输入参数的存储过程(输入参数、输出参数)680     语法：681     create proc[edure] 存储过程名682         [{@参数1 数据类型}{=默认值] [output],683          ...,684         {@参数n 数据类型} [=默认值] [output]685         ]686     as687         SQL语句688     go689     690     例如：691     use stuDB692     --检测是否存在：存储过程存放在系统表sysobjects中693     if exists(select *from sysobjects where name=‘proc_stu‘)694     drop procedure proc_stu695     go696     --创建存储过程697     create proc proc_stu698         @writtenPass int=60,699         @labPass int=60700         as701            print ‘笔试及格线：‘+convert(varchar(5),@writtenPass)702                  +‘机试及格线：‘+convert(varchar(5),@labPass)703            print ‘---------------------------------------------‘704            print ‘       参加本次考试没有通过的学员：            ‘705            select stuName,stuInfo.stuNo,writtenExam,labExam from stuInfo706            inner join stuMarks on stuInfo.stuNo=stuMarks.stuNo707                where writtenExam<@writtenPass or labExam<@labPass708     go709     --调用存储过程710     exec proc_stu --都采用默认值：笔试和机试及格线都为60分711     exec proc_stu 64 --机试采用默认值：笔试及格线64分，机试及格线60分712     exec proc_stu 60,55 --都不采用默认值：笔试及格线60分，机试及格线55分713     --错误的调用方式：exec proc_stu ,55 --希望笔试采用默认值，机试及格线55分714     --正确的调用方式：exec proc_stu @labPass=55 --笔试采用默认值，机试及格线55分715     716     c.创建带输出参数的存储过程717     例如：718     use stuDB719     go720     --检测是否存在：存储过程存放在系统表sysobjects中721     if exists(select *from sysobjects where name=‘proc_stu‘)722     drop proc proc_stu723     go724     --创建存储过程725     create proc proc_stu726       @notpassSum int output,--output关键字，否则视为输入参数727       @writtenPass int=60,--默认参数放后728       @labPass int=60  --默认参数放后729     as730       print ‘笔试及格线：‘+convert(varchar(5),@writtenPass)731         +‘机试及格线：‘+convert(varchar(5),@labPass)732       print ‘---------------------------------------------‘733           print ‘       参加本次考试没有通过的学员：            ‘734           select stuName,stuInfo.stuNo,writtenExam,labExam from stuInfo735       inner join stuMarks on stuInfo.stuNo=stuMarks.stuNo736       where writtenExam<@writtenPass or labExam<@labPass737     go738 739     --调用存储过程740     declare @sum int --定义变量，用于存放调用存储过程时返回的结果741     exec proc_stu @sum output ,64 --调用时，也带output关键字，机试及格线默认为60742     print ‘-------------------------------------------------------------------‘743     if @sum>=3744         print ‘未通过人数：‘+convert(varchar(5),@sum)+‘人，超过60%，及格分数线还应下调‘745     else746         print ‘未通过人数：‘+convert(varchar(5),@sum)+‘人，已控制在60%以下，及格分数线适中‘747     go748 749     d.处理错误信息750     751     语法：752     raiserror (msg_id |msg_str) {,serverity,state} [with option[,...n]]753     msg_id:在sysmessages系统表中指定的用户定义错误信息754     msg_str:用户定义的特定信息，最长255个字符755     severity:与特定信息相关联，表示用户定义的严重性级别756     state:表示错误的状态，是1-127的值757     option:指示是否将错误记录到服务器错误日志中758 759     例如：760     declare @myTable table (stuName varchar(20),stuPwd varchar(20))761     select *from @myTable762     表变量不要超过一万条，超过一万用临时表763     结果集：insert into @myTable764         exec proc_finduseByName765     select ‘王三‘,28,‘男‘ union766     select ......767     select *from @myTable768     存储过程中可调用其它存储过程当成一个T-SQL语句769     print+‘两边数据类型一致‘770     仅仅可以改变  全局变量771     print @@error772     raiserror (‘dddd‘,11,2)773     print @@error774     大于10才影响error775     --1.中间那个参数大于10，可影响error776     --2.使用with，设置@@error   with+seterror   with+login    完后+return777     将错误提取带出存储过程778     779     无参，无输出780     输出结果方式    (1)输出结果集[一，多个   结果集]        (2)返回参数 变量 任意类型 P112页        (3)return 只返回类型781     调用：    (1)简单 exec proc_finduserByName ‘wangjn‘,20,temp output --(insert into 表名 values (‘王军‘,20,‘金堂‘))782         (2)完整(可换位置) exec proc_finduserByName @userName=‘王军‘  --(insert into 表名(列名1，列名2) values (值1，值2))783         存储过程中不允许创建视图784         参数列表建议：输入-输出-含默认值        查询赋值（只有一条）        定义使用时都使用output785     在存储过程中实现分页：786     declare @sqltxt varchar(100)787     set @sqltxt=‘select *from ‘+@tableName+‘where‘+@colName+‘=‘+@value788 789     select top 10[sqltext] *from userInfo where userid not in (select top 10*2[@Num或@sqltext] userid from userInfo)790     791     exec proc_finduserByName ‘userInfo‘,‘userName‘792 793 T-SQL补充：794     在进行表联接的时候，小表考前，大表靠后（增强查询效率）795     A.declare @mytb Table (id int,names varchar(10))796     表变量，在内存中797     B.create table #myTable (id int ,names varchar(10))798     #局部临时表，当前数据库799     C.create table ##myTable (id int,names varchar(10))800     ##全局临时表，在tempdb数据库中（占表名或位置）801     区别：802     A.声明周期短（作用域小），用于当前批处理语句之中803     B.连续不断（有效），只在当前联接对象有效804     C.连续不断（有效），不同联接对象任可使用805     触发器（操作日志表）：（代码或语句）806     触发器与表相连，建于某一张表单上807     触发器是一种特殊的存储过程808     特殊：（1）不能使用exec外部调用或只使用显型调用（insert，update，delete）（2）特殊事务不有使用begin transaction而自动触发成一个事务成功或失败809     语法：810     create trigger Trigger1811     on dbo.userInfoTable812     for /*insert,update,delete*/813     as814         /*if update()...列集触发器*/815     触发器的原理：816     (1)一行数据发生变化，就会被触发一次817     例如：insert 就会记录了插入信息    deleted就记录了删除信息818     (2)只有一个（inserted、deleted）被触发，只能访问到（inserted、deleted）相应信息819     (3)将更新划分为两张表  inserted表与deleted表820     (4)update中：返回（inserted、deleted）语句，只保存了一条语句，并且它与所关联的表结构一样821     在触发器里永远不存在where条件822     触发器影响相应效率（inserted、deleted），并且它当中有两张内存表823     列集触发器的诊断    即：if   /*update (列名)....*/   无单引号在小括号内中824     例如：825     if update(列名 )  不加单引号826     select @new=HowseHolderIdNo from inserted827     select @old=HowseHoderIdNo from deleted828     if @old<>@new829         begin830             insert into 表名 select...831             ...........832             ..........833         end