SQL Math Function Learning

  notes from the "Programmer's SQL Code" by Yang Zhengko, a teacher of the ROC web

#创建数据库表CREATE Table T_person (fidnumber VARCHAR ( -), FName varchar ( -), Fbirthday datetime, Fregday datetime, Fweight DECIMAL (Ten,2)) #插入数据insert into T_person (fidnumber,fname,fbirthday,fregday,fweight) VALUES ('123456789120','Tom','1981-03-22','1998-05-01',56.67) insert into T_person (fidnumber,fname,fbirthday,fregday,fweight) VALUES ('123456789121','Jim','1987-01-18','1999-08-21',36.17) insert into T_person (fidnumber,fname,fbirthday,fregday,fweight) VALUES ('123456789122','Lily','1987-11-08','2001-09-18',40.33) insert into T_person (fidnumber,fname,fbirthday,fregday,fweight) VALUES ('123456789123','Kelly','1982-07-12','2000-03-01',46.23) insert into T_person (fidnumber,fname,fbirthday,fregday,fweight) VALUES ('123456789124','Sam','1983-02-16','1998-05-01',48.68) insert into T_person (fidnumber,fname,fbirthday,fregday,fweight) VALUES ('123456789125','Kerry','1984-08-07','1999-03-01',66.67) insert into T_person (fidnumber,fname,fbirthday,fregday,fweight) VALUES ('123456789126','Smith','1980-01-09','2002-09-23',51.28) insert into T_person (fidnumber,fname,fbirthday,fregday,fweight) VALUES ('123456789127','billgates','1972-07-18','1995-06-19',60.32);#1, the absolute value: MySQL, MSSQL Server are suitable for the #abs (X) function, the function accepts a parameter. Selectfweight- -, ABS (fweight- -), ABS (-5.38) from t_person#2, Index: MySQL, MSSQL Server are suitable for #power (x, y) function, the function accepts two parameters. The first argument is the expression to be exponentiation, and the second argument is a power. SelectFweight, Power (fweight,-0.5), POWER (Fweight,2), POWER (Fweight,3), POWER (Fweight,4) fromt_person#3, square root: MySQL, MSSQL Server are suitable for #sqrt (X) functions. The function accepts a parameter. This parameter is the expression of the square root to be computed. SelectFWEIGHT,SQRT (Fweight) fromt_person#4, seeking the Rand () function in the random number #mysqlSelectRand ( ) #MSSQL the rand () function in Server. Same as the MySQL use method. But it also provides a parameter Rand (X) which is a random number seed. SelectRand ( .)#5, rounded to the largest integer----the number of ceilings to be taken. This function is used to drop a number after the decimal point and rounded up to the nearest largest integer. Like what3.33will be rounded to4ceil (x), CEILING (x) can be used in #MySQLSelectFweight,ceil (fweight) from T_personSelectFweight,ceiling (Fweight) fromonly CEILING (X) is supported in T_person#mssql ServerSelectFweight,ceiling (Fweight) fromt_person#6, rounded to the smallest integer----the number of floors to be taken. This function is used to drop a number after the decimal point and round down to the nearest smallest integer. Like what3.33will be rounded to3the floor (X) function in the #MySQL, MSSQL Server. SelectFweight,floor (Fweight) fromT_person
#7, rounding----The ROUND () function rounds the numeric value to the nearest value. In English ROUND can be understood as "radius", rounded to "nearest to my radius" of course, is also rounded up.  #ROUND (X) ROUND (x,d) function The function can accept a parameter, or it can accept two parameters. #1), two parameters of the round (x,d) function.        X is the value to be rounded, and d is the calculated precision, which is the number of decimal digits that are retained when rounding. When D is0The decimal place is not retained for rounding. Like what3.663Rounding with a precision of 2 gets3.66. When D is0The decimal place is not retained for rounding. Like what3.663The precision is0The rounding gets4Note: D can also be negative. This indicates rounding in the integer part such as233.7The precision is-2The rounding gets $。36.63The precision is-1The rounding gets +. SelectFName, Fweight, ROUND (Fweight,1), ROUND (fweight*-1,0), ROUND (fweight,-1) fromT_person #2), a single parameter of the round (X) function. X is the value to be rounded, which can be seen as a precision of0The rounding operation, i.e. ROUND (m,0) such as3.663Rounded up to get4SelectFname,fweight,round (Fweight), ROUND (fweight*-1) from t_person#8), the sine value: #SIN (X) The function accepts a parameter. SelectFname,fweight, SIN (fweight) fromt_person#9), cosine: #COS (X) The function accepts a parameter. SelectFName, Fweight,cos (fweight) fromt_person#Ten), the inverse sine value: #ASIN (X) The function accepts a parameter. SelectFName, Fweight, ASIN (1/fweight) From t_person# One), Inverse cosine: #ACOS (X) The function accepts a parameter. SELECT FName, Fweight, ACOS (1/fweight) From t_person# A), tangent value: #TAN (X) The function accepts a parameter. SelectFName, Fweight, TAN (fweight) fromt_person# -), the inverse tangent value: ATAN (x) ATAN (ATAN2) is supported in the #MySQL. The latter two results in the same calculation. #ATAN2 (x, y), the function returns2the inverse tangent of the variable X and YSelectFName, Fweight, ATAN (fweight) fromT_personSelectFName, Fweight, ATAN (Fweight,2) fromT_personSelectFName, Fweight, ATAN2 (Fweight,2) fromT_person#mssql Server supports ATAN (x) ATN2SelectFName, Fweight, ATAN (fweight) fromT_personSelectFName, Fweight, ATN2 (Fweight,2) fromt_person# -), cotangent: #COT (X) The function accepts a parameter. SelectFName, Fweight, COT (fweight) fromt_person# the), pi value: #PI () The function does not accept parameters. SelectFName, Fweight, Fweight*pi () fromt_person# -), radians to the angle system: #DEGREES (X) The function accepts a parameter. Angle System= Radian System * the/πSelectFName, Fweight, DEGREES (fweight) fromT_personSelectFName, Fweight, (fweight* the)/acos (-1) fromt_person# -), the angle system is converted to radians: #RADIANS (X) The function accepts a parameter. Radian System= Angle System *π/ theSelectFName, Fweight, RADIANS (fweight) fromT_personSelectFName, Fweight, (Fweight*acos (-1))/ the  fromt_person# -), Symbol: #SIGN (X) The function accepts a parameter. If the value is greater than0The return1, if the value equals0The return0, if the value is less than0Then return-1. SelectFName, fweight-48.68, sign (fweight-48.68) from t_person# +), to divide the remainder: #MySQL中提供了MOD () function: Used to calculate the remainder after two numbers are divisible. The first argument is a divisor, and the second argument is dividend. SelectFname,fweight,mod (Fweight,5) from T_person#mssql server does not support mod (). But the direct support operator"%"used to calculate the integer remainder of two numbers. SelectFName, Fweight, fweight%5  fromt_person# -), natural logarithm: #MySQL中: #LOG (X) The function accepts a parameter. Also accepts a parameter of two numbers. #LOG (b,x) B is the integer entered for itself. X is the parameter to be asked. The logarithm of the bottom of the #MySQL支持以2为底, base 10, at any self-input. SelectFName, Fweight, LOG (fweight) fromT_personSelectFName, Fweight, LOG2 (fweight) fromT_personSelectFName, Fweight, LOG10 (fweight) fromT_personSelectFName, Fweight, LOG (2, Fweight) fromt_person#mssql Server: #只支持LOG (x), LOG10 (x) is the base logarithm. SelectFName, Fweight, LOG (fweight) fromT_personSelectFName, Fweight, LOG10 (fweight) fromt_person# +), exponentiation: #POWER (x, Y) the function accepts two parameters. X is the second power Y for the field #mysqlSelectFName, Fweight, POWER (2, Fweight) fromT_personSelectFName, Fweight, POW (2, Fweight) fromsupport for POWER (x, y) in T_person#mssql ServerSelectFName, Fweight, POWER (2, Fweight) fromT_person

SQL Math Function Learning