Use mysqlselect with where substatement to query the result set

Mysqlselect is used to query the data in the table. However, we often use where and select to filter the result set. This article introduces the mysql select statement in combination with the use method and example of where query, for more information, see. WHERE clause

In a SELECT statement, the statement is first executed FROM the FROM clause. after execution, an intermediate result set is generated, and then the WHERE clause is executed. The WHERE clause filters the result set generated by the FROM clause. for each row of records in the intermediate result set, the WHERE clause returns a boolean value (TRUE/FALSE). if TRUE, this row of records will remain in the result set. if FALSE, this row of records will be removed from the result set. For example:

SELECT name FROM student WHERE studentNO = 2

The intermediate result set returned by the FROM clause is as follows:

StudentNO name1 Zhang San 2 Li Si 3 Wang Wu 4 Zhao Liu

A total of four rows of records, each row of records is executed by the WHERE clause. In the first row, studentNO is 1, so the returned value of the studentNO = 2 expression is FALSE. this row of records is removed. In the second row, studentNO is 2, so studentNO = 2 returns TRUE, and this row of records continues to be retained. Similarly, the records in the third and fourth rows are also removed. after the WHERE statement is executed, the intermediate result set is:

StudentNO name2 Li Si

Then execute the SELECT statement. The final result set is:

Name Li Si

Comparison Operators

The WHERE clause returns a boolean value. Therefore, comparison operators are often used in the WHERE clause. Comparison operators:

= Equal

<=> Equal or empty

<Less

> Greater

<= Less than or equal

> = Greater than or equal

<> Not equal

! = Not equal

Result 2 = 2 is true, result 15 <9 is false, result 3> 2 is true, 5! = 4. The result is true.

Strings can also be compared. the result of 'B' <'G' is true, and the result of 'H'> 'k' is false.

The time value can be compared. the earlier time is less than the later time. '2014-5-4 '<'2014-02-15' returns true, and '2014-2-18 '> '2014-2-19' returns false.

= The difference between the comparison operator and <=> operator is that when two null values are compared, = returns unknown, and <=> returns true.

Comparison Operators in subqueries

SELECT studentNO FROM student WHERE studentNO> (SELECT studentNO FROM student WHERE name = 'Li si ')

A subquery can be used in the WHERE clause. In the preceding example, a scalar query can return only one scalar value.

The same row subquery can also be used in the WHERE clause:

SELECT studentNO FROM student WHERE (studentNO, name) = (SELECT studentNO, name FROM student WHERE name = 'Li si ')

The WHERE clause does not necessarily contain comparison operators. if not, an implicit conversion is executed. If 0 is used, it is converted to false. if other values are used, it is converted to true.

WHERE clause without comparison operators

SELECT studentNO FROM student WHERE 0

An empty set is returned, because each row of record WHERE returns false.

SELECT studentNO FROM student WHERE 1

Or

SELECT studentNO FROM student WHERE 'abc'

Will return the studentNO column of all Row Records in the student table. Because the WHERE clause returns true for each record.