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

This article mainly introduces the relevant knowledge of "what are the common operations of MySQL conditional query sentences". The editor shows you the operation process through actual cases, and the operation method is simple, fast and practical. I hope this article "what are the common operations of MySQL conditional query sentences" can help you solve the problem.

As the name implies, a conditional query uses the where sentence to filter out the data that meets the criteria.

Syntax:

Select

From

Where

Here we take the t_user table as an example.

-- query information SELECT * FROM t_user WHERE sex=' male'--query information that gender is not male SELECT * FROM t_user WHERE NOT sex=' male'--query information of gender male and age 20 SELECT * FROM t_user WHERE sex=' male 'AND age=20-- query information of gender male or age 20 SELECT * FROM t_user WHERE sex=' male' OR age=20 fuzzy query

LIKE

Whether it matches a pattern or not can be used with wildcards to determine whether a character is numeric or numeric. Wildcard:% any number of characters, including 0 characters _ any single character

We know that in the actual query, often we only need general information to find the results we need (such as Taobao search for goods, etc.), and this requires a fuzzy query, for example:

-- _ underscore matches one character at a time--% can match 0 or more characters at a time SELECT * FROM t_user WHERE NAME LIKE'_ Lee _ 'SELECT * FROM t_user WHERE NAME LIKE' b__'SELECT * FROM t_user WHERE NAME LIKE'b%'

The result of the third sql query above is as follows:

Between the two between and, contains a critical value; in determines whether the value of a field belongs to an IS NULL (empty) or IS NOT NULL (not empty) in the in list

-- search for information between 100 and 130 weights, including 100 and 130SELECT * FROM t_user WHERE weight BETWEEN 100 AND 130-- query information with weights of 100 or 110 SELECT * FROM t_user WHERE weight IN (100110)-- search for birthday information that is null or not null SELECT * FROM t_user WHERE birthday IS NULLSELECT * FROM t_user WHERE birthday IS NOT NULL

It should be noted here that if we want to use null as the query condition of where, it cannot be written as follows

-- SELECT * FROM t_user WHERE birthday=NULL

There is no query result in this way, so we need to use is null here.

Union

Using union or union all will merge the query results of the two sql statements

When using union, mysql deletes duplicate records in the result set, while with union all, mysql returns all records and is more efficient than union.

SELECT * FROM t_user WHERE age=20 UNION SELECT * FROM t_user WHERE sex=' male'

Query results:

SELECT * FROM t_user WHERE age=20 UNION ALLSELECT * FROM t_user WHERE sex=' male'

Query results:

Sort

Sort the query results using the ORDER BY clause to sort order by sort order ASC/DESC asc represents ascending order, desc represents descending order, if not written, the default is ascending order by clause that can support a single field, multiple fields, expressions, functions, aliases

-- SELECT * FROM t_user ORDER BY weight ASC-- in ascending order of weight, SELECT * FROM t_user ORDER BY weight DESC-- in descending order of weight, if the weight is equal, arrange it according to student number, ascending order SELECT * FROM t_user ORDER BY weight ASC, limit the number of number ASC

Limit clause: limit the number of displayed results of a query (position at the end of the sql statement)

In the actual query, a large amount of data will be stored in the database, so we will control the number of queries, so we need limit

-- starting with the first piece of data, query 2 pieces of data (excluding the first piece of data) SELECT * FROM t_user LIMIT 1Pol 2

The first digit after limit refers to the location where the query starts.

The second digit refers to the number of data items queried

Grouping

Group by clause: groups the information of the query

Having clause: filter the grouped result set

-- grouping via sex-- here select must be followed by the field after group by or the grouping function SELECT sex,AVG (weight) FROM t_user GROUP BY sexSELECT sex,AVG (weight) FROM t_user GROUP BY sex HAVING sex=''

What needs to be noted here is:

The query list is special. It requires that the grouping function and the fields that appear after group by are grouped before filtering the original table group by clause before where grouping and after filtering the result set after grouping having where is pre-grouping filtering and having is grouping post-filtering.

Synthesis

In the actual development process, these words are used together and have a certain position. If the position is not correct, an error will be reported, for example:

SELECT sex,AVG (weight) wFROM t_userWHERE sex = 'male' GROUP BY sexHAVING sex = 'male' ORDER BY w ASCLIMIT 0,1

There are certain requirements for the order of clauses.

Query results:

This is the end of the content about "what are the common operations of MySQL conditional query sentences"? thank you for your reading. If you want to know more about the industry, you can follow the industry information channel. The editor will update different knowledge points for you every day.

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.