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  Region 3  ' WA ' ' Washington area ' 4  Else ' Other regions ' 5 End 6   from Hr.employees

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

1  Select firstname,lastname,region 2   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  SelectFirstname,lastname,2   CaseWhen region ='WA'Then'Washington area'3When region is NULLThen'Unknown Area'4  Else 'Other regions'5 End6   fromHr.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.orders 2  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.orders 2  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  SelectDatePart (Year,getdate ()) asN'year',2DatePart (Month,getdate ()) asN'Month',3DatePart (Day,getdate ()) asN'Day',4DatePart (Hour,getdate ()) asN'when',5DatePart (Minute,getdate ()) asN'points',6DatePart (Second,getdate ()) asN'seconds'

Results

(4) Special date interception section, for example:

1   Select  as n ' The Day of the year ',2          as N' the first day of the week ' , 3          as N' the first 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  SelectDATEADD (Day, -, GETDATE ()) asN'What's the date after 20 days?',2DateDiff (Year,'19491001', GETDATE ()) asN'The motherland has been established for so many years',3DateDiff (Year,'19911002', GETDATE ()) asN'how big is the cock?'

(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' ) Date Taken '

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