MySQL function introduction database application-Mysql Tutorial-php.cn

Home

Database

Mysql Tutorial

MySQL function introduction database application

coldplay.xixi

Apr 12, 2021 am 09:23 AM

database application

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

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

Copy after login

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 '%田' Copy after login

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()

Copy after login

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

Copy after login

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')

Copy after login

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!

Statement of this Website

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress images for free

Clothoff.io

AI clothes remover

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

How to fix KB5055612 fails to install in Windows 10?

3 weeks ago By DDD

Roblox: Grow A Garden - Complete Mutation Guide

3 weeks ago By DDD

Roblox: Bubble Gum Simulator Infinity - How To Get And Use Royal Keys

3 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Nordhold: Fusion System, Explained

3 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Mandragora: Whispers Of The Witch Tree - How To Unlock The Grappling Hook

3 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

God-level code editing software (SublimeText3)

Hot Topics

Java Tutorial

1667

CakePHP Tutorial

1426

Laravel Tutorial

1328

PHP Tutorial

1273

C# Tutorial

1255

Related knowledge

MySQL's Role: Databases in Web Applications Apr 17, 2025 am 12:23 AM

The main role of MySQL in web applications is to store and manage data. 1.MySQL efficiently processes user information, product catalogs, transaction records and other data. 2. Through SQL query, developers can extract information from the database to generate dynamic content. 3.MySQL works based on the client-server model to ensure acceptable query speed.

Explain the role of InnoDB redo logs and undo logs. Apr 15, 2025 am 12:16 AM

InnoDB uses redologs and undologs to ensure data consistency and reliability. 1.redologs record data page modification to ensure crash recovery and transaction persistence. 2.undologs records the original data value and supports transaction rollback and MVCC.

MySQL's Place: Databases and Programming Apr 13, 2025 am 12:18 AM

MySQL's position in databases and programming is very important. It is an open source relational database management system that is widely used in various application scenarios. 1) MySQL provides efficient data storage, organization and retrieval functions, supporting Web, mobile and enterprise-level systems. 2) It uses a client-server architecture, supports multiple storage engines and index optimization. 3) Basic usages include creating tables and inserting data, and advanced usages involve multi-table JOINs and complex queries. 4) Frequently asked questions such as SQL syntax errors and performance issues can be debugged through the EXPLAIN command and slow query log. 5) Performance optimization methods include rational use of indexes, optimized query and use of caches. Best practices include using transactions and PreparedStatemen

MySQL vs. Other Programming Languages: A Comparison Apr 19, 2025 am 12:22 AM

Compared with other programming languages, MySQL is mainly used to store and manage data, while other languages such as Python, Java, and C are used for logical processing and application development. MySQL is known for its high performance, scalability and cross-platform support, suitable for data management needs, while other languages have advantages in their respective fields such as data analytics, enterprise applications, and system programming.

MySQL: From Small Businesses to Large Enterprises Apr 13, 2025 am 12:17 AM

MySQL is suitable for small and large enterprises. 1) Small businesses can use MySQL for basic data management, such as storing customer information. 2) Large enterprises can use MySQL to process massive data and complex business logic to optimize query performance and transaction processing.

How does MySQL index cardinality affect query performance? Apr 14, 2025 am 12:18 AM

MySQL index cardinality has a significant impact on query performance: 1. High cardinality index can more effectively narrow the data range and improve query efficiency; 2. Low cardinality index may lead to full table scanning and reduce query performance; 3. In joint index, high cardinality sequences should be placed in front to optimize query.

MySQL for Beginners: Getting Started with Database Management Apr 18, 2025 am 12:10 AM

The basic operations of MySQL include creating databases, tables, and using SQL to perform CRUD operations on data. 1. Create a database: CREATEDATABASEmy_first_db; 2. Create a table: CREATETABLEbooks(idINTAUTO_INCREMENTPRIMARYKEY, titleVARCHAR(100)NOTNULL, authorVARCHAR(100)NOTNULL, published_yearINT); 3. Insert data: INSERTINTObooks(title, author, published_year)VA

MySQL vs. Other Databases: Comparing the Options Apr 15, 2025 am 12:08 AM

MySQL is suitable for web applications and content management systems and is popular for its open source, high performance and ease of use. 1) Compared with PostgreSQL, MySQL performs better in simple queries and high concurrent read operations. 2) Compared with Oracle, MySQL is more popular among small and medium-sized enterprises because of its open source and low cost. 3) Compared with Microsoft SQL Server, MySQL is more suitable for cross-platform applications. 4) Unlike MongoDB, MySQL is more suitable for structured data and transaction processing.

See all articles