MySQL experience 6 -- MySQL language structure -- operators and expressions _ MySQL-mysql tutorial

MySQL tips 6-MySQL language structure-operators, expressions bitsCN.com 1. 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 "+" 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 "%" operator to obtain the remainder of one or more division operations: SELECT 12% 5,-32%, 3% 0; 2. comparison operator 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> = 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 rule is run, the calculation rule is 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. Operator calculation rules: operator calculation rules & bits AND ~ 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 + (positive),-(negative ),~ (By bit NOT) 1 NOT 6 * (multiplication),/(division), % (modulo) 2 AND 7 + (plus),-(minus) 3 ALL, ANY, BETWEEN, IN, LIKE, OR, SOME 8 =, >,<, >=, <=, <> ,! =,!> ,! <比较运算符 4="（赋值）" 9 ^（位异或）、&（位与）、|（位或） 5 6. 表达式 表达式就是常量、变量、列名、复杂计算、运算符和函数的组合。一个表达式通常可以得到一个值。与常量和变量一样，表达式的值也具有某种数据类型，可能的数据类型有字符类型、数值类型、日期时间类型。这样，根据表达式的值的类型，表达式可分为字符型表达式、数值型表达式和日期型表达式。 表达式还可以根据值的复杂性来分类： 当表达式的结果只是一个值，如一个数值、一个单词或一个日期，这种表达式叫做标量表达式。例如：1+2，'a'> 'B '. When the result of an expression is a row of values 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 tianyazaiheruanbitsCN.com