获取最大值/最小值
least : 当有两个参数或者多个参数时,返回最小值,当有一个值为null,则返回为null
greatest:当有两个参数或者多个参数时,返回最大值,当有一个值为null,则返回为null
mysql> select least(2,5,10),least('b','d','e'),least(null,6);
+---------------+--------------------+---------------+
| least(2,5,10) | least('b','d','e') | least(null,6) |
+---------------+--------------------+---------------+
| 2 | b | NULL |
+---------------+--------------------+---------------+
1 row in set (0.00 sec)
mysql> select greatest(1,6,10),greatest('b','d','e');
+------------------+-----------------------+
| greatest(1,6,10) | greatest('b','d','e') |
+------------------+-----------------------+
| 10 | e |
+------------------+-----------------------+
1 row in set (0.00 sec)
in:判断一个值是否在in列表中,如果值在列表中,返回值为1,否则返回值为0
not in:判断一个值是否不在在in列表中,如果值不在列表中,返回值为1,否则返回值为0
mysql> select 6 in (1,2,3,4,5,6) as a,10 in (1,2,3,4,5,6) as b, null in (1,2,3,4,5,6) as c, 5 in (1,2,3,4,5,6,null) as d,7 in (1,2,3,4,5,6,null) as d;
+---+---+------+------+------+
| a | b | c | d | d |
+---+---+------+------+------+
| 1 | 0 | NULL | 1 | NULL |
+---+---+------+------+------+
1 row in set (0.01 sec)
mysql> select 5 not in (1,2,3,4,5,6) as a, 10 not in (1,2,3,4,5,6) as b,null not in
(1,2,3,4,5,6) as c;
+---+---+------+
| a | b | c |
+---+---+------+
| 0 | 1 | NULL |
+---+---+------+
1 row in set (0.00 sec)
like:通配符:通配符用来匹配字符串,如果匹配则返回值为1,否则返回值为0
like:常用的通配符:%用于匹配0个或者多个任意字符
_:用与匹配1个任意字符
mysql> select 'hello' like 'hel%';
+---------------------+
| 'hello' like 'hel%' |
+---------------------+
| 1 |
+---------------------+
1 row in set (0.01 sec)
mysql> select 'hello' like 'hel%','hello' like 'hel_';
+---------------------+---------------------+
| 'hello' like 'hel%' | 'hello' like 'hel_' |
+---------------------+---------------------+
| 1 | 0 |
+---------------------+---------------------+
1 row in set (0.00 sec)
mysql> select 'hello' like 'hel%','hello' like 'hel__';
+---------------------+----------------------+
| 'hello' like 'hel%' | 'hello' like 'hel__' |
+---------------------+----------------------+
| 1 | 1 |
+---------------------+----------------------+
1 row in set (0.00 sec)
regexp正则匹配:用来匹配字符串,如果匹配则返回值为1,否则返回值为0
常用的通配符
^……:以……开头
……$:以……结尾
.:匹配一个字符
*:匹配0个或者多个前面的字符
[……]:用于匹配在方括号内的任意字符
mysql> select 'hello' regexp '^h' as a,'hello' regexp '0$' as b,'hello' regexp '.' as c,'hello' regexp 'l*' as d,'hello' regexp 'p*' as e,'hello' regexp '[a,b,c,d]' as
f;
+---+---+---+---+---+---+
| a | b | c | d | e | f |
+---+---+---+---+---+---+
| 1 | 0 | 1 | 1 | 1 | 0 |
+---+---+---+---+---+---+
1 row in set (0.00 sec)
逻辑运算符
逻辑非(not 或者 !)
当操作为0时返回值为1,当操作数为非0时返回值为0,当操作数为null时返回值为null
mysql> select not 0,not 1,not null;
+-------+-------+----------+
| not 0 | not 1 | not null |
+-------+-------+----------+
| 1 | 0 | NULL |
+-------+-------+----------+
1 row in set (0.00 sec)
逻辑与(and 或者 && )
当所有的操作数都为非0时并且不为null,返回值为1
当有一个操作数或者多个操作数为0时,返回值为0
其余情况返回值为null
mysql> select 1 and 0,5 and 10,null and 5;
+---------+----------+------------+
| 1 and 0 | 5 and 10 | null and 5 |
+---------+----------+------------+
| 0 | 1 | NULL |
+---------+----------+------------+
1 row in set (0.00 sec)
逻辑或 (or 或者 |)
当两个操作数都为非空时,且任意一个操作数为非0时,结果返回值为1,否则返回值为0,当有一个操作数为null时,并且另一个操作数为非0时,结果返回值为1,否则返回值为null
当两个操作数为null时,结果返回值为null
mysql> select 1 or 5,1 or 0,null or 1,null or 0,null or null;
+--------+--------+-----------+-----------+--------------+
| 1 or 5 | 1 or 0 | null or 1 | null or 0 | null or null |
+--------+--------+-----------+-----------+--------------+
| 1 | 1 | 1 | NULL | NULL |
+--------+--------+-----------+-----------+--------------+
1 row in set (0.00 sec)
逻辑异 (xor)
当任意一个操作数为null时,返回值为null,
对于非空的操作数,如果两个操作数都为非0时,或者都为0,返回结果都为0
如果一个操作数为0时,另一个操作数为非0时,则返回值为1
mysql> select null xor null,null xor 1,1 xor 5,0 xor 0,0 xor 1;
+---------------+------------+---------+---------+---------+
| null xor null | null xor 1 | 1 xor 5 | 0 xor 0 | 0 xor 1 |
+---------------+------------+---------+---------+---------+
| NULL | NULL | 0 | 0 | 1 |
+---------------+------------+---------+---------+---------+
1 row in set (0.00 sec)
位操作运算符
|:位或
&:位与
^:位异或
<<:位左移
>>:位右移
~:位取反
位或运算符(|):将对应的二进制位有一个或者两个位1时,则运算结果为1,否则为0
mysql> select 10 | 15;
+---------+ 将10转换为二进制为:1010,
| 10 | 15 | 将15转换为二进制为:1111
+---------+ 返回值为 1111,将1111转换为十进制为15,所以输出结果为15
| 15 |
+---------+
1 row in set (0.00 sec)
位与运算符(&):对应的二进制都为1时计算结果为1,否则为0
mysql> select 10 & 15;
+---------+
| 10 & 15 | 1010
+---------+ 1111
| 10 | 1010转换十进制为10,所以输出结果为10
+---------+
1 row in set (0.01 sec)
位异或运算符(^):对应的二进制位不同时,计算结果为1,否则为0
mysql> select 10 ^ 15;
+---------+
| 10 ^ 15 | 1010
+---------+ 1111
| 5 | 0101转换为十进制位5,所以输出结果为5
+---------+
1 row in set (0.00 sec)
位左移运算符(<<):将指定的二进制位向左移动指定的位数,左移指定的位数后,左边高位的数值将被移除,空余位置使用0补齐
mysql> select 1 << 2;
+--------+
| 1 << 2 | 00000001 00000100
+--------+ 100转换为十进制为4,输出结果为4
| 4 |
+--------+
1 row in set (0.00 sec)
位右移运算符(>>):将二进制为向右移动指定的位数,有以后,右边低位的数值将被丢弃,左边高位空出的位置使用0补齐
mysql> select 15 >>1 ;
+--------+
| 15 >>1 | 1111 111
+--------+ 111转换为十进制为7,所以输出结果为7
| 7 |
+--------+
1 row in set (0.00 sec)
位取反运算符(~):将对应的二进制位诸位反转,即1取反为0,0取反为1
mysql> select ~1;
+----------------------+
| ~1 | 将1转换为二进制,默认有64为将1前面补齐63个0
+----------------------+ 将63个0转换为1,将1转换为0
| 18446744073709551614 | 将1111…………1110转换为十进制,最后结果就是这个值
+----------------------+
1 row in set (0.00 sec)
mysql> select ~18446744073709551614;
+-----------------------+
| ~18446744073709551614 |
+-----------------------+
| 1 |
+-----------------------+
1 row in set (0.00 sec)
mysql的函数
数学函数
绝对值函数 :abs(x)
abs(x):返回x的绝对值
mysql> select abs(5),abs(-5);
+--------+---------+
| abs(5) | abs(-5) |
+--------+---------+
| 5 | 5 |
+--------+---------+
1 row in set (0.00 sec)
返回圆周率pi()
mysql> select pi();
+----------+
| pi() |
+----------+
| 3.141593 |
+----------+
1 row in set (0.00 sec)
平方根sqrt(x)返回非负数x的二次方
mysql> select sqrt(9),sqrt(3);
+---------+--------------------+
| sqrt(9) | sqrt(3) |
+---------+--------------------+
| 3 | 1.7320508075688772 |
+---------+--------------------+
1 row in set (0.00 sec)
求余函数:mod(x,y)
mod(x,y):返回x被y除后的余数
mysql> select mod(5,3);
+----------+
| mod(5,3) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)
获取整数的函数:ceil(x) ceiling(x) floor(x)
ceil(x):返回不小于x的最小整数
mysql> select ceil(-3.2),ceil(6.3);
+------------+-----------+
| ceil(-3.2) | ceil(6.3) |
+------------+-----------+
| -3 | 7 |
+------------+-----------+
1 row in set (0.00 sec)
ceiling(x):返回不小于x的最小整数
mysql> select ceiling(-3.2),ceiling(6.3);
+---------------+--------------+
| ceiling(-3.2) | ceiling(6.3) |
+---------------+--------------+
| -3 | 7 |
+---------------+--------------+
1 row in set (0.00 sec)
floor(x):返回不大于x的最大整数
mysql> select floor(-3.5),floor(6.5);
+-------------+------------+
| floor(-3.5) | floor(6.5) |
+-------------+------------+
| -4 | 6 |
+-------------+------------+
1 row in set (0.00 sec)
返回随机数的函数:rand() rand(x)
rand():用于返回一个随机的浮点数,范围0-1之间
mysql> select rand();
+-------------------+
| rand() |
+-------------------+
| 0.810707468363184 |
+-------------------+
1 row in set (0.00 sec)
mysql> select rand();
+--------------------+
| rand() |
+--------------------+
| 0.3244342052605322 |
+--------------------+
1 row in set (0.00 sec)
rand(x):返回一个随机的浮点数,范围0-1,x被作为种子,用来生产重复序列的值
mysql> select rand(1),rand(1),rand(5),rand(5);
+---------------------+---------------------+---------------------+---------------------+
| rand(1) | rand(1) | rand(5) | rand(5) |
+---------------------+---------------------+---------------------+---------------------+
| 0.40540353712197724 | 0.40540353712197724 | 0.40613597483014313 | 0.40613597483014313 |
+---------------------+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)
四舍五入的函数:round(x) round(x,y)
round(x):对x进行四舍五入
mysql> select round(3.56),round(2.23),round(pi ());
+-------------+-------------+--------------+
| round(3.56) | round(2.23) | round(pi ()) |
+-------------+-------------+--------------+
| 4 | 2 | 3 |
+-------------+-------------+--------------+
1 row in set (0.00 sec)
round(x,y):对x进行四舍五入,并且保留小数点后y位
mysql> select round(32.6562,3),round(pi (),10);
+------------------+-----------------+
| round(32.6562,3) | round(pi (),10) |
+------------------+-----------------+
| 32.656 | 3.1415926536 |
+------------------+-----------------+
1 row in set (0.00 sec)
获取数值的函数
truncate(x,y):对x进行截取,结果保留小数点后y位
mysql> select truncate(3.25698,2),truncate(pi (),10);
+---------------------+--------------------+
| truncate(3.25698,2) | truncate(pi (),10) |
+---------------------+--------------------+
| 3.25 | 3.1415926535 |
+---------------------+--------------------+
1 row in set (0.00 sec)