SQL运算符-4

算术运算符

加法与减法：+、-

• 整数加减：整数
• 有浮点数加减：浮点数
• 有字符串加减：转化为数值加减；不能转化记作0；

乘法与除法：*、/或div

• 整数相乘：整数
• 有浮点数相乘：浮点数（保留小数点后四位）
• 除法：浮点数
• 一个数除以0为NULL

求模（求余）%或mod

SELECT 12 % 3, 12 MOD 5 FROM dual;

比较运算符

等号、不等于：=；<=>、<>；!=

• =
  • 都是值，值比较
  • 都是字符串，ANSI比较
  • 值与字符串，将字符串转化为数字比较
  • 有一个为NULL，比较结果为NULL
  • SQL中赋值符号使用 :=
• <=>
  • 为了NULL
  • ‘’ <=> NULL结果为0
  • NULL <=> NULL结果为1
• <>，!=
  • 不相等则返回1，相等则返回0
  • 不能判断NULL，有NULL结果为NULL

空运算符、非空运算符

• IS NULL、ISNULL
  • 一个值是否为NULL，是返回1，否返回0
  • select null is null, isnull(null);
• IS NOT NULL
  • 一个值是否不为NULL，不为null返回1，否则返回0
  • select null is not null;

最小值、最大值运算符least, greatest

• LEAST
  • 参数是整数或浮点数，返回最小值
  • 参数是字符，顺序比较前面的字符，返回靠前
  • 参数有null，返回null
• GREATEST
  • 参数是整数或浮点数，返回最最值
  • 参数是字符，顺序比较前面的字符，返回靠后
  • 参数有null，返回null

BETWEEN AND

• 左右均闭
• 左为下界，右为上届

IN、NOT IN

• 给定的值是否是IN列表中的一个值
• 给定的值或列表中的值有null，结果为null

LIKE

• 模糊匹配，满足条件返回1，否则返回0
• 给定的值或匹配条件有null，结果为null
• “%”：匹配0个或多个字符。
• “_”：只能匹配一个字符。

ESCAPE

• 使用转义符
• 默认\转义：job_id LIKE ‘IT\_%‘;
• 其他转义：job_id LIKE ‘IT$_%‘ escape ‘$‘;

REGEXP

• 语法格式： expr REGEXP 匹配条件
• '^'以它后面字符开头的字符串
• '$'以它后面字符结尾的字符串
• '.'匹配任意单字符
• '[…]'方括号内任意字符（[abc], [a-z], [0-9]）
• ‘*’ 匹配零个或多个在它前面的字符

逻辑运算符

非not 或 ！

• 给定的值为0时返回1
• 给定的值为1时返回0
• 给定的值为null时返回null

与and 或 &&

• 所有值均为非0值，且没有null，返回1
• 所有值有0值，返回0 0 AND NULL
• 其他返回null1 AND NULL |

或or 或 ||

• 给定的值都不为null，且有任意一个值为0，返回1
• 有一个值为1，返回1，1 OR NULL
• 两个都是null，或有null另一个为0，返回null0 || NULL NULL || NULL

异或 xor

• 任意值为NULL，返回NULL
• 都是0或者都不等于0，返回0
• 一个值为0，另一个值不为0，返回1

位运算符

按位与 &

• 给定值对应的二进制数逐位进行逻辑与运算

按位或 |

• 给定值对应的二进制数逐位进行逻辑或运算

取反 ~

• 将给定的值的二进制数逐位进行取反操作

右移、左移 >> <<

• 给定的值的二进制数的所有位右移指定的位数
• 给定的值的二进制数的所有位左移指定的位数

运算符的优先级

拓展：使用正则表达式查询

练习

• 1.选择工资不在5000到12000的员工的姓名和工资

select last_name, salary
from employees
where salary not between 5000 and 12000;

select last_name, salary
from employees
where salary < 5000 or salary > 12000;

• 2.选择在20或50号部门工作的员工姓名和部门号

select last_name, department_id
from employees
where department_id = 20 or department_id = 50;

select last_name, department_id
from employees
where department_id in (20,50);

• 3.选择公司中没有管理者的员工姓名及job_id

select last_name, job_id
from employees
where manager_id is null;

select last_name, job_id
from employees
where isnull(manager_id );

• 4.选择公司中有奖金的员工姓名，工资和奖金级别

select last_name, salary, commission_pct
from employees
where commission_pct is not null;

• 5.选择员工姓名的第三个字母是a的员工姓名

select last_name
from employees
where last_name like '__a%';

• 6.选择姓名中有字母a和k的员工姓名

select last_name
from employees
where last_name like '%a%k%' or last_name like '%k%a%';

• 7.显示出表 employees 表中 first_name 以 'e’结尾的员工信息

select first_name
from employees
where first_name regexp 'e$';

select first_name
from employees
where first_name like '%e';

• 8.显示出表 employees 部门编号在 80-100 之间的姓名、工种

select last_name, job_id, department_id
from employees
where department_id between 80 and 100;

• 9.显示出表 employees 的 manager_id 是 100,101,110 的员工姓名、工资、管理者id

select last_name, salary, manager_id
from employees
where manager_id in (100,101,110);