SQL Server Database Foundation programming

Øgo Batch processing statements

SQL Server interprets go as a signal that the current SQL batch statement should be sent to SQL Server

For executing multiple statements at the same time

Plus the go return result is still 5 results

Divided into 3 batches of execution

Ø use, switch database

Use master

Go

Master is the SQL system database, which records a lot of system information, such as you built a library, in the master library there is a place to record the user database you built all the table name Ah, index what kind of ...

Ø Create, delete database

Method 1,

--Determine if the database exists, delete it

if (exists (SELECT * from sys.databases where name = ' Testhome '))

    Drop Database Testhome

Go

--Create DATABASE, set database file, save directory for log file

Create DATABASE Testhome

On

    name = ' Testhome ',

    filename = ' C:\data\students.mdf '    

)

Log On (

    name = ' Testhome_log ',

    filename = ' C:\data\testHome_log.ldf '

)

Go

Method 2 (Set file size),

if (exists (SELECT * from sys.databases where name = ' Testhome '))

    Drop Database Testhome

Go

Create DATABASE Testhome

--The default belongs to the primary primary filegroup, which can be omitted

On Primary (    

    --specific description of the data file

    name = ' Testhome_data ',                --The logical name of the main data file

    filename = ' c:\testHome_data.mdf ',    --The physical name of the master data file

    Size = 3MB,                        --The initial size of the master data file

    MaxSize = 50MB,                    --maximum value of main data file growth

    filegrowth = 10%                -The growth rate of the master data file

)

--The specific description of the log file, the meaning of each parameter ibid.

Log On (

    name = ' Testhome_log ',

    FileName = ' C:\testHome_log.ldf ',

    Size = 1MB,

    FileGrowth = 1MB

)

Go

Ø Basic Data type

Exact number Type

Type	Describe
bigint	The bigint data type is used when the integer value may exceed the range of the int data type, range: -2^63 to 2^63-1, storage space 8 bytes
Int	Integer data type, range -2^31 to 2^31-1, storage space 4 bytes
smallint	integers, ranges from -2^15 to 2^15-1, storage space 2 bytes
tinyint	range from 0 to 255, storage space 1 bytes
Bit	You can take an integer data type with a value of 1, 0, or NULL, one byte per 8 bit, 16bit for 2 bytes, and 24bit for 3 bytes
Decimal	Numeric data type with fixed precision and number of decimal digits, valid from-10^38 +1 to 10^38-1
Numeric	Ditto
Money	Data type of currency or currency value, ranging from -922,337,203,685,477.5808 to 922,337,203,685,477.5807
SmallMoney	Currency type, -214,748.3648 to 214,748.3647

Approximate number types

Type	Describe
Float	Represents the approximate numeric data type for floating-point numeric data. Floating-point data is approximate, range -1.79e + 308 to -2.23E-308, 0, and 2.23E-308 to 1.79E + 308
Real	Real's SQL-92 synonyms are float (24), range from -3.40e + 38 to -1.18E-38, 0, and 1.18E-38 to 3.40E + 38

Date Time Type

Type	Describe
Datetime	The data type that represents the date and time of day, from January 1, 1753 to December 31, 9999
smalldatetime	range from January 1, 1900 to June 6, 2079

String type

Type	Describe
Char	A fixed-length or variable-length character data type with a range of 1 to 8,000 bytes
Text	Maximum length is 2^31-1
varchar	A fixed-length or variable-length character data type with a maximum storage size of 2^31-1 bytes

Unicode string type

Type	Describe
NChar	Character data type, fixed in length, must be between 1 and 4,000
nvarchar	Variable-length Unicode character data. Maximum storage size is 2^31-1 bytes
ntext	Variable-length Unicode data with a maximum length of 2^30-1 (1,073,741,823) characters

Binary string type

Type	Describe
Binary	Fixed-length binary data with a length of n bytes ranging from 1 to 8,000 values. The storage size is n bytes.
varbinary	Variable-length binary data. n can take a value from 1 to 8,000. The maximum storage size is 2^31-1 bytes
Image	Variable-length binary data, from 0 to 2^31-1 (2,147,483,647) bytes

SQL Server Database Foundation programming