MySQL进阶查询

NULL值正则表达式各种运算符算术运算符比较运算符逻辑运算符（1）逻辑非（2）逻辑与（3）逻辑或（4）逻辑异或 位运算符运算符的优先级

NULL值

表示缺失的值与数字0或者空白（spaces）是不同的使用IS NULL或IS NOT NULL进行判断NULL值和空值的区别 空值长度为0，不占空间；null值的长度为null，占用空间IS NULL无法判断空值空值使用“=”或者“<>”来处理count（）计算时，null会忽略，空值会加入计算

验证实验

mysql> alter table info add column addr varchar(50); mysql> select * from info; +----+---------+-------+-------+------+ | id | name | score | hobby | addr | +----+---------+-------+-------+------+ | 1 | zhansan | 66.00 | 1 | NULL | | 2 | lisi | 68.00 | 2 | NULL | | 3 | wangwu | 74.00 | 3 | NULL | | 4 | zhaoliu | 54.00 | 4 | NULL | | 5 | xiaoqi | 99.00 | 2 | NULL | | 10 | T1 | 58.00 | 2 | NULL | +----+---------+-------+-------+------+ mysql> update info set addr='nj' where score>=60; mysql> select * from info; +----+---------+-------+-------+------+ | id | name | score | hobby | addr | +----+---------+-------+-------+------+ | 1 | zhansan | 66.00 | 1 | nj | | 2 | lisi | 68.00 | 2 | nj | | 3 | wangwu | 74.00 | 3 | nj | | 4 | zhaoliu | 54.00 | 4 | NULL | | 5 | xiaoqi | 99.00 | 2 | nj | | 10 | T1 | 58.00 | 2 | NULL | +----+---------+-------+-------+------+ mysql> select count(addr) from info; #null没有被加入统计 +-------------+ | count(addr) | +-------------+ | 4 | +-------------+ mysql> update info set addr='' where name='T1'; #改为空字符串 mysql> select * from info; +----+---------+-------+-------+------+ | id | name | score | hobby | addr | +----+---------+-------+-------+------+ | 1 | zhansan | 66.00 | 1 | nj | | 2 | lisi | 68.00 | 2 | nj | | 3 | wangwu | 74.00 | 3 | nj | | 4 | zhaoliu | 54.00 | 4 | NULL | | 5 | xiaoqi | 99.00 | 2 | nj | | 10 | T1 | 58.00 | 2 | | +----+---------+-------+-------+------+ mysql> select count(addr) from info; #空字符串加入统计 +-------------+ | count(addr) | +-------------+ | 5 | +-------------+

正则表达式

根据指定的匹配模式匹配记录中符合要求的特殊字符使用REGEXP关键字指定匹配模式常用匹配模式 符号描述符号描述^匹配开始字符p1丨p2匹配p1或p2$匹配结束字符[…]匹配字符集中的任意一个字符.匹配任意单个字符[^…]匹配不在中括号内的任意字符*匹配任意个前面的字符{n}匹配前面的字符串n次+匹配前面字符至少1次{n,m}匹配前面的字符串至少n次，至多m次

测试表

mysql> select * from info; +----+---------+-------+-------+------+ | id | name | score | hobby | addr | +----+---------+-------+-------+------+ | 1 | zhansan | 66.00 | 1 | nj | | 2 | lisi | 68.00 | 2 | nj | | 3 | wangwu | 74.00 | 3 | nj | | 4 | zhaoliu | 54.00 | 4 | NULL | | 5 | xiaoqi | 99.00 | 2 | nj | | 10 | T1 | 58.00 | 2 | | | 11 | owooo | 55.00 | 1 | | | 12 | owo | 55.00 | 1 | | | 13 | yowooo | 58.00 | 1 | | | 14 | yowo | 65.00 | 1 | | +----+---------+-------+-------+------+

^ 匹配开始字符

mysql> select * from info where name regexp '^z'; +----+---------+-------+-------+------+ | id | name | score | hobby | addr | +----+---------+-------+-------+------+ | 1 | zhansan | 66.00 | 1 | nj | | 4 | zhaoliu | 54.00 | 4 | NULL | +----+---------+-------+-------+------+

$ 匹配结束字符

mysql> select * from info where name regexp 'u$'; +----+---------+-------+-------+------+ | id | name | score | hobby | addr | +----+---------+-------+-------+------+ | 3 | wangwu | 74.00 | 3 | nj | | 4 | zhaoliu | 54.00 | 4 | NULL | +----+---------+-------+-------+------+

. 匹配任意单个字符

mysql> select * from info where name regexp 'li..'; +----+------+-------+-------+------+ | id | name | score | hobby | addr | +----+------+-------+-------+------+ | 2 | lisi | 68.00 | 2 | nj | +----+------+-------+-------+------+

‘*’ 匹配任意个前面的字符

mysql> select * from info where name regexp 'owoo*'; +----+--------+-------+-------+------+ | id | name | score | hobby | addr | +----+--------+-------+-------+------+ | 11 | owooo | 55.00 | 1 | | | 12 | owo | 55.00 | 1 | | | 13 | yowooo | 58.00 | 1 | | | 14 | yowo | 65.00 | 1 | | +----+--------+-------+-------+------+

+ 匹配前面字符至少1次

mysql> select * from info where name regexp 'owoo+'; +----+--------+-------+-------+------+ | id | name | score | hobby | addr | +----+--------+-------+-------+------+ | 11 | owooo | 55.00 | 1 | | | 13 | yowooo | 58.00 | 1 | | +----+--------+-------+-------+------+

[…] 匹配字符集中的任意一个字符

mysql> select * from info where name regexp '[xyz]'; +----+---------+-------+-------+------+ | id | name | score | hobby | addr | +----+---------+-------+-------+------+ | 1 | zhansan | 66.00 | 1 | nj | | 4 | zhaoliu | 54.00 | 4 | NULL | | 5 | xiaoqi | 99.00 | 2 | nj | | 13 | yowooo | 58.00 | 1 | | | 14 | yowo | 65.00 | 1 | | +----+---------+-------+-------+------+

[^…] 匹配不在中括号内的任意字符

mysql> select * from info where name regexp '[^xyz]'; +----+---------+-------+-------+------+ | id | name | score | hobby | addr | +----+---------+-------+-------+------+ | 1 | zhansan | 66.00 | 1 | nj | | 2 | lisi | 68.00 | 2 | nj | | 3 | wangwu | 74.00 | 3 | nj | | 4 | zhaoliu | 54.00 | 4 | NULL | | 5 | xiaoqi | 99.00 | 2 | nj | | 10 | T1 | 58.00 | 2 | | | 11 | owooo | 55.00 | 1 | | | 12 | owo | 55.00 | 1 | | | 13 | yowooo | 58.00 | 1 | | | 14 | yowo | 65.00 | 1 | | +----+---------+-------+-------+------+

{n} 匹配前面的字符串n次

mysql> select * from info where name regexp 'oo{2}'; +----+--------+-------+-------+------+ | id | name | score | hobby | addr | +----+--------+-------+-------+------+ | 11 | owooo | 55.00 | 1 | | | 13 | yowooo | 58.00 | 1 | | +----+--------+-------+-------+------+

{n,m}匹配前面的字符串至少n次，至多m次

mysql> select * from info where name regexp 'oo{2,2}'; +----+--------+-------+-------+------+ | id | name | score | hobby | addr | +----+--------+-------+-------+------+ | 11 | owooo | 55.00 | 1 | | | 13 | yowooo | 58.00 | 1 | | +----+--------+-------+-------+------+

各种运算符

算术运算符

运算符描述+加法-减法*乘法/除法%取余数 mysql> select 1+2 as addition, 2-1 as subtraction, 2*3 as mulltiplication, 4/2 as division, 7%2 as remainder; +----------+-------------+----------------+----------+-----------+ | addition | subtraction | multiplication | division | remainder | +----------+-------------+----------------+----------+-----------+ | 3 | 1 | 6 | 2.0000 | 1 | +----------+-------------+----------------+----------+-----------+

比较运算符

运算符描述运算符描述=等于IS NOT NULL判断一个值是否不为NULL>大于BETWEEN AND两者之间<小于IN在集合中>=大于等于LIKE通配符匹配<=小于等于GREATEST两个或多个参数时返回最大值!=或<>不等于LEAST两个或多个参数时返回最小值IS NULL判断一个值是否为NULLREGEXP正则表达式

比较运算中，如果不相等则返回 1，如果相等则返回 0，这点正好跟等于的返回值相反，需要注意的是不等于运算符不能用于判断NULL。

（一）等于

mysql> select 2=4,2='2','e'='e',(2+2)=(3+1),'r'=null; +-----+-------+---------+-------------+----------+ | 2=4 | 2='2' | 'e'='e' | (2+2)=(3+1) | 'r'=null | +-----+-------+---------+-------------+----------+ | 0 | 1 | 1 | 1 | NULL | +-----+-------+---------+-------------+----------+

从以上查询可以看出来：

如果两者都是整数，则按照整数值进行比较如果一个整数一个字符串，则会自动将字符串转换为数字，再进行比较如果两者都是字符串，则按照字符串进行比较如果两者中至少有一个值是NULL，则比较结果是NULL

（二）不等于

mysql> select 'zhangsan'<>'lisi',1<>2,3!=3,2.5!=2,null<>null; +--------------------+------+------+--------+------------+ | 'zhangsan'<>'lisi' | 1<>2 | 3!=3 | 2.5!=2 | null<>null | +--------------------+------+------+--------+------------+ | 1 | 1 | 0 | 1 | NULL | +--------------------+------+------+--------+------------+

（三）大于、大于等于、小于、小于等于

mysql> select 5>4,'a'>'b',2>=3,(2+3)>=(1+2),4.4<3,1<2,'x'<='y',5<=5.5,'u'>=null; +-----+---------+------+--------------+-------+-----+----------+--------+-----------+ | 5>4 | 'a'>'b' | 2>=3 | (2+3)>=(1+2) | 4.4<3 | 1<2 | 'x'<='y' | 5<=5.5 | 'u'>=null | +-----+---------+------+--------------+-------+-----+----------+--------+-----------+ | 1 | 0 | 0 | 1 | 0 | 1 | 1 | 1 | NULL | +-----+---------+------+--------------+-------+-----+----------+--------+-----------+

（四）IS NULL、IS NOT NULL

mysql> select 2 is null,'f' is not null,null is null; +-----------+-----------------+--------------+ | 2 is null | 'f' is not null | null is null | +-----------+-----------------+--------------+ | 0 | 1 | 1 | +-----------+-----------------+--------------+ IS NULL 判断一个值是否为NULL，如果为NULL返回1，否则返回 0IS NOT NULL 判断一个值是否不为NULL，如果不为NULL 返回1，否则返回0

（五）BETWEEN AND

between and比较运算符通常用于判断一个值是否落在某两个值之间。 mysql> select 4 between 2 and 6,5 between 6 and 8,'c' betweenn 'a' and 'f'; +-------------------+-------------------+-------------------------+ | 4 between 2 and 6 | 5 between 6 and 8 | 'c' between 'a' and 'f' | +-------------------+-------------------+-------------------------+ | 1 | 0 | 1 | +-------------------+-------------------+-------------------------+

（六）LEAST、GREATEST

LEAST：当有两个或者多个参数时，返回其中的最小值。如果其中一个值为null，则返回结果就为NULL。GREATEST：当有两个或者多个参数时，返回其中最大值。如果其中一个值为null，则返回结果就为NULL。 mysql> select least(1,2,3),least('a','b','c'),greatest(1,2,3),greatest('a','b','c'); +--------------+--------------------+-----------------+-----------------------+ | least(1,2,3) | least('a','b','c') | greatest(1,2,3) | greatest('a','b','c') | +--------------+--------------------+-----------------+-----------------------+ | 1 | a | 3 | c | +--------------+--------------------+-----------------+-----------------------+

（七）IN、NOT IN

IN 判断一个值是否不在对应的列表中，如果是返回 1，否则返回0。NOT IN判断一个值是否不在对应的列表中，如果不是返回1，否则返回0。 mysql> select 2 in (1,2,3,4,5),'c' not in ('a','b','c'); +------------------+--------------------------+ | 2 in (1,2,3,4,5) | 'c' not in ('a','b','c') | +------------------+--------------------------+ | 1 | 0 | +------------------+--------------------------+

**（八）LIKE、NOT　LIKE　**

like用来匹配字符串，如果匹配成功则返回1，反之返回0。like支持两种通配符；‘%’用于匹配任意数目的字符，而‘_’只能匹配一个字符。not like正好跟like相反，如果没有匹配成功则返回1，反之返回0.

mysql> select 'bdqn' like 'bdq_','kgc' like '%c','etc' not like '%th'; +--------------------+-----------------+----------------------+ | 'bdqn' like 'bdq_' | 'kgc' like '%c' | 'etc' not like '%th' | +--------------------+-----------------+----------------------+ | 1 | 1 | 1 | +--------------------+-----------------+----------------------+

逻辑运算符

逻辑运算符又被称为布尔运算符，通常用来判断表达式的真假，如果为真返回 1，否则返回 0，真和假也可以用 true 和 false表示，MySQL中支持使用逻辑运算符有四种。

运算符描述NOT或 ！逻辑非AND或&&逻辑与OR或丨丨逻辑或XOR逻辑异或

（1）逻辑非

逻辑非用NOT或 ！表示，逻辑非将跟在它后面的逻辑测试取反，把真变成假，把假变成真。 如果not后面的操作数为0时，所得值为1；如果操作数为非0时，所得值为0； 如果操作数为NULL时，所得值为null。

mysql> select not 2,!3,not 0,!(4-4); +-------+----+-------+--------+ | not 2 | !3 | not 0 | !(4-4) | +-------+----+-------+--------+ | 0 | 0 | 1 | 1 | +-------+----+-------+--------+

（2）逻辑与

逻辑与用AND或&&表示，通常用于判断两个值或多个值的有效性，如果所有值都是真返回1，否则返回0。

mysql> select 2 and 3,4 && 0,0 && null,1 and null; +---------+--------+-----------+------------+ | 2 and 3 | 4 && 0 | 0 && null | 1 and null | +---------+--------+-----------+------------+ | 1 | 0 | 0 | NULL | +---------+--------+-----------+------------+

（3）逻辑或

逻辑或用OR或||表示，逻辑或表示包含的操作数，任意一个为非零值并且不是null值时，返回1，否则返回0。

mysql> select 2 or 3,4 || 0,0 or null,1 || null; +--------+--------+-----------+-----------+ | 2 or 3 | 4 || 0 | 0 or null | 1 || null | +--------+--------+-----------+-----------+ | 1 | 40 | NULL | NULL | +--------+--------+-----------+-----------+

（4）逻辑异或

逻辑异或用XOR表示，两个非null值的操作数，如果两者都是0或者都是非0，则返回0；如果一个为0，另一个为非0，则返回结果为1；当任意一个值为null时，返回值为null。

mysql> select 2 xor 3,0 xor 0,0 xor 5,1 xor null,null xor null; +---------+---------+---------+------------+---------------+ | 2 xor 3 | 0 xor 0 | 0 xor 5 | 1 xor null | null xor null | +---------+---------+---------+------------+---------------+ | 0 | 0 | 1 | NULL | NULL | +---------+---------+---------+------------+---------------+

位运算符

位运算是对二进制数进行计算的运算符，MYSQL支持6种位运算符。

运算符描述&按位与丨按位或~按位取反^按位异或<<按位左移>>按位右移 mysql> select 10 & 15,10 | 15,10^15,5 &~1; +---------+---------+-------+-------+ | 10 & 15 | 10 | 15 | 10^15 | 5 &~1 | +---------+---------+-------+-------+ | 10 | 15 | 5 | 4 | +---------+---------+-------+-------+ mysql> select 1<<2,10>>2; +------+-------+ | 1<<2 | 10>>2 | +------+-------+ | 4 | 2 | +------+-------+

右移左移位运算

“>>” 右移位运算 向右移动位数，多余的位数直接删除

“<<” 左移位运算 （向左移动位置，空缺处补0)

运算符的优先级

决定了不同的运算符在计算过程中的先后顺序优先级高的先运算，同级的按从左到右进行计算可以使用()小括号来改变计算优先级运算符的优先级 优先级运算符优先级运算符1！8丨2~9=,<=>,>=,>,<=,<,<>,!=,IS,like,regexp,in3^10between,case,when,then,else4*,/(DIV),%(MOD)11not5+,-12&&,and6>>,<<13丨丨,or,xor7&14:=