MySQL experiences 6 -- MySQL Language Structure -- operators and expressions

1. Arithmetic Operators arithmetic operators perform mathematical operations on two expressions, which can be any numeric data type. Arithmetic Operators include: + (plus),-(minus), * (multiplication),/(Division), and % (Modulo. (1) The "+" operator www.2cto.com "+" is used to obtain the sum of one or more values: SELECT 1.2 + 3.09345, 0.00000000001 + 0.00000000001; (2) the "-" operator "-" is used to subtract another value from a value and change the parameter Symbol: SELECT 200-201, 0.14-0.1,-2 (change the parameter symbol ); note: If this operator and BIGINT are both used, the return value is also a BIGINT. This means that the minus sign "-" should be avoided in integer operations that may generate-263; otherwise, an error may occur. The + (plus) and-(minus) operators can also be used to perform arithmetic operations on DATETIME values (such as DATETIME. For example: SELECT '2014-01-20 '+ interval 22 DAY; Note: The INTERVAL keyword is followed by a time interval. 22 days indicates that 22 days are added based on the current date. The current date is, and 22 days later is 2008-02-11. (3) The "*" operator "*" is used to obtain the product of two or more values: SELECT 5*12, 5*0, -19530415 *-19540319 (no parentheses are added when calculating the product of a negative number); (4) the "/" operator is the same as multiplication, but Division by Zero is not allowed, in this case, MySQL returns the NULL: (5) "%" operator www.2cto.com "%" operator to obtain the remainder of one or more Division operations: SELECT 12% 5, -32% 7,3% 0; 2. comparison operator (also called relational operator) is used to compare the values of two expressions. The calculation result is a logical value, which can be 1 (true), 0 (false) and NULL (uncertain ). The following table lists various comparison operators that can be used in MySQL. Operator-included operator = equal to <= less than or equal to> greater than <> ,! = Not equal to <less than <=> equal to or equal to null> = www.2cto.com greater than or equal to the comparison operator can be used to compare numbers and strings. Numbers are compared as floating-point values, while strings are case-insensitive (unless special BINARY keywords are used ). We have already introduced that MySQL can automatically convert numbers to strings During computation, while MySQL can automatically convert strings to numbers during comparison. The following example illustrates how MySQL processes numbers and strings in different ways under different circumstances. For example, execute the following statement: SELECT 5 = '5ab', '5' = '5ab'; Result:; analysis: the first one, the letters a and B are automatically converted to numbers, because there is no corresponding number, the solid conversion is 0, so it is equal to the left. The second one may be converted to the other type. (1) The "=" operator "=" operator is used to compare whether the two sides of the expression are equal. You can also compare strings, for example: SELECT 3.14 = 3.142, 'A' = 'A', 'A' = 'B', 'apple' = 'bana'; note: because MySQL compares strings in case-insensitive mode by default, the result of the expression 'A' = 'A' is true. If you want to perform case-sensitive comparison, you can add the BINARY keyword, which means that the string is processed in BINARY format. When a comparison operation is performed on a string, MySQL will distinguish the case sensitivity of the string. SELECT 'apple' = 'apple', BINARY 'apple' = 'apple'; Result:; (2) the "<>" operator and the "=" operator are opposite to the "<>" operator, which is used to detect whether the two sides of the expression are not equal. If they are not equal, the true value is returned, returns a false value if they are equal. Example: SELECT 5 <> 5, 5 <> 6, 'A' <> 'A', '5a '<> '5b'; Result: 0, 1, 0, 1 SELECTNULL <> NULL, 0 <> NULL, 0 <> 0; Result: null, null, 0 3. logical operators are used to test a condition. The calculation result is TRUE (1) or FALSE (0 ). The logical operators provided by MySQL are shown in the following table. If the calculation rules are www.2cto.com, the calculation rules are NOT or! Logical non-OR | logical or and or & logical and xor logic exclusive OR (1) AND operator AND is used to test two OR more values (OR evaluate the expression) if all its components are true and not NULL, it returns the true value; otherwise, it returns the false value. For example, SELECT (1 = 1) AND (9> 10), ('A' = 'A') AND ('C' <'D '); (compare by ASCLL code) the result is 0, 1; (2) if the XOR operator contains a value or expression that is true while the other is false and is not NULL, then it returns the true value; otherwise, it returns the false value. (The two return false, different return true) for example: SELECT (1 = 1) XOR (2 = 3), (1 <2) XOR (9 <10); 4. bitwise operators perform binary operations between two expressions. These two expressions can be of integer or integer-compatible data type (such as the struct type, but not the image type ), bitwise operators are shown in the following table. Computation rules: the computation rules are calculated based on www.2cto.com ~ Bitwise inversion | bit OR> shifted right ^ bit XOR <shifted left 5. IN addition to the preceding operators, MySQL also provides other common operators, such as BETWEEN, IN, is null, and isnot null, LIKE, And REGEXP, these are already described in the WHERE clause in the SELECT statement. When a complex expression has multiple operators, the operator priority determines the order in which operations are executed. The execution sequence affects the calculation result. The following table lists the operator priorities. In an expression, operations are performed in the order of first high (smaller priority), then low (larger priority. Operator priority operator priority + (positive),-(negative ),~ (By bit NOT) 1 NOT 6 * (multiplication),/(Division), % (module) www.2cto.com 2 AND 7 + (plus),-(minus) 3 ALL, ANY, BETWEEN, IN, LIKE, OR, SOME 8 =, >,<, >=, <=, <> ,! =,!> ,! <Comparison operator 4 = (assign value) 9 ^ (bitwise OR), & (bitwise AND), | (bitwise OR) 5 6. an expression is a combination of constants, variables, column names, complex computations, operators, and functions. A regular expression can obtain a value. Like constants and variables, expression values also have certain data types. Possible data types include character type, value type, and date and time type. In this way, expressions can be divided into numeric expressions, numeric expressions, and date expressions based on the type of the expression value. Expressions can also be classified based on the complexity of values. When an expression returns only one value, such as a value, a word, or a date, this expression is called a scalar expression. For example, 1 + 2, 'A'> 'B '. Www.2cto.com when the expression result is a line of value composed of different types of data, this expression is called a row expression. For example, ('20140901', 'wanglin', 'computer ', 081101 ). When the expression result is a set of 0, 1, or multiple row expressions, this expression is called a table expression. Expressions can also be divided into single expressions and compound expressions by form. A single expression is a single value, such as a constant or column name. A composite expression is an expression that is composed of multiple single expressions connected by an operator, for example, 1 + 2 + 3, a = B + 3, '2017-01-20 '+ INTERVAL 2 MONTH. Expressions are generally used in the WHERE clause of SELECT and SELECT statements. Author tianyazaiheruan