[SQL] SQL Server Basic Syntax

Struct Query Language

1.3NF

A. atomicity

B. No data redundancy

C. Referencing a primary key for another table

2. Constraints

A. Non-null constraints

B. PRIMARY KEY constraints

C. Unique constraints

D. Default Constraints

E. Check constraints

F. FOREIGN KEY constraints

3. Foreign keys and relationships

The foreign key is the constraint, does not do the foreign key processing, the relation also exists.

4.char varchar nchar nvarchar

Char: is a fixed character, and if not enough, it is handled with a space. does not need to calculate the length, the speed is fast.

VARCHAR: Is the size of the dynamic. You need to calculate the length, the speed is slow, the storage occupies small.

Nvarchar: is stored in Unicode encoding. Each character occupies 2 bytes. The Chinese can be displayed correctly in any SQL Server. English is 1 bytes without n. Chinese 2 bytes

5.crud

[SELECT]:

Top 5 before taking 5:select

Top 5 percent before taking 5%:select

Remove Duplicates: SELECT DISTINCT

Condition: SELECT * FROM table name where

[Insert]:insert into Table name [(Field name, field name)] Values (field value, field value)

[Update]:update table name set field name = field value [where]

[Delete]:d elete [from] table name [where]

6. Operator Precedence

[Logical operator]: and OR not (!)

[comparison operator]:< > =! =

Comparison operators generally have higher precedence for logical operators, except not.! is only lower than the precedence of the parentheses.

7. Fuzzy Query

Like:select * from table name where field name like value

_: Matches any one character

%: matches any number of characters

[%]: match character%

8. Null value Processing

Isnull:select ISNULL (Chinese, ' missing test ') from the Scroe English result is empty show missing test

Is (not) Null:select * from Scroe where 中文版 is null The English result is null query out

9. Aggregation functions

SUM AVG COUNT MAX MIN

Aggregate functions are generally used in conjunction with group BY, and have the ability to filter group by after

10. Order of complete query statements

Select Top 5 Distinct * from T where field = Fieldvalue GROUP BY Field2 have field2>5 order by field

1:from T 2:* 3:where 4:distinct 5:group by Field2 6:having field2>5 7:order by 8:top 5

　　

11. Common functions

[Type conversion]:cast (value as type) convert (type, value)

[String]:ltrim (Value) RTRIM (value) LOWER (value) UPPER (value) LEN (value) SUBSTRING (value, start position, length)

[Date]:getdate () Take current time DATEADD (datepart,number,date) plus time Date+num DATEDIFF (datepart,startdate,enddate) Take the difference End-start DATEPART (Datepart,date)

Cancel/force Insert identity column: Set IDENTITY_INSERT table name off/on

Empty table: TRUNCATE TABLE name

12. Copying a table

SELECT * into new table from original table where 1=1

13. Joint queries

When the 2 result set columns are the same, the types are the same and can be combined into a single result set.

Union: Duplicate rows are automatically removed

UNION ALL: Displays all rows.

14. Connection Query

In-between connections and outer joins

Internal connection: [Inner]join

SELECT * FROM T1 inner joins T2 on T1. ID2 = T2.id

The self-connection is a special inner connection, the tree structure is stored: SELECT * from T as T1 join T as T2 where T2. PID = T1.id

　　　　　　

External connection:

Left [outer] join: connected to the right, the contents of the left table are displayed. NULL flag

Right [outer] join: To connect, right to display all the contents of the table. The left is not marked with a null

Full [outer] join: Fully connected, left and right table contents all displayed, no null flag

15. open Window function

You can combine multiple rows into a single area. You can make a statistic of a zone. Different from group BY, you can query all columns.

Open Window function: Over () can be used with order by and partition by (similar group by)

Rank: Rank () over (order by ID)

Sort: row_number () over (order by ID)

Subtotal: AVG (COST) over (partition by costs) (average fee)

16. View

Essentially encapsulates an SQL statement that does not store any data.

Created: CREATE VIEW viewName as SQL statement

View Sql:exec sp_helptext ViewName

17. Business

A transaction is a guarantee that multiple operations succeed at the same time or fail simultaneously

Begin Tran (START transaction) Commit Tran (COMMIT TRANSACTION) ROLLBACK TRAN (ROLLBACK TRANSACTION)

Turn transaction autocommit on/off: set implicit_transactions off/on

18. Stored Procedures

The essence of a stored procedure is to encapsulate a piece of code.

Created: Create proc procname

@param1 int, @param2 int output

As

Set @param2 [email protected] * @param1

Parameter default: Stored procedure only the last parameter can have a default value.

19. Index

[Focus Index]:

[non-focused index]:

Create INDEX IndexName on TableName (fieldName)

20. Triggers

An operation is performed automatically when a table is being modified. There are 2 ways to do it, 1 is to trigger the source operation before the replacement execution 2 is triggered after the source operation is executed

Temp table: Inserted deleted

2 different ways: after | Instead of

3 Trigger sources: Insert Update Delete

Create:

Create Trigger Triggername

After insert

As Begin

INSERT INTO T SELECT * from inserted

End

Recommendation: Influence efficiency with caution

21. Cursors

Row-wise Operation data

Executes the specified for each data

Use: (Let the age of each person in T-table increase by 1)

DECLARE C1 cursor FOR

Select Id,age from T

DECLARE @id int

DECLARE @age int

Open C1

Fetch C1 into @id, @age

while (@ @FETCH_STATU = 0)

Begin

Set @age = 1 + @age

Update T Set age = @age WHERE id = @id

Fetch C1 into @id, @age

End

Close C1

DEALLOCATE C1

[SQL] SQL Server Basic Syntax