SQL Learning Summary (1)--Database technology

1.1 Create a database

1.1.1 Create a default database

Create Database Db_sqldata (Db_sqldata is the DB name)

1.1.2 to create a database that specifies the location of the database file

Create DATABASE Mydatabase1

On

(

Name=mydatabase1_data,

Filename= ' E:\mydata\Mydatabase1_data.mdf ',

Size=4,

MAXSIZE=10,

filegrowth=10%

)

Log on

(

Name=mydatabase1_log,

Filename= ' E:\mydata\Mydatabase1_log.ldf ',

Size=1,

Maxsize=6,

Filegrowth=1

)

Note: The SQL statement is case-insensitive, the delimiter for each item is a comma, and the last item has no comma; on () is the description of the data file, and log on () is the description of the transaction log with the following 5 parameters:

1) Name: Logical file name

2) FileName: The path and file name of the database file to be saved

3) Size: Initial database file sizes

4) MaxSize: Maximum value of the database file

5) FileGrowth: The automatic growth rate of the database file can be a percentage, or it can be a specific value

1.1.3 Create a database of multiple data files, multiple log files

Create DATABASE Mydatabase2

On

(

Name=mydatabase21_data,

Filename= ' E:\mydata\Mydatabase_data21.mdf ',

Size=4,

MAXSIZE=10,

filegrowth=10%

),

(

Name=mydatabase22_data,

Filename= ' E:\mydata\Mydatabase_data22.mdf ',

Size=3,

MAXSIZE=10,

Filegrowth=1

)

Log on

(

Name=mydatabase21_log,

Filename= ' E:\mydata\Mydatabase21_log.ldf ',

Size=1,

Maxsize=6,

Filegrowth=1

),

(

Name=mydatabase22_log,

Filename= ' E:\mydata\Mydatabase22_log.ldf ',

Size=1,

Maxsize=6,

filegrowth=10%

)

Note: a comma is added between the two databases, and the file names of the database files are not the same as the saved locations

1.1.4 Create a database of multiple filegroups

Create DATABASE Mydatabase3

On

Primary

(

Name=mydatabase31_data,

Filename= ' E:\mydata\Mydatabase_data31.mdf ',

Size=4,

MAXSIZE=10,

filegrowth=10%

),

Filegroup Newgroup1

(

Name=mydatabase32_data,

Filename= ' E:\mydata\Mydatabase_data32.mdf ',

Size=3,

MAXSIZE=10,

Filegrowth=1

)

Log on

(

Name=mydatabase31_log,

Filename= ' E:\mydata\Mydatabase31_log.ldf ',

Size=1,

Maxsize=6,

Filegrowth=1

)

Note: filegroups are an advanced method for organizing database objects, and by default, the database has only one filegroup primary. By configuring a database with multiple filegroups, you can create new objects (tables, views, and so on) on the specified filegroup. It is also important to note the definition method and position of the common group. Its key word is filegroup.

1.2 Modify Database

1.2.1 Renaming a database

EXEC sp_renamedb mydatabase2,mydatabasenew

1.2.2 Add data files to the database

ALTER DATABASE MYDATABASE1

Add File

(

Name=mydatabase1_new,

Filename= ' E:\mydata\Mydatabase1_new.mdf ',

Size=3

)

Note: in the data file entry for the Add database, the name entry is essential.

1.2.3 increase the log file for the database

ALTER DATABASE MYDATABASE1

Add log file

(

Name=mydatabase1_new_log,

Filename= ' E:\mydata\Mydatabase1_new_log.ldf ',

Size=3

)

1.2.4 Modifying database Files

ALTER DATABASE MYDATABASE1

Modify File

(

Name=mydatabase1_new_log,

Size=3,

Maxsize=5

)

Note: Use the Name property to specify which database file you want to modify, and you can modify the database file size, maximum value, growth rate, and so on.

1.2.5 Delete a database file

ALTER DATABASE MYDATABASE1

Remove File Mydatabase1_new_log

1.3 Deleting a database

Drop Database DATABASE[,^...N]

Note: You cannot use SQL code to delete databases and system databases that are in use

1.4 detaching and attaching databases

Separation: sp_detach_db [@dbname =] ' database_name ' [, [@skipchecks =] ' skipchecks '] [, [@keepfulltextindexfile =] ' Keepfullte Xtindexfile ']

Additional: sp_attach_db [@dbname =] ' dbname ', [@filename1 =] ' Filename_n ' [,... 16]

The return value is 0, succeeded, 1, failed.

1.5 backing up and restoring Databases

1.5.1 fully backing up the database

Backup DATABASE [Mydatabase1] to Disk=n ' e:\mydata\back1 ' with Noformat,noinit,name=n ' multidatabase-full Databases ', Skip, norewind,nounload,stats=10

1.5.2 Differential BACKUP Database

Backup DATABASE [Mydatabase1] to Disk=n ' E:\mydata\back2 ' with differential,noformat,noinit,,name= N ' multidatabase-differential database backup ', skip,norewind,nounload,stats=10

1.5.2 transaction log BACKUP Database

Backup LOG [Mydatabase1] to Disk=n ' E:\mydata\back3 ' with Noformat,noinit,name=n ' multidatabase-transaction log Backups ', Skip,norewind, nounload,stats=10

1.5.3 backing up files and filegroups for a database

Sp_addumpdevice ' disk ', ' zf_backupfile ', ' d:\data\mybackupfile.bak '--Create a backup device

Backup Database Mydatabase1

File= ' Mydatabase1_data ',

filegroup= ' primary '

To Zf_backupfile-backing up the files and filegroups of a database with a backup device

1.5.4 Restoring a database

Restore database [Mydatabase1] from disk=n ' E:\mydata\back1 ' with file=1,nounload,stats=10

SQL Learning Summary (1)--Database technology