MySQL function introduction database application

MySQL functions

• Commonly used functions
• Aggregation functions
• Database level MD5 encryption

Commonly used functions

##RAND(x) Returns a random number from 0 to 1. When the x value is the same, the returned random number is the same. SELECT RAND(2) – 1.5865798029924SIGN(x )Returns the sign of x. If x is a negative number, 0, or a positive number, -1, 0, and 1 are returned respectivelySELECT SIGN(-10) – (-1)PI()Return pi (3.141593)SELECT PI()– 3.141593##TRUNCATE(x,y )ROUND(x)##ROUND( x,y)Retain the value of x to y decimal places, but round off when truncatingSELECT ROUND(1.23456,3) – 1.235POW(x,y).POWER(x,y)Return x raised to the power of ySELECT POW(2,3) – 8SQRT(x)Returns the square root of xSELECT SQRT(25) – 5EXP(x)Return e to the power of xSELECT EXP(3) – 20.085536923188MOD(x,y)Return The remainder after ySELECT MOD(5,2) – 1LOG(x)Returns the natural logarithm (with e as the base Logarithm of )SELECT LOG(20.085536923188) – 3LOG10(x)Returns the logarithm of base 10SELECT LOG10(100) – 2RADIANS(x)Convert angles to radiansSELECT RADIANS(180) – 3.1415926535898 DEGREES(x)Convert radians to anglesSELECT DEGREES(3.1415926535898) – 180SIN(x)Find the sine value (the parameter is radians)SELECT SIN(RADIANS(30)) – 0.5ASIN( x)Find the inverse sine value (the parameter is radians)Find the cosine Value (the parameter is radians)Find the inverse cosine value ( The parameter is radians)Find the tangent value (the parameter is radians)Find the arctangent value (the parameter is radians)Find the cotangent value (the parameter is radians)

Function

Function

Example

ABS(x)

Returns the absolute value of x

SELECT ABS(-1) – Returns 1

CEIL(x),CEILING(x)

Return the smallest integer greater than or equal to x

SELECT CEIL(1.5) – Return 2

FLOOR(x)

Returns the largest integer less than or equal to x

SELECT FLOOR(1.5) – Returns 1

RAND()

Returns a random number from 0 to 1

SELECT RAND() --0.6264973735683573

Returns the value of x to y decimal places (the biggest difference from ROUND is that it will not be rounded)

SELECT TRUNCATE(1.23456,3) – 1.234

Returns the integer closest to x

SELECT ROUND(1.23456) – 1

##COS(x)

SELECT COS(RADIANS(30)) --0.5

ACOS(x)

TAN(x)

SELECT TAN(RADIANS(45)) --1

ATAN(x) ATAN2(x)

COT(x)

-- 数学运算SELECT ABS(-8) as 绝对值 -- 绝对值SELECT CEILING(9.4) -- 向上取整SELECT FLOOR(9.4)  -- 向下取整SELECT RAND()	-- 返回一个 0-1 之间的随机数SELECT SIGN(-10)  -- 返回一个数的符号	0 返回 0  负数返回-1	正数返回 1

Related free learning recommendations: mysql video tutorial

##INSERT(s1,x,len,s2)Replace string s2 with a string of length len starting from the x position of s1SELECT INSERT('12345',1,3,'abc') – abc45UPPER(s),UCASE(S)Convert all letters of string s into uppercase lettersSELECT UPPER('abc') – ABCLOWER(s),LCASE(s)Convert all letters of string s into lowercase lettersSELECT LOWER('ABC') – abcLEFT(s,n)Returns the first n characters of string sSELECT LEFT('abcde',2) – abRIGHT(s,n)Returns the last n characters of string sSELECT RIGHT('abcde',2) – deLPAD(s1,len,s2)String s2 to fill the beginning of s1 so that the string length reaches lenSELECT LPAD('abc ',5,'xx') – xxabc##RPAD(s1,len,s2)LTRIM(s)RTRIM(s)TRIM(s)TRIM(s1 FROM s)REPEAT(s,n)SPACE(n)REPLACE (s,s1,s2)STRCMP(s1,s2)# #SUBSTRING(s,n,len)Get the string with length len starting from the nth position in string sMID(s,n,len)Same as SUBSTRING(s,n,len)Get the starting position of s1 from the string sGet the starting position of s1 from string sReverse the order of string sReturn the nth string##FIELD(s,s1,s2…)Return the first string position matching string sSELECT FIELD ('c','a','b','c') – 3FIND_IN_SET(s1,s2)Returns the same value as in string s2 The position of the string matched by s1Function

Function	Function	Example
CHAR_LENGTH(s)	Returns the characters of string s Number	SELECT CHAR_LENGTH('Hello 123') – 5
LENGTH(s)	Return the length of string s	SELECT LENGTH('Hello 123') – 9
CONCAT(s1,s2,…)	Convert strings s1, s2 and other strings Combined into one string	SELECT CONCAT('12','34') – 1234
string s2 to fill the end of s1, so that the string The length reaches len	SELECT RPAD('abc',5,'xx') – abcxx
Remove the string s The space at the beginning
Remove the space at the end of the string s
Remove spaces at the beginning and end of string s
Remove the string s1 at the beginning and end of the string s	SELECT TRIM('@' FROM '@@abc@@ ') – abc
Repeat string s n times	SELECT REPEAT('ab',3) – ababab
Return n spaces
Replace string s2 with string s1 in string s	SELECT REPLACE('abc','a','x') --xbc
Compare strings s1 and s2
		##LOCATE( s1,s),POSITION(s1 IN s)
SELECT LOCATE('b', 'abc') – 2		INSTR(s,s1)
SELECT INSTR('abc','b') – 2		REVERSE(s)
SELECT REVERSE('abc') – cba		ELT(n,s1,s2,…)
SELECT ELT(2,'a','b','c' ) – b
		-- 字符串函数SELECT CHAR_LENGTH('我们的征途是星辰和大海') -- 字符串的长度SELECT CONCAT('我','爱','猫猫') -- 拼接字符串SELECT INSERT('我爱编程helloworld',1,2,'超级热爱') -- 查询，从某个位置开始替换某个长度SELECT LOWER('MaoMao') -- 小写字母SELECT UPPER('maomao') -- 全变大写SELECT INSTR('maonmao','n') -- 返回第一次出现的子串的索引SELECT REPLACE('猫猫说坚持就能成功','坚持','努力') -- 替换出现的指定字符串SELECT SUBSTR('猫猫说坚持就能成功',4,3)   -- 返回指定的子字符串 (源字符串，截取的位置，截取的长度)SELECT REVERSE('猫猫说坚持就能成功') -- 反转-- 查询有田的同学，将田改成猪SELECT REPLACE(studentname,'田','猪') FROM studentWHERE studentname LIKE '%田'

FunctionExampleReturn the current dateSELECT CURDATE() –> 2021-01-09SELECT NOW()–> 2021-01 -09 10:03:14SELECT LOCALTIME()–> 2021-01- 09 10:03:14##UNIX_TIMESTAMP()Return the current time in the form of UNIX timestampSELECT UNIX_TIMESTAMP()->1617977084

-- 时间和日期函数（记住）SELECT CURRENT_DATE()	-- 获取当前日期SELECT CURDATE()	-- 获取当前日期SELECT NOW()	-- 获取当前的时间SELECT LOCALTIME()	-- 本地时间SELECT YEAR(NOW())SELECT MONTH(NOW())SELECT DAY(NOW())SELECT HOUR(NOW())SELECT MINUTE(NOW())SELECT SECOND(NOW())-- 系统SELECT SYSTEM_USER()SELECT USER()SELECT VERSION()

Aggregation function

		##CURDATE();CURRENT_DATE()
	NOW()	Return the current date and time
	LOCALTIME()	Return the current date and time

##Function name

Description

SUM()AVG()MAX()MIN()

-- ================ 聚合函数 ============-- 都能够统计 表中的数据 （想查询一个表中有多少个记录，就是用这个count()）SELECT COUNT(studentname) FROM student;	  -- COUNT(指定列)，会忽略所有的null值SELECT COUNT(borndate) FROM student;	-- 结果 8 少一个 因为是null
 SELECT COUNT(*) FROM student;	-- Count(*)	不会忽略所有的null值	本质 计算行数SELECT COUNT(1) FROM result;	-- Count(1)	不会忽略所有的null值	本质 计算行数SELECT SUM(studentresult) AS 总和 FROM resultSELECT AVG(studentresult) AS 平均分 FROM resultSELECT MAX(studentresult) AS 最高分 FROM resultSELECT MIN(studentresult) AS 最低分 FROM result-- 查询不同课程的平均分，最高分，最低分-- 核心：根据不同的课程分组SELECT any_value(`subjectname`) AS 科目名,AVG(studentresult) AS 平均分,MAX(studentresult) AS 最高分,MIN(studentresult) AS 最低分FROM result rINNER JOIN `subject` subON r.`subjectno` = sub.`subjectno`GROUP BY r.subjectno	-- 通过什么字段来分组-- 查询不同课程的平均分，最高分，最低分，平均分大于80SELECT any_value(`subjectname`) AS 科目名,AVG(studentresult) AS 平均分,MAX(studentresult) AS 最高分,MIN(studentresult) AS 最低分FROM result rINNER JOIN `subject` subON r.`subjectno` = sub.`subjectno`GROUP BY r.subjectno	-- 通过什么字段来分组HAVING 平均分>50

What is MD5 Mainly enhances algorithm complexity and irreversibility

COUNT()	Count
Sum
Average value
Maximum value
Minimum value
MD5 encryption at database level

MD5 is irreversible, the specific value of md5 is the sameMD5 The principle of cracking a website, there is a dictionary behind it , Value after MD5 encryption: Value before MD5 encryption

-- ====================  测试MD5 加密  ===================CREATE TABLE `testmd5`(
	`id` INT(4) NOT NULL,
	`name` VARCHAR(20) NOT NULL, 
	`pwd` VARCHAR(50) NOT NULL,
	PRIMARY KEY(`id`))ENGINE=INNODB DEFAULT CHARSET=utf8-- 明文密码INSERT INTO testmd5 VALUES(1,'zhangsan','123456'),(2,'lisi','123456'),(3,'wangwu','123456')-- 加密UPDATE testmd5 SET pwd=MD5(pwd) WHERE id = 1UPDATE testmd5 SET pwd=MD5(pwd) -- 加密全部的密码-- 插入的时候加密INSERT INTO testmd5 VALUES(4,'xiaoming',MD5('123456'))-- 如何校验：将用户传递进来的密码，进行md5加密，然后比对加密后的值SELECT  * FROM testmd5 WHERE `name`='xiaoming' AND pwd=MD5('123456')

More related free learning recommendations:

mysql tutorial

(video)

The above is the detailed content of MySQL function introduction database application. For more information, please follow other related articles on the PHP Chinese website!