Database reading notes (1)

First, the basic concept

The dbms:database Management system is a large software for manipulating and managing databases that is used to establish, use, and maintain databases.

Rdbms:relational database Management system, which is based on the database management system, is to increase the relationship between data, relationships and data constraints of the three data model to store and manage the data.

sql:structed Query Language is a kind of database query and programming language, which is used for accessing data and querying, updating and managing relational database system.

ide:integrated Development Environment integrated development environment.　

　　The core goal of the database system is to guarantee the data integrity and data access performance on the basis of realizing the functional requirements.

To extend the database:

1 Setting up the database for self-growth mode;

2 increase the size of the data file and log file in the database, that is, modify the MaxSize property;

3 Add a new secondary data file or log file for the database. Optimal

Shrink Database:

1 set the database to automatically shrink, set the Auto_shrink database option implementation;

2 manually execute DBCC SHRINKDATABASE statements to shrink the size of the entire database;

3 Execute the DBCC SHRINKFILE statement to manually shrink the size of the files in the database.

Why use a database?

A. Efficient and consistent structured data access method for complex arbitrary conditions combined with nested data queries (relational database management system even designed a language for this function: SQL)

B. Ensure the atomicity, consistency, separation and persistence of data operations.

C. Ensure that the data remains intact in the event of server outages, network outages, server crashes, and client crashes

D. Quickly use backups on a remote device on a new server to restore data to the state before the disaster occurs when the server is completely physically destroyed

E. Restore data to any time or before any operation at any time.

Atomicity: The atomicity of a transaction means that the program contained in the transaction acts as a logical unit of work for the database, which either executes all of the data modification operations or does not execute at all. This characteristic is called atomicity.
Consistency: Transactional consistency means that the database must be in a consistent state before and after a transaction is executed. This attribute is called transactional consistency. If the state of the database satisfies all the integrity constraints, the database is said to be consistent.

Separation: separation refers to concurrent transactions being isolated from each other.    That is, the operations within a transaction and the data being manipulated must be blocked from being seen by other transactions attempting to modify it. Separation is the security guarantee that the DBMS provides for conflicts between concurrent transactions.

Persistence: persistence means that updates to committed transactions cannot be lost when a system or media failure occurs. That is, once a transaction is committed, the DBMS guarantees that its changes to the data in the database should be permanent and can withstand any system failure. Persistence is ensured through database backup and recovery.

Second, back up the database

A backup is the process of copying a database to a dump device. Where the dump device refers to a tape or disk that is used to place a copy of the database.

Backup limit:

Any backup operation that implicitly or explicitly references offline data will fail.

When the database is in use, SQL Server can use the online backup process to back up the database. insert INTO, UPDATE, delete operations can be performed during the backup process, but the backup operation waits if the database file is being created or deleted when you try to start the backup operation.

All recovery models allow you to back up a single file or filegroup of a full or partial SQL Server database or database. You cannot create a table-level backup.

Backup and restore principles:

As mentioned earlier, the data file MDF preserves the real-time state of the data, and the transaction log file LDF records the process of data changes in the database. At this point, to back up the database, there are naturally two choices:

Because the MDF file holds the real-time state of the data in the database, a copy of the MDF file is made to make the database backup at the current moment.

Since the LDF file holds the entire process of data changes in the database, you can back up the LDF file, then rebuild the entire database from scratch with these backed up transaction log records, and restore the database to any point in time, compared to the first method, through transaction logging.

In fact, SQL Server uses both of these methods.

Data backup:

Data backup is simple, is to copy MDF files.

However, if the database is particularly large, such as dozens of G, copying the entire MDF file over and over will consume a lot of IO resources and disk space. Considering that the contents of the MDF file are saved by paging, and the data in the database does not change in a certain amount of time, it is not necessary to copy the entire MDF file every time the backup is made. Just set a bit bit in the header of each page. Since the last backup, this page has been modified, then, with the last backup, just need to back up those pages that have changed. This is the full backup and differential backup in SQL Server. for the sake of simplifying the design and simplifying the backup recovery process, although SQL Server can make any differential backups after each full backup, all differential backups are relative to the last full backup, not the last differential backup that may exist. Naturally, there must be a full backup before any differential backups.

third, database snapshot

　　A database snapshot is a read-only static view of a database. A database snapshot provides a read-only, static view of the source database when it creates a snapshot, and does not contain uncommitted transactions.

From the moment the snapshot was created, the database snapshot is transactionally consistent with the source database. A database snapshot is always on the same server instance as its source database. When the source database is updated, the database snapshot is also updated. Therefore, the longer a database snapshot exists, the more likely it is to run out of free disk space.  multiple snapshots can exist in a given source database. The snapshot is retained until the database owner explicitly deletes each database snapshot. Database snapshots run at the data page level. Database snapshots Use one or more "sparse files" to store data.

database snapshots are not related to snapshot backups, snapshot isolation of transactions, or snapshot replication.

A typical application of database snapshots in MSDN is

1) Maintain historical data to generate reports.

2) Uninstall the report using a mirrored database that is maintained in order to achieve the availability goal.

3) The impact of data protection from management failures.

4) protect data from user errors.

IV. SQL Server and Transact-SQL

SQL: Structured Query Language, structured query Language.

SQL is a database query and programming language for accessing data and querying, updating, and managing relational database systems. SQL is also the extension of the database script file.

SQL is an advanced, non-procedural programming language that allows users to work on high-level data structures. It does not require the user to specify how to store the data, nor does it require the user to understand how the data is stored, so different database systems with a completely different underlying structure can use the same SQL language as the interface for data entry and management. It takes a collection of records as an Action object, all SQL statements accept the collection as input, and the return set as output, which allows the output of one SQL statement to be input to another SQL statement, so that SQL statements can be nested.

Transact-SQL is: In addition to adding variables, operators, functions, Process Control and annotations, the essence is still the SQL language.

Ddl:data definition language. is the syntax for database object control syntax, the addition, modification, and deletion of database objects (data tables, stored procedures, correspondence fire custom types, etc.). CREATE, ALTER, DROP.

Dml:data manipulation Language. Data manipulation language. Common developers commonly known as CRUD (Create, retrieve, uadate, delete) function, meaning the data is added, intercepted, modified, deleted. (Insert, select, Uadate, delete).

Dcl:data Control LANGUAGE, which manages the security permissions of users and roles on a particular database. Grant: Used to grant a collection of user or role permissions. DENY: Used to explicitly restrict a collection of permissions. REVOKE: Used to revoke a collection of permissions on an object.

V. Database data types

There are 6 types of character data:

String: char, varchar, text;

Unicode strings: nchar, nvarchar, ntext.

What's the difference between 1,char,nchar,nvarchar,varchar?
What is the difference between 2,text and ntext and what is the difference between the various character classes above?
3, about the types with big and band small, such as: Smallint,int and bigint; and what is the difference between smalldatetime,datetime and bigdatetime?

Char and nchar are fixed-length string types, and varchar and nvarchar are variable-length string types. That is, if the field type is char (10), even if the character you enter is ' ABC ', it will be saved as ' ABC ' in the database, and 7 spaces will be automatically prepended to the field.  The use of varchar (10) does not precede the space. Char and nchar when the fill data is empty, the database automatically replaces it with a full space, so that the not NULL is a dummy.  So if the field cannot be empty, be sure to judge in advance in the program. Char and nchar are faster to read and write than varchar and nvarchar because they are fixed in length.

As for the previous n what is different, there are n for Unicode characters, and no n is not supported, and the difference between text and ntext is the same. In general, Unicode is not required when dealing with only Chinese and English, and no special symbols are involved. Another case where Unicode is required is the need to add string data to the SQL statement, and do not want the inside of things such as single quotes to make SQL misunderstanding, you can encode it in Unicode, each character will occupy two bytes, single quotation marks are not recognized by SQL.

The difference between text and char and varchar is that the data for char and varchar fields is saved in the table, while the text field can hold large volumes of text, and the data is kept in another space, and of course there is no difference on the surface.

About the type with big and small. The small representative is a simplified data type that supports a smaller range, but takes up less space. Big is a very large data type, occupying a large space, but the scope of support is also large. For example, smallint only takes one byte, but only 0-255 of the digits are supported.  DateTime is the same. There is no bigdatetime type in SQL.

(1) Binary data type

Binary data includes binary, Varbinary, and Image

Binary data types can be either fixed-length (binary) or variable-length. binary[(N)] is a fixed n-bit binary data. where n is the range of values from 1 to 8000. The size of its storage scenting is n + 4 bytes.

varbinary[(N)] is a binary data of n-bit variable length. where n is the range of values from 1 to 8000. The size of its storage scenting is n + 4 bytes, not n bytes.

The data stored in the Image data type is stored as a bit string, not interpreted by SQL Server, and must be interpreted by the application. For example, applications can store data in the Image data type using BMP, Tief, GIF, and JPEG formats.

(2) Character data type

The types of character data include Char,varchar and Text. Character data is data that is any combination of letters, symbols, and numbers.

Varchar is a variable-length character data whose length does not exceed 8KB.

Char is a fixed-length character data with a length of up to 8KB.

ASCII data over 8KB can be stored using the text data type. For example, because Html documents are all ASCII characters and are typically longer than 8KB in length, these documents can be stored in SQL Server with the Text data type.

(3) Unicode data type

Unicode data types include Nchar,nvarchar and ntext

In Microsoft SQL Server, traditional non-Unicode data types allow the use of characters defined by a specific character set. During the SQL Server installation process, a character set is allowed to be selected. With Unicode data types, any character defined by the Unicode standard can be stored in a column. In the Unicode standard, all characters defined in various character sets are included. With the Unicode data type, the prevailing scenting is twice times the size of the scenting used by non-Unicode data types.

In SQL Server, Unicode data is stored in Nchar, Nvarchar, and Ntext data types. Columns stored with this type of character can store characters in multiple character sets. When the length of a column changes, you should use the nvarchar character type, where you can store up to 4,000 characters. When the length of a column is fixed, you should use the Nchar character type, and you can store up to 4,000 characters at this time. When you use the Ntext data type, the column can store more than 4,000 characters.

(4) Date and time data type

Date and time data types include two types of Datetime and smalldatetime

Date and time data types are made up of valid dates and times. For example, valid date and time data includes "4/01/98 12:15:00:00:00 PM" and "1:28:29:15:01am 8/17/98". The previous data type is the date before, and the time after. The second data type is the time before, and the date after.

In Microsoft SQL Server, date and time data types include datetime and smalldatetime two types when the date range is stored starting January 1, 1753 and ending December 31, 9999 (each value requires 8 Storage bytes). When using the smalldatetime data type, the stored date range starts January 1, 1900 and ends on December 31, 2079 (each value requires 4 bytes of storage).

The format of the date can be set. The commands for formatting dates are as follows:

Set DateFormat {format | @format _var|

Where, Format | @format_var is the order of the dates. Valid parameters include MDY, DMY, YMD, YDM, MYD, and DYM. By default, the date format is MDY.

For example, when the Set DateFormat YMD is executed, the date is formatted as a month and day form;

When the Set DateFormat DMY is executed, the date is in the form of the sun and moon

(5) Numeric data types

Numeric data contains only numbers. Numeric data types include positive and negative numbers, decimals (floating-point numbers), and integers. Integers consist of positive and negative integers, such as 39, 25, 0-2, and 33967.

In Micrsoft SQL Server, the data types stored by integers are int,smallint and Tinyint. The INT data type stores data in a range that is larger than the Smallint data type stores the data, while the Smallint data type stores the range greater than the tinyint data type stores the data.

The range of data that is stored using the INT data is from 2 147 483 648 to 2 147 483 647 (each value requires 4 bytes of storage space).

When using the Smallint data type, the range of stored data ranges from 32 768 to 32 767 (each value requires 2 bytes of storage).

When you use the tinyint data type, the range of stored data is from 0 to 255 (each value requires 1 bytes of storage space).

The exact decimal data type of data in SQL Server is decimal and Numeric. The amount of storage space that this data occupies is determined by the number of bits in the data.

In SQL Server, the data type of the approximate decimal data is Float and Real. For example, One-third of this score is recorded.

3333333, which can be expressed accurately when using approximate data types. Therefore, the data retrieved from the system may not be exactly the same as the data stored in the column.

(6) Currency data indicates the number of positive or negative currencies

In Microsoft SQL Server, the data type of the currency data is money and smallmoney. While the Money data type requires 8 bytes of storage, the SmallMoney data type requires 4 bytes of storage.

(7) Special data types

Special data types include data types that were not previously mentioned. There are 3 special data types, namely timestamp, Bit and uniqueidentifier.

The Timestamp is used to represent the sequence of SQL Server activities in a binary projection format. Timestamp data is not related to inserting data or date and time.

The Bit is made up of 1 or 0. Use the Bit data type when representing true or false, on, or off. For example, ask if a client request for each visit can be stored in a column of this data type.

The uniqueidentifier consists of 16-byte hexadecimal digits that represent a globally unique. GUIDs are useful when the table's record rows require uniqueness. For example, using this data type in the customer Identification Number column can differentiate between different customers.

(8) User-defined data types

User-defined data types are based on the data types that are available in Microsoft SQL Server. You can use a user-defined data type when you must store the same data type in several tables, and to ensure that the columns have the same data type, length, and nullability. For example, you can define a data type called Postal_Code, which is based on the Char data type.

When you create a user-defined data type, you must provide three numbers: the name of the data type, the system data type that you are basing on, and the nullability of the data type.

1) Create a user-defined data type

You can use Transact-SQL statements to create user-defined data types. System stored procedures sp_addtype can be used to create user-defined data types. Its grammatical form is as follows:

sp_addtype {type},[,system_data_bype][, ' null_type ')

Where type is the name of the user-defined data type.  System_data_type are system-supplied data types, such as Decimal, Int, Char, and so on. Null_type indicates how the data type handles null values and must be enclosed in single quotes, such as ' null ', ' NOT null ', or ' nonull '.

Example:

Use Cust

Exec sp_addtype ssn, ' Varchar (one) ', ' not Null '

Creates a user-defined data type SSN, which is based on a system data type that is variable to 11 characters, and does not allow nulls.

Example:

Use Cust

Exec sp_addtype birthday,datetime, ' Null '

Creates a user-defined data type of birthday, which is based on the system data type that is DateTime and allows null.

Example:

Use master

Exec sp_addtype telephone, ' varchar ', ' not Null '

Eexc sp_addtype Fax, ' varchar ', ' Null '

Create two data types, telephone and fax

2) Delete user-defined data types

Can be deleted when the user-defined data type is not required. The command to delete a user-defined data type is sp_droptype {' type '}.

Example:

Use master

Exec sp_droptype ' ssn '

Note: This user-defined data type cannot be deleted when a column in a table is also using a user-defined data type, or if a default or rule is also bound on it.

Database snapshots