sql第2章創(chuàng)建管理數據庫

1、1,,Sql server 2005程序設計與管理基礎,Sql server 2005程序設計與管理基礎,第2章 創(chuàng)建管理數據庫,2,第2章 創(chuàng)建管理數據庫,2.1 SQL SERVER數據庫的結構2.2 創(chuàng)建數據庫 2.3 管理數據庫2.4 分離和附加數據庫,3,2.1 SQL SERVER 數據庫結構,2.1.1 SQL Server的數據庫的結構 2.1.2 SQL Server的數據文件及文件組2.1.3

2、SQL Server的事務日志 2.1.4 SQL Server 的系統數據庫,4,2.1.1 SQL Server的數據庫的結構,1. SQL Server的數據庫數據庫的數據按不同形式組織在一起，構成了不同的數據庫對象。以二維表的形式組織在一起的數據構成了數據庫的表對象，數據庫是數據庫對象的容器。用SSMS連接到數據庫看到的都是邏輯對象，不是存放在物理磁盤上的文件，數據庫對象沒有對應的磁盤文件，整個數據庫對應磁盤上的文件與文件

3、組。,5,,2.1.1 SQL Server的數據庫的結構,6,數據庫,2.1.1 SQL Server的數據庫的結構,圖2.2 數據庫結構圖,7,2.1 SQL SERVER 數據庫結構,2.1.1 SQL Server的數據庫的結構 2.1.2 SQL Server的數據文件及文件組2.1.3 SQL Server的事務日志 2.1.4 SQL Server 的系統數據庫,8,2.1.2 SQL Server 數據庫文件及文件

4、組,SQL Server的數據庫文件的三種類型 1.主數據文件（Primary file） 用來存儲數據庫的數據和數據庫的啟動信息。每個數據庫必須并且只有一個主數據文件，其擴展名為.MDF。實際的文件都有兩種名稱：操作系統文件名和邏輯文件名（T-SQL語句中使用）。,9,2.1.2 SQL Server 數據庫文件及文件組,2.輔助數據文件（Secondary file）

5、 用來存儲數據庫的數據，使用輔助數據庫文件可以擴展存儲空間。如果數據庫用一個主數據文件和多個輔助數據文件來存放數據，并將它們放在不同的物理磁盤上，數據庫的總容量就是這幾個磁盤容量的和。輔助數據文件的擴展名為：.NDF。,10,2.1.2 SQL Server 數據庫文件及文件組,3.事務日志文件（Transaction log） 用來存放數據庫的事務日志。凡是對數據庫進行的增、刪、改等操作，都會記錄在事務日志文件

6、中。當數據庫被破壞時可以利用事務日志文件恢復數據庫的數據。每個數據庫至少要有一個事務日志文件，事務日志文件的擴展名為：.LDF。,11,2.1.2 SQL Server 數據庫文件及文件組,4、文件組文件組的概念 文件組是SQL Server 中一個或多個文件的命名集合，它構成分配或用于數據庫管理的單個單元。文件組的使用場合當有多個磁盤，希望把文件分布在這些磁盤上以提高性能。,Student數據庫,Student_1.m

7、df,Student_2.ndf,Student_3.ndf,Student_log.ldf,C盤,d盤,e盤,f盤,,,,,primary,Studgrp,Studgrp,12,2.1.2 SQL Server 數據庫文件及文件組,文件組的作用：當對數據庫對象寫操作時,數據庫會根據組內數據文件的大小,按比例寫入組內所有數據文件中.當查詢數據時,系統會創(chuàng)建多個單獨的線程來并行讀取分配在不同物理磁盤上的每個文件,從一定程度上提高了查詢

8、速度.,13,文件組的類型:主文件組和用戶定義文件組主文件組：包含主文件的文件組為主文件組 如果主文件組空間耗盡，那么將不能向系統表添加新目錄信息 只要在下列情況下，主文件組才會填滿：關閉了自動增長 選項，或者含有主文件組中文件的所有磁盤空間耗盡。 如果發(fā)生主文件組滿的情況: 要么重新打開自動增長，要 么將其它文件從磁盤上移走以釋放更多的空間。 用戶定義的文件組:指在首次創(chuàng)建或以后更改數據庫時，

9、 用戶明確創(chuàng)建的任何文件組。如果用戶定義的文件組填滿， 那么只有明確地分配到該文件組的用戶表會受到影響,2.1.2 SQL Server 數據庫文件及文件組,14,指定默認的文件組數據庫建立時的默認文件組為主文件組未指定文件組的對象都將放到默認文件組中在任何時候，有且僅有一個文件組被指定為 DEFAULT 文件組 指定默認文件組的方法: Alter database 數據庫名 modif

10、y filegroup 文件組名 default查看文件組信息：,2.1.2 SQL Server 數據庫文件及文件組,15,2.1 SQL SERVER 數據庫結構,2.1.1 SQL Server的數據庫的結構 2.1.2 SQL Server的數據文件及文件組2.1.3 SQL Server的事務日志 2.1.4 SQL Server 的系統數據庫,16,2.1.3 SQL Server的事務日志,1.事務是一組T-SQL

11、語句的集合，這組語句作為單個的工作與恢復的單元。事務作為一個整體來執(zhí)行，對于其數據的修改，要么全都執(zhí)行，要么全都不執(zhí)行。 2.事務日志是數據庫中已發(fā)生的所有修改和執(zhí)行每次修改的事務的一連串記錄。 3.事務日志記錄更改數據的流程。,17,2.1 SQL SERVER 數據庫結構,2.1.1 SQL Server的數據庫的結構 2.1.2 SQL Server的數據文件及文件組2.1.3 SQL Server的事務日志 2.1.4

12、 SQL Server 的系統數據庫,18,2.1.4 SQL Server 的系統數據庫,SQL server 2005的系統構成 數據庫分為兩類有系統數據庫和用戶數據庫。當安裝完成后系統會自動創(chuàng)建四個系統數據庫,并且在其中定義了若干個系統表和系統的存儲過程。 下面分別介紹系統中的四個系統數據庫，這四個數據庫分別是master、model、tempb、msdb,19,2.1.4 SQL Server 的系統數

13、據庫,1、master數據庫 此數據庫記錄了SQL server 2005的所有的服務器機系統信息，所有的注冊賬戶和密碼以及所有的系統設置信息等大量對對系統至關重要的信息，是系統的關鍵性所在，所以他一旦受到破壞，可能會導致這個系統的癱瘓。2、model數據庫 為用戶提供了模板和原型包含了每一用戶數據庫所需要的系統表。它的定制結構可以被更改，因為每當用戶創(chuàng)建新的數據庫時，都是復制model數據庫的模板，

14、所以所作的所有更改都將反映到用戶數據庫當中。,20,2.1.4 SQL Server 的系統數據庫,3、tempdb數據庫 此數據庫保存所有的臨時性表和臨時存儲過程，并滿足任何其他的臨時存儲要求。tempdb數據庫是全局資源，在每次啟動時都重新創(chuàng)建，在用戶離開或系統關機時,臨時數據庫中的臨時表將被刪除。4、msdb數據庫 此數據庫用來存儲計劃信息、備份和恢復相關信息、SQL server代理程序調度報

15、警和作業(yè)調度等操作。,21,第2章 創(chuàng)建管理數據庫,2.1 SQL SERVER數據庫的結構2.2 創(chuàng)建數據庫 2.3 管理數據庫2.4 分離和附加數據庫,22,2.2 創(chuàng)建數據庫,2.2.1 用SSMS圖形界面創(chuàng)建數據庫 2.2.2 用T-SQL命令創(chuàng)建數據庫,23,,2.2.1 用SSMS圖形界面創(chuàng)建數據庫,例1、運用SSMS建立STUDENT數據庫.1) 數據庫的文件名為STUDENT_DATA，物理文件名為E:\

16、SQL\STUDENT_DATA.MDF，初始大小為3MB。文件組為PRIMARY，文件屬性為文件自動增長，文件增長限制為20MB。2） 日志文件為STUDENT_LOG,物理文件名為E:\SQL\STUDENT_LOG.LDF，初始大小為1MB。3） 輔助數據文件為STUDENT_DATA1物理文件名為E:\SQL\STUDENT_DATA1.NDF。初始大小為1MB，文件組為PRIMARY，文件屬性為文件自動增長，文件增長限制為

17、20MB。,24,練習,用SSMS創(chuàng)建職工信息管理數據庫，數據庫名為employee，將數據文件和日志文件存放在e:\sql中，并設置數據文件大小為3MB，文件增長率為15%；日志文件大小為1MB，文件增長率為10%。,25,2.2 創(chuàng)建數據庫,2.2.1 用SSMS圖形界面創(chuàng)建數據庫 2.2.2 用T-SQL命令創(chuàng)建數據庫,26,2.2.2 用T-SQL命令創(chuàng)建數據庫,使用Transact-SQL語言創(chuàng)建數據庫，語法如下：CREA

18、TE DATABASE database_name[ON]{ [PRIMARY] [FILEGROUP groupname] (NAME=logical_file_name,FILENAME=’os_file_name’,[,SIZE=size][,MAXSIZE={max_size|UNLIMITED}][,FILEGROWTH=grow_increment])}[,…n]LOG ON{(NAME=logical_f

19、ile_name,FILENAME=’os_file_name’[,SIZE=size][,MAXSIZE={max_size|UNLIMITED}][,FILEGROWTH=growth_increment])}[,…n]],27,2.2.2 用T-SQL命令創(chuàng)建數據庫,各參數說明如下：database_name ：要建立的數據庫名稱。PRIMARY：該參數在主文件組中指定文件。若沒有指定 PRIMARY關鍵字，該語句

20、中列的第一個文件成為主文件LOG ON：指定建立數據庫的事務日志文件。NAME：指定數據或事務日志文件的名稱。FLENAEM：指定文件的操作系統文件名稱和路徑。os_file_name中的路徑必須為安裝SQL服務器的計算機上的文件夾。,28,2.2.2 用T-SQL命令創(chuàng)建數據庫,SIZE：指定數據或日志文件的大小，單位可以用KB、MB等。 MAXSIZE：指定文件能夠增長到的最大長度，單位可以用KB、MB等。如果沒有

21、指定最大長度，文件將一直增長到磁盤滿為止。 FILEGROWTH：指定文件的增長量，該參數不能超過MAXSIZE的值。默認單位在KB，也可以指定用MB單位。也可以使用百分比。如果沒指定參數，默認百分之十，最小64K。,29,2.2.2 用T-SQL命令創(chuàng)建數據庫,例2：用T-SQL語句創(chuàng)建學生信息管理數據庫，數據庫名為student, 將數據文件和日志文件存放在d:\stud中,數據文件和日志文件的初始大小為3MB，增長幅度為1MB，

22、文件大小限制為20MB。,30,2.2.2 用T-SQL命令創(chuàng)建數據庫,CREATE DATABASE studentON(NAME=’student_data’, FILENAME=’d:\stud\student_data.mdf’, SIZE=3MB, FILEGROWTH=1MB, MAXSIZE=20MB) LOG ON(NAME=’student_log’,

23、FILENAME=’d:\stud\student_log.ldf’, SIZE=3MB,FILEGROWTH=1MB,MAXSIZE=20MB)GO,31,課堂練習,1、創(chuàng)建圖書數據庫BOOK，數據庫文件和事務日志文件放在指定目錄下（E:\SQL\book_data)，數據庫文件和事務日志文件大小擴充方式和容量：初始大小3MB，增長幅度為1MB，文件大小限制為20MB。,32,2.2.2 用T-SQL命令創(chuàng)建數據庫,例3：某

24、公司生產許多產品，數據量大，需要建立產品信息數據庫。數據庫命名為product.其數據文件和事務文件分別需要3個，文件信息為:,33,2.2.2 用T-SQL命令創(chuàng)建數據庫,CREATE DATABASE product ON PRIMARY (NAME =‘p1_DATA', FILENAME=‘d:\product\ product_DATA1.mdf ' , SIZE = 3MB, FILEGR

25、OWTH = 15%),FILEGROUP USERGROUP(NAME =‘p2_DATA', FILENAME = ‘d:\product\ product_DATA2.ndf ' , SIZE = 3MB, MAXSIZE=10MB,FILEGROWTH = 15%),(NAME =‘p3_DATA', FILENAME = ‘d:\product\ product_DATA3.ndf &#

26、39; , SIZE = 3MB, MAXSIZE=20MB,FILEGROWTH = 15%),34,2.2.2 用T-SQL命令創(chuàng)建數據庫,LOG ON (NAME =‘p1_log', FILENAME = ‘d:\product\product_log1.ldf' , SIZE = 2MB, FILEGROWTH = 10%),(NAME =‘p2_log', FILENAME = ‘d:

27、\product\product_log2.ldf' , SIZE = 2MB, FILEGROWTH = 10%)GO,35,第2章 創(chuàng)建管理數據庫,2.1 SQL SERVER數據庫的結構2.2 創(chuàng)建數據庫 2.3 管理數據庫2.4分離和附加數據庫,36,2.3 管理數據庫,2.3.1 打開數據庫2.3.2 查看數據庫2.3.3 數據庫更名2.3.4 修改數據庫2.3.5 收縮數據庫2.3.6 修

28、改數據庫選項2.3.7 數據庫的刪除,37,2.3.1 打開數據庫,1.在SSMS中打開數據庫，單擊要打開的數據庫即可。2.在“SQL語句編輯器”中，使用USE語句打開并切換數據庫 ，語法為：USE database_name 3.在“SQL語句編輯器”中，直接通過數據庫下拉列表框 打開并切換數據庫。,,38,2.3 管理數據庫,2.3.1 打開數據庫2.3.2 查看數

29、據庫2.3.3 數據庫更名2.3.4 修改數據庫2.3.5 收縮數據庫2.3.6 修改數據庫選項2.3.7 數據庫的刪除,39,2.3.2 查看數據庫信息,1. 用T-SQL命令查看數據庫信息 語法格式為： [EXECUTE ] sp_helpdb database_name 例6：查看數據庫northwind信息。EXEC sp_helpdb ‘northwind’,40,2.3 管理數據庫,2.3.1

30、 打開數據庫2.3.2 查看數據庫2.3.3 數據庫更名2.3.4 修改數據庫2.3.5 收縮數據庫2.3.6 修改數據庫選項2.3.7 數據庫的刪除,41,2.3.3 數據庫更名,1、語法格式： alter database database_name modify name=new_database_name 例7：更改數據庫“student”的名稱為“stud”。命令語句如下：

31、USE master GO  ALTER DATABASE student MODIFY NAME=stud GO,42,2.3.3 數據庫更名,2、存儲過程語法格式： EXEC sp_renamedb oldname,newname 例8：更改數據庫“stud”的名稱為“student”。命令語句如下： EXEC sp_rename

32、db stud, student GO,43,2.3 管理數據庫,2.3.1 打開數據庫2.3.2 查看數據庫2.3.3 數據庫更名2.3.4 修改數據庫2.3.5 收縮數據庫2.3.6 修改數據庫選項2.3.7 數據庫的刪除,44,2.3.4 修改數據庫,,2.3.4.1 增加數據庫容量2.3.4.2 增加數據庫文件或文件組,45,2.3.4.1 增加數據庫容量,1）使用SSMS增加數據庫容量

33、 在SSMS右擊要增加容量的數據庫（如“student”數據庫），彈出快捷菜單，選擇屬性命令，打開student數據庫的屬性對話框，在屬性對話框中對數據庫文件的分配空間進行重新設定。[注意] 重新指定的數據庫分配空間必須大于現有空間,否則不會對該文件的大小進行修改并提示出錯信息.,46,2.3.4.1 增加數據庫容量,2）增加數據庫容量的T-SQL語句為： ALTER DATABASE database_nam

34、e MODIFY FILE (NAME=file_name, SIZE=newsize ),47,2.3.4.1 增加數據庫容量,例9：為STUDENT數據庫增加容量，原數據庫文件STUDENT_DATA的初始分配空間為3M，現在將STUDENT_DATA的分配空間增加至5M,增長率為15%。操作命令如下：USE masterGOALTER DATABASE st

35、udentMODIFY FILE(NAME= student _data,SIZE=5MBFILEGROWTH=15% ),48,2.3.4.2 增加數據庫文件或文件組,1、使用T-SQL語句增加數據庫文件組： ALTER DATABASE database_name ADD FILEGROUP groupname,2、增加數據庫文件(到某個組中)，T-SQL語句為： AL

36、TER DATABASE database_name ADD [LOG] FILE (NAME=file_name, FILENAME=‘os_file_name’, SIZE=newsize, MAXSIZE=maxsize, FILEGROWTH=file_increatment ) [TO FILEGROUP grou

37、pname],49,2.3.4.2 增加數據庫文件或文件組,例10：使用T-SQL語句修改學生信息管理數據庫,在該數據庫下增加一個數據文件student_data2.ndf,存放在d:\student下，文件初始大小為2mb，增長率為15%。 T-SQL語句為：Alter database studentAdd file(name=student_data2，Filename=‘d:\student\student_data

38、2.ndf’，Size=2mb,Filegrowth=15%)Go,50,2.3 管理數據庫,2.3.1 打開數據庫2.3.2 查看數據庫2.3.3 數據庫更名2.3.4 修改數據庫2.3.5 收縮數據庫2.3.6 修改數據庫選項2.3.7 數據庫的刪除,51,2.3.5 收縮數據庫,收縮數據庫容量1)使用SSMS收縮數據庫容量 在SSMS右擊要增加容量的數據庫（如“student”數據庫），彈出快

39、捷菜單，選擇屬性命令，打開student數據庫的屬性對話框，在屬性對話框中選擇“選項”頁，選擇“其它選項”中的“自動”，將其自動收縮屬性設置為“true”。,52,2.3.5 收縮數據庫,2)使用T-SQL語句收縮數據庫DBCC SHRINKDATABASE 語句DBCC SHRINKDATABASE (數據庫名 [, 目標百分比] [, {NOTRUNCATE | TRUNCATEONLY}]) 目標百分比:指收縮后文件中

40、的 可用空間大小所占的百分比.如果不給出目標百分比,則數據庫的容量收縮至最小.NOTRUNCATE 選項:執(zhí)行收縮時將實際數據移動,而文件末尾的可用空間不釋放給操作系統，文件的物理大小不會更改,數據文件好像沒有收縮。 TRUNCATEONLY 選項:將文件末尾的所有可用空間收回給操作系統。但在文件內不執(zhí)行任何頁移動操作。指定的文件只能收縮到上一次分配的大小 收縮后的數據庫不能比 model 數據庫更小 ,且收縮后的目標大小始終不小

41、于文件的最小大小,53,2.3.5 收縮數據庫,例11:使用T-SQL語句收縮學生信息管理數據庫student容量至最小./* 在收縮數據庫前將此數據庫設定為單用戶模式*/EXEC SP_dboption ‘student’,’single user’,‘true’goDBCC SHRINKDATABASE （’STUDENT’）GOEXEC SP_dboption ‘student’,’single user’,‘fals

42、e’go,54,3)收縮數據庫中的一個數據文件使用SSMS收縮數據文件DBCC SHRINKFILE 語句DBCC SHRINKFILE ({文件名 | 文件id} [, 目標大小][, { EMPTYFILE | NOTRUNCATE | TRUNCATEONLY}]) EMPTYFILE注意：將所有數據從指定文件中遷移到同一文件組中的其 它文件。該選項允許使用 ALTER DATABASE 語句

43、 除去文件。如果想刪除以被清空的數據庫文件，可以使用如下T-SQL命令語句：ALTER DATABASE database_name REMOVE FILE file_name,2.3.5 收縮數據庫,55,2.3.5 收縮數據庫,例12：使用T-SQL語句修改學生信息管理數據庫，清空（移動）數據文student_data2.ndf中的數據到同文件組中的其他文件中，并刪除該數據文件。實現的T-SQL

44、語句:DBCC SHRINKFILE(‘student_data2’,emptyfile)GOALTER DATABASE student REMOVE FILE student_data2GO,56,2.3.5 收縮數據庫,4)自動收縮數據庫 ALTER DATABASE 數據庫名 SET AUTO_SHRINK ON/OFF,57,2.3 管理數據庫,2.3.1 打開數據庫2.3.2 查看數據庫

45、2.3.3 數據庫更名2.3.4 修改數據庫2.3.5 收縮數據庫2.3.6 修改數據庫選項2.3.7 數據庫的刪除,58,2.3.6 修改數據庫選項,1.使用SSMS設定和修改數據庫選項 2.使用T-SQL語句查看和設定數據庫的選項 1)查看數據庫選項可以使用系統存儲過程，語句如下： EXEC sp_dboption ‘database_name’2)修改數據庫選項可以使用存儲過程，命令格式如下：

46、 EXEC sp_dboption [database_name,option_name,{TRUE|FALSE}],59,2.3.6 設定修改數據庫選項,例13：查看數據庫“student”的選項。命令語句如下： EXEC sp_dboption ‘student' GO例14：更改數據庫student為只讀狀態(tài)，命令語句如下： EXEC sp_dboption ' stud

47、ent ','read only',TRUE GO,60,2.3 管理數據庫,2.3.1 打開數據庫2.3.2 查看數據庫2.3.3 數據庫更名2.3.4 修改數據庫2.3.5 收縮數據庫2.3.6 修改數據庫選項2.3.7 數據庫的刪除,61,2.3.7 刪除數據庫,1.使用SSMS對象資源管理器刪除數據庫 在SSMS對象資源管理器中展開“數據庫”文件夾，單擊要刪除的數據庫（如”

48、student”)，單擊鼠標右鍵，在彈出的菜單中單擊“刪除”命令。,62,2.3.7 刪除數據庫,2.使用T-SQL語句刪除數據庫語法格式為：DROP DATABASE database_name [,database_name]3.使用系統存儲過程刪除數據庫Exec sp_dbremove database_name注意:任何時候刪除數據庫前都應備份數據庫master。無法刪除系統數據庫msdb、model、maste

49、r 、tempdb,63,2.3.7 刪除數據庫,例15：使用T-SQL語句與或系統存儲過程刪除student 數據庫。實現的T-SQL語句:Drop database studentGO或EXEC sp_dbremove studentGO,64,第2章 創(chuàng)建管理數據庫,2.1 SQL SERVER數據庫的結構2.2 創(chuàng)建數據庫 2.3 管理數據庫2.4分離和附加數據庫,65,2.4 分離和附加數據庫,SQL

50、Server 2005 允許分離數據庫的數據和事務日志文件，然后將其重新附加到另一臺服務器，甚至同一臺服務器上1、分離數據庫分離數據庫將從 SQL Server 刪除數據庫，但是保持組成該數據庫的數據和事務日志文件中的數據庫完好無損分離數據庫要求沒有用戶正在使用數據庫，數據庫中沒有未完成的事務，并且在內存中沒有這個數據庫的臟頁一旦數據庫被分離，其條目就從 master 數據庫的 sysdatabases 表中被移除。從 SQL

51、 Server 的角度看，數據庫似乎被刪除了但是其物理文件仍然保存在磁盤上,66,2.4 分離和附加數據庫,1）使用SSMS對象資源管理器分離數據庫 在SSMS右擊要分離的數據庫（如“student” 數據庫），彈出快捷菜單，選擇“任務”下的“分離”命令。,67,2.4 分離和附加數據庫,2）使用系統存儲過程分離數據庫EXEC sp_detach_db database_name例4：使用系統存儲過程分離數據庫stu

52、dent。EXEC sp_detach_db ‘student’GO注意：在分離數據庫的時候要確保沒有人在使用此數據庫，如果數據庫正被使用，在分離前應先斷開連接。,68,2、附加數據庫當附加在數據庫上時，必須指定主數據文件的名稱和物理位置可以指定新的數據庫名字。當日志文件不存在時,附加數據庫后將生成一個新的日志,也可以用此種方法快速收縮日志文件.但當日志文件多于一個此收縮日志的方法將不起作用.,2.4 分離和附加數據庫

53、,69,2.4 分離和附加數據庫,1）使用SSMS對象資源管理器附加數據庫 在SSMS右擊要附加的數據庫（如“student”數據庫），彈出快捷菜單，選擇“任務”下的“附加”命令。,70,2.4 分離和附加數據庫,2）使用系統存儲過程附加數據庫sp_attach_db [ @dbname = ] 'dbname' , [ @filename1 = ] 'filename_n' [ ,...

54、16 ]例5：使用系統存儲過程附加學生信息管理數據庫student。實現語句:EXEC sp_attach_db ‘student’,’d:\stud\student_data.mdf’或EXEC sp_attach_db @dbname =’student ’,@filename1 =’ d:\stud\student_data.mdf ’,@filename2 =’ d:\stud\student_log.ldf ’