SQLSERVER2000教程

1、SQLSERVER2000教程第一章第一节简介SQLServer2000是微软公司开发的采用SQL语言的关系型数据库管理系统，它拥有高弹性与多元化的结构，不仅符合业界的需要，更能与现今的互联网紧密集成，而对WindowsCE/98/NT/2000/XP/2003等操作系统的全面支持的优越性得到最终程序开发人员的普遍认可。SQLServer2000是服务器级的数据库管理系统,不论是客户机/服务器、多层结构，还是DatabaseWeb应用程序,SQLServer2000都起着后端数据库的角色。可以说，SQLServer2000是所有数据的汇总与管理中心，是整个应用系统的枢纽。第二节数据库分类逻辑数

2、据库(1)系统数据库:系统数据库作为SQLSERVER默认安装的一部分而安装，由系统表和存储过程组成。系统数据库、表以及存储过程共同为您提供了设计、创建、部署和维护世界级SQLSERVER数据库的工具和支持。Master数据库:是追踪所有其它数据库和配置信息的关键数据库。它控制着用户数据库和SQLSERVER的操作。Master数据库对你的信息绝对重要，所以你应该总是保留Master数据库的最近备份。Msdb数据库:是SQLSERVERAgent的主要支持。SQLSERVERAgent负责复制、任务调度、警报、系统操作员信息以及备份信息。因此，Msdb也是非常重要的，哪怕你不使用SQLSERV

3、ERAgent，在Msdb中的备份信息也有助于数据库的恢复。Model数据库:是一个创建新数据库的模板，它包含了应该出现在每个用户数据库中的系统表。在创建一个数据库时，SQLSERVER会把Model数据库的全部内容复制到新数据库中。因此，如果你想每次创建数据库时自动地创建某些对象、权限和用户，你就可以把它们放在Model数据库中。Tempdb数据库:保存所有临时表和临时存储过程。临时表可以被程序员显示的创建。当客户端连接终止时，这些临时表也会被自动删除。默认大小为8MB.SQLSERVER支持两种类型的临时表:1、以#符号开头的全局临时表可为所有连接所用。在一个全局表创建之后，所有用户都可以

4、使用它。2、以#符号开头的局部临时表仅在创建它们的连接中可见，并且仅能由表的所有者访问。在Tempdb中创建临时表不需要在名称前加标志，并且可以授予权限和废除权限。在Tempdb中直接创建的临时表必须显示的删除，否则它们会一直留在Tempdb中，除非SQLSERVER重新启动。演示数据库:PUBS和NORTHWIND,是SQLSERVER提供的示例数据库。该数据库及其中的表经常在联机帮助的文档内容所介绍的示例中使用。自定义数据库:用户通过SQL命令自己创建的数据库。第三节数据库分类物理数据库（1）主要数据文件:此文件是数据库的起始点。每个数据库必须有且仅有一个主要数据文件。主要数据文件的扩展名

5、.mdf。（2）次要数据文件:这些文件是可选的，可以包含所有不在主要数据文件中的数据和对象。数据库可以有多个次要数据文件。次要数据文件的扩展名为.ndf。（3）日志文件:这些文件包含所有用于恢复数据库的事务日志信息。每个数据库至少有一个日志文件。日志文件扩展名为.Id代注意:数据库文件一词通常指的是三种文件类型中的任何一种。数据文件一词则指的是主要数据文件和次要数据文件。日志文件一词指的则是作为数据库事务日志一部分的文件。第四节数据的存储方式1、页:在SQLSERVER中页是最基本的存储单位。页包含数据本身以及数据的物理存放位置的信息。所有页的大小都是相同的：8KB（在7.0版本以前，页的大小

6、是2KB）。2、盘区:分为混合盘区和统一盘区。当创建一个表时，在混合盘区上分配给该表引一个页。如果对该数据库来说不存在混合盘区，就给它分配一个这样的盘区。混合盘区可以包含来自几个表的页。一个混合盘区最多可包含来自8个表的页（因为一个盘区由8页组成，所以大小为64KB）。当一个表增加的页超过8页时，就作为统一盘区被保留为该表专用。第五节文件组文件组是一个或多个文件的集合，这些文件组成分配和管理的单元。文件组可以在一开始创建数据库时创建，以后多个文件添加到数据库中时再创建。但是，一旦文件添加到数据库中以后，就不能再将这些文件移动到其他不同的文件组中。文件组只能包含数据文件，不能有事务日志文件。一个

7、文件不能属于多个文件组。文件组有三种类型：1、主文件组：这些文件组包含主要数据文件和未放入其他文件组的所有其他文件。系统表的所有页都是从主文件组分配的。2、用户定义文件组：这些文件组是在CREATEDATABASE或ALTERDATABASE语句中，或企业管理器中的属性页中使用FILEGROUP关键字指定的。3、默认值：这些文件组包含所有表和索引的页，这些表和索引在创建时未指定文件组。在每个数据库中，一次只有一个文件组为默认文件组。如果没有指定默认文件组，则默认文件组为主文件组。注意：每个数据库最多只能创建256个文件组，而且文件组不能独立于数据库文件创建。文件组是对数据库中的文件进行分组的一

8、种管理机制。第二章创建和管理数据库第一节创建数据库-格式Createdatabase数据库名OnPrimary(Name=逻辑文件名，Filename=物理文件名，Size=文件起始大小，Maxsize=文件最大容量，Filegrowth=文件增量)，Filegroup文件组名(Name=逻辑文件名，Filename=物理文件名，Size=文件起始大小，Maxsize=文件最大容量，Filegrowth=文件增长容量)Logon(Name=逻辑文件名，Filename=物理文件名，Size=文件起始大小，Maxsize=文件最大容量，Filegrowth=文件增长容量)说明：Primary:指

9、定下面文件为主文件组的文件。可省略。Filename:指定文件的实际存储位置。Size:指定文件的起始大小。Maxsize:指定文件可达到的最大容量。Filegrowth:定义的文件的增量。文件的增量设置不能超过Maxsize设置。可以指定一个确切的增长数值，也可以指定增长的百分比(起始值的百分比)，默认为10%。Logon:指定下面为日志文件。-实例(1)创建只有一个数据文件和一个日志文件的数据库CreatedatabasestudentOnPrimary(Name=student_mdf,Filename=d:sqlstudent_mdf.mdf,Size=2,Maxsize=10,Fil

10、egrowth=1)Logon(Name=student_ldf,Filename=d:sqlstudent_ldf.ldf,Size=1,Maxsize=5,Filegrowth=1)创建有多个数据文件和日志文件的数据库CreatedatabasescoreOnPrimary(Name=score_mdf,Filename=d:sqlserverscore_mdf.mdf,Size=5MB,Maxsize=50MB,Filegrowth=5MB),(Name=score_ndf1,Filename=d:sqlserverscore_ndf1.ndf,Size=3MB,Maxsize=30MB

11、,Filegrowth=20%),(Name=score_ndf2,Filename=d:sqlserverscore_ndf2.ndf,Size=6,Maxsize=50,Filegrowth=4)Logon(Name=score_ldf1,Filename=d:sqlserverscore_ldf1.ldf,Size=8MB,Maxsize=100MB,Filegrowth=5MB),(Name=score_ldf2,Filename=d:sqlserverscore_ldf2.ldf,Size=10,Maxsize=100,Filegrowth=10)创建带有多个文件组的数据库Creat

12、edatabasebookOnPrimary(Name=book_mdf,Filename=d:booksbook_mdf.mdf,Size=3,Maxsize=30,Filegrowth=3),Filegroupgroup1(Name=book_ndf1,Filename=d:booksbook_ndf1.ndf,Size=2MB,Maxsize=20MB,Filegrowth=25%),Filegroupgroup2(Name=book_ndf2,Filename=d:booksbook_ndf2.ndf,Size=4,Maxsize=30,Filegrowth=4)Logon(Name=

13、book_ldf1,Filename=d:booksbook_ldf1.ldf,Size=5,Maxsize=40,Filegrowth=5),(Name=book_ldf2,Filename=d:booksbook_ldf2.ldf,Size=6,Maxsize=60,Filegrowth=30%)第二节修改数据库(1)添加数据文件:格式:Alterdatabase数据库名AddFile(Name=逻辑文件名，Filename=物理文件名，Size=文件起始大小，Maxsize=文件最大容量，Filegrowth=文件增量)ToFileGroup文件组名说明:ToFileGroup:指定添加

14、的数据文件到哪个文件组中，该文件组必须存在，默认为主文件组。实例：Alterdatabasestudentaddfile(Name=student_ndf,Filename=d:sqlstudent_ndf.ndf,Size=1,Maxsize=5,Filegrowth=1)(2)添加日志文件:格式：Alterdatabase数据库名AddLogFile(Name=逻辑文件名，Filename=物理文件名，Size=文件起始大小，Maxsize=文件最大容量，Filegrowth=文件增量)实例：AlterdatabasestudentAddLogFile(Name=student_ldf1,

15、Filename=d：sqlstudent_ldf1.ldf,Size=4,Maxsize=40,Filegrowth=15%)(3)添加文件组：格式:Alterdatabase数据库名AddFilegroup文件组名。实例:AlterdatabasestudentAddFilegroupgroup1向该文件组中添加文件AlterdatabasestudentAddFile(Name=student_ndf2,Filename=d:sqlstudent_ndf2.ndf,Size=3,Maxsize=30,Filegrowth=3)ToFilegroupgroup1(4)修改文件(数据文件和日

16、志文件):格式:Alterdatabase数据库名ModifyFile(Name=逻辑文件名，Size=新的文件大小,Maxsize=将要达到的容量,Filegrowth=修改后的增量)说明:修改数据文件和日志文件的格式是相同的，但逻辑文件名和物理文件名不能修改。在修改文件时，必须指定文件的逻辑名，用来标识将要修改的文件。而不必指定文件的物理名，否则将出现错误。如果指定修改文件的Size,则新的文件大小必须比当前文件大小要大。而修改文件的Maxsize和Filegrowth时，数值可以增大，也可以和原来的相同。实例：AlterdatabasestudentModifyFile(Name=stu

17、dent_ldf1,Filegrowth=20%)(5)修改文件组属性:a）Alterdatabase数据库名ModifyFilegroup文件组名readonly|readwrite|defaultb）说明：当想修改文件组的属性时，必须保证该文件组中存在文件。Readonly为只读、readwrite为读写、default为默认设置。一般不修改。c）实例:AlterdatabasestudentModifyFilegroupgroup1readwrite（6）删除文件:a）格式:Alterdatabase数据库名RemoveFile文件名b）说明:不能删除主要数据文件和主要日志文件。c）实例

18、：AlterdatabasestudentRemoveFilestudent_ldf1（7）删除文件组:a）格式:Alterdatabase数据库名RemoveFilegroup文件组名b）说明:要删除的文件组中不能包含数据文件。c）实例：AlterdatabasestudentRemoveFilestudent_ndf2AlterdatabasestudentRemoveFilegroupgroup1第三节删除数据库（1）格式:Dropdatabase数据库名（2）实例：Dropdatabasestudent第四节设置数据库选项1、格式:SP_dboption数据库名，选项名，ture|fa

19、lse2、说明:SP_dboption:系统的存储过程，可以通过它设置数据库选项，它支持对所有可用的数据库选项进行设置。a）如果只设置SP_dboption数据库名，那么将显示在本数据库中设置为True的选项。b）如果设置SP_dboption数据库名，选项名,那么将显示指定的选项当前处于什么状态（ON或者OFF）3、实例:将student数据库的readonly选项设置为True,即打开此选项。SP_dboptionstudent,readonly,true第五节清空数据库的LOG日志文件1）格式:DUMPTRANSACTION数据库名WITHNO_LOG（2）实例：DUMPTRANSACT

20、IONstudentWITHNO_LOG第六节压缩数据库有时，人们可能为预期有一定程度活动的数据库分配了太多的空间，当意识到分配了太多空间时，可能决定压缩分配的空间大小oSQLSERVER提供三种可以压缩数据库大小的方法，autoshrink数据库选项，企业管理器”和数据库一致性检查器（DBCC）”命令。1、使用DBCCShrinkdatabase压缩数据库：DBCCShrinkdatabase命令在默认情况下会对数据库的数据和日志部分都进行压缩。如果您只想减少数据和日志部分，则您必须首先压缩整个数据库，然后使用Alterdatabase语句来增加数据库的数据或日志部分。、格式:DBCCShr

21、inkdatabase(数据库名，压缩后可使用的百分比)NOTRUNCATE|TRUNCATEONLY、说明：NOTRUNCATE:不会将可用的空间释放给操作系统，而是留给数据库文件。TRUNCATEONLY:将数据文件未用的空间释放给操作系统。、实例：DBCCShrinkdatabase(student,20)TRUNCATEONLY2、使用“企业管理器”压缩数据库:有两种方式:在企业管理器中，选中要压缩的数据库，点击鼠标右键，点击属性，从弹出的“数据库属性”的对话框中选中“选项”标签，在复选框中选中“自动压缩”选项即可。在企业管理器中，选中要压缩的数据库，点击鼠标右键，选中“所有任务”，点

22、击“收缩数据库”,填充相应的选项即可。第七节维护数据库SetShowPlan_TextSetShowPlan_All第三章数据类型第一节系统数据类型是SQLSERVER支持的内置数据类型。1、字符型:char、nchar、varchar、nvarchar、text、ntextchar:固定长度的非Unicode字符数据，最大的长度为8000字符。nchar:固定长度的Unicode数据，最大的长度为4000字符。varchar:可变长度的非Unicode数据，最大的长度为8000字符。nvarchar:可变长度的Unicode数据，最大的长度为4000字符。text:可变长度的非Unicode

23、数据，最大的长度为2人31-1个字符。ntext:可变长度的Unicode数据，最大的长度为2人30-1个字符对于定义为char或nchar的列，SQLSERVER将用字符串来填满指定的字节数。定义为varchar或nvarchar的列只存储输入的实际长度，可能舍去尾部空间。SQLSERVER处理尾部空间，取决于SETANSI_PADDING，以及该列是固定长度还是可变长度。根据SETANSI_PADDING值处理尾部空间ANSI_PADDINGchar和ncharVarchar和nvarcharON串被空格填充到列的长度串未被空格填充到列的长度，尾部空间被保留OFF串被空格填充到列的长度串未

24、被空格填充到列的长度，尾部空间被截掉2、日期和时间型:smalldatetime、datetime。smalldatetime:从1900年1月1日到2079年6月6日，精确到1分钟。datetime:从1753年1月1日到9999年12月310，精确到三百分之一秒，即3.33毫秒。3、数据型数值类型A、整型：smallint、int、tinyint。smallint:从2人15到2人15-1。int:从-2人31到2人31-1。tinyint:从0到255。B、近似数字数据类型:float和real。afloat:浮点精度数字数据，从-1.79E+308到1.79E+308。real:浮点精

25、度数字数据，从-3.40E+308到3.40E+308。C、精确数字数据类型：decimal和numeric。decimal:不带符号的整数，按10进位。numeric:decimal(十近制)的同义词。D、货币数据类型:money和smallmoney。money:从-2人63到2人63-1,精确到每个货币单位的万分之一。smallmoney:从-214，748.3648到+214，748.3647，精确到每个货币单位的万分之一。4、逻辑数据类型:bitBit:整形数据，值为1或0。第二节用户自定义数据类型是SQLSERVER支持的内置数据类型。用户可以通过两个系统存储过程创建和删除用户定义

26、类型。(它们不是真正的新数据类型，而像是一种复合型数据类型或结构。)Sp_addtype过程创建用户定义的数据类型，Sp_droptype过程删除定义的数据类型。1、创建自定义的数据类型、格式:Sp_addtype自定义数据类型名，系统的数据类型，null|notnull、说明:A、用户自定义的数据类型是基于系统的数据类型创建的。B、null|notnull：指定该列是否为空。默认为null。C、如果系统数据类型包括圆括弧，必须用引号把它括起来。、实例:Sp_addtypebirthday，datetime，notnullCreatetablestu1(sidint,sbirthdaybirt

27、hday)2、删除自定义的数据类型、格式:Execsp_droptype自定义的数据类型。、实例:Execsp_droptypebirthday。注意:我们还可以通过企业管理器创建自定义的数据类型。首先，选中一个数据库，点击右键，选中“新建”，从弹开的菜单中选中“用户定义的数据类型”，在打开的窗体中填充相应的选项即可。第四章创建和维护表第一节创建表、修改表、删除表1、创建表、格式:Createtable表名(字段名1数据类型,字段名n数据类型)、说明:表名的定名原则要遵循标识符的定名原则。字段和字段之间用逗号隔开。、实例:UsebookgoCreatetablebook(bookidint,b

28、ooknamevarchar(10)2、修改表(1)、增加字段:格式:Altertable表名Add字段名字段类型实例:Altertablebookaddprovideridvarchar(10),addressvarchar(20).、修改字段:格式:Altertable表名Altercolumn字段名字段类型实例:AltertablebookAltercolumnaddressvarchar(10)、删除字段:格式:Altertable表名Dropcolumn字段名实例:Altertablebookdropcolumnaddress3、删除表、格式:Droptable表名、说明:把不再起作

29、用的表从数据库中删除。、实例：Droptablebook第二节数据的完整性是通过实现过程数据完整性和声明数据完整性来强制执行的。1、Identity:identity属性可以生成唯一标识表中每一行的连续值。格式:Identity(初始值，增量)实例：Createtabletab1(idintidentity(1,1),namevarchar(10)说明:一个表中只能有一个identity标识的列，字段必须是整型。不能更新定义有identity属性的列。不能向定义有identity属性的列赋null值或附加默认约束。2、Uniqueidentifier:使用Uniqueidentifer和New

30、id函数也可以生成列的唯一值，与Identity属性类似。如果创建的列是uniqueidentifer数据类型，则必须使用newid函数为它生成新值。A、手动添加列值实例:Createtabletab2(iduniqueidentifier,namevarchar(10)插入值:insertintotab2values(newid(),mary)B、自动添加列值实例：Createtabletab2(iduniqueidentifierNOTNULLROWGUIDCOLCONSTRAINTDF_tab2_IDDEFAULT(newid(),namevarchar(10)插入值:insertint

31、otab2values(mary)说明：在创建ID列时直接设置列的值为自动填充，值的来源由newid()函数随机产生。第三节使用约束(1)主键(primarykey):唯一标识每一行。1)说明:一个表中只可以定义一个主键。不能在主键列中输入null值。最多可定义16列作为主键。定义之后，则不能禁用primarykey约束.2)实例:创建单一主键：Createtabletab3(idintprimarykey,bidint)创建复合主键：Createtabletab4(idint,bidint,constraintpk_id_bidprimarykey(id,bid)(2)外键(foreignk

32、ey):Foreignkey定义列值与另一个表的Primarykey相匹配的列。1)说明：Foreignkey约束必须引用另一个表的Primarykey或Unique约束。相关表中定义为主键的所有列必须作为Foreignkey所包含在当前表中。如果使用Withnocheck选项，将不会验证表中的现有数据。2)实例：Createtabletab5(cidintprimarykeynotnull,idint,foreignkey(id)referencestab3(id)CHECK约束:根据指定值测试列中的输入值。每次再列中插入或更新数据时均要进行这一测试。1)说明：可以为Check约束定义Whe

33、re字句中的类似条件，但它们不能包含子查询。Check约束条件可以引用同一个表中的列。Check约束条件必须对布尔表达式求值。可以绑定有规则的列定义Check约束2)实例:createtabletab6(idint,sexchar(2)constraintchk_tab6_sexcheck(sexin(m,w)默认约束(default):此约束用于在用户未提供列值的情况下，提供一个自动添加的列值。1)说明:个表中只能有一列定义有Default约束。不能在数据类型为Timestamp的列或具有Identity属性的列中定义Default约束。2)实例:Createtabletab7(idint,

34、addressvarchar(20)constraintDef_tab7_adddefault吉林省长春市)或者Altertabletab7addconstraintdef_tab7_iddefault100forid唯一性约束(Unique):在列中应用unique约束以确保列中不输入重复值。列中所有行的值均不相同。1)说明:可以向表中的多列应用unique约束。向现有表应用unique约束时，一直会验证现有数据。可以向notnull列应用unique约束，但仅有其中一行能包含null值2)实例:Createtabletab8(idintunique)或者Createtabletab9(id

35、int)Altertabletab9addconstraintunq_idunique(id)删除约束:对不需要的约束从列中删除。1)格式Altertable表名dropconstraint约束名2)实例:Altertabletab9dropconstraintunq_id说明：删除列前必须先删除列中的约束第五章处理数据第一节Transact-SQL语言介绍SQL(structuredquerylanguage)语言是一种结构化的查询语言。它的功能包括查询、操作、定义和控制四个方面。它是一种综合的、通用的、功能强大的关系数据库语言；Transact-SQL:是SQL语言的一种版本，并且只能在S

36、QLSERVER上使用。TSQL是SQLSERVER功能的核心。不管应用程序的用户界面是什么形式，只要和数据库服务器连接最终都必然体现为Transact-SQL.第二节检索数据是数据库最频繁执行的活动。在SQL中，使用SELECT语句可以在需要的表单中检索数据。格式:SelectAll|Distinct字段表列from表名Where查询条件Groupby字段表列(分组)Having分组条件(用于已分组的结果)Orderby字段表列Asc(升序)|Desc(降序)实例:UsestudentgoCreatetablestudent(stunointprimarykey,stunamevarchar

37、(10)notnull,Classint,sexchar(2)check(sexin(男,女)插入值:insertintostudentvalues(1,tom,5,男)insertintostudentvalues(2,rose,6,女)insertintostudentvalues(3,smith,6,男)insertintostudentvalues(4,mary,5,女)1、基本语句检索数据全表查询:Select*fromstudent选择字段查询:Selectstuno,stunamefromstudent排列数据:Selectstuname,classfromstudentorde

38、rbystunodesc消除重复项:Selectclassfromstudent|Selectdistinctclassfromstudent约束结果:Selectstunamefromstudentwherestuno=12、选择语句检索数据(1)比较运算符:大于=大于等于或！=不等于实例:Selectstunamefromstudentwhereclass2between.and.或notbetween.and.运算符：between后是数值的下限，and后是数值的上限。between.and.包括上下限。notbetween.and.不包括上下限。实例:Select*fromstuden

39、twherestunobetween2and4In运算符:查找属性值属于指定集合的元组。实例：Select*fromstudentwherestunoin(1,3,4)4)like运算符:属于字符串匹配条件查询。有两个通配符:a)_(下划线):代表任意单个字符。实例:Select*fromstudentwherestunameliket_mb)%(百分号):代表任意长度的字符串。实例:Select*fromstudentwherestunamelike%m5)isnotnull运算符(未知值):实例:Select*fromstudentwhereclassisnotnull(6)and或者or运算符:多重条件查询。实例:Select*fromstudentwherestuno=1orstuno=2orstuno=3Select*fromstudentwherestuno=1andstuname=tom第三节运算符1、算术运算符运算符+含义（加）加法（减）减法*（乘）乘法/%（除）除法（模）返回一个除法的整数余数2、比较运算