系统数据库功能简介.DOC

1、- 1 -第一部分 系统数据库功能简介 一、 Master 数据库：数据库记录了一个 SQL Server 系统的所有系统信息，这些信息主要有：A、 所有的登录信息B、 系统设置信息C、 SQL Server 初始化信息D、 系统中其它系统数据库和用户数据库的相关信息，包括其主文件的存放位置等；二、 Model 数据库：是所有用户数据库和 Tempdb 数据库的创建模板，当创建数据库时系统会将 Model 数据库中的内容复制到新建的数据库中去。A、 数据库的最小容量B、 数据库选项设置C、 经常使用的数据库对象，如用户自定义的数据类型函数规则缺省值等；三、 Msdb 数据库：存储计划信息与备份

2、和恢复相关的信息。四、 Tempdb 数据库：临时的表和存贮过程，在此数据库上数据操作比别的数据库要快五、 两例示例数据库 pubs 和 northwind 为学习工具，帮助的代码都可运行。第二部分 创建和管理数据库一、创建数据库:1、 格式:Create database 数据库名On Primary（Name=逻辑文件名，Filename=物理文件名，Size=文件起始大小， Maxsize=文件最大容量，Filegrowth=文件增量），Filegroup 文件组名（Name=逻辑文件名，Filename=物理文件名，Size=文件起始大小，Maxsize=文件最大容量，Filegrow

3、th=文件增长容量）Log on（Name=逻辑文件名，Filename=物理文件名，Size=文件起始大小，Maxsize=文件最大容量，Filegrowth=文件增长容量）- 2 -说明：（1）Primary:指定下面文件为主文件组的文件。可省略。（2）Filename:指定文件的实际存储位置。（3）Size:指定文件的起始大小（512K/1M）。（4）Maxsize:指定文件可达到的最大容量。（5）Filegrowth:定义的文件的增量。文件的增量设置不能超过Maxsize设置。可以指定一个确切的增长数值，也可以指定增长的百分比（起始值的百分比），默认为10%。（10%/1M）（6）Lo

4、g on :指定下面为日志文件。(7)主要数据文件扩展名*.Mdf，次要数据文件扩展名*.ndf，日志文件扩展名*.ldf2、 实例:创建带有多个文件组的数据库Create database bookOn Primary( Name=book_mdf,Filename=d:booksbook_mdf.mdf,Size=3,Maxsize=30,Filegrowth=3 ),Filegroup group1( Name=book_ndf1,Filename=d:booksbook_ndf1.ndf,Size=2MB,Maxsize=20MB,Filegrowth=25% ),Filegroup

5、group2( Name=book_ndf2,Filename=d:booksbook_ndf2.ndf,Size=4,Maxsize=30,Filegrowth=4 )Log on( Name=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% )二、修改和删除数据库:- 3 -1、修改数据库:(1)添加数据文件:a) 格式:Alter d

6、atabase 数据库名 Add File（ Name=逻辑文件名，Filename=物理文件名，Size=文件起始大小，Maxsize=文件最大容量，Filegrowth=文件增量 ）To FileGroup 文件组名b) 说明: To FileGroup :指定添加的数据文件到哪个文件组中，该文件组必须存在，默认为主文件组。c) 实例:Alter database student add file ( Name=student_ndf,Filename=d:sqlstudent_ndf.ndf,Size=1,Maxsize=5,Filegrowth=1 )(2)添加日志文件:a) 格式:

7、Alter database 数据库名 Add Log File（ Name=逻辑文件名，Filename=物理文件名，Size=文件起始大小，Maxsize=文件最大容量，Filegrowth=文件增量 ）b) 实例: Alter database student Add Log File ( Name=student_ldf1,Filename=d:sqlstudent_ldf1.ldf,Size=4,Maxsize=40,Filegrowth=15% )（3）添加文件组:a) 格式:Alter database 数据库名 Add Filegroup 文件组名。b) 实例:Alter da

8、tabase student Add Filegroup group1向该文件组中添加文件Alter database student Add File( Name=student_ndf2,Filename=d:sqlstudent_ndf2.ndf,Size=3,Maxsize=30,- 4 -Filegrowth=3 )To Filegroup group1(4)修改文件(数据文件和日志文件):a) 格式:Alter database 数据库名 Modify File ( Name=逻辑文件名，Size=新的文件大小,Maxsize=将要达到的容量,Filegrowth=修改后的增量 ）

9、b) 说明:修改数据文件和日志文件的格式是相同的,但逻辑文件名和物理文件名不能修改。在修改文件时，必须指定文件的逻辑名，用来标识将要修改的文件。而不必指定文件的物理名，否则将出现错误。如果指定修改文件的Size,则新的文件大小必须比当前文件大小要大。而修改文件的Maxsize和Filegrowth时，数值可以增大，也可以和原来的相同，也可以减小。c) 实例: Alter database student Modify File ( Name=student_ldf1,Filegrowth=20% )（5）删除文件:a) 格式:Alter database 数据库名 Remove File 文件

10、名b) 说明:不能删除主要数据文件和主要日志文件。c) 实例: Alter database student Remove File student_ldf1(6)删除文件组:a) 格式:Alter database 数据库名 Remove Filegroup 文件组名b) 说明:要删除的文件组中不能包含数据文件。c) 实例: Alter database student Remove File student_ndf2Alter database student Remove Filegroup group12、 删除数据库:（1） 格式:Drop database 数据库名（2） 实例:D

11、rop database student三、设置数据库选项:1、格式:SP_dboption 数据库名 ，选项名 ，ture|false2、 说明:SP_dboption:系统的存储过程（system procedure），可以通过它设置数据库选项，它支持对所有 可用的数据库选项进行设置。a) 如果只设置SP_dboption 数据库名，那么将显示在本数据库中设置为True的选项。b) 如果设置SP_dboption 数据库名，选项名，那么将显示指定的选项当前处于什么状态（ON或者OFF）3、 实例:将student数据库的read only选项设置为True,即打开此选项。常用三项(read

12、 only|single user|dbo use only) SP_dboption student, read only,true四、压缩数据库:有时，人们可能为预期有一定程度活动的数据库分配了太多的空间，当意识到分配了太多空间时，可能决定压缩分配的空间大小。SQLSERVER提供三种可以压缩数据库- 5 -大小的方法，autoshrink数据库选项，“企业管理器”和“数据库一致性检查器（DBCC）”命令。1、 使用DBCC Shrinkdatabase压缩数据库:DBCC Shrinkdatabase 命令在默认情况下会对数据库的数据和日志部分都进行压缩。如果您只想减少数据和日志部分，则

13、您必须首先压缩整个数据库，然后使用Alter database 语句来增加数据库的数据或日志部分。a)、格式:DBCC Shrinkdatabase （数据库名，压缩后可使用的百分比，NOTRUNCATE|TRUNCATEONLY）b)、说明: NOTRUNCATE:不会将可用的空间释放给操作系统，而是留给数据库 文件。TRUNCATEONLY:将数据文件未用的空间释放给操作系统。c)、实例: DBCC Shrinkdatabase（student,20，TRUNCATEONLY）2、 使用“企业管理器”压缩数据库:有两种方式:a) 在企业管理器中，选中要压缩的数据库，点击鼠标右键，点击属性，

14、从弹出的“数据库属性”的对话框中选中“选项”标签，在复选框中选中“自动压缩”选项即可。b) 在企业管理器中，选中要压缩的数据库，点击鼠标右键，选中“所有任务”，点击“收缩数据库”,填充相应的选项即可。第三部分 存储数据一、数据类型:指定列、数据存储参数和局部变量的数据特性。数据按照数据类型存储在列中。数据类型可以分为两大类:1、 系统数据类型:是 SQLSERVER 支持的内置数据类型。1) 字符型:char、nchar、varchar、nvarchar、text、ntext 。a) char:固定长度的非 Unicode 字符数据，最大的长度为 8000 字符。b) nchar: 固定长度的

15、 Unicode 数据，最大的长度为 4000 字符。c) varchar:可变长度的非 Unicode 数据，最大的长度为 8000 字符。d) nvarchar: 可变长度的 Unicode 数据，最大的长度为 4000 字符。e) text: 可变长度的非 Unicode 数据，最大的长度为 231-1 个字符。f) ntext: 可变长度的 Unicode 数据，最大的长度为 230-1 个字符对于定义为 char 或 nchar 的列，SQLSERVER 将用字符串来填满指定的字节数。定义为 varchar 或 nvarchar 的列只存储输入的实际长度，可能舍去尾部空间。SQLSE

16、RVER 处理尾部空间，取决于 SET ANSI_PADDING,以及该列是固定长度还是可变长度。- 6 -根据 SET ANSI_PADDING 值处理尾部空间ANSI_PADDING char 和 nchar Varchar 和 nvarcharON 串被空格填充到列的长度 串未被空格填充到列的长度，尾部空间被保留OFF 串被空格填充到列的长度 串未被空格填充到列的长度，尾部空间被截掉2）日期和时间型:smalldatetime 、datetime。a) smalldatetime:从 1900 年 1 月 1 日到 2079 年 6 月 6 日，精确到 1 分钟。b) datetime:

17、从 1753 年 1 月 1 日到 9999 年 12 月 31 日，精确到三百分之 一秒，即 3.33 毫秒。3）数值型数据类型: A、整型:smallint、int、tinyint。a) smallint:从-215 到 215-1。b) int:从-231 到 231-1。c) tinyint:从 0 到 255。B、近似数字数据类型 :float 和 real。a) float:浮点精度数字数据，从-1.79E+308 到 1.79E+308。b) real: :浮点精度数字数据，从-3.40E+308 到 3.40E+308。C、精确数字数据类型 : decimal 和 numeri

18、c。a) decimal:不带符号的整数，按 10 进位。b) numeric:decimal（十近制）的同义词，可带小数（numeric(3,2)共有三位数，其中有两位是小数，e.g. 1.23；有最大值问题 0-9 正确，10 报错）D、货币数据类型 :money 和 smallmoney。a) money:从-263 到 263-1,精确到每个货币单位的万分之一。b) smallmoney:从-214，748.3648 到+214，748.3647，精确到每个货币单位的万分之一。4）逻辑数据类型:bit。Bit:整形数据，值为 1 或 0。2、 用户自定义类型: 用户可以通过两个系统存储

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

20、eate table stu1(sid int, sbirthday birthday)删除自定义的数据类型:- 7 -（1） 、格式：Exec sp_droptype 自定义的数据类型。（2） 、实例:Exec sp_droptype birthday。注意:我们还可以通过企业管理器创建自定义的数据类型。首先，选中一个数据库，点击右键，选中“新建” ，从弹开的菜单中选中“用户定义的数据类型” ，在打开的窗体中填充相应的选项即可。二、表操作:1、创建表:(1)、格式:Create table 表名 （ 字段名 1 数据类型,字段名 n 数据类型）（2） 、说明: 表名的定名原则要遵循标识符的定

21、名原则。字段和字段之间用逗号隔开。（3） 、实例:Use bookgoCreate table book(bookid int ,bookname varchar(10)2、修改表:(1)、增加字段:a)、格式:Alter table 表名 Add 字段名 字段类型b)、实例:Alter table book add providerid varchar(10),address varchar(20)(2)、修改字段:a) 格式:Alter table 表名 Alter column 字段名 字段类型b) 实例: Alter table book Alter column address va

22、rchar(10)(3)、删除字段:a) 格式:Alter table 表名 Drop column 字段名b) 实例: Alter table book drop column address3、删除表:(1)、格式:Drop table 表名(2)、说明:把不再起作用的表从数据库中删除。（3） 、实例:Drop table book三、数据完整性: 是通过实现过程数据完整性和声明数据完整性来强制执行的。1、 Identity : identity 属性可以生成唯一标识表中每一行的连续值。1）格式: Identity （初始值 ，增量）2）实例:Create table tab1(id in

23、t identity(1,1),name varchar(10)3）说明:一个表中只能有一个 identity 标识的列，字段必须是整型。不能更新定义有 identity 属性的列。不能向定义有 identity 属性的列赋 null 值或附加默认约束。2、Uniqueidentifier: 使用 Uniqueidentifer 和 Newid 函数也可以生成列的唯一值，与 Identity 属性类似。如果创建的列是 uniqueidentifer 数据类型，则必须使用 newid 函数为它生成新值。实例:Create table tab2 (id uniqueidentifier,name

24、varchar(10)插入值:insert into tab2 values(newid(),mary)create table s(stuid int identity(2,2),age uniqueidentifier)- 8 -insert into tab2 values(mary, newid()3、使用约束:(1)主键（primary key ）:唯一标识每一行。1) 说明: 一个表中只可以定义一个主键。 不能在主键列中输入 null 值。 最多可定义 16 列作为主键。 定义之后，则不能禁用 primary key 约束.2） 实例:A、创建单一主键:Create table t

25、ab3(id int primary key, bid int) B、创建复合主键:Create table tab4(id int,bid int,constraint pk_id_bid primary key(id,bid)(2)外键（foreign key）: Foreign key 定义列值与另一个表的 Primary key 相匹配的列。1）说明: Foreign key 约束必须引用另一个表的 Primary key 或 Unique 约束。 相关表中定义为主键的所有列必须作为 Foreign key 所包含在当前表中。 如果使用 With nocheck 选项，将不会验证表中的

26、现有数据。2）实例: Create table tab5(cid int primary key not null,id int,foreign key(id) references tab3(id)（3）CHECK 约束:根据指定值测试列中的输入值。每次再列中插入或更新数据时均要进行这一测试。 1）说明: 可以为 Check 约束定义 Where 字句中的类似条件，但它们不能包含子查询。 Check 约束条件可以引用同一个表中的列。 Check 约束条件必须对布尔表达式求值。 可以绑定有规则的列定义 Check 约束 2) 实例: create table tab6(id int, sex

27、char(2) constraint chk_tab6_sex check (sex in (m,w) ) )（4）默认约束（default） :此约束用于在用户未提供列值的情况下，提供一个自动添加的列值。1）说明: 一个表中只能有一列定义有 Default 约束。 不能在数据类型为 Timestamp 的列或具有 Identity 属性的列中定义 Default 约束。2）实例:Create table tab7(id int, address varchar(20) constraint Def_tab7_add - 9 -default 吉林省长春市)或者 Alter table tab

28、7 add constraint def_tab7_id default 100 for id(5)唯一性约束(Unique): 在列中应用 unique 约束以确保列中不输入重复值。列中所有行的值均不相同。1）说明: 可以向表中的多列应用 unique 约束。 向现有表应用 unique 约束时，一直会验证现有数据。 可以向 not null 列应用 unique 约束，但仅有其中一行能包含null 值（候选建） 。2）实例: Create table tab8(id int unique)或者Create table tab9(id int) Alter table tab9 add co

29、nstraint unq_id unique (id)（6）删除约束:Alter table 表名 drop constraint 约束名实例:Alter table tab9 drop constraint unq_id第四部分 处理数据第一节 TRANSACTSQL 语言一、Transact-SQL 语言介绍:A、 SQL（structured query language ）语言是一种结构化的查询语言。它的功能包括查询、操作、定义和控制四个方面。它是一种综合的、通用的、功能强大的关系数据库语言；B、 Transact-SQL:是 SQL 语言的一种版本，并且只能在 SQLSERVER 上

30、使用。TSQL是 SQLSERVER 功能的核心。不管应用程序的用户界面是什么形式，只要和数据库服务器连接最终都必然体现为 Transact-SQL.二、插入数据:可以使用不同的 INSERT 语句向表或视图添加全部列数据或只带一部分列数据的行。1、插入单行:语法:Insert into 表名 （字段清单） values （列值）实例:insert into student values（4,mike,5,男 ）2、 插入多行:语法:Insert into 表名 select 语句实例:Create table stu（sno varchar（10） ，sname varchar（10） ，c

31、lass int,sex char （2） ） - 10 -Insert into stu select * from student三、修改数据:可以对以前添加到表中的数据进行修改。 UPDATE 语句用来更改现有行中的数据，可以是添加新数据，也可以是修改现有的数据。语法:Update 表名 set 字段 1=列值 1，字段 2=列值 2，字段 n=列值 n where 条件实例: insert into student values(5,jack,6,男)select * from student;update student set class=5 where stuno=5select

32、 * from student;四、删除表中的数据:可以使用 DELETE 语句删除不在起作用的数据。语法:Delete from 表名 where 条件实例:Delete from student where stuno=5五、检索数据:是数据库最频繁执行的活动。在 SQL 中，使用 SELECT 语句可以在需要的表单中检索数据。格式:Select All | Distinct 字段表列 from 表名Where 查询条件Group by 字段表列（分组）Having 分组条件（用于已分组的结果）Order by 字段表列 Asc（升序）| Desc（降序）实例: Use student g

33、oCreate table student (stuno int primary key ,stuname varchar(10) not null,Class int , sex char(2) check (sex in (男,女)插入值:insert into student values(1,tom,5,男)insert into student values(2,rose,6,女)insert into student values(3,smith,6,男)insert into student values(4,mary,5,女)1、基本语句检索数据: 全表查询:Select * from student 选择字段查询:Select stuno ,stuname from student 排列数据:Select stuname,class from student order by stuno desc 消除重复项:Select class from student | Select distinct class from student 约束结果:Select stuname from student where stuno=12、选择语句检索数据:（1） 比较运算符: 大于= 大于等于2（2） betweenand 或 not betweenand 运算符: