一、去重和空值
学习运算符之前我们先讨论一下去重和空值的问题：
这是我们要操作的表
select DEPARTMENT_ID from employees order by EMPLOYEE_ID ;
查看部门id，发现很多都是一个重复的
1.distinct
select distinct DEPARTMENT_ID from employees order by DEPARTMENT_ID;
2.null参与运算
select employee_id , salary "月工资" , salary * ( 1 + commission_pct ) * 12 "年工资" from employees;
运算中有一个null，结果为null
3.用ifnull函数解决问题
select employee_id , salary “月工资” , salary * ( 1 + IFNULL(commission_pct,0) ) * 12 “年工资” , commission_pct from employees;
if null函数本质是将null变为0参与运算。
二、比较运算符
select employee_id , salary from employees where salary < 2400;
三、dual伪表和数值运算
1.常规运算
浮点型转化
字符串隐式转化
字符串和null
字符串隐式转化成0（不包含数字）
除法0值的问题
select 100/0.0,100 div 10 ,100 div 0 from dual;
取余0值问题
select 100 % 3,100%0 from dual;
2.比较运算符
select 1=2,1!=2,1='1',1='a' from dual;
select 'a' = 'a','a'='ab','a'='b' from dual;
空值参与比较运算的问题
select last_name,salary from employees where COMMISSION_PCT = NULL;
3.<=>安全相等
select last_name,salary from employees where COMMISSION_PCT <=> NULL;
除了<=>之外与null相运算都是null
不相等
四、常用正则相关的比较运算符
1.基本运算符
is null
select last_name,salary from employees where COMMISSION_PCT is NULL;#
not 取反
select last_name,salary from employees where NOT (COMMISSION_PCT <=> NULL);
等价于is not null
select last_name,salary from employees where (COMMISSION_PCT IS NOT NULL);
least()和greatest()
select least('a','b','c','e'),greatest('a','b','c','e') from dual;
取两列中最长的字符串和最短的字符串
select least( first_name, last_name ),greatest(first_name, last_name) from employees;
betweent … and 区间
注意区间不能为空，不然输出会有问题
select * from employees where salary between 6000 and 8000 ;
等价于
select * from employees where salary > 6000 and salary < 8000 ;
in和not in
select * from employees where department_id in (10,20,30,40);
等价于
select * from employees where department_id = 10 or department_id = 20 or department_id = 30 or department_id = 40;
not in
select * from employees where department_id not in (10,20,30,40);
2.模糊查询
%：代表不确定个数的字符
查询包含字符’a’的员工信息
查询字符’a’开头的员工信息
查询包含字符’a’和’e’的员工信息
写法1：
写法2：
select last_name from employees where last_name like '%a%e%' or last_name like '%e%a%' ;
_：占位符一个不确定的字符
select last_name from employees where last_name like '_a%' ;
转义字符
\
查询JOB_ID的第三个字符是_且第四个是a的JOB_ID：
select JOB_ID from employees where JOB_ID like "__\_a%" ;
其他字符做转义，需要告诉系统
$做转义字符：
select JOB_ID from employees where JOB_ID like "__$_a%" escape '$' ;
3.正则表达式
regexp 和 rlike
select "shkstart" regexp "^s","shkstart" regexp "t$","shkstart" regexp "hk" , "shkstart" regexp "ht"from dual;
select "guigui" regexp "gu.gu","guigui" regexp "[gu]" from dual;
五、逻辑运算符
or：
select last_name from employees where last_name like '%a%e%' or last_name like '%e%a%' ;
and：
select last_name,salary,department_id from employees where department_id = 50 and salary > 6000;
六、位运算
select 4<<1,8>>1 from dual;
总结
常见运算符基本就是这些了，里面有使用案例，希望大家后面多多练习，毕竟mysql除了懂原理外大量的练习才是王道。
可以点个小👍吗
