1 在t表上使用算术运算符合比较运算符进行运算

(1)t表中只包含两个字段。分别是字段num和字段str,两者分别是INT类型和VARCHAR类型。代码运行如下：

mysql> CREATE TABLE t( num INT, str VARCHAR(20));
Query OK, 0 rows affected (0.01 sec)

(2)向t表中插入一条记录。num值为30,str值为'mysql'。代码执行如下：

mysql> INSERT INTO t VALUES( 30, 'mysqr);
Query OK, 1 row affected (0.00 sec)

(3)从t表中取出nUm的值与数字4进行加法、减法、乘法、除法和求余运算。代码 执行如下：

mysql> SELECT num,num+4,nun-4,num*4,num DIV 4,num%4 FROM t;
+-----+--------+-------+-------+-----------+-------+
| num 丨 num+4 | nun-4 | num*4 | num DIV 4 | num%4 |
+-----+--------+-------+-------+-----------+-------+
|  30 |   34   |   26  |  120  |      7    |    2  |
+-----+--------+-------+-------+-----------+-------+
1 row in set (0.03 sec)

(4)使用比较运算符将num的值与20进行比较。代码执行如下：

mysql> SELECT num,num=20,num<>20,num>20,num>=20,num<20,num<=20,num<=>20 FROM t;
+-------+--------+---------+--------+---------+--------+---------+----------+
| num   | num=20 | num<>20 | num>20 | num>=20 | num<20 | num<=20 | num<=>20 |
+-------+--------+---------+--------+---------+--------+---------+----------+
|   30  |    0   |     1   |    1   |    1    |    0   |    0    |     0    |
+-------+--------+---------+--------+---------+--------+---------+----------+
1 row in set (0.00 sec)

(5)判断num的值是否落在26~33之间，并且判断num的值是否在（3，28，30, 33)这个集合中。代码执行如下：

mysql> SELECT num,num BETWEEN 26AND 33,num IN(3,28,30,33) FROM t;
+-----+-----------------------+--------------------+
| num | num BETWEEN 26 AND 33 | num IN(3,28,30,33) |
+-----+-----------------------+--------------------+
| 301 |            1          |           1        |
+-----+-----------------------+--------------------+
1 row in set (0.00 sec)

(6)判断t表的str字段的值是否为空；用LIKE来判断是否是以“my”这两个字母开头；用REGEXP来判断是否第一字母是M最后一个字母是Y。代码执行如下：

mysql> SELECT str,str IS NULL,str LIKE 'my%',str REGEXP '^m',str REGEXP 'y$' FROM t;
+------+-------------+----------------+-----------------+-----------------+
| str  | str IS NULL | str LIKE 'my%' | str REGEXP '^m' | str REGEXP ’y$’ |
+------+-------------+----------------+-----------------+-----------------+
|mysql |     0       |       1        |        1        |      0          |
+------+-------------+----------------+-----------------+-----------------+
1 row in set (0.00 sec)

2.将数字2、0和如1之间的任意两个进行逻辑运算

逻辑运算包括与、或、非和异或4种。分别将2、0和NULL中的任意两个进行逻辑运算。进行与和或运算的代码执行如下：

mysql> SELECT 2&&0,2&&NULL,0 AND NULL,2||0,2||NULL,0 OR NULL;
+------+---------+-----------+------+---------+-----------+
| 2&&0 | 2&&NULL | OAND NULL | 2||0 | 2||NULL | 0 OR NULL |
+------+---------+-----------+------+---------+-----------+
|   0  |   NULL  |     0     |   1  |    1    |    NULL   |
+------+---------+-----------+------+---------+-----------+
1 row in set (0.00 sec)

进行非和异或运算的代码执行如下：

mysql> SELECT !2,!0,NOT NULL,2 XOR 0,2 XOR NULL,0 XOR NULL;
+------+---------+-----------+---------+------------+-------------+
|  !2  |   !0    |  NOT NULL | 2 XOR 0 | 2 XOR NULL 丨 0 XOR NULL |
+------+---------+-----------+---------+------------+-------------+
|   0  |     1   |    NULL   |     1   |     NULL   |      NULL   |
+------+---------+-----------+---------+------------+-------------+
1 row in set (0.00 sec)

3.按下列要求进行位运算 (1)将数字4和6进行按位与、按位或。并将4按位取反。代码执行如下:

mysql> SELECT 4&6,4|6,~4;
+-----+-----+--------------------+
| 4&6 | 4|6 |   ~4               |
+-----+-----+--------------------+
|  4  |   6 |18446744073709551611|
+-----+-----+--------------------+
1 row in set (0.00 sec)

(2)将数字6分别左移两位和右移两位。代码执行如下:

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