SQL Sever database Common execution statements

--Using the master database
Use master

--Create a database file
Create DATABASE name
On
（
Name=,--Logical Name
Filename=. NDF,--Data file physical path name
size=,--File Initial Size
Maxsize=,--the maximum size of the file
Filegrowth=-Automatic growth
）

--Create a database log file
Log on
（
Name=,--Logical Name
Filename=. LDF,--Log file physical path name
size=,--File Initial Size
Maxsize=,--the maximum size of the file
Filegrowth=-Automatic growth
）
Go

--Modify the name of the database
ALTER DATABASE name
Modify name = new names

--add a data file to the database

add file

name=,--logical name
Filename=. ndf ,--Data file physical path name
size=,--file Initial size
maxsize=,--Maximum size of file
filegrowth=-autogrow
)
Span style= "FONT-SIZE:16PX;" >go

--Add a log file to the database
ALTER DATABASE TestDB
Add log file
（
Name=,--Logical Name
Filename=. LDF,--Log file physical path name
size=,--File Initial Size
Maxsize=,--the maximum size of the file
Filegrowth=-Automatic growth
）
Go

--delete data files or log files
ALTER DATABASE TestDB
Remove file Data file logical name
Go

ALTER DATABASE TestDB
Remove file log file logical name
Go

--Modify the data file or log file
ALTER DATABASE TestDB
Modify File
(
name=,--Logical name
filename=. NDF,--Data file physical path name
size=,--File Initial size
maxsize=,--the maximum size of the file
filegrowth=-Automatic growth
)
Go
Modify File
(
name=,--Logical name
filename=. LDF,--Log file physical path name
size=,--File Initial size
maxsize=,--the maximum size of the file
filegrowth=-Automatic growth
)
Go

--Detach database (execute stored procedure)
EXEC sp_detach_db Database name
Go

--Attaching a database (executing a stored procedure)
exec sp_attach_db @dbname =,--Additional logical name
@filename1 =,--Data File physical path name
@filename2 =--log File physical path name
Go

--Delete Database
Use master
Go

Drop database name
Go

--Data type

Kinds:
Numeric data type: Int,bigint,smallint,tinyint,decimal,mumeric,float,real,money,smallmoney,bit

Character data type: Char,varchar,nchar,nvarchar,ntext text

Date and time: Datetime,smalldatetime,date,time,datetime2,datetimeoffsef

Binary data type: binary,varbinary,image

Other data types: uniqueidentifier,xml,timestamp,sql_variant

Integral type:
BigInt (The range of values is ( -2^63) to (2^63-1)), storing 8 bytes. (not commonly used)
Int (The range of values is ( -2^31) to (2^31-1)), storing 4 bytes.
smallint (values range from ( -2^15) to 2^15 1), storing 2 bytes.
tinyint (values range from 0 to 255), 1 bytes stored.

Floating-point types:
Float (storage depends on the value of N)
Real (store 4 bytes)

Character type:
Char type is fixed length
VARCHAR type is variable length

Date Type:
DateTime (Values range from January 1, 1753 to December 31, 9999), with an accuracy of 3.33 milliseconds.
smalldatetime (Value range from January 1, 1900 to June 6, 2079), accuracy 1 minutes.

Other types:

A) fixed-length data types and variable-length data types for storing large non-Unicode characters, Unicode characters, and binary data:
ntext: The type length is variable Unicode, with a maximum length of 2^30-1 characters, and a storage size of twice times the number of characters entered.
Text: variable-length non-Unicode data in the server code page with a maximum length of 2^31-1 characters, and storage is still 2,147,483,647 bytes when the server code page uses double-byte characters. Depending on the string, the storage size may be less than 2,147,483,647 bytes.
Image: A variable-length binary data, from 0 to 2^31-1 bytes.

b) The character data type used to store the Unicode character data set (nchar length fixed, nvarchar length variable).
nchar[(n) a fixed-length Unicode character data of]:n characters, and the n value must be between 1 and 4000 (inclusive). The storage size is twice times n bytes.
nvarchar[(N|max)]: variable-length Unicode character data, n values from 1 to 4000 (inclusive), max indicates a maximum storage size of 2^31-1 bytes.
The storage size is twice times the number of characters entered and 2 bytes, and the length of the input data can be 0 characters.

c) binary data types used to store binary data in fixed or variable lengths.
binary[(n)]: fixed-length binary data with a length of n bytes, where n is a value from 1 to 8,000. The storage size is n bytes.
varbinary[(N|max)]: variable-length binary data. n can take a value from 1 to 8,000. Max indicates that the maximum storage size is 2^31 bytes-1 bytes. The storage size is the actual length of the input data + 2 bytes. The length of the input data can be 0 bytes.

To create a data table:

--Use the newly created database before creating the table
Use database name
Go

--create data table
create Table data table name
(
--table
--Syntax: Field name data type [constraint]
--constraint can be added, Add a constraint, plus multiple constraints can also

go

--Create a data table to add constraints
CREATE TABLE data table name
(
--identity identity column constraint: autogrow, can only add the constraint on a shaped column
--primary key PRIMARY KEY constraint: cannot be null and value cannot be duplicated
--unique UNIQUE Constraint: Value unique cannot be duplicated, but can be empty
--not NULL non-NULL constraint: value cannot be empty
--check CHECK Constraint: You can set a check condition
--default DEFAULT constraint: You can set a default value
)
Go

--Modify Table
ALTER TABLE table name

--Add columns
ALTER TABLE table name
Features of the Add column name data type column

--Delete Table
DROP table Name

--Delete Columns
ALTER TABLE table name
Drop Column Name

--Modify User name
ALTER TABLE table name
ALTER COLUMN name

--Adding CHECK constraints
ALTER TABLE table name
Add constraint constraint name constraint expression

--Delete a constraint
ALTER TABLE table name
DROP CONSTRAINT constraint name

Constraint type:

PRIMARY KEY constraint (PRIMARY KEY constraint): The primary key column data is unique and is not NULL, short, PK.
Unique constraint (UNIQUE constraint): guarantees that the column does not allow duplicate values, referred to as UQ.
Check constrait: Restricts the allowable values in the column and the relationships between multiple columns, referred to as CK.
Default constraint: Sets the default value for a column, referred to as DF.
External health constraint (FOREIGN KEY constraint): Used to establish a relationship between two tables, you need to specify a master/slave table, or FK.

To add a constraint syntax:
ALTER TABLE table name
Add constraint constraint name constraint type specific constraint description

Data manipulation Language (DML):
Retrieves data from an existing database by a specified combination, conditional expression, or sort, or inserts, deletes, modifies, and other operations on a database table that already exists.
Command:
Select field name from table name
WHERE Current condition

Insert into table name (field name)
Values (value 1, value 2, value ...)

Update table name SET field name = value
WHERE Current condition

Delete from table name
WHERE Current condition

Data definition language (DDL)
Create, modify, or delete various objects in the database, including tables, views, indexes, and so on.
Command:
Create TABLE, create view, CREATE INDEX, ALTER TABLE, DROP table, drop view, DROP index

Data Control Language (DCL)
Used to grant or retract a privilege to access a database, control the time and effect of data access, and monitor the database.
Command:
GRANT, REVOKE

Transaction Control Language (TCL)
The statement used to manipulate the transaction commit or rollback operation.
Command:
Commit, Rollback

Logical operators:
The and logical language (true if two Boolean expressions are true).
Not logical non (negates the value of any other Boolean operator).
or logical OR (true if one of the two Boolean expressions is true)

Comparison operators
Between (true if the operation is lost within a range).
In (True if the operand is equal to one in the expression list).
Like (true if the operand matches a pattern).

Wildcard characters:
% (any string that contains 0 or more characters).
_ (underscore) (any single character).

To add a data operation:
Insert data:
1) Insert into table name (field 1, field n ...)
Values (value 1, value 2, value ...)

2) insert INTO table name
Select field from new table name

Create a table that is not created directly:
3) Select field into not created new table name form table name

4) insert INTO table name
Values (value 1), (value 2), (value 3)

5) insert INTO table name
Select value 1 Union value 2 Union value 3

To modify data operations:
Updade table Name set field name (without conditions)
Updade table Name Set field name 1, field name 2 where current condition (conditional)

To delete a data operation:
Delete from table name (without conditions)
Delete from table name where current condition (with condition)

Delete all the data from the table:
TRUNCATE TABLE name

Querying Data operations:
--Query all rows for all columns
SELECT * FROM table name

--Query part of the row section column
Select field 1, Field 2, field n from table name

--Query all columns of a section row
SELECT * FROM table name
WHERE Current condition

--Query part of the row section column
Select field 1, Field 2, field n from table name
WHERE Current condition

--Specify a column query and set a column name for each column (equivalent to taking an alias)
The first way:
Select alias 1 = field Name 1, alias 2 = field Name 2, alias n= field name n from table name

The second way:
Select field Name 1 as Alias 1, field name 2 as Alias 2, field name N as alias n from table name

GROUP BY group Query:
The first way:
Select field Name 1 from table name Group By field name 1

The second way:
Select field Name 1 from table name
WHERE Current condition
Group By field name 1

The third method:
Select field Name 1 from table name
Group By field name 1
Having count (*) range

The fourth method:
Select field Name 1 from table name
WHERE Current condition
Group By field name 1
Having count (*) range

Order by query after ordering:
First method (Descending):
SELECT * FROM table name
Order BY field name Desc

The second method (ascending):
SELECT * FROM table name
Order BY Field name ASC (ASC not written, default is ascending)

The third method (sorted by multiple different types):
SELECT * FROM table name
Order BY field name 1 desc (descending), field name 2 desc (Descending)

Top take the first few data:
The first method (for example, take the first 5 lines):
Select Top 5 * from table name

The second method (with 10%):
Select top percent * from table name

Use the "+" symbol for multi-column merging and querying

Note:

1, may have overlooked some, but basically very complete.

2, Ben Boven for the original, is a personal accumulation of notes.

SQL Sever database Common execution statements