0831 database establishment, additions and deletions, various function types, type conversion, fuzzy query, sorting

Create DATABASE lian0831
Go
Use lian0831
Go
CREATE TABLE Stu
(
code int NOT NULL,
CID varchar () NOT NULL,
Name varchar (10),

Banji varchar (10),
Sex varchar (10),
The age int is not NULL,
Yufen Decimal (18,2),
Shufen Decimal (18,2),
Yingfen Decimal (18,2),
)
Insert into Stu values (1001, ' 370302199101011111 ', ' Zhang San ', ' One shift ', ' male ', 25,70,65,76)
Insert into Stu values (1002, ' 370302199101011112 ', ' John Doe ', ' one shift ', ' female ', 25,76,85,66)
Insert into Stu values (1003, ' 370302199201011111 ', ' Li Qi ', ' One shift ', ' male ', 24,30,95,46)
Insert into Stu values (1004, ' 370302199301011112 ', ' Xena ', ' one shift ', ' female ', 23,60,65,68)
Insert into Stu values (1005, ' 370302199201011112 ', ' Wang Qi ', ' Class two ', ' female ', 24,74,68,76)
Insert into Stu values (1006, ' 370302199302011111 ', ' Don Ride ', ' Class two ', ' Male ', 23,76,67,75)
Insert into Stu values (1007, ' 370302199103011112 ', ' Tri Yu ', ' Class two ', ' female ', 25,70,35,86)
Insert into Stu values (1008, ' 370302199404011111 ', ' cow super ', ' Class two ', ' Male ', 22,72,64,78)
Insert into Stu values (1009, ' 370302199107011112 ', ' King Snow ', ' class three ', ' female ', 25,73,64,77)
Insert into Stu values (1010, ' 370302199207011111 ', ' Zhang Rui ', ' Class three ', ' Male ', 24,77,65,77)
Insert into Stu values (1011, ' 370302199203011111 ', ' License ', ' Class three ', ' Male ', 24,74,63,72)
Insert into Stu values (1012, ' 370302199004011111 ', ' Yuke ', ' Class three ', ' Male ', 26,50,45,86)
Sp_renamedb Lian0831,xuesheng
Sp_renamedb xuesheng,lian0831
Select* from Stu
Select Yufen from Stu
--to show Yufen as a language achievement
Select Yufen as language from Stu
--Check the age of the person aged 24, language score, gender
Select Yufen,name,sex from Stu where age=24
--Query the names of people yufen from 60 to 80
Select name from Stu where Yufen between 75and 80
--Automatically remove the weight when querying, do not delete, on a column to re-display
Select distinct Yufen from Stu where age=24
--Check the language scores of the female students aged 24
Select Yufen as language, name as name from Stu where age=23 and sex= ' female ' and banji= ' class '
--Check the name of the student in class 23 or 24
Select Age as, name as ' name ' from Stu where age=23 or age=24 and Banji = ' class One '
--Query all data ages 22 to 23
Select*from Stu where Age in (22,23)
--Query number is not all data between 1003 and 1011
SELECT * from Stu where code not in (1003,1011)
--not between 1003 and 1011.
SELECT * from Stu where code not between 1003 and 1011

--Fuzzy query

--a percent semicolon can represent any character, any length
Select *from stu where name like '% li% '
--underline means to find any one character
Select *from stu where name like ' Xu _ '
--the brackets in the quotation marks are underlined, meaning any value in parentheses.
Select *from stu where name like ' _[permission, Hark] '

--Ascending descending
--According to the language score ascending
Select *from stu ORDER by Yufen ASC
--in descending order of several points
Select Shufen as Math from Stu ORDER by Shufen Desc
--all data in descending order of the top three in minutes
Select top Stu ORDER BY Shufen Desc
--in descending order of the age of 25
Select *from Stu where age=25 order by Shufen Desc
--The age of 25 is sorted in descending order to find the first
Select Top 1*from stu where age=25 order by Shufen Desc

--Mathematical functions
--Absolute value
Select ABS (-5)--Draw results
Print ABS (-7)--Get the message
Select *from stu where ABS (Shufen) >=85
--Take the upper limit ceiling
Select Yufen from Stu where CEILING (Yufen) >75
--Take lower floor
Select Floor (1.7)
--n Power
Select POWER (2,4)
Select ' This is ' + ' age squared: ', POWER (age,2) from Stu
--round rounded, followed by a comma to indicate the first few
Select ROUND (222.336,1)
--square root
Select SQRT (4)
--Square
Select SQUARE (4)

--String function
--ASCII returns the ASCII encoding of the first letter in a string
Select ASCII (' name ')
Select ASCII (name) from Stu
Select NAME,ASCII (name) from Stu where ASCII (name) >200
--char convert ASCII to corresponding character
Select CHAR (77)
Select CHAR (Yufen) from Stu
--charindex search for another expression in one expression and return to the starting position
--Look for the character segment preceded by a comma and return the index of the first letter, starting at 1
Select CHARINDEX (' EF ', ' Abcdefghi ')
Select CHARINDEX (' 1992 ', CID) from stu--a return value of 0 indicates no found.
--Concatenation of strings
Select ' A ' + ' B ' + ' C '
--difference returns the similarity, using 0-4 to indicate the similarity degree
Select difference (' A ', ' abc ')
--left comma is a string, followed by a left-to-right intercept how much
Select Left (' ABCdef ', 3)
--right intercept from right to left
Select Right (' 123456 ', 3)
Select Left (name,1) from Stu where age=23
--len query string length, the preceding space is counted, the following space does not count
Select LEN (' abc ')
Select LEN (name) from Stu where yufen>=75
--lower convert uppercase to lowercase
Select lower (' ABCdef ')
--Lowercase to uppercase
Select UPPER (' AbcDEF ')
--delete the left space, equivalent to the trimstart in. Net
Select LTRIM (' a ')
--Delete the space on the right
Select RTRIM (' 123 ')
--patindex is equivalent to CHARINDEX, which returns the index of the first letter of the searched character segment
Select PATINDEX ('%cd% ', ' ABCDEFG ')--find the string to be added before and after, and not the same as charindex.
--replace Replace display, not change
--The first is the position string to find, the second is to select the replacement character segment, and the third is the character segment to change to.
Select Yufen,name,replace (Sex, ' female ', ' lady ') from Stu where age=25 order by yufen ASC
--replicate copy, can be specified number of times
Select REPLICATE (' a ', 2)--shows the two times it was copied
--reverse flipping A string
Select REVERSE (' ABCD ')
Select REVERSE (name) from Stu where age=24
--Print spaces
Select ' A ' +space (+) + ' B '
--STR converts a decimal number to a string, and the 1 parameter is the original data,
The--2 parameter is the length of the string (greater than or equal to the length of the integer part of the original data),
--3 parameter is the number after the decimal point, the decimal point is also counted as one
Select STR (1111.111,6,3)
The--stuff parameter 1 is the source string, and the parameter 2 is the beginning of the first index,
--Parameter 3 means to delete several (including the starting character of the index).
---Parameter 4 indicates the string to be inserted
Select STUFF (' abcde12345 ', 1, 2, ' haha ')
--substring to intercept a specified length of string from a specified index
Select Yufen, SUBSTRING (name,1,1) from Stu where age=24 order by yufen ASC

--Time-date function
SELECT @ @DATEFIRST as ' 1st day ', DATEPART (DW, GETDATE ()) as ' Today '
SELECT GETDATE ()
SELECT @ @DATEFIRST-system constant, returns a value directly
--dateadd, Parameter 1 is the increment of the item, the parameter 2 is how much to increase, and the parameter 3 is the time at which the increase
Select DATEADD (year,2, ' 2007-01-01 ')--month Day
--datediff difference, parameter 1 is the specified number, meal 2 is the beginning, parameter 3 is the end
Select DATEDIFF (MONTH, ' 2007-01-01 ', ' 2007-05-01 ')
--datename returns the type specified by parameter 1
Select Datename (WEEKDAY, ' 2011-1-2 ')--(Year,month, Day,week)
--datepart is equivalent to Datename, which differs from Datename return string, DATEPART returns int

--getdate Get local time date
Select GETDATE ()
--isdate determine if the time and date format is correct, correct is 1, error is 0
Select ISDATE (' 1-2-31 ')
--Accurate acquisition of time
Select Sysdatetime ()

--Data conversion, function conversion
--cast
Select CAST (123 as varchar (20))-value type converted to string
Select CAST (1.23 as int)--decimal type converted to int
Select CAST (123 as Decimal (18,2))--int type converted to decimal
Select CAST (' 1.23 ' as Decimal (18,2))--Convert string type to decimal
Select CAST (' 12.3 ' as int)--String to int

--convert--Front write type, behind write original data, with cast opposite
Select CONVERT (int, ' 123 ')

0831 database establishment, additions and deletions, various function types, type conversion, fuzzy query, sorting