SQL Server (i) Introduction to the database SQL Server Environment configuration database basics

Preface

This is my work more than two years later, starting from the most basic SQL start, seriously learn SQL Server, Leackage Check and consolidation have all; because I was just beginning to learn, always upset, recently changed work, found 1-2 years of experience, asked the basic thing or a lot, This time needed is a solid foundation of Kung Fu, so a series of shock + introspection, their own holidays at home from the most basic re-understanding of SQL Server, continue to precipitate a bit. Where there is wrong or need in-depth discussion, please direct message or small window I;

(i) Introduction to databases SQL Server environment Metabase basics

1. What is a database, what are the characteristics of a database, and why use a database?

database is a kind of database management software, which needs to be installed on the computer's operating system;

Data management System (DBMS):D Atabasemanagementsystem

The database has the characteristics:

① mass storage data;

② fast query speed;

③ control of concurrency access;

④ safety;

⑤ the integrity of the data;

2. Different databases, different features

MYSQL: The function is simple, the speed is very fast, the data is not rigorous;

MSSQL Server: can only run on the Windows platform, and. NET has a good combination, more than MySQL function, but the big data processing is not particularly good;

Oracle:

Sqllite:

Access: file-level database;

3. Knowledge of Database Concepts

database: different classes of data into different databases, easy to manage the categories of data, high security, avoid naming conflicts;

table: relational database relationship refers to a table;

columns (column)/field (field)/rows (row) Ganso ;

primary KEY (Primary key): Unique identifier in the data row, only one primary key in a table, divided into: logical primary key and business primary key;

Logical PRIMARY key: The field that does not have any business meaning, it is the field that the program looks at completely;

Business PRIMARY key: Use fields that are meaningful to the business, such as ID/study number ...;

Key features required for primary keys:

① does not allow NULL column as primary key;

② columns with no duplicate data;

③ columns with no actual business logic; (logical primary key)

④ stable columns; (the data in the column does not change frequently)

⑤ Select a single column as the primary key, (when multiple columns are required to uniquely identify a single record in a table, you can select multiple columns to form a primary key, which can be called: Composite primary key/Composite primary key/federated primary key) Generally, it is not recommended to use combined primary key;

problems with data redundancy:

resulting in a waste of storage space;

Update/delete data exception;

In order to deal with the problem of data redundancy, it is necessary to split such a table into two or more tables to store, this time need to use the primary foreign key to the data of several tables together.

foreign KEY (Foreign key), how to use the primary foreign key to unite the data in the table?

When the data is split into two tables to store, each table has its own primary key. One of the tables has a column in addition to its primary key, which is used to refer to the primary key in another table, which is the "Foreign key column" of the table, the table with the foreign key column is called the Foreign key table, and the primary key is referred to as the "primary key table".

Because the data of the foreign key column refers to the data of the primary key column of another table, the data of the foreign key column cannot be filled out arbitrarily and must be the data that already exists for the primary key of the other table.

Installation and environment configuration for 4.SQL server

The installation process has a corresponding version of the graphic tutorial, here I will not write;

After the installation is complete, you need to start the SQL Server service:

Start → run →services.msc and then find the corresponding service that needs to be started;

View Database Version statement: Print @ @Version;

5.SQL Server installation failed, how to clean the uninstall off;

① in the program unload directory, uninstall all the programs with SQL Server words;

② Delete all installation directories;

③ Delete registry: Start → run →regedit; in Microsoft Project under Current_User and Local_machine software with SQL Server removed;

6. Create databases and tables from the designer;

① Each database has only one master data file (. mdf), log file (_log.ldf), and any number of secondary data files (. ndf), multiple transaction log files;

② file group: Multiple data files can be logically divided into a group to facilitate later management and maintenance (backup/Set up the table in the corresponding file group, etc.);

③ Separation: Right-click, detach, and delete links to the database to be detached;

④ Attach: Right-click the database, select Attach, locate the database file under directory;

⑤ offline: Temporarily do not use this database, right click Offline, still in the original directory, need to restore, right click online;

⑥ generate a compatible script to move the database: Right-click on the database → task → generate script, optionally generate down version, optional according to demand;

Note:<1> attach and detach is the same in the case of the database version, the different version will be error;

<2> error: "Saving changes is not allowed." Your changes require that the following tables be deleted and recreated. You have made changes to a table that cannot be recreated or enabled the "Prevent saving changes that require table re-creation" option; Workaround: Tools → options →designer/design → Tick Block save changes that require re-creation of the table;

<3> error: Append database times Wrong ". Access denied: "; Permissions issues, set the database file security as: Everyone;

<4> The lower version is compatible with the higher version, and the higher version is incompatible with the lower version;

7. Data type of the database;

①Image: used to store byte[] types;

② String types:Char,nchar,varchar,nvarchar,text,ntext,varchar (max), nvarchar (max) are string types, the following are the corresponding differences;

<1> difference with N and without N:

Char:char (2), indicating that 2 bytes can be stored, such as: ab,12, Zhao;

Nchar:nchar (2), indicating that two characters can be stored, each character occupies 2 bytes, such as: Mink Cicada, ab,12;

Summarize:

Without N: Storage Chinese occupies 2 bytes, storage number/English , etc. occupies 1 bytes;

With N: 2 bytes per character, regardless of the Chinese/English/digital storage;

The data type with n is the longest 4000,nchar (4000)/nvarchar (4000);

The longest data type without n is 8000,char (8000)/varchar (8000);

<2> the difference with Var and without Var:

CHAR (10): 1;

1111111111;

varchar (10): 1;

1111111111;

Summarize:

without var: fixed length, storage 1 characters, also occupy 10 bytes, will automatically fill 9 spaces, occupy 10 bytes;

with var: variable length, will dynamically reallocate the storage space according to the actual stored character size, relatively save storage space;

10, the storage maximum 10 bytes, whether it is fixed length or variable length, the error will be exceeded;

<3> Large string storage:

SQL 2005 Pre-use: text and ntext;

SQL 2005 uses: varchar (max)/nvarchar (max); Max represents 4GB memory;

Note: ① for double-byte characters, solve with nvarchar;

③bit type: The value of the type of bit in the program and in the query statement is 1 (true) and 0 (false), but the True/false must be entered in the designer;

Note: The detailed data type document, search in the help → index "data type [SQL Server]" will have a detailed explanation of the document;

8. Database of 5 system databases

①Master: records all system-level information for the SQL Server system. For example: Login account information, link server and system configuration settings, record the existence of all other databases, location of data files, initialization information of SQL Server, etc. Unable to start SQL Server if Master does not exist;

②msdb: for SQL Server Agent scheduled alerts and jobs, database timed to perform certain operations, Database Mail, etc.;

③Model: used as a template for all databases created on an instance of SQL Server. Modifications to the model database (database size/collation/recovery model/other database options) will be applied to all databases created later. Create a table in model Creating other databases in the future will have the same table by default;

④tempdb: A workspace that holds temporary objects or intermediate sets. A global resource that can be used by all instance users who are linked to SQL Server. Tempdb is recreated each time SQL Server is started;

⑤Resource: A read-only database. Contains the system objects that SQL Server includes. System objects are physically persisted in the Resource database, but are logically displayed in the SYS schema of each database.

SQL Server (i) Introduction to the database SQL Server Environment configuration database basics