SQL Server fuzzy query sort aggregate function Mathematical function string function time Date function conversion function conversion

Create DATABASE lianxi831--Creating databases
Go
Use lianxi831--reference database
Go
CREATE TABLE XS-insert Table
(
code int NOT NULL,--write content
Name varchar (10),
CID varchar (18),
Banji varchar (10),
Yufen Decimal (18,2),
Shufen Decimal (18,2),
Yingfen Decimal (18,2),
)
Go
INSERT into XS values (1, ' Zhang San ', ' 370322199101011234 ', ' one shift ', 80,70,60, ' Male ')--write content
INSERT into XS values (1, ' John Doe ', ' 370322199102011234 ', ' one shift ', 81,71,61, ' female ')
INSERT into XS values (1, ' Harry ', ' 370322199103011234 ', ' one shift ', 82,72,62, ' male ')
INSERT into XS values (1, ' Zhao Liu ', ' 370322199104011234 ', ' one shift ', 83,73,63, ' female ')
INSERT into XS values (1, ' 33 ', ' 370322199105011234 ', ' Class two ', 70,60,50, ' male ')
INSERT into XS values (1, ' Lee 44 ', ' 370322199106011234 ', ' Class two ', 71,61,51, ' female ')
INSERT into XS values (1, ' King 55 ', ' 370322199107011234 ', ' Class two ', 72,62,52, ' male ')
INSERT into XS values (1, ' Zhao 66 ', ' 370322199108011234 ', ' Class two ', 73,63,53, ' female ')
INSERT into XS values (1, ' Three sheets ', ' 370322199109011234 ', ' Class three ', 60,50,40, ' male ')
INSERT into XS values (1, ' John Doe Li ', ' 370322199110011234 ', ' Class three ', 61,51,41, ' female ')
INSERT into XS values (1, ' Harry King ', ' 370322199111011234 ', ' Class three ', 62,52,42, ' male ')
INSERT into XS values (1, ' Zhao Liu Zhao ', ' 370322199112011234 ', ' Class three ', 63,53,43, ' female ')
Go
Select *from XS
ALTER TABLE XS Add [Xingbie]char (10)--Add column
Go
Select *from XS
ALTER TABLE XS drop column [Xingbie]--Delete column
Select *from XS

--Fuzzy query like
Select *from xs where name like '% li% '
Select *from xs where name like ' King _ '
Select *from xs where name like ' _[Zhang San, John Doe, Harry] '
--Sort order BY
Select *from xs ORDER by yufen ASC
Select *from xs ORDER by yufen Desc
Select Top 2*from xs where name like '% King ' ORDER by Yingfen Desc

--Aggregate function
--Average
Select AVG (yufen) from XS where banji= ' a class '
--Find the number
Select COUNT (*) from XS where banji= ' class three '
--Maximum value
Select MAX (yufen) from XS
--Minimum value
Select MIN (yufen) from XS
--Summation
Select SUM (yufen) from XS where banji= ' class two '

--GROUP BY
Select Banji from XS GROUP by Banji
Select Banji,count (*) from XS where yufen>60 GROUP by Banji ORDER by COUNT (*) desc
--having can only follow group by.
Select Banji,count (*) from XS where yufen>60 GROUP by Banji have Count (*) >2

--Mathematical functions
--Absolute value
Select ABS (-8)

--Take online and offline
Select CEILING (3.14)
Select Floor (3.14)

--Several parties
Select POWER (2,3)

--Square
Select SQUARE (4)

--square root
Select SQRT (4)

--rounded, followed by a comma after the decimal point there are several
Select ROUND (25.52,1)

--String function

--string conversion to encoding
Select ASCII (' a ')
--only the beginning of conversion
Select ASCII (' abc ')
--encode into a string
Select CHAR (97)

--View the first occurrence of the index number returned with a value of 0, indicating no found
Select CHARINDEX (' CD ', ' ABCDEFG ')

--Concatenation of strings
Select ' A ' + ' B ' + ' C '

-How many lengths to intercept from the front
Select Left (' ABCdef ', 2)
Select Right (' ABCdef ', 2)

--Calculates the length of the string in front of it, the length of the following is not counted
Select LEN (' abc ')

--Convert to uppercase to lowercase
Select UPPER (' abc ')
Select LOWER (' ABC ')

--Remove the space at the front to remove the space behind
Select LTRIM (' a ')
Select RTRIM (' a ')

--Returns the index of the first occurrence of the character
Select PATINDEX ('%abc% ', ' ABCDEFG ')

--Replace the first position fill in the location of the content to be replaced, the second position to fill in the content to be replaced, the third place to fill in the replacement content
Select REPLACE (name, ' Zhang San ', ' three sheets ') from XS

--Copy the content that needs to be copied, the number of copies
Select REPLICATE (' ABC ', 2)

--Flip string
Select REVERSE (' abc ')

--Print spaces
Select ' A ' +space (5) + ' B '

--str converting decimals to strings
--Parameter one is the original character, parameter two is the length of the string, parameter three is the length of the decimal point of J Intercept, the decimal place occupies a length
Select STR (2222.22,5,1)

--stuff inserting a string
--Parameter one is the original string, parameter two is the index from the beginning of the first, parameter three is to delete several, parameter four is the inserted string
Select STUFF (' 123abc ', 4,3, ' hello ')

--substring to intercept the specified length from the specified string
--Parameter one is the original string, parameter two is to intercept the beginning of the index of the string, parameter three is the length of interception
Select SUBSTRING (' ABCDEFG ', 3, 1)

--Time-date function
--system constants, return a value directly
SELECT @ @DATEFIRST

--dateadd parameter one is the increment of the item, parameter two is the increase of the amount, the parameter three is the increment of the benchmark
Select DATEADD (year,8, ' 2016-8-8 ')
Select DATEADD (month,8, ' 2016-8-8 ')
Select DATEADD (day,8, ' 2016-8-28 ')

--datediff calculate the time interval for two dates
Select DATEDIFF (year, ' 2008-8-8 ', ' 2016-8-31 ')
Select DATEDIFF (MONTH, ' 2008-8-8 ', ' 2016-8-31 ')
Select DATEDIFF (Day, ' 2008-8-8 ', ' 2016-8-31 ')

--datename returns the specified time type
Select Datename (Day, ' 2008-8-8 ')
Select Datename (WEEK, ' 2016-2-1 ')--week of the year
Select Datename (WEEKDAY, ' 2016-8-31 ')

--datepart is equivalent to Datename, the difference is that Datename returns a string, and DATEPART returns an int type
Select DATEPART (WEEKDAY, ' 2016-8-31 ')--1 is Sunday, 7 is Saturday

--Returns an integer that returns the year, month, and day of the date
Select year (' 2016-8-31 ')
Select month (' 2016-8-31 ')
Select Day (' 2016-8-31 ')

--Get local time
Select GETDATE ()--No parameters required
--Get the exact time of the system
Select Sysdatetime ()

--Determine if the time and date format is correct
Select ISDATE (' 2008-8-31 ')--returns 1 correctly, error returned 0

--Conversion of data, function conversions
--cast (as front write the original data type, as behind write converted data type)
Select CAST (123 as varchar (20))-value type converted to String type
--convent
Select CONVERT (int, ' 123 ')--write the converted data type at the front and write the data before the conversion

SQL Server fuzzy query sort aggregate function Mathematical function string function time Date function conversion function conversion