Where in usage in MySQL

Here are two ways to introduce

WHERE column in (Value1,value2,...)
WHERE column not in (Value1,value2,...)

1, in the back is the recordset, such as:

SELECT * FROM table where uname in (select uname from user);

Example:

SELECT * FROM article WHERE uid in (SELECT uid from user where status=0)
In this SQL example, we implemented all articles that identified all users with a status of 0 (which may be forbidden). First, all status=0 users are given a single query:

[SQL]View PlainCopy

1. SELECT uid from user WHERE status=0

The query results are then used as list items in to implement the final query results, and note that the results returned in the subquery must be a field list item.

2, in followed by a string, such as:

SELECT * FROM table where uname in (' AAA ', BBB ', ' CCC ', ' ddd ', ' eee ', ffff ');

Note: Be sure to label the string in single quotation marks;

3, in the following is an array, as follows, please refer to:

$pieces is an array with data

for ($i =0; $i <count ($pieces); $i + +) {

$uname = $uname. "'". $pieces [$i]. "',";

}

$the _uname = "uname in (". $uname. ")";

SELECT * FROM table where ". $the _uname.";

Note: The principle of this method is actually very simple, the second is to put the array programming the "2nd case" form

MySQL uses Find_in_set to query data that matches multiple IDs in a comma-separated string

The Find_in_set (str,strlist) function is different from a like fuzzy query, which separates values with ","

STR string to query for

Strlist field name parameters are separated by "," as (1,2,6,8)

Query field (strlist) contains the result of (str), the result is null or the record

The following examples illustrate

The test table has the following fields and values

Below I want to query the area contains "1″ This parameter record

SELECT * FROM Test where find_in_set (' 1 ', area)

return value

The following query btype field contains "15″ the value of this parameter

SELECT * FROM Test where find_in_set (' btype ')

return value

MySQL between usage
MySQL between syntax
The between operator is used in the WHERE expression to select a range of data between two values. Between is used in conjunction with and with the following syntax:

[SQL]View PlainCopy

1. WHERE column between value1 and value2
2. WHERE column not between value1 and value2

Usually value1 should be less than value2. When between is preceded by the NOT operator, it indicates the opposite meaning of between, which is to select a value outside the range.
Between instances
Select User Data with UID from 2 to 5:

[SQL]View PlainCopy

1. SELECT * from user WHERE uid between 2 and 5

The results of the returned query are as follows:

In addition to the numeric types, between also supports string ranges, selecting all username from A to J (and including single-letter k/k) as follows:
SELECT * FROM user WHERE username between ' a ' and ' K '
The character range also supports Chinese characters, but it usually doesn't make sense.
MySQL between border
Although almost all databases support between ... And operator, but different databases to between ... There is a difference in how and is handled. In MySQL, between contains value1 and value2 boundary values, such as the example above that selects user data with UID between 2 and 5.
While some databases do not contain value1 and value2 boundary values (similar to > and <), there are also databases that contain value1 and do not contain value2 (similar to >= and <). So in the use of between ... And, check to see how your database handles between boundary values.
MySQL between Time and date
Between and is commonly used to retrieve content within a time or date period, here are some examples of common between time and date:

[SQL]View PlainCopy

2. int timestamp format, querying data from 2008-08-08 20:00:00 to 2009-01-01 0 o'clock
4. SELECT * from table WHERE column_time between 1218196800 and 1230739199
8. date format, querying for data before 0 o'clock 2008-08-08 to 2009-01-01
10. SELECT * from table WHERE column_time between ' 2008-08-08 ' and ' 2009-01-01 ' /c4>
14. DATETIME format, querying data from 2008-08-08 20:00:00 to 2009-01-01 0 o'clock
16. SELECT * from table WHERE column_time between ' 2008-08-08 20:00:00 ' and ' 2008-12-31 23: 59:59 '
18. However, for data that is queried to the current time, we recommend using the >= operator:
20. DATETIME format, query 2008-08-08 20:00:00 to the current moment of data
22. SELECT * from table WHERE column_time >= ' 2008-08-08 20:00:00 '

It can be seen that the same requirements, different field types, may not be the same. In terms of efficiency, the int timestamp format is efficient.
Each of the above between examples, although all are SELECT queries, but between can also be used for UPDATE, DELETE, and other applicable WHERE expressions in SQL.
MySQL between data comparison
Between also has a data comparison function with the following syntax:

[SQL]View PlainCopy

1. Expr between min and max

When the value of the expr expression is greater than or equal to min and less than or equal to Max, the return value of between is 1, otherwise 0 is returned. With this function, you can judge an expression or value otherwise in a range:

[SQL]View PlainCopy

1. Returns 0
2. SELECT 1 between 2 and 3
3. Returns 1
4. SELECT ' B ' between ' a ' and ' C '
5. Judging the date range
6. SELECT 20080808 between 20080101 and 20090101

Between and <, <=, >=, > and other operators have similar functions in some cases, but the between is more computationally efficient. Of course, due to the existence of boundary value of between is not flexible enough, so different circumstances, the use of what operators, need to be treated specifically.

MySQL in usage
MySQL in syntax
The in operator is used in the WHERE expression to support multiple selections in the form of list items, with the following syntax:

[SQL]View PlainCopy

1. WHERE column in (value1,value2,...)
2. WHERE column not in (value1,value2,...)

When in is preceded by the NOT operator, it means the opposite of in, that is, not selected within these list items.
In use instance
Select User Data with UID 2, 3, 5:

[SQL]View PlainCopy

1. SELECT * from user WHERE uid in (2,3,5)

The results of the returned query are as follows:

In sub-query
In more cases, the value of the in list item is ambiguous and may be obtained by a subquery:
SELECT * FROM article WHERE uid in (SELECT uid from user where status=0)
In this SQL example, we implemented all articles that identified all users with a status of 0 (which may be forbidden). First, all status=0 users are given a single query:

[SQL]View PlainCopy

1. SELECT uid from user WHERE status=0

The query results are then used as list items in to implement the final query results, and note that the results returned in the subquery must be a field list item.
In operator supplemental description
In list items not only support numbers, but also characters and even time-date types, and you can mix these different types of items without consistency with the type of column:

[SQL]View PlainCopy

1. SELECT * from user WHERE uid in (3 ',' C ')

One in can only be scoped to a field, and if you want to specify more fields, you can use the and OR or logical operators:
SELECT * from the user WHERE UID in (username) OR (' admin ', ' 5idev ')
After using the and or or logical operators, in can also be used with other operators such as like, >=, =, and so on.
About efficiency issues with the in operator
If the in list item is deterministic, then multiple OR can be used instead:

[SQL]View PlainCopy

1. SELECT * from user WHERE uid in (2,3,5)
2. The equivalent is:
3. SELECT * from user WHERE (uid=2 or aid=3 or aid=5)

It is generally assumed that if you are working with an indexed field, using OR is more efficient than in, but you must use the in operator when the list item is indeterminate (if a subquery is required to get the result). Also, the in operator is used when the subquery table data is smaller than the primary query.

Source: http://blog.csdn.net/k8080880/article/details/8482659

Http://www.jb51.net/article/76517.htm

Read more

Where in usage in MySQL