MySQL进阶查询(null值、正则表达式和各种运算符)案例兼注解

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 |
+-----+-------+---------+-------------+----------+

从以上查询可以看出来:

  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 |
+------+-------+

右移左移位运算

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

在这里插入图片描述
“<<” 左移位运算 (向左移动位置,空缺处补0)

运算符的优先级

  • 决定了不同的运算符在计算过程中的先后顺序
  • 优先级高的先运算,同级的按从左到右进行计算
  • 可以使用()小括号来改变计算优先级
  • 运算符的优先级
优先级运算符优先级运算符
18
2~9=,<=>,>=,>,<=,<,<>,!=,IS,like,regexp,in
3^10between,case,when,then,else
4*,/(DIV),%(MOD)11not
5+,-12&&,and
6>>,<<13丨丨,or,xor
7&14:=
  • 3
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值