算术运算符主要用于数学运算,其可以连接运算符前后的两个数值或表达式,对数值或表达式进行加(+)、减(-)、乘(*)、除(/)和取模(%)运算。
- 一个整数类型的值对整数进行加法和减法操作,结果还是一个整数;
- 一个整数类型的值对浮点数进行加法和减法操作,结果是一个浮点数;
- 加法和减法的优先级相同,进行先加后减操作与进行先减后加操作的结果是一样的;
- 在Java中,+的左右两边如果有字符串,那么表示字符串的拼接。但是在MySQL中+只表示数值相加。如果遇到非数值类型,先尝试转成数值,如果转失败,就按0计算。(补充:MySQL中字符串拼接要使用字符串函数CONCAT()实现)
#计算出员工的年基本工资
SELECT employee_id,salary,salary * 12 annual_sal
FROM employees;
由运算结果可以得出如下结论:
- 一个数乘以整数1和除以整数1后仍得原数;
- 一个数乘以浮点数1和除以浮点数1后变成浮点数,数值与原数相等;
- 一个数除以整数后,不管是否能除尽,结果都为一个浮点数;
- 一个数除以另一个数,除不尽时,结果为一个浮点数,并保留到小数点后4位;
- 乘法和除法的优先级相同,进行先乘后除操作与先除后乘操作,得出的结果相同。
- 在数学运算中,0不能用作除数,在MySQL中,一个数除以0为NULL。
#筛选出employee_id是偶数的员工
SELECT * FROM employees
WHERE employee_id MOD 2 = 0;
可以看到,100对3求模后的结果为3,对5求模后的结果为0。比较运算符用来对表达式左边的操作数和右边的操作数进行比较,比较的结果为真则返回1,比较的结果为假则返回0,其他情况则返回NULL。比较运算符经常被用来作为SELECT查询语句的条件来使用,返回符合条件的结果记录。
#查询salary=10000,注意在Java中比较是==
SELECT employee_id,salary FROM employees WHERE salary = 10000;
#查询commission_pct等于0.40
SELECT employee_id,commission_pct FROM employees WHERE commission_pct = 0.40;
SELECT employee_id,commission_pct FROM employees WHERE commission_pct <=> 0.40;
#如果把0.40改成 NULL 呢?
可以看到,使用安全等于运算符时,两边的操作数的值都为NULL时,返回的结果为1而不是NULL,其他返回结果与等于运算符相同。
#查询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;
#例子:
SELECT last_name, manager_id
FROM employees
WHERE manager_id IS NULL;
#查询commission_pct不等于NULL
SELECT employee_id,commission_pct FROM employees WHERE commission_pct IS NOT NULL; SELECT employee_id,commission_pct FROM employees WHERE NOT commission_pct <=> NULL; SELECT employee_id,commission_pct FROM employees WHERE NOT ISNULL(commission_pct);
由结果可以看到,当参数是整数或者浮点数时,LEAST将返回其中最小的值;当参数为字符串时,返回字母表中顺序最靠前的字符;当比较值列表中有NULL时,不能判断大小,返回值为NULL。
SELECT last_name, salary
FROM employees
WHERE salary BETWEEN 2500 AND 3500;
SELECT employee_id, last_name, salary, manager_id
FROM employees
WHERE manager_id IN (100, 101, 201);
LIKE运算符通常使用如下通配符:
- “%”:匹配0个或多个字符。
- “_”:只能匹配一个字符。
SQL语句示例如下:
```sql
SELECT first_name FROM employees WHERE first_name LIKE 'S%';
SELECT last_name FROM employees WHERE last_name LIKE '_o%';
```
**ESCAPE**
- 回避特殊符号的:**使用转义符**。例如:将[%]转为[$%]、[]转为[$],然后再加上[ESCAPE‘$’]即可。
```sql
SELECT job_id
FROM jobs
WHERE job_id LIKE ‘IT\_%‘;
```
- 如果使用\表示转义,要省略ESCAPE。如果不是\,则要加上ESCAPE。
```sql
SELECT job_id
FROM jobs
WHERE job_id LIKE ‘IT$_%‘ escape ‘$‘;
```
REGEXP运算符用来匹配字符串,语法格式为: expr REGEXP 匹配条件 。如果expr满足匹配条件,返回1;如果不满足,则返回0。若expr或匹配条件任意一个为NULL,则结果为NULL。
(1)‘^’匹配以该字符后面的字符开头的字符串。
(2)‘$’匹配以该字符前面的字符结尾的字符串。
(3)‘.’匹配任何一个单字符。
(4)“[...]”匹配在方括号内的任何字符。例如,“[abc]”匹配“a”或“b”或“c”。为了命名字符的范围,使用一 个‘-’。“[a-z]”匹配任何字母,而“[0-9]”匹配任何数字。
(5)‘*’匹配零个或多个在它前面的字符。例如,“x*”匹配任何数量的‘x’字符,“[0-9]*”匹配任何数量的数字, 而“*”匹配任何数量的任何字符。
逻辑运算符主要用来判断表达式的真假,在MySQL中,逻辑运算符的返回结果为1、0或者NULL。 MySQL中支持4种逻辑运算符如下:
SELECT last_name, job_id
FROM employees
WHERE job_id NOT IN ('IT_PROG', 'ST_CLERK', 'SA_REP');
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary >=10000 AND job_id LIKE '%MAN%';
#查询基本薪资不在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;
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary >= 10000
OR job_id LIKE '%MAN%';
注意:
OR可以和AND一起使用,但是在使用时要注意两者的优先级,由于AND的优先级高于OR,因此先对AND两边的操作数进行操作,再与OR中的操作数结合
select last_name,department_id,salary
from employees
where department_id in (10,20) XOR salary > 8000;
位运算符是在二进制数上进行计算的运算符。位运算符会先将操作数变成二进制数,然后进行位运算,最后将计算结果从二进制变回十进制数。
MySQL支持的位运算符如下:
1的二进制数为0000 0001,左移两位为0000 0100,对应的十进制数为4。4的二进制数为0000 0100,左移两位为0001 0000,对应的十进制数为16。
正则表达式通常被用来检索或替换那些符合某个模式的文本内容,根据指定的匹配模式匹配文本中符合要求的特殊字符串。例如,从一个文本文件中提取电话号码,查找一篇文章中重复的单词或者替换用户输入的某些敏感词语等,这些地方都可以使用正则表达式。正则表达式强大而且灵活,可以应用于非常复杂的查询。MySQL中使用REGEXP关键字指定正则表达式的字符匹配模式。下表列出了REGEXP操作符中常用字符匹配列表。
在fruits表中,查询f_name字段以字母‘b’开头的记录,SQL语句如下:
mysql> SELECT * FROM fruits WHERE f_name REGEXP '^b';
在fruits表中,查找f_name字段中包含字母‘o’或者‘t’的记录,SQL语句如下:
mysql> SELECT * FROM fruits WHERE f_name REGEXP '[ot]';
在fruits表中,查询s_id字段中包含4、5或者6的记录,SQL语句如下:
mysql> SELECT * FROM fruits WHERE s_id REGEXP '[456]';
mysql> SELECT * FROM fruits WHERE f_id REGEXP '[^a-e1-2]';
在fruits表中,查询f_name字段值出现字母‘x’至少2次的记录,SQL语句如下:
mysql> SELECT * FROM fruits WHERE f_name REGEXP 'x{2,}';
在fruits表中,查询f_name字段值出现字符串“ba”最少1次、最多3次的记录,SQL语句如下:
mysql> SELECT * FROM fruits WHERE f_name REGEXP 'ba{1,3}';