#3.逻辑运算符:OR || AND && NOT ! XOR
#or and
SELECT last_name,salary,department_id
FROM employees
#where department_id = 10 or department_id = 20;
#WHERE department_id = 50 and department_id = 20;
WHERE department_id = 50 AND salary>6000;
#not
SELECT last_name,salary,department_id,commission_pct
FROM employees
#where salary not between 6000 and 8000;
#where not commission_pct is not null;
WHERE NOT commission_pct <=> NULL;
#xor:逻辑异或
SELECT last_name,salary,department_id
FROM employees
WHERE department_id = 50 XOR salary>6000;
#or 和 and 可以一起使用，但是要注意优先级，且and的优先级高与or
# 4.位运算符 & | ^ ~ >> <<
#7& ^ |
SELECT 12 & 5,12 | 5,12 ^ 5
FROM DUAL;
# ~:取反
SELECT 10&~1
FROM DUAL;
#>> <<: 在于一定范围内满足：左移一位乘2，右移一位除2
SELECT 4 <<1, 8>>1
FROM DUAL;
#章节练习：
# 1.选择工资不在5000到12000的员工的姓名和工资
SELECT last_name,salary
FROM employees
WHERE salary < 5000 OR salary > 8000;
# 2.选择在20或50号部门工作的员工姓名和部门号
SELECT last_name,salary,department_id
FROM employees
WHERE department_id = 20 OR department_id = 50;
# 3.选择公司中没有管理者的员工姓名及job_id
SELECT last_name,job_id
FROM employees
WHERE manager_id IS NULL;
# 4.选择公司中有奖金的员工姓名，工资和奖金级别
SELECT last_name,salary,commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;
SELECT *
FROM employees;
# 5.选择员工姓名的第三个字母是a的员工姓名
SELECT last_name
FROM employees
WHERE last_name LIKE ‘__a%’;
# 6.选择姓名中有字母a和k的员工姓名
SELECT last_name
FROM employees
WHERE last_name LIKE ‘%a%k%’ OR last_name LIKE ‘%k%a%’;
# 7.显示出表 employees 表中 first_name 以 ‘e’结尾的员工信息
SELECT last_name
FROM employees
WHERE last_name LIKE ‘%e’
# 8.显示出表 employees 部门编号在 80-100 之间的姓名、工种
SELECT last_name,job_id
FROM employees
WHERE department_id BETWEEN 80 AND 100;
# 9.显示出表 employees 的 manager_id 是 100,101,110 的员工姓名、工资、管理者id
SELECT last_name, job_id,manager_id
FROM employees
WHERE manager_id IN(100,101,110);
请在这里编辑您的隐藏内容