7.运算符

目录

一.算数运算符

1、算术运算符

2、比较运算符

1、等号(=)用来判断数字、字符串和表达式是否相等。

2、安全等于运算符(<=>)

3、不等于运算符(<>或者!=)

4、小于或等于运算符(<=)

5、小于运算符(<)

6、IS NULL(IS NULL)，IS NOT NULL 运算符

7、BETWEEN AND运算符

8、LEAST运算符

9、GREATEST运算符

10、IN、NOT IN 运算符

11、LIKE 模糊查询符号

12、REGEXP

二.逻辑运算符

逻辑运算符：

1、NOT或者!

2、AND或者&&

3、OR或者||

4、XOR

位运算符

1、位或运算符(|)

2、位与运算符(&)

3、位异或运算符(^)

4、位左移运算符(<<)

5、位右移运算符(>>)

6、位取反运算符(~)

三.其他运算符

综合案例一运算符的使用:

经典习题

---

一.算数运算符

运算符连接表达式中各个操作数，其作用是用来指明对操作数所进行的运算。

运用运算符可以更加灵活地使用表中的数据，常见的运算符类型有:

算术运算符，比较运算符，逻辑运算符，位运算符。

1、算术运算符

下面分别讨论不同算术运算符的使用方法

举例：

创建表tmp14，定义数据类型为INT的字段num，插入值64，对num值进行算术运算：

首先创建表tmp14，输入语句如下：

create table tmp14
(
num int
);

向字段num插入数据64：

insert into tmp14 value(64);

接下来，对num值进行加法和减法运算：

select num, num+10, num-3+5, num+5-3, num+36.5 from tmp14;

由计算结果可以看到，可以对num字段的值进行加法和减法的运算， 而且由于’+’和’–’的优先级相同，因此先加后减，或者先减后加之后的结果是相同的。

在比如：对tmp14表中的num进行乘法、除法运算。

SELECT num, num *2, num /2, num/3, num%3 FROM tmp14;

由计算结果可以看到，对num进行除法运算时候，由于64无法被3整除，因此MySQL

对 num/3求商的结果保存到了小数点后面四位，结果为21.3333；64除以3的余数为1，

因此取余运算num%3的结果为1。

在数学运算时，除数为0的除法是没有意义的，因此除法运算中的除数不能为0，

如果被0除，则返回结果为NULL。

如果用0除num。

select num ,num /0 ,num % 0 from tmp14;

由计算结果可以看到，对num进行除法求商或者求余运算的结果均为NULL。

2、比较运算符

一个比较运算符的结果总是1或者是NULL，比较运算符经常在SELECT的查询条件子句中使用，用来查询满足指定条件的记录。

MySQL中比较运算符如表5.11所示。

1、等号(=)用来判断数字、字符串和表达式是否相等。

如果相等，返回值为 1，否则返回值为0。

使用’=’进行相等判断，SQL语句如下：

select 1=0, '2'=2, 2=2,'0.02'=0, 'b'='b', (1+3) = (2+2),NULL=NULL; 

由结果可以看到，在进行判断时，2=2 和‘2’=2的返回值相同，都为 1

因为在进行判断时，MySQL 自动进行了转换，把字符’2’转换成了数字2:

‘b’=b’为相同的字符比较，因此返回值为1:表达式1+3和表达式2+2的结果都为4，

因此结果相等，返回值为1;

由于’=’不能用于空值NULL的判断，因此返回值为NULL。数值比较时有如下规则:

(1)若有一个或两个参数为NULL，则比较运算的结果为NULL。

(2)若同一个比较运算中的两个参数都是字符串，则按照字符串进行比较。

(3)若两个参数均为整数，则按照整数进行比较。

(4)若一个字符串和数字进行相等判断，则MySQL可以自动将字符串转换为数字。

2、安全等于运算符(<=>)

这个操作符和=操作符执行相同的比较操作，不过<=>可以用来判断 NULL值。

在两个操作数均为NULL时，其返回值为1而不为NULL;

而当一个操作数为NULL 时，其返回值为0而不为NULL。

使用’<=>’进行相等的判断，SQL语句如下：

select 1<=>0, '2'<=>2, 2<=>2,'0.02'<=>0, 'b'<=>'b', (1+3) <=> (2+1),NULL<=>NULL; 

由结果可以看到，‘<=>’在执行比较操作时和‘=’的作用是相似的，

唯一的区别是’<=>’可以用来对NULL进行判断，两者都为NULL时返回值为1。

3、不等于运算符(<>或者!=)

‘<>’或者 '!=’用于判断数字、字符串、表达式不相等的判断。

如果不相等，返回值为1;否则返回值为0。这两个运算符不能用于判断空值NULL。

使用’<>’和’!=’进行不相等的判断，

SQL语句如下：

select 'good'<>'god', 1<>2, 4!=4, 5.5!=5, (1+3)!=(2+1),NULL<>NULL; 

由结果看到，两个不等于运算符作用相同，都可以进行数字、字符串、表达式的比较

判断。

4、小于或等于运算符(<=)

用来判断左边的操作数是否小于或者等于右边的操作数。

如果小于或者等于，返回值为1，否则返回值为0。‘<=’不能用于判断空值NULL。

使用’<=’进行比较判断，

SQL语句如下：

select 'good'<='god', 1<=2, 4<=4, 5.5<=5, (1+3) <= (2+1),NULL<=NULL; 

由结果可以看到，左边操作数小于或者等于右边时，返回值为1，例如：4<=4；

当左边操作数大于右边时，返回值为0，例如：’good’第3个位置的’o’字符在字母表中 的顺序大于’god’中的第3个位置的’d’字符，因此返回值为0；同样比较NULL值时返回NULL。

5、小于运算符(<)

“<’运算符用来判断左边的操作数是否小于右边的操作数，

如果小于，返回值为 1;否则返回值为0。“<’不能用于判断空值 NULL。

使用’<’进行比较判断，SQL语句如下：

select 'good'<'god', 1<2, 4<4, 5.5<5, (1+3) < (2+1),NULL<NULL; 

由结果可以看到，当左边操作数小于右边时，返回值为 1，例如:1<2;

当左边操作数大于右边时，返回值为0例如:“good’第3个位置的’字符在字母表中

的顺序大于god'中的第3个位置的“d’字符，因此返回值为0:同样比较NULL值时返回

NULL。

6、IS NULL(IS NULL)，IS NOT NULL 运算符

IS NULL和ISNULL检验一个值是否为 NULL，如果为 NULL，返回值为1。否则返回值

为0:

IS NOT NULL检验一个值是否为非NULL，如果是非NULL，返回值为 1:否则返回值为

0。

SQL语句如下：

select NULL is null, isnull(NULL),isnull(10), 10 is not NULL; 

7、BETWEEN AND运算符

语法格式为:expr BETWEEN min AND max。

假如expr大于或等于min且小于或等于max,则BETWEEN的返回值为1，否则返回值为0。

使用BETWEEN AND 进行值区间判断，输入SQL语句如下：

select 4 between 4 and 6, 4 between 4 and 6,12 between 9 and 10;

select 'x' between 'f' and 'g', 'b' between 'a' and 'c';

由结果可以看到，4在端点值区间内或者等于其中一个端点值时，

BETWEEN AND表达式返回值为 1;12并不在指定区间内，因此返回值为0;

对于字符串类型的比较，按字母表中字母顺序进行比较，

’x’不在指定的字母区间内，因此返回值为 0，

而‘b’位于指定字母区间内，因此返回值为1。

8、LEAST运算符

语法格式为:LEAST (值1,值2, ... 值n)，其中值n表示参数列表中有n个值。

在有两个或多个参数的情况下,返回最小值。

假如任意一个自变量为NULL,则LEAST()的返回值为NULL

使用LEAST运算符进行大小判断，SQL语句如下：

select least(2,0), least(20.0,3.0,100.5), least('a','c','b'),least(10,NULL);

由结果可以看到，当参数中是整数或者浮点数时，LEAST 将返回其中最小的值，

当参数为字符串时，返回字母表中顺序最靠前的字符:

当比较值列表中有NULL 时，不能判断大小返回值为NULL。

9、GREATEST运算符

语法格式为:GREATEST (值 1,值2，... 值n)，其中n表示参数列表中有个值。

当有2个或多个参数时，返回值为最大值，

假如任意一个自变量为NULL，则GREATEST()的返回值为NULL。

使用GREATEST运算符进行大小判断，SQL语句如下：

select greatest(2,0), greatest(20.0,3.0,100.5), greatest('a','c','b'),greatest(10,NULL); 

10、IN、NOT IN 运算符

IN 运算符用来判断操作数是否为IN列表中的其中一个值，

如果是，返回值为 1;否则返回值为0。

NOT IN运算符用来判断表达式是否为IN列表中的其中一个值，

如果不是，返回值为1:否则返回值为0。

使用IN、NOT IN运算符进行判断，SQL语句如下：

select 2 in (1,3,5,'thks'), 'thks' in (1,3,5,'thks');

select 2 not in (1,3,5,'thks'), 'thks' not in (1,3,5,'thks');

11、LIKE 模糊查询符号

LIKE运算符用来匹配字符串，

语法格式为:expr LIKE 匹配条件，

如果expr 满足匹配条件，则返回值为1(TRUE);

如果不匹配，则返回值为0(FALSE)。

若xpr 或匹配条件中任何一个为NULL，则结果为NULL。

LIKE运算符在进行匹配时，可以使用下面两种通配符:

‘%’，匹配任何数目的字符，甚至包括零字符。

‘-’只能匹配一个字符。

使用运算符LIKE进行字符串匹配运算，SQL语句如下：

select 'stud' like 'stud', 'stud' like 'stu_','stud' like '%d','stud' like 't_ _ _', 's' like NULL; 

由结果可以看到， 指定匹配字符串为“stud”。“stud”表示直接匹配“stud”字符串，满足匹配条件，返回1;

“stu_”表示匹配以stu 开头的长度为4个字符的字符串，“stud”正好是4个字符，

满足匹配条件，因此匹配成功，返回1;

“%d”表示匹配以字母“d”结尾的字符串，“stud”满足匹配条件，匹配成功，返回1;

“t___”表示匹配以“t’开头的长度为4个字符的字符串，“stud”不满足配条件，因此返回 0:

当字符's’与NULL匹配时,结果为NULL。

12、REGEXP

REGEXP运算符用来匹配字符串，

语法格式为:expr  REGEXP  匹配条件

如果expr 满足匹配条件，返回 1;

如果不满，则返回0;

若 expr 或匹配条件任意一个为 NULL，则结果为NULL。

REGEXP运算符在进行匹配时，常用的有下面几种通配符:

(1)'^’匹配以该字符后面的字符开头的字符串

(2)'$’匹配以该字符后面的字符结尾的字符串。

(3)'.’匹配任何一个单字符。

(4)“[...]”匹配在方括号内的任何字符。例如，“[abc]”匹配“a”、“b”或“c”

为了命名字符的范围，使用一个“-’。“[a-z]”匹配任何字母，而“[0-9]”匹配任何数字。

(5)'*’匹配零个或多个在它前面的字符。例如，“x*”匹配任何数量的“x’字符“

"[0-9]*”匹配任何数量的数字，而“*”匹配任何数量的任何字符。

使用运算符REGEXP进行字符串匹配运算，SQL语句如下：

select 'ssky' regexp '^s', 'ssky' regexp 'y$', 'ssky' regexp '.sky', 'ssky' regexp '[ab]'; 

由结果可以看到，指定匹配字符串为“ssky”.

“^s”表示匹配任何以字母“s’开头的字符串，因此满足匹配条件，返回 1;

“y$”表示任何以字母“y”结尾的字符串，因此满足匹配条件，返回1;

“.sky”匹配任何以“sky”结尾字符长度为4的字符串满足匹配条件返回1;

“[ab]”匹配任何包含字母“a’或者“b’的字符串，指定字符串中没有字母“a’也没有字 母“b’， 因此不满足匹配条件，返回0。

二.逻辑运算符

逻辑运算符：

在SQL中，所有逻辑运算符的求值所得结果均为 TRUE、FALSE 或NULL。

在MySQL中,它们体现为 1(TRUE) 0(FALSE)和NULL。

其大多数都与不同的数据库SQL 通用 。

1、NOT或者!

逻辑非运算符NOT或者!表示当操作数为0时，所得值为 1;

当操作数为非零值时，所得值为0:

当操作数为NULL时，所得的返回值为NULL。

分别使用非运算符“NOT”和“！”进行逻辑判断，SQL语句如下：

select not 10,not(1-1),not -5,not null,not 1+1;

2、AND或者&&

逻辑与运算符AND或者&&表示

当所有操作数均为非零值、并且不为 NULL时，计算所得结果为1:

当一个或多个操作数为0时，所得结果为0，其余情况返回值为 NULL。

全1为1，有0为0，若有null则为null，若有null和0则为0

分别使用与运算符“AND”和“&&”进行逻辑判断，SQL语句如下：

 select 1 and -1,1 and 0,1 and null,0 and null;

select 1 && -1,1 && 0,1 && null,0 && null;

1/8由结果可以看到，“AND”和“&&”的作用相同。

“1 AND -1”中没有0或者NULL因此结果为1;

“1 AND 0”中有操作数0因此结果为0:

“1 AND NULL”中虽然有NULL但是没有操作数0，返回结果为 NULL。

3、OR或者||

逻辑或运算符OR或者||

表示当两个操作数均为非NULL值，且任意一个操作数为非零值时，结果为 1，否则结

果为 0;

当有一个操作数为 NULL，且另一个操作数为非零值时，则结果为1，否则结果为

NULL;

当两个操作数均为NULL时，则所得结果为NULL。

有1为1，全0为0，全null为null，有null和0时为null

分别使用或运算符“OR”和“||”进行逻辑判断，SQL语句如下：

 select 1 or -1 or 0,1 or 2,1 or null,0 or null,null or null;

  select 1 || -1 || 0,1 || 2,1|| null,0 || null,null || null;

由结果可以看到，“OR”和“||”的作用相同。

“1 OR -1 OR 0”中有0，但同时包含有非0的值1和-1，返回结果为1;

“1 OR 2”中没有操作数0返回结果为1;

“1 OR NULL”中虽然有NULL，但是有操作数1，返回结果为1:

“0 OR NULL”中没有非0值，并且有NULL，返回结果为NULL;

“NULL OR NULL”中只有NULL，返回结果为NULL。

4、XOR

逻辑异或运算符XOR。

当任意一个操作数为NULL时，返回值为NULL;

对于非NULL的操作数，

如果两个操作数都是非0值或者都是0值，则返回结果为 0: ===》相同为 0

如果一个为0值，另一个为非0值，返回结果为 1。 ===》不同为1

有null为null

使用异或运算符“XOR”进行逻辑判断，SQL语句如下：

select 1 xor 1,0 xor 0,1 xor 0,1 xor null,1 xor 1 xor 1;

执行上面的语句，结果如下。

由结果可以看到，“1 XOR 1”和“0 XOR 0”中运算符两边的操作数都为非零值，

或者都是零值，因此返回0:

“1 XOR 0”中两边的操作数，一个为0值，另一个为非0值，返回结果为1

“1 XOR NULL”中有一个操作数为NULL返回结果为NULL

“1 XOR 1 XOR 1“中有多个操作数，运算符相同，因此运算顺序从左到右依次算，

“1 XOR 1”的结果为0再与1进行异或运算，因此结果为1。

a XOR b的计算等同于(a AND (NOT b))或者((NOT a) AND b)。

位运算符

位运算符是用来对二进制字节中的位进行测试、移位或者测试处理，MySOL 中提供

的位运算符有按位或(|)，按位与(&)，按位异或(^)，按位左移(<<)，按位右移(>)

按位取反(~)，如表5.13 所示。

1、位或运算符(|)

位或运算的实质是将参与运算的几个数据，按对应的二进制数逐位进行逻辑或运

算。

对应的二进制位有一个或两个为 1，则该位的运算结果为 1，否则为0。

有1为1，全0为0

使用位或运算符进行运算，SQL语句如下：

select 10 | 15,9 | 4 | 2;

10的二进制数值为 1010，15 的二进制数值为 1111，按位或运算之后，结果为

1111，

即整数 15;9的二进制数值为 1001，4 的二进制数值为 100，2的二进制数值为

0010，

按位或运算之后，结果为1111，即也是整数15。其结果为一个64位无符号整

数。

2、位与运算符(&)

位与运算的实质是将参与运算的几个操作数，按对应的二进制数逐位进行逻辑与

运算。

对应的二进制位都为1，则该位的运算结果为1，否则为 0。

全1为1，有0为0

使用位与运算符进行运算，SQL语句如下：

 select 10 & 15,9 & 4 & 2;

10的二进制数值为1010，15的二进制数值为1111，按位与运算之后，结果为

1010，

即整数10: 9的二进制数值为 1001，4的二进制数值为 100，2的二进制数值为

0010，

按位与运算之后，结果为0000，即整数0。其结果为一个64位无符号整数。

3、位异或运算符(^)

位异或运算的实质是将参与运算的两个数据，按对应的二进制数逐位进行逻辑异

或运算对应位的二进制数不同时，对应位的结果才为 1。如果两个对应位数都为0

或者都为1，则对应位的结果为0。

不同为1，相同为0

使用位异或运算符进行运算，SQL语句如下：

select 10 ^ 15,1 ^ 0 ,1 ^ 1;

4、位左移运算符(<<)

位左移运算符<<使指定的二进制值的所有位都左移指定的位数。左移指定位数

之后，左边高位的数值将被移出并丢弃，右边低位空出的位置用0补齐。

语法格式为:expr<

使用位左移运算符进行运算，SQL语句如下：

 select 1<<2,4<<2;

1的二进制值为00000001，左移两位之后变成00000100，即十进制整数4:

十进制4左移两位之后变成00010000，即变成十进制的16。

5、位右移运算符(>>)

位右移运算符>>使指定的二进制值的所有位都右移指定的位数。右移指定位数

之后，右边低位的数值将被移出并丢弃，左边高位空出的位置用0补齐。

语法格式为:expr>>n。这里n指定值expr 要移位的位数。

使用位右移运算符进行运算，SQL语句如下：

select  1>>1, 16>>2;

1的二进制值为00000001，右移1位之后变成0000000，即十进制整数0:

16的二进制值为00010000右移两位之后变成00000100，即变成十进制的4。

6、位取反运算符(~)

位取反运算的实质是将参与运算的数据，按对应的二进制数逐位反转，

即1取反后变0,0取反后变为1。

使用位取反运算符进行运算，SQL语句如下：

select 5 & ~1; 

逻辑运算 5 &~1 中，由于位取反运算符“~’的级别高于位与运算符“&’，

因此先对1取反操作，取反之后，除了最低位为0其他为都为 1，

然后再与十进制数值 5 进行与运算，结果为0100，即整数4。

MySQL 经过位运算之后的数值是一个64 位的无符号整数,

1的二进制数值表示为最右边位为1，其他位均为0，

取反操作之后，除了最低位，其他位均变为 1。

可以使用BIN()函数查看1取反之后的结果，SOL语句如下:

select bin(~1);

将1的取反的二进制表示出来

三.其他运算符

=========================================================================

综合案例一运算符的使用:

1、案例目的

创建数据表，并对表中的数据进行运算操作，掌握各种运算符的使用方法。

创建表tmp15，其中包含VARCHAR类型的字段note和INT类型的字段price，

使用运算符对表 tmp15 中不同的字段进行运算;

使用逻辑操作符对数据进行逻辑操作;使用位操作符对数据进行位操作。

本案例使用数据表tmp15，首先创建该表，SQL语句如下:

CREATE TABLE tmp15 (note VARCHAR(100), price INT);

向表中插入一条记录，note值为“Thisisgood”，price值为50，SQL语句如下：

INSERT INTO tmp15 VALUES(“Thisisgood”, 50);

2. 案例操作过程：

1) 对表tmp15中的整型数值字段price进行算术运算，执行过程如下：

SELECT price, price + 10, price -10, price * 2, price /2, price%3 FROM tmp15 ;

2)对表tmp15中的整型数值字段price进行比较运算，执行过程如下：

SELECT price, price> 10, price<10, price != 10, price =10, price <=>10,price <>10 FROM tmp15 ;

3)判断price值是否落在30~80区间；返回与70，30相比最大的值，

判断price是否为IN列表（10, 20, 50, 35）中的某个值，执行过程如下：

SELECT price, price BETWEEN 30 AND 80, GREATEST(price, 70,30), price IN (10, 20,

50,35) FROM tmp15 ;

4)对tmp15中的字符串数值字段note进行比较运算，判断表tmp15中note字段是否为空；

使用LIKE判断是否以字母’t’开头；使用REGEXP判断是否以字母’y’结尾；

判断是否包含字母’g’或者’m’，执行过程如下：

SELECT note, note IS NULL, note LIKE 't%', note REGEXP '$y' ,note REGEXP '[gm]'

FROM tmp15 ;

5)将price字段值与NULL，0进行逻辑运算，执行过程如下：

SELECT price, price && 1, price && NULL, price||0, price AND 0, 0 AND NULL, price

OR NULL FROM tmp15 ;

SELECT price,!price,NOT NULL,price XOR 3, 0 XOR NULL, price XOR 0 FROM tmp15 ;

6) 将price字段值与2、4进行按位与、按位或操作，并对price进行按位操作，执行过程

如下：

SELECT price, price&2 , price|4, ~price FROM tmp15 ;

7)将price字段值分别左移和右移两位，执行过程如下：

SELECT price, price<<2, price>>2 FROM tmp15 ;

=========================================================================

经典习题

(1)MySQL中的小数如何表示，不同表示方法之间有什么区别?

        MySQL中使用浮点数和定点数来表示小数，浮点类型有两种：单精度浮点类型（float）和双精度浮点类型（double）。定点类型只有一种：decimal。浮点类型和定点类型都可以用（M,N）来表示，其中M称为精度，表示总共的位数；N称为标度，表示小数的位数。decimal类型不同于float和double，decimal实际是以串存放的，decimal可能的最大取值范围与double一样，但是其有效的取值范围由M和D的值决定。

(2)BLOB和TEXT分别适合于存储什么类型的数据?

        TEXT列保存非二进制字符串，纯文本文件，如文章内容、评论等。BLOB是一个二进制大对象，用来存储可变数量的数据，如图片、音频信息等。
        BLOB列存储的是二进制字符串（字节字符串）；TEXT列存储的是非二进制字符串（字符字符串）。BLOB列没有字符集，并且排序和比较基于列值字节的数值；TEXT列有一个字符集，并且根据字符集对值进行排序和比较。

(3)说明ENUM和SET类型的区别以及在什么情况下适用?

        ENUM只能取单值，它的数据列表是一个枚举集合。它的合法取值列表最多允许有65535个成员。因此，在需要从多个值中选取一个时，可以使用ENUM。
        SET可取多值。它的合法取值列表最多允许有64个成员。空字符串也是一个合法的SET值。在需要取多个值的时候，适合使用SET类型。

(4)在MySOL中执行如下算术运算:(9-7)*4，8+15/3，17 DIV 2，39 % 12。

select num,(num-7)*4,num-1+15/3,(num+8)/2,(num+30)%12  from xiaocheshi1;

(5)在MySQL中执行如下比较运算:

36>27,15>=8，40<50,15<=15，NULL<=>NULL,NULL<=>1，5<=>5。

select 36>27,15>=8,40<50,15<=15,null<=>null,null<=>1,5<=>5;

(6)在MySQL中执行如下逻辑运算:

4&&8，-2 || NULL，NULL XOR 0 0 XOR 1，!2

select 4&&8,-2 || null,null xor 0,0 xor 1,!2;

(7)在MySQL中执行如下位运算:13 & 17，2018 || 1420，~16。

select 13 & 17,2018 ^ 1420,~16;