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

判断一个值是否为NULL

REGEXP

正则表达式

比较运算中，如果不相等则返回 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 |
+-----+-------+---------+-------------+----------+

从以上查询可以看出来：

  1. 如果两者都是整数，则按照整数值进行比较
  2. 如果一个整数一个字符串，则会自动将字符串转换为数字，再进行比较
  3. 如果两者都是字符串，则按照字符串进行比较
  4. 如果两者中至少有一个值是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 |
+-----------+-----------------+--------------+
  1. IS NULL 判断一个值是否为NULL，如果为NULL返回1，否则返回 0
  2. IS 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

  1. LEAST：当有两个或者多个参数时，返回其中的最小值。如果其中一个值为null，则返回结果就为NULL。
  2. 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

  1. IN 判断一个值是否不在对应的列表中，如果是返回 1，否则返回0。
  2. 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 |
+------+-------+

右移左移位运算

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

datatime mysql 传空值 mysql空值如何表示_1024程序员节


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

datatime mysql 传空值 mysql空值如何表示_mysql_02

运算符的优先级

  • 决定了不同的运算符在计算过程中的先后顺序
  • 优先级高的先运算，同级的按从左到右进行计算
  • 可以使用()小括号来改变计算优先级
  • 运算符的优先级

优先级

运算符

优先级

运算符

1

！

8

丨

2

~

9

=,<=>,>=,>,<=,<,<>,!=,IS,like,regexp,in

3

^

10

between,case,when,then,else

4

*,/(DIV),%(MOD)

11

not

5

+,-

12

&&,and

6

>>,<<

13

丨丨,or,xor

7

&

14

:=