Case:Create data table tmp15, which contains the field note of varchar type and the field price of int type.
Use bit operators to perform bit operations on data.
(Free learning recommendation:mysql video tutorial)
First create the tmp15 table, insert a record, the note value is "Thisisgood ", the price value is 50, the SQL statement is as follows:
mysql> create table tmp15 -> ( -> note varchar(100), -> price int -> );Query OK, 0 rows affected (0.13 sec)mysql> into tmp15 values -> ( -> "Thisisgood",50 -> ); mysql> insert into tmp15 values -> ("Thisisgood",50);Query OK, 1 row affected (0.06 sec)
(1) Perform arithmetic operations on the integer value field price in table tmp15, the SQL statement is as follows:
mysql> select price, -> price + 10, -> price - 10, -> price * 2, -> price / 2, -> price % 3 -> from tmp15;+-------+------------+------------+-----------+-----------+-----------+| price | price + 10 | price - 10 | price * 2 | price / 2 | price % 3 |+-------+------------+------------+-----------+-----------+-----------+| 50 | 60 | 40 | 100 | 25.0000 | 2 |+-------+------------+------------+-----------+-----------+-----------+1 row in set (0.00 sec)
( 2) Compare the integer value field price in table tmp15. The SQL statement is as follows:
mysql> select price, -> price>10, -> price<10, -> price != 10, -> price = 10, -> price<=>10, -> price<>10 -> from tmp15;+-------+----------+----------+-------------+------------+------------+-----------+| price | price>10 | price<10 | price != 10 | price = 10 | price<=>10 | price<>10 |+-------+----------+----------+-------------+------------+------------+-----------+| 50 | 1 | 0 | 1 | 0 | 0 | 1 |+-------+----------+----------+-------------+------------+------------+-----------+1 row in set (0.00 sec)
(3) Determine whether the price value falls in the range of 30-80 and return the maximum value between 70 and 30. , to determine whether price is a value in the in list (10, 20, 50, 35), the SQL statement is as follows:
mysql> select price, -> price between 30 and 80, -> greatest(price,70,30), -> price in(10,20,50,35) -> from tmp15;+-------+-------------------------+-----------------------+-----------------------+| price | price between 30 and 80 | greatest(price,70,30) | price in(10,20,50,35) |+-------+-------------------------+-----------------------+-----------------------+| 50 | 1 | 70 | 1 |+-------+-------------------------+-----------------------+-----------------------+1 row in set (0.00 sec)
(4) Compare the string value field note in tmp15 and determine Whether the note field in table tmp15 is empty, use LIKE to determine whether it starts with the letter "t", use regexp to determine whether it ends with the letter "y", or whether it contains the letter "g" or "m", the SQL statement is as follows:
mysql> select note, -> note is null, -> note like 't%', -> note regexp '$y', -> note regexp '[gm]' -> from tmp15;+------------+--------------+----------------+------------------+--------------------+| note | note is null | note like 't%' | note regexp '$y' | note regexp '[gm]' |+------------+--------------+----------------+------------------+--------------------+| Thisisgood | 0 | 1 | 0 | 1 |+------------+--------------+----------------+------------------+--------------------+1 row in set (0.05 sec)
(5) Perform logical operations on the price field value and null and 0. The SQL statement is as follows:
mysql> select price, -> price && 1, -> price && null, -> price || 0, -> price and 0, -> 0 and null, -> price or null -> from tmp15;+-------+------------+---------------+------------+-------------+------------+---------------+| price | price && 1 | price && null | price || 0 | price and 0 | 0 and null | price or null |+-------+------------+---------------+------------+-------------+------------+---------------+| 50 | 1 | NULL | 1 | 0 | 0 | 1 |+-------+------------+---------------+------------+-------------+------------+---------------+1 row in set (0.00 sec)mysql> select price, -> !price, -> not null, -> price xor 3, -> 0 xor null, -> price xor 0 -> from tmp15;+-------+--------+----------+-------------+------------+-------------+| price | !price | not null | price xor 3 | 0 xor null | price xor 0 |+-------+--------+----------+-------------+------------+-------------+| 50 | 0 | NULL | 0 | NULL | 1 |+-------+--------+----------+-------------+------------+-------------+1 row in set (0.00 sec)
(6) Perform bitwise AND and bitwise OR operations on the price field value and 2 and 4. , and perform bitwise operations on price. The SQL statement is as follows:
mysql> select price, -> price & 2, -> price | 4, -> ~price from tmp15;+-------+-----------+-----------+----------------------+| price | price & 2 | price | 4 | ~price |+-------+-----------+-----------+----------------------+| 50 | 2 | 54 | 18446744073709551565 |+-------+-----------+-----------+----------------------+1 row in set (0.00 sec)
(7) Move the price field value left and right by two places respectively. The SQL statement is as follows:
mysql> select price, -> price<<2, -> price>>2 -> from tmp15;+-------+----------+----------+| price | price<<2 | price>>2 |+-------+----------+----------+| 50 | 200 | 12 |+-------+----------+----------+1 row in set (0.00 sec)
Related free learning recommendations:mysql database(Video)
The above is the detailed content of mysql exercise 2: use of operators. For more information, please follow other related articles on the PHP Chinese website!