MySQL系列：数据类型、运算符及函数（5）
1. 数据类型
MySQL支持多种数据类型，主要有数值类型、日期/时间类型和字符串类型。
（1）数值类型：包括整数类型：TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，
浮点小数类型：FLOAT和DOUBLE，
定点小数类型：DECIMAL。
（2）日期/时间类型：包括 YEAR、TIME、DATE、DATETIME和TIMESTAMP。
（3）字符串类型：包括 CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET等。
1.1 整数类型
整数类型的字段可以添加AUTO_INCREMENT自增约束条件。
整数类型：
mysql> CREATE TABLE product -> ( -> id INT AUTO_INCREMENT PRIMARY KEY, -> product_name VARCHAR (100) NOT NULL -> );
mysql> DESC product; +--------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | product_name | varchar(100) | NO | | NULL | | +--------------+--------------+------+-----+---------+----------------+
整数类型无符号：
mysql> CREATE TABLE product -> ( -> id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, -> product_name VARCHAR (100) NOT NULL -> );
mysql> DESC product; +--------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | product_name | varchar(100) | NO | | NULL | | +--------------+------------------+------+-----+---------+----------------+
1.2 浮点数类型和定点数类型
MySQL中使用浮点数和定点数来表示小数。
浮点类型包括：单精度浮点类型（FLOAT）和双精度浮点类型（DOUBLE）。
定点类型只有：DECIMAL。
浮点类型和定点类型都可以使用（M, N）来表示，其中M为精度，表示总位数；N为标度，表示小数的位数。
FLOAT和DOUBLE在不指定精度时，默认会按照实际的精度，DECIMAL不指定精度默认为（10，0）。
1.3 日期与时间类型
MySQL中表示日期的数据类型：DATETIME、DATE、TIMESTAMP、TIME和YEAR。
CURRENT_DATE()：返回当前日期
NOW()：返回当前日期 + 时间
mysql> SELECT CURRENT_DATE(), NOW(); +----------------+---------------------+ | CURRENT_DATE() | NOW() | +----------------+---------------------+ | 2017-11-01 | 2017-11-01 19:40:58 | +----------------+---------------------+
1.4 字符串类型
（1）CHAR和VARCHAR类型
CHAR(m)：固定长度字符串，m表示列长度，取值范围 0~255（28-1）。
VARCHAR(m)：长度可变的字符串，m表示列长度，取值范围 0~65535（216-1）。
（2）ENUM类型
ENUM是一个字符串对象，其值为表创建时在列规定中枚举的一列值。
语法格式：
column_name ENUM('value1', 'value2', ...)
ENUM类型字段只能在指定的枚举列表中取值，一次只能取一个。
ENUM值在内部使用整数表示，每个枚举值都有一个索引值，枚举列表索引值从1开始。MySQL存储索引编号。
枚举最多可以有65535（216-1）个元素。
mysql> CREATE TABLE product -> ( -> id INT AUTO_INCREMENT PRIMARY KEY, -> product_name VARCHAR(10), -> size ENUM('x-small', 'small', 'medium', 'large', 'x-large') NOT NULL DEFAULT 'small' -> );
mysql> DESC product;
+--------------+----------------------------------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+----------------------------------------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| product_name | varchar(10) | YES | | NULL | |
| size | enum('x-small','small','medium','large','x-large') | NO | | small | |
+--------------+----------------------------------------------------+------+-----+---------+----------------+
3 rows in set (0.06 sec)
ENUM类型的取值范围，示例：size
| 值 | 索引 |
|---|---|
| NULL | NULL |
| '' | 0 |
| x-small | 1 |
| small | 2 |
| medium | 3 |
| large | 4 |
| x-large | 5 |
ENUM值按照索引顺序排列，并且空字符串排在非空字符串前，NULL值排在其他所有枚举值前。
ENUM列总有一个默认值。如果ENUM列定义为NULL，则NULL则为该列的一个有效值，并且默认为NULL。如果ENUM列定义为NOT NULL，则默认值为允许的值列表的第1个元素。
mysql> INSERT INTO product(product_name, size) VALUES ('最小码', 1);
mysql> INSERT INTO product(product_name, size) VALUES ('小码', 'small');
mysql> SELECT * FROM product; +----+--------------+---------+ | id | product_name | size | +----+--------------+---------+ | 1 | 最小码 | x-small | | 2 | 小码 | small | +----+--------------+---------+ 2 rows in set
2. 运算符
MySQL运算符是执行特定算术或逻辑操作的符号，主要分四大类：算术运算符、比较运算符、逻辑运算符及位操作运算符。
2.1 算术运算符
MySQL中的算术运算符：
| 运算符 | 说明 |
|---|---|
| + | 加法运算 |
| - | 减法运算 |
| * | 乘法运算 |
| / | 除法运算，返回商 |
| % | 求余运算，返回余数 |
2.2 比较运算符
比较运算符的结果总是1、0或者NULL，比较运算符常在SELECT查询条件子句中使用，用来查询满足指定条件的记录。
3. 函数
3.1 数学函数
3.2 字符串函数
3.3 日期和时间函数
（1）获取当前日期
mysql> SELECT CURDATE(), CURRENT_DATE(); +------------+----------------+ | CURDATE() | CURRENT_DATE() | +------------+----------------+ | 2018-03-27 | 2018-03-27 | +------------+----------------+
（2）获取当前时间
mysql> SELECT CURTIME(), CURRENT_TIME(); +-----------+----------------+ | CURTIME() | CURRENT_TIME() | +-----------+----------------+ | 18:52:52 | 18:52:52 | +-----------+----------------+
（3）获取当前日期时间
mysql> SELECT SYSDATE(), NOW(), CURRENT_TIMESTAMP(), LOCALTIME();
+---------------------+---------------------+---------------------+---------------------+ | SYSDATE() | NOW() | CURRENT_TIMESTAMP() | LOCALTIME() | +---------------------+---------------------+---------------------+---------------------+ | 2018-03-27 19:16:51 | 2018-03-27 19:16:51 | 2018-03-27 19:16:51 | 2018-03-27 19:16:51 | +---------------------+---------------------+---------------------+---------------------+
（4）UNIX时间戳
UNIX_TIMESTAMP(date)：返回一个Unix时间戳（1970-01-01 00:00:00 GMT之后的秒数）作为无符号整数。
mysql> SELECT UNIX_TIMESTAMP(), UNIX_TIMESTAMP(NOW()), UNIX_TIMESTAMP('2018-03-27'); +------------------+-----------------------+------------------------------+ | UNIX_TIMESTAMP() | UNIX_TIMESTAMP(NOW()) | UNIX_TIMESTAMP('2018-03-27') | +------------------+-----------------------+------------------------------+ | 1522149805 | 1522149805 | 1522080000 | +------------------+-----------------------+------------------------------+
FROM_UNIXTIME(date)：把UNIX时间戳转换为普通格式的时间，与UNIX_TIMESTAMP(date)互为反函数。
mysql> SELECT FROM_UNIXTIME(1522149805); +---------------------------+ | FROM_UNIXTIME(1522149805) | +---------------------------+ | 2018-03-27 19:23:25 | +---------------------------+
（5）获取月份：MONTH(date)
mysql> SELECT MONTH(NOW()), NOW(); +--------------+---------------------+ | MONTH(NOW()) | NOW() | +--------------+---------------------+ | 3 | 2018-03-27 19:31:15 | +--------------+---------------------+
mysql> SELECT MONTHNAME(NOW()), NOW(); +------------------+---------------------+ | MONTHNAME(NOW()) | NOW() | +------------------+---------------------+ | March | 2018-03-27 19:32:46 | +------------------+---------------------+
（6）获取星期
DAYNAME(date)：返回对应的星期英文名称
mysql> SELECT DAYNAME(NOW()), NOW(); +----------------+---------------------+ | DAYNAME(NOW()) | NOW() | +----------------+---------------------+ | Tuesday | 2018-03-27 19:36:17 | +----------------+---------------------+
DAYOFWEEK(date)：返回date对应的一周中的索引，1表示周日，...，7表示周六
mysql> SELECT DAYOFWEEK(NOW()), NOW(); +------------------+---------------------+ | DAYOFWEEK(NOW()) | NOW() | +------------------+---------------------+ | 3 | 2018-03-27 19:37:32 | +------------------+---------------------+
WEEKDAY(date)：返回date对应的工作日索引，0表示周一，...，6表示周日。
mysql> SELECT WEEKDAY(NOW()), NOW(); +----------------+---------------------+ | WEEKDAY(NOW()) | NOW() | +----------------+---------------------+ | 1 | 2018-03-27 19:40:58 | +----------------+---------------------+
（7）日期计算
DATE_ADD(date,INTERVAL expr unit)
mysql> SELECT DATE_ADD(NOW(),INTERVAL 1 DAY), NOW(); +--------------------------------+---------------------+ | DATE_ADD(NOW(),INTERVAL 1 DAY) | NOW() | +--------------------------------+---------------------+ | 2018-03-28 19:47:46 | 2018-03-27 19:47:46 | +--------------------------------+---------------------+
ADDDATE(expr,days)
mysql> SELECT ADDDATE(NOW(),1), NOW(); +---------------------+---------------------+ | ADDDATE(NOW(),1) | NOW() | +---------------------+---------------------+ | 2018-03-28 19:49:38 | 2018-03-27 19:49:38 | +---------------------+---------------------+
DATE_SUB(date,INTERVAL expr unit)
mysql> SELECT DATE_SUB(NOW(),INTERVAL 1 DAY),NOW(); +--------------------------------+---------------------+ | DATE_SUB(NOW(),INTERVAL 1 DAY) | NOW() | +--------------------------------+---------------------+ | 2018-03-26 19:51:01 | 2018-03-27 19:51:01 | +--------------------------------+---------------------+
SUBDATE(expr,days)
日期与时间格式化：DATE_FORMAT(date,format)
mysql> SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%m:%s'); +----------------------------------------+ | DATE_FORMAT(NOW(),'%Y-%m-%d %H:%m:%s') | +----------------------------------------+ | 2018-03-27 19:03:59 | +----------------------------------------+
3.4 条件判断函数
3.5 系统信息函数
（1）VERSION()：获取MySQL版本号
mysql> SELECT VERSION(); +------------+ | VERSION() | +------------+ | 5.7.20-log | +------------+
（2）CONNECTION_ID()：获取MySQL服务器当前连接的次数，每个连接都有各自唯一的ID
mysql> SELECT CONNECTION_ID(); +-----------------+ | CONNECTION_ID() | +-----------------+ | 12 | +-----------------+
mysql> SHOW PROCESSLIST;
mysql> SHOW FULL PROCESSLIST; +----+------+-----------------+------+---------+------+----------+-----------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------------+------+---------+------+----------+-----------------------+ | 3 | root | localhost:16352 | NULL | Sleep | 1723 | | NULL | | 4 | root | localhost:16424 | test | Sleep | 1679 | | NULL | | 12 | root | localhost:17521 | test | Query | 0 | starting | SHOW FULL PROCESSLIST | | 13 | root | localhost:20898 | test | Sleep | 745 | | NULL | | 14 | root | localhost:20916 | test | Sleep | 154 | | NULL | +----+------+-----------------+------+---------+------+----------+-----------------------+
SHOW PROCESSLIST与SHOW FULL PROCESSLIST区别：
如果是root账号，能看到所有用户的当前连接；如果是普通账号，则只能看到自己占用的连接。
SHOW PROCESSLIST：只显示前100条；
SHOW FULL PROCESSLIST：查看全部记录。
（3）查看当前所使用的数据库
mysql> SELECT DATABASE(), SCHEMA();
（4）获取当前登录用户名
mysql> SELECT USER(), CURRENT_USER(), SYSTEM_USER(); +----------------+----------------+----------------+ | USER() | CURRENT_USER() | SYSTEM_USER() | +----------------+----------------+----------------+ | root@localhost | root@localhost | root@localhost | +----------------+----------------+----------------+
（5）LAST_INSERT_ID()：获取最后一个自动生成的ID值
LAST_INSERT_ID()自动返回最后一个INSERT或UPDATE为AUTO_INCREMENT列设置的第一个发生的值。
一次插入一条记录：返回最后一条插入记录的ID值。
mysql> INSERT INTO product(product_name, size) VALUES ('最小码', 'x-small');
mysql> INSERT INTO product(product_name, size) VALUES ('小码', 'small');
mysql> SELECT LAST_INSERT_ID(); +------------------+ | LAST_INSERT_ID() | +------------------+ | 2 | +------------------+ 1 row in set
一次插入多条记录：当使用一条INSERT语句插入多行时，LAST_INSERT_ID()只返回插入第一行数据时产生的值。
mysql> INSERT INTO product(product_name, size) VALUES ('中码', 'medium'), ('大码', 'large'), ('最大码', 'x-large');
mysql> SELECT LAST_INSERT_ID(); +------------------+ | LAST_INSERT_ID() | +------------------+ | 3 | +------------------+
mysql> SELECT * FROM product; +----+--------------+---------+ | id | product_name | size | +----+--------------+---------+ | 1 | 最小码 | x-small | | 2 | 小码 | small | | 3 | 中码 | medium | | 4 | 大码 | large | | 5 | 最大码 | x-large | +----+--------------+---------+
