资源描述
第4章数据库管理本章导读本章主要介绍了有关数据表的操作，包括表的创建、修改、删除和建立索引等操作以及表中数据维护的有关操作和方法，基本掌握使用SQLServerManagementStudio和T-SQL语句对表的操作和表的数据操作，包括创建表、删除表、对表中字段建立索引、向表中增加数据、修改数据、删除数据等。要对SQLServer2005中的数据类型有一个清楚的认识。,4.1数据库的创建与管理,4.1.1SQLServer系统数据库1系统数据库1）Master数据库2）Model数据库3）Msdb数据库4）Tempdb数据库5）Resource数据库6）Distribution数据库2数据库快照,4.1数据库的创建与管理,4.1.2数据库的创建,1准备创建数据库（学生选课）,1）确定数据库的名称、所有者（创建数据库的用户）数据库名称：studentcourse2）确定存储该数据库的数据文件的大小及文件空间增长方式，确定关系、索引，及系统存储参数的配置，确定数据库的存取方法。主数据文件：逻辑名称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使用SQLServerManagementStudio创建数据库,3.1关系数据库设计思路,图4.1【创建数据库】界面,图4.2【新建数据库】对话框,3使用Transact-SQL语言创建数据库,1）命令格式CREATEDATABASEONPRIMARY，N，NLOGON，N其中，N表示设置文件属性，格式如下。(NAME=逻辑文件名，FILENAME=物理文件名称，SIZE=数据库文件的初始容量值，MAXSIZE=物理文件的最大容量值|UNLIMITED，FILEGROWTH=增加容量值)，N其中，N表示设置文件组属性，格式如下。FILEGROUP文件组名称DEFAULT，N,2）参数说明（1）放在“”中的“”表示整个“”括起来的选项都可省略，如果不省，则“”括起的选项不能省。使用“”分隔的多个选项，表示只能选择其中一个。（2）数据库的名称必须符合标识符规则，最长为128个字符。数据库名称在SQLServer的实例中必须唯一。数据库的逻辑文件名是数据库在SQLServer中的标识符。FILENAME指定数据库物理文件名称和路径，它和数据库逻辑名称一一对应。文件组的逻辑名称必须在数据库中唯一，不能是系统提供的名称PRIMARY和PRIMARY_LOG。（3）“ON”定义数据文件；“PRIMARY”定义主文件组中的文件；“LOGON”定义日志文件。一个数据库只能有一个主文件，如果没有定义主文件，列在数据文件项的第一个文件就是主文件。,3使用Transact-SQL语言创建数据库,（4）数据库文件容量单位可以是KB，MB，GB，TB，缺省值为MB，长度必须为整数，主文件的，最小容量是Model数据库的主文件长度；对于其他类型文件，最小长度为512KB。（5）MAXSIZE：指定物理文件的最大容量。如果不设置文件的最大尺寸，那么文件的增长最大值将是磁盘的所有空间。UNLIMITED选项允许文件增长到磁盘已满。（6）FILEGROWTH：指定文件每次增加容量的大小或百分比，基数为当前文件大小。当FILEGROWTH=0时，表示文件不增长。（7）DEFAULT：指定命名文件组为数据库中的默认文件组。,图4.11用命令创建Studentcourse数据库界面,CREATEDATABASEstudentcourseONPRIMARY(NAME=studentcourse,FILENAME=C:DATAstudentcourse.mdf,SIZE=3072KB,MAXSIZE=UNLIMITED,FILEGROWTH=1024KB),FILEGROUPgroup2(NAME=secondsc,FILENAME=C:mydbsecondsc.ndf,SIZE=3072KB,MAXSIZE=51200KB,FILEGROWTH=1024KB)LOGON(NAME=studentcourse_log,FILENAME=C:LOGstudentcourse_log.ldf,SIZE=1024KB,MAXSIZE=20480KB,FILEGROWTH=10%),3）创建学生选课数据库,4.1.3管理数据库,1查看数据库信息,1）命令格式EXECsp_helpdb数据库名2）功能查看指定数据库的相关数据文件信息、数据库拥有者、创建时间等信息。若缺省数据库名，则显示所有数据库信息。【例4.1】查看学生选课“studentcourse”数据库的信息。方法一：使用SQLServerManagementStudio查看数据库信息方法二：使用系统存储过程命令查看数据库信息。EXECsp_helpdbstudentcourse【例4.2】查看所有数据库信息。EXECsp_helpdb,4.1.3管理数据库,2打开数据库,1）命令格式USE2）功能使指定数据库成为当前数据库【例4.3】打开学生选课“studentcourse”数据库。方法一：使用SQLServerManagementStudio打开数据库方法二：使用命令。USEstudentcourse,3修改数据库,1）命令格式AlterDatabase数据库名AddFile,NToFilegroup文件组名称|AddLogFile,N|RemoveFile逻辑文件名称WithDelete|ModifyFile|ModifyName=新数据库名称|AddFilegroup新增文件组名称|RemoveFilegroup文件组名称|ModifyFilegroup原文件组名称文件组属性|Name=新文件组名称2）功能AddFile：向数据库添加文件。AddLogfile：向数据库添加日志文件。RemoveFile：从数据库中删除文件。ModifyFile：对文件进行修改，包括SIZE、FILEGROWTH和MAXSIZE，每次只能对一个属性进行修改。ModifyName：重新命名数据库。Add|Remove|ModifyFilegroup：向数据库中添加删除修改文件组,【例4.4】向数据库Studentcourse中添加一个名为group2的文件组，并在该文件组中添加一个名为Studentcourse2、路径为默认的次数据文件，初始值大小为2MB，最大值为50MB，文件以1MB增长；再添加一个名为Studentcourse_Log2的日志文件，初始值大小为1MB，最大值为100MB，文件以10%增长。然后对数据库中Studentcourse2文件重命名为Studentcourse_2，最后把该文件从数据库中移除。方法一：使用SQLServerManagementStudio方法二：使用SQL语言修改数据库。命令如下所示：alterDATABASEstudentcourseaddfilegroupgroup2-新增group2文件组GO,3修改数据库,4.1.3管理数据库,4.1.3管理数据库,alterDATABASEstudentcourseaddfile(NAME=studentcourse2,-新增studentcourse2次数据文件FILENAME=C:ProgramFilesMicrosoftSQLServerMSSQL.1MSSQLDATAstudentcourse2.ndf,SIZE=2MB,MAXSIZE=50MB,FILEGROWTH=1MB)tofilegroupgroup2GoalterDATABASEstudentcoursemodifyfile-修改studentcourse主数据文件的文件增长为2MB(NAME=studentcourse,FILEGROWTH=2MB)GoalterDATABASEstudentcourseaddLOGfile-新增studentcourse_log2日志文件(NAME=Nstudentcourse_log2,FILENAME=C:ProgramFilesMicrosoftSQLServerMSSQL.1MSSQLDATAstudentcourse_log2.ldf,SIZE=1MB,MAXSIZE=100MB,FILEGROWTH=10%)GoalterDATABASEstudentcoursemodifyfile(name=studentcourse2,newname=studentcourse_2)-对数据库中studentcourse2次数据文件重命名成studentcourse_2GoalterDATABASEstudentcourseremovefilestudentcourse_2-从数据库中移除studentcourse_2次要数据文件,1）命令格式DBCCSHRINKDATABASE(数据库名,Target_Percent)Notruncate|Truncateonly2）功能压缩指定数据库。【例4.6】压缩学生选课studentcourse数据库，使其最大可用空间为30%。方法一：使用SQLServerManagementStudio压缩数据库方法二：使用SQL命令压缩数据库DBCCSHRINKDATABASE(Studentcourse,30),4.1.3管理数据库,5压缩数据库,4.1.3管理数据库,【例4.7】压缩学生选课数据库studentcourse中的一个secondsc次数据文件，将其压缩为2MB。方法一：使用SQLServerManagementStudio方法二：使用SQL命令。DBCCSHRINKFILE(secondsc,2),5压缩数据库,4.2.1数据系统视图,1Sysobjects系统视图2Syscolumns系统视图3Sysindexes系统视图4Sysusers系统视图5Sysdatabases系统视图6Sysdepends系统视图7Sysconstraints系统视图,4.2数据表的创建,数据类型及其确定原则SQLServer的九大类数据类型1）整型数据类型(Integer)2）精确数字数据类型(ExactNumeric)3）近似数字数据类型(ApproximateNumeric)4）货币数据类型(Monetary)5）日期和时间数据类型(DateTime)6）字符数据类型(Character)7）二进制数据类型8）特殊数据类型9）自定义数据类型(UDT)EXEC,4.2.2数据类型,【例4.10】建一个以Datetime为基础的出生日期（Birthday）可为空的数据类型。方法一：使用SQLServerManagementStudio方法二：使用SQL命令。EXECSP_ADDTYPEBirthday，Datetime，Null,4.2.2数据类型,4.2.3创建数据表结构,1熟悉CREATETABLE语句的格式1）CREATETABLE语句的简化格式CREATETABLE服务器名.数据库名.架构名.|数据库名.架构名.|架构名.数据库表名(列名数据类型NOTNULLidentity(初值,步长)DEFAULT默认值UNIQUEPRIMARYKEYCLUSTERED|NONCLUSTERED，列名数据类型NOTNULLDEFAULT默认值UNIQUE，n，列名AS计算列值的表达式，n，CONSTRAINT主键约束名PRIMARYKEY(属性名)，CONSTRAINT检查约束名CHECK(逻辑表达式)，n，FOREIGNKEY(外键属性)REFERENCES参照表(参照属性)，n)ON文件组|默认文件组,2）CREATETABLE语句的说明,【例4.11】创建数据库“book_shop”的数据表book，数据表由书号、书名、出版社、出版日期、单价、数量、总价（单价*数量）、电子邮件地址和数据库表使用者字段组成。其中书号列定义为主键并且为系统自动编号即标识列，种子值（起始值）为1000，增量为1，要求出版社字段的值只能是高教、浙大、电子和中央四个之一，电子邮件地址字段中必须包含符号，单价必须大于0，数量必须大于等于0，出版日期的默认值设置为当前日期函数。（1）标识IDENTITY属性（2）计算所得的列（3）空值NULL约束（4）PRIMARYKEY约束（5）UNIQUE约束（6）DEFAULT约束（7）CHECK约束（8）FOREIGNKEY约束,方法一：使用SQLServerManagementStudio方法二：使用命令。EXECsp_helpdbstudentcourseCREATEdatabasebookshopGoCREATETABLEbook(书号intidentity(1000,1)NOTFORREPLICATIONPRIMARYKEYCLUSTERED,书名char(20)notnull,出版社char(20),出版日期datetimeDEFAULT(getdate(),单价smallintcheck(单价0),数量smallintcheck(数量=0),总价as单价*数量,电子邮件地址varchar(25),check(电子邮件地址like%),check(出版社in(高教,浙大,电子,中央),2）CREATETABLE语句的说明,【例4.12】创建数据库学生选课“studentcourse”的数据表S、C、SC，数据表结构如表3.6，表3.7，表3.8所示。各表的完整性约束如表3.9，表3.10，表3.11所示。方法一：使用SQLServerManagementStudio创建数据表方法二：使用SQL命令。1）创建课程表C的语句如下：CREATETABLEC(课程号Char(3)NOTNULL,课程名Varchar(20)NOTNULL,学分SmallintNULL,预选课程号Char(3)NULL,教师Char(8)NULL,CONSTRAINTFK_PcnoFOREIGNKEY(预选课程号)REFERENCESC(课程号),CONSTRAINTCK_CnoCHECK(课程号Like0-90-9),CONSTRAINTPK_CPRIMARYKEYCLUSTERED(课程号ASC)ONPRIMARY,2）CREATETABLE语句的说明,【例4.12】创建数据库学生选课“studentcourse”的数据表S、C、SC，数据表结构如表3.6，表3.7，表3.8所示方法二：使用SQL命令。2）创建学生基本信息表S的语句如下：CREATETABLES(学号Char(6)NOTNULLDEFAULT(J0400),姓名Char(8)NOTNULL,性别Char(2)NOTNULL,出生日期DatetimeNOTNULLDEFAULT(19800101),系Varchar(20)NOTNULL,电话Char(8)NULL,CHECK(学号LikeA-Z0-90-90-90-9),CHECK(性别=女OR性别=男),CHECK(电话Like0-90-90-9-0-90-90-90-9),CONSTRAINTPK_SPRIMARYKEYCLUSTERED(学号ASC)ONPRIMARY,2）CREATETABLE语句的说明,【例4.12】创建数据库学生选课“studentcourse”的数据表S、C、SC，数据表结构如表3.6，表3.7，表3.8所示各表的完整性约束如表3.9，表3.10，表3.11所示。方法二：使用SQL命令。3）创建学生选课数据表SC的语句如下：CREATETABLESC(学号Char(6)NOTNULL,课程号Char(3)NOTNULL,成绩SmallintNULL,FOREIGNKEY(课程号)REFERENCESC(课程号),FOREIGNKEY(学号)REFERENCESS(学号),CHECK(成绩=(0)AND成绩=(100)OR成绩ISNULL),PRIMARYKEYCLUSTERED(学号ASC,课程号ASC)）,2）CREATETABLE语句的说明,【例4.13】返回有关所有对象的信息。USEMaster;GOEXECSp_Help;GO【例4.14】返回学生选课“studentcourse”中学生表的信息。USEStudentcourseGOEXECSp_Helps,4.2.5查看数据表,1修改表的结构,1）命令格式ALTERTABLEALTERCOLUMN类型（宽度）NULL|NOTNULL|ADD类型（宽度）NULL|NOTNULL完整性约束,n|DROPCOLUMNCASCADERESTRICT,n|DROPCONSTRAINT|ALL,n2）功能ALTERTABLE：将要修改的当前数据库中的指定数据表的表名。ALTERCOLUMN：修改当前数据库中的指定数据表的指定属性。ADD：向当前数据库中的指定数据表增加指定属性或列级完整性约束。DROPCOLUMN：删除当前数据库中的指定数据表中的指定属性。DROP：删除当前数据库中的指定数据表中的指定列级完整性约束。,4.2.6修改数据表,【例4.15】修改当前数据库“studentcourse”中S表的系属性改成char(25)，增加一个入学时间字段，它的数据类型为datetime，并设置默认值为getdate()，最后删除入学时间字段。方法一：使用SQLServerManagementStudio方法二：使用命令。EXECsp_helpdbstudentcourseALTERTABLEsALTERCOLUMN系char(25)GoALTERTABLEsADD入学时间datetimeGoALTERTABLEsADDCONSTRAINTDF_sjDEFAULT(getdate()for入学时间,4.2.6修改数据表,2修改表的名称,1）命令格式Sp_rename,2）功能重命名当前数据库中的指定数据表名。【例4.16】重命名数据库bookshop的数据表book名称，改为“书籍资料”。方法一：使用SQLServerManagementStudio方法二：使用SQL命令。Sp_renamebook,书籍资料,4.2.6修改数据表,1）命令格式DROPTABLE表名2）功能删除表【例4.17】删除当前数据库中的表S。方法一：使用SQLServerManagementStudio方法二：使用SQL命令。DROPTABLES,4.2.7删除数据表,4.3.1使用SQLServerManagementStudio插入、修改与删除数据,4.3数据库表的操作,4.3.2使用T-SQL语句进行插入、修改和删除数据,1插入数据2更新数据3删除记录,插入数据记录的方法有三种，第一种是利用SQLServerManagementStudio，第二种是使用SELECT查询语句（本节暂不介绍），第三种是使用INSERT命令。1）命令格式INSERTINTO数据表名(列名表)VALUES(元组值)INSERTINTO数据表名(列名表)SELECT查询语句INSERTINTO数据表名(列名表)DEFAULTVALUES2）功能向指定数据表的属性列插入数据，VALUES后跟的元组值为属性列提供数据。其中列名表中的属性排列顺序和VALUES后跟的元组值的排列顺序要一致。对应的数据类型要一致。如果没有指定列名表，则表示数据表中的所有属性列。“DEFAULTVALUES”选项会将默认值插入到该属性列中，如果某列没有默认值，允许则向该列插入空值NULL，如果某列不允许空值也没有默认值，则会出错。,1插入数据,【例4.19】以下示例使用属性列显式指定插入到每个列的值。方法一：使用SQLServerManagementStudio方法二：使用SQL命令。USEStudentcourseGOINSERTINTOs(学号,姓名,性别,出生日期,系)VALUES(L0401,张云龙,男,1987-11-11,路桥系)GOSELECT*FROMs,1插入数据,【例4.20】将查询结果插入数据表，如将学号L0401、成绩80以及课程表中所有课程号插入到sc中。方法一：使用SQLServerManagementStudio方法二：使用SQL命令。USEStudentcourseGOINSERTINTOscSELECTL0401,课程号,80FROMcGOSELECT*FROMsc,1插入数据,1）命令格式UPDATE基本表名SET列名=值表达式，列名=值表达式WHERE条件表达式2）功能更新指定基本表，满足WHERE子句条件的记录的指定属性值。其中值表达式可以是常量、变量、表达式。若缺省WHERE，则修改表中的所有元组。但在进行修改操作时，需注意数据库的一致性。【例4.21】更新s表中的所有行出生日期列中的值变为原出生日期值加1。方法一：使用SQLServerManagementStudio方法二：使用SQL命令USEStudentcourseGOUPDATEsSET出生日期=出生日期+1,2更新数据,【例4.22】将选C01课程的学号是L0401的学生的成绩改成85分。USEStudentcourseGOUPDATEscSET成绩=85WHERE课程号=C01AND学号=L0401GOSELECT*FROMscGO【例4.23】将张云龙学生的成绩减少5分。USEStudentcourseGOUPDATEscSET成绩=成绩-5WHERE学号IN(SELECT学号FROMsWHERE姓名=张云龙)GOSELECT*FROMsc,2更新数据,1）命令格式DELETEFROM基本表名WHERE条件表达式2）功能删除表【例4.24】删除学号为L0401的学生选课信息。USEStudentcourseGODELETEFROMscWhere学号=L0401GOSELECT*FROMsc【例4.25】从SC表中删除所有行USEStudentcourseGODELETEFROMscGOSELECT*FROMsc,3删除记录,4.删除所有行,1）命令格式TRUNCATETABLE数据库名.架构名.|架构名.表名;2）功能使用TRUNCATETABLE命令删除所有行。【例4.26】从学生选课数据表SC中删除所有行。TRUNCATETABLEstudentcourse.DBO.scGOSELECT*FROMsc,1索引的作用1）加速数据检索2）优化查询3）强制数据完整性2索引的分类1）聚集索引2）非聚集索引3）唯一索引,4.4索引管理,4.4.1索引概述,1）命令格式CREATEUNIQUECLUSTERED|NONCLUSTEREDINDEX索引名ON数据表名|视图名（字段名表ASC|DESC,n）WITHPAD_INDEX,FILLFACTOR=填充因子,IGNORE_DUP_KEY,STATISTICS_NORECOMPUTEON文件组名2）功能只有在指定的索引名称存在时，才能使用DROPEXISTING选项，该项说明首先删除指定表的索引后再重新构造它。“UNIQUE”表示建立唯一索引。CLUSTERED表示建立聚集索引，NOCLUSTERED表示建立非聚集索引。,4.4.2创建索引,4.4.2创建索引,表4.11“studentcourse”索引情况表,【例4.28】使用SQL命令，在数据库“studentcourse”中的数据表S中，关于“学号”建立聚集索引，关于“姓名”建立非聚集索引。CREATEINDEXIN_姓名ons(姓名)CREATEuniqueclusteredINDEXIN_学号ons(学号)WITHpad_index,fillfactor=100-填充因子为100【例4.29】为数据库“studentcourse”中的数据表关于c.课程名降序建立唯一索引IN_课程名。IFEXISTS(selectnamefromsysindexeswherename=IN_课程名)DROPINDEXc.IN_课程名GoUSEstudentcourseCREATEuniqueINDEXIN_课程名onc(课程名desc)。,4.4.2创建索引,1）命令格式DROPINDEX索引名,n2）功能删除指定的索引。可以列出多个要删除的索引名。利用DROPINDEX命令删除通过定义PRIMARYKEY或UNIQUE约束创建的索引，必须先删除指定的约束。在系统表的索引不能使用DROPINDEX删除。删除表中的聚集索引，将使表中的所在非聚集索引重建。【例4.32】删除数据库“studentcourse”中，数据表sc中的索引IN_成绩、数据表c中的索引IN_课程名。UsestudentcourseDROPINDEXsc.IN_成绩,c.IN_课程名,4.4.3删除索引,1查看表中的索引1）命令格式sp_helpindexobjname=表或视图的名称2）功能报告有关表或视图上索引的信息，当前数据库中表或视图的名称的数据类型为nvarchar(776)。【例4.33】查看“studentcourse”数据库中的数据表S上索引的类型。运行结果如图4.59所示。UsestudentcourseGOsp_helpindexs,4.4.4查看索引,【例4.36】修改在【例4.27】中创建的索引IX_teacher，修改后的索引基于“教师”和“课程名”，成为组合索引。,4.4.5修改索引,
展开阅读全文