SQL you have to know--functions and type conversions

Use Myschooltwo
--isnull (Expression,value): Returns expression If expression is not empty, otherwise returns value.
Select StudentID, IsNull (中文版, 0) from score
If the 中文版 value is null returns
--String function
--len (): Calculate string length
Select LEN (' 123456 ')
Select LEN (sName), sName from Student
--lower (), UPPER (): Turn lowercase, uppercase
Select LOWER (' ACBDFGCD ')
Select Upper (' ACBDFGCD ')
--ltrim (): Whitespace to the left of the string is removed
--rtrim (): The space to the right of the string is removed
--ltrim (RTRIM ("BB"))
Select Len (LTrim (' ABCD ') + ' 123 ')
Select Len (RTrim (' ABCD ') + ' 123 ')
Select Len (RTrim (' LTrim (' ABCD ')) + ' 123 ')
--left (), right () intercept string
Select Left (' Zhang San ', 1)
--Result +1 = 612346 conversion of the automatic implementation type
Select Right (' ipv612345 ', LEN (' ipv612345 ')-3) + 1
--substring (string,start_position,length) intercepts a string IP from the first position
--The parameter string is the main string, start_position is the starting position of the substring in the main string, and length is the maximum length of the substring.
Select SUBSTRING ("ipv612345", 1, 2)
---Check the last name of all students in the class to remove duplicates
SELECT DISTINCT (substring (sName, 1, 1)) from Student


--Date function
--getdate (): Gets the current date time
Select GETDATE ()
--dateadd (datepart, number, date), calculates the date after the increase.
--The parameter date is the day to be calculated, the parameter number is increment, the parameter datepart is the unit of measure, the optional value is as follows:
--Value alias description
--year yy,yyyy Year
--quarter Qq,q Quarter
--month mm,m Month
--dayofyear Dy,y Day of the year
--day Dd,d Day
--week WK,WW The first week of the year
--weekday Dw,w Week
--hour hh Hour
--minute Mi,n min
--second ss,s sec
--millisecond MS MS
--dateadd (day, 3,date) is the date of the day after which the date is calculated.
--and DATEADD (month, -8,date) is the date of the month before the date of the calculation
Select DATEADD (MONTH, 1, GETDATE ())
Select DATEADD (year,-1, GETDATE ())
Select DATEADD (Day, 1, GETDATE ())
--quarter Quarter Week a week
Select DATEADD (quarter, 1, GETDATE ())
Select DATEADD (week, 1, GETDATE ())

--datediff the time difference between two months will usually use it to calculate the current monthly sales Ah, this year's sales.
--datediff (DatePart, StartDate, EndDate): Calculates the difference between two dates.
--datepart is the unit of measure, the desirable value is DATEADD.
Select DATEDIFF (MONTH, GETDATE (), ' 2012-12-21 ')
Select DATEDIFF (Day, GETDATE (), ' 2012-12-21 ')

--ask for the current month's sales record
--meaning that the date in the XSJL table is calculated to be zero with the current month's date difference
SELECT * from XSJL where DATEDIFF (MONTH, date, GETDATE ()) = 0

--statistics on the number of students in different birthday years
--year (Sbirthday) get year
Select COUNT (*), Year (Sbirthday) from Student
Group by year (Sbirthday)
Select year (GETDATE ())--2011
Select Month (getdate ())
Select Day (getdate ())
--ask how many students each age
Select COUNT (*), DATEDIFF (year, Sbirthday, GETDATE ()) from Student
GROUP by DateDiff (year, Sbirthday, GETDATE ())
SELECT * FROM Student
--datename datepart returns a specific part of a date
Select Datename (year, GETDATE ())--2011 but is a string type
Select DATEpart (year, GETDATE ())--2011 but an integer
-The current day is the first day of this year
Select Datename (DayOfYear, GETDATE ())
-Current day is the first week of the year 48 weeks
Select Datename (Week, GETDATE ())
--Back to ' Thursday '
Select Datename (WEEKDAY, GETDATE ())
--return ' from the beginning of Sunday
Select DATEPART (WEEKDAY, GETDATE ())

--Type conversion
--Questions
Select ' Average score ' + 123-cannot convert nvarchar type to int
Select AVG (中文版) from score--average number of digits only need to retain two digits valid
SELECT * FROM Student--sbirthday only accurate to day
--cast (expression as data_type)
Select ' Average score ' + CAST (123.56 as nvarchar (10))
--implicit conversions have occurred by default here
Select Right (SNo, 3) + 1 from Student
--the result ibid.
Select CAST (Right (SNo, 3) as int) + 1 from Student
-The result is cast is not rounded
Select CAST (123.556 as int)
--if it must be rounded, use to round
Select ROUND (1234.45789, 0)--return
Select ROUND (1234.65789, 0)--return
--need to combine cast use removal
Select CAST (ROUND (1234.789, 0) as int)--Return

--cast reserved two decimal places with decimal
Select CAST (1234.789 as Decimal (10, 2))--Return
--decimal parameters after viewing the Help document
Select CAST (AVG (中文版) as decimal (2)) from score
--convert (data_type, expression)
Select CONVERT (int, 1234.56789)--Returns not rounded
Select CONVERT (int, ROUND (1234.5678, 0))--Return
Select CONVERT (Decimal (10, 2), 1234.5678)--return
--Supplement using convert read date
--date only day of month
Select GETDATE ()
Select CONVERT (nvarchar, GETDATE ())--Return 24 2011
--You need to use the third parameter to specify the return date format
Select CONVERT (nvarchar), GETDATE (), 20)--Return 14:16:55
Select CONVERT (nvarchar), GETDATE (), 23)--Return
--Modify the length of the intercept nvarchar
Select CONVERT (nvarchar), GETDATE (), 20)--Return
--only take hours minutes seconds
Select CONVERT (nvarchar), GETDATE (), 108)--Return: 20:02
--Resolve the Sbirthday format in student above
Select SId, SName, SAge, sNo, convert (nvarchar, sbirthday, max) as ' date of birth ',
Sclassid, SSex, Sphone from Student

SQL you have to know--functions and type conversions