数据库技术与应用第04章.ppt

第4章 数据库管理 u本章导读 本章主要介绍了有关数据表的操作，包括表的创建、修改、删 除和建立索引等操作以及表中数据维护的有关操作和方法，基本掌 握使用 SQL Server Management Studio和T-SQL语句对表的操作和 表的数据操作，包括创建表、删除表、对表中字段建立索引、向表 中增加数据、修改数据、删除数据等。要对SQL Server2005中的数 据类型有一个清楚的认识。 4.1数据库的创建与管理 4.1.1 SQL Server系统数据库 1系统数据库 1）Master数据库 2）Model数据库 3）Msdb数据库 4）Tempdb数据库 5）Resource数据库 6）Distribution数据库 2数据库快照 4.1数据库的创建与管理 4.1.2 数据库的创建 1准备创建数据库（学生选课） 1） 确定数据库的名称、所有者（创建数据库的用户） 数据库名称：studentcourse 2）确定存储该数据库的数据文件的大小及文件空间增长方式，确定 关系、索引，及系统存储参数的配置，确定数据库的存取方法。 主数据文件：逻辑名称studentcourse； 物理文件名：C:Datastudentcourse.mdf，初始大小：3MB， 最大空间：UNLIMITED，空间增加量：1MB，属于文件组primary。 次数据文件：逻辑名称secondsc； 物理文件名：C:mydbsecondsc.ndf，初始大小：3MB， 最大空间：50MB，空间增加量：1MB，属于文件组group1。 日志文件：逻辑名称studentcourse_log； 物理文件名：C:Logstudentcourse_log.ldf，始初大小：1MB， 最大空间：20MB，空间增加量：10% 索引：每一数据表关于主关键字建立索引文件。 2使用SQL Server Management Studio创建数据库 3.1 关系数据库设计思路 图4.1 【创建数据库】界面 图4.2 【新建数据库】对话框 3使用Transact-SQL语言创建数据库 1）命令格式 CREATE DATABASE ONPRIMARY ，N ，N LOG ON ，N 其中，N 表示设置文件属性，格式如下。 (NAME=逻辑文件名， FILENAME=物理文件名称 ，SIZE=数据库文件的初始容量值 ，MAXSIZE=物理文件的最大容量值 |UNLIMITED ，FILEGROWTH=增加容量值 ) ，N 其中，N 表示设置文件组属性，格式如下。 FILEGROUP 文件组名称 DEFAULT ，N 2）参数说明 （1）放在“ ”中的“”表示整个“ ”括起来的 选项都可省略，如果不省，则“” 括起的选项不能省。 使用“”分隔的多个选项，表示只能选择其中一个。 （2）数据库的名称必须符合标识符规则，最长为128个字 符。数据库名称在 SQL Server 的实例中必须唯一。数据库 的逻辑文件名是数据库在SQL Server中的标识符。FILENAME 指定数据库物理文件名称和路径，它和数据库逻辑名称一一 对应。文件组的逻辑名称必须在数据库中唯一，不能是系统 提供的名称 PRIMARY 和 PRIMARY_LOG。 （3）“ON”定义数据文件；“PRIMARY”定义主文件组中 的文件；“LOG ON”定义日志文件。一个数据库只能有一个 主文件，如果没有定义主文件，列在数据文件项的第一个文 件就是主文件。 3使用Transact-SQL语言创建数据库 （4）数据库文件容量单位可以是KB，MB，GB，TB，缺省值为MB ，长度必须为整数，主文件的，最小容量是Model数据库的主 文件长度；对于其他类型文件，最小长度为512KB。 （5）MAXSIZE：指定物理文件的最大容量。如果不设置文件的 最大尺寸，那么文件的增长最大值将是磁盘的所有空间。 UNLIMITED选项允许文件增长到磁盘已满。 （6）FILEGROWTH：指定文件每次增加容量的大小或百分比，基 数为当前文件大小。当FILEGROWTH=0时，表示文件不增长。 （7）DEFAULT：指定命名文件组为数据库中的默认文件组。 图4.11 用命令创建Studentcourse数据库界面 CREATE DATABASE studentcourse ON PRIMARY ( NAME =studentcourse, FILENAME=C:DATAstudentcourse.mdf, SIZE = 3072KB, MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB), FILEGROUP group2 ( NAME = secondsc, FILENAME = C:mydbsecondsc.ndf , SIZE = 3072KB , MAXSIZE =51200KB, FILEGROWTH = 1024KB ) LOG ON ( NAME = studentcourse_log, FILENAME=C:LOGstudentcourse_log.ldf , SIZE = 1024KB , MAXSIZE = 20480KB , FILEGROWTH = 10%) 3）创建学生选课数据库 4.1.3 管理数据库 1查看数据库信息 1）命令格式 EXEC sp_helpdb 数据库名 2）功能 查看指定数据库的相关数据文件信息、数据库拥有者、创建时 间等信息。若缺省数据库名，则显示所有数据库信息。 【例4.1】查看学生选课“studentcourse”数据库的信息。 方法一：使用SQL Server Management Studio查看数据库信息 方法二：使用系统存储过程命令查看数据库信息。 EXEC sp_helpdb studentcourse 【例4.2】查看所有数据库信息。 EXEC sp_helpdb 4.1.3 管理数据库 2打开数据库 1）命令格式 USE 2）功能 使指定数据库成为当前数据库 【例4.3】打开学生选课“studentcourse”数据库。 方法一：使用SQL Server Management Studio打开数据库 方法二：使用命令。 USE studentcourse 3修改数据库 1）命令格式 Alter Database 数据库名 Add File,N To Filegroup 文件组名称 |Add Log File ,N |Remove File 逻辑文件名称 With Delete |Modify File |Modify Name=新数据库名称 |Add Filegroup新增文件组名称 |Remove Filegroup 文件组名称 |Modify Filegroup 原文件组名称 文件组属性|Name=新文件组名称 2）功能 Add File：向数据库添加文件。Add Logfile：向数据库添加 日志文件。Remove File：从数据库中删除文件。Modify File ：对文件进行修改，包括SIZE、FILEGROWTH和MAXSIZE，每次只 能对一个属性进行修改。Modify Name：重新命名数据库。Add | Remove | Modify Filegroup：向数据库中添加删除修改文件组 【例4.4】向数据库Studentcourse中添加一个名为group2的 文件组，并在该文件组中添加一个名为Studentcourse2、路径 为默认的次数据文件，初始值大小为 2MB ，最大值为50MB，文 件以1MB增长；再添加一个名为Studentcourse_Log2的日志文件 ，初始值大小为1MB ，最大值为100MB，文件以10%增长。然后 对数据库中Studentcourse2文件重命名为Studentcourse_2，最 后把该文件从数据库中移除。 方法一：使用SQL Server Management Studio 方法二：使用SQL语言修改数据库。 命令如下所示： alter DATABASE studentcourse add filegroup group2 -新增group2文件组 GO 3修改数据库 4.1.3 管理数据库 4.1.3 管理数据库 alter DATABASE studentcourse add file (NAME =studentcourse2, -新增studentcourse2次数据文件 FILENAME = C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATA studentcourse2.ndf, SIZE = 2MB , MAXSIZE =50MB, FILEGROWTH =1MB ) to filegroup group2 Go alter DATABASE studentcourse modify file -修改studentcourse主数据文件的文件增长为2MB ( NAME =studentcourse, FILEGROWTH =2MB ) Go alter DATABASE studentcourse add LOG file -新增studentcourse_log2日志文件 ( NAME = Nstudentcourse_log2, FILENAME = C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQL DATA studentcourse_log2.ldf, SIZE =1MB , MAXSIZE = 100MB , FILEGROWTH = 10%) Go alter DATABASE studentcourse modify file (name=studentcourse2,newname=studentcourse_2) -对数据库中studentcourse2次数据文件重命名成studentcourse_2 Go alter DATABASE studentcourse remove file studentcourse_2 -从数据库中移除studentcourse_2次要数据文件 1）命令格式 DBCC SHRINKDATABASE (数据库名,Target_Percent) Notruncate|Truncateonly 2）功能 压缩指定数据库。 【例4.6】压缩学生选课studentcourse数据库，使其最大 可用空间为30%。 方法一：使用SQL Server Management Studio压缩数据库 方法二：使用SQL命令压缩数据库 DBCC SHRINKDATABASE(Studentcourse, 30 ) 4.1.3 管理数据库 5压缩数据库 4.1.3 管理数据库 【例4.7】压缩学生选课数据库studentcourse中的一个 secondsc次数据文件，将其压缩为2MB。 方法一：使用SQL Server Management Studio 方法二：使用SQL命令。 DBCC SHRINKFILE (secondsc ,2) 5压缩数据库 4.2.1 数据系统视图 1Sysobjects 系统视图 2Syscolumns系统视图 3Sysindexes系统视图 4Sysusers系统视图 5Sysdatabases系统视图 6Sysdepends系统视图 7Sysconstraints系统视图 4.2 数据表的创建 数据类型及其确定原则 SQL Server 的九大类数据类型 1）整型数据类型(Integer) 2）精确数字数据类型(Exact Numeric) 3）近似数字数据类型(Approximate Numeric) 4）货币数据类型(Monetary) 5）日期和时间数据类型(Date Time) 6）字符数据类型(Character) 7）二进制数据类型 8）特殊数据类型 9）自定义数据类型(UDT)EXEC 4.2.2 数据类型 【例4.10】建一个以Datetime为基础的出生日期（Birthday ）可为空的数据类型。 方法一：使用SQL Server Management Studio 方法二：使用SQL命令。 EXEC SP_ADDTYPE Birthday，Datetime，Null 4.2.2 数据类型 4.2.3 创建数据表结构 1熟悉CREATE TABLE语句的格式 1）CREATE TABLE语句的简化格式 CREATE TABLE 服务器名.数据库名.架构名.|数据库名 .架构名.|架构名.数据库表名 ( 列名 数据类型 NOT NULL identity(初值,步长) DEFAULT 默认值 UNIQUE PRIMARY KEY CLUSTERED | NONCLUSTERED ， 列名 数据类型 NOT NULL DEFAULT 默认值UNIQUE ， n ， 列名 AS 计算列值的表达式，n ， CONSTRAINT 主键约束名 PRIMARY KEY(属性名)， CONSTRAINT 检查约束名 CHECK ( 逻辑表达式)，n， FOREIGN KEY (外键属性) REFERENCES 参照表(参照属性) ，n ) ON 文件组|默认文件组 2）CREATE TABLE语句的说明 【例4.11】创建数据库“book_shop”的数据表book，数据表 由书号、书名、出版社、出版日期、单价、数量、总价（单价* 数量）、电子邮件地址和数据库表使用者字段组成。其中书号 列定义为主键并且为系统自动编号即标识列，种子值（起始值 ）为1000，增量为1，要求出版社字段的值只能是高教、浙大、 电子和中央四个之一，电子邮件地址字段中必须包含符号，单 价必须大于0，数量必须大于等于0，出版日期的默认值设置为 当前日期函数。 （1）标识IDENTITY 属性 （2）计算所得的列 （3）空值NULL约束 （4）PRIMARY KEY 约束 （5）UNIQUE 约束 （6）DEFAULT约束 （7）CHECK 约束 （8）FOREIGN KEY 约束 方法一：使用SQL Server Management Studio 方法二：使用命令。 EXEC sp_helpdb studentcourse CREATE database bookshop Go CREATE TABLE book (书号 int identity(1000,1) NOT FOR REPLICATION PRIMARY KEY CLUSTERED, 书名 char(20) not null, 出版社 char(20), 出版日期 datetime DEFAULT (getdate(), 单价 smallint check(单价0), 数量 smallint check(数量=0), 总价 as 单价*数量, 电子邮件地址 varchar(25), check(电子邮件地址 like %), check (出版社 in (高教,浙大,电子,中央) 2）CREATE TABLE语句的说明 【例4.12】创建数据库学生选课“studentcourse”的数据表S、 C、SC，数据表结构如表3.6，表3.7，表3.8所示。 各表的完整性约束如表3.9 ，表3.10 ，表3.11所示。 方法一：使用SQL Server Management Studio创建数据表 方法二：使用SQL命令。 1）创建课程表C的语句如下： CREATE TABLE C ( 课程号 Char (3) NOT NULL, 课程名 Varchar (20) NOT NULL, 学分 Smallint NULL, 预选课程号 Char (3) NULL, 教师 Char (8) NULL, CONSTRAINT FK_Pcno FOREIGN KEY( 预选课程号 ) REFERENCES C (课程号), CONSTRAINT CK_Cno CHECK ( 课程号 Like 0-90-9 ), CONSTRAINT PK_C PRIMARY KEY CLUSTERED (课程号 ASC ) ) ON PRIMARY 2）CREATE TABLE语句的说明 【例4.12】创建数据库学生选课“studentcourse”的数据表S、 C、SC，数据表结构如表3.6，表3.7，表3.8所示 方法二：使用SQL命令。 2）创建学生基本信息表S的语句如下： CREATE TABLE S ( 学号 Char (6) NOT NULL DEFAULT (J0400), 姓名 Char (8) NOT NULL, 性别 Char (2) NOT NULL, 出生日期 Datetime NOT NULL DEFAULT (19800101), 系 Varchar (20) NOT NULL, 电话 Char (8) NULL, CHECK (学号 Like A-Z 0-9 0-9 0-9 0-9 ), CHECK (性别 =女 OR 性别 =男), CHECK (电话 Like 0-90-90-9 -0-90-90-90-9 ), CONSTRAINT PK_S PRIMARY KEY CLUSTERED ( 学号 ASC) ) ON PRIMARY 2）CREATE TABLE语句的说明 【例4.12】创建数据库学生选课“studentcourse”的数据 表S、C、SC，数据表结构如表3.6，表3.7，表3.8所示 各表的完整性约束如表3.9 ，表3.10 ，表3.11所示。 方法二：使用SQL命令。 3）创建学生选课数据表SC的语句如下： CREATE TABLE SC ( 学号 Char (6) NOT NULL, 课程号 Char (3) NOT NULL, 成绩 Smallint NULL, FOREIGN KEY(课程号) REFERENCES C (课程号), FOREIGN KEY( 学号 ) REFERENCES S (学号), CHECK (成绩=(0) AND 成绩 ALTER COLUMN 类型（宽度）NULL|NOT NULL |ADD 类型（宽度）NULL|NOT NULL完整性约束,n |DROP COLUMN CASCADERESTRICT,n |DROP CONSTRAINT|ALL ,n 2）功能 ALTER TABLE：将要修改的当前数据库中的指定数据表的表名。 ALTER COLUMN：修改当前数据库中的指定数据表的指定属性。 ADD：向当前数据库中的指定数据表增加指定属性或列级完整性约束。 DROP COLUMN：删除当前数据库中的指定数据表中的指定属性。 DROP：删除当前数据库中的指定数据表中的指定列级完整性约束。 4.2.6 修改数据表 【例4.15】修改当前数据库“studentcourse”中S表的系属 性改成char(25)，增加一个入学时间字段，它的数据类型为 date time，并设置默认值为getdate()，最后删除入学时间字 段。 方法一：使用SQL Server Management Studio 方法二：使用命令。 EXEC sp_helpdb studentcourse ALTER TABLE s ALTER COLUMN 系char(25) Go ALTER TABLE s ADD 入学时间 datetime Go ALTER TABLE s ADD CONSTRAINT DF_sj DEFAULT(getdate() for 入学时间 4.2.6 修改数据表 2修改表的名称 1）命令格式 Sp_rename , 2）功能 重命名当前数据库中的指定数据表名。 【例4.16】重命名数据库bookshop的数据表book名称，改为 “书籍资料”。 方法一：使用SQL Server Management Studio 方法二：使用SQL命令。 Sp_rename book, 书籍资料 4.2.6 修改数据表 1）命令格式 DROP TABLE 表名 2）功能 删除表 【例4.17】删除当前数据库中的表S。 方法一：使用SQL Server Management Studio 方法二：使用SQL命令。 DROP TABLE S 4.2.7 删除数据表 4.3.1 使用SQL Server Management Studio插入、修改与 删除数据 4.3 数据库表的操作 4.3.2 使用T-SQL语句进行插入、修改和删除数据 1插入数据 2更新数据 3删除记录 插入数据记录的方法有三种，第一种是利用SQL Server Management Studio，第二种是使用SELECT查询语句（本节暂不 介绍），第三种是使用INSERT命令。 1）命令格式 l INSERT INTO 数据表名 (列名表) VALUES (元组值) l INSERT INTO 数据表名(列名表) SELECT 查询语句 l INSERT INTO 数据表名(列名表) DEFAULT VALUES 2）功能 向指定数据表的属性列插入数据，VALUES 后跟的元组值为属 性列提供数据。其中列名表中的属性排列顺序和VALUES 后跟的 元组值的排列顺序要一致。对应的数据类型要一致。如果没有 指定列名表，则表示数据表中的所有属性列。 “DEFAULT VALUES”选项会将默认值插入到该属性列中，如 果某列没有默认值，允许则向该列插入空值NULL，如果某列不 允许空值也没有默认值，则会出错。 1 插入数据 【例4.19】以下示例使用属性列显式指定插入到每个列的值。 方法一：使用SQL Server Management Studio 方法二：使用SQL命令。 USE Studentcourse GO INSERT INTO s(学号,姓名,性别,出生日期,系) VALUES (L0401, 张云龙, 男, 1987-11-11,路桥系) GO SELECT * FROM s 1 插入数据 【例4.20】将查询结果插入数据表，如将学号L0401、成 绩80以及课程表中所有课程号插入到sc中。 方法一：使用SQL Server Management Studio 方法二：使用SQL命令。 USE Studentcourse GO INSERT INTO sc SELECT L0401,课程号,80 FROM c GO SELECT * FROM sc 1 插入数据 1）命令格式 UPDATE 基本表名 SET 列名=值表达式， 列名=值表达式 WHERE 条件表达式 2）功能 更新指定基本表，满足WHERE子句条件的记录的指定属性值。 其中值表达式可以是常量、变量、表达式。若缺省WHERE，则修 改表中的所有元组。但在进行修改操作时，需注意数据库的一 致性。 【例4.21】更新s表中的所有行出生日期列中的值变为原出生 日期值加1。 方法一：使用SQL Server Management Studio 方法二：使用SQL命令 USE Studentcourse GO UPDATE s SET 出生日期=出生日期+1 2更新数据 【例4.22】将选C01课程的学号是L0401的学生的成绩改成85分。 USE Studentcourse GO UPDATE sc SET 成绩=85 WHERE 课程号=C01 AND 学号=L0401 GO SELECT * FROM sc GO 【例4.23】将张云龙学生的成绩减少5分。 USE Studentcourse GO UPDATE sc SET 成绩=成绩-5 WHERE 学号 IN (SELECT 学号 FROM s WHERE 姓名=张云龙) GO SELECT * FROM sc 2更新数据 1）命令格式 DELETE FROM 基本表名 WHERE 条件表达式 2）功能 删除表 【例4.24】删除学号为L0401的学生选课信息。 USE Studentcourse GO DELETE FROM sc Where 学号=L0401 GO SELECT * FROM sc 【例4.25】从SC 表中删除所有行 USE Studentcourse GO DELETE FROM sc GO SELECT * FROM sc 3删除记录 4. 删除所有行 1）命令格式 TRUNCATE TABLE 数据库名.架构名.|架构名.表名 ; 2）功能 使用TRUNCATE TABLE命令删除所有行。 【例4.26】从学生选课数据表SC中删除所有行。 TRUNCATE TABLE studentcourse.DBO.sc GO SELECT * FROM sc 1索引的作用 1）加速数据检索 2）优化查询 3）强制数据完整性 2索引的分类 1）聚集索引 2）非聚集索引 3）唯一索引 4.4 索引管理 4.4.1 索引概述 1）命令格式 CREATE UNIQUECLUSTERED|NONCLUSTERED INDEX 索引名 ON 数据表名|视图名（字段名表ASC|DESC,n） WITH PAD_INDEX ,FILLFACTOR=填充因子 ,IGNORE_DUP_KEY ,STATISTICS_NORECOMPUTE ON 文件组名 2）功能 只有在指定的索引名称存在时，才能使用DROPEXISTING选 项，该项说明首先删除指定表的索引后再重