什么语言都会涉及到运算的,SQL也不例外,而其运算符,又可以分以下几种:算术运算符,比较运算符,逻辑运算符,位运算符。
这个运算符主要用于数学运算,这个也是常见的一些加减乘除等运算
运算符 | 作用 | 例子 |
---|---|---|
+ | 加法运算符,计算两个或者表达式的和 | SELECT A+B |
- | 减法运算符,计算两个或者表达式的差 | SELECT A-B |
* | 乘法运算符,计算两个或者表达式的乘积 | SELECT A*B |
/或者DIV | 除法运算符,计算两个或者表达式的商 | SELECT A/B 或 SELECT A DIV B |
% 或 MOD | 求余又称求模运算符,计算两个或者表达式的余数 | SELECT A%B 或 SELECT A MOD B |
这个相对来说很容易理解,直接用伪表进行演示。
SELECT 7+3,7-3,7*3,7/3,7 DIV 3, 7%3,7 MOD 3,7+3*3, 7+7%3 FROM DUAL;
算是运算符其实又会涉及到一个隐式转换
SELECT 100+'1',100+'a',100+NULL,100+'',100+'2021-12-16',100/'a',100+'aa23' FROM DUAL;
这个可以看出如果使用算术运算符,其如果后面各自的其它格式可以转换成数字的回转成数组,无法转换的直接转换位0,当然null除外。同样如果字符串中数字和非数字组合的,如果数字在前直接截取转换,如果不是数字在前就直接转换为0;
补充: 其实=
还可以作为赋值符存在,后面聊,先记住这个即可。
比较运算符也就是名字的意思,就是比较这个运算符前后数据的,比较结果为真返回1,比较结果为假则返回0,其它情况则返回NULL;而比较运算符经常作为SELECT 语句中的条件来使用,返回符合条件的结果。也就是说适合放在WHERE后面。
运算符 | 作用 | 例子 |
---|---|---|
= | 等于运算符,判断两个值,字符串或表达式是否相等 | WHERE A=B |
<=> | 安全等于运算符,安全的判断两个值,字符串或表达式是否相等,和 =意义几乎一样,但是其可以判断是否等于 null | WHERE A<=>B |
<>或!= | 不等于运算符,判断两个值,字符串或表达式是否不相等相等 | WHERE A<>B 或 WHERE A!=B |
< | 小于运算符,前面的值,字符串或表达式是否小于后面的值,字符串或表达式。 | WHERE A<B |
<= | 小于等于运算符(记住=必须在后面不然运行报错),前面的值,字符串或表达式是否小于等于后面的值,字符串或表达式。 | WHERE A<=B |
> | 大于运算符,前面的值,字符串或表达式是否小于后面的值,字符串或表达式。 | WHERE A>B |
>= | 大于等于运算符(记住=必须在后面不然运行报错),前面的值,字符串或表达式是否大于等于后面的值,字符串或表达式。 | WHERE A>=B |
不过为了证明一些特种,暂时把数学运算符放在非条件语句中:
列举下面的例子。
SELECT 1=1,1="1",1= NULL,1="a","AB">"AC",1<=>1,NULL<=>NULL,NULL=NULL FROM DUAL;
这个时候可以看出等号的一些特点。
ANSI
编码,这个有点像是java中String判断大小的样子,也是从左向右依次对比,如果可以判断出结果就不会在判断后面的字母。现在用具体的表演示题作为条件的结果。
先看表内信息:
SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno FROM test.emp WHERE mgr='7698';
SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno FROM test.emp WHERE mgr=NULL;
SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno FROM test.emp WHERE mgr<=>'7698';
这个可以看出如果是非NULL的时候,<=>
和=
效果一样,但是如果是判断NULL值的呢?
SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno FROM test.emp WHERE mgr<=>NULL;
可见判断是否等于NULL的时候,需要用<=>
,前面说过NULL
值不等于0,"","NULL"
。当然判断是否为NULL的时候还可以用 IS NULL
.
SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno FROM test.emp WHERE mgr IS NULL;
下面的判断大小的时候,暂时不用数字了,而用判断字符串,因为逻辑还是有些不同的。
SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno FROM test.emp WHERE ename >'SCOTT';
再补充一下
SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno FROM test.emp WHERE mgr<NULL; SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno FROM test.emp WHERE mgr<=NULL; SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno FROM test.emp WHERE mgr>NULL; SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno FROM test.emp WHERE mgr>=NULL; SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno FROM test.emp WHERE mgr!=NULL; SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno FROM test.emp WHERE mgr<>NULL;
可以看出= > >= < <= <> !=
对于NULL判断只会返回NULL值。
而这个几个遵守的规则其实很=
的规则几乎一样的,所以就不在重复写出这个规则了
SELECT 1>1,1>"1",1>= NULL,1>"a","AB">"AC",1<>1,1!=1,1!='1' FROM DUAL;
前面说的的一个IS NULL这个判断条件,而现在说一些这一部分,其名为比较运算符。
运算符 | 作用 | 例子 |
---|---|---|
IS NULL 或 ISNULL | 为空运算符,判断值,字符串或者表达式是否为空 | WHERE A IS NULL;或WHERE A ISNULL; |
IS NOTNULL | 不为空运算符,判断值,字符串或者表达式是否不为空 | WHERE A IS NOTNULL; |
LEAST | 最小运算符,在多个值中返回最小的值 | LEAST(A,…,B) |
GREATEST | 最大运算符,在多个值中返回最大的值 | GREATEST(A,…,B) |
BETWEEN ... AND ... | 两值之间的运算符,判断在这个两个值之间的数据,同时包含这两个值 | WHERE BETWEEN A AND B; |
IN | 属于运算符,判断一个值是否列表中的任意任意一个值 | WHERE BETWEEN C IN (A,B); |
NOT IN | 不属于运算符,判断一个值是否不是列表中的任意一个值 | WHERE BETWEEN C NOT IN (A,B); |
LIKE | 模糊匹配运算符,判断一个值,是否模糊查询的匹配规则(会用% _ 等符号) | WHERE BETWEEN A LIKE B; |
REGEXP 或 RLIKE | 正则表达式运算符 判断一个值,是否符号正则表达式 | WHERE BETWEEN A REGEXP B;或 WHERE BETWEEN A RLIKE B; |
有些解释还是用代码演示呈现比较好。
IS NULL , ISNULL,IS NOT NULL,
SELECT empno, ename, job, mgr FROM test.emp WHERE mgr IS NULL; SELECT empno, ename, job, mgr FROM test.emp WHERE ISNULL(mgr); SELECT empno, ename, job, mgr FROM test.emp WHERE mgr IS NOT NULL;
LEAST,GREATEST
SELECT LEAST(3,"1",5,6),LEAST("A","P","F","C"),GREATEST(3,"1",5,6),GREATEST("A","P","F","C") FROM DUAL;
然后看一下例子:
SELECT empno, ename, sal,comm,LEAST(sal,comm),GREATEST(sal,comm) FROM test.emp;
其也是遵循了算术运算符中(类似 <)的相同的规则,数字与字符串涉及到隐式转换为数字,以及字符串的如何比较大小,遇见NULL直接返回NULL.
BETWEEN AND
SELECT empno, ename, sal,comm FROM test.emp WHERE sal BETWEEN 800 AND 1600;
可以看出BETWEEN AND
是前后闭包的,也就是包括两个值。前后两个值不要用NULL
,虽然运行不会报错但无论是否有值都不会返回值,没用什么意义。
IN , NOT IN
SELECT empno, ename, sal,comm FROM test.emp WHERE comm IN (300,500,NULL);
通过上面截图看数据库表中的数据,comm有的值为NULL,但是得到的数据如下:
可以看出虽然不会报错,但是NULL的值不会通过IN得到。
但是对于NOT IN
却不是这样。
SELECT empno, ename, sal,comm FROM test.emp WHERE comm NOT IN (300,500);
如果添加上NULL。
SELECT empno, ename, sal,comm FROM test.emp WHERE comm NOT IN (300,500,NULL);
发现其不会得到任何的数据值,所以在IN和NOT IN 中就不要添加NULL,如果真心对其操作可以单独对其,使用IS NULL, ISNULL,IS NOT NULL, <=>,NOT A <=> B
LIKE
SELECT empno, ename FROM test.emp WHERE ename LIKE 'SMITH';
如果这样使用的话,还不如直接用=
,至少会让其性能提高,毕竟LIKE
是模糊查询消耗性能更多.
所以下面说一下在模糊查询中常用的几个字符
%
: 表示0-N个字符,演示如下
SELECT empno, ename FROM test.emp WHERE ename LIKE 'S%'; -- 这个表示是以S开始,当然其% 也可以方多个位置 比如:%S%等操作 这样表示含有S的字符串(其实日期字段也可以)。
_
:表示任意单个字符。
SELECT empno, ename FROM test.emp WHERE ename LIKE '_M%'; -- 表示 第二个字符是M的所有字符串 当然也可以多地方使用比如__JJ, _M_C%等
\
转义符,毕竟有点上面两个字符会作为本身出现
SELECT empno, ename FROM test.emp WHERE ename LIKE '\_M%'; -- 因为没数据所以就不在演示
当然你也可以自定义 转义符
SELECT empno, ename FROM test.emp WHERE ename LIKE '?_M%' ESCAPE '?';
这个后面需要跟一些正则表达式,如果就不展开说了,我在Java基础中写一篇正则表达式----地址
SELECT empno, ename FROM test.emp WHERE ename RLIKE '[W,J]A*';
这个其实在前面用过一个NOT,现在整体看一下。
运算符 | 作用 | 例子 |
---|---|---|
NOT 或! | 逻辑非 | NOT A 或 !=A |
AND 或 && | 逻辑与 | A AND B 或 A && B |
OR 或 || | 逻辑或 | A OR B 或 A ||B |
XOR | 逻辑异或 | A XOR B |
这个在前面用过,就是一个将一个条件进行取反。还是那句话后面不要有NULL,因为没用意义;
SELECT empno, ename FROM test.emp WHERE NOT empno='7521'; -- 或者 SELECT empno, ename FROM test.emp WHERE empno!='7521';
其实在很多的搜索的的条件如果单一得到是数据太多,我们需要更加精确的搜索到某些数据,如下:
SELECT * FROM test.emp WHERE deptno='10' AND sal='1300.00'; -- 当然AND 后面可以有多个 AND 串联 A AND B AND C -- 或着以下写法 SELECT * FROM test.emp WHERE deptno='10' && sal='1300.00';
这个就是看数据满足其中一个条件即可。
SELECT * FROM test.emp WHERE deptno='10' OR deptno='20'; -- 这个也是可以多个使用的。 --或者如下写 SELECT * FROM test.emp WHERE deptno='10' || deptno='20';
补充
OR和AND可以一起使用,但是在使用的时候注意两者的优先级,AND的优先级大于OR,所以MYSQL会先对AND 两边的操作进行操作,得到的数据在与OR的操作书进行结合。
这个和OR区别就是OR满足可以满足OR前后的条件就行,不如果前后都满足和满足其一都可以得到,但是XOR却只能两者之中满足一个,如果都满足也不会得到。所以方便理解,就来两个对比一下。
SELECT * FROM test.emp WHERE deptno='20' OR mgr='7839';
SELECT * FROM test.emp WHERE deptno='20' XOR mgr='7839';
可以看出两者得到是数据条数都不一样,同时看一下是否满足两个条件。
其实这个有点底层的意思,其实再聊JAVA的时候,遇见过尤其是再ArrayList存储长度扩展的时候用的<<
其实聊这个有点涉及到汇编的感觉了。
现在看一下MYSQL中的文运算符
运算符 | 作用 | 例子 |
---|---|---|
& | 按位与,相当于位AND | SELECT A&B |
| | 按位或,相当于位OR | SELECT A|B |
^ | 按位异或,相当于位XOR | SELECT A^B |
~ | 按位取反 | SELECT ~A |
>> | 按位右移 | SELECT >>A |
<< | 按位左移 | SELECT <<A |
其实上面本质上将数据转成二进制然后进行操作的,不过也能说是转成而本质人家就是二进制。这个说要是展开了说会涉及原码,补码,反码。所以现在只是简单的列举例子看一下吧,如果需要到时候单独再来一篇,然后再开一个专栏,将其放在汇编下面。
演示最好用数字,因为数字对于我们来水转换二进制更方便。
SELECT 3&2,3|2,3^2,3>>1,3<<1,~1 FROM DUAL;
这一部分暂时先了解即可,一般基础使用的或不会涉及到位运算符,而这个如果再展开说我估计又得一大段。后面单独来一篇。
前面说了这样的运算符,其不可能单独只使用一种,而再使用中多是混合使用,所以现在看一下其优先级,下面这个表,权重越高其优先级越高。
权重 | 运算符 |
---|---|
1 | :=,=(赋值) |
2 | ||,OR , XOR |
3 | &&, AND |
4 | NOT |
5 | BETWEEN ,CASE ,WHEN,THEN,ELSE |
6 | =(比较运算符),<==>>=,<=,>,<>,!=,IS,LIKE,REGEXP,IN |
7 | | |
8 | & |
9 | <<与>> |
10 | -,+ |
11 | *,/.DIV.%,MOD |
12 | ^ |
13 | -(负号),~ |
14 | ! |
15 | () |
其实上面这个权重表我直接网上复制的一份,其实本质上我也不可能全部记住的,只是使用中常用的一些知道即可,其它的可以翻看文档。