#2. 比较运算符
# =, <=>, <> !=, <, <=, >, >=
SELECT 1=2, 1!=2, 1<>2, 1>2, 1<=>2, 1 = ;1;, 1=;a;, 0=;b; #字符串存在隐式转换;如果转换数值不成功;看作0
FROM DUAL;
SELECT ;a;=;a;, ;a;=;b; #存粹是字符串和字符串比较;就是按照ASIC码进行转换
FROM DUAL;
SELECT 1=NULL, NULL=NULL #只要有null参与的话;结果就为null。两边都为null结果依然为null
FROM DUAL;
SELECT last_name,salary,commission_pct
FROM employees
#where salary=6000;
#where commission_pct = null;
WHERE IFNULL(commission_pct,0)=0;
#<=>安全等于;可以对null进行操作;可以对两边的null进行判断。如果两边都为null;则判断null<=>null为1。
SELECT last_name,salary,commission_pct
FROM employees
WHERE commission_pct <=> NULL;
SELECT 3<>2, ;4; <> NULL, ;; != NULL, NULL != NULL # 1, null, null, null
FROM DUAL;
# IS NULL :为空运算;判断值、字符串或者表达式是否为空; SELECT B FROM TABLE WHERE A IS NULL
# IS NOTNULL :不为空运算符;判断值、字符串或者表达式是否不为空; SELECT B FROM TABLE WHERE A IS NOT NULL
# LEAST :最小值运算符;在多值中返回最小值; ; SELECT D FROM TABLE WHERE C LEAST(A,B)
# GREATEST :最大值运算符;在多值中返回最大值; ; SELECT D FROM TABLE WHERE C GREASTEST(A,B)
# BETWEEN AND:两者之间的运算符;判断一个值是否在两个值之间 ; SELECT D FROM TABLE WHERE C BETWEEEN A AND B
# ISNULL :为空运算符;判断一个值、字符串或者表达式 ; SELECT B FROM TABLE WHERE A ISNULL
# IN :属于运算符;判断一个值是否为列表中的任意一个值 ; SELECT D FROM TABLE WHERE C NOT IN (A,B)
# NOT IN :不属运算符; ; SELECT D FROM TABLE WHERE C NOT IN (A,B)
# LIKE :模糊匹配运算符;判断一个值是否符合模糊匹配原则 ; SELECT D FROM TABLE WHERE A LIKE B
# REGEXP :正则表达式运算符;判断一个值是否符合正则表达式的规则;SELECT C FROM TABLE WHERE A REGEXP B
# RLIKE :正则表达式运算符;判断一个值是否符合正则表达式的规则;SELECT C FROM TABLE WHERE A RLIKE B
# IS NULLIS NOT NULL IS NULL
SELECT * FROM employees;
SELECT last_name, salary, commission_pct
FROM employees
WHERE commission_pct IS NULL; #只显示字段commission_pct为null的数据行
SELECT last_name, salary, commission_pct
FROM employees
WHERE commission_pct IS NOT NULL; #只显示字段commission_pct不为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 NOT commission_pct <=> NULL; #只显示字段commission_pct为null的数据行
# 3. 逻辑运算符OR || AND && NOT ! XOR
# OR
SELECT last_name,salary,department_id
FROM employees
WHERE department_id = 10 OR department_id = 20; #实际上是取得并集
# AND
SELECT last_name,salary,department_id
FROM employees
WHERE department_id = 10 AND department_id = 20; #实际上是取得交集
SELECT last_name,salary,department_id
FROM employees
WHERE department_id = 50 AND salary > 6000; #实际上是取得交集
SELECT last_name,salary,department_id
FROM employees
WHERE NOT salary BETWEEN 6000 AND 8000; #实际上是取得交集
# NOT
SELECT last_name,salary,department_id,commission_pct
FROM employees
WHERE commission_pct IS NOT NULL; #
SELECT last_name,salary,department_id,commission_pct
FROM employees
WHERE NOT commission_pct <=> NULL; #
# XOR,异或
SELECT last_name,salary,department_id
FROM employees
WHERE department_id = 50 XOR salary>6000; #满足其中一个条件;但是不满足另外一个条件;就叫做异或
# 注意;AND的优先级高于OR
相关练习
# 1. 选择工资不在5000到12000的员工的姓名和工资
SELECT *
FROM employees;
SELECT first_name, last_name, salary
FROM employees
WHERE salary NOT BETWEEN 5000 AND 12000;
# 2. 选择在20或者50号部门工作的员工姓名和部门号
SELECT first_name, last_name, department_id
FROM employees
WHERE department_id IN (20,50);
SELECT first_name, last_name, department_id
FROM employees
WHERE department_id =20 OR department_id = 50;
# 3. 选择公司没有管理者的员工姓名以及job_id
SELECT first_name, last_name, job_id, manager_id
FROM employees
WHERE manager_id IS NULL;
# 4. 选择公司中有奖金的员工姓名;工资和奖金级别
SELECT first_name, last_name, salary, commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;
# 5. 选择员工姓名的第三个字母是a的员工姓名
SELECT *
FROM employees;
SELECT first_name, last_name
FROM employees
WHERE first_name LIKE ;__a%;
# 6. 选择姓名中有字母a和k的员工姓名
SELECT first_name, last_name
FROM employees
WHERE first_name LIKE %a% AND first_name LIKE %k%;
# 7. 显示出表employees表中first_name 以 ;e; 结尾的员工信息
SELECT first_name, last_name
FROM employees
WHERE first_name LIKE %e;;
# 8. 显示出表employees部门编号在80~100之间的姓名、工种
SELECT *
FROM employees;
SELECT first_name, last_name, job_id,department_id
FROM employees
WHERE department_id BETWEEN 80 AND 100;
# 9. 显示出表employees的manager_id是100;101;110的员工姓名;工资;管理者的id
SELECT first_name, last_name, salary, manager_id
FROM employees
WHERE manager_id IN (100,101,110);
