Database of SQL Basics

1. Basic Concepts

　The first thing to emphasize is that our database is managed by the database system, and we will end up with the database system when we log into the database and operate on it. Can be understood to operate on the database is the client, the database system is the server. A database consists of specific data and database objects, which can be regarded as a large container, in addition to storing data and describing the data, such as tables, views, indexes, etc. are objects in this database. We know that there are 4 system databases in the database, which is an important part of the database system to store data and record related information during operation. Their role is as follows.

(1) The master database, like its name, is the most important database in SQL, and SQL Server is able to run properly without the database being destroyed. It contains system configuration information such as System user's configuration information, user privilege information and so on. Because the master database is very important, it is necessary to back up the master database.

(2) Model database, just as classes created in C # automatically inherit from object, the database we create in the database also has a template created, which is the model database. For example, I added a table test to the model database, and when I randomly created a new database, I found that the test table already exists in the database.

(3) msdb database, which is used primarily to store descriptive information about running a scheduled job that has been created. It is also a very important database, and it is generally recommended that the database be backed up. There are many limitations to this database, and many processes use this database, so it is best not to modify the msdb database.

(4) The tempdb database, which acts as a staging database to hold some temporary data. When SQL Server shuts down, the database disappears, and when SQL Server restarts, tempdb is rebuilt. About Tempdb also note that you cannot create temporary tables too casually, otherwise when too much of a table causes the current task to fail, and the entire server may not work.

A data file and log file in a database with a suffix of. mdf and. LDF, and a data file that ends in MDF as the primary data file. We can also specify a secondary data file for the database, which ends with a. NDF, which can have only one master data file in a database, but can have multiple secondary data files. For ease of administration and data allocation, you can specify a filegroup for a file, one file cannot belong to multiple filegroups at the same time, and you cannot specify a filegroup for the log file. When you create a filegroup that contains multiple files, when you save the table to a filegroup, the system can simultaneously query multiple files in the filegroup in parallel, which can provide a shorter time to find efficiency. The smallest physical unit that can be managed in SQL2008 is the page, with a page of 8KB, which is 8192 bytes. 8 consecutive pages for a single area, which is the size of 64KB. For a table, each row of data cannot be more than one page, each page in addition to storing data will also hold some file basic information, so that a row of data can occupy a maximum space of 8,060 bytes. There is a special case for this rule, which is that some text data types with nvarchar and varchar in the column can exceed this limit. The following is a SQL operation on the database.

Create Databasedbtest on Primary(Name=test_maindata, filename='D:\test_maindata.mdf', size=5, FileGrowth=Ten%), filegroup group1 (name=test_data1, filename='D:\TEST_DATA1.NDF', size=2, FileGrowth=Ten%), (name=test_data2, filename='D:\TEST_DATA2.NDF', size=2, FileGrowth=Ten% )Log  on    --Note Log files cannot be placed in filegroups(Name=test_log1, filename='D:\test_log1.ldf', size=2, FileGrowth=1MB), (name=test_log2, filename='D:\test_log2.ldf', size=2, FileGrowth=2MB)--View basic information about a databaseSelect *  fromsys.databases--to view information about a database fileSelect *  fromsys.database_files--View information about a database group, which shows two rows of data, the first behavior MDF file, and the second behavior group1Select *  fromsys.filegroups--View basic and status information about a database fileSelect *  fromsys.master_files--View the status of the database,SelectDATABASEPROPERTYEX ('dbtest','Status')--View space usageexecsp_spaceused--View database basic information, no more sys.databases informationexecsp_helpdb--Modify the name of the database, obviously this should be cautiousAlter DatabaseDbtest Modify Name=hahahaexecSp_renamedb'hahaha','dbtest'--detaching a databaseexecsp_detach_db dbtestCreate Databasedbtest on(Name='Test_maindata', filename='D:\test_maindata.mdf' ) forAttach

2. Database status

　　The database consists of 7 states, which you can use to query sys.databases or perform DATABASEPROPERTYEX functions to obtain state information about the database.

-- Both of These methods can obtain state information for the database Select state_desc,* from sys.databasesSelect databasepropertyex ('  testDb','Status')

Online: The presence or presence of a database, at which time access is performed.

Offline: Offline or offline, the database is not working properly. At this point, you can move and copy the database in this state.

Restoring: Restore state, the database is not available, such as a file that is restoring the primary filegroup, or an offline restore of one or more secondary files.

Recovering: The recovery state, the database is not available, after the successful recovery, the database will be automatically online, if the recovery fails, the database will be flagged as suspect status.

Suspect: The primary filegroup is suspect or corrupted and the database cannot be recovered during SQL Server startup, and the database is not available at this time.

Recovery pending: The recovery is not completed and the database is not available, which means that SQL Server has some resource-related errors during the recovery process and that the database is not corrupt may require certain conditions for the recovery process to start executing.

Emergency: Mainly used for troubleshooting, when the user sets the database state to emergency, this database will be read-only and single-user mode, disable logging, at this time only the role of the sysadmin fixed server can be accessed, And only the role of the sysadmin fixed server can set the database state to emergency.

3. Data type

Basic data types include numeric types, text types, binary data types, date types, currency types, and so on. Numeric types are divided into integer and approximate data types, with 4 integer data types, bigint (8 bytes), int (4 bytes), smallint (2 bytes), tinyint (1 bytes), where bigint, int, and, in addition to tinyint, smallint can store negative numbers, they use the highest bit as the sign bit, and tinyint cannot store negative numbers, it can only store 0~255 integers. Approximate data types including decimal, numeric, real, float,decimal, and numeric data types are completely equivalent except that the name is different, the decimal usage is decimal (p,s), P indicates that the length of the integer part is the length of the fractional part, the range of P is 0~38, and the default is the value between 18,s and 0~p. Due to the large span of decimal precision, the decimal data type is not fixed in order to make better use of the resource, when p is less than or equal to 9 o'clock for 5 bytes, and when P is 38, the number of bytes will be as high as 17 bytes. Floating-point type float and real the biggest advantage is to be able to store a very large range of numbers, the disadvantage is that floating-point types prone to rounding errors, if the data need to be a large scientific calculation, but the accuracy of the data requirements are not very strict, then choose float or real is a good choice. The N range in float (n) is 1~53, and when N is 1~24, 4 bytes of storage is required, and N is 25~53 with 8 bytes of storage. Real is actually the float, real data range is -3.40e+38~-1.18e-38,0,1.18e-38~3.40e+38,float data range is -1.79e+308~-2.23e-308, 0,2.23e-308~1.79e+308.

text data types are char, varchar, text and nchar, nvarchar, NTEXT six data types, of which the first three are non-Unicode types and the last three are Unicode types. The char data type stores data when a character occupies a single byte of storage space, it can store up to 8,000 characters, and when the true data length is less than 8000, the remaining characters are null characters, and when the true data length is greater than 8000, the excess portion is truncated. Char is a fixed type, in order to make more efficient use of space, we can use varchar, it is basically the same as Char is the only difference is that varchar is mutable. Text is designed to solve the length of more than 8,000 byte of the literal, it is used to store the length of more than 8,000 characters of variable text, the maximum length of up to 2 31 square minus 1. Unicode specifies a unique binary encoding for each character in each language, with 2 bytes per character in standard cases, and, of course, other implementations, which is the Unicode translation format (the Unicode conversion form, UTF). This nchar range is 0~4000, but note that nvarchar length in addition to 0~4000, you can also specify nvarchar (max), which is 2 of the 30-square minus 1, Microsoft recommends that designers try to use nvarchar instead of ntext, Because obviously variable nvarchar is more space-saving than ntext. The same ntext range is also 2 of the 30-time minus 1.

Binary data type binary, varbinary, image, and text data types are also recommended for use with varbinary (max) instead of image. The currency data type consists of money and smallmoney, which consists of 8 bytes, where 4 bytes stores the integer portion and 4 bytes stores the fractional portion. The SmallMoney is 2 bytes + bytes altogether 4 bytes. The money and smallmoney fractional parts are only 4 bits, and the excess will be rounded. In addition, the currency type can be preceded by a number with the ￥ symbol. For date types, we use a lot of the datetime data type, in addition to a smalldatetime data type, smalldatetime represents less time range than datetime and low precision. One inconvenient place to use DateTime is that it includes both date data and time data, and sometimes only one of them. The powerful SQL Server has a type of date and specific time data that is specific to the dates data. If the requirement is a datetime type and you want the fractional part of the unit to be more precise, you can also use the DateTime2 data type, which can be up to 7 bits.

Database of SQL Basics