SQL笔记-- 2. 运算符

1. 最简单的直接进行加减乘除

SELECT 100+1, 100-4, 100+'1', 100-'3', 100-'a', 100+'b',
100*3,100*'a',100/3,100 DIV 3 FROM DUAL;

 2.比较运算符

包括=，<=>, <=,>=, !=

其中 <=> 作为安全等于符号， 主要用于null值的等于，当然也肯用is null 代替。

3. 一些文字运算符

针对空值的

SELECT last_name,commission_pct FROM employees WHERE commission_pct IS NULL;
SELECT last_name,commission_pct FROM employees WHERE commission_pct IS NOT NULL;

最小最大值 least/greatest(.....)

范围限制

a.在范围中

SELECT last_name,salary FROM employees WHERE salary BETWEEN 4000 AND 6000;
SELECT last_name,salary FROM employees WHERE salary <= 6000 AND salary >= 4000;

b.在范围外

SELECT last_name,salary FROM employees WHERE salary NOT BETWEEN 4000 AND 6000;
SELECT last_name,salary FROM employees WHERE salary > 6000 OR salary < 4000;

离散限制

SELECT last_name,department_id FROM employees WHERE department_id IN(10,20,30);
SELECT last_name,department_id FROM employees WHERE department_id =10 OR department_id =20
OR department_id =30;

当选择不在范围中的，也是加not（即NOT IN(10,20,30)）

模糊查询

查询last_name 中包含a和e的员工信息

SELECT last_name first_name FROM employees WHERE last_name LIKE '%a%' AND 
last_name LIKE '%e%';

查询第二个字符是a的员工信息（前面加一个下换线代表前面有一个字符,两个连续的下划线代表前面有两个字符）

SELECT last_name first_name FROM employees WHERE last_name LIKE '_a%';

正则表达式

‘^’代表以该字符开头； ‘$’代表以该字符结尾； ‘.’ 匹配任一个单字符； '[...]' 匹配括号里的所有之一就可以，‘*’ 匹配0个或者多个在他前面的（eg. f*n 可以匹配 fan， fwwn...）

SELECT 'lijun' REGEXP '^l','lijun' REGEXP '^i',
'lijun'REGEXP 'l.n','lijun' REGEXP 'l.j';

 3. 逻辑运算符， and, or, not, xor(异或，当两边条件一真一假时选择）

优先级not>and>or, xor