《數據庫技術與應用第04章.ppt》由會員分享，可在線閱讀，更多相關《數據庫技術與應用第04章.ppt（48頁珍藏版）》請在裝配圖網上搜索。

第4章數據庫管理本章導讀本章主要介紹了有關數據表的操作，包括表的創(chuàng)建、修改、刪除和建立索引等操作以及表中數據維護的有關操作和方法，基本掌握使用SQLServerManagementStudio和T-SQL語句對表的操作和表的數據操作，包括創(chuàng)建表、刪除表、對表中字段建立索引、向表中增加數據、修改數據、刪除數據等。要對SQLServer2005中的數據類型有一個清楚的認識。,4.1數據庫的創(chuàng)建與管理,4.1.1SQLServer系統(tǒng)數據庫1．系統(tǒng)數據庫1）Master數據庫2）Model數據庫3）Msdb數據庫4）Tempdb數據庫5）Resource數據庫6）Distribution數據庫2．數據庫快照,4.1數據庫的創(chuàng)建與管理,4.1.2數據庫的創(chuàng)建,1．準備創(chuàng)建數據庫（學生選課）,,1）確定數據庫的名稱、所有者（創(chuàng)建數據庫的用戶）數據庫名稱：studentcourse2）確定存儲該數據庫的數據文件的大小及文件空間增長方式，確定關系、索引，及系統(tǒng)存儲參數的配置，確定數據庫的存取方法。主數據文件：邏輯名稱studentcourse；物理文件名：C:\Data\studentcourse.mdf，初始大?。?MB，最大空間：UNLIMITED，空間增加量：1MB，屬于文件組primary。次數據文件：邏輯名稱secondsc；物理文件名：C:\mydb\secondsc.ndf，初始大?。?MB，最大空間：50MB，空間增加量：1MB，屬于文件組group1。日志文件：邏輯名稱studentcourse_log；物理文件名：C:\Log\studentcourse_log.ldf，始初大?。?MB，最大空間：20MB，空間增加量：10%索引：每一數據表關于主關鍵字建立索引文件。,2．使用SQLServerManagementStudio創(chuàng)建數據庫,3.1關系數據庫設計思路,,圖4.1【創(chuàng)建數據庫】界面,圖4.2【新建數據庫】對話框,3．使用Transact-SQL語言創(chuàng)建數據庫,1）命令格式CREATEDATABASE[ON[PRIMARY][[，…N]][，[，…N]]][LOGON{[，…N]}]其中，[[，…N]]表示設置文件屬性，格式如下。([NAME=邏輯文件名，]FILENAME=‘物理文件名稱’[，SIZE=數據庫文件的初始容量值][，MAXSIZE={物理文件的最大容量值|UNLIMITED}][，FILEGROWTH=增加容量值])[，…N]其中[[，…N]]表示設置文件組屬性，格式如下。FILEGROUP文件組名稱[DEFAULT][，…N],2）參數說明（1）放在“[]”中的“”表示整個“[]”括起來的選項都可省略，如果不省，則“”括起的選項不能省。使用“｜”分隔的多個選項，表示只能選擇其中一個。（2）數據庫的名稱必須符合標識符規(guī)則，最長為128個字符。數據庫名稱在SQLServer的實例中必須唯一。數據庫的邏輯文件名是數據庫在SQLServer中的標識符。FILENAME指定數據庫物理文件名稱和路徑，它和數據庫邏輯名稱一一對應。文件組的邏輯名稱必須在數據庫中唯一，不能是系統(tǒng)提供的名稱PRIMARY和PRIMARY_LOG。（3）“ON”定義數據文件；“PRIMARY”定義主文件組中的文件；“LOGON”定義日志文件。一個數據庫只能有一個主文件，如果沒有定義主文件，列在數據文件項的第一個文件就是主文件。,3．使用Transact-SQL語言創(chuàng)建數據庫,（4）數據庫文件容量單位可以是KB，MB，GB，TB，缺省值為MB，長度必須為整數，主文件的，最小容量是Model數據庫的主文件長度；對于其他類型文件，最小長度為512KB。（5）MAXSIZE：指定物理文件的最大容量。如果不設置文件的最大尺寸，那么文件的增長最大值將是磁盤的所有空間。UNLIMITED選項允許文件增長到磁盤已滿。（6）FILEGROWTH：指定文件每次增加容量的大小或百分比，基數為當前文件大小。當FILEGROWTH=0時，表示文件不增長。（7）DEFAULT：指定命名文件組為數據庫中的默認文件組。,圖4.11用命令創(chuàng)建Studentcourse數據庫界面,CREATEDATABASEstudentcourseONPRIMARY(NAME=studentcourse,FILENAME=C:\DATA\studentcourse.mdf,SIZE=3072KB,MAXSIZE=UNLIMITED,FILEGROWTH=1024KB),FILEGROUP[group2](NAME=secondsc,FILENAME=C:\mydb\secondsc.ndf,SIZE=3072KB,MAXSIZE=51200KB,FILEGROWTH=1024KB)LOGON(NAME=studentcourse_log,FILENAME=C:\LOG\studentcourse_log.ldf,SIZE=1024KB,MAXSIZE=20480KB,FILEGROWTH=10%),3）創(chuàng)建學生選課數據庫,4.1.3管理數據庫,1．查看數據庫信息,1）命令格式EXECsp_helpdb[數據庫名]2）功能查看指定數據庫的相關數據文件信息、數據庫擁有者、創(chuàng)建時間等信息。若缺省數據庫名，則顯示所有數據庫信息?！纠?.1】查看學生選課“studentcourse”數據庫的信息。方法一：使用SQLServerManagementStudio查看數據庫信息方法二：使用系統(tǒng)存儲過程命令查看數據庫信息。EXECsp_helpdbstudentcourse【例4.2】查看所有數據庫信息。EXECsp_helpdb,4.1.3管理數據庫,2．打開數據庫,1）命令格式USE2）功能使指定數據庫成為當前數據庫【例4.3】打開學生選課“studentcourse”數據庫。方法一：使用SQLServerManagementStudio打開數據庫方法二：使用命令。USEstudentcourse,3．修改數據庫,1）命令格式AlterDatabase數據庫名{AddFile[,…N][ToFilegroup文件組名稱]|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:\ProgramFiles\MicrosoftSQLServer\MSSQL.1\MSSQL\DATA\studentcourse2.ndf,SIZE=2MB,MAXSIZE=50MB,FILEGROWTH=1MB)tofilegroupgroup2GoalterDATABASEstudentcoursemodifyfile--修改studentcourse主數據文件的文件增長為2MB(NAME=studentcourse,FILEGROWTH=2MB)GoalterDATABASEstudentcourseaddLOGfile--新增studentcourse_log2日志文件(NAME=Nstudentcourse_log2,FILENAME=C:\ProgramFiles\MicrosoftSQLServer\MSSQL.1\MSSQL\DATA\studentcourse_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|Truncateonly}]2）功能壓縮指定數據庫?！纠?.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數據系統(tǒng)視圖,1．Sysobjects系統(tǒng)視圖2．Syscolumns系統(tǒng)視圖3．Sysindexes系統(tǒng)視圖4．Sysusers系統(tǒng)視圖5．Sysdatabases系統(tǒng)視圖6．Sysdepends系統(tǒng)視圖7．Sysconstraints系統(tǒng)視圖,4.2數據表的創(chuàng)建,１．數據類型及其確定原則２．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創(chuàng)建數據表結構,1．熟悉CREATETABLE語句的格式1）CREATETABLE語句的簡化格式CREATETABLE[{服務器名.[數據庫名].[架構名].|數據庫名.[架構名].|架構名.}]數據庫表名(列名數據類型[NOTNULL][identity(初值,步長)][DEFAULT默認值][UNIQUE][PRIMARYKEY][CLUSTERED|NONCLUSTERED][，列名數據類型[NOTNULL][DEFAULT默認值][UNIQUE][，…n]][，列名AS計算列值的表達式[，…n]][，[CONSTRAINT主鍵約束名]PRIMARYKEY(屬性名)][，[CONSTRAINT檢查約束名]CHECK(邏輯表達式)[，n]][，[FOREIGNKEY(外鍵屬性)REFERENCES參照表(參照屬性)[，…n]])[ON{文件組|默認文件組}],2）CREATETABLE語句的說明,【例4.11】創(chuàng)建數據庫“book_shop”的數據表book，數據表由書號、書名、出版社、出版日期、單價、數量、總價（單價*數量）、電子郵件地址和數據庫表使用者字段組成。其中書號列定義為主鍵并且為系統(tǒng)自動編號即標識列，種子值（起始值）為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】創(chuàng)建數據庫學生選課“studentcourse”的數據表S、C、SC，數據表結構如表3.6，表3.7，表3.8所示。各表的完整性約束如表3.9，表3.10，表3.11所示。方法一：使用SQLServerManagementStudio創(chuàng)建數據表方法二：使用SQL命令。1）創(chuàng)建課程表C的語句如下：CREATETABLEC(課程號Char(3)NOTNULL,課程名Varchar(20)NOTNULL,學分SmallintNULL,預選課程號Char(3)NULL,教師Char(8)NULL,CONSTRAINTFK_PcnoFOREIGNKEY(預選課程號)REFERENCESC(課程號),CONSTRAINTCK_CnoCHECK(課程號Like[0-9][0-9]),CONSTRAINTPK_CPRIMARYKEYCLUSTERED(課程號ASC))ON[PRIMARY],2）CREATETABLE語句的說明,【例4.12】創(chuàng)建數據庫學生選課“studentcourse”的數據表S、C、SC，數據表結構如表3.6，表3.7，表3.8所示方法二：使用SQL命令。2）創(chuàng)建學生基本信息表S的語句如下：CREATETABLES(學號Char(6)NOTNULLDEFAULT(J0400),姓名Char(8)NOTNULL,性別Char(2)NOTNULL,出生日期DatetimeNOTNULLDEFAULT(19800101),系Varchar(20)NOTNULL,電話Char(8)NULL,CHECK(學號Like[A-Z][0-9][0-9][0-9][0-9]),CHECK(性別=女OR性別=男),CHECK(電話Like[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]),CONSTRAINTPK_SPRIMARYKEYCLUSTERED(學號ASC))ON[PRIMARY],2）CREATETABLE語句的說明,【例4.12】創(chuàng)建數據庫學生選課“studentcourse”的數據表S、C、SC，數據表結構如表3.6，表3.7，表3.8所示各表的完整性約束如表3.9，表3.10，表3.11所示。方法二：使用SQL命令。3）創(chuàng)建學生選課數據表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）命令格式ALTERTABLE{ALTERCOLUMN類型（寬度）[NULL|NOTNULL]|ADD類型（寬度）[NULL|NOTNULL][完整性約束][,…n]|DROPCOLUMN[[CASCADE︱RESTRICT]][,…n]|DROP[CONSTRAINT|ALL][,…n]}2）功能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）功能重命名當前數據庫中的指定數據表名?！纠?.16】重命名數據庫bookshop的數據表book名稱，改為“書籍資料”。方法一：使用SQLServerManagementStudio方法二：使用SQL命令。Sp_rename‘book’,‘書籍資料’,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查詢語句（本節(jié)暫不介紹），第三種是使用INSERT命令。1）命令格式INSERTINTO數據表名(列名表)VALUES(元組值)INSERTINTO數據表名(列名表)SELECT查詢語句INSERTINTO數據表名(列名表)DEFAULTVALUES2）功能向指定數據表的屬性列插入數據，VALUES后跟的元組值為屬性列提供數據。其中列名表中的屬性排列順序和VALUES后跟的元組值的排列順序要一致。對應的數據類型要一致。如果沒有指定列名表，則表示數據表中的所有屬性列?！癉EFAULTVALUES”選項會將默認值插入到該屬性列中，如果某列沒有默認值，允許則向該列插入空值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，則修改表中的所有元組。但在進行修改操作時，需注意數據庫的一致性?！纠?.21】更新s表中的所有行出生日期列中的值變?yōu)樵錾掌谥导?。方法一：使用SQLServerManagementStudio方法二：使用SQL命令USEStudentcourseGOUPDATEsSET出生日期=出生日期+1,2．更新數據,【例4.22】將選C01課程的學號是L0401的學生的成績改成85分。USEStudentcourseGOUPDATEscSET成績=85WHERE課程號=C01AND學號=L0401‘GOSELECT*FROMscGO【例4.23】將張云龍學生的成績減少5分。USEStudentcourseGOUPDATEscSET成績=成績-5WHERE學號IN(SELECT學號FROMsWHERE姓名=張云龍)GOSELECT*FROMsc,2．更新數據,1）命令格式DELETEFROM基本表名[WHERE條件表達式]2）功能刪除表【例4.24】刪除學號為L0401的學生選課信息。USEStudentcourseGODELETEFROMscWhere學號=L0401‘GOSELECT*FROMsc【例4.25】從SC表中刪除所有行USEStudentcourseGODELETEFROMscGOSELECT*FROMsc,3．刪除記錄,4.刪除所有行,1）命令格式TRUNCATETABLE[{數據庫名.[架構名].|架構名.}]表名[;]2）功能使用TRUNCATETABLE命令刪除所有行?！纠?.26】從學生選課數據表SC中刪除所有行。TRUNCATETABLEstudentcourse.DBO.scGOSELECT*FROMsc,1．索引的作用1）加速數據檢索2）優(yōu)化查詢3）強制數據完整性2．索引的分類1）聚集索引2）非聚集索引3）唯一索引,4.4索引管理,4.4.1索引概述,1）命令格式CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]INDEX索引名ON數據表名|視圖名（字段名表[ASC|DESC][,…n]）[WITH[PAD_INDEX][[,]FILLFACTOR=填充因子][[,]IGNORE_DUP_KEY][[,]STATISTICS_NORECOMPUTE]][ON文件組名]2）功能只有在指定的索引名稱存在時，才能使用DROP＿EXISTING選項，該項說明首先刪除指定表的索引后再重新構造它?！癠NIQUE”表示建立唯一索引。CLUSTERED表示建立聚集索引，NOCLUSTERED表示建立非聚集索引。,4.4.2創(chuàng)建索引,4.4.2創(chuàng)建索引,表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創(chuàng)建索引,1）命令格式DROPINDEX索引名[,…n]2）功能刪除指定的索引?？梢粤谐龆鄠€要刪除的索引名。利用DROPINDEX命令刪除通過定義PRIMARYKEY或UNIQUE約束創(chuàng)建的索引，必須先刪除指定的約束。在系統(tǒng)表的索引不能使用DROPINDEX刪除。刪除表中的聚集索引，將使表中的所在非聚集索引重建?！纠?.32】刪除數據庫“studentcourse”中，數據表sc中的索引IN_成績、數據表c中的索引IN_課程名。UsestudentcourseDROPINDEXsc.IN_成績,c.IN_課程名,4.4.3刪除索引,1．查看表中的索引1）命令格式sp_helpindex[@objname=]表或視圖的名稱‘2）功能報告有關表或視圖上索引的信息，當前數據庫中表或視圖的名稱的數據類型為nvarchar(776)?！纠?.33】查看“studentcourse”數據庫中的數據表S上索引的類型。運行結果如圖4.59所示。UsestudentcourseGOsp_helpindexs,4.4.4查看索引,【例4.36】修改在【例4.27】中創(chuàng)建的索引IX_teacher，修改后的索引基于“教師”和“課程名”，成為組合索引。,4.4.5修改索引,