一、算术运算符
+ 加
- 减
* 乘
div / 除以
mod % 取模
SELECT 100 + 'A' FROM DUAL;
SELECT 100 - '1' FROM DUAL;
SELECT 100 * 2 FROM DUAL;
SELECT 100 / 0 FROM DUAL;
SELECT 100 DIV 2 FROM DUAL;
SELECT 100 MOD 2 FROM DUAL;
二、比较运算符
1、隐式转换
'A' 跟数值 1 比较时，会隐式转换成数值 0
SELECT 1 = 'A' FROM DUAL
'1' 跟数值 1 比较时，会隐式转换成数值 1,
SELECT 1 = '1' FROM DUAL;
'A' 跟字符串比较时，会隐式转换ASCN
SELECT 'A' = 'A' FROM DUAL;
2、NULL
NULL 跟任何比较都等于NULL
SELECT 1 = NULL,NULL = NULL FROM DAUL;
SELECT last_name FROM employess WHERE commission_pct = NULL;
3、<=> 安全等于
SELECT 1 <=> NULL, NULL <=> NULL FROM DUAL;
SELECT last_name FROM employess WHERE commission_pct <=> NULL;
4、其它
<> != 不等于
< 小于
> 大于
<= 小于等于
>= 大于等于
5、IS NULL \ IS NOT NULL \ ISNULL(EXP)
SELECT last_name FROM employees WHERE commission_pct IS NULL;
SELECT last_name FROM employees WHERE NOT commission_pct <=> NULL;
SELECT last_name FROM employees WHERE commission_pct IS NOT NULL;
SELECT last_name FROM employees WHERE ISNULL(commission_pct);
6、IN(SET) \ NOT IN(SET)
SELECT last_name,department_id FROM employees WHERE department_id IN(10,20,30);
SELECT last_name,department_id FROM employees WHERE department_id NOT IN(10,20,30);
7、LEAST(SET) \ GREATEST(SET)
SELECT LEAST('A','B'),GREATEST('A','B') FROM DUAL;
8、BETWEEN ... AND 从小到大 闭区间
SELECT last_name,salary FROM employees WHERE salary BETWEEN 6000 AND 8000;
9、LIKE 模糊匹配
名字中有a的
SELECT last_name FROM employees WHERE last_name LIKE '%a%';
名字首字母为a的
SELECT last_name FROM employees WHERE last_name LIKE 'a%';
名字第二字母为a的
SELECT last_name FROM employees WHERE last_name LIKE '_a%';
名字第二字母为_
SELECT last_name FROM employees WHERE last_name LIKE '_/_%';
----------练习题-------------------
选择工资不在5000到12000的员工的姓名和工资
SELECT last_name,salary from employees
where salary NOT BETWEEN 5000 AND 12000
选择在20或50号部门工作的员工姓名和部门号
SELECT last_name,department_id FROM employees
WHERE department_id in(20,50)
选择公司中没有管理者的员工姓名及job_id
SELECT last_name,job_id FROM employees
WHERE manager_id IS NULL;
SELECT last_name,job_id FROM employees
WHERE manager_id <=> NULL;
选择公司中有奖金的员工姓名，工资和奖金级别
SELECT last_name,salary,commission_pct FROM employees
WHERE NOT commission_pct <=> NULL;
SELECT last_name,salary,commission_pct FROM employees
WHERE commission_pct IS NOT NULL;
选择员工姓名的第三个字母是a的员工姓名
SELECT last_name FROM employees
WHERE last_name LIKE '__a%'
选择姓名中有字母a和k的员工姓名
SELECT last_name FROM employees
WHERE last_name LIKE '%a%k%' OR last_name LIKE '%k%a%';
SELECT last_name FROM employees
WHERE last_name LIKE '%a%' AND last_name LIKE '%k%';
显示出表 employees 表中 first_name 以 'e'结尾的员工信息
SELECT first_name FROM employees
WHERE first_name LIKE '%e';
显示出表 employees 部门编号在 80-100 之间的姓名、工种
SELECT last_name,job_id FROM employees
WHERE department_id BETWEEN 80 AND 100;
SELECT last_name,job_id FROM employees
WHERE department_id >= 80 AND department_id <= 100;
显示出表 employees 的 manager_id 是 100,101,110 的员工姓名、工资、管理者id
SELECT last_name,salary,manager_id FROM employees
WHERE manager_id IN(100,101,110);
