SQL Learning to create student management database

1. First, before learning to create a database, as shown, let's first describe the type of database:

650) this.width=650; "Src=" Http://s2.51cto.com/wyfs02/M02/8A/95/wKioL1g07YOB3bfEAABOxWnrXK8639.png-wh_500x0-wm_3 -wmp_4-s_2606502671.png "title=" qq picture 20161123091343.png "alt=" Wkiol1g07yob3bfeaaboxwnrxk8639.png-wh_50 "/>

from the database Application Management Perspective , the SQL Server database is divided into the system database and the user database

Second, what is the system database and the user database? What are their respective roles?

System Database: (1) Definition: It is SQL Server database management system self-bringing and automatic maintenance , for storing information about the proper operation of the maintenance system

(2) Composition: Master, which is an important database for recording all system-level information in the database management system, and if it is corrupt, SQL Server does not work properly

Model, which provides the templates and prototypes needed to create a user database

tempdb, which holds temporary tables and other temporary stored procedures, is a workspace 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, and temporary tables and stored procedures are automatically deleted when the user disconnects from the database.

msdb, which provides the storage required for scheduling information and job history, for agent dispatch alerts and jobs, and so on.

User database: It is used to store data about the user's business, where the data is maintained by the user itself

Note: Generally speaking, the establishment of the database is a user database, generally do not have operational permissions on the system database

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

First look at the composition of the database:

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/8A/C0/wKiom1g6o_2DPdufAAAj5BScFlU347.png "title=" QQ picture 20161127171354.png "alt=" Wkiom1g6o_2dpdufaaaj5bscflu347.png "/>

Data features of file and log files:

Database files	Function
Main data files	Hold the startup information, some or all of the data and database objects in DB
Secondary data files	Store data and database objects in addition to the primary database file
Log file	Used to store the transaction log information needed to recover the database, and to record the database update status

Note: The use of secondary data files and note data files is not different for users, users do not need to care about their data files are placed on which data file

3, about the storage allocation of the database:

(1) When considering the spatial allocation of a database, you need to understand the following rules:

A, all databases contain one primary data file with one or more transaction log files, and can also contain 0 or more secondary data files

b, when creating the user database, the model database is automatically copied to the new user database, and is copied to the primary data file, so the size of the primary data file is at least 5MB size

C, the storage allocation unit of data in the database is the data page;

One page is a piece of contiguous disk space of 8KB (8*1024 bytes)

A page is the smallest space allocation unit that stores data, and the size of the page determines the maximum size of a row of data in a database table

D, a row of data from a table is not allowed to be stored on a different page in SQL Server

4. Properties of database files:

(1) file name and 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 primary data file must be greater than the model database primary database file size (usually 5MB)

(3) Growth mode

(4) Maximum size

5. Create student database in interface mode:

The database can be created in the SSMs tool, here is an example of SQL Server2014

First, start the SSMs tool (the SSMs tool searches the SSMs tool on the disk where you installed the SQL Server, and then create a shortcut to use it later) to determine that the server is connected correctly and enter the main interface of SSMS

650) this.width=650; "Src=" Http://s1.51cto.com/wyfs02/M00/8A/C0/wKiom1g6rj6yA7mbAAB-Bi6Pej8668.png-wh_500x0-wm_3 -wmp_4-s_198506644.png "title=" qq picture 20161127175744.png "alt=" Wkiom1g6rj6ya7mbaab-bi6pej8668.png-wh_50 "/>

Next, in the SSMs window, right-click the database object, and in the popup shortcut menu, choose the New Database command to open the New Database window. Enter the database name "student" in which to modify the logical name of the database file, file type, filegroup, initial size, autogrow, and path related properties, respectively, as required. Effect

650) this.width=650; "Src=" Http://s2.51cto.com/wyfs02/M00/8A/BC/wKioL1g6r5CTsxraAADPqM3nDxc847.jpg-wh_500x0-wm_3 -wmp_4-s_4037998781.jpg "title=" qq picture 20161127180348.jpg "alt=" Wkiol1g6r5ctsxraaadpqm3ndxc847.jpg-wh_50 "/>

Second, set the database basic properties as required: Click the autogrow column with the ' three dots ' button to open the Change Student Settings dialog box, where you can change how the file automatically grows by megabytes or by percentage.

650) this.width=650; "Src=" Http://s3.51cto.com/wyfs02/M00/8A/BC/wKioL1g6sE-jLM_JAAAre1JvxM0710.png-wh_500x0-wm_3 -wmp_4-s_2095214537.png "title=" qq picture 20161127180700.png "alt=" Wkiol1g6se-jlm_jaaare1jvxm0710.png-wh_50 "/>

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 side of the window, which you can use to set options such as the collation of the database, recovery settings, compatibility level, and so on. , select "Chinese_prc_ci_as" in the Collation drop-down list box with the default values for other items. Where CHINESE_PRC is the Chinese Simplified character set, CI indicates case-insensitive, as denotes accent sensitivity.

650) this.width=650; "Src=" Http://s2.51cto.com/wyfs02/M01/8A/BC/wKioL1g6sRvD-JX0AAD3VuRVo7Q250.jpg-wh_500x0-wm_3 -wmp_4-s_3064011607.jpg "title=" qq picture 20161127181022.jpg "alt=" Wkiol1g6srvd-jx0aad3vurvo7q250.jpg-wh_50 "/>

Finally, click the OK button to display the progress of the creation. After a successful creation, the new Database window is automatically closed and a child node named "Student" is added under the database node of the Object Explorer window (the right click of the mouse is refreshed).

650) this.width=650; "Src=" Http://s2.51cto.com/wyfs02/M00/8A/BC/wKioL1g6sijz_5OuAAAwKUJcDf8018.png-wh_500x0-wm_3 -wmp_4-s_2473376264.png "title=" qq picture 20161127181348.png "alt=" Wkiol1g6sijz_5ouaaawkujcdf8018.png-wh_50 "/>

650) this.width=650; "Src=" Http://s3.51cto.com/wyfs02/M00/8A/C0/wKiom1g6sj6B7dKPAABqv03B3HE062.jpg-wh_500x0-wm_3 -wmp_4-s_2602497976.jpg "title=" qq picture 20161127181335.jpg "alt=" Wkiom1g6sj6b7dkpaabqv03b3he062.jpg-wh_50 "/>

6. Create a student database in the command mode:

(1) Create the basic format of the T-SQL statement syntax for the database,

Create DATABASE name

On

(

......

)

Log on

(

......

)

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

(2) A specific example titled: Creating 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%

)

This article from "A Growing small Tree" blog, reproduced please contact the author!

SQL Learning to create student management database