目录
6、IS NULL(IS NULL),IS NOT NULL 运算符
一.算数运算符
运算符连接表达式中各个操作数,其作用是用来指明对操作数所进行的运算。
运用运算符可以更加灵活地使用表中的数据,常见的运算符类型有:
算术运算符,比较运算符,逻辑运算符,位运算符。
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<<n。这里n指定值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;