MYSQL运算符
运算符
算数运算符
注:
SELECT 100+1
等价于
SELECT 100+'1'(隐式转化)
SELECT 100+'a'#其中的a看作是0,结果是100
除法(/或是div)结果是浮点型(除数是0答案是null)
取模(%或是mod)结果的符号与被模数一样
比较运算符
注:
- =:一个等于号就是等号(有null参与答案也是null)
如果是字符串和字符串比较就不进行隐式转化
SELECT id
FROM employees
WHERE id = null;#不出结果
-
<=>:安全等于(解决null的问题,两边是null,结果是1)
-
<>:不等于
注:
- IS NULL \ IS NOT NULL \ ISNULL:
SELECT id
FROM employees
WHERE id IS NULL;#出结果是null的数据
等价于
SELECT id
FROM employees
WHERE ISNULL(id);
- LEAST \ GREATEST(比ASCII的值)
select least('q','b','t','a'),greatest('q','b','t','m')
from dual; # a t
- BETWEEN ... AND(左闭右闭,从小到大)
SELECT id
FROM employees
WHERE id (not) between 1001 and 1003;
- IN(集合) \ NOT IN(集合)(离散)
SELECT id
FROM employees
WHERE id in (1001,1002,1003);#或的关系
- LIKE(模糊查询)
SELECT name #查询name里有a字符的员工
FROM employees
WHERE name LIKE '%a%';#%:代表不确定个数的字符
WHERE name LIKE 'a%';#以a开头
WHERE name LIKE '%a';#以a结尾
SELECT name #查询name里有a和e字符的员工
FROM employees
WHERE name LIKE '%a%e%' or '%e%a%';
WHERE name LIKE '%a%' and name LIKE '%e%';
SELECT name #查询name第二个字符是a字符的员工
FROM employees
WHERE name LIKE '_a%';#:代表一个不确定字符
SELECT name #查询name第二个是_,第三个是a字符的员工
FROM employees
WHERE name LIKE '_\_a%';#转义
- REGEXP \ RLIKE(正则表达式,独立于SQL的规则)
SELECT 'wht' REGEXP '^w'
FROM dUAL;
SELECT 'wht' REGEXP 'w.t', 'wht' REGEXP '[ht]'
FROM dUAL;
逻辑运算符
-
XOR:前后的真假不一样(只满足其中一方的条件)
-
AND优先级>OR
位运算符(频率较低)
- 左右移动:在一定范围内满足规律