Common operations of DataBase

(1) Try catch use

--Open the Try Catch featureSetXact_abort onbeginTrybegin Tran        Insert  intoTableName (ID)Values(1)        Commit Tran        Print 'commited'EndTrybeginCatchrollback    Print 'rolled Back'EndCatch

(2) Get the number of days before the current month one months ( DatePart (date, datetime) DATEADD (date, number, datetime) Cast (exp as datetime))

Select DatePart( Day,DATEADD( Day,-1,         Cast(cast( Year(getdate()) as nvarchar)+'-'+         cast(Month(getdate()) as nvarchar)+'-01'  as datetime)))

(3) Take decimal second bit rounding (type conversion)

Select Convert (Numeric (2IsNull(50.01634,0))

Results: 50.02

(4) Get time

[1] One month first day: Select DateAdd (Mm,datediff (Mm,0,getdate ()), 0)

--dateadd when both the added and initial values are 0 o'clock, the returned time is 1900-01-01 00:00:000

Select DATEADD (mm,0,0);

[2] YTD: Year (GETDATE ())

[3] Month: Month (GETDATE ())

[4] Today: Day (GetDate ())

[5] Yesterday's record: DateDiff (Day,[datetime],getdate ()) =1 to change the Datetime to your corresponding field, GETDATE ()-datetime is the time difference.

[6] This month/week/day record: DateDiff (Month/week/day,[dateadd],getdate ()) =0

[7] This week's Monday: Select DateAdd (wk, DateDiff (Wk,0,getdate ()), 0)

[8] first day of the quarter: Select DateAdd (QQ, DateDiff (Qq,0,getdate ()), 0)

[9] morning of the Day (midnight): Select DateAdd (DD, DateDiff (Dd,0,getdate ()), 0)

[10] first day of the year:: Select DateAdd (Yy,datediff (Yy,0,getdate ()), 0)

[11] Last day of last year (minus 3 milliseconds for the first day of the year): Select DateAdd (Ms,-3,dateadd (yy, DateDiff (yy, 0, GETDATE ()), 0))

[12] first day of the month: Select DATEADD (mm, DateDiff (mm, 0, GetDate ()), 0);

[13] Last day of the month: select DateAdd (ms,-3, DateAdd (mm, DateDiff (Mm,0,getdate ()), 0))

[14] Last day of the year: Select DateAdd (ms,-3, DateAdd (yy, DateDiff (yy, 0, GETDATE ()) +1, 0))

[15] First Monday of the month: Select DateAdd (wk, DateDiff (wk,0, DateAdd (Dd,6-datepart ()), Day,getdate ()), 0)

[16] Display day of the week: Select Datename (Weekday, GETDATE ());

[17] shows the number of days of the Month: select DateDiff (DD, GETDATE (), DateAdd (mm, 1, GETDATE ()));

[18] Shows how many days a certain month is a year

Create   FunctionDaysInMonth1 (@datetime datetime)returns int asbegin    return  Day(Datepart(DD,DATEADD(MS,-3,DATEADD(MM,DateDiff(MM,0,@datetime)+1,0) ) ))EndCall SELECT dbo. DaysInMonth1 ('2012-2-13');

(5) Get the table for all users in the current database

Select *  from where xtype='U'

(6) Randomly remove 3 data

Select Top 3 * from Table ORDER by NewId ()

(7) Finding redundant duplicate records in a table

SELECT * FROM table where ID in (select SID from table group by ID has Count (Name) >1)

(8) Keyword WAITFOR mainly has waitfor delay (deferred execution), waitfor time (execution at a specific period)

WAITFOR DELAY ' 00:00:08 '

Print (' Hello, Im waitfrom delay ')

waitfor time ' 20:49:50 '

Print (' Hello,im waitfor time ')

(9) Detection data is present

[1] Detecting whether a database exists

if (Exists (SELECT * from sys.databases where name= ' School '))

SELECT * FROM Student

Go

[2] detection of the existence of the data table

if (exists (SELECT * from sys.objects where name= ' Student '))

SELECT * from Student ORDER BY SID Desc

Go

[3] Detecting the presence of data columns

if (exists (SELECT * from Sys.all_columns where object_id=object_id (' Student ') and Name= ' Sname '))

SELECT * FROM Student

Go

(10) Table connection

--while unconditionally querying two unrelated tables, the resulting new table field is the sum of two table fields, the number of information bars is the product of two pieces of information (that is: Cartesian product of two tables)

Select T.*, student.* from T, Student

--cross-connect is the kind of connection that is said above

Select t.*,student.* from T cross Join Student

-Random numbers can generate random numbers between 0-1

Select Rand (); Select Rand (CheckSum (NewId ()))

--Random number 0-9

Select ABS (Rand ()); Select ABS (CheckSum (NewId ()))%10

--Random number A-B between

Select A+abs (Checksum (NEWID ()))% (b-a+1)

Select 1+abs (Checksum (NEWID ()))%100

String manipulation

--substring string Intercept (str, StartIndex, Length)

Select SubString (' Olive116 ', 1, 5)

--left/right to intercept a string from the left

Select Left/right (' Olive ', 3)

--String substitution replace (str, STR1, STR2)

Select Replace (' oolive ', ' o ', ' 1 ')

--Reverse sort

Select Reverse (' Olive ')

--Deletes the specified length and inserts another set of characters at the specified starting point

Select Stuff (' oliveooo ', 6,3, ' Hello ')

--Repeats the value of the string in the specified number of times

Select Replicate (' Olive ', 3)

--Returns the first occurrence of a position in an expression

Select PatIndex ('%olive% ', ' hello! Olive ')

--Returns the starting position of the specified expression in the string

Select CharIndex (' O ', ' hello,olive ')

--Returns the integer value of the first character of an input expression

Select UniCode (' O ')

--Returns character data converted from numeric data

Select STR (123.1314,4), LEN (str (123.3212,321))

Select STR (123.1334,4,4)

--Pronunciation matching degree

Select Sname,soundex (Sname) from Student

--difference () simplifies two string pronunciation similarity, returns a value between 0-4 to reflect a two-character similarity, and the larger the value, the more similar

Select Sname,soundex (Sname), Difference (Sname, ' Olive ') from Student

--Querying duplicate records

SELECT * FROM table where SID in (select SID from table group by Sid has COUNT (SID) >0)

--delete duplicate records (and keep the minimum SID Records)

Delete from Table where

Sid in (the Select SID from the Table group by SID has COUNT (SID) >0)

and Sid not in (the Select MIN (SID) from the Student group by SID has count (SID) >0)

--Double quotes

Select "" As Result

--isnumeric () Determines whether the number is 1 no for 0

Select IsNumeric (' a ')

--isdate () Determines whether the date is 1 No to 0

Select IsDate (' 2012-3-2 ')

Common operations of DataBase