用t-sql語句完成創(chuàng)建和管理數(shù)據(jù)庫操作練習

《用t-sql語句完成創(chuàng)建和管理數(shù)據(jù)庫操作練習》由會員上傳分享，免費在線閱讀，更多相關內容在應用文檔-天天文庫。

1、·創(chuàng)建數(shù)據(jù)庫語句語法：CREATEDATABASE數(shù)據(jù)庫名[ON[PRIMARY]{(NAME=數(shù)據(jù)文件的邏輯名稱，F(xiàn)ILENAME=‘數(shù)據(jù)文件的路徑和文件名’,(要包含文件擴展名)SIZE=數(shù)據(jù)文件的初始容量，(不能小于Model數(shù)據(jù)庫的初始文件大小)MAXSIZE=數(shù)據(jù)文件的最大容量，F(xiàn)ILEGROWTH=數(shù)據(jù)文件的增長量)}[,…n]LOGON{(NAME=事務日志文件的邏輯名稱，F(xiàn)ILENAME='事務日志文件的物理名稱'，SIZE=事務日志文件的初始容量，MAXSIZE=事務日志文件的最大容量，F(xiàn)ILEGROWTH=事務日志文件的增長量)}[,…n]]

2、判斷同名的數(shù)據(jù)庫是否存在：IFEXISTS(SELECT*FROMmaster..sysdatabasesWHEREname='數(shù)據(jù)庫名')DROPDATABASE數(shù)據(jù)庫名GO修改數(shù)據(jù)庫操作語句：ALTERDATABASE數(shù)據(jù)庫名｛[addfile文件描述][tofilegroup文件組名]新增數(shù)據(jù)文件｜[addlogfile文件描述]新增日志文件｜[removefile邏輯文件名]刪除數(shù)據(jù)庫文件｜[addfilegroup文件組名]新增文件組｜[removefilegroup文件組名]刪除文件組｜[modifyfile文件修改內容描述]修改數(shù)據(jù)文件屬性｝查看數(shù)

3、據(jù)庫文件屬性EXECsp_helpfile系統(tǒng)存儲過程，用于查看數(shù)據(jù)庫有哪些文件及文件屬性。刪除數(shù)據(jù)庫Dropdatabase數(shù)據(jù)庫名稱分離數(shù)據(jù)庫EXECSp_detach_db數(shù)據(jù)庫名稱附加數(shù)據(jù)庫CREATEDATABASE數(shù)據(jù)庫名ONPRIMARY(FILENAME=’’)說明：附加數(shù)據(jù)庫只需指定數(shù)據(jù)庫的主數(shù)據(jù)文件。FORATTACH如果移動過數(shù)據(jù)庫文件，則需要指出所有移動過的文件。用存儲過程也可以附加數(shù)據(jù)庫：sp_attach_db數(shù)據(jù)庫名，filename=’’創(chuàng)建數(shù)據(jù)庫例：createdatabasecompanyonprimary(name=comp

4、any_data,filename='d:mssq17datacompany.mdf',size=10,maxsize=unlimited,filefrowth=10%)logon(name=company_log,filename='d:mssq17datacompany.ldf',size=1,maxsize=5,filegrowth=1)創(chuàng)建一個指定多個數(shù)據(jù)文件和日志文件的數(shù)據(jù)庫createdatabaseemployeesonprimary(name=employee1,filename='d:mssq17dataemployee1.m

5、df',size=10,maxsize=unlimited,filegrowth=10%),(name=employee2,filename='d:mssq17dataemployee2.mdf'size=20,maxsize=100,filegrowth=1)logon(name=employeelog1,filename='d:mssq17dataemployee1.ldf',size=10,maxsize=50,filegrowth=1),(name=employeelog2,filename='d:mssq17dataemployee2

6、.ldf'size=10,maxsize=50,filegrowth=1)修改數(shù)據(jù)庫例：1.增大數(shù)據(jù)庫文件alterdatabase產(chǎn)品數(shù)據(jù)庫Modifyfile(name='產(chǎn)品數(shù)據(jù)庫_log',size=3)將產(chǎn)品數(shù)據(jù)庫的日志文件的初始值增加到3MB2.壓縮數(shù)據(jù)庫execsp_dboption'數(shù)據(jù)庫名','autoshrink','true'dbccshrinkfile('數(shù)據(jù)庫名_data',6)3.修改數(shù)據(jù)庫結構alterdatabaseemployeesaddfilegroupdata1alterdatabaseemployeesaddfile(na

7、me=employee3,filename='d:employee3.ndf',size=1,maxsize=50,filegrouth=1),(name=employee4,filename='d:employee3.ndf',size=2,maxsize=50,filegrouth=10%)tofilegroupdata1alterdatabaseemployeeesaddlogfile(name=employeelog3,filename='d:employeelog3.ldf',size=1,maxsize=50,filegrouth=1)4.刪除

8、數(shù)據(jù)庫dropdatab