数据库chapSQLServer创建和维护

1、SQL Server主要内容SQL Server数据库创建和维护T-SQL语言视图、存储过程和触发器数据库管理和保护 Microsoft SQL Server 2000（简称SQL Server）是Microsoft公司在Windows平台上开发的一个基于客户机/服务器(C/S) 模式的关系数据库管理系统。网络客户机客户机SQLServer数据库服务器客户机应用程序负责商业逻辑和向用户提供数据。服务器对数据库的数据进行操作和管理。SQL Server主要管理工具 SQL Server提供了一整套数据库系统管理工具和实用程序，打开“开始/程序Microsoft SQL Server”菜单即可看到

2、如下所示的SQL Server主要工具：1服务管理器（SQL Server Services Manager） SQL Server服务管理器的作用:启动、停止或暂停SQL Server服务器所支持的服务。服务有3种状态：停止（红色）：该服务没有启动，所有与SQL Server连接的客户端全部断开。启动（绿色）：服务正常运行，相关管理和服务功能可用。暂停（黄色）：暂停状态时，已连接的客户端任务可以完成，不允许新的连接。选择该复选框可以在启动操作系统时自动启动所选择的SQL Server服务。表示服务管理器处于启动状态表1 SQL Server提供的主要服务服务名称主要作用支持主要服务和管理SQ

3、L Server（SQL Server数据库引擎服务）是SQL Server的核心服务组件，是实际的数据库服务器，该服务启动后，客户端才可以连接服务器上。一般的数据库功能都由它提供数据管理（创建、维护数据库等）、T-SQL处理、并发控制、数据完整性控制等SQL Server Agent（SQL Server代理服务）作业调度和管理，可支持数据库定时、自动备份、维护，监视数据库、异常告警等功能，必需和SQL Server服务一起使用作业、报警、操作员等Distributed Transaction Coordinator（分布式事务处理协调器服务DTC）管理分布式事务，它允许客户端应用程序在一个

4、事务中包含多个不同的数据源分布式事务处理协调器2企业管理器（Enterprise Manager） SQL Server企业管理器是可视化的数据库管理工具，使用企业管理器可以对SQL Server服务器、数据库及数据库对象进行各种有效的管理，执行所有的SQL Server管理任务。菜单栏 对象浏览器:进行数据库维护和操作SQL语句编辑器 执行结果显示窗口 工具条 3查询分析器（SQL Query Analyzer） 4SQL Server联机丛书数据库中的对象 表（Table）索引（Index）默认（Default）约束（Constraint）视图（View）存储过程（Stored Proce

5、dure）触发器（Trigger）用户（User）、角色（Role）用户定义的数据类型、用户定义的函数SQL Server有两类数据库：(1)系统数据库:存放SQL Server工作时所需要的系统级信息。数据库主要作用master从整体上控制SQL Server系统和用户数据库的运行。保存登录标识、系统配置、用户数据库基本信息等。该库非常重要，应设置权限禁止一般用户访问，另外要及时备份model是新建数据库的模板，包含了每个用户数据库都需要的一些系统表，SQL Server以它为基础创建新的数据库msdb支持SQL Server Agent服务，它记录有关作业、警报、操作员等信息tempdb是

6、个临时数据库，它为SQL Server的查询、存储过程等的执行提供临时存储(2)用户自定义数据库:用户根据数据管理的需要建立的数据库，由用户自行创建和维护。 SQL Server提供两个样本数据库 ：pubs（图书出版的数据库）Northwind（有关贸易公司的数据库）创建数据库创建数据库 创建和打开数据库 修改数据库 删除数据库创建和打开数据库 数据库名 数据库名称在服务器中必须惟一符合标识符的规则数据库名称最多可以包含128个字符 创建和打开数据库数据库文件数据文件日志文件（.ldf）主数据文件（.mdf）次数据文件（.ndf）一个多个所有文件都具有逻辑文件名与物理文件名创建和打开数据库文

7、件组是用户对数据库文件（不包括事务日志文件）进行分组而形成的。每个数据库中至少包含一个文件组（主文件组）使用文件和文件组时要遵循以下规则 主数据文件必须属于主文件组任何未指定文件组中的文件都放在主文件组。所有系统表都包含在主文件组中。一个文件只能属于一个文件组一个文件或文件组只能属于一个数据库日志文件不能成为任何文件组的成员使用CREATE DATABASE命令建立数据库 CREATE DATABASE 数据库名ON PRIMARY(NAME=, FILENAME=, SIZE=, MAXSIZE=, FILEGROWTH=)LOG ON(NAME=, FILENAME=, SIZE=, MA

8、XSIZE=, FILEGROWTH=)FILEGROUP 文件组名 使用USE命令可以打开已创建的数据库 打开已创建的数据库命令格式为 USE 数据库名 例1 创建期刊采编系统数据库MagDb 磁盘的文件夹要预先建立例2 创建未指定数据文件和日志文件的数据库usedb1 例3 创建数据库usedb，其主数据文件为use1，次数据文件为use2和use3；日志文件包括uselog1和uselog2 例4 创建包含多个文件组的数据库usedb2 修改数据库 增加或删除数据文件和日志文件缩小分配给数据文件或日志文件的空间扩大分配给数据文件或日志文件的空间创建文件组改变默认文件组改变数据库名使用AL

9、TER DATABASE命令改变数据库定义 ALTER DATABASE 数据库名ADD FILE (文件参数) TO FILEGROUP 文件组名REMOVE FILE (文件参数) MODIFY FILE (文件参数) ADD LOG FILE (文件参数) ADD FILEGROUP 文件组名REMOVE FILEGROUP 文件组名 例5 为数据库usedb1增加一个数据文件add_use1 例6 为数据库usedb1增加一个日志文件add_use1_log 例7 为数据库usedb1增加一个数据文件add_use2并添加到新建的文件组usedbFG中 例8 更改数据库usedb1中文

10、件add_use2的初始大小为10M 缩小数据库或数据文件 缩小数据库大小 DBCC SHRINKDATABASE（数据库名，参数） 参数数据库缩小到原来大小的百分比缩小数据文件大小 DBCC SHRINKFILE（文件名，参数）参数数据文件缩小后的绝对数值，默认单位MB例9 将数据库usedb2的大小缩小到60% 例10 将数据库usedb2中的文件userlog2的大小缩小到10M 删除数据库 删除数据库的指令格式为：DROP DATABASE 数据库名 例11 将数据库usedb删除 创建和维护表创建和维护表创建数据库表修改数据库表使用约束 建立索引删除数据库表创建数据表的一般过程：设计

11、表结构 定义字段 输入字段 定义字段类型 定义字段属性 编辑字段 保存表 输入数据创建数据库表的指令格式 CREATE TABLE 表名（列名1 数据类型 identity |NOT NULL| NULL , 列名2 数据类型 identity |NOT NULL| NULL , ）NULL：可以无值，默认值NOT NULL：必须输入值identity计数器，递增的整数，默认：初值1，步长1；用户可自定义，如identity (-20,4)规则每个数据表只能有一个具有identity属性的列；该列的数据类型使用整型或精确数型；该列的数据值自动拥有，用户不能修改该列的数据值。例：Create t

12、able demotable(user_id numeric(10,0) identity, Name varchar(30) not NULL, Comments varchar(100) null) 表名 表名是惟一的 可以使用中文，也可以使用西文符号最多64个字符 一般表都是永久表临时表 临时表的表名必须以#打头 仍然使用CREATE TABLE命令例:Create table #demotable (user_id numeric(10,0) identity, Name varchar(30) not NULL) 列名 表中的每一列也称为字段，所以列名也叫字段名；字段名的命名方式基本

13、同数据表名的命名；字段名在数据表中要求是惟一的，但在整个数据库中可以重名 。数据类型 T-SQL中支持的、常用的数据类型 字符型 日期时间类型 数值类型 二进制型 位型 文本型 图像型 时间戳型 字符型 字符型变量应定义长度允许定义的最大长度是255 有两种固定长度的char( )类型，空格补位可变长度的varchar( )类型 输入字符型数据时必须用单引号将数据括起来 日期时间类型 datetime和smalldatetime是用来存储日期和时间数据 日期时间类型数据必须用单引号括起来比较内容 Datetime Smalldatetime 最小值 Jan 1,1753 Jan 1,1900

14、最大值 Dec 31,9999 Jun 6,2079 占用存储空间 8byte 4byte 精度 3.33毫秒 1分钟 整数类型 整数类型的比较 比较内容 Int Smallint Tinyint 最小值 -231 -215 0 最大值 231-1 215-1 255 占用存储空间 4byte 2byte 1byte 浮点数类型 比较内容FloatReal最小值-1.79E+308-3.40E+38最大值1.79E+3083.40E+38占用存储空间8Byte4Byte精度最多15位最多7位精确数类型 精确数类型有两种形式DecimalNumeric 与浮点类型数据的区别 可以自定义精度的位数

15、（最多28位）例 num_col(5,2)货币型 货币类型的比较 比较内容MoneySmallmoney最小值-922,337,203,685,477.5808-214,748.3648最大值922,337,203,685,477.5807214,748.3647占用存储空间8Byte4Byte精度小数点后4位小数点后4位使用SQL语句输入货币类型数据可以在数据前加上货币符号（如￥、$等）位数据类型 位数据类型bit是一种逻辑数据类型 只有1和0两种数值一般常用作true/false使用 定义bit列时，不允许为NULL，也不能建立索引 多个bit列可占用一个字节文本型和图像型 文本类型tex

16、t 图像类型image 每行可以存储2GB的二进制大型对象 时间戳型 时间戳型表示Timestamp由系统自动赋值的一个计数器数据 记录了数据行的操作顺序 时间戳列的数据保持惟一，它可以惟一标识表中的列 时间戳列的值实际来自于事务日志 例3 建立期刊采编系统数据库中各数据表 创建mag_dept表 创建mag_emp表 创建mag_info表 创建mag_doc表 修改数据库表 添加或删除列修改列的属性 更改列名和表名 添加或删除列 增加列的语句格式 Alter table 表名 add 列名 数据类型 identity |NOT NULL| NULL ，删除列的语句格式 Alter tabl

17、e 表名 drop column 列名 例12 向数据表mag_emp中添加一新属性ID（身份证号），数据类型为字符型，固定长度18 修改列的属性 命令格式 alter table 表名 alter column 列名 数据类型 identity |NOT NULL| NULL 例13 将数据表mag_info中PubDate的数据类型更改为smalldatetime 更改列名和表名 使用SQL Server内部存储过程更改列名 sp_rename 表名.原列名, 新列名 更改表名sp_rename 原表名 新表名 例14 数据库usedb1中建有数据表test，包含number、stu_na

18、me和class三个字段，请将class更名为class_no 例15 将数据库usedb1中数据表test更名为student 使用约束 约束的作用实现数据的完整性实体完整性参照完整性用户自定义完整性约束的类型 建立约束 删除约束 参照完整性学号姓名S256S257S258王丹章华李力性别系号男女男D23D25D30系号D23D25D30系名负责人物理机械计算机王娟杨华张天关系S关系DEPT约束的类型 名称作用实现的完整性Primary key定义主键,保证主键列无重复值实体完整性Unique保证该列无重复值实体完整性Foreign key定义外键,保证数据表间数据的一致性参照完整性Chec

19、k定义表中某些列的数据范围自定义完整性Default为列的数据提供默认值自定义完整性建立约束 定义约束时使用Create table语句或使用Alter table语句即可以在定义数据表的数据列时直接定义约束，也可以对已定义的数据表添加约束 使用Alter table语句添加约束的基本格式 Alter table 表名 Add Constraint 约束名 约束定义使用Create table语句建立约束 Create table 表名 （列名1 数据类型 identity |NOT NULL| NULL Constraint 约束名 约束定义,列名2 数据类型 identity |NOT NULL| NULL Constraint 约束名 约束定义, Constraint 约束名 约束定义） PRIMARY KEY定义主键约束的基本格式为： Constraint 约束名 primary key (列名1，列名2，)例16 向数据表map_dept中添加主键约束 例17在数据库usedb2中建立数据表map_dept，同时定义主键约束 例18 在数据库usedb1中建立数据表grade