MariaDB数据类型与运算符

发表于 2018-12-24 更新于 2023-05-25 分类于《MySQL 数据库从入门到精通》

数据库表由多列字段构成,每一个字段指定了不同的数据类型.指定字段的数据类型之后,也就决定了向字段插入的数据内容,例如,当要插入数值的时候,可以将它们存储为整数类型,也可以将它们存储为字符串类型.不同的数据类型也决定了MySQL在存储它们的时候使用的方式,以及在使用它们的时候选择什么运算符号进行运算,下面的小结内容将介绍,常用的数据类型,和常用的运算符,在最后再看即可常用的MySQL系统函数的使用.

MySQL支持多种数据类型,主要分为几大类,包括:数值类型,日期时间类型,和字符串类型等.

数值类型(int):包括TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT
数值类型(float):包括FLOAT,DOUBLE,DECIMAL
日期时间型(time):包括YEAR,TIME,DATE,DATETIME,TIMESTAMP
字符串类型(string):包括CHAR,VARCHAR,BINARY,VARBINARY,BLOB,TEXT,ENUM,SET

MariaDB 数据类型

◆整数数据类型◆

数值型类型主要用来存储数字,MySQL提供了多种数值数据类型,不同的数据类型提供不同的取值范围,可以存储的值范围越大,其所需要的存储空间也会越大,MySQL主要提供的整形有:TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT,整数类型的属性字段可以添加AUTO_INCREMENT自增长约束条件,如下表所示:

类型名称	说明信息	存储占比
TINYINT	很小的整数	1个字节
SMALLINT	小的整数	2个字节
MEDIUMINT	中等大小整数	3个字节
INT	普通大小整数	4个字节
BIGINT	大整数	8个字节

上表可看出,不同类型的数据字节是不同的,整数类型的取值范围也是固定的,基本上分为有符号和无符号型,下表就是他们的相应取值范围,仅供参考:

数据类型	有符号	无符号
TINYINT	-128-127	0-255
SMALLINT	32768-32767	0-65535
MEDIUMINT	-8388608-8388607	0-16777215
INT	-2147483648-2147483647	0-4294967295
BIGINT	这个范围不多说,(大)	0-无法形容的大

实例1: 创建一个整数类型的表.

MariaDB [lyshark]> create table myint
    -> (
    -> uid int(10),
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.00 sec)

以上是uid就是一个整数类型的字段,注意后面的(10)意思是指定能够显示的数值中数字的个数.

实例2: 分别创建整形的数据类型字段看看.

MariaDB [lyshark]> create table temp
    -> (
    -> a tinyint,
    -> b smallint,
    -> c mediumint,
    -> d int,
    -> e bigint
    -> );
Query OK, 0 rows affected (0.00 sec)

MariaDB [lyshark]> desc temp;

+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| a     | tinyint(4)   | YES  |     | NULL    |       |
| b     | smallint(6)  | YES  |     | NULL    |       |
| c     | mediumint(9) | YES  |     | NULL    |       |
| d     | int(11)      | YES  |     | NULL    |       |
| e     | bigint(20)   | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

◆浮点数和定点数◆

在MySQL中浮点数和定点数都是用来表示小数的,浮点数类型有两种:单精度浮点数(FLOAT)和双精度浮点数(DOUBLE),定点类型的话只有一种(DECIMAL),下表是这几个数值的说明信息:

类型名称	说明信息	存储占比
FLOAT	单精度浮点数	4个字节
DOUBLE	双精度浮点数	8个字节
DECIMAL	压缩的定点数	M+2个字节

实例: 创建temp表,其中字段x,y,z数据类型分别是 float(5.1) double(5.1) decimal(5.1)并向表中插入一些数据.

MariaDB [lyshark]> create table temp
    -> (
    -> x float(5,1),
    -> y double(5,1),
    -> z decimal(5,1)
    -> );
Query OK, 0 rows affected (0.00 sec)

向表中插入数据，并查看结果,MySQL默认自动截断小数点后面的数据,具体截断位数由计算机硬件和操作系统决定.

MariaDB [lyshark]> insert into temp values(5.12,5.22,5.123);
Query OK, 1 row affected, 1 warning (0.01 sec)

MariaDB [lyshark]> select * from temp1;
+------+------+------+
| x    | y    | z    |
+------+------+------+
|  5.1 |  5.2 |  5.1 |
+------+------+------+
1 row in set (0.00 sec)

MariaDB [lyshark]>

◆日期与时间类型◆

MySQL中有多种表示日期的数据类型,主要有LDATETIME、DATE、TIME和YEAR.例如,当只记录年信息的时候,可以只使用 YEAR类型而没有必要使用DATE,每一个类型都有合法的取值范围,当指定确实不合法的值时系统将”0”值插入到数据库中,下面先来看一下他的类型说明吧:

类型名称	日期格式	日期范围	存储需求
YEAR	YYYY	1901-2155	1字节
TIME	HH:MM:SS	-838:59:59-838:59:59	3字节
DATE	YYYY-MM-DD	1000-01-01-9999-12-3	3字节
DATETIME	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00-9999:12-31 23:59:59	8字节
TIMESTAMP	YYYY-MM-DD HH:MM:SS	1970-01-01 00:00:01 UTC-2038-01-19 03:14:07 UTC	4字节

YEAR类型: 主要用于存储一个年份,例如：1997 2018

1.创建temp表,定义数据类型为year的字段x,并向表中插入数据.

MariaDB [lyshark]> create table temp(x year);                 #创建一个year类型的字段
Query OK, 0 rows affected (0.00 sec)

MariaDB [lyshark]> desc temp;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| x     | year(4) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.01 sec)

MariaDB [lyshark]> insert into temp values(2018),("2020");    #插入一些数据：注意必须是1901-2155之间的数
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [lyshark]> select * from temp;
+------+
| x    |
+------+
| 2018 |
| 2020 |
+------+
2 rows in set (0.00 sec)

TIME类型:主要用于存储时间,例如：12:12:21

1.创建temp1表,定义数据类型为time的字段x,并向表中插入数据.

MariaDB [lyshark]> create table temp1(                        #创建一个time类型的字段
    -> x time
    -> );
Query OK, 0 rows affected (0.01 sec)

MariaDB [lyshark]> desc temp1;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| x     | time | YES  |     | NULL    |       |
+-------+------+------+-----+---------+-------+
1 row in set (0.00 sec)

MariaDB [lyshark]> insert into temp1 values('11:22:05'),('23:23'),('20');   #分别插入数据：注意（%HH-%MM-%SS）
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

MariaDB [lyshark]> select * from temp1;
+----------+
| x        |
+----------+
| 11:22:05 |
| 23:23:00 |
| 00:00:20 |
+----------+
3 rows in set (0.00 sec)

2.当然啦我们可以简写省略冒号.

MariaDB [lyshark]> create table temp1(x time);
Query OK, 0 rows affected (0.01 sec)

MariaDB [lyshark]> insert into temp1 values('102231');
Query OK, 1 row affected (0.00 sec)

MariaDB [lyshark]> select * from temp1;
+----------+
| x        |
+----------+
| 10:22:31 |
+----------+
1 row in set (0.00 sec)

3.向temp表中的x字段插入当前系统运行时间,通过函数(CURRENT_TIME),(NOW()取出.

MariaDB [lyshark]> select * from temp;
Empty set (0.00 sec)

MariaDB [lyshark]> insert into temp values (CURRENT_TIME),(NOW());
Query OK, 2 rows affected, 1 warning (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 1

MariaDB [lyshark]> select * from temp;
+----------+
| x        |
+----------+
| 21:27:43 |
| 21:27:43 |
+----------+
2 rows in set (0.00 sec)

DATE类型:Date类型主要用于存储年月日,例如：1997-10-05

1.创建temp表,表中是date类型的x字段,并插入一条数据.

MariaDB [lyshark]> create table temp(x date);       #创建一个date类型的字段
Query OK, 0 rows affected (0.00 sec)

MariaDB [lyshark]> desc temp;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| x     | date | YES  |     | NULL    |       |
+-------+------+------+-----+---------+-------+
1 row in set (0.00 sec)

MariaDB [lyshark]> insert into temp values('1997-10-05'),('20180523');   #插入一些数据
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [lyshark]> select * from temp;
+------------+
| x          |
+------------+
| 1997-10-05 |
| 2018-05-23 |
+------------+
2 rows in set (0.00 sec)

2.向temp表中插入系统当前日期,通过函数(CURRENT_DATE()),(NOW())取出系统日期.

MariaDB [lyshark]> select * from temp;
+------------+
| x          |
+------------+
| 1997-10-05 |
| 2018-05-23 |
+------------+
2 rows in set (0.00 sec)

MariaDB [lyshark]> insert into temp values(CURRENT_DATE()),(NOW());    #取出系统当前日期并插入
Query OK, 2 rows affected, 1 warning (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 1

MariaDB [lyshark]> select * from temp;
+------------+
| x          |
+------------+
| 1997-10-05 |
| 2018-05-23 |
| 2018-06-16 |
| 2018-06-16 |
+------------+
4 rows in set (0.00 sec)

DATATIME:DateTime类型用于存储日期和时间,例如：2018-01-24 22:12:24

1.创建temp表dt字段类型为datetime,并插入一条数据.

MariaDB [lyshark]> create table temp(dt datetime);
Query OK, 0 rows affected (0.00 sec)

MariaDB [lyshark]> desc temp;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| dt    | datetime | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
1 row in set (0.00 sec)

MariaDB [lyshark]> insert into temp values('1997-05-10 10:22:14'),('20180616220101');   #插入日期时间
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [lyshark]> select * from temp;
+---------------------+
| dt                  |
+---------------------+
| 1997-05-10 10:22:14 |
| 2018-06-16 22:01:01 |
+---------------------+
2 rows in set (0.00 sec)

2.取系统当前日期并插入temp表的dt字段.

MariaDB [lyshark]> select * from temp;
+---------------------+
| dt                  |
+---------------------+
| 1997-05-10 10:22:14 |
| 2018-06-16 22:01:01 |
+---------------------+
2 rows in set (0.00 sec)

MariaDB [lyshark]> insert into temp values(now());    #取系统日期插入temp表的dt字段
Query OK, 1 row affected (0.00 sec)

MariaDB [lyshark]> select * from temp;
+---------------------+
| dt                  |
+---------------------+
| 1997-05-10 10:22:14 |
| 2018-06-16 22:01:01 |
| 2018-06-16 22:03:39 |
+---------------------+
3 rows in set (0.00 sec)

MariaDB [lyshark]>

TIMESTAMP类型:TimeStamp与DateTime相同,但是TimeStamp是使用的UTC(世界标准时间)

1.创建temp表并插入timestamp类型的x字段,插入一条数据.

MariaDB [lyshark]> create table temp(x timestamp);          #创建一个timestamp类型的字段
Query OK, 0 rows affected (0.00 sec)

MariaDB [lyshark]> desc temp;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type      | Null | Key | Default           | Extra                       |
+-------+-----------+------+-----+-------------------+-----------------------------+
| x     | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+
1 row in set (0.00 sec)

MariaDB [lyshark]> insert into temp values('2018-06-16 22:24:00');  #插入一条时间记录
Query OK, 1 row affected (0.01 sec)

MariaDB [lyshark]> select *from temp;
+---------------------+
| x                   |
+---------------------+
| 2018-06-16 22:24:00 |
+---------------------+
1 row in set (0.00 sec)

MariaDB [lyshark]> set time_zone='+12:00'                            #将时间上调12小时
    -> ;
Query OK, 0 rows affected (0.00 sec)

MariaDB [lyshark]> select * from temp;                              #再次查询已经是第二天了
+---------------------+
| x                   |
+---------------------+
| 2018-06-17 02:24:00 |
+---------------------+
1 row in set (0.00 sec)

◆文本字符串类型◆

字符串类型用来存储字符串数据,除了可以存储字符串数据之外,还可以存储其他数据,比如图片和声音的二进制数据.MySQL支持两类字符型数据:文本字符串和二进制字符串,本小节主要介绍文本字符串类型,文本字符串可以进行区分或者不区分大小写的串比较,另外还可以进行模式匹配查找.MysQL中文本字符串类型指CHAR,VARCHAR,TEXT,ENUM和SET,如下表所示.

类型名称	说明信息	存储需求
CHAR	固定长度非二进制字符串	M字节,1<=M<=255
VARCHAR	变长非二进制字符串	L+1字节
TIMYTEXT	非常小的非二进制字符串	L+1字节
TEXT	小的非二进制字符串	L+2字节
MEDIUMTEXT	中等非二进制字符串	L+3字节
LONGTEXT	大的非二进制字符串	L+4字节
ENUM	枚举类型	l或2个字节
SET	SET成员类型	1,2,3,4或8个字节

CHAR和VARCHAR:定长和不定长字符串类型

CHAR和VARCHAR的长度区别:

● CHAR是一种定长字符串,它的长度在初始化时就被固定比如说：char(10)则固定分配10个字符的长度,如果使用了CHAR类型,不论你的数据填充多少都会消耗4字节存储空间.
● VARCHAR是一种不定长字符串,它的长度取决于你输入的字符数,使用VARCHAR的话,它会动态的分配空间大小,但最大也不能超过定义的长度

1.定义一个temp表,里面有两个字段分别是ch,vch类型是char(4)和varchar(4)插入数据查看区别.

MariaDB [lyshark]> create table temp
    -> (
    -> ch char(4),
    -> vch varchar(4)
    -> );
Query OK, 0 rows affected (0.00 sec)

MariaDB [lyshark]> desc temp;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| ch    | char(4)    | YES  |     | NULL    |       |
| vch   | varchar(4) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

MariaDB [lyshark]> insert into temp values('xy ','xy ');
Query OK, 1 row affected (0.01 sec)

MariaDB [lyshark]> select *from temp;
+------+------+
| ch   | vch  |
+------+------+
| xy   | xy   |
+------+------+
1 row in set (0.00 sec)

TEXT类型:用于保存非二进制字符串,如文章内容评论内容等,当保存或查询text列的值时,不删除尾部空格.

关于TEXT类型的取值范围:

● TINYTEXT 最大长度为255(2^8-1)字符的TEXT列.
● TEXT 最大长度为65535(2^16-1)字符的TEXT列.
● MEDIUMTEXT 最大长度为16777215(2^24-1)字符的TEXT列.
● LONGTEXT 最大长度为4294967295字符的TEXT列.

1.创建一个表temp1,并创建text字段,写入一段话看看.

MariaDB [lyshark]> create table temp1(x text);
Query OK, 0 rows affected (0.02 sec)

MariaDB [lyshark]> desc temp1;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| x     | text | YES  |     | NULL    |       |
+-------+------+------+-----+---------+-------+
1 row in set (0.00 sec)

MariaDB [lyshark]> insert into temp1 values('hello lyshark')
    -> ;
Query OK, 1 row affected (0.00 sec)

MariaDB [lyshark]> select * from temp1;
+---------------+
| x             |
+---------------+
| hello lyshark |
+---------------+
1 row in set (0.00 sec)

ENUM枚举类型:enum的值根据列索引顺序排列,并且空字符串排在非空字符串前,NULL值排在其他所有的枚举值前面

1.来看一个枚举的小例子,注意:枚举默认标号从1开始.

MariaDB [lyshark]> create table temp2(enm enum('first','second','thire'));
Query OK, 0 rows affected (0.00 sec)

MariaDB [lyshark]> desc temp2;
+-------+--------------------------------+------+-----+---------+-------+
| Field | Type                           | Null | Key | Default | Extra |
+-------+--------------------------------+------+-----+---------+-------+
| enm   | enum('first','second','thire') | YES  |     | NULL    |       |
+-------+--------------------------------+------+-----+---------+-------+
1 row in set (0.00 sec)

MariaDB [lyshark]> insert into temp2 values('1'),('2'),('3'),(NULL);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

MariaDB [lyshark]> select * from temp2;
+--------+
| enm    |
+--------+
| first  |
| second |
| thire  |
| NULL   |
+--------+
4 rows in set (0.00 sec)

MariaDB [lyshark]>

set集合:但在声明成集合时,其取值就已经固定了

MariaDB [lyshark]> create table temp3(s set('a','b','c','d'));            #首先定义了一个集合，元素有abcd
Query OK, 0 rows affected (0.01 sec)

MariaDB [lyshark]> desc temp3;
+-------+----------------------+------+-----+---------+-------+
| Field | Type                 | Null | Key | Default | Extra |
+-------+----------------------+------+-----+---------+-------+
| s     | set('a','b','c','d') | YES  |     | NULL    |       |
+-------+----------------------+------+-----+---------+-------+
1 row in set (0.00 sec)

MariaDB [lyshark]> 
MariaDB [lyshark]> insert into temp3 values('a'),('a,b,c'),('a,b,c,d');   #分别插入3个不同的集合，看看
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

MariaDB [lyshark]> select * from temp3;
+---------+
| s       |
+---------+
| a       |
| a,b,c   |
| a,b,c,d |
+---------+
3 rows in set (0.00 sec)

MariaDB [lyshark]> insert into temp3 values('a,'f'');                     #在插入f时报错，因为集合中定义是没有f
ERROR 1064 (42000): You have an error in your SQL syntax;

◆二进制字串类型◆

在MySQL中的二进制数据类型有:BIT、BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB,LONGBLOB,老样子,看下面的表格,就清晰啦.

类型名称	说明信息	存储需求
BIT	位字段类型	(M+7/8)个字节
BINARY	固定长度二进制字符串	M个字节
VARBINARY	可变长二进制字符串	M+1字节
TINYBLOB	非常小的BLOB	L+1字节
BLOB	小BLOB	L+2字节
MEDIUMBLOB	中等大小的BLOB	L+3字节
LONGBLOB	非常大的BLOB	L+4字节

bit类型:位字段类型,也就是说插入的数据会被转换成101011011这样的格式

1.定义并插入数据测试,x+0表示将二进制结果转换为对应的数字的值,bin()函数将数字转换为2进制.

MariaDB [lyshark]> create table temp5(x bit(4));
Query OK, 0 rows affected (0.00 sec)

MariaDB [lyshark]> desc temp5;
+-------+--------+------+-----+---------+-------+
| Field | Type   | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| x     | bit(4) | YES  |     | NULL    |       |
+-------+--------+------+-----+---------+-------+
1 row in set (0.00 sec)

MariaDB [lyshark]> insert into temp5 values(100),(115),(10);
Query OK, 3 rows affected, 2 warnings (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 2

MariaDB [lyshark]> select BIN(x+0) from temp5;
+----------+
| BIN(x+0) |
+----------+
| 1111     |
| 1111     |
| 1010     |
+----------+
3 rows in set (0.00 sec)

BINARY和VARBINARY类型: 定长与不定长二进制字符串类型.

1.binary类型是一个定长,二进制字节字符串类型,在字段不足制定字节是会自动在后面填\0.
2.varbinary类型是一个可变长,二进制字节字符串类型,而vb字段不会填充.

创建一个temp10,分别有两个字段b,vb类型分别是binary(3)和varbinary(30)

MariaDB [lyshark]> create table temp10(
    -> b binary(3),
    -> vb varbinary(30)
    -> );
Query OK, 0 rows affected (0.00 sec)

MariaDB [lyshark]> desc temp10;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| b     | binary(3)     | YES  |     | NULL    |       |
| vb    | varbinary(30) | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

MariaDB [lyshark]> insert into temp10 values(5,5);
Query OK, 1 row affected (0.01 sec)

MariaDB [lyshark]> select length(b),length(vb) from temp10;     #可以看到b占用3字节，而vb是只占用1字节
+-----------+------------+
| length(b) | length(vb) |
+-----------+------------+
|         3 |          1 |
+-----------+------------+
1 row in set (0.00 sec)

MariaDB 运算符类型

运算符链接表达式中各个操作数,其作用是用来指明对操作数所进行的运算,运用运算符可以更加灵活的使用表中的数据,常见的运算符有:算术运算,比较运算,逻辑运算,位运算等,下面我们将依次介绍这几种运算符的运用.

◆算术运算符◆

运算符	作用
+	加法运算
-	减法运算
*	乘法运算
/	除法运算
%	求余运算

加法运算(+)

MariaDB [lyshark]> select * from temp;
+------+
| num  |
+------+
|  100 |
+------+
1 row in set (0.00 sec)

MariaDB [lyshark]> select num,num+10 from temp;
+------+--------+
| num  | num+10 |
+------+--------+
|  100 |    110 |
+------+--------+
1 row in set (0.00 sec)

减法运算(-)

MariaDB [lyshark]> select * from temp;
+------+
| num  |
+------+
|  100 |
+------+
1 row in set (0.01 sec)

MariaDB [lyshark]> select num,num-10 from temp;
+------+--------+
| num  | num-10 |
+------+--------+
|  100 |     90 |
+------+--------+
1 row in set (0.00 sec)

乘法运算(*)

MariaDB [lyshark]> select * from temp;
+------+
| num  |
+------+
|  100 |
+------+
1 row in set (0.00 sec)

MariaDB [lyshark]> select num,num*10 from temp;
+------+--------+
| num  | num*10 |
+------+--------+
|  100 |   1000 |
+------+--------+
1 row in set (0.00 sec)

除法运算(/)

MariaDB [lyshark]> select * from temp;
+------+
| num  |
+------+
|  100 |
+------+
1 row in set (0.00 sec)

MariaDB [lyshark]> select num,num/10 from temp;
+------+---------+
| num  | num/10  |
+------+---------+
|  100 | 10.0000 |
+------+---------+
1 row in set (0.00 sec)

取余数运算(%)

MariaDB [lyshark]> select * from temp;
+------+
| num  |
+------+
|  100 |
+------+
1 row in set (0.00 sec)

MariaDB [lyshark]> select num,num%10 from temp;
+------+--------+
| num  | num%10 |
+------+--------+
|  100 |      0 |
+------+--------+
1 row in set (0.00 sec)

◆比较运算符◆

运算符	作用
=	等于
<=>	安全的等于
<>(!=)	不等于
<=	小于等于
>=	大于等于
>	大于
IS NULL	判断一个值是否为NULL
IS NOT NULL	判断一个值是否不为NULL
LEAST	在有两个或多个参数时,返回最小值
GREATEST	当有两个或多个参数时,返回最大值
BETWEEN AND	判断一个值是否落在两个值之间
ISNULL	与IS NULL作用相同
IN	判断一个值是IN列表中的任意一个值
NOT IN	判断一个值不是IN列表中的任意一个值
LIKE	通配符匹配
REGEXP	正则表达式匹配

等于运算符(=): 使用等于运算符进行相等判断

MariaDB [lyshark]> select 1=1 , 1=0 , '1'=1 , '0.01'=0 , 'a'='a' , (1+1)=(2+2) , NULL=NULL;
+-----+-----+-------+----------+---------+-------------+-----------+
| 1=1 | 1=0 | '1'=1 | '0.01'=0 | 'a'='a' | (1+1)=(2+2) | NULL=NULL |
+-----+-----+-------+----------+---------+-------------+-----------+
|   1 |   0 |     1 |        0 |       1 |           0 |      NULL |
+-----+-----+-------+----------+---------+-------------+-----------+
1 row in set (0.00 sec)

全等于(<=>): 这个运算符和=功能相同,但是全等于可以用来判断NULL值,而等于是不能的

MariaDB [lyshark]> select 1<=>0 , 1<=>1 , '1'<=>1 , '0.01' <=> 0 , 'a' <=> 'a' , (10+10)<=>(20+20) , NULL<=>NULL ;
+-------+-------+---------+--------------+-------------+-------------------+-------------+
| 1<=>0 | 1<=>1 | '1'<=>1 | '0.01' <=> 0 | 'a' <=> 'a' | (10+10)<=>(20+20) | NULL<=>NULL |
+-------+-------+---------+--------------+-------------+-------------------+-------------+
|     0 |     1 |       1 |            0 |           1 |                 0 |           1 |
+-------+-------+---------+--------------+-------------+-------------------+-------------+
row in set (0.00 sec)

不等于(<>或!=): 俩数不相等返回1,相等返回0

MariaDB [lyshark]> select 'lyshark' != 'admin' , 1!=2 , 1 <> 1 , (10+10)<>(10+10) , NULL!=NULL;
+----------------------+------+--------+------------------+------------+
| 'lyshark' != 'admin' | 1!=2 | 1 <> 1 | (10+10)<>(10+10) | NULL!=NULL |
+----------------------+------+--------+------------------+------------+
|                    1 |    1 |      0 |                0 |       NULL |
+----------------------+------+--------+------------------+------------+
1 row in set (0.00 sec)

小于运算符(<): 两数相比较,左边小于右边返回1,否则返回0

MariaDB [lyshark]> select 'xxx' < 'xxxx' , 1<2 , 1<1 , 5.5<5 , (1+1)<(10-10) , NULL <NULL ;
+----------------+-----+-----+-------+---------------+------------+
| 'xxx' < 'xxxx' | 1<2 | 1<1 | 5.5<5 | (1+1)<(10-10) | NULL <NULL |
+----------------+-----+-----+-------+---------------+------------+
|              1 |   1 |   0 |     0 |             0 |       NULL |
+----------------+-----+-----+-------+---------------+------------+
1 row in set (0.00 sec)

小于等于(<=): 两数相比较,左边小于或者等于右边返回1,否则返回0

MariaDB [lyshark]> select 'xxxx' <= 'xxxx' , 1<=1 , 1<=2 , 5.5<=5 , NULL<=NULL;
+------------------+------+------+--------+------------+
| 'xxxx' <= 'xxxx' | 1<=1 | 1<=2 | 5.5<=5 | NULL<=NULL |
+------------------+------+------+--------+------------+
|                1 |    1 |    1 |      0 |       NULL |
+------------------+------+------+--------+------------+
1 row in set (0.00 sec)

大于运算符(>): 两数相比较,左边大于右边返回1,否则返回0

MariaDB [lyshark]> select 'xxxx' > 'xxx' , 5>1 , 10>10 , NULL > NULL;
+----------------+-----+-------+-------------+
| 'xxxx' > 'xxx' | 5>1 | 10>10 | NULL > NULL |
+----------------+-----+-------+-------------+
|              1 |   1 |     0 |        NULL |
+----------------+-----+-------+-------------+
1 row in set (0.00 sec)

大于等于(>=): 两数相比较,左边大于或者等于右边返回1,否则返回0

MariaDB [lyshark]> select 'xxxx' >= 'xxxx' , 1>=1 , 1>=10 , NULL>=NULL;
+------------------+------+-------+------------+
| 'xxxx' >= 'xxxx' | 1>=1 | 1>=10 | NULL>=NULL |
+------------------+------+-------+------------+
|                1 |    1 |     0 |       NULL |
+------------------+------+-------+------------+
1 row in set (0.00 sec)

IS NULL运算符(ISNULL)和IS NOT NULL运算符(ISNOTNULL): is null如果为NULL返回1否则返回0,而is not null则相反.

MariaDB [lyshark]> select null is null , isnull(null) , isnull(1) , 1 is not null;
+--------------+--------------+-----------+---------------+
| null is null | isnull(null) | isnull(1) | 1 is not null |
+--------------+--------------+-----------+---------------+
|            1 |            1 |         0 |             1 |
+--------------+--------------+-----------+---------------+
1 row in set (0.00 sec)

between and 运算符(expr BETWEEN min AND max): 假如expr大于或等于min并且小于或等于max,则beetween返回1,否则返回0

MariaDB [lyshark]> select 4 between 2 and 5 , 4 between 4 and 6 , 20 between 5 and 10;
+-------------------+-------------------+---------------------+
| 4 between 2 and 5 | 4 between 4 and 6 | 20 between 5 and 10 |
+-------------------+-------------------+---------------------+
|                 1 |                 1 |                   0 |
+-------------------+-------------------+---------------------+
1 row in set (0.00 sec)

letsa运算符(least 值1,值2…..值n): 在定义的数值列表中返回最小的那个元素的数值

MariaDB [lyshark]> select least(10,0) , least(1,2,3,4,5,6,7,8,9) , least('a','b','c') , least(10,null);
+-------------+--------------------------+--------------------+----------------+
| least(10,0) | least(1,2,3,4,5,6,7,8,9) | least('a','b','c') | least(10,null) |
+-------------+--------------------------+--------------------+----------------+
|           0 |                        1 | a                  |           NULL |
+-------------+--------------------------+--------------------+----------------+
1 row in set (0.00 sec)

greatest运算符(greatest 值1,值2….值n): 在定义的数值列表中返回最大的那个元素的数值

MariaDB [lyshark]> select greatest(10,0) , greatest(1,2,3,4,5,6,7,8,9) , greatest('a','b','c') , greatest(10,null);
+----------------+-----------------------------+-----------------------+-------------------+
| greatest(10,0) | greatest(1,2,3,4,5,6,7,8,9) | greatest('a','b','c') | greatest(10,null) |
+----------------+-----------------------------+-----------------------+-------------------+
|             10 |                           9 | c                     |              NULL |
+----------------+-----------------------------+-----------------------+-------------------+
1 row in set (0.00 sec)

IN 和NOT IN 运算符(值1 IN (值1,值2…..值n)): in运算符判断指定数值是否在指定的一个列表里,有则返回1无则返回0,而not in运算符恰恰相反.

MariaDB [lyshark]> select 1 in (1,2,3,4,5) , 'lyshark' in ('root','admin','lyshark');
+------------------+-----------------------------------------+
| 1 in (1,2,3,4,5) | 'lyshark' in ('root','admin','lyshark') |
+------------------+-----------------------------------------+
|                1 |                                       1 |
+------------------+-----------------------------------------+
1 row in set (0.00 sec)

MariaDB [lyshark]> select 10 not in (1,2,3,4,5) , 'lyshark' not in ('root','admin','lyshark');
+-----------------------+---------------------------------------------+
| 10 not in (1,2,3,4,5) | 'lyshark' not in ('root','admin','lyshark') |
+-----------------------+---------------------------------------------+
|                     1 |                                           0 |
+-----------------------+---------------------------------------------+
1 row in set (0.00 sec)

LIKE匹配运算符(expr LIKE 匹配条件): like运算符用来匹配字符串,如果expr满足条件则返回1否则返回0,若expr或匹配条件中任何一个为NULL则结果为NULL.

LIKE通配符:

%:匹配任意字符,贪婪匹配
_:只匹配一个字符
t__:表示匹配以t开头,长度为2个字符的字符串
%d:表示匹配以字母d结尾的字符串

MariaDB [lyshark]> select 'lyshark' like 'lyshark' , 'lyshark' like '%k' , 'lyshark' like 'ly_____';
+--------------------------+---------------------+--------------------------+
| 'lyshark' like 'lyshark' | 'lyshark' like '%k' | 'lyshark' like 'ly_____' |
+--------------------------+---------------------+--------------------------+
|                        1 |                   1 |                        1 |
+--------------------------+---------------------+--------------------------+
1 row in set (0.00 sec)

regexp字符串匹配运算符(expr regexp 匹配条件): regexp运算符能够更加精确的匹配,如果expr满足条件则返回1否则返回0,若expr或匹配条件中任何一个为NULL则结果为NULL.

REGEXP通配符：

^：匹配以该字符后面的字符开头的字符串
$：匹配以该字符后面的字符结尾的字符串
.：匹配任意一个单一字符
[…]：匹配在方括号内的任意字符

MariaDB [lyshark]> select 'lyshark' regexp '^l' , 'lyshark' regexp 'k$' , 'lyshark' regexp '..shark' , 'lyshark' regexp '[lyak]';
+-----------------------+-----------------------+----------------------------+---------------------------+
| 'lyshark' regexp '^l' | 'lyshark' regexp 'k$' | 'lyshark' regexp '..shark' | 'lyshark' regexp '[lyak]' |
+-----------------------+-----------------------+----------------------------+---------------------------+
|                     1 |                     1 |                          1 |                         1 |
+-----------------------+-----------------------+----------------------------+---------------------------+
1 row in set (0.00 sec)

◆逻辑运算符◆

运算符	作用
NOT 或 !	逻辑非
AND 或 &&	逻辑与
OR	逻辑或
XOR	逻辑异或

NOT逻辑非: not或!逻辑非运算符,当操作数为0时返回1,当操作为1时返回0,当操作数为NULL时,返回NULL

MariaDB [lyshark]> select not 1 , not(1-1) , not -10 , not NULL;
+-------+----------+---------+----------+
| not 1 | not(1-1) | not -10 | not NULL |
+-------+----------+---------+----------+
|     0 |        1 |       0 |     NULL |
+-------+----------+---------+----------+
1 row in set (0.00 sec)

AND逻辑与: and是逻辑与运算符,当两边都为真是结果为1,否则结果为0

MariaDB [lyshark]> select 1 and -1 , 1 and 0 , 1 and NULL , 0 and NULL;
+----------+---------+------------+------------+
| 1 and -1 | 1 and 0 | 1 and NULL | 0 and NULL |
+----------+---------+------------+------------+
|        1 |       0 |       NULL |          0 |
+----------+---------+------------+------------+
1 row in set (0.00 sec)

OR逻辑或: or是逻辑或运算符,两边的结果如果有一边为真,则返回1否则返回0

MariaDB [lyshark]> select 1 or 1 , 1 or 0 , 1 or -1 , 1 or NULL;
+--------+--------+---------+-----------+
| 1 or 1 | 1 or 0 | 1 or -1 | 1 or NULL |
+--------+--------+---------+-----------+
|      1 |      1 |       1 |         1 |
+--------+--------+---------+-----------+
1 row in set (0.00 sec)

XOR异或: xor逻辑异或运算符,当任意一个操作数为null时返回null,如果两边都为0则返回1否则返回0

MariaDB [lyshark]> select 1 xor 1 , 0 xor 0 , 1 xor 0 , 1 xor null;
+---------+---------+---------+------------+
| 1 xor 1 | 0 xor 0 | 1 xor 0 | 1 xor null |
+---------+---------+---------+------------+
|       0 |       0 |       1 |       NULL |
+---------+---------+---------+------------+
1 row in set (0.00 sec)

◆移位运算符◆

运算符	作用
\	位或
&	位与
^	位异或
<<	位左移
>>	位右移
~	位取反

位或(|): 位或运算符,按照提供数据的二进制形式依次或运算,最后输出结果

MariaDB [lyshark]> select 10 |15 , 9|4|2 ;
+--------+-------+
| 10 |15 | 9|4|2 |
+--------+-------+
|     15 |    15 |
+--------+-------+
1 row in set (0.00 sec)

位与(&): 位与运算符,按照提供数据的二进制形式依次与运算,最后输出结果

MariaDB [lyshark]> select 10 & 15 ,9&4&2 ;
+---------+-------+
| 10 & 15 | 9&4&2 |
+---------+-------+
|      10 |     0 |
+---------+-------+
1 row in set (0.00 sec)

位异或(^): 将指定数据的二进制形式,逐一按位或运算

MariaDB [lyshark]> select 10 ^ 15 , 1^0 , 1^1;
+---------+-----+-----+
| 10 ^ 15 | 1^0 | 1^1 |
+---------+-----+-----+
|       5 |   1 |   0 |
+---------+-----+-----+
1 row in set (0.00 sec)

按位左移(expr<<需要左移的位数): 将指定数据expr,的二进制形式,按位左移

MariaDB [lyshark]> select 4 <<2;
+-------+
| 4 <<2 |
+-------+
|    16 |
+-------+
1 row in set (0.00 sec)

按位右移(expr>>需要右移的位数): 将指定数据expr,的二进制形式,按位右移

MariaDB [lyshark]> select 16 >>2;
+--------+
| 16 >>2 |
+--------+
|      4 |
+--------+
1 row in set (0.00 sec)

按位取反(~): 将相应位数的二进制形式,逐位反转

MariaDB [lyshark]> select 5 & ~1 ;
+--------+
| 5 & ~1 |
+--------+
|      4 |
+--------+
1 row in set (0.00 sec)

MariaDB 常用函数(拓展)

函数表示对输入参数值返回一个具有特定关系的值,MySQL提供了大量丰富的函数,在进行数据库管理以及数据的查询和操作时将会经常用到各种函数.通过对数据的处理,数据库功能可以变得更加强大,更加灵活地满足不同用户的需求.各类函数从功能方面主要分为以下几类：数学函数、字符串函数、日期和时间函数、条件判断函数、系统信息函数和加密函数等.下面我们就来介绍一些基础函数的使用.

◆数学函数◆

绝对值函数:abx(x)

MariaDB [lyshark]> select abs(2) , abs(-10.5) ,abs(-100);
+--------+------------+-----------+
| abs(2) | abs(-10.5) | abs(-100) |
+--------+------------+-----------+
|      2 |       10.5 |       100 |
+--------+------------+-----------+
1 row in set (0.00 sec)

平方根函数:sqrt(x)

MariaDB [lyshark]> select sqrt(10) , sqrt(50) , sqrt(-10);
+--------------------+--------------------+-----------+
| sqrt(10)           | sqrt(50)           | sqrt(-10) |
+--------------------+--------------------+-----------+
| 3.1622776601683795 | 7.0710678118654755 |      NULL |
+--------------------+--------------------+-----------+
1 row in set (0.00 sec)

获取整数的函数:ceil(x),ceiling(x),floor(x)

MariaDB [lyshark]> select ceil(-3.35) , ceiling(3.35) , floor(3.35);
+-------------+---------------+-------------+
| ceil(-3.35) | ceiling(3.35) | floor(3.35) |
+-------------+---------------+-------------+
|          -3 |             4 |           3 |
+-------------+---------------+-------------+
1 row in set (0.00 sec)

获取随机数函数:rand(),rand(x)

MariaDB [lyshark]> select rand() , rand(10);
+--------------------+--------------------+
| rand()             | rand(10)           |
+--------------------+--------------------+
| 0.5317976954689227 | 0.6570515219653505 |
+--------------------+--------------------+
1 row in set (0.00 sec)

其他函数:round(x),round(x,y),runcate(x,y)

MariaDB [lyshark]> select round(-1.14) , round(1.14) , round(1.66);        #对数据进行四舍五入
+--------------+-------------+-------------+
| round(-1.14) | round(1.14) | round(1.66) |
+--------------+-------------+-------------+
|           -1 |           1 |           2 |
+--------------+-------------+-------------+
1 row in set (0.00 sec)

MariaDB [lyshark]> select round(1.38,1) , round(232.38989,3);              #对指定小数点后几位进行四舍五入
+---------------+--------------------+
| round(1.38,1) | round(232.38989,3) |
+---------------+--------------------+
|           1.4 |            232.390 |
+---------------+--------------------+
1 row in set (0.00 sec)

MariaDB [lyshark]> select truncate(1.31,1) , truncate(20.9999,2);           #截断操作，y参数为保留小数点后几位
+------------------+---------------------+
| truncate(1.31,1) | truncate(20.9999,2) |
+------------------+---------------------+
|              1.3 |               20.99 |
+------------------+---------------------+
1 row in set (0.00 sec)

◆字符串函数◆

计算字符串长度:CHAR_LENGTH,字节长度:LENGTH(str)

MariaDB [lyshark]> select CHAR_LENGTH('date'),CHAR_LENGTH('egg');
+---------------------+--------------------+
| CHAR_LENGTH('date') | CHAR_LENGTH('egg') |
+---------------------+--------------------+
|                   4 |                  3 |
+---------------------+--------------------+
1 row in set (0.00 sec)

MariaDB [lyshark]> select LENGTH('date'),LENGTH('egg');
+----------------+---------------+
| LENGTH('date') | LENGTH('egg') |
+----------------+---------------+
|              4 |             3 |
+----------------+---------------+
1 row in set (0.00 sec)

MariaDB [lyshark]>

合并字符串函数:CONCAT()

MariaDB [lyshark]> select CONCAT('hello','lyshark'),CONCAT('my',NULL,'SQL');
+---------------------------+-------------------------+
| CONCAT('hello','lyshark') | CONCAT('my',NULL,'SQL') |
+---------------------------+-------------------------+
| hellolyshark              | NULL                    |
+---------------------------+-------------------------+
1 row in set (0.00 sec)

字符串替换:INSERT(s1,len,s2) 将从Quest替换,从第二个字符开始,一直替换三个.

MariaDB [lyshark]> select insert('Quest',2,3,'what') ;
+----------------------------+
| insert('Quest',2,3,'what') |
+----------------------------+
| Qwhatt                     |
+----------------------------+
1 row in set (0.00 sec)

大小写转换:LOWER(str),LCASE(str) 将大写转换成小写

MariaDB [lyshark]> select LOWER('LYSHARK'),LCASE('well');
+------------------+---------------+
| LOWER('LYSHARK') | LCASE('well') |
+------------------+---------------+
| lyshark          | well          |
+------------------+---------------+
1 row in set (0.00 sec)

大小写转换:UPPER(str),UCASE(str) 将小写转换成大写

MariaDB [lyshark]> select UPPER('black'),UCASE('mkdirs');
+----------------+-----------------+
| UPPER('black') | UCASE('mkdirs') |
+----------------+-----------------+
| BLACK          | MKDIRS          |
+----------------+-----------------+
1 row in set (0.00 sec)

比较字符串大小:STRCMP(s1,s2) 相同返回0,s1<s2返回-1,其他返回1

MariaDB [lyshark]> select STRCMP('lyshark','lyshark'),STRCMP('txt','ttxt'),STRCMP('ttxt','txt');
+-----------------------------+----------------------+----------------------+
| STRCMP('lyshark','lyshark') | STRCMP('txt','ttxt') | STRCMP('ttxt','txt') |
+-----------------------------+----------------------+----------------------+
|                           0 |                    1 |                   -1 |
+-----------------------------+----------------------+----------------------+
1 row in set (0.00 sec)

字符串逆序:REVERSE(str)

MariaDB [lyshark]> select REVERSE('lyshark');
+--------------------+
| REVERSE('lyshark') |
+--------------------+
| krahsyl            |
+--------------------+
1 row in set (0.00 sec)

◆日期时间函数◆

获取当前日期:CURDATE()

MariaDB [lyshark]> select CURDATE(),CURRENT_DATE(),CURDATE()+1;
+------------+----------------+-------------+
| CURDATE()  | CURRENT_DATE() | CURDATE()+1 |
+------------+----------------+-------------+
| 2018-12-24 | 2018-12-24     |    20181225 |
+------------+----------------+-------------+
1 row in set (0.00 sec)

获取当前时间:CURTIME()

MariaDB [lyshark]> select CURTIME(),CURRENT_TIME(),CURTIME()+1;
+-----------+----------------+-------------+
| CURTIME() | CURRENT_TIME() | CURTIME()+1 |
+-----------+----------------+-------------+
| 20:49:04  | 20:49:04       |      204905 |
+-----------+----------------+-------------+
1 row in set (0.00 sec)

获取日期与时间:ALL

MariaDB [lyshark]> select CURRENT_TIMESTAMP(),LOCALTIME(),NOW(),SYSDATE();
+---------------------+---------------------+---------------------+---------------------+
| CURRENT_TIMESTAMP() | LOCALTIME()         | NOW()               | SYSDATE()           |
+---------------------+---------------------+---------------------+---------------------+
| 2018-12-24 20:50:19 | 2018-12-24 20:50:19 | 2018-12-24 20:50:19 | 2018-12-24 20:50:19 |
+---------------------+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)

获取UNIX时间戳:UNIX_TIMESTAMP()

MariaDB [lyshark]> select UNIX_TIMESTAMP(),UNIX_TIMESTAMP(NOW()),NOW();
+------------------+-----------------------+---------------------+
| UNIX_TIMESTAMP() | UNIX_TIMESTAMP(NOW()) | NOW()               |
+------------------+-----------------------+---------------------+
|       1545702698 |            1545702698 | 2018-12-24 20:51:38 |
+------------------+-----------------------+---------------------+
1 row in set (0.00 sec)

获取年份:YEAR()

MariaDB [lyshark]> select year('18-10-10');
+------------------+
| year('18-10-10') |
+------------------+
|             2018 |
+------------------+
1 row in set (0.00 sec)

获取月份:MONTH()

MariaDB [lyshark]> select month('2018-10-05');
+---------------------+
| month('2018-10-05') |
+---------------------+
|                  10 |
+---------------------+
1 row in set (0.00 sec)

获取星期:DAYNAME()

MariaDB [lyshark]> select dayname('2018-10-25');
+-----------------------+
| dayname('2018-10-25') |
+-----------------------+
| Thursday              |
+-----------------------+
1 row in set (0.00 sec)

获取天:DAYOFYEAR()

MariaDB [lyshark]> select dayofyear('2018-10-10');
+-------------------------+
| dayofyear('2018-10-10') |
+-------------------------+
|                     283 |
+-------------------------+
1 row in set (0.00 sec)

◆条件判断函数◆

IF(expr,v1,v2): 如果表达式expr是true(expr<>0 and expr<> NULL),则if()函数返回为v1,否则返回v2,if()语句返回值为数字或字符串,具体情况视其所在语境而定.

MariaDB [lyshark]> select if(1>2,2,3),
    -> if(1<2,'yes','no'),
    -> if(strcmp('test','test1'),'no','yes');

+-------------+--------------------+---------------------------------------+
| if(1>2,2,3) | if(1<2,'yes','no') | if(strcmp('test','test1'),'no','yes') |
+-------------+--------------------+---------------------------------------+
|           3 | yes                | no                                    |
+-------------+--------------------+---------------------------------------+
1 row in set (0.00 sec)

IFNULL(v1,v2): 假如v1不为null,则ifnull()返回值为v1,否则其返回值为v2.

MariaDB [lyshark]> select ifnull(1,2) , ifnull(null,10) , ifnull(1/0,'wrong');
+-------------+-----------------+---------------------+
| ifnull(1,2) | ifnull(null,10) | ifnull(1/0,'wrong') |
+-------------+-----------------+---------------------+
|           1 |              10 | wrong               |
+-------------+-----------------+---------------------+
1 row in set (0.00 sec)

case expr when v1 then r1 [when v2 then r2][else rn] end: 如果expr等价于某个vn,则返回对应位置then后面的结果.如果所用值都不相等,则返回else后面的rn.

MariaDB [lyshark]> select case 2 when 1 then 'one' when 2 then 'two' else 'more' end;
+------------------------------------------------------------+
| case 2 when 1 then 'one' when 2 then 'two' else 'more' end |
+------------------------------------------------------------+
| two                                                        |
+------------------------------------------------------------+
1 row in set (0.00 sec)


MariaDB [lyshark]> select case when 1<0 then 'true' else 'false' end;
+--------------------------------------------+
| case when 1<0 then 'true' else 'false' end |
+--------------------------------------------+
| false                                      |
+--------------------------------------------+
1 row in set (0.00 sec)

◆系统信息函数◆

获取版本:version()

MariaDB [lyshark]> select version();
+----------------+
| version()      |
+----------------+
| 5.5.60-MariaDB |
+----------------+
1 row in set (0.04 sec)

查看当前用户连接数:connection_id()

MariaDB [lyshark]> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|               2 |
+-----------------+
1 row in set (0.00 sec)

输出当前用户的连接信息:show processlist

MariaDB [lyshark]> show processlist;
+----+------+-----------+---------+---------+------+-------+------------------+----------+
| Id | User | Host      | db      | Command | Time | State | Info             | Progress |
+----+------+-----------+---------+---------+------+-------+------------------+----------+
|  2 | root | localhost | lyshark | Query   |    0 | NULL  | show processlist |    0.000 |
+----+------+-----------+---------+---------+------+-------+------------------+----------+
1 row in set (0.01 sec)

MariaDB [lyshark]>
MariaDB [lyshark]> show full processlist;
+----+------+-----------+---------+---------+------+-------+-----------------------+----------+
| Id | User | Host      | db      | Command | Time | State | Info                  | Progress |
+----+------+-----------+---------+---------+------+-------+-----------------------+----------+
|  2 | root | localhost | lyshark | Query   |    0 | NULL  | show full processlist |    0.000 |
+----+------+-----------+---------+---------+------+-------+-----------------------+----------+
1 row in set (0.00 sec)

返回当前使用的数据库:schema()

MariaDB [lyshark]> select database(),schema();
+------------+----------+
| database() | schema() |
+------------+----------+
| lyshark    | lyshark  |
+------------+----------+
1 row in set (0.00 sec)

◆加密解密函数◆

加密函数:password(str)

MariaDB [lyshark]> select password('newpass');
+-------------------------------------------+
| password('newpass')                       |
+-------------------------------------------+
| *D8DECEC305209EEFEC43008E1D420E1AA06B19E0 |
+-------------------------------------------+
1 row in set (0.00 sec)

加密函数:MD5(str)

MariaDB [lyshark]> select MD5('mypass');
+----------------------------------+
| MD5('mypass')                    |
+----------------------------------+
| a029d0df84eb5549c641e04a9ef389e5 |
+----------------------------------+
1 row in set (0.00 sec)

加密函数:encode(str,pswd_str)

MariaDB [lyshark]> select encode('secret','cry'),length(encode('secret','cry'));
+------------------------+--------------------------------+
| encode('secret','cry') | length(encode('secret','cry')) |
+------------------------+--------------------------------+
| ▒h▒ ▒                  |                              6 |
+------------------------+--------------------------------+
1 row in set (0.00 sec)

解密函数:decode(crypt_str,pswd_str)

MariaDB [lyshark]> select decode(encode('secret','cry'),'cry');
+--------------------------------------+
| decode(encode('secret','cry'),'cry') |
+--------------------------------------+
| secret                               |
+--------------------------------------+
1 row in set (0.00 sec)

◆其他通用函数◆

格式化函数:ormat(x,n)

MariaDB [lyshark]> select format(123.123,2);
+-------------------+
| format(123.123,2) |
+-------------------+
| 123.12            |
+-------------------+
1 row in set (0.00 sec)

IP地址转为数字:inet_aton(ipaddr)

MariaDB [lyshark]> select inet_aton("192.168.1.1");
+--------------------------+
| inet_aton("192.168.1.1") |
+--------------------------+
|               3232235777 |
+--------------------------+
1 row in set (0.00 sec)

数字转为IP地址:inet_ntoa

MariaDB [lyshark]> select inet_ntoa(3232235777);
+-----------------------+
| inet_ntoa(3232235777) |
+-----------------------+
| 192.168.1.1           |
+-----------------------+
1 row in set (0.00 sec)