SQL Server Study Notes Series 3

I. If I write it in front, it's a weekend! Life continues. When you stop, it may suddenly become uncomfortable. Sometimes, when you see one thing, you will find that there are many excellent people in this society, and there are also many people who are hard-working and hard-working! Work on Friday morning, buy breakfast, go to the Company restaurant for breakfast, I met a person

I. If I write it in front, it's a weekend! Life continues. When you stop, it may suddenly become uncomfortable. Sometimes, when you see one thing, you will find that there are many excellent people in this society, and there are also many people who are hard-working and hard-working! Work on Friday morning, buy breakfast, go to the Company restaurant for breakfast, I met a person

I. Preface

Today is another weekend! Life continues. When you stop, it may suddenly become uncomfortable. Sometimes, when you see something, you will find that there are many excellent people in this society, silently

There are many hard-working people! I went to work on Friday morning, bought breakfast, and went to the company restaurant for breakfast. I met a person. Maybe I didn't pay attention twice, but every time I had breakfast at the restaurant,

I saw him eat a bowl of porridge each time, and even some pickles were useless. Maybe a single dog like me, I cannot understand the pain of having a home. Maybe this is a kind of his life.

But what I see more is a kind of hardship for his family and for the future. Maybe I think too much, but in any case, we should always pay attention to our health, eat breakfast on time, and maintain nutrition.

In the future, our family members and our future happiness should work harder and take care of ourselves. After talking so much about it, let's continue our study of sqlserver! Previous Learning Series:

SQL Server Study Notes Series 1: http://www.cnblogs.com/liupeng61624/p/4354983.html

SQLServer Study Notes Series 2: http://www.cnblogs.com/liupeng61624/p/4367580.html

Ii. case expression

(1) Simple expressions. For example, to query the region (region) in the employee table, use "Washington region" instead of "WA.

1 select firstname, lastname, 2 case region3 when 'wa 'then' Washington region '4 else' other regions '5 end6 from hr. employees

(2) Search expression. Here we first query the information in the employee table (hr. employees.

1  select firstname,lastname,region2  from hr.employees

If we want to use "Unknown region" to express the region where region is null, what should we do? In this case, you need to use a search expression, which can be written as follows:

1 select firstname, lastname, 2 case when region = 'wa 'then' Washington region '3 when region is null then' unknown region '4 else' other regions '5 end6 from hr. employees

Result:

3. Processing of date and time data.

(1) string date

'20140901', which is a string date, must be a four-digit year, two-digit month, and two-digit date. For example, the date of the order table to be queried is greater than '123 '. You can write as follows:

1  select * from sales.orders2  where orderdate>'20080301'

Result:

(2) cast conversion. For example, you can convert '20180101' to the time type. The results are the same.

1  select * from sales.orders2  where orderdate>cast('20080301' as datetime)

(3) datepart: truncates a part of a date. year indicates the year, month indicates the month, and day indicates the date. And so on .....

For example, extract a part 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', 5 datepart (minute, getdate () as n ', 6 datepart (second, getdate () as n' s'

Result

(4) special date truncation, for example:

1 select datepart (dayofyear, getdate () as n' day of the year ', 2 datepart (weekday, getdate () as n' day of the first week ', 3 datepart (week, getdate () as n' week of the year'

Result

(5) addition and subtraction of dates. DateAdd (growth unit (year, month, day), step size (Increase), base number), datediff (unit, step size, base number ).

For example, what is the value of the current date plus 20 days? How long is the founding of my beloved motherland counted? How old is Ben diaosi this year?

1 select dateadd (day, 20, getdate () as n' what is the day after 20', 2 datediff (year, '123', getdate ()) as n' motherland has been established for so many years ', 3 datediff (year, '123', getdate () as n''

(6) cast and convert can both convert strings or other forms to the specified type. The difference is that the conversion style can be specified when convert is converted, and cast is converted directly.

For example:

1 select convert (nvarchar, getdate (), 112) as n' form after conversion ', 2 left (convert (nvarchar, getdate (), 112), 6) as 'n' 'retrieve the year'

Result

This time, there is little content. In the next section, we will prepare to learn the commonly used join connections.

I hope you can give us some guidance and learn from your mistakes! Thank you!