SQL Server Foundation

First, common commands

1. Open the SQL Server service using the command line

Command-line tools to get Administrator privileges:

NET start MSSQLSERVER open SQL Server service

NET restart MSSQLSERVER restart the SQL Server service

NET stop MSSQLSERVER shut down the SQL Server service

2, use the command to log in (this method can be used in a computer without SQL Server to operate a computer with SQL Server)

Run: SQPL?

[-s server name] [-u login Name] [-p password]

The 1> indicates that the connection was successful, and then you can enter the SQL statement to proceed.

Ii. Knowledge of basic concepts

1. Database Login Name:

after SQL Server installation, there is a super administrator "sa" (Super Administrator short), generally do not use this user management data, nor can the user's password set to empty, casually put the user action, Setting the password to NULL will bring a large security risk to the database, so it is common to disable or delete the SA account after the installation is complete and other logins have been created.

How to create a new database user I wrote about it in another blog post. Http://www.cnblogs.com/chenyongblog/p/3719408.html

2. System database:

The system database master,model,msdb,tempdb that comes with SQL Server.

Here is a quote from this article: an in-depth understanding of how SQL Server system database works

Master  
The master database holds all the databases placed on the SQL Server entity, and it is the glue that holds the engine together. Because SQL Server does not start if you do not use the primary database, you must be careful to manage the database. Therefore, it is necessary to make regular backups of this database.  

This database includes information such as system logins, configuration settings, connected servers, and general information about other systems and user databases for that entity. The primary database also has an extended stored procedure that provides access to external processes, allowing you to interact with features such as disk subsystems and system API calls. These processes are generally used in modern programming languages such as C + +.  
 
If unfortunately the system crashes and you have to restore the master database, you can refer to MCSE/MCDBA Steven Warren's post on TechRepublic. This article is very thorough and explains some of the special steps needed to recover an important database.  
 
Model 
model is a template database that is used to create a new user database on an entity. You can put any stored procedures, views, users, etc. in the model database, so that when you create a new database, the new database will contain all the objects you put in the model database.  
 
Tempdb 
 
as the name suggests, tempdb has temporary objects, such as global and local temporary tables and stored procedures. ( Here I understand the temporary table of my system, for example, when I'm doing a collection query, I don't want to create a new table, I can save the data through tempdb )
 
This database is recreated every time that SQL Server restarts, and contains objects that are created from the objects defined in the model database. In addition to these objects, tempdb has other objects, such as table variables, result sets from table-valued functions, and temporary table variables. because tempdb retains these object types for all databases on the SQL Server entity, it is important to optimize the configuration of the database.  

Msdb 
The msdb database is used to store information such as database backups, SQL agent information, DTS packages, SQL Server tasks, and replication information such as log transfers.  

3. Documents and types of composition

The database file consists of a data file. MDF/.NDF and at least 1 log files. ldf composition

4. Server role

(1) SysAdmin: Perform any action in SQL Server
(2) serveradmin: Configure server settings
(3) Setupadmin: Installation replication and management expansion process
(4) securityadmin: Manage login and CREATE DATABASE permissions and read audits
(5) Processadmin: Managing SQL Server Processes
(6) DBCreator: Creating and modifying databases
(7) Diskadmin: Managing Disk files

5. Database Roles

(1) db_owner: Users who can perform all the technical actions in the database
(2) Db_accessadmin: Users can be added and deleted
(3) Db_datareader: Users who can view data from user tables in all databases
(4) Db_datawriter: Users who can add, modify, or delete data from user tables in all databases
(5) Db_ddlaadmin: Users who can perform all DDL operations in the database
(6) Db_securityadmin: You can manage security permissions in the database
(7) Db_backoperator: Users who can back up the database (and can publish DBCC and CHECKPOINT statements, which are typically executed before the backup)
(8) Db_denydatareader: Users who cannot see task data in the database
(9) Db_denydatawriter: Users who cannot modify any data in the database

Third, database integrity

1. Entity integrity constraints: Entity integrity requirements each row of data in the table reflects a different entity and cannot have the same data row. by index, UNIQUE constraint, PRIMARY KEY constraint, or by representing a column property.

2, Domain integrity constraints: Domain integrity refers to the validity of a given column input by restricting data types, check constraints, input format, FOREIGN KEY constraints, default values, non-null constraints and other methods, can achieve the domain integrity of the subject.

3. Referential integrity constraints: Referential integrity constraints are used to maintain defined relationships between tables when data rows are entered or deleted. The child table cannot add content that is not associated with a column that has a FOREIGN key constraint set in the primary table. The primary table cannot modify the column with the FOREIGN KEY constraint set, and when the child table has relevant information to match it, the main table cannot delete the column with the foreign key set, and when the child table has relevant information to match, referential integrity is implemented by the reference relationship between the primary key and the foreign key.

4, Custom integrity constraints: User-defined integrity is used to define specific rules, through the rules of the database, stored procedures and other methods to constrain.

Iv. primary key, foreign key

1. Primary key: When some information is not allowed to repeat, this requires a column that uniquely identifies each row in the table and is used to enforce the entity integrity of the table, which is defined as the primary key of the table. A table can have only one primary key, and the primary key constraint ensures that the rows in the table are unique, although some tables allow no primary key, but typically you should set a column as the primary key for the table.

2. Foreign key: When the information of a column in a child table must be entered in the presence of the primary table, a "reference" relationship should be established to ensure that a data item in the child table must exist in the primary table . "Foreign key" is used to achieve this purpose, it is relative to the primary key, is the "child table" corresponding to the "primary table" column, in the child table is called the foreign key or the reference key, its value requirements and the primary table's primary key or unique key corresponding to the foreign key to enforce referential integrity. A table can have more than one foreign key.

3. Composite PRIMARY key: If two or more columns are combined to uniquely identify each row in the table, the primary key is also called a composite primary key. When a composite primary key is used, the data input must be the same as all the columns with the composite primary key set.

4, the main key selection principle:

(1) A minimum is a key with the fewest number of columns, and if you can select from a single primary key and a combined primary key, you should select a single primary key, because it is faster to manipulate a column than to manipulate multiple columns. Of course there are exceptions to this rule, for example, a combination of two integer types is faster than a column of a large character type.
(2) Stability refers to the characteristics of the data in the column, since the primary key is usually used to establish a connection between two tables, so the primary key data should not be updated frequently, ideally, should never change.