2.运算符

# 运算符
# 1. 算数运算符： + - * / div % mod
SELECT 100,100+0,100-0,100+50,100+50-30,100+35.5,100-35.5 
FROM DUAL;

SELECT 100 + '1'	#结果是101  + 只表示加法运算
FROM DUAL;

SELECT 100 + 'a'  #此时将'a'看作0处理
FROM DUAL;

SELECT 100 + NULL  #NULL与任何值参与运算结果为NULL
FROM DUAL;

SELECT 100,100 * 1, 100 * 1.0,100 / 1.0, 100 / 3,100 div 0,100 / 0; #除数为0 结果为NULL

# 取模运算  a % b 结果正负与a相同
SELECT 12 % 3, 12 % 5, 12 MOD -5, -12 MOD 5, -12 %-5

# 2. 比较运算符	1为true,0为false
#  =、<=>、!=、<>、<、>、<=、>=

SELECT 1 = 2, 1 != 2 ,1 = '1',1 = 'a', 0='a' #字符串存在，隐式转换，不存在默认为0
FROM DUAL;

SELECT 'a' = 'a','a' = 'b'#两边都是字符串，不会隐式转换
FROM DUAl;

SELECT 1 = NULL,NULL = NULL #NULL参与运算，结果仍未NULL
FROM DUAL;

SELECT last_name,salary,commission_pct
FROM employees
WHERE salary = 6000;

# <=> 安全等于 有一个是NULL,等于0 都是NULL，等于1
SELECT 1 <=> NULL,NULL <=> NULL 

#查询commission_pct为NULL的情况
SELECT last_name,salary,commission_pct
FROM employees
WHERE commission_pct <=> NULL;

# 3. 关键字
# 3.1 IS NULL \ IS NOT NULL\ ISNULL()
SELECT last_name,salary,commission_pct
FROM employees
WHERE commission_pct IS NULL;

SELECT last_name,salary,commission_pct
FROM employees
WHERE ISNULL(commission_pct);

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

SELECT last_name,salary,commission_pct
FROM employees
WHERE NOT commission_pct <=> NULL;

# 3.2 LEAST() \ GREATEST() 最小 \ 最大

SELECT LEAST(1,2,3,4,5),GREATEST(1,2,3,4,5)
FROM DUAl;

SELECT LEAST(first_name,last_name)
FROM employees;

# 3.3 BETWEEN ... AND  左闭右闭
#工资在6000-8000
SELECT employee_id,last_name,salary
FROM employees
WHERE salary BETWEEN 6000 AND 8000;

SELECT employee_id,last_name,salary
FROM employees
WHERE salary >= 6000 && salary <= 8000;

SELECT employee_id,last_name,salary
FROM employees
WHERE salary >= 6000 AND salary <= 8000;

#工资不在6000-8000
SELECT employee_id,last_name,salary
FROM employees
WHERE salary NOT BETWEEN 6000 AND 8000;

SELECT employee_id,last_name,salary
FROM employees
WHERE salary < 6000 OR salary > 8000;

SELECT employee_id,last_name,salary
FROM employees
WHERE salary < 6000 || salary > 8000;

# 3.4 IN \ NOT IN
SELECT employee_id,last_name,department_id
FROM employees
WHERE department_id in (10,20,30);

SELECT employee_id,last_name,department_id
FROM employees
WHERE department_id = 10 OR department_id = 20 OR department_id = 30;

SELECT employee_id,last_name,department_id
FROM employees
WHERE department_id NOT in (10,20,30);

SELECT employee_id,last_name,department_id
FROM employees
WHERE department_id != 10 OR department_id != 20 OR department_id != 30;

# 3.5 LIKE:模糊查询
# %：代表任意多个字符 包括0
# _: 代表一个字符
# 如果所查字段中包含%或_，需要使用转义字符 \
SELECT employee_id,last_name 
FROM employees
WHERE last_name like '%a%';

SELECT employee_id,last_name 
FROM employees
WHERE last_name like '%a%e%' OR last_name like '%e%a%';

SELECT employee_id,last_name 
FROM employees
WHERE last_name like '_a%';

#第二个字符是 _,第三个字符是_
SELECT employee_id,last_name 
FROM employees
WHERE last_name like '_\_a%';

#指定字符作为转义字符 ESCAPE
SELECT employee_id,last_name 
FROM employees
WHERE last_name like '_$_a%' ESCAPE '$';

# 4.逻辑运算符
#OR || AND && NOT ！ XOR(异或)不同为true
SELECT employee_id,last_name,department_id
FROM employees
WHERE department_id = 10 OR department_id = 20; 

SELECT employee_id,last_name,department_id
FROM employees
WHERE department_id = 50 AND salary > 6000; 

SELECT employee_id,last_name,salary
FROM employees
WHERE salary NOT BETWEEN 6000 AND 8000;

SELECT employee_id,salary,department_id
FROM employees
WHERE department_id = 50 XOR salary > 6000;