MySql snacks-2. common comparison functions and comparison operators-MySQL

MySql snacks-2. common comparison functions and comparison operators bitsCN.com
1. Comparison operators: =,>, <,> =, <=, <>, and ,! =, <=> Here we know <> and! = Meaning is the same. they all mean not equal. look at your preferences. <=> It is used when the values on both sides of the comparison are NULL, in this case, mysql provides a comparison operator similar to "= ", the difference is that when the numbers to be compared on both sides are NULL, it returns true (indicating that the two sides are equal). if one side is NULL, it returns false (indicating that the two sides are not equal ). If the comparison operator "=" is used, the return value is NULL when either side has NULL. 2. Comparison function: BETWEEN... AND: checks whether a number is within a certain range. Expr BETWEEN min AND max. There is also a not between... AND, indicating that there is no longer a certain range IN (): check whether a certain number is IN some numbers. Expr IN (value ,...). The corresponding value is not in (), indicating that INTERVAL (N, N1, N2, N3,...) is not in the listed number ,...): N1, N2 ,..... the first index greater than N in, N1, N2... sort by size, for example, INTERVA (10, 1, 10,100,100 0). This function returns the index of 2,100 as 2 GREATEST (): returns the maximum number of a group. Corresponding to LEAST (): returns the minimum value of a set of numbers. IS: same as boolean value judgment, IS boolean_value. here, note that boolean_value has three types: TRUE, FALSE, UNKNOWN, and UNKNOWN, which correspond to null values, for example, if null is unknown, true is returned. The opposite of is not, which IS similar in usage. Is null: determines whether a value is null. When using ODBC programming, this is null. in addition to determining whether it is null, there are two purposes: 1. if SQL _auto_is_null, this Mysql system variable IS set to 1 (the default value IS 1 ), after you insert an AUTO_INCREMENT automatically generated value (automatically generated by the system), you can use the following statement to obtain the previously inserted record: [SQL] SELECT * FROM tbl_name WHERE auto_col IS NULL here tbl_name IS the table name, and auto_col corresponds to the column name of the auto-increment table. The value of the auto-increment column in the record is the same as the value obtained by calling LAST_INSERT_ID. Of course, if the insertion fails, no record is returned for the preceding SELECT statement. of course, if SQL _auto_is_null is set to 0 and false, no operation will be performed. 2. for fields corresponding to the data format of DATE or DATETIME, and the field IS set to non-null not null, you can use is null to find the special DATE "2017-00-00". the following code IS used: [SQL] SELECT * FROM tbl_name WHERE date_column IS NULL. this IS mainly because ODBC does not support a date value of "0000-00-00. Is not null, which IS similar to is null. STRCMP: compares two characters and returns the values-1, 0, and 1. LIKE: simple pattern matching, which is commonly used. We usually know that "%" represents any number of characters, and "_" represents one character. First, let's take a look at the LIKE format: [SQL] expr LIKE pat [ESCAPE 'escape _ char '] Here, the escape next to it specifies the ESCAPE symbol. The default ESCAPE characters are "/". to use "%" and "_", you can use "/%" and "/_" by default; to search for "/n", search for "// n". to search for "/", search "////", because there are two resolutions here, one is parser (text binder) generated (that is, if the expr contains the '/' symbol, the binder will change it '//'), one request is matched (when matching, '//' is changed ). Pat can be a string, a string expression, or a column name. LIKE can also be used in numeric expressions, for example, 10 LIKE '1% 'is returned to true. Note that if one of expr or pat is NULL, LIKE will return null not like, which is similar to bitsCN.com.