运算符
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'
FROM DUAL;
+ 只是加号运算，此时是将字符串转换成数值（隐形转换）
1.1初始将a当作0来处理
SELECT 100 + 'a' FROM DUAL;
1.2取模运算： % mod
特点：余数的(+ -)号只与被余数有关
SELECT 12 % 3,12 % 5,-12 % 5,-12 %-5
FROM DUAL;
2.比较运算符： = <=>(安全对于) / <>和!=(不等于) > >= < <=
2.1 = 的使用
字符串存在隐形转换，但不能转换则看作0
SELECT 1 = 2,1 != 2,1 = '1',1 ='a',0 = 'a'
FROM DUAL;
比较中只要存在NULL结果都为NULL
SELECT 1 = NULL, NULL = 1,NULL = NULL
FROM DUAL;
当比较时都为字符串则当作字符串比较
SELECT 'a' = 'a','ab' = 'ab','a' = 'b','ac' = 'ab'
FROM DUAL;
2.2 <=>的使用:为NULL而生
SELECT 1 <=> NULL, NULL <=> 1,NULL <=> NULL #比较中全部为NULL结果1 FROM DUAL;
2
2.1 IS NULL(是NULL) \ IS NOT NULL(不是NULL) \ISNULL(是NULL)
案例：查询表中commission_pct为NULL的数据
SELECT last_name,commssion_pct
FROM employees
WHERE commission_pct IS NULL;
或
SELECT last_name,commission_pct
FROM employees
WHERE ISNULL(commission_pct);
案例：查询表中commission_pct为NULL的数据
SELECT last_name,commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;
或
SELECT last_name,commission_pct
FROM employees
WHERE NOT commission_pct <=> NULL;
2.2 LEAST()(求最小的值) \ GREATEST()(求最大的值)
SELECT LEAST('a','b','c','d'),GREATEST('a','b','c','d')
FROM DUAL;
SELECT LEAST(first_name,last_name),first_name,last_name
FROM employees
2.3BETWEEN 条件下界1 AND 条件上界2 （查询条件1到条件2之间的数据，包括边界）
#案例：查询工资在6000到8000的员工信息
SELECT employee_id,salary
FROM employees
WHERE salary BETWEEN 6000 AND 8000;
2.4 IN(set) \ NOT IN(set) 查询的是离散的数据
案例：查询部门为10，20，30的员工
SELECT last_name,department_id
FROM employees
#where department_id = 10 or department_id = 20 or department_id = 30;
WHERE department_id IN(10,20,30);
案例：查询部门不是10，20，30的员工
SELECT last_name,department_id
FROM employees
WHERE department_id NOT IN(10,20,30);
2.5 LIKE:模糊查询
%：代表不确定个数的字符（0个，1个，或多个）
案例：查询last_name中包含'a'的员工信息
SELECT last_name
FROM employees
WHERE last_name LIKE '%a%';
案例：查询last_name中包含'a'和包含'e'的员工信息
SELECT last_name
FROM employees
WHERE last_name LIKE '%a%' AND last_name LIKE '%e%';
或
SELECT last_name
FROM employees
WHERE last_name LIKE '%a%e%' OR last_name LIKE '%e%a%';
LIke中_：代表一个不确定的字符
案例：查询last_name中包含'a'为第二个字符的员工信息
SELECT last_name
FROM employees
WHERE last_name LIKE '_a%';
案例：查询last_name中包含_为第二个字符且'a'为第三个字符的员工信息
引入转义字符 / 或是 ESCAPE 规定一个字符
SELECT last_name
FROM employees
#WHERE last_name LIKE '__a%';
WHERE last_name LIKE '__a%' ESCAPE '';
2.6REGEXP \RLIKE:正则表达式
'^%'是以什么开头，'%$'是以什么结尾，REGEXP '%' 包含什么
SELECT 'shkstart' REGEXP '^s', 'shkstart' REGEXP 't$', 'shkstart' REGEXP 'hk'
FROM DUAL;
“.” 在REGEXP中代表一个字符
SELECT 'shkstart' REGEXP 'st.rt'
FROM DUAL;
3.逻辑运算符： OR ||(或) \ AND &&(且) \NOT !(非) \XOR(异或)
OR AND and的优先级高与or
SELECT employee_id,salary,department_id
FROM employees
#WHERE department_id = 50 and salary > 6000;
WHERE department_id = 50 OR salary > 6000;
#NOT的运用
SELECT employee_id,salary,department_id,commission_pct
FROM employees
#where commission_pct is not null;
WHERE NOT commission_pct <=> NULL;
#XOR:只满足一边的条件，追求的是"异"
SELECT employee_id,salary,department_id
FROM employees
WHERE department_id = 50 XOR salary > 6000;
4.位运算符：& | ^ ~ >> << 都是二进制运算
SELECT 12 & 5,12 | 5,12 ^ 5
FROM DUAL;
~:是指将0变为1，1变更为0
SELECT 10 & ~1
FROM DUAL;
在一定范围内：向左移动1位，相当于乘以2；向右移动1位，相当于除以2
SELECT 4 << 1,8 >> 1
FROM DUAL;
