MySQL系列：資料型別、運算子及函式（5）

libingql發表於2017-10-30

MySql資料型別函式

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（2⁸-1）。

　　VARCHAR(m)：長度可變的字串，m表示列長度，取值範圍 0~65535（2¹⁶-1）。

　　（2）ENUM型別

　　ENUM是一個字串物件，其值為表建立時在列規定中列舉的一列值。

　　語法格式：

　　column_name ENUM('value1', 'value2', ...)

　　ENUM型別欄位只能在指定的列舉列表中取值，一次只能取一個。

　　ENUM值在內部使用整數表示，每個列舉值都有一個索引值，列舉列表索引值從1開始。MySQL儲存索引編號。

　　列舉最多可以有65535（2¹⁶-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 |
+----+--------------+---------+

PostgreSQL：陣列——型別、運算子和函式
2020-12-01
SQL陣列型別函式
PostgreSQL：數字——型別、運算子和函式
2020-11-30
SQL型別函式
PostgreSQL：時間——型別、運算子和函式
2020-11-30
SQL型別函式
資料型別和運算子
2020-11-12
資料型別
PostgreSQL：網路地址——型別、運算子和函式
2020-12-01
SQL型別函式
資料型別與函式索引-MySQL篇
2022-01-06
資料型別函式索引MySql
Java中的資料型別和運算子
2020-11-16
Java資料型別
JAVA基礎--資料型別和運算子
2020-10-08
Java資料型別
資料型別和運算子測試題
2020-06-10
資料型別
MySQL入門系列：MySQL資料型別
2019-03-07
MySql資料型別
[MySQL光速入門]010 運算子與函式
2019-04-18
MySql函式
Python3基礎——運算子、資料型別
2018-09-20
Python資料型別
python教程1.2：變數+資料型別+運算子
2024-05-03
Python變數資料型別
JavaScript中的變數、資料型別以及運算子
2020-10-26
JavaScript變數資料型別
day02 go的資料型別和運算子
2024-07-02
Go資料型別
php運算子運用之型別運算子該如何使用
2021-09-11
PHP型別
PostgreSQL：字元——型別及函式
2020-11-30
SQL字元型別函式
型別轉換運算子
2024-08-01
型別
Java基礎知識（變數、資料型別、運算子）
2020-12-07
Java變數資料型別
string型別資料的find函式
2019-03-12
型別函式
1：賦值運算子函式
2020-12-02
賦值函式
php型別運算子的使用
2021-09-11
PHP型別
Redis系列-資料型別及使用場景
2024-11-22
Redis資料型別
sql server 資料型別轉換函式
2018-09-05
SQLServer資料型別函式
資料型別與函式索引-PostgreSQL篇
2022-01-10
資料型別函式索引SQL
資料型別與函式索引-Oracle篇
2022-01-09
資料型別函式索引Oracle
Dart函式、類和運算子-處理資訊
2019-09-23
Dart函式
SQL SERVER 日期和時間資料型別及函式 (Transact-SQL)
2024-04-01
SQLServer資料型別函式
實驗一 c語言開發環境使用和資料型別、運算子、表示式
2024-03-17
C語言開發環境資料型別
實驗1 C語言開發環境使用和資料型別，運算子，表示式
2024-03-16
C語言開發環境資料型別
實驗一 C語言開發環境使用和資料型別，運算子，表示式
2024-10-02
C語言開發環境資料型別
實驗1 C語言開發環境使用和資料型別、運算子、表示式
2024-09-27
C語言開發環境資料型別
過載運算子、解構函式
2024-10-20
函式
瘋狂Java講義_Chapter03資料型別和運算子
2020-11-30
JavaAPT資料型別
[Mysql]資料型別
2024-08-06
MySql資料型別
MySQL資料型別
2024-09-10
MySql資料型別
MYSQL 資料型別
2021-10-25
MySQL 資料型別
java練習——任務1.1——資料型別、運算子、控制語句、陣列、表示式的使用
2020-09-26
Java資料型別陣列
Mysql系列第二講詳解mysql資料型別（重點）
2020-09-25
MySql資料型別