MySQL学习笔记-第一篇-基础知识与命令_mysql -h 域名

目录

1 登录命令

1.1 主机域名登录

1.2 主机ip登录

2 库命令

2.1 显示库

2.2 创建数据库

2.3 删除数据库

2.4 MySQL-8.新特性

3 表命令

3.1 创建数据表

3.2 查看数据表结构

3.3 修改表数据结构

3.4 删除数据表

3.5 MySQL 8.0 的新特性 1 - 默认字符集改为utf8mb4

3.6 MySQL 8.0 新特性 2 自增变量的持久化

4 数据类型和运算符

4.1 MySQL 数据类型

4.2 浮点类型和定点数类型

4.3 日期与时间类型

4.4 文本字符串类型

4.5 二进制字符串类型

4.6 如何选择数据类型

4.7 常见运算符

5 MySQL 函数

5.1 数学函数

5.2 字符串函数

5.3 日期和时间函数

5.4 条件判断函数

5.5 系统信息函数

5.6 加密函数

5.7 其它函数

---

1 登录命令

1.1 主机域名登录

mysql -h 主机域名 -u 用户名 -p （回车后输入密码）
 
例子: mysql -h localhost -u root -p (******)

1.2 主机ip登录

命令: mysql -h 主机ip -u 用户名 -p （回车后输入密码）
 
例子: mysql -h 127.0.0.1 -u root -p (******)

2 库命令

2.1 显示库

命令: SHOW DATABASES;
 
例子: SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| world              |
+--------------------+
6 rows in set (0.01 sec)
 
其中，初始时会有六个MySQL自动创建的必备的数据库存在于其data目录下。

2.2 创建数据库

创建数据库是在系统磁盘上划分一块区域用于数据的存储和管理，如果管理员在设置权限的时候为用户创建了数据库，用户可以直接使用，否则用户需要自己创建数据库。

命令: CREATE DATABASE 数据库名
 
例子: CREATE DATABASE tx_test_db_1

创建完成之后查看自己创建的数据库：

命令: SHOW CREATE DATABASE 数据库名
 
例子: SHOW CREATE DATABASE tx_test_db_1\G
*************************** 1. row ***************************
       Database: tx_test_db_1
Create Database: CREATE DATABASE `tx_test_db_1` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */
1 row in set (0.00 sec)

再次查看当前服务器存在的所有库：

show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| tx_test_db_1       |
| world              |
+--------------------+
7 rows in set (0.00 sec)

注意，数据库名不能重复，否则会报错如下：

ERROR 1007 (HY000): Can't create database 'tx_test_db_1'; database exists

2.3 删除数据库

删除数据库是将已经存在的数据库从磁盘空间上清除，数据库中的所有数据也将一同被删除。

命令: DROP DATABASE 数据库名
 
例子: drop database tx_test_db_1;

删除之后再查看刚刚创建的数据库会报错：

show create database tx_tests_db_1;
ERROR 1049 (42000): Unknown database 'tx_tests_db_1'

删除不存在的数据库也会报错：

drop database tx_test_db_1;
ERROR 1008 (HY000): Can't drop database 'tx_test_db_1'; database doesn't exist

再次查看当前服务器存在的所有库：

show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| world              |
+--------------------+
6 rows in set (0.00 sec)

已经成功删除我们上次刚刚创建的tx_test_db_1库了。

2.4 MySQL-8.新特性

系统库infomation_schema中的系统表全部替换为InnoDB引擎，默认的MySQL实例将不包含其它引擎的系统表。

select distinct(engine) from information_schema.tables;
+--------------------+
| ENGINE             |
+--------------------+
| NULL               |
| InnoDB             |
| CSV                |
| PERFORMANCE_SCHEMA |
+--------------------+
4 rows in set (0.02 sec)

3 表命令

3.1 创建数据表

所谓创建数据表，是指在已经创建的数据库中建立新表。创建数据表的过程是规定数据列的属性的过程，同时也是实施数据完整性（包括实体完整性、引用完整性和域完整性等）约束的过程。

命令: CREATE TABLE <表名>
     （
        字段名1 数据类型[列级别约束条件] [默认值],
        字段名2 数据类型[列级别约束条件] [默认值],
        ......
        [表级别的约束条件]
      );
 
例如: 
mysql> use tx_test_db_1;
Database changed
mysql> create table test_1
    -> (
    -> id INT(11),
    -> name VARCHAR(25),
    -> age INT(11),
    -> PRIMARY KEY(id)
    -> )
    -> ;
Query OK, 0 rows affected, 2 warnings (0.03 sec)
 
mysql> create table test_2
    -> (
    -> id INT(11) PRIMARY KEY,
    -> name VARCHAR(25),
    -> id_card INT(21) UNIQUE
    -> );
Query OK, 0 rows affected, 2 warnings (0.03 sec)

注意：多列之间要用逗号隔开，但最后一列声明时不能带逗号，否则会报错：

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 6

同时，创建表之前必须先指定数据库，否则也会报错：

mysql> create table test_1;
ERROR 1046 (3D000): No database selected

查看已经创建完成的表：

命令: SHOW CREATE TABLE 表名
 
例子:
mysql> show create table test_1;
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                      |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test_1 | CREATE TABLE `test_1` (
  `id` int NOT NULL,
  `name` varchar(25) DEFAULT NULL,
  `age` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

查看该库中所有的表：

命令: SHOW TABLES
 
例子:
mysql> show tables;
+------------------------+
| Tables_in_tx_test_db_1 |
+------------------------+
| test_1                 |
| test_2                 |
+------------------------+
2 rows in set (0.00 sec)

3.1.1 使用主键约束

主键又称主码，是表中一列或多列的组合。主键约束要求主键列的数据唯一，并且不允许为空。主键能够唯一的标识表中的一条记录，可以结合外键来定义不同数据表之间的关系，并且可以加快数据库查询的速度。主键分为两种类型：单字段主键和多字段联合主键。

1.单字段主键

主键由一个字段组成，SQL语句格式分为以下两种情况。

（1）在定义列的同时指定主键，语法规则如下：

字段名 数据类型 PRIMARY KEY [默认值]

（2）在定义完所有列之后指定主键：

[CONSTRAINT <约束名>] PRIMARY KEY [字段名]

2.多字段主键

主键由多个字段组成，SQL语句格式只有一种。

（1）在定义完所有列之后指定主键：

[CONSTRAINT <约束名>] PRIMARY KEY [字段名1, 字段名2, ..., 字段名n]

3.1.2 使用外键约束

外键用来在两个表之间建立联系，可以是一列或者多列。一个表可以有一个或多个外键。外键对应的是参照完整性，一个表的外键可以为空值，若不为空值，则每一个外键值必须等于另一个表中主键的某个值。

主表：外键所依赖的表。

从表：外键所在的表。

外键是表中的一个字段，对应另一个表的主键，主要作用是保证数据的完整性、一致性，定义外键后，不允许在有子表关联的情况下删除主表中的关联行。

创建外键的语法规则如下：

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

创建一个部门表tb_dept1: 

mysql> create table tb_dept1
    -> (
    -> id INT(11) PRIMARY KEY COMMENT "部门编号",
    -> name VARCHAR(25) NOT NULL COMMENT "部门名称",
    -> location VARCHAR(50) COMMENT "部门位置"
    -> );
Query OK, 0 rows affected, 1 warning (0.03 sec)

定义数据表tb_emp5,让它的字段deptId作为外键关联到tb_dept1的主键id:

mysql> CREATE TABLE tb_emp5
    -> (
    -> id INT(11) PRIMARY KEY,
    -> name VARCHAR(25),
    -> deptId INT(11),
    -> salary FLOAT,
    -> CONSTRAINT fk_emp_dept1 FOREIGN KEY(deptId) REFERENCES tb_dept1(id)
    -> );
Query OK, 0 rows affected, 2 warnings (0.04 sec)

注意：关联字段的数据类型必须匹配，否则在创建子表的时候会报错：

ERROR 1005(HY000): Can't create table 'database.tablename'(error: 150)

3.1.3 使用非空约束

非空约束指字段值不能为空。对于使用了非空约束的字段，如果用户在添加数据时没有指定值，数据库系统会报错。非空约束的规则如下：

字段名 数据类型 NOT NULL 

3.1.4 使用唯一性约束

唯一性约束要求该列唯一，允许为空但是只能出现一个空值。唯一性约束可以确保一列或者几列不出现重复值。唯一性约束的语法规则如下：

1.在定义完列之后指定唯一性约束，语法规则如下：

字段名 数据类型 UNIQUE

2.在定义完所有列后指定唯一性约束，语法规则如下：

[CONSTRAINT <约束名>] UNIQUE(<字段名>)

注意：UNIQUE 和 PRIMARY KEY 的区别：一个表中可以有多个字段声明为UNIQUE,但却只能有一个 PRIMARY KEY 声明；声明为 PRIMARY KEY 的列不允许有空值，但声明为 UNIQUE 的列允许有一个空值（NULL）。

3.1.5 使用默认约束

默认约束指定某列的默认值，如果插入数据时该列无值，则取默认值。语法规则如下：

字段名 数据类型 DEFAULT 默认值

3.1.6 设置表的属性值自动增加

可以通过为表主键添加 AUTO_INCREMENT 关键字自动生成字段的主键值。默认的，初始值为   1，每新增一条记录字段值自动加 1。一个表只能有一个字段使用 AUTO_INCREMENT 约束，且该字段必须为主键值的一部分。AUTO_INCREMENT 约束的字段可以是任何整数类型：

字段名 数据类型 AUTO_INCREMENT

3.2 查看数据表结构

查看数据表结构可以使用DESCRIBE和SHOW CREATE TABLE语句。

3.2.1 查看基本表结构语句 DESCRIBE

DESCRIBE/DESC 语句可以查看表的字段信息，其中包括字段名、字段数据类型、是否为主键、是否可以存储NULL值、是否有默认值等。语法规则如下：

DESCRIBE 表名
 
或者简写为：
 
DESC 表名

3.2.2 查看表详细结构语句

SHOW CREATE TABLE <表名/G>

3.3 修改表数据结构

常用的操作有修改表名、修改字段名或数据类型、增加和删除字段、修改字段的排列位置、修改表的存储引擎、删除表的外键约束等。

3.3.1 修改表名

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

3.3.2 修改字段的数据类型

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

3.3.3 修改字段名

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

3.3.4 添加字段

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

若没有填写位置参数，则新添加的字段放在最后。

3.3.5 删除字段

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

3.3.6 修改字段的排列位置

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

3.3.7 修改表的存储引擎

存储引擎是MySQL中的数据存储在文件或者内存中时采用的不同的技术实现。可以根据自己的需要，切换不同的存储引擎。可以用 ”SHOW ENGINES;“ 语句查看当前系统支持的存储引擎。

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

3.3.8 删除表的外键约束

对于数据库中定义的外键，如果不再需要，可以将其删除。外键一旦删除，就会解除主表和从表之间的关联关系，语法规则如下：

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

3.4 删除数据表

删除数据表分为两种情况，一种是删除没有被其它表关联的表：

DROP TABLE [IF EXISTS] 表1, 表2, ..., 表n

另一种是删除的表被其它表关联：此时需要先找到其它所有关联表，将其与本次要删除的表主键关联的外键约束删除后才能进行本次删除。

3.5 MySQL 8.0 的新特性 1 - 默认字符集改为utf8mb4

在 MySQL 8.0 之前，默认的字符集为 latin1 ，utf8字符集指向的是utf8mb3。网站开发人员在数据库设计的时候往往会将编码设置为 utf8 字符集，如果遗忘修改默认的编码，就会出现乱码的问题。从 MySQL 8.0 开始，默认的编码改为 utf8mb4 从而避免了上述问题。

查看数据库默认编码：

命令: SHOW VARIABLES LIKE 'character_set_database';
 
例子: 
mysql> show variables like 'character_set_database';
+------------------------+---------+
| Variable_name          | Value   |
+------------------------+---------+
| character_set_database | utf8mb4 |
+------------------------+---------+
1 row in set, 1 warning (0.00 sec)

3.6 MySQL 8.0 新特性 2 自增变量的持久化

在 MySQL 8.0 之前，自增主键 AUTO_INCREMENT 的值如果大于 max(primary key) + 1，这种情况下，MySQL 重启后会重置 AUTO_INCREMENT = max(primary key) + 1，这种现象在某些情况下会导致业务主键冲突或者其它难以发现的问题，出现上述结果的原因是自增主键没有持久化。对于MySQL 8.0 之前的系统中，对于自增主键的分配规则，是由InnoDB数据字典内部的一个计数器来决定的，而该计数器只在内存中维护，并不会持久化到磁盘。当数据库重启时，该计数器会通过下面这种方式初始化：

SELECT MAX(ai_col) FROM table_name FOR UPDATE

MySQL 8.0 将自增主键的计数器持久化到重做日志中。每次计数器发生改变，都会将其写入重做日志。如果数据库重启，InnoDB会根据重做日志中的信息来初始化计数器的内存值。

4 数据类型和运算符

4.1 MySQL 数据类型

MySQL 中数据类型主要有数值类型、日期类型和字符串类型。

1. 数值类型：包括整型TINIINT、SMALLINT、MEDIUMINT、INT、BIGINT，浮点小数型FLOAT和DOUBLE，定点小数型DECIMAL。

2. 日期和时间类型：YEAR、TIME、DATE、DATETIME和TIMESTAMP。

3. 字符串类型：CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET等

4.1.1 整数类型

整数类型的字段可以添加 AUTO_INCREMENT 自增约束条件。

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

id字段的数据类型为INT(11)，注意后面的11，它表示的是该数据类型指定的宽度，即能够显示的数值中数字的个数，例如。假设声明一个INT类型的字段：

year INT(4)

该声明表示，在year字段中的数据一般只显示4位数字的宽度。

显示宽度和数据类型的取值范围是无关的，显示宽度只是指明 MySQL 最大可能显示的数字个数，数值的位数小于宽度时由空格自动填充；如果插入了大于显示宽度的值，只要该值不超过该类型整数的取值范围，数值依然可以插入，而且能够显示出来。如果不指定宽度，系统为每一种类型指定默认的宽度值（即与其有符号数的最小值的宽度相同）。

不同的整数类型有不同的取值范围，并且需要不同的存储空间，因此，应该根据实际需要选择最适合的类型，这样有利于提高查询的效率和节省存储空间。

显示宽度只用于显示提示，并不能限制取值范围和占用空间。

4.2 浮点类型和定点数类型

浮点数和定点数都可以用（M,N）表示，其中M称为精度，表示总共的位数；N称为标度，表示小数的位数。

例：无论是浮点数还是定点数，如果用户指定的精度超出精度范围，则会四舍五入。

mysql> CREATE TABLE tmp2
    -> (
    -> x FLOAT(5,1),
    -> y DOUBLE(5,1),
    -> z DECIMAL(5.1)
    -> );
Query OK, 0 rows affected, 2 warnings (0.04 sec)

向表中插入数据：

mysql> INSERT INTO tmp2 VALUES(5.11122, 5.15, 5.123);
Query OK, 1 row affected, 1 warning (0.02 sec)

可以看到，上述插入语句会有一个警告信息，使用 SHOW WARNINGS；语句查看警告信息：

mysql> SHOW WARNINGS;
+-------+------+----------------------------------------+
| Level | Code | Message                                |
+-------+------+----------------------------------------+
| Note  | 1265 | Data truncated for column 'z' at row 1 |
+-------+------+----------------------------------------+
1 row in set (0.00 sec)

可以看到，FLOAT 和 DOUBLE 在进行四舍五入时没有给出警告，只给出字段z数值被截断的警告，查看结果：

mysql> SELECT * FROM tmp2;
+------+------+------+
| x    | y    | z    |
+------+------+------+
|  5.1 |  5.2 |    5 |
+------+------+------+
1 row in set (0.00 sec)

可以看到，我们建表的时候搞错了DECIMAL的数据类型的精度，裂了，现在改一下：

mysql> ALTER TABLE tmp2 MODIFY z DECIMAL(5,1);
Query OK, 1 row affected (0.06 sec)
Records: 1  Duplicates: 0  Warnings: 0

这回好看一些：

mysql> SELECT * FROM tmp2;
+------+------+------+
| x    | y    | z    |
+------+------+------+
|  5.1 |  5.2 |  5.0 |
+------+------+------+
1 row in set (0.00 sec)

FLOAT 和 DOUBLE在不指定精度的时候默认会按照实际精度（由计算机硬件和操作系统决定），DECIMAL若不指定精度默认为（10,0）。

浮点数对于定点数的优点是长度一定的情况下，浮点数能表示更大的数据范围；它的缺点是会引起精度问题。

在MySQL中，定点数以字符串形式存储，在对精度要求比较高的时候（货币、科学计算），使用DECIMAL类型比较好，另外两个浮点数在进行加减运算和比较运算时容易出问题，所以在使用浮点数时要注意，尽量避免做浮点数比较。

4.3 日期与时间类型

1. YEAR 

YEAR 类型是一个单字节类型，用于表示年，在存储时只需要一个字节。可以使用各种类型格式指定YEAR值，如下所示：

（1）以四位字符串或四位数字表示的YEAR，范围为‘1901’ ~ ‘2155’。输入格式为 'YYYY' 或 YYYY。例如：输入 ‘2010’ 或 2010，插入到数据库均为2010。

（2）以两位字符串表示的YEAR，范围为 ‘00’ 到 ‘99’ 。'00' ~ '69' 和 ‘70’ ~ ‘99’ 范围的值分别被表示为2000 ~ 2069和1970 ~ 1999范围的YEAR值。‘0’ 与 ‘00’ 的作用相同。插入超过取值范围的值将被转换为2000.

（3）以2位数字表示的YEAR，范围为1 ~ 99。1 ~ 69 和 70 ~ 99范围的值分别被转换为2001 ~ 2069 和 1970 ~ 1999范围的YEAR值。注意：在这里0值将被转换为0000而不是2000。非法的YEAR值将被转换为0000。

2. TIME 

TIME 类型小时部分会如此大的原因是TIME类型不仅仅可以表示一天的时间，还可以表示某个事件过去的时间或者两个事件的时间间隔（可以大于24小时，甚至为负）。可以使用各种类型格式指定TIME 值，如下所示：

（1）‘D HH:MM:SS’格式的字符串。可以使用下面的任意一种“非严格”的语法：

‘HH:MM:SS’、‘HH:MM’、'D HH:MM'、‘D HH’或‘SS’。这里的D表示日，可以取0~34之间的值。在插入数据库时，D被转换为小时保存，格式为 “D*24 + HH”。

（2）‘HHMMSS’格式的、没有间隔符号的字符串或者HHMMSS格式的数值，假定是有意义的时间。例如 ‘101112’ 被理解为 ‘10:11:12’ ，但 ‘109712’ 是不合法的，存储时将被转换为 00:00:00。

注意：如果没有冒号，MySQL解释值时，假定最右边两位表示秒（被认为是过去的时间），相反，则被认为是当天的时间。

3. DATE 

DATE类型用在仅需要日期值时，没有时间部分，在存储时需要3字节。日期格式为 'YYYY-MM-DD' 。可以使用字符串类型或者数字类型插入，如下：

（1）以 ‘YYYY-MM-DD’ 或者 'YYYYMMDD' 字符串格式表示的日期，取值范围为‘1000-01-01’ ~ ’9999-12-31‘。

（2）以‘YY-MM-DD’ 或者 'YYMMDD' 字符串表示的日期，‘00’ ~ ‘69’ 表示 ‘2000 ~ 2069’；‘70’ ~ ‘99’ 表示 ‘1970 ~ 1999’。

（3）以YY-MM-DD 或 YYMMDD 数字格式表示的，和（2）中规则一样。

（4）用 CURRENT_DATE() 或者 NOW() 插入当前系统日期，CURRENT_DATE() 只返回当前系统日期，不包括时间部分，NOW() 函数返回日期和时间。MySQL允许不严格的语法，任何标点符号都可以作为日期的分隔符，如：2022^05^12。

4. DATETIME

规则和之前DATE以及TIME类似，只是这里的时间就是当天时间。直接实操一下吧，写字累了！

mysql> CREATE TABLE tmp6
    -> (
    -> dt DATETIME
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO tmp6 VALUES(NOW());
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO tmp6 VALUES(220512071326);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO tmp6 VALUES(970406080000);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO tmp6 VALUES(97468000);
ERROR 1292 (22007): Incorrect datetime value: '97468000' for column 'dt' at row 1

mysql> SELECT * FROM tmp6;
+---------------------+
| dt                  |
+---------------------+
| 2022-05-12 07:12:21 |
| 2022-05-12 07:13:26 |
| 1997-04-06 08:00:00 |
+---------------------+
3 rows in set (0.00 sec)

5. TIMESTAMP

显示宽度固定在19个字符，存储时需要4个字节。取值范围小于DATETIME，为 '1970-01-01 00:00:00' UTC ~ '2038-01-19 03:14:07' UTC，其中UTC为世界标准时间。

TIMESTAMP 与 DATETIME 除了存储字节和支持的范围不同外，还有一个最大的区别就是：DATETIME 在存储日期数据时，按实际输入的格式存储，与时区无关；而 TIMESTAMP 值的存储是以 UTC 格式保存的，存储时对当前时区进行转换，检索时再转换回当前时区，查询时，不同时区显示的值是不同的。

mysql> CREATE TABLE tmp7
    -> (
    -> ts TIMESTAMP
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO tmp7 VALUES(NOW());
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM tmp7;
+---------------------+
| ts                  |
+---------------------+
| 2022-05-12 07:36:20 |
+---------------------+
1 row in set (0.00 sec)

由于现在我是再东八区，试试转换时区后再读取值：

mysql> SHOW VARIABLES LIKE 'TIME_ZONE';
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| time_zone     | +08:00 |
+---------------+--------+
1 row in set, 1 warning (0.00 sec)

mysql> SET TIME_ZONE='+10:00';
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'TIME_ZONE';
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| time_zone     | +10:00 |
+---------------+--------+
1 row in set, 1 warning (0.00 sec)

mysql> SELECT * FROM tmp7;
+---------------------+
| ts                  |
+---------------------+
| 2022-05-12 09:36:20 |
+---------------------+
1 row in set (0.00 sec)

由结果可以看到，因为东10区比东8区快了两个小时，所以查询结果经过时间转换后，显示的值增加了2个小时。相同的，如果时区每减小一个值，查询显示的日期中的小时数减1。

4.4 文本字符串类型

4.4.1 CHAR 和 VARCHAR

CHAR(M)为固定长度字符串，在定义时指定字符列长。当保存时在右侧填充空格，以达到指定的长度。M表示列的长度，M 的范围为 0 ~ 255 个字节。当检测到实际值时，尾部的空格会被删除。

VARCHAR(M)是长度可变的字符串，M表示最大列长。M的范围是 0 ~ 65535 字节，实际长度为实际占用空间的字符串长度+1。当检测到实际值时，尾部的空格不会被删除。

4.4.2 TEXT类型

TEXT列保存非二进制字符串，如文章、评论等。当保存或查询TEXT列的值时，不删除尾部空格。

4.4.3 ENUM类型

ENUM是字符串对象，其值为表创建时规定的一系列枚举值，语法格式如下：

字段名 ENUM('值1','值2',..., '值n')

ENUM类型的字段在取值时，一次只能取一个枚举列表中的值，创建的成员中含有空格时，其尾部的空格将被自动删除。ENUM值在内部用整数表示，每个枚举值均有一个索引值，列表值所允许的成员值从1开始编号，MySQL存储的就是这个索引编号。

例如，定义ENUM类型的列 enumCol('first', 'second', 'third'), 则该列可以取的值和每个值的索引为:

ENUM 值依照列索引顺序排列，空字符串在非空字符串之前，NULL值排在其它所有值之前。

ENUM 值总有一个默认值：如果将 ENUM 列声明为 NULL，NULL 值为该列的一个有效值，并且为默认值；如果将 ENUM 列声明为 NOT NULL，其默认值为允许的值列表的第一个元素。

4.4.4 SET 类型

SET ('值1', '值2', ..., '值n')

内部使用整数表示，列表中每一个值都有一个索引编号。当创建表时，SET成员值最后的空格会被删除。与 ENUM 不同的是，ENUM 类型的字段只能从定义的列值中选择一个插入，而 SET 类型的列可以从定义的列值中选择多个字符串的联合。如果插入的 SET 值有重复，MySQL会删除重复的值；插入的 SET 值顺序不重要，最终会按照定义的顺序显示，若插入不存在的值，则会报错。

mysql> CREATE TABLE tmp11
    -> (
    -> test_set SET('兔仔','猪仔','皮仔','狗哥','花猪')
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO tmp11 VALUES('猪仔,兔仔'),('花猪,狗哥,皮仔'),('兔仔,兔仔,兔仔');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> INSERT INTO tmp11 VALUES('花猪的女朋友');
ERROR 1265 (01000): Data truncated for column 'test_set' at row 1

mysql> SELECT * FROM tmp11;
+----------------------+
| test_set             |
+----------------------+
| 兔仔,猪仔            |
| 皮仔,狗哥,花猪       |
| 兔仔                 |
+----------------------+
3 rows in set (0.00 sec)

可以看到，如果插入了列表中没有的值，将阻止该值插入；插入重复的值，会自动去重；插入时的顺序并不影响最终显示结果。

4.5 二进制字符串类型

4.5.1 BIT 类型

BIT 类型是位字段类型。M 表示每个值的位数，范围为 1 ~ 64。如果 M 省略，默认为1。如果为BIT(M) 列分配的值的长度小于 M 位，就在左边用 0 填充。MySQL不允许插入超过该列允许范围的值：

mysql> CREATE TABLE tmp12
    -> (
    -> b BIT(4)
    -> );
Query OK, 0 rows affected (0.03 sec)

MySQL 不允许插入超过列允许范围的值：

mysql> INSERT INTO tmp12 VALUES(2),(9),(14),(15),(16);
ERROR 1406 (22001): Data too long for column 'b' at row 5

删除（16）后成功插入：

mysql> INSERT INTO tmp12 VALUES(2),(9),(14),(15);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

MySQL底层保存的是数值的十六进制形式：

mysql> SELECT * FROM tmp12;
+------------+
| b          |
+------------+
| 0x02       |
| 0x09       |
| 0x0E       |
| 0x0F       |
+------------+
4 rows in set (0.00 sec)

使用 b+0 查看该列的十进制数字形式的值：

mysql> SELECT b+0 FROM tmp12;
+------+
| b+0  |
+------+
|    2 |
|    9 |
|   14 |
|   15 |
+------+
4 rows in set (0.01 sec)

使用函数 BIN() 将十进制值转换为对应的二进制形式：

mysql> SELECT BIN(b+0) FROM tmp12;
+----------+
| BIN(b+0) |
+----------+
| 10       |
| 1001     |
| 1110     |
| 1111     |
+----------+
4 rows in set (0.01 sec)

4.5.2 BINARY 和 VARBINARY 类型

用来存储二进制字节字符串，语法格式如下：

字段名 BINARY(M) 或 VARBINARY(M) 

BINARY 长度固定，指定长度后，若数值不够长，将在右边添加 ’\0‘ 补充到 M。

VARBINARY 是可变长的，最大长度为 M, 实际长度为值实际长加1。

4.5.3 BLOB 类型

BLOB 是一个二进制大对象，用来存储可变数量的数据，存储的是二进制字节字符串；TEXT 列存储的是非二进制字符串。 BLOB 列没有字符集，排序和比较基于列值字节的数值；TEXT 列有一个字符集，并根据字符集进行比较、排序。

4.6 如何选择数据类型

MySQL 提供了大量的数据类型，为了优化存储、提高数据库性能，在任何情况都应该选择精准的数据类型。

1. 整数和浮点数

如果不需要小数部分，就是用整数来保存数值。

2. 浮点数和定点数

浮点数相对于定点数的优势是：在长度一定的情况下了浮点数能表示的范围更大，但浮点数容易产生精度丢失，所以对精度要求高的时候，建议使用DECIMAL来存储。

3. 日期和时间类型

如果只需要年，就用YEAR；如果只需要表示时间，就用TIME；如果只需要表示日期，就用DATE;如果需要同时记录日期和时间，可以使用 DATETIME 或者 TIMESTAMP 。由于 TIMESTAMP 只能存储 1970-01-01 00:00:01 UTC ~ 2038-01-19 -3:16:05 UTC 的时间，所以当要保存更大范围的日期和时间的时候就要使用 DATETIME。TIMESTAMP还有一个DATETIME不具备的属性，默认情况下，当插入一条记录但没有指定 TIMESTAMP 这个列时，MySQL 会把 TIMESTAMP 列设为当前时间。因此当需要插入记录的同时记录当前时间，使用 TIMESTAMP 是方便的；另外，TIMESTAMP 在空间上比 DATETIME 更有效。

4. CHAR 与 VARCHAR 之间的特点与选择

CHAR 与 VARCHAR 之间的区别如下：

• CHAR 是固定长度的字符串，VARCHAR 是可以变长度的字符串。
• CHAR 会自动删除数据的尾部空格，VARCHAR 不会删除数据的尾部空格。

CHAR 是固定长度的，所以它的处理速度比 VARCHAR 快，但它的缺点是浪费存储空间，所以对于存储不大但是对于速度要求较高的可以使用 CHAR 类型，反之可以使用 VARCHAR 实现。

存储引擎对于选择 CHAR 和 VARCHAR 的影响：

• 对于 MyISAM ：最好使用 CHAR 。这样可以使整个表静态化，从而使数据检索速度更快，用空间换时间。
• 对于 InnoDB ：使用 VARCHAR 。因为 InnoDB 数据表的存储格式不分固定长度和可变长度，因此使用 CHAR 不一定比 VARCHAR 快，但由于 VARCHAR 是按照实际长度存储的，比较节省空间，所以对磁盘 I/O 和数据存储总量比较好。

5. ENUM 和 SET 

ENUM 只能取单值，它的合法值列表最多允许 65535 个成员。因此，在需要从多个值中选择一个时，可以使用 ENUM 。比如：性别。

SET 可取多值，它的合法值列表最多允许 64 个成员。空字符串也是一个合法的 SET 值。在要取多个值时，适合用 SET ，比如：兴趣爱好。

6. BLOB 和 TEXT 

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

4.7 常见运算符

1. 算数运算符

除法运算精度是四位小数，若除数为零，则结果为 NULL 。

2. 比较运算符

数值比较时的规则：

• 若比较值存在 NULL，则结果为 NULL。
• 若两值均为字符串，则按字符串比较。
• 若两值均为数值，则按数值进行比较。
• 若一个为字符串、一个为数值，则会将字符串转换为数值。

安全等于 <==> 运算符可以用来判断 NULL 值，若两者均为 NULL，返回 1，若只有一者为 NULL，返回 0。

LIKE 运算符用来匹配字符串，语法格式为：expr LIKE 匹配条件。如果 expr 满足匹配条件的，返回1; 否则，返回 0。expr 或 匹配条件中任何一个为 NULL，结果为 NULL。LIKE 运算符在进行匹配时，可以使用下面两种通配符：

（1）’%‘，匹配任何数目的字符，甚至包括零字符。

（2）’_‘，只能匹配一个字符。

mysql> CREATE TABLE str_like
    -> (
    -> name VARCHAR(10)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> SELECT * FROM str_like;
+------------+
| name       |
+------------+
| litaixun   |
| luoyongmei |
+------------+
2 rows in set (0.00 sec)

全字符串直接匹配：

mysql> SELECT * FROM str_like WHERE name LIKE 'li';
Empty set (0.00 sec)

mysql> SELECT name FROM str_like WHERE name LIKE 'luoyongmei';
+------------+
| name       |
+------------+
| luoyongmei |
+------------+
1 row in set (0.00 sec)

匹配以某字符串开头：

mysql> SELECT * FROM str_like WHERE name LIKE 'li%';
+----------+
| name     |
+----------+
| litaixun |
+----------+
1 row in set (0.00 sec)

匹配以某字符串结尾：

mysql> SELECT * FROM str_like WHERE name LIKE '%li';
Empty set (0.00 sec)

匹配包含某字符串：

mysql> SELECT * FROM str_like WHERE name LIKE '%ai%';
+----------+
| name     |
+----------+
| litaixun |
+----------+
1 row in set (0.00 sec)

匹配固定长度与格式的字符串：

mysql> SELECT name FROM str_like WHERE name LIKE '_______mei';
+------------+
| name       |
+------------+
| luoyongmei |
+------------+
1 row in set (0.00 sec)

REGEXP 运算符用来匹配字符串，语法格式：expr REGEXP 匹配条件。如果 expr 满足匹配条件，返回 1，否则返回 0。若 expr 或匹配条件中任意一个为 NULL，则结果为 NULL。

REGEXP 运算符在进行匹配时，常用的有下面几种通配符：

（1）'^' 匹配以该字符后面的字符串开头的字符串

mysql> SELECT name FROM str_like WHERE name REGEXP '^li';
+----------+
| name     |
+----------+
| litaixun |
+----------+
1 row in set (0.01 sec)

（2）'$' 匹配以该字符前面的字符串结束的字符串

mysql> SELECT name FROM str_like WHERE name REGEXP 'mei$';
+------------+
| name       |
+------------+
| luoyongmei |
+------------+
1 row in set (0.00 sec)

（3）'.' 匹配任意一个单字符

mysql> SELECT name FROM str_like WHERE name REGEXP '.i';
+------------+
| name       |
+------------+
| litaixun   |
| luoyongmei |
+------------+
2 rows in set (0.00 sec)

（4）”[...]“ 匹配方括号中的任意字符串，"[a-z]" 表示任何字母，"[0-9]" 表示任何数字。

mysql> SELECT name FROM str_like WHERE name REGEXP '[tx]';
+----------+
| name     |
+----------+
| litaixun |
+----------+
1 row in set (0.00 sec)

mysql> SELECT name FROM str_like WHERE name REGEXP '[0-9]';
Empty set (0.00 sec)

（5）'*' 匹配零个或多个在它前面的字符。

3. 逻辑运算符

4. 位运算符

5. 运算符的优先级

不确定优先级时，请使用 () 来强制改变优先级。

5 MySQL 函数

本章目标：

• 掌握各种数学函数的用法
• 掌握各种字符串函数的用法
• 掌握时间和日期函数的用法
• 掌握条件函数的用法
• 掌握系统信息函数的用法
• 掌握加密函数的用法
• 掌握其它特殊函数的用法、

5.1 数学函数

• ABS(X) 返回 X 的绝对值

mysql> SELECT ABS(-5), ABS(4);
+---------+--------+
| ABS(-5) | ABS(4) |
+---------+--------+
|       5 |      4 |
+---------+--------+
1 row in set (0.00 sec)

• PI() 返回七位的圆周率

mysql> SELECT PI();
+----------+
| PI()     |
+----------+
| 3.141593 |
+----------+
1 row in set (0.01 sec)

• SQRT(X) 返回 非负数X 的二次方根

mysql> SELECT SQRT(-5), SQRT(4);
+----------+---------+
| SQRT(-5) | SQRT(4) |
+----------+---------+
|     NULL |       2 |
+----------+---------+
1 row in set (0.01 sec)

可见，若 X 为负数则返回 NULL，并不会报错。

• MOD(X, Y) 返回 X 被 Y 除后的余数，对小数部分的数值也起作用。

mysql> SELECT MOD(29, 8), MOD(54, 10), MOD(45.5, 6);
+------------+-------------+--------------+
| MOD(29, 8) | MOD(54, 10) | MOD(45.5, 6) |
+------------+-------------+--------------+
|          5 |           4 |          3.5 |
+------------+-------------+--------------+
1 row in set (0.00 sec)

• CEIL(X), CEILING(X) 返回 X 的天；FLOOR(X) 返回 X 的地。

mysql> SELECT CEIL(4.5), CEILING(5.4), FLOOR(8.5), CEIL(-0.1), FLOOR(0.1);
+-----------+--------------+------------+------------+------------+
| CEIL(4.5) | CEILING(5.4) | FLOOR(8.5) | CEIL(-0.1) | FLOOR(0.1) |
+-----------+--------------+------------+------------+------------+
|         5 |            6 |          8 |          0 |          0 |
+-----------+--------------+------------+------------+------------+
1 row in set (0.00 sec)

• RAND() 和 RAND(X) 用来获取随机数，返回一个 0 ~ 1 之间的浮点值。

mysql> SELECT RAND(), RAND(), RAND();
+--------------------+--------------------+---------------------+
| RAND()             | RAND()             | RAND()              |
+--------------------+--------------------+---------------------+
| 0.6218551719764761 | 0.5899249358008848 | 0.08405919380864053 |
+--------------------+--------------------+---------------------+
1 row in set (0.00 sec)

mysql> SELECT RAND(6), RAND(6), RAND(11);
+--------------------+--------------------+-------------------+
| RAND(6)            | RAND(6)            | RAND(11)          |
+--------------------+--------------------+-------------------+
| 0.6563190842571847 | 0.6563190842571847 | 0.907234631392392 |
+--------------------+--------------------+-------------------+
1 row in set (0.00 sec)

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

mysql> SELECT ROUND(-4.5), ROUND(-1.45), ROUND(0.54), ROUND(0.45);
+-------------+--------------+-------------+-------------+
| ROUND(-4.5) | ROUND(-1.45) | ROUND(0.54) | ROUND(0.45) |
+-------------+--------------+-------------+-------------+
|          -5 |           -1 |           1 |           0 |
+-------------+--------------+-------------+-------------+
1 row in set (0.00 sec)

• ROUND(X, Y) 返回最接近于 X 的数，Y 用来指定保留位数，以小数点为0开始分隔，若 Y 为负值，则归零 X 小数点左起第 Y 位开始后面所有值。

ysql> SELECT ROUND(1.542, 2), ROUND(1.16, 0), ROUND(-0.16, 1), ROUND(66.66, -2), ROUND(232.38, -1), ROUND(232.38, -2);
+-----------------+----------------+-----------------+------------------+-------------------+-------------------+
| ROUND(1.542, 2) | ROUND(1.16, 0) | ROUND(-0.16, 1) | ROUND(66.66, -2) | ROUND(232.38, -1) | ROUND(232.38, -2) |
+-----------------+----------------+-----------------+------------------+-------------------+-------------------+
|            1.54 |              1 |            -0.2 |              100 |               230 |               200 |
+-----------------+----------------+-----------------+------------------+-------------------+-------------------+
1 row in set (0.00 sec)

• TRUNCATE(X, Y) 返回被舍去至小数点后 Y 位的数字 X，以小数点处为 0 分隔, 若 Y 为负值，则归零 X 小数点左起第 Y 位开始后面所有值。

mysql> SELECT TRUNCATE(1.31, 1), TRUNCATE(1.99, 1), TRUNCATE(19.99, 0), TRUNCATE(19.99, -1);
+-------------------+-------------------+--------------------+---------------------+
| TRUNCATE(1.31, 1) | TRUNCATE(1.99, 1) | TRUNCATE(19.99, 0) | TRUNCATE(19.99, -1) |
+-------------------+-------------------+--------------------+---------------------+
|               1.3 |               1.9 |                 19 |                  10 |
+-------------------+-------------------+--------------------+---------------------+
1 row in set (0.00 sec)

ROUND(X, Y) 在截取值时会四舍五入，而TRUNCATE(X, Y) 不会。

• 符号函数 SIGN(X) 返回参数 X 的符号，值为负、零、正时返回依次是-1，0，1 。

mysql> SELECT SIGN(-21), SIGN(0), SIGN(21);
+-----------+---------+----------+
| SIGN(-21) | SIGN(0) | SIGN(21) |
+-----------+---------+----------+
|        -1 |       0 |        1 |
+-----------+---------+----------+
1 row in set (0.00 sec)

• 幂运算 POW(X, Y)、POWER(X, Y) 返回 X 的 Y 次方的结果值。

mysql> SELECT POW(2, 3), POW(2, -3), POWER(6, 2), POWER(5, -1);
+-----------+------------+-------------+--------------+
| POW(2, 3) | POW(2, -3) | POWER(6, 2) | POWER(5, -1) |
+-----------+------------+-------------+--------------+
|         8 |      0.125 |          36 |          0.2 |
+-----------+------------+-------------+--------------+
1 row in set (0.00 sec)

• EXP(X) 返回 e 的 X 次方。

+------------------+--------------------+
| EXP(2)           | EXP(3)             |
+------------------+--------------------+
| 7.38905609893065 | 20.085536923187668 |
+------------------+--------------------+
1 row in set (0.01 sec)

• 对数运算 LOG(X) 返回 X 的自然对数，X 相对于基数 e 的底。

mysql> SELECT LOG(6), LOG(-6);
+-------------------+---------+
| LOG(6)            | LOG(-6) |
+-------------------+---------+
| 1.791759469228055 |    NULL |
+-------------------+---------+
1 row in set, 1 warning (0.01 sec)

mysql> SHOW WARNINGS;
+---------+------+--------------------------------+
| Level   | Code | Message                        |
+---------+------+--------------------------------+
| Warning | 3020 | Invalid argument for logarithm |
+---------+------+--------------------------------+
1 row in set (0.00 sec)

• LOG10(X) 返回 X 的基数为 10 的对数。

mysql> SELECT LOG10(10), LOG10(100);
+-----------+------------+
| LOG10(10) | LOG10(100) |
+-----------+------------+
|         1 |          2 |
+-----------+------------+
1 row in set (0.00 sec)

剩下几个和数学太紧密的函数就先不记下来了，头会炸的，以后如果真能用上，再回来补吧。

5.2 字符串函数

• CHAR_LENGTH(X) 返回 X 的字符个数，多字节字符也算作一个字符。

mysql> SELECT CHAR_LENGTH('date'), CHAR_LENGTH('0123'), CHAR_LENGTH('汉字');
+---------------------+---------------------+-----------------------+
| CHAR_LENGTH('date') | CHAR_LENGTH('0123') | CHAR_LENGTH('汉字')   |
+---------------------+---------------------+-----------------------+
|                   4 |                   4 |                     2 |
+---------------------+---------------------+-----------------------+
1 row in set (0.00 sec)

• LENGTH(X) 返回 X 的字节个数，当编码为 utf8 时，一个汉字占 3 个字节，一个字母或数字占一个字节。

mysql> SHOW VARIABLES LIKE '%character_set%';
+--------------------------+---------------------------------------------------------+
| Variable_name            | Value                                                   |
+--------------------------+---------------------------------------------------------+
| character_set_client     | utf8mb4                                                 |
| character_set_connection | utf8mb4                                                 |
| character_set_database   | utf8mb4                                                 |
| character_set_filesystem | binary                                                  |
| character_set_results    | utf8mb4                                                 |
| character_set_server     | utf8mb4                                                 |
| character_set_system     | utf8mb3                                                 |
| character_sets_dir       | C:\Program Files\MySQL\MySQL Server 8.0\share\charsets\ |
+--------------------------+---------------------------------------------------------+
8 rows in set, 1 warning (0.00 sec)

mysql> SELECT LENGTH('hanzi'), LENGTH('汉字'), LENGTH('012345');
+-----------------+------------------+------------------+
| LENGTH('hanzi') | LENGTH('汉字')   | LENGTH('012345') |
+-----------------+------------------+------------------+
|               5 |                6 |                6 |
+-----------------+------------------+------------------+
1 row in set (0.00 sec)

• CONCAT(S1, S2, ..., Sn) 返回 S1 ~ Sn 的连接字符串。

mysql> SELECT CONCAT('1', '2', '3', '4', '5'), CONCAT('MySQL',' ', '8.0'), CONCAT(NULL, 'litaixun');
+---------------------------------+----------------------------+--------------------------+
| CONCAT('1', '2', '3', '4', '5') | CONCAT('MySQL',' ', '8.0') | CONCAT(NULL, 'litaixun') |
+---------------------------------+----------------------------+--------------------------+
| 12345                           | MySQL 8.0                  | NULL                     |
+---------------------------------+----------------------------+--------------------------+
1 row in set (0.00 sec)

• CONCAT_WS(X, S1, S2, ..., Sn) 返回字符 X 作为连接符连接的 S1 ~ Sn 的字符串。

mysql> SElECT CONCAT_WS('', 'li', 'tai', 'xun'), CONCAT_WS(' ', CONCAT_WS('-', '2022', '05', '15'), CONCAT_WS(':', '21', '24', '26')), CONCAT_WS('-', 'MySQL', NULL, '8.0');
+-----------------------------------+--------------------------------------------------------------------------------------+--------------------------------------+
| CONCAT_WS('', 'li', 'tai', 'xun') | CONCAT_WS(' ', CONCAT_WS('-', '2022', '05', '15'), CONCAT_WS(':', '21', '24', '26')) | CONCAT_WS('-', 'MySQL', NULL, '8.0') |
+-----------------------------------+--------------------------------------------------------------------------------------+--------------------------------------+
| litaixun                          | 2022-05-15 21:24:26                                                                  | MySQL-8.0                            |
+-----------------------------------+--------------------------------------------------------------------------------------+--------------------------------------+
1 row in set (0.00 sec)

• INSERT(S1, X, LEN, S2) 返回 S1 从 X 位置开始，长度为 LEN 的字符串被 S2 替换后的字符串。

mysql> SELECT INSERT('litaixun', 3, 6, 'yongmei') AS s1, INSERT('litaixun', 3, 99, 'yongmei') AS s2, INSERT('litaixun', -1, 6, 'yongmei') AS s3, INSERT('litaixun', 3, 6, NULL) AS s4, INSERT('MySQL 5.7', 7, 9, '8.0') AS s5;
+-----------+-----------+----------+------+-----------+
| s1        | s2        | s3       | s4   | s5        |
+-----------+-----------+----------+------+-----------+
| liyongmei | liyongmei | litaixun | NULL | MySQL 8.0 |
+-----------+-----------+----------+------+-----------+
1 row in set (0.00 sec)

• LOWER(X) 或 LCASE(X) 返回字符串 X 的所有字母转换为小写后的字符串。

mysql> SELECT LOWER('LiTaiXun'), LCASE('LuoYongMei');
+-------------------+---------------------+
| LOWER('LiTaiXun') | LCASE('LuoYongMei') |
+-------------------+---------------------+
| litaixun          | luoyongmei          |
+-------------------+---------------------+
1 row in set (0.00 sec)

• UPPER(X) 或 UCASE(X) 返回字符串 X 的所有字母转换为大写后的字符串。

mysql> SELECT UPPER('litaixun'), UCASE('luoyongmei');
+-------------------+---------------------+
| UPPER('litaixun') | UCASE('luoyongmei') |
+-------------------+---------------------+
| LITAIXUN          | LUOYONGMEI          |
+-------------------+---------------------+
1 row in set (0.01 sec)

• LEFT(S, N) 返回 S 前 N 个字符。

mysql> SELECT LEFT('litaixun', 5);
+---------------------+
| LEFT('litaixun', 5) |
+---------------------+
| litai               |
+---------------------+
1 row in set (0.00 sec)

• RIGHT(S, N) 返回 S 后 N 个字符。

mysql> SELECT RIGHT('luoyongmei', 3);
+------------------------+
| RIGHT('luoyongmei', 3) |
+------------------------+
| mei                    |
+------------------------+
1 row in set (0.00 sec)

• LPAD(S1, LEN, S2) 返回用 S2 把 S1 填充到 LEN 长度的字符串。

mysql> SELECT LPAD('you are', 10, '**'), LPAD('you are', 3, '**');
+---------------------------+--------------------------+
| LPAD('you are', 10, '**') | LPAD('you are', 3, '**') |
+---------------------------+--------------------------+
| ***you are                | you                      |
+---------------------------+--------------------------+
1 row in set (0.01 sec)

• RPAD(S1, LEN, S2) 返回用 S2 把 S1 填充到 LEN 长度的字符串。

mysql> SELECT RPAD('you are ', 10, '**'), LPAD('you are', 3, '**');
+----------------------------+--------------------------+
| RPAD('you are ', 10, '**') | LPAD('you are', 3, '**') |
+----------------------------+--------------------------+
| you are **                 | you                      |
+----------------------------+--------------------------+
1 row in set (0.00 sec)

我靠，真的是什么函数都有，现实不会用到这些奇奇怪怪的函数吧。。。

• LTRIM(S) 返回删除 X 前导空白的字符串。
• RTRIM(S) 返回删除 X 后导空白的字符串。
• TRIM(S) 返回删除 X 前后空白的字符串。

mysql> SELECT LTRIM('    ltx'), TRIM('   love   '), RTRIM('lym        ');
+------------------+--------------------+----------------------+
| LTRIM('    ltx') | TRIM('   love   ') | RTRIM('lym        ') |
+------------------+--------------------+----------------------+
| ltx              | love               | lym                  |
+------------------+--------------------+----------------------+
1 row in set (0.01 sec)

• TRIM(S1 FROM S) 返回从 S 中两边删除字符串 S1 后的字符串。

mysql> SELECT TRIM('ov' FROM 'ovovovovovovloveovovovov');
+--------------------------------------------+
| TRIM('ov' FROM 'ovovovovovovloveovovovov') |
+--------------------------------------------+
| love                                       |
+--------------------------------------------+
1 row in set (0.00 sec)

• REPEAT(S, N) 返回 N 个 S 连接的字符串。

mysql> SELECT REPEAT(' ILOVEU-lym ', 11);
+--------------------------------------------------------------------------------------------------------------------------------------+
| REPEAT(' ILOVEU-lym ', 11)                                                                                                           |
+--------------------------------------------------------------------------------------------------------------------------------------+
|  ILOVEU-lym  ILOVEU-lym  ILOVEU-lym  ILOVEU-lym  ILOVEU-lym  ILOVEU-lym  ILOVEU-lym  ILOVEU-lym  ILOVEU-lym  ILOVEU-lym  ILOVEU-lym  |
+--------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT REPEAT('6', 6) AS 卧槽;
+--------+
| 卧槽   |
+--------+
| 666666 |
+--------+
1 row in set (0.00 sec)

• SPACE(X) 返回 X 个空格组成的字符串。

mysql> SELECT CONCAT('(', SPACE(6), ')');
+----------------------------+
| CONCAT('(', SPACE(6), ')') |
+----------------------------+
| (      )                   |
+----------------------------+
1 row in set (0.00 sec)

• REPLACE(S, S1, S2) 返回用 S2 替换 S 中所有 S1 后的字符串。

mysql> SELECT REPLACE('www.***.com', '***', 'MySQL');
+----------------------------------------+
| REPLACE('www.***.com', '***', 'MySQL') |
+----------------------------------------+
| www.MySQL.com                          |
+----------------------------------------+
1 row in set (0.00 sec)

• STRCMP(S1, S2) 用于比较 S1 和 S2 的大小，相等返回 0， S1 小于 S2 返回 -1， S1 大于 S2 返回 1。

mysql> SELECT STRCMP('ltx', 'lym'), STRCMP('ltx', 'btx'), STRCMP('ltx', 'ltx');
+----------------------+----------------------+----------------------+
| STRCMP('ltx', 'lym') | STRCMP('ltx', 'btx') | STRCMP('ltx', 'ltx') |
+----------------------+----------------------+----------------------+
|                   -1 |                    1 |                    0 |
+----------------------+----------------------+----------------------+
1 row in set (0.00 sec)

• SUBSTRING(S, N, LEN) 和 MID(S, N, LEN) 返回从 S 中 N 位置截取的长度 LEN 的字符串。

mysql> SELECT SUBSTRING('litaixun', 0, 5), SUBSTRING('litaixun', -3, 3), SUBSTRING('litaixun', 3, 10), MID('luoyongmei', -3, 3), MID('luoyongmei', 4, 11);
+-----------------------------+------------------------------+------------------------------+--------------------------+--------------------------+
| SUBSTRING('litaixun', 0, 5) | SUBSTRING('litaixun', -3, 3) | SUBSTRING('litaixun', 3, 10) | MID('luoyongmei', -3, 3) | MID('luoyongmei', 4, 11) |
+-----------------------------+------------------------------+------------------------------+--------------------------+--------------------------+
|                             | xun                          | taixun                       | mei                      | yongmei                  |
+-----------------------------+------------------------------+------------------------------+--------------------------+--------------------------+
1 row in set (0.00 sec)

• LOCATE(STR1, STR) 、 POSITION(STR1 IN STR) 和 INSTR(STR, STR1) 返回子串在原串中的起始位置。

mysql> SELECT LOCATE('xun', 'litaixun' ), POSITION('mei' IN 'luoyongmei'), INSTR('MySQL', 'sql');
+----------------------------+---------------------------------+-----------------------+
| LOCATE('xun', 'litaixun' ) | POSITION('mei' IN 'luoyongmei') | INSTR('MySQL', 'sql') |
+----------------------------+---------------------------------+-----------------------+
|                          6 |                               8 |                     3 |
+----------------------------+---------------------------------+-----------------------+
1 row in set (0.00 sec)

• REVERSE(S) 返回 S 反转后的字符串。

mysql> SELECT REVERSE('abc'), REVERSE('ABN');
+----------------+----------------+
| REVERSE('abc') | REVERSE('ABN') |
+----------------+----------------+
| cba            | NBA            |
+----------------+----------------+
1 row in set (0.01 sec)

• ELT(N, S1, S2, ..., SN) 返回 N 指定的字符串，若 N 小于 1 或大于参数列表，返回 NULL 。

mysql> SELECT ELT(1, 'first', 'second', 'third'), ELT(6, 'first', 'second', 'third');
+------------------------------------+------------------------------------+
| ELT(1, 'first', 'second', 'third') | ELT(6, 'first', 'second', 'third') |
+------------------------------------+------------------------------------+
| first                              | NULL                               |
+------------------------------------+------------------------------------+
1 row in set (0.00 sec)

• FIELD(S, S1, S2, ..., SN) 返回字符串 S 在参数列表中第一次出现的位置，如果 S 为 NULL 则返回 0 ，若没有出现也返回 0 。

mysql> SELECT FIELD('xun', 'hi', 'my', 'name', 'is', 'Xun'), FIELD('xun', 'hi', 'my', 'name', 'is', 'mei');
+-----------------------------------------------+-----------------------------------------------+
| FIELD('xun', 'hi', 'my', 'name', 'is', 'Xun') | FIELD('xun', 'hi', 'my', 'name', 'is', 'mei') |
+-----------------------------------------------+-----------------------------------------------+
|                                             5 |                                             0 |
+-----------------------------------------------+-----------------------------------------------+
1 row in set (0.01 sec)

• FIND_IN_SET(S1, S2) 返回字符串 S1 在 S2 中出现的第一个位置，S2 是一个由逗号分隔的字符串列表，若 S1 中包含逗号，这个函数将无法正常运行。
• MAKE_SET(X, S1, S2, ..., SN) 返回按 X 的二进制从 S1 ~ SN 中提取的字符串。

我简直裂，越记越离谱，MySQL设计的就像上述两个函数这样的奇奇怪怪的函数真的会用上吗？

1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111.疯完了。

5.3 日期和时间函数

• CURDATE() 和 CURRENT_DATE() 函数均返回当前日期。

ysql> SELECT CURDATE(), CURRENT_DATE(), CURDATE()+0;
+------------+----------------+-------------+
| CURDATE()  | CURRENT_DATE() | CURDATE()+0 |
+------------+----------------+-------------+
| 2022-05-17 | 2022-05-17     |    20220517 |
+------------+----------------+-------------+
1 row in set (0.01 sec)

• CURRENT_TIMESTAMP()、LOCALTIME()、NOW()、SYSDATE() 返回当前日期和时间值。

mysql> SELECT CURRENT_TIMESTAMP(), LOCALTIME(), NOW(), SYSDATE()+0;
+---------------------+---------------------+---------------------+----------------+
| CURRENT_TIMESTAMP() | LOCALTIME()         | NOW()               | SYSDATE()+0    |
+---------------------+---------------------+---------------------+----------------+
| 2022-05-17 08:02:19 | 2022-05-17 08:02:19 | 2022-05-17 08:02:19 | 20220517080219 |
+---------------------+---------------------+---------------------+----------------+
1 row in set (0.00 sec)

• UNIX_TIMESTAMP(date) 返回（1970-01-01 00:00:00 GTM 之后的秒数）的 UNIX 时间戳。

mysql> SELECT UNIX_TIMESTAMP(NOW()), NOW();
+-----------------------+---------------------+
| UNIX_TIMESTAMP(NOW()) | NOW()               |
+-----------------------+---------------------+
|            1652875009 | 2022-05-18 19:56:49 |
+-----------------------+---------------------+
1 row in set (0.00 sec)

• FROM_UNIXTIME(date) 返回 UNIX 时间戳对应的日期时间。

mysql> SELECT FROM_UNIXTIME('1652875009');
+-----------------------------+
| FROM_UNIXTIME('1652875009') |
+-----------------------------+
| 2022-05-18 19:56:49.000000  |
+-----------------------------+
1 row in set (0.00 sec)

• UTC_DATE() 返回当前的 UTC 日期值。

mysql> SELECT UTC_DATE(), UTC_DATE()+0;
+------------+--------------+
| UTC_DATE() | UTC_DATE()+0 |
+------------+--------------+
| 2022-05-18 |     20220518 |
+------------+--------------+
1 row in set (0.00 sec)

• UTC_TIME() 返回当前的时间值。

mysql> SELECT UTC_TIME(), UTC_TIME()+0;
+------------+--------------+
| UTC_TIME() | UTC_TIME()+0 |
+------------+--------------+
| 12:05:37   |       120537 |
+------------+--------------+
1 row in set (0.00 sec)

• MONTH(date) 返回当前日期的月份数。

mysql> SELECT MONTH(CURDATE()),MONTH(CURRENT_DATE()), MONTH(CURRENT_TIMESTAMP()), MONTH(LOCALTIME()), MONTH(NOW()), MONTH(SYSDATE())+0;
+------------------+-----------------------+----------------------------+--------------------+--------------+--------------------+
| MONTH(CURDATE()) | MONTH(CURRENT_DATE()) | MONTH(CURRENT_TIMESTAMP()) | MONTH(LOCALTIME()) | MONTH(NOW()) | MONTH(SYSDATE())+0 |
+------------------+-----------------------+----------------------------+--------------------+--------------+--------------------+
|                5 |                     5 |                          5 |                  5 |            5 |                  5 |
+------------------+-----------------------+----------------------------+--------------------+--------------+--------------------+
1 row in set (0.00 sec)

• MONTHNAME(date) 返回当前日期的月份英文全称。

ysql> SELECT MONTHNAME(CURDATE()), MONTHNAME(CURRENT_DATE()), MONTHNAME(FROM_UNIXTIME(UNIX_TIMESTAMP()));
+----------------------+---------------------------+--------------------------------------------+
| MONTHNAME(CURDATE()) | MONTHNAME(CURRENT_DATE()) | MONTHNAME(FROM_UNIXTIME(UNIX_TIMESTAMP())) |
+----------------------+---------------------------+--------------------------------------------+
| May                  | May                       | May                                        |
+----------------------+---------------------------+--------------------------------------------+
1 row in set (0.01 sec)

• DAYNAME(d) 返回 d 对应的工作日的英文名。

 
mysql> SELECT DAYNAME(UTC_DATE());
+---------------------+
| DAYNAME(UTC_DATE()) |
+---------------------+
| Thursday            |
+---------------------+
1 row in set (0.01 sec)

• DAYOFWEEK(d) 返回 d 对应的一周中的索引。

mysql> SELECT DAYOFWEEK(CURDATE());
+----------------------+
| DAYOFWEEK(CURDATE()) |
+----------------------+
|                    5 |
+----------------------+
1 row in set (0.00 sec)

• WEEKDAY(d) 返回 d 对应的工作日的索引。

mysql> SELECT WEEKDAY(CURRENT_DATE());
+-------------------------+
| WEEKDAY(CURRENT_DATE()) |
+-------------------------+
|                       3 |
+-------------------------+
1 row in set (0.00 sec)
 
0 : 周一
1 : 周二
  .
  .
  .
  .
6 : 周日

• WEEKD(d) 和 WEEKOFYEAR(d) 计算日期 d 是一年中的第几周；第二个参数指定哪天是每周的第一天。
• DAYOFYEAR(d) 返回日期 d 在一年中的位置。
• DAYOFMONTH(d) 返回日期 d 在一个月中的位置。
• YEAR(d) 返回日期 d 对应的年份。
• QUARTER(d) 返回日期 d 对应的季度。
• MINUTE(t) 返回时间 t 对应的分钟。
• SECOND(t) 返回时间 t 对应的秒数。
• EXTRACT(type FROM date) 提取日期中的某一部分。

mysql> SELECT EXTRACT(YEAR FROM LOCALTIME()), EXTRACT(MONTH FROM CURRENT_TIMESTAMP()), EXTRACT(HOUR_SECOND FROM SYSDATE());
+--------------------------------+-----------------------------------------+-------------------------------------+
| EXTRACT(YEAR FROM LOCALTIME()) | EXTRACT(MONTH FROM CURRENT_TIMESTAMP()) | EXTRACT(HOUR_SECOND FROM SYSDATE()) |
+--------------------------------+-----------------------------------------+-------------------------------------+
|                           2022 |                                       5 |                              213911 |
+--------------------------------+-----------------------------------------+-------------------------------------+
1 row in set (0.01 sec)

注意：日不能和年月连用，否则会报错。

• TIME_TO_SEC(t) 转换时间 t 为秒。

先跳过最后一点。。。熬不住了！！

5.4 条件判断函数

• IF(expr, v1, v2): 如果 expr 为 TRUE(expr <> 0 AND expr <> NULL), 返回 v1 , 否则返回 v2 。
• IFNULL(v1, v2): 如果 v1 不为 NULL，返回 v1，否则返回 v2 。
• CASE expr WHEN v(1) THEN r(1) [WHEN v(2) THEN r(2) ... WHEN v(n) THEN r(n) ELSE r(n+1)] 执行 expr 选中分支。

mysql> SELECT IF(1>2, 'yes', 'no');
+----------------------+
| IF(1>2, 'yes', 'no') |
+----------------------+
| no                   |
+----------------------+
1 row in set (0.00 sec)

mysql> SELECT IFNULL(NULL, NULL), IFNULL(NULL, 'mei');
+--------------------+---------------------+
| IFNULL(NULL, NULL) | IFNULL(NULL, 'mei') |
+--------------------+---------------------+
|               NULL | mei                 |
+--------------------+---------------------+
1 row in set (0.01 sec)

mysql> SELECT CASE 54
    -> WHEN 1 THEN 1
    -> WHEN 2 THEN 2
    -> ELSE "I'm going to sleep now !"
    -> END
    -> AS tmp;
+--------------------------+
| tmp                      |
+--------------------------+
| I'm going to sleep now ! |
+--------------------------+
1 row in set (0.00 sec)

5.5 系统信息函数

• VERSION() 返回 MySQL 版本。

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.0.29    |
+-----------+
1 row in set (0.00 sec)

• CONNECTION_ID() 返回 MySQL 服务器当前连接的次数，每个连接都有各自唯一的 ID 。

mysql> SELECT CONNECTION_ID();
+-----------------+
| CONNECTION_ID() |
+-----------------+
|               8 |
+-----------------+
1 row in set (0.01 sec)

• SHOW PROCESSLIST; SHOW FULL PROCESSLIST 命令 显示当前运行的线程，不仅可以查看当前的所有连接数，还可以查看当前的连接状态，帮助识别出有问题的查询语句等。如果是 root 账号，能看到所有用户的当前连接，如果是普通账号，只能看到自己的连接。

mysql> SHOW FULL PROCESSLIST;
+----+-----------------+-----------------+--------+---------+-------+------------------------+-----------------------+
| Id | User            | Host            | db     | Command | Time  | State                  | Info                  |
+----+-----------------+-----------------+--------+---------+-------+------------------------+-----------------------+
|  5 | event_scheduler | localhost       | NULL   | Daemon  | 79719 | Waiting on empty queue | NULL                  |
|  8 | root            | localhost:51959 | totest | Query   |     0 | init                   | SHOW FULL PROCESSLIST |
+----+-----------------+-----------------+--------+---------+-------+------------------------+-----------------------+
2 rows in set (0.01 sec)

• DATABASES() 和 SCHEMA() 返回当前数据库名。

mysql> SELECT DATABASE(), SCHEMA();
+------------+----------+
| DATABASE() | SCHEMA() |
+------------+----------+
| totest     | totest   |
+------------+----------+
1 row in set (0.00 sec)

• USER()、CURRENT_USER()、SYSTEM_USER()、SESSION_USER() 返回当前被 MySQL 服务器验证的用户名和主机名的组合。

mysql> SELECT USER(),CURRENT_USER(),SYSTEM_USER(),SESSION_USER();
+----------------+----------------+----------------+----------------+
| USER()         | CURRENT_USER() | SYSTEM_USER()  | SESSION_USER() |
+----------------+----------------+----------------+----------------+
| root@localhost | root@localhost | root@localhost | root@localhost |
+----------------+----------------+----------------+----------------+
1 row in set (0.00 sec)

• CHARSET(set) 返回字符串 str 自变量的字符集。

mysql> SELECT CHARSET('abc'), CHARSET(CONVERT('abc' USING latin1)), CHARSET(VERSION());
+----------------+--------------------------------------+--------------------+
| CHARSET('abc') | CHARSET(CONVERT('abc' USING latin1)) | CHARSET(VERSION()) |
+----------------+--------------------------------------+--------------------+
| utf8mb4        | latin1                               | utf8mb3            |
+----------------+--------------------------------------+--------------------+
1 row in set (0.01 sec)

• COLLATION(str) 返回字符串 str 的排列方式。

mysql> SELECT COLLATION('abc'), COLLATION(CONVERT('abc' USING latin1)), COLLATION(VERSION());
+--------------------+----------------------------------------+----------------------+
| COLLATION('abc')   | COLLATION(CONVERT('abc' USING latin1)) | COLLATION(VERSION()) |
+--------------------+----------------------------------------+----------------------+
| utf8mb4_0900_ai_ci | latin1_swedish_ci                      | utf8_general_ci      |
+--------------------+----------------------------------------+----------------------+
1 row in set (0.00 sec)

• LAST_INSERT_ID() 返回当前连接最后生成的 AUTO_INCREMENT 值，与表无关。

5.6 加密函数

• MD5(str) 返回 str MD5 128 比特校验和，该值以 32 位十六进制数字二进制字符串形式返回。
• SHA(str) 返回 SHA 函数加密后的字符串。
• SHA2(str, hash_length) 使用 hash_length 做长度加密 str 。支持的值有 0、224、256、384、512 其中 0 等于 256 。

5.7 其它函数

• FORMAT(x, n) 将数字 x 格式化， 并以四舍五入的方式保留小数点后 n 位，结果以字符串形式返回。若 n 位零，返回结果不包含小数部分。
• CONV(N, from_base, _to_base) 进行不同进制之间的转换，如果有任意一个参数为 NULL ，则返回值为 NULL 。自变量 N 被理解为整数，但可以为其赋值为字符串。最小基数是 2，最大是 36 。 
• INET_ATON(expr) 返回点分十进制形式的 ip 地址的整数形式。
• INET_NTOA(expr) 返回将数字网络地址的点分十进制 ip 地址。
• GET_LOCK(str, timeout) 在 timeout 指定的超时前获取 str 为命名的锁。
• RELEASE_LOCK(str) 解锁 GET_LOCK(str, timeout) 获取的锁。
• IS_FREE_LOCK(str) 检查 str 锁是否可用。
• IS_USED_LOCK(str) 检查 str 锁是否在用。
• BENCHMARK(count, expr) 函数重复 count 次执行表达式 expr 。它用于计算 MySQL 处理表达式 expr 的速度。另一个作用是，在 MySQL 客户端内部报告语句执行时间。

麻了，第一部分算是学完记完了，内容可很是又细又多啊，变态，感觉这些东西了解就行，用得也不会很多，用到的时候查缺补漏就行，关键还是要学其它的原理性的东西，这些函数什么的，真的整吐了，等心情好的时候再回来补那些有的没的了。

下一讲见，希望那时候有个好心情。