SQL Server Learning Note Series 3

First, write it in front.

Today is a double break again! Life is still going on, and when you stop, it may suddenly seem uncomfortable. Sometimes, see a thing, you will find the original in this society, excellent people a lot, silently

There are many people who struggle hard. Friday morning on time to work, buy breakfast, to the company's restaurant to eat breakfast, I met a person, maybe twice I will not pay attention, but every time I eat breakfast in the restaurant,

Will meet him, I see him is every time with a bowl of white porridge in there to eat, and even a little pickles are useless, perhaps I such a single dog, can not understand the pain of a family, perhaps this is his kind of life

Way, but I saw more of his kind of hardship, for his family, for the future of an effort. Maybe I think too much, but anyway, we should pay attention to the body, eat breakfast on time, maintain nutrition, for

After our family and later happiness, we should work hard and take care of themselves. Say so much, or continue our study of SQL Server! Previous series of studies:

SQL Server Learning Note Series 1:http://www.cnblogs.com/liupeng61624/p/4354983.html

SQL Server Learning Note Series 2:http://www.cnblogs.com/liupeng61624/p/4367580.html

Two. Case expression

(1) Simple expression, for example, to query the employee table inside the area (region) for WA's "Washington Land District" instead of the expression.

1  Select firstname,lastname,2 case  region3 if  ' WA ' Then ' Washington Land District ' 4  Else ' other ' 5  End6  from Hr.employees

(2) Search expression, here we first query the employee table (Hr.employees inside the information.

1  Select Firstname,lastname,region2 from  hr.employees

If the region is null, we want to use "Unknown region" to express, how to write it? In this case, you need to use the search expression, you can write:

1  Select firstname,lastname,2 Case if  region = ' WA ' Then ' Washington Land District ' 3 when       area is null then ' unknown ' 4  Else ' Other regions ' 5  End6 from  hr.employees

Results:

Three. Processing of date and time data.

(1) String date

' 20080301 ', which is a string date, but must be guaranteed as a four-bit year, two-bit month, and two-bit date. For example, the Query order table date is greater than ' 20080301 '. It can be written like this:

1  SELECT * from Sales.orders2  where orderdate> ' 20080301 '

Results:

(2) cast for conversion. For example, you can say ' 20080301 ' into a time type. And the result is the same.

1  SELECT * from Sales.orders2  where Orderdate>cast (' 20080301 ' as datetime)

(3) DatePart, the interception date of a paragraph, year represents the interception of years, month represents the interception of the month, day represents the date of interception. Wait a minute.....

For example, intercept a portion of the current time.

1  Select DATEPART (year,getdate ()) as n ' Year ', 2         datepart (Month,getdate ()) as N ' Month ', 3         datepart (day,getdate ( ) as N ' Day ', 4         datepart (Hour,getdate ()) as n ' time ', 5         datepart (minute,getdate ()) as n ' Min ', 6         datepart (second, GETDATE ()) as N ' second '

Results

(4) Special date interception section, for example:

1   Select DATEPART (dayofyear,getdate ()) as n ' the day ordinal of the Year ', 2         datepart (Weekday,getdate ()) as n ' The day of the Week ', 3         DatePart (Week,getdate ()) as N ' The Week of the Year '

Results

(5) Date of addition and subtraction. DATEADD (Growth unit (year, month, day), Step (growth), base), DATEDIFF (unit, step, base).

For example: What is the current date plus 20 days? How long has my beloved motherland been founded? How old is Ben Dick this year?

1  Select DATEADD (Day,20,getdate ()) as n ' 20 days after what day ', 2         datediff (year, ' 19491001 ', GETDATE ()) as n ' The motherland was founded for so many years, 3         DateDiff (year, ' 19911002 ', GETDATE ()) as N ' dick wire how Big

(6) Cast and convert, both can speak string or other forms of conversion to the specified type. The difference is: Convert conversion can be specified in the style of conversion, cast direct conversion.

For example:

1  Select CONVERT (Nvarchar,getdate (), as n ' converted form ', 2 left        (CONVERT (Nvarchar,getdate (),), 6) as n ' taken out of the month '

Results

This time the content is relatively few, the next section prepares to learn our common join connection, prepares to study well.

I hope that you Daniel give guidance, inappropriate to accept learning! Thank you!

SQL Server Learning Note Series 3