【MySQL】各种运算符介绍

算术运算符

DESCRIBE departments;
# 算术运算符
# + - / * div mod(%)
SELECT 100 + '1'# java中结果是1001，是连接的作用
# 在SQL中，会将字符串转换为数值（隐式转换）
FROM DUAL;
SELECT 100 + 'a'# 此时'a'看做0来处理
FROM DUAL;
SELECT 100 + NULL # NULL值参与运算，结果为NULL
FROM DUAL;
SELECT 100/2 # 只要是除法，都会保留小数
FROM DUAL;
SELECT 100 DIV 0 # 分母如果为0.结果是NULL
FROM DUAL;

# 取模运算,结果的符号只与被模数的符号有关系，与模数符号无关系
SELECT 12 % 3,12 % 5,12 MOD -5
,-12 MOD 5,-12 MOD -5
FROM DUAL;

SELECT employee_id,last_name,salary
FROM employees
WHERE employee_id % 2 = 0;

比较运算符

比较运算符

# 比较运算符
# 结果为真返回1，为假返回0，其他情况返回NULL
# 1. = 等于

SELECT 1 = 2 ,1 != 2,1 = 'a'
#如果字符串不能隐式转换,就把他看成0
FROM DUAL;
SELECT 'a' = 'a','a' = 'b'
# 纯粹进行字符串的比较,则比较的是ASCLL码
FROM DUAL;
SELECT 1 = NULL
# 只要有NULL参与判断,结果就为NULL!!!!
FROM DUAL;

# <=>安全等于,唯一区别是可以对NULL进行判断
# 两个操作数都是NULL,结果是1;一个操作数是NULL,结果是0
# 练习:查询表中commission_pct为NULL的数据
SELECT commission_pct,employee_id
FROM employees
WHERE commission_pct <=> NULL;

其他表达式(关于NULL)

#安全等于写法
SELECT commission_pct,employee_id
FROM employees
WHERE commission_pct <=> NULL;

SELECT commission_pct,employee_id
FROM employees
WHERE NOT commission_pct <=> NULL;#把结果反过来了

# ISNULL // IS NOT NULL 
SELECT commission_pct,employee_id
FROM employees
WHERE commission_pct IS NULL;

SELECT commission_pct,employee_id
FROM employees
WHERE commission_pct IS NOT NULL;

# 函数写法
SELECT commission_pct,employee_id
FROM employees
WHERE ISNULL(commission_pct) ;

LEAST() \ GREATEST

SELECT LEAST('g','b','t','m'),GREATEST ('g','b','t','m')
FROM DUAL;

between and:连续性查找

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 BETWEEN 8000 AND 6000;
# 交换6000和8000之后,查询不到任何数据

# 查询不在6000和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 NOT BETWEEN 6000 AND 8000;

in (set)\ not in (set) :离散型查找

# 第一种写法
SELECT last_name,salary,department_id
FROM employees
WHERE department_id = 10 OR department_id = 20 OR department_id = 30;
# 第二种写法
SELECT last_name,salary,department_id
FROM employees
WHERE department_id IN (10,20,30);
# not in
SELECT last_name,salary,department_id
FROM employees
WHERE salary NOT IN (6000,7000,8000);

like 模糊查询

# 查询name中包含'a'的员工信息
# %代表不确定个数的字符(0个,1个或者多个)
SELECT last_name,salary,department_id
FROM employees
WHERE last_name LIKE '%a%';

# 查询name中以'a'开头的员工信息
SELECT last_name,salary,department_id
FROM employees
WHERE last_name LIKE 'a%';

# 查询name中包含'a'且包含'e'的员工信息
SELECT last_name,salary,department_id
FROM employees
WHERE last_name LIKE '%a%' AND last_name LIKE '%e%';
#第二种写法
SELECT last_name,salary,department_id
FROM employees
WHERE last_name LIKE '%a%e%' OR last_name LIKE '%e%a%';

# 查询name中第二个字符是'a'的员工信息
# _ 代表一个不确定的字符
SELECT last_name,salary,department_id
FROM employees
WHERE last_name LIKE '_a%';

正则表达式

# regexp 
# ^ 匹配以该字符后面的字符开头的字符串
# $ 匹配以该字符前面的字符结尾的字符串

逻辑运算符

# XOR
# 两个不同时满足的,为真
SELECT employee_id,last_name,salary
FROM employees
WHERE department_id = 50 XOR salary > 6000;

注意优先级,and的优先级大于or的优先级

位运算符

在一定范围内,满足,每向左移动一位,相当于乘以2,右移一位,相当于除以2