SQL Server database creation and basic operations

I. Creating a Database

Create DatabaseTest1--Database name    --Master Data File     on(Name= 'Test1_data',--Master data file nameFileName= 'F:\SQL_study\test1_data\test1.mdf',--Database storage location, note the suffix is MDF, indicating the primary database fileSize=5MB,--Initial Space SizeMaxSize=50MB,--Maximum ValueFileGrowth= Ten%                                --growth ratio/size    )    --log File    Log  on(Name= 'Test1_log', filename= 'F:\SQL_study\test1_data\test1.ldf',--Note the suffix here is. ldf, which represents the log fileSize=2MB, MaxSize=5MB, FileGrowth=1MB);

Create DatabaseTEST2 on    Primary                                                --using primary to indicate the main data file(Name= 'Test2_data', filename= 'F:\SQL_study\test1_data\test2.mdf', size=20MB, MaxSize=Unlimited--indicates no upper limit for maximum valueFileGrowth= Ten%    ),    --secondary data FilesFilegroup Testgroup--file group name, NOTE keyword filegroup(Name= 'Test2_2_data', filename= 'F:\SQL_STUDY\TEST1_DATA\TEST2.NDF',--the suffix of the secondary data file is NDFSize=20MB, MaxSize=Unlimited--indicates no upper limit for maximum valueFileGrowth= Ten%    )    Log  on(Name= 'Test2_log', filename= 'F:\SQL_study\test1_data\test2.ldf', size=50MB, MaxSize=100MB, FileGrowth=10MB)

Note the contents of the comment

Two. Modify the database

Alter  Database Test1--     Use alter to modify the    file                -- use Moidfy file Modified Properties     (        = test1_data,    -- indicates the name of the data file to be modified        = 100MB,    -- change the maximum size of the master data file to 100MB        = 5MB,    -- change to growth mode to 5MB growth     )

Alter Databasetest1Add file                --adding data files to a database Test1bak(Name= 'Test1bak', filename= 'F:\SQL_STUDY\TEST1_DATA\TEST1.NDF', size=10MB, MaxSize=50MB, FileGrowth= 5%    )

The same is also the following:

Add Log file            -- Adding log files Add Filegroup    -- add filegroups    　　　　

Alter Database test1         file test1bak    -- Delete test1bak data file    go    --  The GO command is interpreted as a signal that the current batch of SQL statements should be sent to the SQL Server instance

The same is also the following:

-- Delete a data group    

To add a filegroup's method code:

--add filegroup Fgroup for database test1 and add two data files of size 10MB for this filegroup    Alter Databasetest1AddFilegroup Fgroup--describe the name of the added filegroup    Go    Alter Databasetest1Add file                --two + two data files added here(Name= 'Test_one', filename= 'F:\SQL_STUDY\TEST1_DATA\TEST_ONE.NDF',--because it is a filegroup, it belongs to the auxiliary file, with the NDFSize=10MB), (name= 'Test_two', filename= 'F:\SQL_STUDY\TEST1_DATA\TEST_TWO.NDF', size=10MB) toFilegroup FgroupGo

Remember to add a filegroup to the format, and later to filegroup fgroup that the data files added from go to the code belong to the Fgroup group's

How to delete this filegroup:

    Alter Database test1              file Test_one    -- Delete data files first 1    go    alterdatabase  test1         file test_two    -- Delete data files 2    go    alterdatabase  test1        -- last Delete filegroup

To delete the data file from the file group, and then delete the filegroup

Three. Deleting a database

-- when the drop statement is deleted, it is not recoverable and does not give any hints, so be careful with    -- The system database cannot be deleted, or it will cause the server to be unusable    Drop Database TEST2     Go

SQL Server database creation and basic operations