MySQL--day3--运算符

（以下内容全部来自上述课程）

运算符

1. 算术运算符

一共有：+ - * /或DIV %或MOD

• 在SQL中，+没有连接的作用，就表示加法运算。此时，会将字符串转换为数值（隐式转换）
• null值参与运算，结果就为空
• 除法中，分母如果为0，结果就是null

2. 比较运算符

2.1 = <=> <> != < <= > >=

# =
#        0    1     1      0      1
# 隐式转换 如果转换数值不成功，则看作0
select 1=2, 1!=2, 1='1', 1='a', 0='a'
from dual;

# 字符串比较，就是比较ASCII码
#         1       1         0
select 'a'='a','b'='b', 'a'='b'
from dual;

# 只要有null参加运算，结果就为null
#        null      null
select 1=null, null=null
from dual;


# <=>  安全等于  可以对null进行判断
#        0         1
select 1=null, null=null
from dual;

2.2 关键字类

2.2.1 is null \ is not null \ isnull

# is null \ is not null \ isnull

#练习:查询表中commission_pct为null的数据有哪些 
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);

#练习:查询表中commission_pct不为null的数据有哪些 
SELECT last_name,salary,commission_pct 
FROM employees
WHERE commission_pct IS NOT NULL;
# 或
SELECT last_name,salary,commission_pct 
FROM employees
WHERE commission_pct <=> NULL;

2.2.2 LEAST() 最小 / GREATEST() 最大

# LEAST() 最小 / GREATEST() 最大
#                     b                             t
select least('g', 'b', 't', 'm'), greatest('g', 'b', 't', 'm')
from DUAL;

2.2.3 BETWEEN 条件下界1 AND 条件上界2

#	BETWEEN	条件下界1 AND 条件上界2	(查询条件1和条件2范围内的数据，包含边界)	

#查询工资在6000到8000的员工信息 
SELECT employee_id,last_name,salary 
FROM employees
#where salary between 6000 and 8000; 
WHERE salary>=6000 && salary<= 8000;

#交换6000 和8000之后，查询不到数据 
SELECT employee_id,last name,salary 
FROM employees
WHERE salary BETWEEN 8000 AND 6000;

#查询工资不在6000到8000的员工信息 
SELECT employee_id,last_name,salary 
FROM employees
WHERE salary NOT BETWEEN 6000 AND 8000;
#where salary < 6000 or salary> 8000;

2.2.4 in (set)\ not in (set)

# in (set)\ not in (set)

#练习:查询部门为10,20,30部门的员工信息 
SELECT last name,salary,department_id 
FROM employees
#where department_id = 10 or department_id = 20 or department_id = 30; 
WHERE departmentid IN (10,20,30);

#练习:查询工资不是6000，7000，8000的员工信息 
SELECT last name,salary,department_id 
FROM employees
WHERE salary NOT IN (6000,7000,8000);

2.2.5 LIKE:模糊查询

# LIKE :模糊查询
# %号:代表不确定个数的字符(0个，1个，或多个)

#练习:查询last name中包含字符'a'的员工信息 
SELECT last_name 
FROM employees
WHERE last_name LIKE '%a%';

#练习:查询last_name中以字符'a'开头的员工信息 
SELECT last_name 
FROM employees
WHERE last_name LIKE 'a%';

#练习:查询last_name中包含字符'a'且包含字符'e'的员工信息
#写法1:
SELECT last_name 
FROM employees
WHERE last_name LIKE '%a%' AND last_name LIKE '%e%';
#写法2:
SELECT last_name 
FROM employees
WHERE last name LIKE '%a%e%' OR last name LIKE '%e%a%';

#: _代表一个不确定的字符
#练习:查询第3个字符是'a'的员工信息 
SELECT last_name 
FROM employees
WHERE last_name LIKE '__a%';	

#练习:查询第2个字符是且第3个字符是'a'的员工信息
#需要使用转义字符:\ 
SELECT last_name 
FROM employees
WHERE last_name LIKE '_\_a%'; 

2.2.6 REGEXP \ RLIKE：正则表达式

3. 逻辑运算符

3.1 OR || AND &&

# or	and	
SELECT last_name,salary,department_id 
FROM employees
#where department id = 10 or department id = 20;
#where department id = 10 and department id = 20; 
WHERE department_id =50 AND salary> 6000;

AND优先级比OR高

3.2 NOT ！

# not
SELECT last_name,salary,department_id 
FROM employees
#where salary not between 6000 and 8000; 
#WHERE commission pct IS NOT NULL;
where not commisssion_pct <=> null;

3.3 XOR 逻辑异或

满足两者之一

select last_name, salary, department_id
from employees
where department_id = 50 XOR salary > 6000;

4. 位运算符（了解）

4.1 按位与、或、异或

#       4     13     9 
select 12&5, 12|5, 12^5
from dual;

4.2 按位取反

select 10 & ~1 from DUAL;

4.3 按位左移（*2）右移（/2）

#在一定范围内满足向左移动一位相当于*2
#         8       4
select 4 << 1, 8 >> 1
from DUAL;