Common sqlserver knowledge points

-- Delete the table use [20130823_Recource] godroptablemy_table1, my_table2, My_table3 -- create the table use [distinct] goif (exists (select * fromsys. objectswhere) Then (Idintprimarykeyidentity (1)

-- Delete the table use [20130823_Recource] go drop table my_table1, my_table2, My_table3 -- create the table use [20130823_Recource] go if (exists (select * from sys. objects where) drop table Student1 go create table Student1 (Id int primary key identity (1, 2) no

-- Delete a table

Use [20130823_Recource]

Go

Drop table my_table1, my_table2, My_table3

-- Create a table

Use [20130823_Recource]

Go

If (exists (select * from sys. objects where ))

Drop table Student1

Go

Create table Student1

(

Id int primary key identity (1, 2) not null,

Name nvarchar (30) not null,

Age int not null,

MyMoney decimal,

CreateDateTime datetime default getdate ()

)

-- Insert data

Insert into Student values ('hangsan', 34,2300, GETDATE ())

Insert into Student

Select 'hangsi', GETDATE () union

Select 'hangwu', GETDATE () union

Select 'zhanghong', 12,2300, GETDATE ()

-- Modify data

Update Student set MyMoney = 10000 where Age = 12

-- Delete data

Delete Student where Age = 12

Truncate table student

-- Stored Procedure

If (exists (select * from sys. objects where ))

Drop proc proc_Name

Go

Create proc proc_Name (@ number int, @ number1 int output)

As

Begin

Select @ number1 = su. MyMoney from Student as su where su. Id = @ number

End

-- Execute the Stored Procedure

Declare @ num int

Exec proc_Name 3, @ num output

Print @ num

-- Function

If (exists (select * from sys. objects where ))

Drop function function_Name

Go

Create function function_Name (@ number int)

Returns int

As

Begin

Declare @ number1 int

Select @ number1 = su. MyMoney from Student as su where su. Id = @ number

Return @ number1

End

-- Execute the Function

Select dbo. function_Name (3)

-- View

If (exists (select * from sys. objects where ))

Drop view view_Name

Go

Create view view_Name

As

Select * from Student where ID = 3

-- Execute the Function

Select * from view_Name

-- Cursor

Declare cursor_name cursor scroll

Select su. Name from student as su

Open cursor_name

Declare @ Name nvarchar (20)

Fetch last from cursor_name into @ Name

Print @ Name

Fetch absolute 3 from cursor_name into @ Name

Print @ Name

Fetch relative 1 from cursor_name into @ Name

Print @ Name

Fetch prior from cursor_name into @ Name

Print @ Name

Fetch first from cursor_name into @ Name

While (@ FETCH_STATUS = 0)

Begin

Print @ Name

Fetch next from cursor_name into @ Name

End

Close cursor_name

Deallocate cursor_name

-- Transaction

Begin tran tran_Name

Declare @ error int

Set @ error = 0

Begin try

Update Student set MyMoney = MyMoney + 1000 where ID = 1

Set @ error = @ error + @ ERROR;

Update Student set MyMoney = MyMoney-1000 where ID = 2

Set @ error = @ error + @ ERROR;

End try

Begin catch

Print 'error code: '+ error_number () +' error message: '+ error_message ()

Set @ error = @ error + 1;

End catch

If (@ error> = 1)

Begin

Rollback tran

Print 'failed'

End

Else

Begin

Commit tran

Print 'successfully'

End

-- Trigger

If (exists (select * from sys. objects where ))

Drop trigger trigger_Name

Go

Create trigger trigger_Name

On student

For delete

As

Insert into Student values ('hangss', 11,3400, GETDATE ())

-- Execute the trigger

Delete Student where ID = 1

-- Ranking

Select *, ROW_NUMBER () over (partition by name order by id) as ran from Student

Select *, RANK () over (order by id) as ran from Student

Select *, DENSE_RANK () over (order by id) as ran from Student

Select *, NTILE (2) over (order by id) as ran from Student

-- Window Function

Count (*)

-- Set

Select * from Student

Union -- merge

Select * from Student1

Select * from Student

Intersect -- Intersection

Select * from Student1

Select * from Student

Distinct t -- remove

Select * from Student1

-- Connection

Select su. name, su1.Name from Student as su

Inner join Student1 as su1

On su. id = su1.Id

Select su. name, su1.Name from Student as su

Left join Student1 as su1

On su. id = su1.Id

Select su. name, su1.Name from Student as su

Right join Student1 as su1

On su. id = su1.Id

-- Case

Select *, case

When MyMoney <= 2500 then 'loan'

When 2500

When 4500

End as ran

From Student1

Select distinct top 2 * from Student -- top, distinct

Select isnull (null, 2) -- determines if it is null

Select getdate () -- get the date

Select datename (DAY, GETDATE () -- obtain a field of the date

Select dateadd (MONTH, 1, GETDATE () -- add the current date

Select COUNT (*), AVG (su. MyMoney), SUM (su. MyMoney), MIN (su. MyMoney), MAX (su. MyMoney) from Student as su -- System Function

Select * from Student su where su. Id <> 5 -- yes: <,>, <>, <=, =>

Select * from Student su where su. Name like '% wu' -- fuzzy query: %, _, [], ^

Select * from Student su where su. Id between 2 and 6 -- between and

Select * from Student su where su. Id in (3, 4, 5) -- in ()

Select Age from Student su group by su. Age having Age> 22 -- filter group

Select * from Student su order by su. Id desc -- Sort

Two important tables of the trigger

Table operations

Inserted logical table

Deleted logical table

Insert)

Store added records

None

Delete)

None

Store deleted records

Update)

Store updated records

Store records before update

Trigger rollback

If (exists (select * from sys. objects where name = 'tr _ valid '))

Drop trigger tr_Valid

Go

Create trigger tr_Valid

On mymsg

For insert

As

Declare @ age int;

Select @ age = age from inserted

If (@ age> 50)

Begin

Insert into mymsg select Name, Age from inserted

End

Else

Begin

Print 'Age value incorrect'

Rollback tran; -- if the data is incorrect, roll back the service.

End

Insert into mymsg values ('zl68', 51) -- Test

,