Database Management of Database series (1)

Database classification

1 system databases: Master, model, tempdb, msdb

master database : Stores all system-level information for the SQL Server system. Includes: logon account, connection server, and system configuration settings; The master database records all other databases and their location, size, data table information, and also logs initialization information for SQL Server. If the master database is unavailable, SQL Server cannot start.

Model Database : Provides a template for SQL Server to create all databases.

msdb database : Activities that are primarily used by SQL Server agents for replication, job scheduling, and administrative alerts, which are typically used to schedule tasks or troubleshoot.

tempdb Database : Provides a global resource that is common to all users of SQL Server, which holds all temporary tables and temporary stored procedures. Each time you start SQL Server, the tempdb database is recreated.

2 User Database

Database files

The master data file, master , contains the startup information for the database and points to other files in the database. User data and objects are stored in the file.
node data file (. ndf): optional file for saving data. Used to distribute data across multiple disks, each database can have multiple secondary data files.
transaction log file log data file (. ldf): used to record various operating conditions on the database.

A database should have at least one master data file and one transaction log file, which can have multiple node data files and multiple log files.
Logical name: Specify the database file and transaction log file, by default the file name of the database is the same as the database name, and the file name of the transaction log file is a "_log" by default, but the file names of the two files can be modified directly.
File type: Distinguishes whether the current file is a data file or a log file.
Filegroup: Displays the filegroup to which the current database file belongs, and only one filegroup can exist for a database file.
Initialization: Specify the size of the file initialization, the database file defaults to 3MB, log file default 1MB, can also be modified according to the actual situation.
Autogrow: As time goes on, data content becomes more and more, and the space needs to be allocated continuously. Different auto-growth modes can be set. (Explain the difference between the two)
Path: Specifies where the file is stored. By default, SQL Server sets the storage path to the data subdirectory under the installation directory

How the database is managed

• SQL Server Management Studio
• SQL statements
  

Database management

1 Create

--Create a syntax formatCREATE  DATABASEDatabasesname on［Primary] (name='Databases_data',--represents the logical name of a database fileFileName=' Path \databases_data.mdf ',--represents the physical file name of the database filesSize=XXMB,--indicates the initial size of the database fileMaxSize=XXMB,--represents the maximum size of a database fileFileGrowth=Xx--indicates the growth rate of the database file) [Log  on] (name='Databases_log',--represents the logical name of the log fileFileName=' Path \databases_log.ldf ',--represents the physical file name of the log fileSize=XXMB,--indicates the initial size of the log fileMaxSize=XXMB,--indicates the maximum size of the log fileFileGrowth=Xx--indicates the growth rate of log files)

--Create instance one: Master data file + log fileCreate DatabaseSystop on  Primary(  /*The following are the properties of the main data file*/name=' Systop',--The logical name of the master data file Filename= ' E:\Systop\DB\Systop.mdf',--physical path to the master data fileSize=3MB,--Initial size of master data fileMaxSize=Unlimited--maximum value of the master data fileFileGrowth=Ten%  --growth rate of master data files)Log  on(  /*The following are the properties of the log file*/name=' Systop_log',--The logical name of the log file Filename= ' E:\Systop\DB\Systop_log.ldf',--physical path of the log fileSize=1MB,--Initial size of the log fileFileGrowth=Ten%  --growth rate of log files)Go--Create instance two: Master data file + secondary data file + log fileCreate DatabaseSystop on  Primary(  /*The following are the properties of the main data file*/name=' Systop',--The logical name of the master data file Filename= ' E:\Systop\DB\Systop.mdf',--physical path to the master data fileSize=3MB,--Initial size of master data fileMaxSize=Unlimited--maximum value of the master data fileFileGrowth=Ten%  --growth rate of master data files),(/*The following are the properties of the secondary data file*/name= 'Systop_sec1 ',--logical name of the secondary data fileFileName=' E:\Systop\DB\Systop_sec1.ndf',--The physical path of the secondary data file SIZE=3MB, the initial size of the secondary data file filegrowth=10%-The growth rate of the secondary data file) log on (/* Below is the properties of the log file */name= ' systop_log
    ',--logical name of the log fileFileName=' E:\Systop\DB\Systop_log.ldf ',--physical path of the log fileSize=1MB,--Initial size of the log fileFileGrowth=2MB--growth rate of log files)Go

2 determining whether a database exists

The system comes with the Master System database, and sysdatabases is one of the system data tables that holds some information about all the databases.   use masterSelect* from sysdatabases

3 Deleting a database

--GrammarDrop DatabaseDatabase name--instance OneDrop DatabaseSystop--Example two: The correct way to delete a database UseMaster--set the current database to masterGoif exists(Select *  fromsysdatabaseswhereName='Systop')   Drop DatabaseSystop

Using SQL Server database management system removal

Once deleted, the database files are also deleted.

4 Modifying the database

ALTER DATABASE Databasesname
ADD file < file format > [to FILEGROUP file group name]
| ADD LOG file < document Format >
| REMOVE File Logical file name
| ADD FILEGROUP File Group name
| REMOVE FILEGROUP File Group name
| MODIFY file < format >
| MODIFY FILEGROUP file group name, filegroup properties

Database Management of Database series (1)