2019-03-04  2024-09-18    13059 字  27 分钟
MySQL

基础

数据库（Database / Schema）

表（Table）

数据库表是一系列二维数组的集合

行被称为记录

列被称为字段

主键（Primary Key）

主键又称主码，用于唯一的标识表中的每一条记录。

可以定义表中的一列或多列为主键，主键列上不能有两行相同的值，也不能为空

数据库系统

  • 数据库（DataBase / DB）：用于存储数据的地方
  • 数据库管理系统（DataBase Management System / DBMS）：用于管理数据库的软件
  • 数据库应用程序（DataBase Application）：为了提高数据库系统的处理能力所使用的管理数据库的软件补充。

SQL语言

  • 数据定义语言（DDL）：DROP、CREATE、ALTER
  • 数据操作语言（DML）：INSERT、UPDATE、DELETE
  • 数据查询语言（DQL）：SELECT
  • 数据控制语言（DCL）：GRANT、REVOKE、COMMIT、ROLLBACK

数据库访问接口

ODBC

Open Database Connectivity（ODBC，开放数据库互连）

JDBC

Java Data Base Connectivity

ADO.NET

PDO

PHP Data Object

命令行操作

C:\Program Files\MySQL\MySQL Server 8.0\bin
  • 登录：mysql -h hostname -u username -p

数据库存储引擎

使用SHOW ENGINES查看系统支持的引擎类型

SHOW ENGINES \G

InnoDB存储引擎

事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键。

MyISAM存储引擎

基于ISAM的存储引擎，并对其进行拓展。

拥有较高的插入、查询速度，但不支持事务。

Archive存储引擎

压缩比率非常高，大概是innodb的0-15分之1

Archivec存储引擎使用行锁来实现高并发插入操作，但是它不支持事务，其设计目标只是提供高速的插入和压缩功能。

基本操作

查看已经创建好的数据库/表

SHOW DATABASES;

SHOW TABLES;

创建数据库

CREATE DATABASE database_name;

CREATE SCHEMA database_name;

删除数据库/表

DROP DATABASE database_name;

DROP TABLE table_name;

指定当前数据库

USE database_name;

查看数据库信息

SHOW CREATE DATABASE database_name \G

查看默认存储引擎

SHOW VARIABLES LIKE `%storage_engine%`;

创建数据表

CREATE TABLE <表名>
(
字段名1 数据类型 [列级别约束条件] [默认值],
字段名2 数据类型 [列级别约束条件] [默认值],
······
[表级别约束条件]
);

CREATE TABLE test
(
id INT(11),
name VARCHAR(25),
salary FLOAT
);

主键约束(Primary Key Constraint)

单字段主键

定义列的同时指定主键：字段名 数据类型 PRIMARY KEY [默认值]

定义完所有列之后指定主键：[CONSTRAINT <约束名>] PRIMARY KEY [字段名]

CREATE TABLE test
(
id INT(11) PRIMARY KEY,
name VARCHAR(25),
salary FLOAT
);

CREATE TABLE test
(
id INT(11),
name VARCHAR(25),
salary FLOAT,
PRIMARY KEY (id)
);

多字段联合主键

PRIMARY KEY [字段1，字段2，···，字段n]

CREATE TABLE test
(
id INT(11),
name VARCHAR(25),
salary FLOAT,
PRIMARY KEY (id,name)
);

外键约束

__外键：__首先它是表中的一个字段，他可以不是本表的主键，但对应另一个表的主键

__主表（父表）：__对于两个具有关联关系的表而言，相关字段中主键所在的那个表即是主表。

__从表（子表）：__对于两个具有关联关系的表而言，相关字段中外键所在的那个表即是从表。

[CONSTRAINT <外键名>] FOREIGN KEY 字段名1 [ ，字段名2，···]
REFERENCES <主表名> 主键列1 [ ，主键列2，···]

非空约束(Not Null Constraint)

字段名 数据类型 NOT NULL

CREATE TABLE test
(
id INT(11),
name VARCHAR(25) NOT NULL,
salary FLOAT
);

唯一性约束(Unique Constraint)

一个表中可以有多个字段声明为UNIQUE，但只能有一个PRIMARY KEY声明；

PRIMARY KEY 不允许有空值

UNIQUE 允许空值(NULL) 存在

字段名 数据类型 UNIQUE

CREATE TABLE test
(
id INT(11),
name VARCHAR(25) UNIQUE,
salary FLOAT
);

定义完所有列之后指定唯一约束：[CONSTRAINT <约束名>] UNIQUE(<字段名>)

CREATE TABLE test
(
id INT(11),
name VARCHAR(25),
salary FLOAT,
PRIMARY KEY (id,name),
CONSTRAINT STH UNIQUE(name)
);

默认约束(Default Constraint)

字段名 数据类型 DEFAULT 默认值

CREATE TABLE test
(
id INT(11),
name VARCHAR(25),
salary FLOAT DEFAULT 100.0
);

设置表的属性值自动增加

AUTO_INCREMENT约束的字段可以是任何整数类型（TINYINT、SMALLINT、INT、BIGINT）

字段名 数据类型 AUTO_INCREMENT

CREATE TABLE test
(
id INT(11) PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(25),
salary FLOAT
);

查看表基本结构

可以查看表的字段信息，包括：字段名、数据类型、是否为主键、是否有默认值等。

DESCRIBE 表名; 简写为 DESC 表名;

查看表详细结构

加上\G使显示结果更加直观，易于查看

SHOW CREATE TABLE <表名\G>;

修改表名

ALTER TABLE <旧表名> RENAME [TO] <新表名>;

修改字段类型

ALTER TABLE <表名> MODIFY <字段名> <数据类型>;

修改字段名

ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新数据类型>;

添加字段

ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件] [FIRST | AFTER 已存在字段名];

删除字段

ALTER TABLE <表名> DROP <字段名>;

修改字段排列

ALTER TABLE <表名> MODIFY <字段1> <数据类型> FIRST|AFTER <字段2>;

修改表的存储引擎

引擎名 是否支持
FEDERATED 否
MRG_MYISAM 是
MyISAM 是
BLACKHOLE 是
CSV 是
MEMORY 是
ARCHIVE 是
InnoDB 默认
PERFORMANCE_SCHEMA 是

ALTER TABLE <表名> ENGINES=<更改后的存储引擎名>;

删除表的外键约束

ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>

删除没有被关联的表

DROP TABLE [IF EXISTS] 表1，表2，··· ;

数据类型和运算符

数值数据类型：

  • 整数：TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT
  • 浮点：FLOAT、DOUBLE
  • 定点：DECIMAL

日期/时间类型：

  • YEAR、TIME、DATE、DATETIME、TIMESTAMP

字符串类型：

  • CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM、SET

整数类型

类型名 说明 储存要求(字节)
TINYINT 很小的整数 1
SMALLINT 小的整数 2
MEDIUMINT 中等大小的整数 3
INT(INTEGER) 普通大小的整数 4
BIGINT 大的整数 8
数据类型 有符号 无符号
TINYINT -128~127 0~255
SMALLINT -32768~32767 0~65535
MEDIUMINT -8388608~8388607 0~16777215
INT(INTEGER) -2147483648~2147483647 0~4294967295
BIGINT -9223372036854775808~9223372036854775807 0~18446744073709551615

例如TINYINT需要1字节(8bits)，最大值等于2^8-1，即255。有符号最大值等于2^7-1

year INT(4)，指名year字段的数据一般只显示4位数字的宽度，显示宽度与取值范围是无关的。

​ 例如插入数值19999，使用SELECT查询出来显示的将是完整的5位19999。

浮点数类型和定点数类型

类型名称 说明 存储需求(字节)
FLOAT 单精度浮点 4
DOUBLE 双精度浮点 8
DECIMAL (M,D), DEC 压缩的”严格“定点数 M+2

日期与时间类型

类型名称 日期格式 日期范围 存储需求(字节)
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-31 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 4

可以使用各种格式指定TIME值：

  • D HH:MM:SS格式字符串
  • HH:MM:SS、HH:MM、D HH:MM、D HH、SS

D表示日，可以取0~34之间的值，插入数据库时，D被转换为小时保存

  • HHMMSS格式的、没有间隔的字符串或者数值、如果有时间意义。例如101112被理解为10:11:12但109712不合法，因为他有一个没有意义的分钟部分，存储时变为00:00:00

CURRENT_DATE获取YYYY-MM-DD

CURRENT_TIME获取HH:MM:SS

NOW()获取YYYY-MM-DD HH:MM:SS

  • TIMESTAMP储存时间时会将时间转换为UTC时间，检索时再转换为当前时区。根据当前时区的不同显示的时间值是不同的
  • DATETIME，输入什么就储存什么

文本字符串类型

类型名称 说明 存储需求
CHAR(M) 固定长度非二进制字符串 M字节，1<=M<=255
VARCHAR(M) 变长非二进制字符串 L+1字节，L<=M和1<=M<=255
TINYTEXT 非常小的非二进制字符串 L+1字节，L<28
TEXT 小的非二进制字符串 L+2字节，L<216
MEDIUMTEXT 中等大小的非二进制字符串 L+3字节，L<224
LONGTEXT 大的非二进制字符串 L+4字节，L<232
ENUM 枚举类型，只能有一个枚举字符串值非二进制字符串 1或2字节，取决于枚举值的数目（最大值65535）
SET 一个设置，字符串对象可以有零个或多个SET成员非二进制字符串 1，2，3，4或8字节，取决于集合成员的数量(最多64个成员)

ENUM是一个字符串对象，其值为表创建时在列规定中枚举的一列值，只能选择一个值插入。字段名 ENUM ('值1','值2',...)

SET与ENUM不同的是，插入时可以选择多个字符联合。

二进制字符串类型

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

BIT数据类型用来保存位字段值

例如：以二进制的形式保存数据13，13的二进制形式1101，这里需要位数至少为4的BIT类型，即可定义BIT(4)。大于二进制1111的数据是不能插入BIT(4)的。

BINARY类型的长度是固定的，指定长度后，不足最大长度的，将在它们右边填充\0。

数据类型 储存最大长度
TINYBLOB 255(28-1)B
BLOB 65535(216-1)B
MEDIUMBLOB 16777215(224-1)B
LONGBLOB 4294967295B或4GB(232-1)B

BLOB列存储的是二进制字符串（字节字符串）；TEXT列存储的是非二进制字符串（字符字符串）

BLOB列没有字符集，并且排序和比较基于列值字节的数值；

TEXT列有一个字符集，并且根据字符集对值进行排序和比较。

数据类型选择

浮点数相对于定点数的优势是：在长度一定的情况下，浮点数能表示更大的数据范围，但由于浮点数容易产生误差，因此对精度要求较高时，建议使用DECIMAL来储存。

CHAR时固定长度，所以处理速度比VARCHAR的速度快，但缺点是浪费储存空间。

对于MyISAM存储引擎：最好使用固定长度的数据列替代可变长度的数据列。这样可以使整个表静态化，从而使数据检索更快，用空间换时间。

对于InnoDB存储引擎：使用可变长度的数据列，因为InnoDB数据表的储存格式不分固定长度和可变长度，因此使用CHAR不一定比使用VARCHAR更好，但由于VARCHAR是按照实际的长度储存，比较节省空间，对磁盘I/O和数据存储总量比较好。

BLOB是二进制字符串，TEXT是非二进制字符串，两者均可存放大量的信息。BLOB主要储存图片、音频信息等，而TEXT只能存储文本文件。

常见运算符

算术运算符： + - * / %

比较运算符： > < = >= <= != 以及IN、BETWEEN AND、IS NULL、GREATES、LEAST、LIKE、REGEXP

逻辑运算符： NOT( ! )、AND( && )、OR( || )、XOR

位运算符：& | ~ ^ << >>

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

等于(=)

SELECT 1=0, 2='2', NULL=NULL;

由于=不能用于判断空值NULL，返回NULL

1=0 2=‘2’ 2=2 NULL=NULL
0 1 1 NULL

安全等于(<=>)

与=操作符执行相同的比较操作，不过<=>可以用来判断NULL值。两个操作数均为NULL时，返回1。一个操作数为NULL时，返回0

不能用于判断NULL

不等于(<>或!=) && 小于(<) && 小于等于(<=) && 大于等于(>=) && 大于(>)

BETWEEN AND

语法格式：exper BETWEEN min AND max如果expr大于或等于min并且小于或等于max，则返回0

LEAST

语法格式：LEAST(值1, 值2, 值3...)，在有两个或多个参数的情况下，返回最小值。假如任意一个自变量为NULL，则返回NULL。

GREATEST

语法格式：GREATEST(值1,值2...)，在有两个或多个参数的情况下，返回最大值。假如任意一个自变量为NULL，则返回NULL。

IN、NOT IN

SELECT 2 IN (1，3，5, 'thks'), 'thks' IN (1,3,5,'thks');
#      0 					   1

在左侧表达式为NULL的情况下，或是表中找不到匹配项并且表中的一个表达式为NULL的情况下，IN返回值均为NULL。

SELECT NULL IN (1，3，5, 'thks'), 10 IN (1,3,NULL,'thks');
#      NULL 					  NULL

LIKE

语法格式：expr LIKE 匹配条件, expr满足条件返回1，否则0

若expr或者匹配条件中任何一个为NULL，结果返回NULL

  • %，匹配任何数目的字符，甚至包括0字符
  • _，只能匹配一个字符
SELECT 'stud' LIKE 'stud', 'stud' LIKE 'stu_', 'stud' LIKE '%d', 'stud' LIKE 't___';
#		1					1					1				  0

REGEXP

语法格式：expr REGEXP 匹配条件，满足返回1，否则0

若expr或者匹配条件中任何一个为NULL，结果返回NULL

  • ^匹配以该字符后面的字符开头的字符串
  • $匹配以该字符串后面的字符结尾的字符串
  • .匹配任何一个单字符
  • [...]匹配方括号内的任何字符。例如[abc]匹配a b或c。为了命名字符的范围，使用一个-。
    • [a-z]匹配任何字母，[0-9]匹配任何数字
  • *匹配零个或多个在它前面的字符。例如x*匹配然和数量的x字符，[0-9]*匹配任何数量的数字，而*匹配任何数量的任何字符。
SELECT 'ssky' REGEXP '^s', 'ssky' REGEXP 'y$', 'ssky' REGEXP '.sky', 'ssky' REGEXP '[ab]'
# 	1 	1 	1 	0

NOT 或者 ！

当操作数为NULL时，返回NULL

AND 或者 &&

所有操作数均为非零值、并且不为NULL时，计算所得结果为1；当一个或多个操作数为0时，所得结果为0，其余情况返回NULL。

OR 或者 ||

两个数均为非NULL值，且任意一个操作数为非零值时，结果为1，否则结果为0；当有一个操作数为NULL，且另一个操作数为非零值时，结果为1，否则为NULL；两个操作数均为NULL时，结果为NULL。

SELECT 1 OR NULL, 0 OR NULL;
#	1    NULL

XOR

当任意一个操作数为NULL时，返回NULL；对于非NULL操作数，如果连个操作数都是非0值或者都是0值，返回0；如果一个为0值，另一个为非0值，返回1。

a XOR b的计算等同于(a AND (NOT b))或者((NOT a) AND b)

位运算符

运算符 作用
| 位或
& 位与
^ 位异或
<< 位左移
>> 位右移
~ 位取反，反转所有比特 
SELECT 10|15, 9|4|2;
#		15    15
SELECT 10&15, 9&4&2;
#		10    0
SELECT 10^15, 1^0, 1^1;
#		5      1    0
SELECT 1<<2, 4<<2;
#		4    16
SELECT 1>>1, 16>>2;
#		0    4
SELECT 5 & ~1;
#		4  ~级别高于&

运算符优先级

优先级 运算符
最低 =(赋值运算)，:=
||, OR
XOR
&&, AND
NOT
BETWEEN, CASE, WHEN, THEN, ELSE
=(比较运算符), <=>, >=, >, <=, <, <>, !=, IS, LIKE, REGEXP, IN
|
&
<<, >>
-, +
*, /(DIV), %(MOD)
^
-(负号), ~(位反转)
最高 !

数学函数

绝对值函数ABS(x)和返回圆周率的函数PI()

pi函数默认的显示小数位数是6位

SELECT ABS(-2), pi();
#		2       3.141593 #保留了7位有效数字

平方根函数SQRT(x)和求余函数MOD(x,y)

sqrt返回非负数的二次方根

SELECT SQRT(9), SQRT(-9), MOD(31, 10), MOD(45.5, 6)
#      3        NULL	  1				3.5

取整函数CEIL(x)、CEILING(x)和FLOOR(x)

CEIL(x)和CEILING(x)意义相同，返回不小于x的最小整数值，返回值转化为一个BIGINT。

FLOOR返回不大于x的最大整数值，返回值转化为一个BIGINT

SELECT CEIL(-3.35), CEILING(3.35), FLOOR(-3.35), FLOOR(3.35)
#      -3           4              -4            3

获取随机数RAND()和RAND(x)

RAND(x)返回一个随即浮点值v，范围在0到1之间（0 <= v <= 1.0），若已指定一个整数参数x，则它将被用作种子值，用来产生重复序列

SELECT RAND(), RAND(), RAND(10), RAND(10)
  0.7443382784205845  0.1631086432962759  0.6570515219653505  0.6570515219653505

ROUND(x)、ROUND(x, y)和TRUNCATE(x, y)

ROUND(x)返回最接近于参数x的整数，对x值进行四舍五入

SELECT ROUND(-1.14), ROUND(-1.67), Round(1.14), Round(1.66)
#	   -1            -2            1            2

ROUND(x, y)返回最接近于参数x的数，其值保留到小数点后面y位，若y为负数，则将保留x值到小数点左边y位。

SELECT ROUND(1.38, 1), ROUND(1.38, 0), ROUND(232.38, 1)
#      1.4             1               230

TRUNCATE(x, y)函数对操作数进行截取操作，结果保留小数点后面指定y位

若y为0，则结果不带有小数点或不带有小数部分。若y设为负数，则截取(归零)x小数点左起y位开始的后面所有低位的值。

SELECT TRUNCATE(1.31, 1), TRUNCATE(1.99, 1), TRUNCATE(1.99, 0), TRUNCATE(19.99, -1)
#      1.3                1.9                1                  10

符号函数 SIGN(x)

返回参数的符号，x值为负、零、或正时返回结果依次是-1、0和1

SELECT SIGN(-21), SIGN(0),SIGN(21)
		-1		  0       1

幂函数POW(x,y)、POWER(x,y)、EXP(x)

SELECT POW(2,2), POWER(2,2), POW(2,-2), POWER(2,-2)
	   4         4	        0.25		    0.25

EXP计算e的乘方

SELECT EXP(3), EXP(-3), EXP(0)
  20.085536923187668 | 0.049787068367863944 | 1

对数运算函数LOG(x)和LOG10(x)

LOG(x)返回x的自然对数，x相对于基数e的对数。

SELECT LOG(3), LOG(-3);
  1.0986122886681098 |    NULL

LOG10(x)计算以10为基数的对数

SELECT LOG10(2), LOG10(100), LOG10(-100);
  0.3010299956639812 |          2 |        NULL

角度与弧度互相转换的函数RADIANS(x)和DEGREES(x)

# 角度转化为弧度
SELECT RADIANS(90), RADIANS(180);
  1.5707963267948966 | 3.141592653589793
# 弧度转化为角度
SELECT DEGREES(PI()), DEGREES(PI()/2);
  180 |              90

正弦函数SIN(x)和反正弦函数ASIN(x)

x为弧度值

SELECT SIN(1), ROUND(SIN(PI())), SIN(RADIANS(30));
	0.8414709848078965 |                0 | 0.49999999999999994 
SELECT ASIN(0.8414709848078965), ASIN(3);
 	1 |    NULL

余弦函数COS(x)和反余弦函数ACOS(x)

SELECT COS(0), COS(PI()), COS(1);
   1 |        -1 | 0.5403023058681398
SELECT ACOS(1), ACOS(0), ROUND(ACOS(0.5403023058681398));
   0 | 1.5707963267948966 |                               1

正切函数TAN(x)、反正切函数ATAN(x)和余切函数COT(x)

SELECT TAN(0.3), ROUND(TAN(PI()/4));
	0.30933624960962325 |                  1
SELECT ATAN(0.30933624960962325), ATAN(1);
    0.3 | 0.7853981633974483
SELECT COT(0.3), 1/ATAN(0.3), COT(PI()/4);
    3.2327281437658275 | 3.4310402740531716 | 1.0000000000000002

字符串函数

计算字符串字符数的函数和字符串长度的函数

CHAR_LENGTH(str)返回字符串str所包含的字符个数，一个多字节字符算作一个字符

LENGTH(str)返回为字符串的长度，使用utf8编码字符集时，一个汉字是3字节，一个字母或数字为1字节

SELECT CHAR_LENGTH('str'), CHAR_LENGTH('estr'), CHAR_LENGTH('哈');
    3 |                   4 |                 1
SELECT LENGTH('str'), LENGTH('estr'), LENGTH('哈哈哈！');
    3 |              4 |            8

合并字符串函数

CONCAT(s1,s1,…)

  • 返回结果为连接参数产生的字符串，若有任何一个参数为null返回null。
  • 若有任意一个参数为二进制字符串，返回二进制字符串

CONCAT_WS(x,s1,s2,…)

  • CONCAT With Separator
  • 第一个参数x是其他参数的分隔符，分隔符的位置放在要链接的两个字符串之间。
  • 分隔符可以是一个字符串也可以是其他参数，
  • 分隔符为null，返回null。函数会忽略任何分隔符参数后的null值
SELECT CONCAT('My SQL', '5.7'), CONCAT('My SQL', NULL, '5.7');
    My SQL5.7               | NULL
SELECT CONCAT_WS('-', '1st', '2nd', '3rd'), CONCAT_WS('*', '1st', NULL, '3rd');
    1st-2nd-3rd                         | 1st*3rd

替换字符串函数

INSERT(s1,x,s2)

返回字符串s1，其子字符串起始于x位置和被字符串s2取代的len字符，如果x超过字符串长度，则返回原始字符串。加入len的长度大于七大字符串长度，则从x开始替换，若任何一个参数为null，则返回null

SELECT INSERT('Quest', 2, 4, 'What') AS col1,
	   INSERT('Quest', -1, 4, 'What') AS col2,
	   INSERT('Quest', 3, 100, 'What') AS col3,
	   INSERT('Questhjklhjkl', 6, 100, 'What') AS col4;
	   
| col1  | col2  | col3   | col4      |
+-------+-------+--------+-----------+
| QWhat | Quest | QuWhat | QuestWhat |

字母大小写转换函数

LOWER(str)或者LCASE(str)可以将字符串str中的字母字符全部转换成小写字母

UPPER(str)或者UCASE(str)可以将字符串str中的字母字符全部转换成大写字母

SELECT LOWER('BEAUTIFUL'),LCASE('Well'),UPPER('black'), UCASE('Black');

+--------------------+---------------+----------------+----------------+
| LOWER('BEAUTIFUL') | LCASE('Well') | UPPER('black') | UCASE('Black') |
+--------------------+---------------+----------------+----------------+
| beautiful          | well          | BLACK          | BLACK          |
+--------------------+---------------+----------------+----------------+

获取指定长度的字符串的函数

LEFT(s, n)返回字符串s开始的最左边n个字符

RIGHT(s, n)返回字符串中最右边的n个字符

SELECT LEFT('football', 5), RIGHT('football', 4);

+---------------------+----------------------+
| LEFT('football', 5) | RIGHT('football', 4) |
+---------------------+----------------------+
| footb               | ball                 |
+---------------------+----------------------+

填充字符串函数

LPAD(sl, len, s2)返回字符串s1，其左边由字符串s2填补到len字符长度。假如s1的长度大于len，则返回值被缩短至len字符

RPAD(s1, len, s2)返回字符串s1，其右边由字符串s2填补到len字符长度。假如s1的长度大于len，则返回值被缩短至len字符

SELECT LPAD('hello', 4, '??'), LPAD('hello', 10, '??');

+------------------------+-------------------------+
| LPAD('hello', 4, '??') | LPAD('hello', 10, '??') |
+------------------------+-------------------------+
| hell                   | ?????hello              |
+------------------------+-------------------------+


SELECT RPAD('hello', 4, '?'), LPAD('hello', 10, '?');

+-----------------------+------------------------+
| RPAD('hello', 4, '?') | LPAD('hello', 10, '?') |
+-----------------------+------------------------+
| hell                  | ?????hello             |
+-----------------------+------------------------+

删除空格的函数

LTRIM(s)返回字符串s，字符串左侧空格字符被删除

RTRIM(s)返回字符串s，字符串右侧空格字符被删除

TRIM(s)返回字符串s，字符串两侧空格字符被删除

SELECT '(book)', CONCAT('(',LTRIM(' book '),')');

+--------+---------------------------------+
| (book) | CONCAT('(',LTRIM(' book '),')') |
+--------+---------------------------------+
| (book) | (book )                         |
+--------+---------------------------------+

SELECT '(book)', CONCAT('(',RTRIM(' book '),')');

+--------+---------------------------------+
| (book) | CONCAT('(',RTRIM(' book '),')') |
+--------+---------------------------------+
| (book) | ( book)                         |
+--------+---------------------------------+

SELECT '(book)', CONCAT('(',TRIM(' book '),')');

+--------+--------------------------------+
| (book) | CONCAT('(',TRIM(' book '),')') |
+--------+--------------------------------+
| (book) | (book)                         |
+--------+--------------------------------+

删除自定字符串的函数

TRIM(s1 FROM s)删除字符串s中两端所有的子字符串s1。s1为可选项，在未指定的情况下，删除空格

删除两端所有的，但并不删除中间的

SELECT TRIM('xy' FROM 'xyxboxyokxxyxy');

+----------------------------------+
| TRIM('xy' FROM 'xyxboxyokxxyxy') |
+----------------------------------+
| xboxyokx                         |
+----------------------------------+

重复生成字符串的函数

REPEAT(s, n)返回一个由重复的字符串s组成的字符串，字符串s的数目等于n。若n<=0，则返回一个空字符串。

若s或n为NULL，返回NULL

SELECT REPEAT('mysql', 3);

+--------------------+
| REPEAT('mysql', 3) |
+--------------------+
| mysqlmysqlmysql    |
+--------------------+

空格函数和替换函数

SPACE(n) 返回一个由n个空格组成的字符串

REPLACE(s, s1, s2) 使用字符串s2代替字符串中所有的s1

SELECT CONCAT( '(', SPACE(6), ')' );

+------------------------------+
| CONCAT( '(', SPACE(6), ')' ) |
+------------------------------+
| (      )                     |
+------------------------------+

SELECT REPLACE('xxx.mysql.com', 'x', 'w');

+------------------------------------+
| REPLACE('xxx.mysql.com', 'x', 'w') |
+------------------------------------+
| www.mysql.com                      |
+------------------------------------+

比较字符串大小的函数

STRCMP(s1, s2) 若所有字符均相同，返回0； 若根据当前分类次序，第一个参数小于第二个，则返回-1，其他情况返回1.

SELECT STRCMP('txt', 'txt2'), STRCMP('txt2', 'txt'), STRCMP('txt', 'txt');

+-----------------------+-----------------------+----------------------+
| STRCMP('txt', 'txt2') | STRCMP('txt2', 'txt') | STRCMP('txt', 'txt') |
+-----------------------+-----------------------+----------------------+
|                    -1 |                     1 |                    0 |
+-----------------------+-----------------------+----------------------+

获取字符串的函数

SUBSTRING(s, n, len) 带有len参数的格式，从字符串s返回一个长度同len字符相同的字字符串，起始于位置n。也可能对n使用一个负值。假若这样，则字字符串的位置起始于字符串结尾的n字符，即倒数第n个字符，而不是字符串的开头。

MID(s, n, len)与SUBSTRING(s, n, len)的作用相同

SELECT SUBSTRING('breakfast', 5) AS coll, SUBSTRING('breakfast', 5, 3) AS coll2, SUBSTRING('lunch', -3) AS coll3, SUBSTRING('lunch', -5, 3) AS coll4;

+-------+-------+-------+-------+
| coll  | coll2 | coll3 | coll4 |
+-------+-------+-------+-------+
| kfast | kfa   | nch   | lun   |
+-------+-------+-------+-------+

匹配字串开始位置的函数

LOCATE(str1, str)、POSITION(str1 IN str)和INSTR(str, str1)三个函数作用相同,返回字字符串str1在字符串str中的开始位置

SELECT LOCATE('ball', 'football'), POSITION('ball' IN 'football'), INSTR('football', 'ball');

+----------------------------+--------------------------------+---------------------------+
| LOCATE('ball', 'football') | POSITION('ball' IN 'football') | INSTR('football', 'ball') |
+----------------------------+--------------------------------+---------------------------+
|                          5 |                              5 |                         5 |
+----------------------------+--------------------------------+---------------------------+

字符串逆序

REVERSE(s)将字符串s反转, 返回的字符串的顺序和s字符串顺序相反.

SELECT REVERSE('abc');

+----------------+
| REVERSE('abc') |
+----------------+
| cba            |
+----------------+

返回指定位置的字符串

ELT(N, str1, str2, str3,…, strn) 若n==1, 则返回值为str1, 若n==2, 则返回字符串2, 以此类推. 若n小于1或大于参数的数目, 则返回值为NULL

SELECT ELT('3', '1st', '2nd', '3rd'), ELT(3, 'net', 'os');

+-------------------------------+---------------------+
| ELT('3', '1st', '2nd', '3rd') | ELT(3, 'net', 'os') |
+-------------------------------+---------------------+
| 3rd                           | NULL                |
+-------------------------------+---------------------+

返回指定字符串位置

FIELD(s, s1, s2,…) 返回字符串s在列表s1, s2,…中第一次出现的位置,在找不到s的情况下, 返回值为0. 如果s为null, 则返回值为0, 原因是null不能同任何值进行同等比较

SELECT FIELD('Hi', 'hihi', 'Hey', 'Hi', 'bas') as coll, FIELD('Hi', 'Hey', 'Lo', 'Hilo', 'foo') as col2;

+------+------+
| coll | col2 |
+------+------+
|    3 |    0 |
+------+------+

返回字串位置

FIND_IN_SET(s1, s2)返回字符串s1在字符串列表s2中出现的位置, 字符串列表是一个由多个’,‘分开的字符串组成的列表. 如果s1不在s2或s2为空字符串. 则返回0. 如果任意一个参数为NULL, 则返回值为NULL.这个函数在第一个参数包含一个逗号’,‘时将无法运行.

SELECT FIND_IN_SET('Hi', 'hihi,Hey,Hi,bas');
+--------------------------------------+
| FIND_IN_SET('Hi', 'hihi,Hey,Hi,bas') |
+--------------------------------------+
|                                    3 |
+--------------------------------------+

选取字符串

MAKE_SET(x, s1, s2,…)返回由x的二进制数指定的相应位的字符串组成的字符串, s1对应比特1, s2对应比特01, 以此类推. s1, s2,… 中的null值不会被添加到结果中.

SELECT MAKE_SET(1, 'a', 'b', 'c') as coll, MAKE_SET(1 | 4, 'hello', 'nice', 'world') as coll2, MAKE_SET(1 | 4, 'hello', 'nice', NULL, 'world') as coll3, MAKE_SET(0, 'a', 'b', 'c') as coll4;

+------+-------------+-------+-------+
| coll | coll2       | coll3 | coll4 |
+------+-------------+-------+-------+
| a    | hello,world | hello |       |
+------+-------------+-------+-------+

时间和日期函数

获取当前日期的函数和获取当前时间的函数

CURDATE()和CURRENT_DATE()函数的作用相同,将当前日期按照’YYYY-MM-DD’ 或 YYYYMMDD格式的值返回, 具体格式根据函数在字符串或是数字语境中而定.

SELECT CURDATE(), CURRENT_DATE(), CURDATE() + 0;

+------------+----------------+---------------+
| CURDATE()  | CURRENT_DATE() | CURDATE() + 0 |
+------------+----------------+---------------+
| 2019-04-18 | 2019-04-18     |      20190418 |
+------------+----------------+---------------+

CURTIME()和CURRENT_TIME() 函数作用相同, 将当前时间以’HH:MM:SS’或者’HHMMSS’的格式返回, 具体格式根据函数在字符串或是数字语境中而定.

SELECT CURTIME(), CURRENT_TIME(), CURTIME() + 0;

+-----------+----------------+---------------+
| CURTIME() | CURRENT_TIME() | CURTIME() + 0 |
+-----------+----------------+---------------+
| 21:02:35  | 21:02:35       |        210235 |
+-----------+----------------+---------------+

获取当前日期和时间的函数

CURRENT_TIMESTAMP()、LOCALTIME()、 NOW()和SYSDATE()四个函数的作用相同, 均返回当前日期和时间值, 格式为’YYYY-MM-DD HH:MM:SS’或’YYYYMMDDHHMMSS’, 具体格式根据函数在字符串或是数字语境中而定.

SELECT CURRENT_TIMESTAMP(), LOCALTIME(), NOW(), SYSDATE();

+---------------------+---------------------+---------------------+---------------------+
| CURRENT_TIMESTAMP() | LOCALTIME()         | NOW()               | SYSDATE()           |
+---------------------+---------------------+---------------------+---------------------+
| 2019-04-18 21:11:50 | 2019-04-18 21:11:50 | 2019-04-18 21:11:50 | 2019-04-18 21:11:50 |
+---------------------+---------------------+---------------------+---------------------+

UNIX时间函数

UNIX_TIMESTAMP(date)若无参数调用, 则返回一个Unix时间戳(‘1970-01-01 00:00:00’ GMT之后的秒数) 作为无符号整数. 其中, GMT(Greenwich mean time) 为格林尼治标准时间. 若用date来调用UNIX_TIMESTAMP(), 它会将参数以'1970-01-01 00:00:00’ GMT 后的秒数的形式返回. date可以是一个DATE字符串、DATETIME字符串、TIMESTAMP或一个当地时间的YYMMDD或YYYYMMDD格式的数字.

SELECT UNIX_TIMESTAMP(), UNIX_TIMESTAMP(NOW()), NOW();

+------------------+-----------------------+---------------------+
| UNIX_TIMESTAMP() | UNIX_TIMESTAMP(NOW()) | NOW()               |
+------------------+-----------------------+---------------------+
|       1555593491 |            1555593491 | 2019-04-18 21:18:11 |
+------------------+-----------------------+---------------------+

FROM_UNIXTIME(date) 函数把UNIX时间戳转换成普通格式时间, 与UNIX_TIMESTAMP(date)函数互为反函数.

SELECT FROM_UNIXTIME(1555593491);

+---------------------------+
| FROM_UNIXTIME(1555593491) |
+---------------------------+
| 2019-04-18 21:18:11       |
+---------------------------+

返回UTC日期的函数和返回UTC时间的函数

UTC_DATE()函数返回当前时间UTC (世界标准时间) 日期值, 其格式为’YYYY-MM-DD’或YYYMMDD, 具体格式根据函数在字符串或是数字语境中而定.

SELECT UTC_DATE(), UTC_DATE() + 0;

+------------+----------------+
| UTC_DATE() | UTC_DATE() + 0 |
+------------+----------------+
| 2019-04-18 |       20190418 |
+------------+----------------+

UTC_TIME()返回值为当前UTC时间值, 其格式为’HH:MM:SS’或者’HHMMSS’, 具体格式根据函数在字符串或是数字语境中而定.

SELECT UTC_TIME(), UTC_TIME() + 0;

+------------+----------------+
| UTC_TIME() | UTC_TIME() + 0 |
+------------+----------------+
| 13:25:21   |         132521 |
+------------+----------------+

获取月份的函数

MONTH(date) 函数返回 date 对应的月份，范围值从1～12

MONTHNAME(date) 函数返回日期 date 对应的月份的英文全名。

SELECT MONTH('2016-02-13'), MONTHNAME('2016-02-13');

+---------------------+-------------------------+
| MONTH('2016-02-13') | MONTHNAME('2016-02-13') |
+---------------------+-------------------------+
|                   2 | February                |
+---------------------+-------------------------+

获取星期

DAYNAME(d)函数返回d对应的工作日的英文名称, 例如Sunday、Monday 等.

SELECT DAYNAME('2016-02-10');

+-----------------------+
| DAYNAME('2016-02-10') |
+-----------------------+
| Wednesday             |
+-----------------------+

DAYOFWEEK(d) 函数返回 d 对应的一周中的索引（位置）。1 表示星期日、2 表示星期一

SELECT DAYOFWEEK('2016-02-14');

+-------------------------+
| DAYOFWEEK('2016-02-14') |
+-------------------------+
|                       1 |
+-------------------------+

WEEK(d) 返回 d 对应的工作日索引。 0表示周一、1表示周二

SELECT WEEKDAY('2016-02-14 22:23:00'), WEEKDAY('2016-04-01');

+--------------------------------+-----------------------+
| WEEKDAY('2016-02-14 22:23:00') | WEEKDAY('2016-04-01') |
+--------------------------------+-----------------------+
|                              6 |                     4 |
+--------------------------------+-----------------------+

获取星期数的函数

WEEK(d) 计算星期 d 是一年中的第几周。WEEK() 的双参形式允许指定该星期是否起始于周日或周一，以及返回值的范围是否为从0～53 或从 1～53. 若Mode参数被省略，则使用default_week_format系统自变量的值

Mode 一周的第一天 范围 Week 1 为第一周…
0 周日 0～53 本年度中有一个周日
1 周一 0～53 本年度中有3天以上
2 周日 1～53 本年度中有一个周日
3 周一 1～53 本年度中有3天以上
4 周日 0～53 本年度中有3天以上
5 周一 0～53 本年度中有一个周一
6 周日 1～53 本年度中有3天以上
7 周一 1～53 本年度中有一个周一 
SELECT WEEK('2011-02-20'), WEEK('2011-02-20', 0), WEEK('2011-02-20', 1);

+--------------------+-----------------------+-----------------------+
| WEEK('2011-02-20') | WEEK('2011-02-20', 0) | WEEK('2011-02-20', 1) |
+--------------------+-----------------------+-----------------------+
|                  8 |                     8 |                     7 |
+--------------------+-----------------------+-----------------------+

WEEKOFYEAR(d) 计算某一天位于一年中的第几周， 范围是1～53，相当于WEEK(d, 3)。

SELECT WEEK('2011-02-20', 3), WEEKOFYEAR('2011-02-20');

+-----------------------+--------------------------+
| WEEK('2011-02-20', 3) | WEEKOFYEAR('2011-02-20') |
+-----------------------+--------------------------+
|                     7 |                        7 |
+-----------------------+--------------------------+

获取天数的函数

DAYOFYEAR(d) 函数返回d是一年中的第几天， 范围是从1～366。

DAYOFMONTH(d) 函数返回d是一个月中的第几天，范围是从1～31。

SELECT DAYOFYEAR('2016-02-20'), DAYOFMONTH('2016-02-20');

+-------------------------+--------------------------+
| DAYOFYEAR('2016-02-20') | DAYOFMONTH('2016-02-20') |
+-------------------------+--------------------------+
|                      51 |                       20 |
+-------------------------+--------------------------+

获取年份、季度、小时、分钟、秒数的函数

YEAR(date)  返回date对应的年份，范围是1970～2069。

SELECT YEAR('11-02-03'), YEAR('96-02-03');
# 00~69 转换为  2000～2069
# 70～99 转换为 1970～1999
+------------------+------------------+
| YEAR('11-02-03') | YEAR('96-02-03') |
+------------------+------------------+
|             2011 |             1996 |
+------------------+------------------+

QUARTER(date) 返回date对应的一年中的季度值，范围从1～4

SELECT QUARTER('16-04-01');

+---------------------+
| QUARTER('16-04-01') |
+---------------------+
|                   2 |
+---------------------+

MINUTE() 函数返回指定时间的分钟值

SELECT SECOND('16-02-03 10:10:03');

+-----------------------------+
| SECOND('16-02-03 10:10:03') |
+-----------------------------+
|                           3 |
+-----------------------------+

SECOND(time) 返回time对应的秒数，范围从0～59

SELECT SECOND('10:05:03');

+--------------------+
| SECOND('10:05:03') |
+--------------------+
|                  3 |
+--------------------+

获取日期的指定值的函数

EXTRACT(type FROM date) 函数所使用的时间间隔类型说明符同 DATE_ADD() 或 DATE_SUB() 的相同，但它从日期中提取一部分，而不是执行日期运算。

SELECT EXTRACT(YEAR FROM '2016-07-02') AS coll,
EXTRACT(YEAR_MONTH FROM '2016-07-12 01:02:03') AS coll2,
EXTRACT(DAY_MINUTE FROM '2016-07-12 01:02:03') AS coll3;

+------+--------+--------+
| coll | coll2  | coll3  |
+------+--------+--------+
| 2016 | 201607 | 120102 |
+------+--------+--------+

时间和秒钟转换的函数

TIME_TO_SEC(time) 返回已转化为秒的time参数。转换公式：小时3600+分钟60+秒

SELECT TIME_TO_SEC('23:23:00');

+-------------------------+
| TIME_TO_SEC('23:23:00') |
+-------------------------+
|                   84180 |
+-------------------------+

SEC_TO_TIME(seconds) 返回被转化为小时、分钟和秒数的seconds参数值，其格式为’HH:MM:SS’ 或 HHMMSS，具体格式根据该函数是够用在字符串或数字语境中而定。

SELECT SEC_TO_TIME(2345), SEC_TO_TIME(2345)+0,
	TIME_TO_SEC('23:23:00'), SEC_TO_TIME(84180);
	
	+-------------------+---------------------+-------------------------+--------------------+
| SEC_TO_TIME(2345) | SEC_TO_TIME(2345)+0 | TIME_TO_SEC('23:23:00') | SEC_TO_TIME(84180) |
+-------------------+---------------------+-------------------------+--------------------+
| 00:39:05          |                3905 |                   84180 | 23:23:00           |
+-------------------+---------------------+-------------------------+--------------------+

计算日期和时间的函数

计算日期和时间的函数有：DATE_ADD()、 ADDDATE()、DATE_SUB()、SUBDATE()、ADDTIME()、 SUBTIME()和DATE_DIFF() 。

DATE_ADD(date, INTERVAL expo type) 和 DATE_SUB(date, INTERVAL expo type)，其中，date 是一个DATETIME 或 DATE 值，用来指定起始时间。expr 是一个表达式，用来指定从起始日期添加或减去的时间间隔值。expr 是一个字符串；对于负值的时间间隔，他可以以一个符号-开头。type 为关键字，它指示了表达式被解释的方式。

type 值 预期的 expr 格式
MICROSECOND MICROSECONDS
SECOND SECONDS
MINUTE MINUTES
HOUR HOURS
DAY DAYS
WEEK WEEKS
MONTH MONTHS
QUARTER QUARTERS
YEAR YEARS
SECOND_MICROSECOND ‘SECONDS.MICROSECONDS’
MINUTE_MICROSECOND ‘MINUTES.MICROSECONDS’
MINUTE_SECOND ‘MINUTES:SECONDS’
HOUR_MICROSECOND ‘HOURS.MICROSECONDS’
HOUR_SECOND ‘HOURS:MINUTES:SECONDS’
HOUR_MINUTE ‘HOURS:MINUTES’
DAY_MICROSECOND ‘DAYS.MICROSECONDS’
DAY_SECOND ‘DAYS HOURS:MINUTES:SECONDS’
DAY_MINUTE ‘DAYS HOURS:MINUTES’
DAY_HOUR ‘DAYS HOURS’
YEAR_MONTH ‘YEARS-MONTHS’

若date参数是一个DATE值，计算只会包括YEAR、MONTH和DAY部分（即没有时间部分），其结果是一个DATE值。否则，结果将是一个DATETIME值。

DATE_ADD(date, INTERVAL expo type) 和 ADDDATE(date, INTERVAL expo type) 两个函数的作用相同，执行日期的加减运算。

SELECT DATE_ADD('2010-12-31 23:59:59', INTERVAL 1 SECOND) as col1,
	ADDDATE('2010-12-31 23:59:59', INTERVAL 1 SECOND) as col2,
	DATE_ADD('2010-12-31 23:59:59', INTERVAL '1:1' MINUTE_SECOND) as col3;
	
+---------------------+---------------------+---------------------+
| col1                | col2                | col3                |
+---------------------+---------------------+---------------------+
| 2011-01-01 00:00:00 | 2011-01-01 00:00:00 | 2011-01-01 00:01:00 |
+---------------------+---------------------+---------------------+

DATE_SUB(date, INTERVAL expr type) 或者 DUBDATE(date, INTERVAL expo type) 两个函数作用相同，执行日期的减运算。

SELECT DATE_SUB('2011-01-02', INTERVAL 31 DAY) as col1,
	SUBDATE('2011-01-02', INTERVAL 31 DAY) as col2,
	DATE_SUB('2011-01-01 00:01:00', INTERVAL '0 0:1:1' DAY_SECOND) as col3;
	
+------------+------------+---------------------+
| col1       | col2       | col3                |
+------------+------------+---------------------+
| 2010-12-02 | 2010-12-02 | 2010-12-31 23:59:59 |
+------------+------------+---------------------+

ADDTIME(date, expr) 函数将expr值添加到date，并返回修改后的值，date是一个日期或者日期时间表达式，而expr是一个时间表达式。

SELECT ADDTIME('2000-12-31 23:59:59', '1:1:1'), ADDTIME('02:02:02', '02:00:00');

+-----------------------------------------+---------------------------------+
| ADDTIME('2000-12-31 23:59:59', '1:1:1') | ADDTIME('02:02:02', '02:00:00') |
+-----------------------------------------+---------------------------------+
| 2001-01-01 01:01:00                     | 04:02:02                        |
+-----------------------------------------+---------------------------------+

DATEDIFF(date1, date2) 返回起始时间date1和结束时间date2之间的天数。date1和date2为日期或date-and-time表达式。计算中只用到这些值的日期部分。

SELECT DATEDIFF('2010-12-31 23:59:59', '2010-12-30') AS col1,
	DATEDIFF('2010-11-30 23:59:59', '2010-12-31') AS col2;
	
+------+------+
| col1 | col2 |
+------+------+
|    1 |  -31 |
+------+------+

将日期和时间格式化的函数

DATE_FORMAT(date, format) 根据format指定的格式显示date值。主要format格式如下

说明符 说明
%a 工作日的缩写名称(Sun..Sat)
%b 月份的缩写名称(Jan..Dec)
%c 月份，数字形式(0..12)
%D 带有英语后缀的该月日期(0th, 1st, 2nd, 3rd,…)
%d 该月日期，数字形式(00..31)
%e 该月日期，数字形式(0..31)
%f 微秒(000000..999999)
%H 以2位数表示24小时(00..23)
%h, %I 以2位数表示12小时(01..12)
%i 分钟，数字形式(00..59)
%j 一年中的天数(001..366)
%k 以24(0..23)小时表示时间
%l 以12(1..12)小时表示时间
%M 月份名称(January..December)
%m 月份，数字形式(00..12)
%p 上午(AM)或下午(PM)
%r 时间，12小时制(小时 hh: 分钟 mm: 秒数 ss 后加AM或PM)
%S, %s 以2位数形式表示秒(00..59)
%T 时间，24小时制(小时 hh: 分钟 mm: 秒数 ss)
%U 周(00..53)，其中周日为每周的第一天
%u 周(00..53)，其中周一为每周的第一天
%V 周(01..53)，其中周日为每周的第一天；和%X同时使用
%v 周(01..53)，其中周一为每周的第一天；和%x同时使用
%W 工作日名称(周日..周六)
%w 一周中的每日(0=周日..6=周六)
%X 该周的年份，其中周日为每周的第一天；数字形式，4位数；和%V同时使用
%x 该周的年份，其中周一为每周的第一天；数字形式，4位数；和%v同时使用
%Y 4位数形式表示年份
%y 2位数形式表示年份
%% ‘%‘文字字符 
SELECT DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y') AS col1,
	DATE_FORMAT('1997-10-04 22:23:00', '%D %y %a %d %m %b %j') AS col2;
	
+-----------------------+--------------------------+
| col1                  | col2                     |
+-----------------------+--------------------------+
| Saturday October 1997 | 4th 97 Sat 04 10 Oct 277 |
+-----------------------+--------------------------+

TIME_FORMAT(time, format) 根据format字符串安排time值的格式。format字符串可能仅会处理包含小时、分钟和秒的格式说明符，其他说明符产生一个NULL值或0。若time值包含一个大于23的小时部分，则%H和%k小时格式说明符会产生一个大于0..23的通常范围的值。

SELECT TIME_FORMAT('16:00:00', '%H %k %h %I %l');

+-------------------------------------------+
| TIME_FORMAT('16:00:00', '%H %k %h %I %l') |
+-------------------------------------------+
| 16 16 04 04 4                             |
+-------------------------------------------+

GET_FORMAT(val_type, format_type) 返回日期时间字符串的显示格式，val_type 表示日期数据类型，包括DATE、DATETIME和TIME；format_type表示格式化显示类型，包括EUR、INTERVAL、IOS、JIS、USA。GET_FORMAT 根据两个值类型组合返回的字符串显示格式如下表。

值类型 格式化类型 显示格式字符串
DATE EUR %d.%m.%Y
DATE INTERVAL %Y%m%d
DATE ISO %Y-%m-%d
DATE JIS %Y-%m-%d
DATE USA %m.%d.%Y
TIME EUR %H.%i.%s
TIME INTERVAL %H%i%s
TIME ISO %H:%i:%s
TIME JIS %H:%i:%s
TIME USA %H:%i:%s %p
DATETIME EUR %Y-%m-%d %H.%i.%s
DATETIME INTERVAL %Y%m%d%H%i%s
DATETIME ISO %Y-%m-%d %H:%i:%s
DATETIME JIS %Y-%m-%d %H:%i:%s
DATETIME USA %Y-%m-%d %H.%i.%s 
SELECT GET_FORMAT(DATE, 'EUR'), GET_FORMAT(DATE, 'USA');

+-------------------------+-------------------------+
| GET_FORMAT(DATE, 'EUR') | GET_FORMAT(DATE, 'USA') |
+-------------------------+-------------------------+
| %d.%m.%Y                | %m.%d.%Y                |
+-------------------------+-------------------------+

SELECT DATE_FORMAT('2000-10-05 22:23:00', GET_FORMAT(DATE, 'USA'));

+-------------------------------------------------------------+
| DATE_FORMAT('2000-10-05 22:23:00', GET_FORMAT(DATE, 'USA')) |
+-------------------------------------------------------------+
| 10.05.2000                                                  |
+-------------------------------------------------------------+

条件判断函数

IF(expr, v1, v2)函数

IF(expr, v1, v2)，如果表达式expr是TRUE(expr <> 0 and expr <> NULL)，则IF()的返回值为v1；否则返回值为v2。IF()的返回值为数字值或字符串值，具体情况视其所在语境而定。

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                                    |
+-------------+----------------------+---------------------------------------+

IFNULL(v1,v2)函数

IFNULL(v1,v2)假如v1不为NULL，则IFNULL()的返回值为v1；否则其返回值为v2.

IFNULL()的返回值为数字值或字符串值，具体情况视其所在语境而定。

SELECT IFNULL(1,2), IFNULL(NULL, 10), IFNULL(1/0, 'wrong');

+-------------+------------------+----------------------+
| IFNULL(1,2) | IFNULL(NULL, 10) | IFNULL(1/0, 'wrong') |
+-------------+------------------+----------------------+
|           1 |               10 | wrong                |
+-------------+------------------+----------------------+

CASE 函数

CASE expr WHEN v1 THEN r1 [WHEN v2 THEN r2] [ELSE rn] END

该函数表示，如果expr值等于某个vn，则返回对应位置THEN后面的结果。如果与所有值都不想等，返回ELSE后面的rn。

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                                                        |
+------------------------------------------------------------+

SELECT CASE WHEN 1<0 THEN 'true' ELSE 'false' END;

+--------------------------------------------+
| CASE WHEN 1<0 THEN 'true' ELSE 'false' END |
+--------------------------------------------+
| false                                      |
+--------------------------------------------+

系统信息函数

← Manual
Markdown 添加数学公式 →