Query records that meet the conditions in the table-WHERE clause _ MySQL

Query records that meet the conditions in the table-WHERE clause bitsCN.com

Query records that meet the conditions in the table-WHERE Clause

When using SQL statements for query operations, developers or users are not interested in all the records in the data table, but want to get the actual data records, in this case, you need to restrict the query results. In SQL statements, you can use the WHERE clause to filter out non-conforming records.

1. comparison query

In the WHERE clause, you can use a comparison operator to query values, character values, and other information. Comparison operators are classified into three types: arithmetic comparison operators,... And in operators. This section describes the query methods of these three comparison operators. Finally, we will introduce the comparison of strings and time in the WHERE clause.

Arithmetic comparison operator

Arithmetic comparison operators in SQL statements include = (equal to),> = (greater than or equal to), <= (less than or equal to),> (greater than), <(less than), and ),! = (Not equal to), <> (not equal to),!> (Not greater ),! <(Not less ). In the WHERE clause of a SELECT statement, you can use arithmetic comparison operators to compare a specified column. the syntax format is as follows:

Field 1 comparison operator value

Field 1 indicates the name of the field column to be queried in the data table. Field 1 is followed by the arithmetic comparison operator, and the value indicates the value to be compared in the specified column. The result returned by the comparison operator is a logical value. If the logical value is TRUE, the queried records are returned. if the logical value is FALSE, the query results are not returned.

Note: When comparing numeric data in the WHERE clause, you can use no single quotation marks. for other types of data (such as string and time) it must be enclosed in single quotes. In addition, the data types on the left and right of the comparison operators in the WHERE clause must be compatible.

BETWEEN... The AND operator queries records within the specified condition range.

BETWEEN... The AND operator can be used to query records within a specified condition range. Use... A value must be given after the BETWEEN operator AND the AND operator in the AND operator query. The syntax format is as follows:

Field 1 BETWEEN value 1 AND value 2

Field 1 indicates the fields to be queried in the data table. value 1 indicates a small value in the given value. value 2 indicates a large value in the given value. The final query result also contains the values of value 1 and value 2.

Tip: In SQL, you can use the NOT BETWEEN AND operator to exclude some records. For example, to query the instructor information table, the age is not 30 ~ The instructor information BETWEEN 50 can be achieved through not between and.

Query records matching the list using the IN operator

The IN operator query is used to query records that match the list. Using the IN operator, you can query any value that meets the specified expression IN the list. The attribute values after the IN operator can be one or multiple. Separate multiple attribute values with commas. The syntax format is as follows:

Field 1 IN (attribute value 1, attribute value 2, attribute value 3 ...)

Field 1 indicates the fields to be queried in the data table. attribute value 1, attribute value 2, and attribute value 3 indicate the values to be queried. Attribute values can be numeric or character values. If the attribute value is of the character type, use single quotation marks to enclose it.

String comparison

When you use SQL statements for comparative queries, you may encounter string comparison problems. When comparing strings, common databases can use comparison operators to compare strings. In addition, you can use the keyword BINARY to compare strings in MySQL databases.

When you use a comparison operator to compare strings, the character values on both sides of the comparison operator should be enclosed in single quotes.

SELECT 'mysql' = 'MySQL' 

If you want to compare strings to be case-sensitive, you can use the BINARY keyword to perform BINARY comparison on strings. Using the BINARY keyword, a string (number) is converted into a BINARY object. The format is as follows:

SELECT string1 comparison operator BINARY string2 or select binary string1 comparison operator string2SELECT 'mysql' = BINARY 'mysql'

Comparison of date and time

When comparing the date value and time in the WHERE clause, the date and time to be compared must be a string format acceptable to the database server. For example, in the student info table (T_student), the student's birth date is set to a variable of the DATETIME date type. To compare the birth date value of a student in the WHERE clause, you can use single quotes to enclose the date value.

2. logical query

In SQL statements, logical operators include AND, OR, AND NOT. The AND operator is used to query records that meet multiple conditions at the same time. the OR operator is used to query records that meet any of the conditions. The NOT operator is used to query records that meet the opposite conditions.

Use the AND operator to query records that meet multiple conditions at the same time

In SQL execution, in many cases, the WHERE clause does not only want to satisfy one condition, but wants the final query results to satisfy multiple conditions (two or more conditions) at the same time ). In this case, the AND operator is used. The syntax format is as follows:

Condition 1 AND condition 2

WHERE condition 1 and condition 2 are the conditions that must be met when querying in the WHERE clause. If you want to use the AND operator to join multiple conditions in the WHERE clause. You can use the following syntax format.

Condition 1 AND condition 2 AND condition 3...

Here two AND operators are used to connect three conditions. When multiple AND operators are connected, the values on both sides of each AND operator must be TRUE, that is, if these conditions are met at the same time, the results will be displayed.

Note: In the Microsoft SQL Server database, replace the AND operator with the "&" symbol to represent the logic AND operation.

Use the OR operator to query records that meet any of the conditions

When using SQL for query operations, you only need to meet any of the conditions in the query results. In this case, the OR operator is required. You can use the OR operator to query records that meet any of the conditions. The syntax format is as follows:

Condition 1 OR condition 2

WHERE condition 1 and condition 2 are the conditions required for query in the WHERE clause. If either of these conditions is met, the records that meet the conditions will be retrieved. If you want to use the OR operator to join multiple conditions in the WHERE clause. You can use the following syntax format

Condition 1 OR condition 2 OR condition 3...

Here, two OR operators are used to connect three conditions. When multiple OR operators are connected, if any of the conditions on both sides is TRUE, the records that meet the conditions will be displayed.

Note: In the Microsoft SQL Server database, use the "|" symbol instead of the OR operator to represent the logic and operation.

Use the NOT operator to reverse the boolean value of the query condition

In some cases, you need to query records that do NOT meet the specified conditions. in this case, you need to use the NOT operator. The NOT operator returns the boolean value of the query condition.

Note: "~" is used in the Microsoft SQL Server database. The symbol replaces the NOT operator to represent the logic and operation.

These three logical operators can also be mixed in an SQL statement. Among the three logical operators, NOT has the highest priority. AND has a higher priority than OR. For ease of understanding, brackets can be used to enclose each part when these three logical operators are used together.

3. query null values

When SQL statements are used to perform query operations, a null query is also used. In a data table, if the row attribute of a table does not contain any value, that is, the row attribute of the table does not contain any data records. Then it is called a null value. In SQL queries, NULL can be used to indicate the meaning of NULL values. In SQL statements, the keyword IS NULL or IS NOT NULL can be used to judge NULL values.

4. use the LIKE operator for fuzzy search

When using SQL statements for queries, you may encounter such a situation that the complete conditions of the information to be queried cannot be completely determined, but these information has some obvious characteristics. For example, if you want to learn the SQL language and want to find some relevant materials in the library, but you do not know what books are available for the SQL language, at this time, keyword SQL is usually entered in the library management system, so that all books related to SQL will be found. This is fuzzy query. The keyword LIKE for fuzzy query is provided in the SQL language. it must be used with the wildcard "%" and.

In an SQL statement, the wildcard "_" indicates matching a single character. That is, in a query statement, a "_" can only match one character.

In an SQL statement, the wildcard "%" indicates that it matches 0 or multiple characters. That is, a "%" can be 0 characters, a single character, or two or more characters.

When SQL is used to perform fuzzy queries, sometimes the character values in a field in the data table contain the characters "%" or. When querying, developers want to query the characters with the character value "%" or "_" as part of the character, at this time, you need to use the ESCAPE keyword to ESCAPE it. Follow these steps to use the ESCAPE keyword for ESCAPE:

(1) add an escape character before the "%" or "_" character to be escaped. the escape character can be any character.

(2) Name the ESCAPE character after the ESCAPE keyword.

After these two steps, the wildcard ("%" or "_") located after the escape character will be escaped as a common character.

5. use the REGEXP keyword for pattern matching (regular expression)

In the MySQL database, a more flexible pattern matching method is provided, that is, the REGEXP keyword is used to perform the pattern matching on the string. Use the REGEXP keyword to perform pattern matching on the string. as long as the matched string contains a child string that matches the matching template, it is considered as pattern matching. The return value is TRUE.

In MySQL databases, you can use some pattern matching modifiers to test the pattern matching of strings using the REGEXP keyword.

^: Used to match the start of a string.

$: Used to match the end of a string.

[]: Any character in square brackets can match. For example, [abc] indicates matching characters a, B, or c in square brackets.

-: A hyphen is used to indicate the range of character matching. For example, [a-z] indicates matching any character in square brackets from character a to character z.

+: Indicates that the character to be matched appears at least once or multiple times in the matched string.

*: Indicates that the character used for matching appears zero or multiple times in the matched string.

(): The content in parentheses is regarded as a whole. For example, abc indicates that the string matching the style (abc) is abc.

{M}: the integer m indicates the number of occurrences of the string before curly braces. For example, {abc} {2} indicates that the string matching the style {abc} {2} is abcabc.

In addition, REGEXP can perform a pattern matching test on the string.

SELECT 'agf' REGEXP '[a-d]+','banana' REGEXP '(ana){2}' 

BitsCN.com