数据库—逻辑运算符

 #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);

请在这里编辑您的隐藏内容

给TA打赏
共{{data.count}}人
人已打赏
知识碎片

数据库—查询操作

2021-11-28 23:22:08

知识碎片

HTML5中有语义的布局标签

2021-11-29 20:41:02

0 条回复 A文章作者 M管理员
    暂无讨论,说说你的看法吧