SQL第6周數(shù)據(jù)庫上機(jī)作業(yè)答案

1、第二次(第6周)上機(jī)作業(yè)一、用T-SQL語句在E:DATA文件夾中創(chuàng)建一個教師信息數(shù)據(jù)庫teacher，該數(shù)據(jù)庫包含：（界面方式也要會）1、一個主數(shù)據(jù)文件邏輯名teacherdata1 ，物理名E:DATAtdata1.mdf ,初始容量1MB，最大容量10MB，每次增長量為15%。2、一個輔助數(shù)據(jù)文件邏輯名teacherdata2 物理名E:DATAtdata2.ndf ,初始容量2MB，最大容量15MB，每次增長量為2MB。3、 兩個數(shù)據(jù)文件不單獨(dú)創(chuàng)建文件組，即使用默認(rèn)的PRIMARY組；4、一個事務(wù)日志文件邏輯名teacherlog 物理名 E:DATAteacherlog.ldf ，初

2、始容量500KB，最大容量不受限制，每次增長量為500KB。先確認(rèn)E:DATA文件夾已創(chuàng)建， CREATE DATABASE teacher ON PRIMARY ( NAME = teacherdata1 , FILENAME = E:DATAtdata1.mdf , SIZE =5MB , MAXSIZE= 10 , FILEGROWTH = 15% ) , ( NAME = teacherdata2 , FILENAME = E:DATAtdata2.ndf , SIZE = 2 , MAXSIZE= 15 , FILEGROWTH = 2MB ) LOG ON /* 創(chuàng)建事務(wù)日志文件*

3、/ ( NAME = teacherlog , FILENAME = E:DATAteacherlog.LDF, SIZE = 500 KB ,/* 初始容量，KB單位不能省略 */ MAXSIZE = UNLIMITED ,/* 日志文件最大容量不受限制 */ FILEGROWTH = 500 KB/* 增長量 KB 不能省略 */ )二、修改數(shù)據(jù)庫“teacher”，增加兩個文件組，文件組名：Gteacher1和Gteacher2。ALTER DATABASE teacherAdd filegroup Gteacher1ALTER DATABASE teacherAdd filegroup

4、 Gteacher2三、增加一個輔助數(shù)據(jù)文件，文件邏輯名teacherdata3，物理名為：E:data teacherdata3.ndf，初始大小為20MB，最大100MB,增量10MB，歸屬于文件組“Gteacher1”。Alter database teacherAdd file ( NAME = teacherdata3, FILENAME = E:datateacherdata3.ndf, SIZE = 20MB , MAXSIZE = 100, FILEGROWTH = 10MB) to filegroup Gteacher1四、增加一個事物日志文件teacherlog1，物理名E

5、:DATAteacherlog1.ldf ，大小為30MB，最大大小為50MB，每次增長10MB。Alter database teacherAdd LOG file ( NAME =teacherlog1, FILENAME = E:DATAteacherlog1.ldf, SIZE = 30MB , MAXSIZE = 50MB ,FILEGROWTH = 10MB )五、刪除文件組Gteacher2。ALTER DATABASE teacherRemove filegroup Gteacher2六、刪除題四中建立的日志文件teacherlog1.ldfALTER DATABASE teacherremove file teacherlog1七、將數(shù)據(jù)庫teacher的名稱修改為teacheringsystem。alter database teachermodify name=teacher-system八、分離數(shù)據(jù)庫teacheringsystem，再附加數(shù)據(jù)庫teachering