sql第04章管理数据.ppt

第4章 数据库管理 学习目的与要求（1）掌握SQL 工具的使用。（2）了解数据库的结构。（3）掌握创建、打开、修改及删除数据库的方法。（4）掌握创建、修改及删除数据表的方法。（5）掌握创建及删除数据表索引的方法。,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）确定存储该数据库的数据文件的大小及文件空间增长方式，确定关系、索引，及系统存储参数的配置，确定数据库的存取方法.,主数据文件：逻辑名称student；物理文件名：C:Datastudent.mdf；初始大小：3MB；最大空间：UNLIMITED；空间增加量：1MB；属于文件组primary。次数据文件：逻辑名称secondsc；物理文件名：C:mydbsecondsc.ndf，初始大小：3MB，最大空间：50MB，空间增加量：1MB，属于文件组group1。日志文件：逻辑名称student_log；物理文件名：C:Logstudent_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-逻辑数据名称：studentcourse ON PRIMARY-ON 子句指出文件属于PRIMARY组(NAME=studentcourse,-NAME指出了对应的逻辑文件名 FILENAME=C:DATAstudentcourse.mdf,-FILENAME指出物理文件名 SIZE=3072KB,-Size指出了初始分配空间 MAXSIZE=UNLIMITED,-MAXSIZE指出了最大空间 FILEGROWTH=1024KB),-FILEGROWTH指出了文件空间的增长量 FILEGROUP group1-FILEGROUP子句指出次数据文件.NDF(NAME=N secondsc,FILENAME=NC:mydb secondsc.ndf,SIZE=3072KB,MAXSIZE=51200KB,FILEGROWTH=1024KB)LOG ON-LOG ON 子句指出新建数据库的日志文件.LDF(NAME=Nstudentcourse_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 数据库名AddFile,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 group2Goalter DATABASE studentcourse modify file-修改studentcourse主数据文件的文件增长2MB(NAME=studentcourse,FILEGROWTH=2MB)Go,3修改数据库,alter DATABASE studentcourseadd LOG file-新增studentcourse_log2日志文件(NAME=Nstudentcourse_log2,FILENAME=C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQL DATA studentcourse_log2.ldf,SIZE=1MB,MAXSIZE=100MB,FILEGROWTH=10%)Goalter DATABASE studentcoursemodify file-对数据库中次数据文件重命名成studentcourse_2(name=studentcourse2,newname=studentcourse_2)Go,3修改数据库,alter DATABASE studentcourseremove file studentcourse_2-从数据库中移除studentcourse_2次要数据文件,3修改数据库,4.删除数据库1）命令格式DROP DATABASE,N2)功能其功能是删除指定数据库。,3修改数据库,1）命令格式DBCC SHRINKDATABASE(数据库名,Target_Percent)Notruncate|Truncateonly2）功能压缩指定数据库。【例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 数据表的创建,数据类型及其确定原则见教材P60SQL 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 studentcourseCREATE database bookshopGoCREATE 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 成绩=(100)OR 成绩 IS NULL),PRIMARY KEY CLUSTERED(学号 ASC,课程号 ASC),2）CREATE TABLE语句的说明,【例4.13】返回有关所有对象的信息。USE Master;GOEXEC Sp_Help;GO【例4.14】返回学生选课“studentcourse”中学生表的信息。USE StudentcourseGOEXEC Sp_Help s,4.2.5 查看数据表,1修改表的结构,1）命令格式ALTER TABLE ALTER COLUMN 类型（宽度）NULL|NOT NULL|ADD 类型（宽度）NULL|NOT NULL完整性约束,n|DROP COLUMN CASCADERESTRICT,n|DROP CONSTRAINT|ALL,n2）功能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 studentcourseALTER TABLE sALTER COLUMN 系char(25)GoALTER TABLE sADD 入学时间 datetimeGoALTER TABLE sADD CONSTRAINT DF_sj DEFAULT(getdate()for 入学时间GO,4.2.6 修改数据表,ALTER TABLE sDROP DF_sjGOALTER TABLE sDROP COLUMN 入学时间,4.2.6 修改数据表,2修改表的名称,1）命令格式 Sp_rename,2）功能重命名当前数据库中的指定数据表名。【例4.16】重命名数据库bookshop的数据表book名称，改为“书籍资料”。方法一：使用SQL Server Management Studio方法二：使用SQL命令。Sp_rename book,书籍资料,4.2.6 修改数据表,1删除数据库信息,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）命令格式INSERT INTO 数据表名(列名表)VALUES(元组值)INSERT INTO 数据表名(列名表)SELECT 查询语句INSERT INTO 数据表名(列名表)DEFAULT VALUES2）功能向指定数据表的属性列插入数据，VALUES 后跟的元组值为属性列提供数据。其中列名表中的属性排列顺序和VALUES 后跟的元组值的排列顺序要一致。对应的数据类型要一致。如果没有指定列名表，则表示数据表中的所有属性列。“DEFAULT VALUES”选项会将默认值插入到该属性列中，如果某列没有默认值，允许则向该列插入空值NULL，如果某列不允许空值也没有默认值，则会出错。,1 插入数据,【例4.19】以下示例使用属性列显式指定插入到每个列的值。方法一：使用SQL Server Management Studio方法二：使用SQL命令。USE StudentcourseGOINSERT INTO s(学号,姓名,性别,出生日期,系)VALUES(L0401,张云龙,男,1987-11-11,路桥系)GoSELECT*FROM s,1 插入数据,【例4.20】将查询结果插入数据表，如将学号L0401、成绩80以及课程表中所有课程号插入到sc中。方法一：使用SQL Server Management Studio方法二：使用SQL命令。USE StudentcourseGOINSERT INTO sc SELECT L0401,课程号,80 FROM cGoSELECT*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 GO SELECT*FROM s,2更新数据,【例4.22】将选C01课程的学号是L0401的学生的成绩改成85分。USE StudentcourseGOUPDATE sc SET 成绩=85 WHERE 课程号=C01 AND 学号=L0401GO SELECT*FROM scGO【例4.23】将张云龙学生的成绩减少5分。USE Studentcourse GOUPDATE sc SET 成绩=成绩-5WHERE 学号 IN(SELECT 学号 FROM s WHERE 姓名=张云龙)GOSELECT*FROM sc,2更新数据,1）命令格式DELETE FROM 基本表名 WHERE 条件表达式2）功能删除表中满足条件的所有元组。【例4.24】删除学号为L0401的学生选课信息。USE StudentcourseGODELETE FROM sc Where 学号=L0401GOSELECT*FROM sc【例4.25】从SC 表中删除所有行 USE Studentcourse GO DELETE FROM sc GO SELECT*FROM sc,3删除记录,1索引的作用1）加速数据检索2）优化查询3）强制数据完整性。2索引的分类1）聚集索引2）非聚集索引3）唯一索引,4.4 索引管理,4.4.1 索引概述,1）命令格式CREATEUNIQUECLUSTERED|NOCLUSTEREDINDEX 索引名 ON数据表名|视图名（字段名表ASC|DESC,n）WITH PAD_INDEX,FILLFACTOR=填充因子,IGNORE_DUP_KEY,STATISTICS_NORECOMPUTEON 文件组名2）功能同一个表中是唯一索引的名称最多达128个字符。只有在指定的索引名称存在时，才能使用DROPEXISTING选项，该项说明首先删除指定表的索引后再重新构造它。“UNIQUE”表示建立唯一索引。CLUSTERED表示建立聚集索引，NOCLUSTERED表示建立非聚集索引。,4.4.2 创建索引,4.4.2 创建索引,表4.17“studentcourse”索引情况表,【例4.28】使用SQL命令，在数据库“studentcourse”中的数据表S中，关于“学号”建立聚集索引，关于“姓名”建立非聚集索引。CREATE INDEX IN_姓名 on s(姓名)CREATE unique clustered INDEX IN_学号 on s(学号)WITHpad_index,fillfactor=100-填充因子为100【例4.29】为数据库“studentcourse”中的数据表关于c.课程名降序建立唯一索引IN_课程名。IF EXISTS(select name from sysindexes where name=IN_课程名)DROP INDEX c.IN_课程名 GoUSE studentcourseCREATE unique INDEX IN_课程名 on c(课程名 desc),4.4.2 创建索引,1）命令格式DROP INDEX 索引名,n2）功能 删除指定的索引。可以列出多个要删除的索引名。利用DROP INDEX命令删除通过定义PRIMARY KEY或UNIQUE约束创建的索引，必须先删除指定的约束。在系统表的索引不能使用DROP INDEX删除。删除表中的聚集索引，将使表中的所在非聚集索引重建。【例4.32】删除数据库“studentcourse”中，数据表sc中的索引IN_成绩、数据表c中的索引IN_课程名。Use studentcourseDROP INDEX sc.IN_成绩,c.IN_课程名,4.4.3 删除索引,1查看表中的索引1）命令格式sp_helpindex objname=表或视图的名称2）功能 报告有关表或视图上索引的信息，当前数据库中表或视图的名称的数据类型为nvarchar(776)。【例4.33】查看“studentcourse”数据库中的数据表S上索引的类型。运行结果如图4.59所示。Use studentcourseGOsp_helpindex s,4.4.4 查看索引,2查看索引的空间信息1）命令格式sp_spaceused 表的名称2）功能显示行数、保留的磁盘空间以及当前数据库中的表所使用的磁盘空间，或显示由整个数据库保留和使用的磁盘空间【例4.34】查看数据库“studentcourse”和数据表S上的磁盘空间。运行结果如图4.60所示。USE studentcourseEXEC sp_spaceused sEXEC sp_spaceused,4.4.4 查看索引,3查看索引属性1）命令格式INDEXPROPERTY(表的标识号，索引的名称,属性)2）功能显示表中指定索引的属性信息。可以是下列值中的一个。【例4.35】为 sc的 IN_学号课程号索引返回 IsPad Index 属性的设置。USE studentcourseSELECT INDEXPROPERTY(OBJECT_ID(sc),IN_学号课程号,IsPadIndex),4.4.4 查看索引,【例4.36】修改在【例4.27】中创建的索引IX_teacher，修改后的索引基于“教师”和“课程名”，成为组合索引。,修改索引,