MySQL学习笔记(运算符)20221013

mysql学习笔记之运算符20221013

• 基础语法
• 相关练习

基础语法

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