MySQL database single table query

1. Simple query

1. Select statement

Select [distinct] * | {field name 1, field name 2, field name 3,. . . }

        From table name

                                          posed ‐ ’ s ’s ’s                     ’ s ’ s ’ s ’ to ’ s ’ ’ s ‐ ‐ ‐ ‐ ‐ From table name                                                                                     ‐ to ’s ’s ’ ’s ’s ’s ’ s it ’ s                          Name [asc|desc]]

                                                                                                                                                                                                             ##(2) Group by is an optional parameter, used to group query results according to specified fields; having is also an optional entry, used to filter the grouped results

(3) Order by Is an optional parameter, used to sort the query results according to the specified field. The sorting method is controlled by the parameter ASC or DESC. If not specified, the default is ascending order (ASC)

(4) Limit is optional Parameter, used to limit the number of query results. Limit can be followed by two parameters. The first parameter offset represents the offset. If the offset is 0, it starts from the first record of the query result. The offset is n starts from the n 1th record in the query results. If not specified, the default is 0. The second parameter 'number of records' indicates the number of query records returned.

2. Query all fields

(1) Specify all fields in the select statement

(2) Use in the select statement *Wildcards replace all fields: query results can only be displayed in the order in which the fields are defined in the table.

3. Query the specified fields

## 2. Query by conditions

1. Query with relational operators

2. Query with in keyword: The in keyword is used to determine whether the value of a field is in the specified set .

3. Query with between and keyword: used to determine whether the value of a field is within the specified range Inside.

4. NULL value query

5. Query with distinct keyword: filter out the query Duplicate values ​​in the record

When the distinct keyword is applied to multiple fields, only the values ​​of the multiple fields specified after it are the same will it be considered a duplicate record .

6. Query with like keyword: The like keyword can determine whether two strings match. The format is as follows:

SELECT * | [{Field Name 1, Field Name 2, ...} From table name

WHERe field name [not] like 'match string';

(1) Percent sign (%) wildcard: matches a string of any length, including the empty string

You can use multiple % wildcards, or Can be used with not

(2) Underscore (_) wildcard: can only match a single character. If you want to match multiple characters, you need to use multiple underscore wildcards. If you use multiple underscores to match multiple consecutive characters, there must be no spaces between the underscores. . For example, if there is a space in the middle of 'M_ _QL', it can only match 'My SQL' but not 'MySQL'.

(3) Use the percent sign and underscore wildcard characters for query operations:

Note: If you want to match the percent sign and underscore in the string, You need to use '\' in the bronze string to escape the percent sign and underscore, for example, '\%' matches the percent sign literal value.

7. Multi-condition query with and keyword: Use the and keyword to connect two or more query conditions. Only records that meet all conditions will be returned. For each additional query condition, add one more and keyword.

8. Multi-condition query with or keyword: records will be returned as long as one condition is met.

9. The situation when Or and the keywords are used together: and has a higher priority than or. The conditional expressions on both sides of and should be evaluated first, and then both sides of or. conditional expression.

3. Advanced query

1. Aggregation functions: count(),sum(),avg(),max( ) and min()

(1) The count() function is used to count the number of records: selectcount(*) from table name

(2 ) The sum() function is used to find the sum of all values ​​of a field in the table: select sum(field name) from table name

(3) avg() function Used to find the average of all values ​​in a field: select avg (field name) from table name;

(4) The max() function is used to find the maximum value Function, used to find the maximum value of a field: select max(field name) from table name.

(5) The min() function is a function that finds the minimum value: selectmin (field name) from table name

2. Sort the query results

Select field name 1, field name 2,... from table name order by field name 1 [ASC | DESC], field name 2 [ASC | DESC]...

3. Group query

Select field name 1, field name 2,... from table name group by field Name 1, field name 2,... [having conditional expression];

(1) Use group by alone: ​​The query results are classified by different values ​​​​in the field, and the query results only display the values ​​in each group a record.

(2) Group by is used together with the aggregate function

(3) Group by is used together with the having keyword Using the

Having keyword and the where keyword have the same effect. They are both used to set conditional expressions to filter query results. The difference between the two is that the having keyword can be followed by an aggregate function, but the where keyword cannot. . Usually the having keyword is used together with group by to filter the grouped results.

4. Use LIMIT to limit the number of query results: specify which record the query results start from and how many pieces of information are queried in total.

Select field name 1, field name 2,... from table name limit [offset,] Number of records

5. Function (list)

Mathematical function

##Round(x,y)Rounds x, retaining the decimal point y digit##Runcate(x,y)Sign(x)

Function name	Function
Abs(x)	Returns the absolute value of x
Sqrt(x)	Returns the non-negative square root of x
Mod(x ,y)	Returns the remainder after x is divided by y
##Ceiling(x)	Return the smallest integer that is not less than x
Floor(x)	Return No The largest integer greater than x
Truncate the number after the decimal point y digit in x
Returns the sign of x, -1, 0 or 1

String function

Function name##Length(str)Concat(s1,s2,…)Trim(str)Replace(str,s1,s2)Substring(str,n,len)Reverse(str)Locate(s1,str)

Function
Returns the length of the string str
Returns a new string generated by concatenating one or more strings
Remove spaces on both sides of the string
Use string s2 to replace all strings s1 in string str
Returns the substring of string str, starting position is n, length is len
Returns the result after the string is reversed
Returns the starting position of substring s1 in the string str

Date and time functions

Function nameCurdate()Curtime()Performs the addition operation of datesPerform date subtraction operationFormatted output date and time valuesConditional judgment function

Function
Get the current date of the system
Get the current system time	##Sysdate()
Get the current system date and time	Time_to_sec()
Return to convert time into seconds The result	##Adddate()
	Subdate()
	Date_format()

Function name

FunctionIf the expr expression is true, return v1, otherwise return v2If v1 is not null return v1, otherwise return v2If the expr value is equal to v1, v2, etc., return the result after the corresponding position then, otherwise return the result after else rn

	If(expr, v1, v2)
	##Ifnull(v1, v2)
	Case expr when v1 then r1 [ when v2 then r2…] [else rn] end
	Encryption function

Function name

Function##Md5(str)MD5 join the string strEncode(str, pwd_str)Use pwd as password to encrypt the string strDecode(str, pwd_str)Use pwd as password to decrypt the string str

(1) Concat(str1,str2,…) returns the string generated by the connection parameters. If any parameter is null, the return value is null. 4. Alias ​​names for tables and fields 1. Alias ​​names for tables: select * from Table name [as] alias; In the following example, s.gender represents the gender field of the student table 2. Give an alias for the field: select field name [AS] alias [, field name [as] alias,...] from table name; This article explains the MySQL database single table query, please pay attention to php for more related content Chinese website. Related recommendations: $Selector--how to encapsulate DOM into jquery objects Native js componentization Develop simple carousel chart example code css3 animated navigation bar 3D

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