MySQL Database advanced query (i)

Common Functions

Common aggregate functions :

AVG ()	Returns the average of a field
COUNT ()	Returns the number of rows in a field
MAX ()	Returns the maximum value of a field
MIN ()	Returns the minimum value of a field
SUM ()	Returns the and of a field
Name of function	Role

Commonly used String functions

Name of function	Role	Example
CONCAT (str1,str2,..., strn)	Connection string Span style= "Font-family:calibri" >str1,str2,..., strn	Select CONCAT (' My ', ' S ', ' QL '); Return :mysql
INSERT (str,pos,len,newstr)	String Span style= "Font-family:calibri" >str from pos position start ,len characters long substring replaced with string newstr	SELECT INSERT (' This is sql SERVER database ', 3,10, ' MySQL '); Return : This is mysql database
LOWER (str)	turns all characters in the string str to lowercase
UPPER (str)	capitalize all strings in the string str	SELECT UPPER (' MySQL '); Back to : MYSQL
SUBSTRING (Str,num,len)	Returns a substring of the first num position of the string str with a length of Len

3. time-Date function

Name of function	Role	Example
Curdate ()	Get Current date	SELECT curdate ();
Curtime ()	Get current time
Now ()	Get the current date and time
WEEK (date)	Return Date is the week ordinal of a year
Year (date)	Returns the year of the date
HOUR (Time)	Returns the hour value of time
MINUTE (Time)	Returns the minute value of time
DATEDIFF (DATE1,DATE2)	Returns the number of days separated by the date parameter date1 and date2
Adddate (Date,n)	Calculate date parameter date plus n days of day

4. Numeric functions

Function name	Effect	example
Ceil (x)	" Returns the smallest integer greater than or equal to the value x	SELECT Ceil (2.3) Return :3
Floor (x)	Returns less than or equal to the value x
RAND ()	return 0~1

Sub-query Simple sub-query

syntax : SELECT ... From table 1 WHERE field 1 comparison operator ( subquery );

Use As keyword , ANSI compliant

The alias of the SELECT field List from the table name as table ;

a simple way to use spaces

The alias of the SELECT field list from the table name table ;

MySQL Database advanced query (i)