Shulou(Shulou.com)05/31 Report--

Xiaobian to share with you what MySQL functions, I believe most people do not know how, so share this article for everyone's reference, I hope you have a lot of gains after reading this article, let's go to understand it together!

MySQL functions

common functions

aggregate function

MD5 encryption at database level

common functions

ABS(x) returns the absolute value of x SELECT ABS(-1) -returns 1CEIL(x),CEILING(x) returns the smallest integer greater than or equal to x SELECT CEIL (1.5) -Returns 2FLOOR(x) Returns the largest integer less than or equal to x SELECT FLOOR (1.5) -returns 1RAND() returns a random number 0->1 SELECT RAND() --0.6264973735683573RAND(x) returns a random number 0->1, and returns the same random number when x values are the same SELECT RAND(2)-1.5865798029924SIGN (x) returns the sign of x, x is negative, 0, positive returns-1, respectively 0 and 1SELECT SIGN(-10) - (-1)PI() returns pi (3.141593) SELECT PI()- 3.141593TRUNCATE(x,y) Returns the value x to y decimal places (The biggest difference from Round is that rounding is not performed) SELECT TRUNCATE (1.23456,3) - 1.234ROUND(x) Returns the nearest integer to x SELECT ROUND (1.23456) - 1ROUND(x,y) Keep x to y decimal places, but round off SELECT ROUND (1.23456,3)-1.235POW (x,y).POWER(x,y) returns x to the y power SELECT POW(2,3)-8 SQRT(x) returns the square root of x SELECT SQRT(25)-5EXP (x) returns e to the x power SELECT EXP(3)-20.085536923188MOD (x, y) y) Returns the remainder of x divided by y SELECT MOD(5, 2)-1 LOG(x) Returns the natural logarithm (base e logarithm)SELECT LOG (20.085536923188) - 3LOG10(x) Returns base 10 logarithm SELECT LOG10(100) - 2RADIANS(x) Convert angles to radians SELECT RADIANS(180) - 3.1415926535898DEGREES(x) Convert radians to angles SELECT DEGREES (3.1415926535898) - 180SIN(x) sine (parameter is radians)SELECT SIN(RADIANS(30)) - 0.5ASIN(x) arcsine (parameter is radians)

COS(x) Find cosine (argument is radians)SELECT COS(RADIANS(30)) --0.5ACOS(x) Find inverse cosine (argument is radians)

TAN(x) tangent (argument is radians)SELECT TAN(RADIANS(45)) --1ATAN(x) ATAN2 (x) arctangent (argument is radians)

COT(x) Find the cotangent (parameter is radians)

--Mathematical operation SELECT ABS(-8) as Absolute value--Absolute value SELECT CEILING(9.4) --Round up SELECT FLOOR(9.4) --Round down SELECT RAND() --Returns a random number between 0 and 1 SELECT SIGN(-10) --Returns the sign of a number 0 returns 0 negative returns-1 Positive returns 1

Related free learning recommendation: mysql video tutorial

CHAR_LENGTH(s) Returns the number of characters in string s SELECT CHAR_LENGTH ('hello 123')-5LENGTH (s) Returns the length of string s SELECT LENGTH ('hello 123')-9 CONCAT(s1,s2,…) Combines strings s1,s2, etc. into a single string SELECT CONCAT ('12','34') - 1234INSERT (s1,x,len,s2) Replace the string s2 with the string SELECT INSERT starting at the x position of s1 with the string len ('12345', 1, 3,' abc')- abc45UPPER(s),UCAASE(S) Change all letters of string s to upper case SELECT UPPER ('abc ') - ABCLOWER(s),LCASE(s) Change all letters of string s to lower case SELECT LOWER ('ABC') - abcLEFT(s, n) Returns the first n characters of string s SELECT LEFT ('abcde', 2) - abRIGHT(s,n) Returns the last n characters of string s SELECT RIGHT ('abcde',2) - deLPAD (s1,len,s2) string s2 to fill the beginning of s1, making the string length lenSELECT LPAD ('abc', 5,' xx')- xxabcRPAD(s1,len,s2) string s2 to fill the end of s1, making the string lenSELECT RPAD('abc', 5,' xx')- abcxxLTRIM(s) remove spaces at the beginning of string s

RTRIM(s) removes spaces at the end of string s

TRIM(s) removes spaces at the beginning and end of string s

TRIM(s1 FROM s) removes the string at the beginning and end of string s s1SELECT TRIM ('@' FROM '@@abc@@')- abcREPEAT(s,n) repeats string s n times SELECT REPEAT ('ab ',3) -ababSPACE (n) returns n spaces

REPLACE(s,s1,s2) Replace string s1 in string s with string s2 SELECT REPLACE ('abc ',' a','x')--xbcSTRCMP(s1,s2) Compare strings s1 and s2

SUBSTRING(s,n,len) Gets a string of length len starting at the nth position in the string s

MID(s,n,len) Same as SUBSTRING(s,n,len)

LOCATE(s1,s),POSITION(s1 IN s) Gets the start position of s1 from the string s SELECT LOCATE ('b',' abc')-2 INSTR(s,s1) Get the start of s1 from string s SELECT INSTR ('abc',' b')-2 REVERSE(s) Reverse the order of string s SELECT REVERSE ('abc ') - cbaELT (n,s1,s2,…) Returns the nth string SELECT ELT(2,'a',' b','c')- bFIELD(s,s1,s2…) Returns the position of the first string matching string s SELECT FIELD(' c','a',' b','c')- 3FIND_IN_SET(s1,s2) Returns the position of the string matching s1 in string s2

--String function SELECT CHAR_LENGTH ('Our journey is the stars and the sea')--Length of string SELECT CONCAT ('I ',' Love','Cat') --concatenate string SELECT INSERT ('I love programming helloworld', 1,2,'super love') --Query, replace a length from a position SELECT LOWER ('MaoMao ') --lowercase SELECT UPPER ('maomao ') --all-caps SELECT INSTR ('maonmao','n') --Returns the index of the first occurrence of the substring SELECT REPLACE ('Cat says persevere ',' persever','strive')--replaces the occurrence of the specified string SELECT SUBSTR ('Cat says persevere', 4,3) --Returns the specified substring (source string, position of truncation, length of truncation)SELECT REVERSE ('cat says persistence will success') --Reverse--Query students with fields, change fields to pigs SELECT REPLACE (studentname,'field',' pig') FROM studentWHERE studentname LIKE '% field' Function example CURDATE();CURRENT_DATE() returns the current date SELECT CURDATE()-> 2021-01-09NOW() returns the current date and time SELECT NOW()-> 2021-01-09 10:03:14LOCALTIME() returns the current date and time SELECT LOCALTIME()-> 2021-01-09 10:03: UNIX_TIMESTAMP () returns the current time as a UNIX timestamp SELECT UNIX_TIMESTAMP()->1617977084--time and date functions (remember) SELECT CURRENT_DATE() --gets the current date SELECT CURDATE() --GET CURRENT DATE SELECT NOW() --GET CURRENT TIME SELECT LOCALTIME() --LOCAL TIME SELECT YEAR(NOW())SELECT MONTH(NOW ())SELECT DAY(NOW ())SELECT HOUR(NOW())SELECT MINUTE(NOW())SELECT SECOND(NOW())--SYSTEM SELECT SYSTEM_USER()SELECT USER()SELECT VERSION()

aggregate function

function name Description COUNT() Count SUM() Sum AVG() Average MAX() Maximum MIN() Minimum--================ -- COUNT(specified column), ignores all null values SELECT COUNT(borate) FROM student; Select Count(*) FROM student; -- Count(*) All null values are not ignored. SELECT COUNT(1) FROM result; -- Count(1) All null values are not ignored. Select Sum (studentresult) AS SUM FROM resultSELECT AVG (studentresult) AS Mean Score FROM resultSELECT MAX (studentresult) AS Highest Score FROM resultSELECT MIN (studentresult) AS lowest score FROM result--query average score, highest score, lowest score of different courses--core: SELECT any_value(`subjectname`) AS subject name,AVG(studentresult) AS average score,MAX(studentresult) AS maximum score,MIN(studentresult) AS minimum score FROM result rINNER JOIN `subject` subON r.` subjectno` = sub.` subjectno`GROUP BY r.subjectno --By what field to group--Query average score, highest score, lowest score of different courses, average score greater than 80SELECT any_value(`subjectname`) AS subject name,AVG(studentresult) AS average score,MAX(studentresult) AS highest score,MIN(studentresult) AS lowest score FROM result rINNER JOIN `subject` subject ON r.` subjectno` = sub.` subjectno`GROUP BY r.subjectno --By what field are you grouped HAVING average score>50

MD5 encryption at database level

What is MD5?

Main enhancements Algorithm complexity and irreversibility

MD5 is irreversible, and the exact value of MD5 is the same.

MD5 crack the principle of the website, there is a dictionary behind it, MD5 encrypted value: MD5 encrypted value

--==================== `id` INT(4) NOT NULL, `name` VARCHAR(20) NOT NULL, `pwd` VARCHAR(50) NOT NULL, PRIMARY KEY(`id`)ENGINE=INNODB DEFAULT CHARSET=utf8--plaintext PASSWORD INSERT INTO testmd5 VALUES (1,'zhangsan','123456'),(2,'lisi','123456'),(3,'wangwu',' 123456')--Encrypt UPDATE testmd5 SET pwd=MD5(pwd) WHERE id = 1UPDATE testmd5 SET pwd=MD5(pwd) --Encrypt all passwords--Encrypt INSERT INTO testmd5 VALUES when inserting (4,'xiaoming',MD5 ('123456 '))--How to verify: md5 encrypt the password passed in by the user, and then compare the encrypted value SELECT * FROM testmd5 WHERE `name`='xiaoming' AND pwd=MD5 ('123456') The above is "What are MySQL functions?" All the contents of this article, thank you for reading! I believe that everyone has a certain understanding, hope to share the content to help everyone, if you still want to learn more knowledge, welcome to pay attention to the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.