MySQL基础-详解运算符

MySQL基础-详解运算符

一、算术运算符

算术运算符主要用于数学运算，其可以连接运算符前后的两个数值或表达式，对数值或表达式进行加（+）、减（-）、乘（*）、除（/）和取模（%）运算。

1.加法与减法运算符

image-20231009172536362

总结：整数之间的加减运算结果还是整数，一旦出现浮点数结果就为浮点数，如果非数值类型时先尝试转为数值类型，转换失败按照0计算，NULL参与运算的结果都为NULL。（字符串转换为数值类型的规则很宽松，遇到非数值字符前的都是有效字符，而且参与运算后的结果比较智能，能转换为整数会尽量转换为整数如1+’2.0’->3）

2.乘法与除法运算符

image-20231009173122602

总结：乘除运算与加减运算类似，不过需要注意整数之间无论是否能除尽结果都是浮点数并保留小数点后四位，除零为NULL，NULL参与运算的结果都为NULL。

3.取模运算符

image-20231009190552820

总结：取模运算比较好理解，需要额外注意的就是如果模零结果为NULL，NULL参与运算的结果都为NULL。

二、比较运算符

比较运算符用来对表达式左边的操作数和右边的操作数进行比较，比较的结果为真则返回1，比较的结果为假则返回0，其他情况则返回NULL。比较运算符经常被用来作为SELECT查询语句的条件来使用，返回符合条件的结果记录。

1.等于运算符

等于运算符（=）判断等号两边的值、字符串或表达式是否相等，如果相等则返回1，不相等则返回0。

image-20231009193335685

总结：参与等于运算的如果是整数与字符型混合，需要先将字符型转为整数，转换不了的值为0，字符型数据之间的等于运算需要结合字段的比较规则，例如比较规则为utf8mb3_general_ci表明忽略大小写。

2.安全等于运算符

使用安全等于运算符时，两边的操作数的值都为NULL时，返回的结果为1而不是NULL，一边的操作数的值为NULL时，返回的结果是0而不是NULL，其他返回结果与等于运算符相同。

image-20231009194015739

3.不等于运算符

与等于运算符相反，可以参考等于运算符的总结。

image-20231009194400050

4.空运算符

空运算符（IS NULL或者ISNULL）判断一个值是否为NULL，如果为NULL则返回1，否则返回0。

image-20231009194813784

1
2
3
4
5
#查询commission_pct等于NULL。比较如下的四种写法 
SELECT employee_id,commission_pct FROM employees WHERE commission_pct IS NULL; 
SELECT employee_id,commission_pct FROM employees WHERE commission_pct <=> NULL; 
SELECT employee_id,commission_pct FROM employees WHERE ISNULL(commission_pct); 
SELECT employee_id,commission_pct FROM employees WHERE commission_pct = NULL;

前三个SQL语句都能正常工作，第四个SQL语句使用等于运算符比较NULL值，返回的结果都会为NULL，因此一条记录也查不出来：

image-20231009195214132

5.非空运算符

非空运算符（IS NOT NULL）判断一个值是否不为NULL，如果不为NULL则返回1，否则返回0。

image-20231009195328480

6.最小值运算符

image-20231009195645772

总结：当参数是整数或者浮点数时，LEAST将返回其中最小的值；当参数为字符串时，返回字母表中顺序最靠前的字符；当比较值列表中有NULL时，不能判断大小，返回值为NULL。

7.最大值运算符

image-20231009195908851

总结：当参数中是整数或者浮点数时，GREATEST将返回其中最大的值；当参数为字符串时，返回字母表中顺序最靠后的字符；当比较值列表中有NULL时，不能判断大小，返回值为NULL。

8.BETWEEN AND运算符

image-20231009200115235

9.IN运算符

image-20231009200645265

总结：IN运算符用于判断给定的值是否是IN列表中的一个值，如果是则返回1，否则返回0。如果给定的值为NULL，或者IN列表中存在NULL(IN列表存在指定数据的除外)，则结果为NULL。

10.NOT IN运算符

image-20231009200955594

11.LIKE运算符

LIKE运算符主要用来匹配字符串，通常用于模糊匹配，如果满足条件则返回1，否则返回0。如果给定的值或者匹配条件为NULL，则返回结果为NULL。

LIKE运算符通常使用如下通配符：

1
2
“%”：匹配0个或多个字符。 
“_”：只能匹配一个字符。

image-20231009201237798

12.REGEXP运算符

REGEXP运算符用来匹配字符串，语法格式为：expr REGEXP 匹配条件。

1
2
3
4
5
6
7
（1）‘^’匹配以该字符后面的字符开头的字符串。 
（2）‘$’匹配以该字符前面的字符结尾的字符串。 
（3）‘.’匹配任何一个单字符。 
（4）“[...]”匹配在方括号内的任何字符。例如，“[abc]”匹配“a”或“b”或“c”。为了命名字符的范围，使用一个‘-’。“[a-z]”匹配任何字母，而“[0-9]”匹配任何数字。 
（5）‘*’匹配零个或多个在它前面的字符。例如，“x*”匹配任何数量的‘x’字符，“[0-9]*”匹配任何数量的数字，而“*”匹配任何数量的任何字符。
（6）‘+’匹配一个或多个在它前面的字符。例如，“a+”匹配大于等于1个‘a’字符。
（7）‘{n，m}’匹配前面的字符至少n次，至多m次。

image-20231009202431161

正则表达式通常被用来检索或替换那些符合某个模式的文本内容，根据指定的匹配模式匹配文本中符合 要求的特殊字符串。例如，从一个文本文件中提取电话号码，查找一篇文章中重复的单词或者替换用户 输入的某些敏感词语等，这些地方都可以使用正则表达式。正则表达式强大而且灵活，可以应用于非常复杂的查询。

三、逻辑运算符

逻辑运算符主要用来判断表达式的真假，在MySQL中，逻辑运算符的返回结果为1、0或者NULL。

1.逻辑非运算符

逻辑非（NOT或!）运算符表示当给定的值为0时返回1；当给定的值为非0值时返回0；当给定的值为NULL时，返回NULL。

image-20231009203102186

2.逻辑与运算符

逻辑与（AND或&&）运算符是当给定的所有值均为非0值，并且都不为NULL时返回1；当给定的一个值或者多个值为0时则返回0；否则返回NULL。

image-20231009203259685

3.逻辑或运算符

逻辑或（OR或||）运算符是当给定的值都不为NULL，并且任何一个值为非0值时返回1，否则返回0；当一个值为NULL，并且另一个值为非0值时，返回1，否则返回NULL；当两个值都为NULL时，返回NULL。

image-20231009203448438

1
2
3
4
#查询基本薪资不在9000-12000之间的员工编号和基本薪资 
SELECT employee_id,salary FROM employees WHERE NOT (salary >= 9000 AND salary <= 12000);
SELECT employee_id,salary FROM employees WHERE salary <9000 OR salary > 12000;
SELECT employee_id,salary FROM employees WHERE salary NOT BETWEEN 9000 AND 12000;

OR可以和AND一起使用，但是在使用时要注意两者的优先级，由于AND的优先级高于OR，因此先 对AND两边的操作数进行操作，再与OR中的操作数结合。

4.逻辑异或运算符

逻辑异或（XOR）运算符是当给定的值中任意一个值为NULL时，则返回NULL；如果两个非NULL的值都是0或者都不等于0时，则返回0；如果一个值为0，另一个值不为0时，则返回1。

image-20231009204818709

四、课后练习

1.选择工资不在5000到12000的员工的姓名和工资
1
2
3
4
5
6
7
SELECT last_name, salary
FROM employees
WHERE salary < 5000 OR salary > 12000;

SELECT last_name, salary
FROM employees
WHERE salary NOT BETWEEN 5000 AND 12000;
2.选择在20或50号部门工作的员工姓名和部门号
1
2
3
4
5
6
7
SELECT last_name, department_id
FROM employees
WHERE department_id = 20 OR department_id = 50;

SELECT last_name, department_id
FROM employees
WHERE department_id IN(20, 50);
3.选择公司中没有管理者的员工姓名及job_id
1
2
3
SELECT last_name, job_id
FROM employees
WHERE manager_id IS NULL;
4.选择公司中有奖金的员工姓名，工资和奖金级别
1
2
3
SELECT last_name, salary, commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;
5.选员工姓名的第三个字母是a的员工姓名
1
2
3
SELECT last_name
FROM employees
WHERE last_name LIKE '__a%';
6.选择姓名中有字母a和k的员工姓名
1
2
3
4
5
6
7
SELECT last_name
FROM employees
WHERE last_name LIKE '%a%k%' OR last_name LIKE '%k%a%';

SELECT last_name 
FROM employees 
WHERE last_name REGEXP 'a' AND last_name REGEXP 'k';
7.显示出表 employees 表中 first_name 以 ‘e’结尾的员工信息
1
2
3
4
5
6
7
SELECT employee_id,first_name,last_name
FROM employees
WHERE first_name LIKE '%e';

SELECT employee_id,first_name,last_name
FROM employees
WHERE first_name REGEXP 'e$';