Mathematical functions, String functions

--Mathematical functions
--abs Absolute Value
Select ABS (-7)
--ceiling Take the line,
Select CEILING (3.14)
--floor to the downline.
Select Floor (3.14)
--power several parties,
Select POWER (2,3)
--round rounded,
Select ROUND (3.5,0)
--sqrt Open Square root
Select SQRT (4)
--square Square
Select SQUARE (4)
--ascii the ASCII code that returns the leftmost character of the string
Select ASCII (' name ')
--select ASCII name from xueshengxinxi--to view the ASCII code for the first character of the owner's name
--char convert ASCII code to characters
--len return string length
Select LEN (' ABCDEFG ') as length
--charindex returns the index of the first character of a string that appears at the beginning of a string
Select CHARINDEX (' d ', ' abcdefgh ')--index from the beginning, if the return value is zero, indicates that no
--difference returns the similarity, using 0-4 to indicate the degree of similarity
--select difference (' abcdefgh ', ' DCH ')
--left means the string is truncated from the left
Select Left (' ABCKKKKLVHJVJ ', 4)
--right to intercept the string from the right
Select Right (' ABCKKKKLVHJVJ ', 4)
--lower all converted to lowercase
Select LOWER (' SAKFKKJABK ')
--upper all converted to uppercase
--ltrim Remove the left space
Select LTRIM (' abc ')
--rtrim Remove the right space
Select RTRIM (' abc ')
--patindex equivalent to Charindex
--replace Find and replace
--select REPLACE (Sex, ' female ', ' girl ') from student
--replicate Copy and paste
Select REPLICATE (' abc ', 2)
--reverse Flip
Select REVERSE (' SGGZABZSB ')
--space spaces
Select ' A ' +space (5) + ' abc '
--str cast to String
--parameter one is the character to be converted, the second is the length of the reservation after conversion, and the number of digits retained after the decimal point
--Note: Parameter two cannot be converted when the integer part is less than the number of bits in parameter one
Select STR (5478.6331,7,3)
--stuff from the location of the first few indexes to see if you need to remove several
--then insert the content you want to insert
--Parameter one is the string that needs to be inserted
--parameter two is starting from the first few indexes
--parameter three is whether to delete several characters backwards
--parameter four is the newly inserted character
Select STUFF (' abcdef ', 3,0, ' HSGH ')
--substring Intercept string
Select SUBSTRING (' KSFKSDV ', 2, 3)

Practice:

CREATE TABLE Lianxi
(
code int,
Name varchar (20),
Sex char (10),
Tizhong Decimal (18,1),
Age int,
Yuwen Decimal (18,1),
Shuxue Decimal (18,1),
Yiyu Decimal (18,1),
Banji varchar (20)

)
Go
INSERT into Xueshengxinxi values (1., ' Zhao Yiqian ', ' Male ', 55,18,78,82,90, ' one shift ')
INSERT into Xueshengxinxi values (2., ' Money two ', ' female ', 50,19,75,92,80, ' second class ')
INSERT into Xueshengxinxi values (3., ' Magozo ', ' Male ', 68,20,58,78,94, ' one shift ')
INSERT into Xueshengxinxi values (4., ' John Doe ', ' female ', 58,24,68,89,76, ' Class two ')
INSERT into Xueshengxinxi values (5., ' Friday ', ' Male ', 75,23,75,74,56, ' Class two ')
INSERT into Xueshengxinxi values (6., ' Zheng ', ' female ', 54,21,78,80,84, ' one shift ')
INSERT into Xueshengxinxi values (7., ' Wu Qi ', ' Male ', 64,21,76,72,84, ' second class ')
INSERT into Xueshengxinxi values (8., ' Wang Ba ', ' Female ', 61,17,74,62,47, ' one class ')
INSERT into Xueshengxinxi values (9, ' Du Jiu ', ' female ', 57,19,76,85,94, ' second class ')
INSERT into Xueshengxinxi values (10., ' Li Chen ', ' Male ', 78,20,75,84,90, ' one shift ')
INSERT into Xueshengxinxi values (11., ' Wangbaoqiang ', ' Male ', 75,19,78,42,90, ' Class two ')
INSERT into Xueshengxinxi values (12., ' Chen Ying ', ' female ', 51,20,75,82,60, ' one shift ')
INSERT into Xueshengxinxi values (13., ' Maggie Cheung ', ' female ', 49,19,68,82,70, ' Class two ')
INSERT into Xueshengxinxi values (14., ' Jet Li ', ' Male ', 75,21,88,92,70, ' one shift ')
INSERT into Xueshengxinxi values (15., ' Hu ', ' Male ', 67,20,90,81,98, ' one shift ')
INSERT into Xueshengxinxi values (16., ' Huojianhua ', ' Male ', 68,19,78,85,60, ' Class two ')
INSERT into Xueshengxinxi values (17., ' Wang Kai ', ' Male ', 69,20,75,92,50, ' one shift ')
INSERT into Xueshengxinxi values (18., ' Liu Tao ', ' Female ', 52,18,79,72,59, ' one shift ')
INSERT into Xueshengxinxi values (19., ' Liu Lao Root ', ' male ', 70,20,56,81,64, ' second class ')
INSERT into Xueshengxinxi values (20., ' Brigitte ', ' female ', 60,18,85,67,91, ' one shift ')
Go
--Query All information
Select *from Lianxi
--to inquire about the people of all Wang clan
SELECT * from Xueshengxinxi where name like ' King% '
--Check all student information in one class
Select Banji as Class, * from Lianxi where banji= ' class by Banji
--Query The information of all personnel in class two
Select Banji as Class, * from Lianxi where banji= ' class two ' GROUP by Banji

--See all female classmates and weigh over 65 names
Select name from Lianxi where sex= ' woman ' and tizhong>65

--(separate query)
--Find out all the information of the highest and lowest students in a class of Chinese
Select top 1 Banji as class from Lianxi where banji= ' Class A ' group by Banji ORDER by Yuwen
Select top 1 Banji as class from Lianxi where banji= ' Class A ' group by Banji ORDER by Yuwen Desc

--Check out all the information of the students with the highest score and lowest score of the math class.
Select top 1 Banji as class from Lianxi where banji= ' Class A ' group by Banji ORDER by Shuxue
Select top 1 Banji as class from Lianxi where banji= ' Class A ' group by Banji ORDER by Yuwen Desc

--Check out all the information of the highest and lowest students in English

--All personnel information in a class in descending order of language
Select Banji as class, *from lianxi where Banji = ' One shift ' GROUP by Banji ORDER by Yuwen Desc
--Class II All personnel information in ascending order of English
--Two students in 75-class English
Select Banji as Class, COUNT (*) from Lianxi where yiyu>75 GROUP by Banji
-A class with a math score of 70 and more than 3 students
Select Banji as Class, COUNT (*) from Lianxi where shuxue>70 GROUP by Banji have COUNT (*) >3

-Two people with a 65-class weight
Select Banji as Class, COUNT (*) from Lianxi where tizhong>65 GROUP by Banji
-A class of more than 3 people weighing more than 65
Select Banji as Class, COUNT (*) from Lianxi where tizhong>65 GROUP by Banji have COUNT (*) >3

Mathematical functions, String functions