数据库_days4_运算符

算术运算符

#加、减、乘、除（DIV）
mysql>  SELECT 1+1,1-1,2*4,3/8;
+-----+-----+-----+--------+
| 1+1 | 1-1 | 2*4 | 3/8    |
+-----+-----+-----+--------+
|   2 |   0 |   8 | 0.3750 |
+-----+-----+-----+--------+
#数字和字符串相加会有警告
#取余：% 、MOD
mysql> SELECT 3%8;
+------+
| 3%8  |
+------+
|    3 |
+------+
1 row in set (0.00 sec)
#也可以表达为：SELECT MOD(3,8);
mysql> SELECT 3 MOD 8; 
+---------+
| 3 MOD 8 |
+---------+
|       3 |
+---------+
1 row in set (0.00 sec)
#NULL参加运算
mysql> SELECT 1+NULL;
+--------+
| 1+NULL |
+--------+
|   NULL |
+--------+
1 row in set (0.00 sec)

比较运算符

#<,>,=,!=
#结果真为1，结果假为0，数字可以和字符串比较
mysql> SELECT 1=1,1='1',1=2;
+-----+-------+-----+
| 1=1 | 1='1' | 1=2 |
+-----+-------+-----+
|   1 |     1 |   0 |
+-----+-------+-----+
1 row in set (0.00 sec)

• 判断 NULL 必须用 <=> 或IS
  创建一个新表，插入一条值为NULL的数据

CREATE TABLE abc (id TINYINT);
INSERT abc VALUE(NULL);
mysql> SELECT * FROM abc;
+------+
| id   |
+------+
| NULL |
+------+

判断 NULL 用 <=>

mysql> SELECT id<=>null FROM abc;
+-----------+
| id<=>null |
+-----------+
|         1 |
+-----------+

mysql> SELECT id=null FROM abc;
+---------+
| id=null |
+---------+
|    NULL |
+---------+

判断 NULL 也可以用 IS

mysql> SELECT id IS null FROM abc;
+------------+
| id IS null |
+------------+
|          1 |
+------------+

• BETWEEN AND
  查询之前user表中年龄在10到30之间的人(包括10和30)

SELECT id,username,age,age BETWEEN 10 AND 30 FROM cms_user;

• LIKE/NOT LIKE
  一个字符与一个‘_’匹配，结果为真

mysql> SELECT 's' LIKE '_';
+--------------+
| 's' LIKE '_' |
+--------------+
|            1 |
+--------------+

一个汉字也算一个‘_’
匹配两个字符

SELECT id,username,username LIKE '__' FROM cms_user;

• REGEXP正则匹配

SELECT id,username,username REGEXP '^t' FROM cms_user;

只有t/T开头的匹配结果为1

逻辑运算符

&&、||

mysql> SELECT 2&&2,2&&0,2&&NULL,1||1,1||0,1||NULL,0||NULL;
+------+------+---------+------+------+---------+---------+
| 2&&2 | 2&&0 | 2&&NULL | 1||1 | 1||0 | 1||NULL | 0||NULL |
+------+------+---------+------+------+---------+---------+
|    1 |    0 |    NULL |    1 |    1 |       1 |    NULL |
+------+------+---------+------+------+---------+---------+

!/NOT

mysql> SELECT !1,!0,!NULL;
+----+----+-------+
| !1 | !0 | !NULL |
+----+----+-------+
|  0 |  1 |  NULL |
+----+----+-------+

XOR 异或
不同为真，相同为假

mysql> SELECT 1 XOR 0,0 XOR 1 ,1 XOR 1,0 XOR 0;
+---------+---------+---------+---------+
| 1 XOR 0 | 0 XOR 1 | 1 XOR 1 | 0 XOR 0 |
+---------+---------+---------+---------+
|       1 |       1 |       0 |       0 |
+---------+---------+---------+---------+