Shulou(Shulou.com)06/02 Report--

This article mainly talks about "what are the basic programming methods of SQL Server database". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "what are the basic programming methods of SQL Server database"?

Go batch statement

Used to execute multiple statements simultaneously

Use, switch database use mastergo creation, delete database method 1,-- determine whether the database exists, delete if (exists (select * from sys.databases where name = 'testHome')) drop database testHomego-- to create the database Set the database file, log file save directory create database testHomeon (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 (select * from sys.databases where name = 'testHome') drop database testHomegocreate database testHome-- belongs to the primary main filegroup by default On primary (--the specific description of the data file name = 'testHome_data',-- the logical name of the master 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 growth of the master data file fileGrowth = 10%-- growth rate of the master data file)-- specific description of the log file The meaning of each parameter is the same as the above log on (name = 'testHome_log', fileName =' c:\ testHome_log.ldf', size = 1MB, fileGrowth = 1MB) go

Method 3 (set secondary data file),

If (exists (select * from sys.databases where name = 'testHome')) drop database testHomegocreate database testHome-- belongs to the primary main filegroup by default On primary (--the specific description of the data file name = 'testHome_data',-- the logical name of the master 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 growth of the primary data file fileGrowth = 10%-- growth rate of the primary data file),-- specific description of the secondary data file (--specific description of the data file name = 'testHome2_data',-- logical name of the primary data file fileName =' c:\ testHome2_data.mdf' -- physical name of the master data file size = 2MB,-- initial size of the master data file maxSize = 50MB,-- maximum growth of the master data file fileGrowth = 10%-- growth rate of the master data file)-- specific description of the log file The meaning of each parameter is the same as the above log on (name = 'testHome_log', fileName =' c:\ testHome_log.ldf', size = 1MB, fileGrowth = 1MB), (name = 'testHome2_log', fileName =' c:\ testHome2_log.ldf', size = 1MB, fileGrowth = 1MB) go basic data type

Exact numeric type

Types

Description

Bigint

The bigint data type is used in situations where integer values may exceed the range supported by the int data type, ranging from-2 ^ 63 to 2 ^ 63-1, with 8 bytes of storage space

Int

Integer data type, ranging from-2 ^ 31 to 2 ^ 31-1, storage space 4 bytes

Smallint

Integer, ranging from-2 ^ 15 to 2 ^ 15-1, storage space 2 bytes

Tinyint

Range from 0 to 255, storage space 1 byte

Bit

You can take an integer data type with a value of 1, 0, or NULL. Every 8 bit accounts for one byte, 16bit has 2 bytes and 24bit has 3 bytes.

Decimal

Numeric data type with fixed precision and decimal places, valid values from-10 ^ 38 + 1 to 10 ^ 38-1

Numeric

Same as above

Money

Data type of currency or currency value, ranging from-922337203685477.5808 to 922337203685477.5807

Smallmoney

Currency type,-214748.3648 to 214748.3647

Approximate number type

Types

Description

Float

Represents the approximate numeric data type of 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

The SQL-92 synonym for real is float (24), ranging from-3.40E + 38 to-1.18E-38,0 and 1.18E-38 to 3.40E + 38

Date time type

Types

Description

Datetime

A data type that represents the date and time of a day, ranging from January 1, 1753 to December 31, 9999

Smalldatetime

Range from January 1, 1900 to June 6, 2079

String type

Types

Description

Char

Fixed or variable length character data types ranging from 1 to 8000 bytes

Text

The maximum length is 2 ^ 31-1

Varchar

Fixed or variable length character data type with a maximum storage size of 2 ^ 31-1 byte

Unicode string type

Types

Description

Nchar

Character data type, fixed in length, must be between 1 and 4000

Nvarchar

Variable length Unicode character data. The maximum storage size is 2 ^ 31-1 byte

Ntext

Variable length Unicode data with a maximum length of 2 ^ 30-1 (1073741823) characters

Binary string type

Binary string type

Types

Description

Binary

Fixed-length binary data with a length of n bytes, ranging from 1 to 8000. The storage size is n bytes.

Varbinary

Variable length binary data. N can take a value from 1 to 8000. The maximum storage size is 2 ^ 31-1 byte

Image

Variable length binary data, from 0 to 2 ^ 31-1 (2147483647) bytes

Determine whether a table or other objects and columns exist-- determine whether a table or object exists if (exists (select * from sys.objects where name = 'classes')) print' existence'; goif (exists (select * from sys.objects where object_id = object_id ('student')) print' existence'; goif (object_id ('student',' U' is not null) print 'existence' Go-determines whether the column name exists Delete if (exists (select * from sys.columns where object_id = object_id ('student') and name =' idCard')) alter table student drop column idCardgoif (exists (select * from information_schema.columns where table_name = 'student' and column_name =' tel')) alter table student drop column telgo creation if it exists, Delete table-determine whether the current tableif (exists (select * from sys.objects where name = 'classes')) drop table classesgocreate table classes (id int primary key identity (1) exists 2), name varchar (22) not null, createDate datetime default getDate () goif (exists (select * from sys.objects where object_id = object_id ('student')) drop table studentgo-- create tablecreate table student (id int identity (1,1) not null, name varchar (20), age int, sex bit, cid int) go add fields to the table, modify fields, delete fields-add fields alter table student add address varchar (50) not null -- modify field alter table student alter column address varchar (20);-- Delete field alter table student drop column number;-- add multiple fields alter table student add address varchar (22), tel varchar (11), idCard varchar (3) -- determine whether the column name exists. If so, delete if (exists (select * from sys.columns where object_id = object_id ('student') and name =' idCard')) alter table student drop column idCardgoif (exists (select * from information_schema.columns where table_name = 'student' and column_name =' tel')) alter table student drop column telgo add, delete constraints-add new columns, constrain alter table student add number varchar (20) null constraint no_uk unique -- add primary key alter table student add constraint pk_id primary key (id);-- add foreign key constraint alter table student add constraint fk_cid foreign key (cid) references classes (id) go-- add unique constraint alter table student add constraint name_uk unique (name);-- add check constraint alter table student with nocheck add constraint check_age check (age > 1); alter table student add constraint ck_age check (age > = 15 and age

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.