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 |
+-----+-------+---------+-------------+----------+
从以上查询可以看出来:
- 如果两者都是整数,则按照整数值进行比较
- 如果一个整数一个字符串,则会自动将字符串转换为数字,再进行比较
- 如果两者都是字符串,则按照字符串进行比较
- 如果两者中至少有一个值是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,否则返回 0
- 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
- 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,in |
3 | ^ | 10 | between,case,when,then,else |
4 | *,/(DIV),%(MOD) | 11 | not |
5 | +,- | 12 | &&,and |
6 | >>,<< | 13 | 丨丨,or,xor |
7 | & | 14 | := |