数据库系统概论02

1、sql server主要内容主要内容lsql server数据库创建和维护lt-sql语言l视图、存储过程和触发器l数据库管理和保护 microsoft sql server 2000 microsoft sql server 2000（简称（简称sql serversql server）是）是microsoftmicrosoft公司在公司在windowswindows平台上开发的一个基于客户机平台上开发的一个基于客户机/ /服服务器务器(c/s) (c/s) 模式的关系数据库管理系统。模式的关系数据库管理系统。网络客户机客户机sqlserver数据库服务器客户机应用程序负责商业逻辑和向用户提

2、供数据。服务器对数据库的数据进行操作和管理。sql server主要管理工具主要管理工具 sql server提供了一整套数据库系统管理工具和实用提供了一整套数据库系统管理工具和实用程序，程序，打开打开“开始开始/ /程序程序microsoft sql server”microsoft sql server”菜单菜单即可看到如下所示的即可看到如下所示的sql serversql server主要工具主要工具：1服务管理器（服务管理器（sql server services manager） sql server服务管理器的作用:启动、停止或暂停sql server服务器所支持的服务。服务有服务

3、有3种状态：种状态：停止（红色）：该服务没有启动，所有与sql server连接的客户端全部断开。启动（绿色）：服务正常运行，相关管理和服务功能可用。暂停（黄色）：暂停状态时，已连接的客户端任务可以完成，不允许新的连接。选择该复选框可以在启动操作系统时自动启动所选择的sql server服务。表示服务管理器处于启动状态表1 sql server提供的主要服务服务名称主要作用支持主要服务和管理sql server（sql server数据库引擎服务）是sql server的核心服务组件，是实际的数据库服务器，该服务启动后，客户端才可以连接服务器上。一般的数据库功能都由它提供数据管理（创建、维护数

4、据库等）、t-sql处理、并发控制、数据完整性控制等sql server agent（sql server代理服务）作业调度和管理，可支持数据库定时、自动备份、维护，监视数据库、异常告警等功能，必需和sql server服务一起使用作业、报警、操作员等distributed transaction coordinator（分布式事务处理协调器服务dtc）管理分布式事务，它允许客户端应用程序在一个事务中包含多个不同的数据源分布式事务处理协调器2企业管理器（企业管理器（enterprise manager） sql server企业管理器是可视化的数据库管理工具，使用企业管理器可以对sql ser

5、ver服务器、数据库及数据库对象进行各种有效的管理，执行所有的sql server管理任务。菜单栏 对象浏览器:进行数据库维护和操作sql语句编辑器 执行结果显示窗口 工具条 3查询分析器（查询分析器（sql query analyzer） 4sql server联机丛书联机丛书数据库中的对象数据库中的对象 表（table）索引（index）默认（default）约束（constraint）视图（view）存储过程（stored procedure）触发器（trigger）用户（user）、角色（role）用户定义的数据类型、用户定义的函数sql server有两类数据库：(1)系统数据库:存

6、放sql server工作时所需要的系统级信息。数据库数据库主要作用主要作用master从整体上控制从整体上控制sql server系统和用户数据库的运行。保存登录标识、系统配置系统和用户数据库的运行。保存登录标识、系统配置、用户数据库基本信息等。该库非常重要，应设置权限禁止一般用户访问，另、用户数据库基本信息等。该库非常重要，应设置权限禁止一般用户访问，另外要及时备份外要及时备份model是新建数据库的模板，包含了每个用户数据库都需要的一些系统表，是新建数据库的模板，包含了每个用户数据库都需要的一些系统表，sql server以它为基础创建新的数据库以它为基础创建新的数据库msdb支持支持s

7、ql server agent服务，它记录有关作业、警报、操作员等信息服务，它记录有关作业、警报、操作员等信息tempdb是个临时数据库，它为是个临时数据库，它为sql server的查询、存储过程等的执行提供临时存储的查询、存储过程等的执行提供临时存储(2)用户自定义数据库:用户根据数据管理的需要建立的数据库，由用户自行创建和维护。 sql server提供两个样本数据库 ：pubs（图书出版的数据库）northwind（有关贸易公司的数据库）创建数据库创建数据库创建数据库创建数据库l 创建和打开数据库l 修改数据库l 删除数据库创建和打开数据库创建和打开数据库 l数据库名 l数据库名称在服

8、务器中必须惟一l符合标识符的规则l数据库名称最多可以包含128个字符 创建和打开数据库创建和打开数据库数据库文件数据库文件数据文件数据文件日志文件日志文件（.ldf）主数据文件主数据文件（.mdf）次数据文件次数据文件（.ndf）一个一个多个多个所有文件都具有所有文件都具有逻辑文件名逻辑文件名与与物理文件名物理文件名创建和打开数据库创建和打开数据库l文件组是用户对数据库文件（不包括事务日志文件）进行分组而形成的。l每个数据库中至少包含一个文件组（主文件组）l使用文件和文件组时要遵循以下规则 l主数据文件必须属于主文件组l任何未指定文件组中的文件都放在主文件组。l所有系统表都包含在主文件组中。l

9、一个文件只能属于一个文件组l一个文件或文件组只能属于一个数据库l日志文件不能成为任何文件组的成员使用使用create database命令建立数据库命令建立数据库 create database 数据库名on primary(name=, filename=, size=, maxsize=, filegrowth=)log on(name=, filename=, size=, maxsize=, filegrowth=)filegroup 文件组名 使用使用use命令可以打开命令可以打开已创建的数据库已创建的数据库 l打开已创建的数据库命令格式为 luse 数据库名 例例1 创建期刊采编系

10、统创建期刊采编系统数据库数据库magdb l磁盘的文件夹要预先建立例例2 创建未指定数据文件和日志创建未指定数据文件和日志文件的数据库文件的数据库usedb1 例例3 创建数据库创建数据库usedb，其主数据文件为，其主数据文件为use1，次数，次数据文件为据文件为use2和和use3；日志文件包括；日志文件包括uselog1和和uselog2 例例4 创建包含多个文件组的数据创建包含多个文件组的数据库库usedb2 修改数据库修改数据库 l增加或删除数据文件和日志文件l缩小分配给数据文件或日志文件的空间l扩大分配给数据文件或日志文件的空间l创建文件组l改变默认文件组l改变数据库名使用使用al

11、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增加一个数据文件增加一

12、个数据文件add_use2并添加到新建的文件组并添加到新建的文件组usedbfg中中 例例8 更改数据库更改数据库usedb1中文件中文件add_use2的初始大小为的初始大小为10m 缩小数据库或数据文件缩小数据库或数据文件 l缩小数据库大小 ldbcc shrinkdatabase（数据库名，参数）l 参数数据库缩小到原来大小的百分比l缩小数据文件大小 ldbcc shrinkfile（文件名，参数）l参数数据文件缩小后的绝对数值，默认单位mb例9 将数据库将数据库usedb2的大小的大小缩小到缩小到60% 例例10 将数据库将数据库usedb2中的文件中的文件userlog2的大小缩小到

13、的大小缩小到10m 删除数据库删除数据库 l删除数据库的指令格式为：ldrop database 数据库名 例例11 将数据库将数据库usedb删除删除 创建和维护表创建和维护表创建和维护表创建和维护表l创建数据库表l修改数据库表l使用约束 l建立索引l删除数据库表创建数据表的一般过程：创建数据表的一般过程：设计表结构 定义字段 输入字段 定义字段类型 定义字段属性 编辑字段 保存表 输入数据创建数据库表的指令格式创建数据库表的指令格式 create table 表名（列名1 数据类型 identity |not null| null , 列名2 数据类型 identity |not null

14、| null , ）lnull：可以无值，默认值lnot null：必须输入值lidentityl计数器，递增的整数，默认：初值1，步长1；用户可自定义，如identity (-20,4)l规则l每个数据表只能有一个具有identity属性的列；l该列的数据类型使用整型或精确数型；l该列的数据值自动拥有，用户不能修改该列的数据值。l例：create table demotable(user_id numeric(10,0) identity, name varchar(30) not null, comments varchar(100) null) 表名表名 l表名是惟一的 l可以使用中文，

15、也可以使用西文符号l最多64个字符 l一般表都是永久表l临时表 l临时表的表名必须以#打头 l仍然使用create table命令l例:create table #demotable (user_id numeric(10,0) identity, name varchar(30) not null) 列名列名 l表中的每一列也称为字段，所以列名也叫字段名；l字段名的命名方式基本同数据表名的命名；l字段名在数据表中要求是惟一的，但在整个数据库中可以重名 。数据类型数据类型 lt-sql中支持的、常用的数据类型 l字符型 l日期时间类型 l数值类型 l二进制型 l位型 l文本型 l图像型 l时间

16、戳型 字符型字符型 l字符型变量应定义长度l允许定义的最大长度是255 l有两种l固定长度的char( )类型，空格补位l可变长度的varchar( )类型 l输入字符型数据时必须用单引号将数据括起来 日期时间类型日期时间类型 ldatetime和smalldatetime是用来存储日期和时间数据 l日期时间类型数据必须用单引号括起来比较内容 datetime smalldatetime 最小值 jan 1,1753 jan 1,1900 最大值 dec 31,9999 jun 6,2079 占用存储空间 8byte 4byte 精度 3.33毫秒 1分钟 整数类型整数类型 l整数类型的比较

17、比较内容 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位精确数类型精确数类型 l精确数类型有两种形式ldecimallnumeric l与浮点类型数据的区别 l可以自定义精度的位数（最多28位）l例 num_col(5,2)货币型货币型 l货币类型的比较 比较内容moneysmallmoney最

18、小值-922,337,203,685,477.5808-214,748.3648最大值922,337,203,685,477.5807214,748.3647占用存储空间8byte4byte精度小数点后4位小数点后4位n使用sql语句输入货币类型数据可以在数据前加上货币符号（如￥、$等）位数据类型位数据类型 l位数据类型bit是一种逻辑数据类型 l只有1和0两种数值l一般常用作true/false使用 l定义bit列时，不允许为null，也不能建立索引 l多个bit列可占用一个字节文本型和图像型文本型和图像型 l文本类型text l图像类型image l每行可以存储2gb的二进制大型对象 时间

19、戳型时间戳型 l时间戳型表示timestampl由系统自动赋值的一个计数器数据 l记录了数据行的操作顺序 l时间戳列的数据保持惟一，它可以惟一标识表中的列 l时间戳列的值实际来自于事务日志 例例3 建立期刊采编系统建立期刊采编系统数据库中各数据表数据库中各数据表 l创建mag_dept表 创建创建mag_emp表表 创建创建mag_info表表 创建创建mag_doc表表 修改数据库表修改数据库表 l添加或删除列l修改列的属性 l更改列名和表名 添加或删除列添加或删除列 l增加列的语句格式 alter table 表名 add 列名 数据类型 identity |not null| null

20、，l删除列的语句格式 alter table 表名 drop column 列名 例例12 向数据表向数据表mag_emp中添加一新属性中添加一新属性id（身（身份证号），数据类型为字符型，固定长度份证号），数据类型为字符型，固定长度18 修改列的属性修改列的属性 l命令格式 alter table 表名 alter column 列名 数据类型 identity |not null| null 例例13 将数据表将数据表mag_info中中pubdate的的数据类型更改为数据类型更改为smalldatetime 更改列名和表名更改列名和表名 l使用sql server内部存储过程l更改列名

21、sp_rename 表名.原列名, 新列名 l更改表名sp_rename 原表名 新表名 例例14 数据库数据库usedb1中建有数据表中建有数据表test，包含，包含number、stu_name和和class三个字段，请将三个字段，请将class更名为更名为class_no 例例15 将数据库将数据库usedb1中数中数据表据表test更名为更名为student 使用约束使用约束 l约束的作用l实现数据的完整性l实体完整性l参照完整性l用户自定义完整性l约束的类型 l建立约束 l删除约束 参照完整性参照完整性学号学号姓名姓名s256s257s258王丹王丹章华章华李力李力性别性别系号系号男

22、男女女男男d23d25d30系号系号d23d25d30系名系名负责人负责人物理物理机械机械计算机计算机王娟王娟杨华杨华张天张天关系关系s关系关系dept约束的类型约束的类型 名称作用实现的完整性primary key定义主键,保证主键列无重复值实体完整性unique保证该列无重复值实体完整性foreign key定义外键,保证数据表间数据的一致性参照完整性check定义表中某些列的数据范围自定义完整性default为列的数据提供默认值自定义完整性建立约束建立约束 l定义约束时使用create table语句l或使用alter table语句l即可以在定义数据表的数据列时直接定义约束，也可以对已

23、定义的数据表添加约束 使用使用alter table语句添加约束的基本格式语句添加约束的基本格式 alter table 表名 add constraint 约束名 约束定义使用使用create table语句建立约束语句建立约束 create table 表名 （列名1 数据类型 identity |not null| null constraint 约束名 约束定义,列名2 数据类型 identity |not null| null constraint 约束名 约束定义, constraint 约束名 约束定义） primary keyl定义主键约束的基本格式为： constraint 约束名 primary key (列名1，列名2，)例例16 向数据表向数据表map_dept中中添加主键约束添加主键约束 例例17在数据库在数据库usedb2中建立数据表中建立数据表map_dept，同时定义主键约束，同时定义主键约束 例例18 在数据库在数据库usedb1中建立数据表中建立数据表grade，表中包括学号，表中包括学号sno（char）、课程号）、课程号cno（char）、成绩）