Shulou(Shulou.com)05/31 Report--

This article mainly introduces SQL SERVER basic syntax which, the text is very detailed, has a certain reference value, interested friends must read!

Struct Query Language

1.3NF

a. Atomicity

b. No data redundancy

c. Reference primary keys of other tables

2. constraint

a. Non-empty constraints

b. Primary key constraint

c. Unique constraints

d. Default constraints

e. Check constraints

f. Foreign key constraints

3. Foreign key and relationship

Foreign keys are constraints, do not do foreign key processing, relations are also exist.

4.char varchar nchar nvarchar

char: is a fixed character, if not enough, it will be processed with spaces. No need to calculate length, fast.

varchar: is a dynamic size. Need to calculate length, slow speed, small storage footprint.

nvarchar: is stored in unicode encoding. Each character is 2 bytes long. can display Chinese normally in any sql server. English without n occupies 1 byte. Chinese occupies 2 bytes

5.crud

[select]:

Select top 5:Select top 5

Select top 5 percent

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]:delete [from] table name [where]

6. operator precedence

[logical operator]: and or not(!)

[Comparison operator]:

= !=

Comparison operators General logical operators have higher precedence, except not.! Just lower priority than parenthesis.

7. fuzzy query

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

_: Match any character

%: matches any number of characters

[%]: matching characters %

8. null processing

ISNULL:select ISNULL(english,'missing tests') from Scroe

is [not] null:select * from Scroe where english is null

9. aggregate function

SUM AVG COUNT MAX MIN

Aggregate functions are generally used in conjunction with group by,having can filter after group by

10. Complete query sequence

select top 5 distinct * from T where field = fieldValue group by field2 having 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 the present time DATEADD(datepart,number,date) plus time date+num DATEDIFF(datepart,startdate,enddate) take the time difference end-start DATEPART(datepart,date)

cancel/force insert identity column:set identity_insert table name off/on

Empty table:truncate table table name

12. replicated table

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

13. Federated query

When two result sets have the same number of columns and type, they can be merged into one result set.

union: removes duplicate rows automatically

union all: Show all rows.

14. join query

Internal and external connections

inner join:[inner ]join

select * from T1 inner join T2 on T1.ID2 = T2.ID

Self-join is a special inner join, tree structure storage: select * from T as T1 join T as T2 where T2.PID = T1.ID

External connections:

left [outer] join: left join, left table content is displayed completely. None on the right are marked with NULL

right [outer] join: right join, right table content all display. None on the left are marked with NULL.

full [outer] join: full join, left and right table contents are all displayed, none is marked with NULL

15. windowing function

You can combine multiple rows into a single area. Statistics can be carried out on a district. Unlike group by, you can query all columns.

windowing function:over() can be followed by order by and partition by(similar to group by)

rank() over(order by id)

Sort:row_number() over(order by id)

Subtotal:avg(cost) over(partition by cost)

16. view

Essentially encapsulates a SQL statement and stores no data.

creating: create view viewName as sql statement

View sql: exec sp_helptext viewName

17. Business

Transactions guarantee simultaneous success or failure of multiple operations

begin tran commit tran rollback tran

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

18. stored procedure

Stored procedures essentially encapsulate a piece of code.

Create: create proc procName

@param1 int,@param2 int output

as

set @param2 =@param1 * @param1

Parameter defaults: Only the last parameter of a stored procedure can have a default value.

19. index

[Focus Index]:

[unfocused index]:

create index indexName on tableName(fieldName)

20. trigger

Automatic execution of an operation when adding, deleting or modifying a table. There are two ways to execute: 1 is to replace before triggering the source operation, and 2 is to execute after triggering the source operation.

Temporary table:inserted deleted

2 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

Advice: Impact efficiency Use caution

21. cursor

Line by line operation data

Performs the specified

Use:(Increase the age of each person in the T table 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

The above is "SQL SERVER basic syntax what" all the content of this article, thank you for reading! Hope to share the content to help everyone, more relevant knowledge, welcome to pay attention to the industry information channel!

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.