持续更新中。。。
函数 | 描述 | 实例 |
---|---|---|
& | 两位同时为“1”,结果才为“1”,否则为0 | 3&5:0011 & 0101 = 0001 所以3&5=1 mysql> select 3 & 5; +-------+ | 3 & 5 | +-------+ | 1 | +-------+ 1 row in set (0.00 sec) |
> | 大于 | mysql> select 3 > 5; +-------+ | 3 > 5 | +-------+ | 0 | +-------+ 1 row in set (0.00 sec) |
>> | 按位右移 | mysql> select 3>>1; +------+ | 3>>1 | +------+ | 1 | +------+ |
>= | 大于等于 | mysql> select 3 >= 5; +--------+ | 3 >= 5 | +--------+ | 0 | +--------+ |
< | 小于 | mysql> select 3<5; +-----+ | 3<5 | +-----+ | 1 | +-----+ |
<> ,!= | 不等于 | mysql> select 2<>3,2!=3; +------+------+ | 2<>3 | 2!=3 | +------+------+ | 1 | 1 | +------+------+ |
<< | 按位左移 | mysql> select 3<<1; +------+ | 3<<1 | +------+ | 6 | +------+ |
<= | 小于等于 | mysql> select 2<=3; +------+ | 2<=3 | +------+ | 1 | +------+ |
<=> | 严格比较两个NULL值是否相等 两个操作码均为NULL时,其所得值为1;而当一个操作码为NULL时,其所得值为0 | 与 = 的区别在于当两个操作码均为 NULL 时,其所得值为 1 而不为 NULL,而当一个操作码为 NULL 时,其所得值为 0而不为 NULL mysql> select 2<=>3,null=null,null<=>null; +-------+-----------+-------------+ | 2<=>3 | null=null | null<=>null | +-------+-----------+-------------+ | 0 | NULL | 1 | +-------+-----------+-------------+ |
% , MOD | 取余 | mysql> select 10 MOD 4,10 % 4; +----------+--------+ | 10 MOD 4 | 10 % 4 | +----------+--------+ | 2 | 2 | +----------+--------+ |
* | 乘法 | mysql> select 2*3; +-----+ | 2*3 | +-----+ | 6 | +-----+ |
+ | 加法 | mysql> select 1+2; +-----+ | 1+2 | +-----+ | 3 | +-----+ |
- | 减法 | mysql> select 1-2; +-----+ | 1-2 | +-----+ | -1 | +-----+ |
- | 更改参数的符号 | mysql> select --6,-6; +-----+----+ | --6 | -6 | +-----+----+ | 6 | -6 | +-----+----+ |
-> | 评估路径后从JSON列返回值;等效于JSON_EXTRACT()。 | |
->> | 评估路径并取消引用结果后,从JSON列返回值;等效于JSON_UNQUOTE(JSON_EXTRACT())。 | |
/ | 除法 | mysql> select 2/3; +--------+ | 2/3 | +--------+ | 0.6667 | +--------+ |
:= | 赋值 | |
= | 分配一个值(作为SET语句的一部分,或者作为UPDATE语句中SET子句的一部分) | |
= | 等于 | mysql> select 2=3; +-----+ | 2=3 | +-----+ | 0 | +-----+ |
^ | 按位异或 | mysql> select 3^5; +-----+ | 3^5 | +-----+ | 6 | +-----+ |
abs() | 返回绝对值 | mysql> select abs(-3),abs(3); +---------+--------+ | abs(-3) | abs(3) | +---------+--------+ | 3 | 3 | +---------+--------+ |
ACOS(x) | 求 x 的反余弦值(单位为弧度),x 为一个数值 | mysql> SELECT ACOS(0.25); +-------------------+ | ACOS(0.25) | +-------------------+ | 1.318116071652818 | +-------------------+ |
adddate(d,n) | 计算起始日期 d 加上 n 天的日期 | mysql> SELECT ADDDATE("2022-12-12",-10),ADDDATE("2022-12-12",10); +---------------------------+--------------------------+ | ADDDATE("2022-12-12",-10) | ADDDATE("2022-12-12",10) | +---------------------------+--------------------------+ | 2022-12-02 | 2022-12-22 | +---------------------------+--------------------------+ |
addtime(t,n) | n 是一个时间表达式,时间 t 加上时间表达式 n | mysql> SELECT ADDTIME(now(), 5); +---------------------+ | ADDTIME(now(), 5) | +---------------------+ | 2022-12-13 16:30:28 | +---------------------+ |
aes_decrypt | 解密,把……解密 | |
aes_encrypt() | 加密,把……加密 | |
and,&& | 逻辑与 | mysql> select 2 and 0,2 && 0; +---------+--------+ | 2 and 0 | 2 && 0 | +---------+--------+ | 0 | 0 | +---------+--------+ |
any_value() | ANY_VALUE() 函数对于启用了ONLY_FULL_GROUP_BY 模式,使用GROUP BY 进行查询时很有用;该函数用于抑制启用ONLY_FULL_GROUP_BY 模式时导致的值拒绝 | mysql> select any_value(SID),cid ,avg(score)from sc s group by cid; +----------------+------+------------+ | any_value(SID) | cid | avg(score) | +----------------+------+------------+ | 01 | 01 | 64.50000 | | 01 | 02 | 72.66667 | | 01 | 03 | 68.50000 | +----------------+------+------------+ |
ASCII() | 返回字符串的第一个字符的 ASCII 码 | mysql> select ascii('abc'); +--------------+ | ascii('abc') | +--------------+ | 97 | +--------------+ |
ASIN() | 求反正弦值(单位为弧度) | mysql> SELECT ASIN(0.25); +---------------------+ | ASIN(0.25) | +---------------------+ | 0.25268025514207865 | +---------------------+ |
ATAN() | 求反正切值(单位为弧度) | mysql> SELECT ATAN(2.5); +--------------------+ | ATAN(2.5) | +--------------------+ | 1.1902899496825317 | +--------------------+ |
ATAN2(n, m) | 求反正切值(单位为弧度) | mysql> SELECT ATAN2(-0.8, 2); +---------------------+ | ATAN2(-0.8, 2) | +---------------------+ | -0.3805063771123649 | +---------------------+ |
avg() | 获取列表平均值 | mysql> select avg(score)from sc group by sid; +------------+ | avg(score) | +------------+ | 89.66667 | | 70.00000 | | 80.00000 | | 33.33333 | | 81.50000 | | 32.50000 | | 93.50000 | +------------+ |
benchmark() | 测试数据库中特定表达式的执行时间 | mysql> select benchmark(100000000,1+1); +--------------------------+ | benchmark(100000000,1+1) | +--------------------------+ | 0 | +--------------------------+ 1 row in set (6.07 sec) |
between ...and... | 会选取介于两个值之间的数据范围。这些值可以是数值、文本或者日期 | mysql> select 0 between 0 and 3,3 between 0 and 3; +-------------------+-------------------+ | 0 between 0 and 3 | 3 between 0 and 3 | +-------------------+-------------------+ | 1 | 1 | +-------------------+-------------------+ |
BIN(x) | 返回 x 的二进制编码 | mysql> SELECT BIN(15); +---------+ | BIN(15) | +---------+ | 1111 | +---------+ |
BIN_TO_UUID() | 转换二进制UUID为字符串 | |
binary | 将 string 转换为 二进制字符串。 | |
bit_and() | 返回表达式的所有位按位AND结果 | |
bit_count(expr) | 返回 expr 的二进制表达式中”1“的个数 | mysql> select bit_count(3),bit_count(2); +--------------+--------------+ | bit_count(3) | bit_count(2) | +--------------+--------------+ | 2 | 1 | +--------------+--------------+ |
bit_length() | 查找给定字符串的长度,单位为比特 | mysql> select bit_length('郭'),bit_length('a'); +-------------------+-----------------+ | bit_length('郭') | bit_length('a') | +-------------------+-----------------+ | 24 | 8 | +-------------------+-----------------+ |
bit_or() | 按位或处理两个长度相同的二进制数,两个相应的二进位都为 0,该位的结果值为 0,否则为 1 | |
bit_xor() | 按位异或处理两个长度相同的二进制数,两个相应的二进位只要不同,该位的结果值为 1,否则为 0 | |
CAN_ACCESS_COLUMN() | 限内部使用 | |
CAN_ACCESS_DATABASE() | 限内部使用 | |
CAN_ACCESS_TABLE() | 限内部使用 | |
CAN_ACCESS_USER() | 限内部使用 | |
CAN_ACCESS_VIEW() | 限内部使用 | |
CASE | 案例操作员 | |
CAST() | 将值转换为特定类型 | |
CEIL() | 返回大于或等于指定数字的最小整数值 | mysql> select ceil(3.14); +------------+ | ceil(3.14) | +------------+ | 4 | +------------+ |
CEILING() | 返回大于或等于指定数字的最小整数值 | mysql> select ceiling(3.14); +---------------+ | ceiling(3.14) | +---------------+ | 4 | +---------------+ |
CHAR() | 函数将每个参数对应的整数转为对应的字符,并将这些字符组成一个字符串返回 | |
CHAR_LENGTH() | 返回的是字符串中字符的数量,与字符编码无关 | mysql> select char_length('abc'); +--------------------+ | char_length('abc') | +--------------------+ | 3 | +--------------------+ |
CHARACTER_LENGTH() | 返回的是字符串中字符的数量,与字符编码无关 | 同CHAR_LENGTH() |
CHARSET() | 返回参数的字符集 | mysql>select charset('abc'); +----------------+ | charset('abc') | +----------------+ | utf8mb4 | +----------------+ |
COALESCE() | 返回第一个非 NULL 参数 | mysql>select COALESCE(null,6,null); +-----------------------+ | COALESCE(null,6,null) | +-----------------------+ | 6 | +-----------------------+ |
COERCIBILITY() | 返回字符串参数的排序规则强制值 | mysql> |
COLLATION() | 返回字符串参数的排序规则 | mysql> |