Day455. Basic SELECT statement & operator mysql

Basic SELECT statement

1, SQL overview

1. SQL background

  • In 1974, IBM researchers published a paper "sequence: a structured English query language", which unveiled database technology. Up to now, this structured query language has not changed much. Compared with other languages, the half-life of SQL can be said to be very long.

  • SQL (Structured Query Language) is a database application language using relational model. It deals directly with data and was developed by IBM in the 1970s.

  • SQL has two important standards, SQL92 and SQL99

  • Different database manufacturers support SQL statements, but they all have unique contents.

2. SQL classification

  • DDL (Data Definition Languages)

    • These statements define different database objects such as database, table, view and index. They can also be used to create, delete and modify the structure of database and data table.
  • DML (Data Manipulation Language)

    • The main statement keywords include INSERT, DELETE, UPDATE, SELECT, etc.
  • DCL (Data Control Language)

    • The statement keywords to include GRANT, REVOKE, COMMIT, ROLLBACK, SAVEPOINT, and so on.

Because the query statements are used very frequently, many people pick out one category of query statements: DQL (data query language).
In addition, taking out the COMMIT and ROLLBACK separately is called TCL (Transaction Control Language).

2, Rules and specifications of SQL language

1. Basic rules

  • SQL can be written on one or more lines. In order to improve readability, each clause is written separately and indented if necessary
  • Each command is in; Or \ g or \ g end
  • Keywords cannot be abbreviated or broken
  • About punctuation
    • It must be ensured that all (), single quotation marks and double quotation marks end in pairs
    • Half angle input mode in English must be used
    • Data of string type and date time type can be represented by single quotation marks (')
    • Column alias, try to use double quotation marks (""), and it is not recommended to omit as

2. SQL case specification (recommended)

  • MySQL is case insensitive in Windows environment

  • MySQL is case sensitive in Linux environment

    • Database name, table name, table alias and variable name are strictly case sensitive
    • Keywords, function names, column names (or field names), and column aliases (field aliases) are case insensitive.
  • A unified writing standard is recommended:

    • Database name, table name, table alias, field name, field alias, etc. are all lowercase
    • SQL keywords, function names, bound variables, etc. are capitalized

3. Notes

Single line note:#Annotation text (MySQL specific way)
Single line note:-- Annotation text(--Must be followed by a space.)
Multiline comment:/* Annotation text */

4. Data import instruction

Log in to mysql on the command-line client and use the source command to import

mysql> source d:\mysqldb.sql

3, Basic SELECT statement

1,SELECT

SELECT 1; #There are no clauses
SELECT 9/2; #There are no clauses

2,SELECT ... FROM

  • grammar
SELECT  Identifies which columns to select
FROM   Identifies which table to select from
  • give an example
SELECT *
FROM  departments;

In general, unless you need to use all the field data in the table, it is best not to use the wildcard '*'. Although using wildcards can save the time of entering query statements, obtaining unnecessary column data usually reduces the efficiency of the query and the application used. The advantage of wildcards is that when you don't know the names of the required columns, you can get them through it.

In the production environment, it is not recommended that you directly use SELECT * for query.

3. Alias of the column

Add the keyword AS between the column name and the alias, and the alias uses double quotation marks

  • give an example
SELECT last_name AS name, commission_pct comm
FROM  employees;

4. Remove duplicate lines

By default, the query returns all rows, including duplicate rows. DISTINCT

SELECT DISTINCT department_id,salary
FROM employees;

Here are two points to note:

  1. DISTINCT needs to be placed in front of all column names. If it is written as SELECT salary, DISTINCT department_id FROM employees will report an error.

  2. DISTINCT actually de duplicates the combination of all the following column names. You can see that the final result is 74. Because the 74 Department IDs are different, they all have the attribute value of salary. If you want to see the different departments (department_id), just write DISTINCT department_id is enough. There is no need to add other column names.

5. Null values participate in the operation

  • All operators or column values encounter null values, and the result of the operation is null
SELECT employee_id,salary,commission_pct,12 * salary * (1 + commission_pct) "annual_sal"
FROM employees;

Here you must note that in MySQL, a null value is not equal to an empty string. The length of an empty string is 0, and the length of an empty value is null. Moreover, in MySQL, null values take up space.

6. Emphasis sign

``Emphasis sign

SELECT * FROM `ORDER`;
  • conclusion

We need to ensure that the fields and table names in the table do not conflict with reserved words, database systems or common methods. If it is really the same, use a pair of ` ` (emphasis marks) in the SQL statement.

7. Query constant

We want to query the employee name in the employees data table, and add a column of Field corporation, which has a fixed value of "Achang", which can be written as follows:

SELECT 'Achang' as corporation, last_name FROM employees;

4, Display table structure

Use the DESCRIBE or DESC command to represent the table structure.

DESCRIBE employees;
or
DESC employees;
mysql> desc employees;

+----------------+-------------+------+-----+---------+-------+
| Field     | Type    | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| employee_id  | int(6)   | NO  | PRI | 0    |    |
| first_name   | varchar(20) | YES |   | NULL  |    |
| last_name   | varchar(25) | NO  |   | NULL  |    |
| email     | varchar(25) | NO  | UNI | NULL  |    |
| phone_number  | varchar(20) | YES |   | NULL  |    |
| hire_date   | date    | NO  |   | NULL  |    |
| job_id     | varchar(10) | NO  | MUL | NULL  |    |
| salary     | double(8,2) | YES |   | NULL  |    |
| commission_pct | double(2,2) | YES |   | NULL  |    |
| manager_id   | int(6)   | YES | MUL | NULL  |    |
| department_id | int(4)   | YES | MUL | NULL  |    |
+----------------+-------------+------+-----+---------+-------+
11 rows in set (0.00 sec)

The meanings of each field are explained as follows:

  • Field: indicates the field name.
  • Type: indicates the field type. Here, barcode and goodname are text type, and price is integer type.
  • Null: indicates whether the column can store null values.
  • Key: indicates whether the column has been indexed. PRI indicates that the column is part of the table primary key; UNI indicates that the column is part of the UNIQUE index; MUL indicates that a given value is allowed to appear more than once in the column.
  • Default: indicates whether the column has a default value. If so, what is the value.
  • Extra: indicates additional information about a given column that can be obtained, such as AUTO_INCREMENT et al.

5, Filter data

WHERE

  • grammar
SELECT Field 1,Field 2
FROM Table name
WHERE Filter condition

Use the WHERE clause to filter out rows that do not meet the conditions

The WHERE clause follows the FROM clause

  • give an example
SELECT employee_id, last_name, job_id, department_id
FROM  employees
WHERE department_id = 90;

operator

1, Arithmetic operator

The arithmetic operator is mainly used for mathematical operations. It can connect two values or expressions before and after the operator, and perform addition (+), subtraction (-), multiplication (*), division (/) and modulus (%) operations on the values or expressions.

1. Addition and subtraction operators

mysql> SELECT 100, 100 + 0, 100 - 0, 100 + 50, 100 + 50 -30, 100 + 35.5, 100 - 35.5
FROM dual;

+-----+---------+---------+----------+--------------+------------+------------+
| 100 | 100 + 0 | 100 - 0 | 100 + 50 | 100 + 50 -30 | 100 + 35.5 | 100 - 35.5 |
+-----+---------+---------+----------+--------------+------------+------------+
| 100 |   100 |   100 |    150 |      120 |    135.5 |    64.5 |
+-----+---------+---------+----------+--------------+------------+------------+
1 row in set (0.00 sec)

The following conclusions can be drawn from the calculation results:

  • An integer type value adds and subtracts an integer, and the result is still an integer;
  • An integer type value adds and subtracts a floating-point number, and the result is a floating-point number;
  • The priority of addition and subtraction is the same, and the result of adding before subtraction is the same as that of subtraction before addition;
  • In Java, if there is a string on the left and right sides of +, it represents the splicing of strings. However, in mysql, + only indicates the addition of values. If a non numeric type is encountered, try to convert it to a numeric value first. If the conversion fails, press 0. (supplement: String splicing in MySQL should be implemented using the string function CONCAT())

2. Multiplication and division operators

mysql> SELECT 100, 100 * 1, 100 * 1.0, 100 / 1.0, 100 / 2,100 + 2 * 5 / 2,100 /3, 100
DIV 0 FROM dual;

+-----+---------+-----------+-----------+---------+-----------------+---------+-------
----+
| 100 | 100 * 1 | 100 * 1.0 | 100 / 1.0 | 100 / 2 | 100 + 2 * 5 / 2 | 100 /3 | 100
DIV 0 |
+-----+---------+-----------+-----------+---------+-----------------+---------+-------
----+
| 100 |   100 |   100.0 |  100.0000 | 50.0000 |     105.0000 | 33.3333 |  
 NULL |
+-----+---------+-----------+-----------+---------+-----------------+---------+-------
----+
1 row in set (0.00 sec)
#Calculate the annual basic salary of employees
SELECT employee_id,salary,salary * 12 annual_sal
FROM employees;

The following conclusions can be drawn from the calculation results:

  • When a number is multiplied by integer 1 and divided by integer 1, the original number is still obtained;
  • A number multiplied by floating point number 1 and divided by floating point number 1 becomes a floating point number, and the value is equal to the original number;
  • After a number is divided by an integer, whether it can be divided or not, the result is a floating point number;
  • When a number is divided by another number, the result will be a floating point number and will be kept to 4 decimal places;
  • The priority of multiplication and division is the same. The result of multiplication before division is the same as that of division before multiplication.
  • In mathematical operations, 0 cannot be used as a divisor. In MySQL, a number divided by 0 is NULL.

3. Modulo (remainder) operator

mysql> SELECT 12 % 3, 12 MOD 5 FROM dual;

+--------+----------+
| 12 % 3 | 12 MOD 5 |
+--------+----------+
|    0 |     2 |
+--------+----------+
1 row in set (0.00 sec)
#Filter out employees_ ID is an even number of employees
SELECT * FROM employees
WHERE employee_id MOD 2 = 0;

2, Comparison operator

The comparison operator is used to compare the operands on the left and right of the expression. If the comparison result is true, it will return 1, if the comparison result is false, it will return 0, and in other cases, it will return NULL.

Comparison operators are often used as conditions of SELECT query statements to return qualified result records.

1. Equal sign operator

  • The equal sign operator (=) determines whether the values, strings or expressions on both sides of the equal sign are equal. If they are equal, it returns 1, and if they are not equal, it returns 0.
  • When using the equal sign operator, follow the following rules:
    • If the values, strings or expressions on both sides of the equal sign are strings, MySQL will compare them according to the string, which compares whether the ANSI encoding of the characters in each string is equal.
    • If the values on both sides of the equal sign are integers, MySQL will compare the sizes of the two values according to integers.
    • If one of the values on both sides of the equal sign is an integer and the other is a string, MySQL will convert the string into a number for comparison.
    • If one of the values, strings, or expressions on either side of the equal sign is NULL, the comparison result is NULL.
  • Comparison: use of assignment symbols in SQL:=

2. Safe equals operator

The functions of the safe equal operator (< = >) and the equal operator (=) are similar. The only difference is that '< = >' can be used to judge NULL. When both operands are NULL, the return value is 1 instead of NULL; When an operand is NULL, its return value is 0, not NULL.

mysql> SELECT 1 <=> '1', 1 <=> 0, 'a' <=> 'a', (5 + 3) <=> (2 + 6), '' <=> NULL,NULL <=> NULL FROM dual;

+-----------+---------+-------------+---------------------+-------------+-------------
--+
| 1 <=> '1' | 1 <=> 0 | 'a' <=> 'a' | (5 + 3) <=> (2 + 6) | '' <=> NULL | NULL <=>
NULL |
+-----------+---------+-------------+---------------------+-------------+-------------
--+
|     1 |    0 |      1 |          1 |      0 |      
1 |
+-----------+---------+-------------+---------------------+-------------+-------------
--+
1 row in set (0.00 sec)
#Query commission_pct equals 0.40
SELECT employee_id,commission_pct FROM employees WHERE commission_pct = 0.40;
SELECT employee_id,commission_pct FROM employees WHERE commission_pct <=> 0.40;
#What if you change 0.40 to NULL?

You can see that when using the safe equal operator, when the values of operands on both sides are NULL, the returned result is 1 instead of NULL, and other returned results are the same as the equal operator.

3. Not equal to operator

The not equal operators (< > and! =) are used to judge whether the values of numbers, strings or expressions on both sides are not equal. If they are not equal, they return 1, and if they are equal, they return 0. Not equal to operator cannot judge NULL value. If either of the values on both sides is NULL, or both sides are NULL, the result is NULL. Examples of SQL statements are as follows:

mysql> SELECT 1 <> 1, 1 != 2, 'a' != 'b', (3+4) <> (2+6), 'a' != NULL, NULL <> NULL;

+--------+--------+------------+----------------+-------------+--------------+
| 1 <> 1 | 1 != 2 | 'a' != 'b' | (3+4) <> (2+6) | 'a' != NULL | NULL <> NULL |
+--------+--------+------------+----------------+-------------+--------------+
|    0 |  1  |    1  |       1  |   NULL  |     NULL |
+--------+--------+------------+----------------+-------------+--------------+
1 row in set (0.00 sec)

In addition, there are non symbolic operators:

4. Air transport operator

The airlift operator (IS NULL or ISNULL) determines whether a value IS NULL. If it IS NULL, it returns 1, otherwise it returns 0. Examples of SQL statements are as follows:

SELECT NULL IS NULL, ISNULL(NULL), ISNULL('a'), 1 IS NULL;
#Query commission_pct equals NULL. Compare the following four ways
SELECT employee_id,commission_pct FROM employees WHERE commission_pct IS NULL;
SELECT employee_id,commission_pct FROM employees WHERE commission_pct <=> NULL;
SELECT employee_id,commission_pct FROM employees WHERE ISNULL(commission_pct);
SELECT employee_id,commission_pct FROM employees WHERE commission_pct = NULL;

5. Non air operator

The non NULL operator (IS NOT NULL) determines whether a value IS NOT NULL. If it IS NOT NULL, it returns 1, otherwise it returns 0. Examples of SQL statements are as follows:

SELECT NULL IS NOT NULL, 'a' IS NOT NULL,  1 IS NOT NULL;
#Query commission_pct is not equal to NULL
SELECT employee_id,commission_pct FROM employees WHERE commission_pct IS NOT NULL;
SELECT employee_id,commission_pct FROM employees WHERE NOT commission_pct <=> NULL;
SELECT employee_id,commission_pct FROM employees WHERE NOT ISNULL(commission_pct);

6. Minimum operator

The syntax format is: leap (value 1, value 2,..., value n). Where, "value n" means that there are n values in the parameter list.

Returns the minimum value when there are two or more parameters.

SELECT LEAST (1,0,2), LEAST('b','a','c'), LEAST(1,NULL,2);

+---------------+--------------------+-----------------+
| LEAST (1,0,2) | LEAST('b','a','c') | LEAST(1,NULL,2) |
+---------------+--------------------+-----------------+
|    0    |    a      |    NULL    |
+---------------+--------------------+-----------------+
1 row in set (0.00 sec)

It can be seen from the results that when the parameter is an integer or floating point number, leap will return the smallest value;

When the parameter is a string, the character with the highest order in the alphabet is returned; When there is NULL in the comparison value list, the size cannot be determined, and the return value is NULL.

7. Maximum operator

The syntax format is greatest (value 1, value 2,..., value n).

Where n indicates that there are n values in the parameter list. When there are two or more parameters, the return value is the maximum value.

If any argument is NULL, the return value of GREATEST() is NULL.

mysql> SELECT GREATEST(1,0,2), GREATEST('b','a','c'), GREATEST(1,NULL,2);
+-----------------+-----------------------+--------------------+
| GREATEST(1,0,2) | GREATEST('b','a','c') | GREATEST(1,NULL,2) |
+-----------------+-----------------------+--------------------+
|        2 | c           |        NULL |
+-----------------+-----------------------+--------------------+
1 row in set (0.00 sec)

It can be seen from the results that when the parameter is an integer or floating point number, GREATEST will return the largest value; When the parameter is a string, the last character in the alphabet is returned;

When there is NULL in the comparison value list, the size cannot be determined, and the return value is NULL.

8. BETWEEN AND operator

The format used by the BETWEEN operator is usually SELECT D FROM TABLE WHERE C BETWEEN A AND B. at this time, when C is greater than or equal to A and C is less than or equal to B, the result is 1, otherwise the result is 0.

mysql> SELECT 1 BETWEEN 0 AND 1, 10 BETWEEN 11 AND 12, 'b' BETWEEN 'a' AND 'c';

+-------------------+----------------------+-------------------------+
| 1 BETWEEN 0 AND 1 | 10 BETWEEN 11 AND 12 | 'b' BETWEEN 'a' AND 'c' |
+-------------------+----------------------+-------------------------+
|         1 |           0 |            1 |
+-------------------+----------------------+-------------------------+
1 row in set (0.00 sec)
SELECT last_name, salary
FROM  employees
WHERE salary BETWEEN 2500 AND 3500

9. IN operator

The IN operator is used to determine whether a given value is a value IN the IN list. If yes, it returns 1, otherwise it returns 0.

If the given value is null, or if NULL exists IN the IN list, the result is null.

mysql> SELECT 'a' IN ('a','b','c'), 1 IN (2,3), NULL IN ('a','b'), 'a' IN ('a', NULL);
+----------------------+------------+-------------------+--------------------+
| 'a' IN ('a','b','c') | 1 IN (2,3) | NULL IN ('a','b') | 'a' IN ('a', NULL) |
+----------------------+------------+-------------------+--------------------+
|       1     |     0  |     NULL   |     1     |
+----------------------+------------+-------------------+--------------------+
1 row in set (0.00 sec)

SELECT employee_id, last_name, salary, manager_id
FROM  employees
WHERE manager_id IN (100, 101, 201);

10. NOT IN operator

The NOT IN operator is used to determine whether the given value is not a value IN the IN list. If it is not a value IN the IN list, it returns 1, otherwise it returns 0.

SELECT 'a' NOT IN ('a','b','c'), 1 NOT IN (2,3);

11. LIKE operator

The LIKE operator is mainly used to match strings, usually used for fuzzy matching. If the conditions are met, it returns 1, otherwise it returns 0.

If the given value or matching condition is NULL, the returned result is NULL.

The LIKE operator usually uses the following wildcards:

"%": Matches 0 or more characters.
"_": Only one character can be matched.

Examples of SQL statements are as follows:

mysql> SELECT NULL LIKE 'abc', 'abc' LIKE NULL; 
+-----------------+-----------------+
| NULL LIKE 'abc' | 'abc' LIKE NULL |
+-----------------+-----------------+
|      NULL  |      NULL  |
+-----------------+-----------------+
1 row in set (0.00 sec)

SELECT first_name
FROM employees
WHERE first_name LIKE 'S%';

SELECT last_name
FROM  employees
WHERE last_name LIKE '_o%';
  • ESCAPE

    • Avoiding special symbols: use escape characters. For example: convert [%] to[ %], [] converted to[ ], and then add [ESCAPE '$'].
    SELECT job_id
    FROM  jobs
    WHERE job_id LIKE 'IT\_%';
    
    • If you use \ to indicate ESCAPE, omit ESCAPE. If it is not \, add ESCAPE.

      SELECT job_id
      FROM  jobs
      WHERE job_id LIKE 'IT$_%' escape '$';
      

12. REGEXP operator

REGEXP operator is used to match strings. The syntax format is: expr REGEXP matching condition.

If expr meets the matching conditions, return 1; If not, 0 is returned. If either expr or matching condition is NULL, the result is NULL.

  • The following wildcards are commonly used in REGEXP operator matching:
(1)'^'Matches a string that begins with a character after that character.
(2)'$'Matches a string that ends with the character preceding the character.
(3)'.'Matches any single character.
(4)"[...]"Matches any character within square brackets. For example“[abc]"Match“ a"Or“ b"Or“ c". To name the range of characters, use a'-'. "[a-z]"Match any letter, and“[0-9]"Match any number.
(5)'*'Matches zero or more characters that precede it. For example“ x*"Match any number of'x'Characters“[0-9]*"Match any number of numbers, and“*"Matches any number of characters.
  • Examples of SQL statements are as follows

    SELECT 'shkstart' REGEXP '^s', 'shkstart' REGEXP 't$', 'shkstart' REGEXP 'hk';
    SELECT 'atguigu' REGEXP 'gu.gu', 'atguigu' REGEXP '[ab]';
    

3, Logical operator

Logical operators are mainly used to judge whether an expression is true or false. In MySQL, the return results of logical operators are 1, 0 or NULL.

MySQL supports four logical operators as follows:

1. Logical non operator

The logical non (NOT or!) operator means that when the given value is 0, it returns 1; when the given value is non-0, it returns 0;

Returns NULL when the given value is NULL.

SELECT NOT 1, NOT 0, NOT(1+1), NOT !1, NOT NULL;  
SELECT last_name, job_id
FROM  employees
WHERE job_id NOT IN ('IT_PROG', 'ST_CLERK', 'SA_REP');

2. Logic and operators

The logical AND (AND or & &) operator returns 1 when all the given values are non-zero AND are not NULL;

When a given value or values are 0, it returns 0; otherwise, it returns NULL.

SELECT 1 AND -1, 0 AND 1, 0 AND NULL, 1 AND NULL;

SELECT employee_id, last_name, job_id, salary
FROM  employees
WHERE salary >=10000
AND  job_id LIKE '%MAN%';

3. Logical or operator

The logical OR (OR or |) operator returns 1 when the given value is not NULL and any value is a non-0 value, otherwise it returns 0;

When one value is null and the other value is non-zero, it returns 1; otherwise, it returns NULL; when both values are null, it returns null.

SELECT 1 OR -1, 1 OR 0, 1 OR NULL, 0 || NULL, NULL || NULL; 
#Query employee number and basic salary whose basic salary is not between 9000-12000
SELECT employee_id,salary FROM employees
WHERE NOT (salary >= 9000 AND salary <= 12000);

SELECT employee_id,salary FROM employees
WHERE salary <9000 OR salary > 12000;

SELECT employee_id,salary FROM employees
WHERE salary NOT BETWEEN 9000 AND 12000;
SELECT employee_id, last_name, job_id, salary
FROM  employees
WHERE salary >= 10000
OR   job_id LIKE '%MAN%';

be careful:

OR can be used together with AND, but pay attention to their priority when using. Since the priority of AND is higher than OR, first operate on the operands on both sides of AND, AND then combine them with the operands in OR.

4. Logical XOR operator

Different, different is 1, the same is 0

The XOR operator returns NULL when any one of the given values is NULL;

If both non NULL values are 0 or are not equal to 0, 0 is returned; if one value is 0 and the other value is not 0, 1 is returned.

mysql> SELECT 1 XOR -1, 1 XOR 0, 0 XOR 0, 1 XOR NULL, 1 XOR 1 XOR 1, 0 XOR 0 XOR 0;

+----------+---------+---------+------------+---------------+---------------+
| 1 XOR -1 | 1 XOR 0 | 0 XOR 0 | 1 XOR NULL | 1 XOR 1 XOR 1 | 0 XOR 0 XOR 0 |
+----------+---------+---------+------------+---------------+---------------+
|     0 |    1 |    0 |    NULL |       1 |       0 |
+----------+---------+---------+------------+---------------+---------------+
1 row in set (0.00 sec)
select last_name,department_id,salary
from employees
where department_id in (10,20) XOR salary > 8000;

4, Bitwise operator

Bit operators are operators that evaluate on binary numbers. Bit operators first convert operands into binary numbers, then perform bit operations, and finally change the calculation result from binary to decimal numbers.

MySQL supports the following bitwise operators:

1. Bitwise and operator

The bitwise and (&) operator performs a bitwise logical and operation on the binary number corresponding to the given value.

When the value of the binary bit corresponding to the given value is 1, the bit returns 1, otherwise it returns 0.

SELECT 1 & 10, 20 & 30;

The binary number of 1 is 0001 and the binary number of 10 is 1010, so the result of 1 & 10 is 0000 and the corresponding decimal number is 0.

The binary number of 20 is 10100 and the binary number of 30 is 11110, so the result of 20 & 30 is 10100 and the corresponding decimal number is 20.

2. Bitwise OR operator

Bitwise OR (|) operator performs bitwise logical or operation on the binary number corresponding to a given value.

When one or both of the values of the binary bit corresponding to the given value are 1, the bit returns 1, otherwise it returns 0.

SELECT 1 | 10, 20 | 30;

The binary number of 1 is 0001 and the binary number of 10 is 1010, so the result of 1 | 10 is 1011 and the corresponding decimal number is 11.

The binary number of 20 is 10100 and the binary number of 30 is 11110, so the result of 20 | 30 is 11110 and the corresponding decimal number is 30.

3. Bitwise exclusive or operator

Bitwise exclusive or (^) operator performs bitwise logical exclusive or operation on the binary number corresponding to a given value.

When the value of the binary bit corresponding to the given value is different, the bit returns 1, otherwise it returns 0.

SELECT 1 ^ 10, 20 ^ 30;

The binary number of 1 is 0001 and the binary number of 10 is 1010, so the result of 1 ^ 10 is 1011 and the corresponding decimal number is 11.

The binary number of 20 is 10100 and the binary number of 30 is 11110, so the result of 20 ^ 30 is 01010 and the corresponding decimal number is 10.

Another example:

SELECT 12 & 5, 12 | 5,12 ^ 5 FROM DUAL;
+--------+--------+--------+
| 12 & 5 | 12 | 5 | 12 ^ 5 |
+--------+--------+--------+
|    4 |   13 |    9 |
+--------+--------+--------+
1 row in set (0.00 sec)

4. Bitwise negation operator

Bitwise negation (~) operator negates the binary number of a given value bit by bit, that is, 1 becomes 0 and 0 becomes 1.

mysql> SELECT 10 & ~1;
+---------+
| 10 & ~1 |
+---------+
|    10 |
+---------+
1 row in set (0.00 sec)

Since the priority of the bitwise negation (~) operator is higher than that of the bitwise and (&) operators, 10 & ~ 1, first, carry out the bitwise negation operation on the number 1, and the result is 1 except that the lowest bit is 0. Then, carry out the bitwise and operation with 10, and the result is 10.

5. Bitwise shift right operator

The shift right by bit (> >) operator shifts all bits of the binary number of a given value to the right by the specified number of bits. After shifting the specified number of bits to the right, the low-order value on the right is removed and discarded, and the empty position on the high-order number on the left is filled with 0.

mysql> SELECT 1 >> 2, 4 >> 2;
+--------+--------+
| 1 >> 2 | 4 >> 2 |
+--------+--------+
|    0 |    1 |
+--------+--------+
1 row in set (0.00 sec)

The binary number of 1 is 0000 0001, shifted 2 bits to the right is 0000 0000, and the corresponding decimal number is 0. The binary number of 4 is 0000 0100, shifted 2 bits to the right is 0000 0001, and the corresponding decimal number is 1.

6. Bitwise shift left operator

Shift left by bit (< <) operator shifts all bits of the binary number of a given value to the left by the specified number of bits. After shifting the specified number of bits to the left, the high-order value on the left is removed and discarded, and the empty position on the low-order value on the right is filled with 0.

mysql> SELECT 1 << 2, 4 << 2; 
+--------+--------+
| 1 << 2 | 4 << 2 |
+--------+--------+
|    4 |   16 |
+--------+--------+
1 row in set (0.00 sec)

The binary number of 1 is 0000 0001, the left shifted two bits is 0000 0100, and the corresponding decimal number is 4.

The binary number of 4 is 0000 0100, the left shifted two bits is 00010000, and the corresponding decimal number is 16.

5, Operator priority

The larger the number, the higher the priority. Operators with higher priority are calculated first.

It can be seen that the assignment operator has the lowest priority and the expression enclosed by "()" has the highest priority.

Tags: Database MySQL SQL

Posted on Sat, 20 Nov 2021 09:34:12 -0500 by Arrow