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

This article mainly introduces the example of single table query in MySQL database, which is very detailed and has certain reference value. Friends who are interested must finish it!

1. Simple query

1. Select statement

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

From table name

[where conditional expression 1]

[groupby field name [having conditional expression 2]]

[orderby field name [asc | desc]]

[offset] number of records

(1) Distinct is an optional parameter, which is used to eliminate duplicate data in query results.

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

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

(4) Limit is an optional parameter, which is 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. If the offset is n, it starts from the n + 1 record in the query result. If not specified, the default is 0. The second parameter, 'number of records', represents the number of records returned for the query.

two。 Query all fields

(1) specify all fields in the select statement

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

3. Query the specified field

Second, inquire according to the conditions

1. Query with relational operator

two。 Query with the in keyword: the in keyword is used to determine whether the value of a field is in the specified collection.

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

4. Null value query

5. Query with distint keyword: filter out duplicate values in the query record

When the distinct keyword acts on multiple fields, it is considered a duplicate record only if the values of multiple fields specified after it are the same.

6. Query with the 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 an empty string

You can use multiple% wildcards or with not

(2) underscore (_) wildcard: you 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 can be no spaces between underscores. For example, there is a space in the middle of'M_ QL'', which can only match 'MySQL', not' MySQL'.

(3) use percent sign and underscore wildcard for query operation:

Note: if you want to match the percent good and underscore in the string, you need to escape the percent sign and underscore in the bronze string, such as'\% 'matches the literal value of the percent sign.

7. Multi-conditional query with and keyword: use the and keyword to join two or more query conditions, and only records that meet all conditions are returned. Each additional query condition adds an extra and keyword.

8. Multi-conditional query with the or keyword: a record is returned as long as one condition is met.

9. When the Or and and keywords are used together: and takes precedence over or, conditional expressions on both sides of and should be evaluated first, and then conditional expressions on both sides of or should be evaluated.

III. Advanced enquiries

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

(1) 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 the values of a field in the table: select sum (field name) from table name

(3) the avg () function is used to find the average of all values of a field: select avg (field name) from table name.

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

(5) the min () function is the minimum function: selectmin (field name) from table name

two。 Sort 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. Grouping query

Select field name 1, field name 2, … From table name group by field name 1, field name 2, … [having conditional expression]

(1) grouping using group by separately: the query results are classified by different values in the installation field, and the query results show only one record in each group.

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

(3) Group by and having keywords are used together.

The Having keyword and the where keyword serve the same purpose and are 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, while the where keyword cannot. The having keyword is usually used 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.

Select field name 1, field name 2, … From table name limit [offset,] number of records

5. Function (list)

Mathematical function

Function name

Action

Abs (x)

Returns the absolute value of x

Sqrt (x)

Returns the non-negative quadratic root of x

Mod (XBI y)

Returns the remainder of x divided by y

Ceiling (x)

Returns the smallest integer not less than x

Floor (x)

Returns the largest integer not greater than x

Round (XBI y)

Round x to retain y digits after the decimal point

Runcate (XBI y)

Strip out the number after the y place of the x decimal point

Sign (x)

Returns the symbol of x,-1, 0 or 1

String function

Function name

Action

Length (str)

Returns the length of the string str

Concat (S1 ~ S2, …)

Returns a new string generated by one or more string concatenations

Trim (str)

Delete spaces on both sides of a string

Replace (str,s1,s2)

Replace all strings S1 in the string str with the string S2

Substring (str,n,len)

Returns a substring of the string str, starting at n and with a length of len

Reverse (str)

Returns the result after string inversion

Locate (s1thm str)

Returns the starting position of the substring S1 in the string str

Date and time function

Function name

Action

Curdate ()

Get the current date of the system

Curtime ()

Get the current time of the system

Sysdate ()

Get the current system date and time

Time_to_sec ()

Returns the result of converting time into seconds

Adddate ()

Add operation of execution date

Subdate ()

Subtraction of execution date

Date_format ()

Format output date and time values

Conditional judgment function

Function name

Action

If (expr, v1, v2)

Returns v1 if the expr expression is true, v2 otherwise

Ifnull (v1Jing v2)

If v1 does not return v1 for null, otherwise return v2

Case expr when v1 then R1 [when v2 then R2 …] [else rn] end

If the expr value is equal to v1, v2, etc., the result after the corresponding position then is returned, otherwise the result rn after else is returned.

Encryption function

Function name

Action

Md5 (str)

MD5 join the string str

Encode (str, pwd_str)

Use pwd as the password encryption string str

Decode (str, pwd_str)

Use pwd as the password to decrypt the string str

(1) Concat (str1,str2, …) The result is the string generated by the connection parameter. If any of the parameters is null, the return value is null.

Aliases for tables and fields

1. Alias the table: select * from table name [as] alias

For the following example, s.gender represents the gender field of the student table

two。 Alias the field: select field name [AS] alias [, field name [as] alias, …] From table name

The above is all the contents of the article "example of single table query in MySQL database". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow 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.