mysql 函数（操作符讲解）

mysql 函数

操作符有优先级

如果想改变优先级执行顺序，可以使用括号
select 1+2*3
select （1+2）*3

符号代表

对比操作符的执行结果为true，false，null三种

1. between a and b 代表检查值是否在a和b之间
2. coalesce() 代表返回第一个非null的值
3. =代表相等操作符
4. >代表大于操作符
5. >=代表大于等于操作符
6. greatest（）代表返回最大的值
7. in（）代表检查值是否在一系列的值之中
8. interval（）代表返回比第一个参数小的参数的位置
9. is/is not代表检查值是否与布尔值相同/不同
10. Is not null代表检查值是否是非NULL
11. Is null代表检查值是否是NULL
12. Isnull()代表检查参数是NULL
13. Least()代表返回最小的参数
14. <代表小于操作符
15. <=代表小于等于操作符
16. Like代表字符（通配符）匹配
17. Not between A and B代表检查值是否不在A和B的范围之内
18. !=/<>代表不等于操作符
19. Not in()代表检查值是否不在一系列值的当中
20. Not like代表检查值是否不匹配
21. Strcmp()对比两个字符串

例：

1. select * from students where sid=1；  ##找出sid等于1的值
2. select * from students where sid<>1; ##找出sid不等于1的值
3. select * from students where sid!=1;  ##也是找出sid不等于1的值
4. mysql> select * from students ;  ##sname2是有值得

+--------+-------+---------+--------+
| sid    | sname | dept_id | sname2 |
+--------+-------+---------+--------+
|      1 | bc    |       1 | NULL   |
|      2 | aaa   |       2 | NULL   |
|      3 | aa    |       1 | NULL   |
|     11 | bbb   |       1 | NULL   |
| 100001 | c     |       1 | NULL   |
| 980001 | a     |       1 | NULL   |
| 980002 | a     |       1 | NULL   |
| 990001 | c     |       1 | NULL   |
+--------+-------+---------+--------+
8 rows in set (0.00 sec)

5、mysql> select * from students where sname2=null;  ##单独查找出sname2又是空的（不能写=null）

Empty set (0.01 sec)
6、select * from students where sname2 is null;  ##可以写成 is null

7、select * from students where sname2 is not null;  ##或者 is not null

8、mysql> SELECT 1 = 0;  ##返回0就代表false
-> 0
9、mysql> SELECT '0' = 0;  ##字符‘0’会被转换成0再和0作对比，返回true
-> 1
10、mysql> SELECT '0.0' = 0;
-> 1
11、mysql> SELECT '0.01' = 0;
-> 0
12、mysql> SELECT '.01' = 0.01;
-> 1

<>/!=号（不相等）对比操作符
13、mysql> SELECT '.01' <> '0.01';
-> 1
14、mysql> SELECT .01 <> '0.01';
-> 0
15、mysql> SELECT 'zapp' <> 'zappp';
-> 1

is操作符
• mysql> SELECT 1 IS TRUE, 0 IS FALSE, NULL IS UNKNOWN;
• -> 1, 1, 1

 Is not操作符
• mysql> SELECT 1 IS NOT UNKNOWN, 0 IS NOT UNKNOWN, NULL IS
NOT UNKNOWN;
• -> 1, 1, 0
• Is null对比操作符
• mysql> SELECT 1 IS NULL, 0 IS NULL, NULL IS NULL;
• -> 0, 0, 1

• Is not null对比操作符
• mysql> SELECT 1 IS NOT NULL, 0 IS NOT NU
• -> 1, 1, 0
• Expr between min and max对比操作符
• 相当于min <= expr AND expr <= max
• mysql> SELECT 2 BETWEEN 1 AND 3, 2 BETWEE
• -> 1, 0
• mysql> SELECT 1 BETWEEN 2 AND 3;
• -> 0
• mysql> SELECT 'b' BETWEEN 'a' AND 'c';
• -> 1
• mysql> SELECT 2 BETWEEN 2 AND '3';
• -> 1
• mysql> SELECT 2 BETWEEN 2 AND 'x-3';
• -> 0
 

• expr NOT BETWEEN min AN
• 相当于NOT (expr BETWEE
• COALESCE(value,...)对比操
• 返回第一个非NULL的值，
• mysql> SELECT COALESCE(
• -> 2
• mysql> SELECT COALESCE(
• -> NULL
• GREATEST(value1,value2,...
• 返回其中最大的值
• mysql> SELECT GREATEST(
• -> 2
• mysql> SELECT GREATEST(
• -> 767.0
• mysql> SELECT GREATEST(
• -> 'C'
 

expr IN (value,...)对比操作符
• 当expr值能在values中找到，则返回1，否则返回0
• mysql> SELECT 2 IN (0,3,5,7);
• -> 0
• mysql> SELECT 'wefwf' IN ('wee','wefwf','weg');
• -> 1
• mysql> SELECT (3,4) IN ((1,2), (3,4));
• -> 1
• mysql> SELECT (3,4) IN ((1,2), (3,5));
• -> 0
• expr NOT IN (value,...)对比操作符
 

• Isnull(expr)操作符
• 如果expr是null，则
• mysql> SELECT ISNUL
• -> 0
• mysql> SELECT ISNUL
• -> 1
• LEAST(value1,value2,
• 返回最小值，如果其
• mysql> SELECT LEAST
• -> 0
• mysql> SELECT LEAST
• -> 3.0
• mysql> SELECT LEAST
• -> 'A'
 

逻辑操作符返回1 (TRUE), 0 (FALSE), 或者NULL

 

• NOT, !逻辑操作符代表非操作
• mysql> SELECT NOT 10;
• -> 0
• mysql> SELECT NOT 0;
• -> 1
• mysql> SELECT NOT NULL;
• -> NULL
• mysql> SELECT ! (1+1);
• -> 0
• mysql> SELECT ! 1+1;
• -> 1
 

• And,&&逻辑操作符
• mysql> SELECT 1 AND 1;
• -> 1
• mysql> SELECT 1 AND 0;
• -> 0
• mysql> SELECT 1 AND NULL;
• -> NULL
• mysql> SELECT 0 AND NULL;
• -> 0
• mysql> SELECT NULL AND 0;
• -> 0
例：select * from  students where sid=2 and sname='bc';  ##必须满足sid=2和sname=‘bc’两个条件，不然后返回空值

       select * from  students where sid=2 or sname='bc';  ##满足其中一个条件，就会返回满足条件的值（两个满足了条件的值都会返回）

---

分配操作符
分配操作符做赋值操作

:=是赋值操作  =在有些情况下也是赋值操作

例：

mysql> select @a;  ##定义变量

+------+
| @a   |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

mysql> set @a:=1;  ##赋值
Query OK, 0 rows affected (0.01 sec)

mysql> select @a;  ##查询变量

+------+
| @a   |
+------+
|    1 |
+------+
1 row in set (0.01 sec)

set @a:='a';  ##赋值

定义变量并且赋值
select @a:=count(*) from  students; ##把select count(*) from  students;得出的值赋予@a
update students set sname='abc' where sid=1;  ##在update中赋值是=号

##在一般的操作中是:=做赋值

• mysql> SELECT @var1, @var2;  ##@后跟上字符或者字符串可以定义变量
• -> NULL, NULL
• mysql> SELECT @var1 := 1, @var2;
• -> 1, NULL
• mysql> SELECT @var1, @var2;
• -> 1, NULL
• mysql> SELECT @var1, @var2 := @var1;
• -> 1, 1
• mysql> SELECT @var1, @var2;
• -> 1, 1
• mysql> SELECT @var1:=COUNT(*) FROM t1;
• -> 4
• mysql> SELECT @var1;
• -> 4
01

---

流程控制函数

流程控制函数包含以下四种函数（逻辑判断）

case和if作用相似写法不一

CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN result ...] [ELSE result] END
##如果value等于 [compare_value]就返回result，如果不等于这个值是等于下一个[compare_value]时候就返回result

课件
CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN result ...] [ELSE result] END
##当value等于compare_value时，则返回result，否则返回else里的result，如果没有else子句则返回null
例：

mysql> select case 1 when 1 then 'aa' when 2 then 'bb' else 'cc' end;
+--------------------------------------------------------+
| case 1 when 1 then 'aa' when 2 then 'bb' else 'cc' end |
+--------------------------------------------------------+
| aa                                                     |
+--------------------------------------------------------+
1 row in set (0.00 sec)
##如果 case 1等于1就返回aa，如果等于2就返回bb，如果它不等于1也不等于2就返回cc

mysql> select case dept_id when 1 then 'english' when 2 then 'accounting' else 'unknown'  end  from  students;
+---------------------------------------------------------------------------------+
| case dept_id when 1 then 'english' when 2 then 'accounting' else 'unknown'  end |
+---------------------------------------------------------------------------------+
| english                                                                         |
| english                                                                         |
| english                                                                         |
| english                                                                         |
| english                                                                         |
| english                                                                         |
| english                                                                         |
| accounting                                                                      |
+---------------------------------------------------------------------------------+
8 rows in set (0.01 sec)

##查询students表中dept_id等于1就返回English如果等于2就返回accounting，都没有就返回unknown

mysql> select dept_id,case dept_id when 1 then 'english' when 2 then 'accounting' else 'unknown'  end  from  students;
+---------+---------------------------------------------------------------------------------+
| dept_id | case dept_id when 1 then 'english' when 2 then 'accounting' else 'unknown'  end |
+---------+---------------------------------------------------------------------------------+
|       1 | english                                                                         |
|       1 | english                                                                         |
|       1 | english                                                                         |
|       1 | english                                                                         |
|       1 | english                                                                         |
|       1 | english                                                                         |
|       1 | english                                                                         |
|       2 | accounting                                                                      |
+---------+---------------------------------------------------------------------------------+
8 rows in set (0.01 sec)

case的另一种写法

 select sname,case when dept_id>=1 and dept_id<2 then 'english' when dept_id>=2 then 'accounting' else 'unknown' end from students;
+-------+--------------------------------------------------------------------------------------------------------+
| sname | case when dept_id>=1 and dept_id<2 then 'english' when dept_id>=2 then 'accounting' else 'unknown' end |
+-------+--------------------------------------------------------------------------------------------------------+
| abc   | english                                                                                                |
| aaa   | accounting                                                                                             |
| aa    | english                                                                                                |
| bbb   | english                                                                                                |
| c     | english                                                                                                |
| a     | english                                                                                                |
| a     | english                                                                                                |
| c     | english                                                                                                |
+-------+--------------------------------------------------------------------------------------------------------+
8 rows in set (0.00 sec)

mysql> select sname,case when dept_id>=1 and dept_id<=2 then 'english' when dept_id>=2 then 'accounting' else 'unknown' end,dept_id from students;

##当1行判断满足多个条件就返回第一个条件的值，不会往下匹配了
+-------+---------------------------------------------------------------------------------------------------------+---------+
| sname | case when dept_id>=1 and dept_id<=2 then 'english' when dept_id>=2 then 'accounting' else 'unknown' end | dept_id |
+-------+---------------------------------------------------------------------------------------------------------+---------+
| abc   | english                                                                                                 |       1 |
| aaa   | english                                                                                                 |       2 |
| aa    | english                                                                                                 |       1 |
| bbb   | english                                                                                                 |       1 |
| c     | english                                                                                                 |       1 |
| a     | english                                                                                                 |       1 |
| a     | english                                                                                                 |       1 |
| c     | english                                                                                                 |       1 |
+-------+---------------------------------------------------------------------------------------------------------+---------+
8 rows in set (0.01 sec)
流程控制if

if有三个参数

例：

mysql> select sname,if(dept_id>=2,'english','accounting'),dept_id from students;  ##dept_id大于等于2显示English，不是就显示accounting
+-------+---------------------------------------+---------+
| sname | if(dept_id>=2,'english','accounting') | dept_id |
+-------+---------------------------------------+---------+
| bc    | accounting                            |       1 |
| aaa   | english                               |       2 |
| aa    | accounting                            |       1 |
| c     | accounting                            |       1 |
| a     | accounting                            |       1 |
| a     | accounting                            |       1 |
| c     | accounting                            |       1 |
+-------+---------------------------------------+---------+
7 rows in set (0.00 sec)

mysql> select sname,if(dept_id>=1,'english','accounting'),dept_id from students;
+-------+---------------------------------------+---------+
| sname | if(dept_id>=1,'english','accounting') | dept_id |
+-------+---------------------------------------+---------+
| bc    | english                               |       1 |
| aaa   | english                               |       2 |
| aa    | english                               |       1 |
| c     | english                               |       1 |
| a     | english                               |       1 |
| a     | english                               |       1 |
| c     | english                               |       1 |
+-------+---------------------------------------+---------+
7 rows in set (0.00 sec)

流程控制语句nullif（不经常用到）

例：

mysql> select nullif(1,1); ##1等于1就返回null
+-------------+
| nullif(1,1) |
+-------------+
|        NULL |
+-------------+
1 row in set (0.00 sec)

mysql> select nullif(1,2);  ##1不等于2就返回第一个表达式1
+-------------+
| nullif(1,2) |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

---

字符串函数

ASCII（转换）

返回str字符串中最左边字符的ASCII码值，如果是空串则返回0，如果str是null则返回null

mysql> select ascii('2');
+------------+
| ascii('2') |
+------------+
|         50 |
+------------+
1 row in set (0.02 sec)

mysql> select ascii('b');  ##字符转换成数字
+------------+
| ascii('b') |
+------------+
|         98 |
+------------+
1 row in set (0.00 sec)

将字符转换成ascii码

例：

mysql> select char('97');  ##数字转换成字符
+------------+
| char('97') |
+------------+
| a          |
+------------+
1 row in set (0.00 sec)

经常用到

char_length返回字符长度而不是字节长度

例：

mysql> select char_length('abc');
+--------------------+
| char_length('abc') |
+--------------------+
|                  3 |
+--------------------+
1 row in set (0.00 sec)

mysql> select * from students where char_length(sname)=3;  ##找出students表中sname字符等于3的值
+-----+-------+--------+---------+--------+
| sid | sname | gender | dept_id | sname1 |
+-----+-------+--------+---------+--------+
|   2 | aaa   | 2      |       2 | NULL   |
+-----+-------+--------+---------+--------+
1 row in set (0.00 sec)
 

concat 

例：

mysql> select concat('my','s','ql'); ##把字符串拼到一起
+-----------------------+
| concat('my','s','ql') |
+-----------------------+
| mysql                 |
+-----------------------+
1 row in set (0.00 sec)

mysql> select concat('my',null,'ql');  ##字符串中有一个值是null，就返回null
+------------------------+
| concat('my',null,'ql') |
+------------------------+
| NULL                   |
+------------------------+
1 row in set (0.00 sec)

mysql> select concat(sname1,sname) from students;  ##字段sname1和sname的拼接
+----------------------+
| concat(sname1,sname) |
+----------------------+
| wubc                 |
| wangaaa              |
| NULL                 |
| c                    |
| NULLa                |
| NULLa                |
| NULLc                |
+----------------------+
7 rows in set (0.00 sec)

mysql> select concat(sname1,' ',sname) from students;  ##字段sname1和sname的拼接，中间加个空格

mysql> select concat(ifnull(sname1,''),' ',sname) from students;
+-------------------------------------+
| concat(ifnull(sname1,''),' ',sname) |
+-------------------------------------+
| wu bc                               |
| wang aaa                            |
|  aa                                 |
|  c                                  |
| NULL a                              |
| NULL a                              |
| NULL c                              |
+-------------------------------------+
7 rows in set (0.00 sec)
 

concat_ws 规定了用什么分隔符连接各个字符串，遇到null值会把null忽略

mysql> select concat_ws(',',sname1,sname) from students;  ##用逗号做分隔符
+-----------------------------+
| concat_ws(',',sname1,sname) |
+-----------------------------+
| wu,bc                       |
| wang,aaa                    |
| aa                          |
| ,c                          |
| NULL,a                      |
| NULL,a                      |
| NULL,c                      |
+-----------------------------+
7 rows in set (0.00 sec)

mysql> select concat(ifnull(sname1,''),',',sname) from students;  ##ifnull也用逗号做分割
+-------------------------------------+
| concat(ifnull(sname1,''),',',sname) |
+-------------------------------------+
| wu,bc                               |
| wang,aaa                            |
| ,aa                                 |
| ,c                                  |
| NULL,a                              |
| NULL,a                              |
| NULL,c                              |
+-------------------------------------+
7 rows in set (0.00 sec)