Shulou(Shulou.com)06/01 Report--

1. First of all, before you learn to create a database, as shown in the following figure, let's introduce the type of database:

From the point of view of database application management, SQL server database is divided into system database and user database.

Second, what are system databases and user databases? What are their respective functions?

System database: (1) definition: it is self-contained and automatically maintained by SQL server database management system, and is used to store information to maintain the normal operation of the system.

(2) composition: master, which is used as an important database to record all system-level information in the database management system; if it is damaged, the SQL server will not work properly

Model, which provides templates and prototypes for creating user databases

Tempdb, which holds temporary tables and other temporary stored procedures, is the working space shared by all databases on sql server. Each time sql server starts, a tempdb database is recreated to ensure that the database is always empty; temporary tables and stored procedures are automatically deleted when the user disconnects the database.

Msdb, which provides the storage area needed for scheduling information and job history, for agents to schedule alerts, jobs, and so on.

User database: it is used to store data related to the user's business, which is maintained by the user himself.

Note: generally speaking, the establishment of a database is a user database, and generally there is no permission to operate the system database.

2. What is a database? What is the concept of it?

First take a look at the composition of the database:

Functions of data files and log files:

Database file

Function

Main data file

Store DB startup information, some or all of the data, and database objects

The secondary data file stores the data except the primary database file and the database object log file is used to store the transaction log information needed to restore the database and record database updates.

Note: there is no difference between the use of secondary data files and pay attention to the use of data files. Users do not need to care about which data file their data files are placed on.

3. About the storage allocation of database:

(1) when considering the space allocation of the database, you need to understand the following rules:

All databases contain one primary data file and one or more transaction log files, and can also contain zero or more secondary data files

B. When creating a user database, the model database is automatically copied to the new user database and to the main data file; therefore, the size of the main data file is at least the size of 5MB

C. The storage allocation unit of the data in the database is the data page

A page is a piece of contiguous disk space of 8KB (8cm 1024 bytes)

A page is the minimum space allocation unit for storing data. The size of the page determines the maximum size of a row of data in a database table.

D. A row of data in a table is not allowed to be stored on different pages in SQL server

4. Attributes of database files:

(1) File name and its location, database file each data file and log file has a logical file name and physical storage location

(2) initial size, the size of the main data file must be larger than the size of the main database file of the model database (usually 5MB)

(3) growth mode

(4) maximum size

5. Create the student database in the interface:

You can create a database in the SSMS tool. Here we take SQL Server2014 as an example.

First, start the SSMS tool (the SSMS tool searches the disk where you installed SQL Server for the SSMS tool, and then create a shortcut to make it easier to use later). After making sure that the server is connected correctly, go to the main interface of SSMS

Then, in the SSMS window, right-click the Database object, select New Database from the pop-up shortcut menu, and open the New Database window. Enter the database name "student" and modify the logical name, file type, filegroup, initial size, automatic growth and path of the database file according to certain requirements. The effect is as shown in the figure:

Second, according to the requirements to set the basic properties of the database: click the "automatic growth" column, there are "three points" button, open the "change student automatic growth settings" dialog box, in which you can change the automatic growth of files by megabytes or by percentage. As shown in the picture

Then, set the advanced properties of the database (collation): click options in the upper-left corner of the New Database window, and the options tab appears on the right half of the window. can be used to set the database collation, recovery settings, compatibility level and other options. As shown in the figure, in the collation drop-down list box, select "Chinese_PRC_CI_AS" other items to use the default values. Where Chinese_PRC is a simplified Chinese character set, CI means case-insensitive, and AS means stress-sensitive.

Finally, click the OK button to display the creation progress. After the creation is successful, the New Database window is automatically closed and a child node named "student" is added under the database node of the object Explorer window (right-click to refresh), as shown in the figure

6. Create the student database by command:

(1) the basic syntax format of T-SQL statements for creating a database

Create database database name

On

(

.

)

Log on

(

.

)

Note: if there are multiple data files or log files to use, separate them, but not between data files and log files.

(2) take a specific example: create a database rsgl, including two database files and two log files

Create database rsgl

On

(

Name = rsgl_data1

Filename = "c:\ rsgl1.mdf"

Size = 5MB

Maxsize = 10MB

Filegrowth = 1MB

),

(

Name = rsgl_data2

Filename = "c:\ rsgl2.ndf"

Size = 5MB

Maxsize = 10MB

Filegrowth = 1MB

)

Log on

(

Name = rsgl_log3

Filename = "c:\ rsgl3.ldf"

Size = 1MB

Maxsize = 5MB

Filegrowth = 10%

),

(

Name = rsgl_log4

Filename = "c:\ rsgl4.ldf"

Size = 1MB

Maxsize = 5MB

Filegrowth = 10%

)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.