---
tags: MySQL, SQL
---
# MySQL-Func
[TOC]
* * *
## 字元函式：
### 連接字串
- CONCAT(A, B, C) COLUMNS or 任意字元
- CONCAT_WS('str', A, B, C) str:分割字元
- SPACE(n) n 空白字元數量
- IFNULL(COLUMN, 'str') COLUMN 如果是NULL 輸出 str
### 插入字串
- INSERT('str', n, m, 's')
將'str' 的 n 字元 ~ n+m 字元 由 's' 字串取代
### 替換字串
- REPLACE('str', 'r', 's')
在'str'字串中的字串 'r' 用 's' 取代 --> 'sts'
沒有 'r' 則不處理
### 反轉字串 也可以放字串 COL
- REVRESE('COLUMN') - > 'NMULOC'
### 重複字串
- REPEAT('STR', 'NUM') NUM 可用數字COL
### 大小寫轉換：
- 大寫UPPER() UCASE()
- 小寫LOWER() LCASE()
### 輸出'字串' 用單引號框住'STR'
- QUOTE(COLUMN)
### 修正(刪除)空白字元
| / | func |
| --- | ------- |
| 右 | RTRUM() |
| 左 | LTRIM() |
| 雙邊 | TRIM() |
### 修正(刪除)其他字元
TRIM('str', COLUMN) ,要修正的字元'str'
strXXXXXXstr -> XXXXXX
### 補位(str) n:字元數
- RPAD(COLUMN, n, 'str') -> COLUMN....
- LPAD(COLUMN, n, 'str') -> ....COLUMN
### 擷取SUBSTRING()
| func | / |
| ------------------------------ | --------------------------------------- |
| RIGHT(COLUMN, n, '.') | n 字元數 "COL"UMN |
| LEFT(COLUMN, n, '.') | n 字元數 COL"UMN" |
| MID(COLUMN, n, m) | CO"LUM"N n=2 m=3 (n:起始字元index m:擷取字元數) |
| SUBSTRING(COLUMN, n, m) | 同MID() |
| SUBSTRING(COLUMN FROM n FOR m) | n ~ m個字元 |
| SUBSTRING(COLUMN, -m) | 倒數m 個字元 |
| LOCATE('STR', COLUMN) | COLUMN 中的STR INDEX 第幾字元 |
| POSITION('STR' IN COLUMN) | COLUMN 中的STR INDEX 第幾字元 |
SUBSTRING_INDEX(COLUNM, 'STR', n) 第n STR 之前的字元
STR: |
COL: A|B|C
n: 1 2
### 字元數
| func | / |
| -------------------------- | ------ |
| LENGTH(COLUMN) COLUMN | 字串字元數 |
| CHAR_LENGTH(COLUMN) COLUMN | 字串字元數 |
| CHARACTER_LENGTH(COLUMN) | 字串字元數 |
| BIT_LENGTH(COLUMN) | 字串佔位元數 |
### 時間資料格式：
| func | / |
| --------- | -------------------- |
| DATE | yyyy-mm-dd |
| TIME | hhh:mm:ss |
| DATETIME | yyyy-mm-dd hhh:mm:ss |
| TIMESTAMP | yyyy-mm-dd hhh:mm:ss |
| YEAR | yyyy |
* * *
### 時間格式擷取函式：
| func | / |
| -------- | ------------ |
| DATE() | 擷取yyyy-mm-dd |
| TIME() | 擷取hhh:mm:ss |
| YEAR() | 擷取yyyy |
| MONTH() | 擷取mm |
| DAY() | 擷取dd |
| HOUR() | 擷取hhh |
| MINUTE() | 擷取mm |
| SECOND() | 擷取ss |
* * *
### 使用EXTRACT()函式擷取：
EXTRACT(interval FROM DATETIME_COLUMN)
interval Format for given values
| func | format |
| ------------------ | :-----------: |
| DAY | dd |
| DAY_HOUR | ‘dd hh' |
| DAY_MICROSECOND | ‘dd.nn’ |
| DAY_MINUTE | ‘dd hh:mm’ |
| DAY_SECOND | ‘dd hh:mm:ss’ |
| HOUR | hh |
| HOUR_MICROSECOND | ‘hh.nn’ |
| HOUR_MINUTE | ‘hh:mm’ |
| HOUR_SECOND | ‘hh:mm:ss’ |
| MICROSECOND | nn |
| MINUTE | mm |
| MINUTE_MICROSECOND | ‘mm.nn’ |
| MINUTE_SECOND | ‘mm:ss’ |
| MONTH | mm |
| QUARTER | qq |
| SECOND | ss |
| SECOND_MICROSECOND | 'ss.nn’ |
| WEEK | ww |
| YEAR | yy |
| YEAR_MONTH | ‘yy-mm' |
* * *
### 日期時間格式化函式：
DATE_FORMAT(COLUMN, '%code') 也可以處理TIME_FORMAT()
TIME_FORMAT(COLUMN, '%code') 其實可以用不到！
| % | func | unit |
| ----- | ---------------------------------------------------------------------------------------------------------------------- | ----------------------- |
| Code | Description | Results |
| %a | Abbreviated weekday name | (Sun...Sat) |
| %b | Abbreviated month name | (Jan...Dec) |
| %c | Month (numeric) | (1...12) |
| %d | Day of the month (numeric) | (00...31) |
| %D | Day of the month with English suffix (1st, 2nd, 3rd, etc.)%e Day of the month | (numeric) (0...31) |
| %f | Microseconds (numeric) | (000000...999999) |
| %h,%I | Hour 12hr | (01...12) |
| %H | Hour 24hr | (00...23) |
| %i | Minutes (numeric) | (00...59) |
| %j | Day of the year | (001...366) |
| %k | Hour | (0...23) |
| %l | Hour | (1...12) |
| %m | Month (numeric) | (01...12) |
| %M | Month name | (January...December) |
| %p | AM or PM | (AM or PM) |
| %r | Time, 12-hour | (hh:mm:ss [AP]M) |
| %s,%S | Seconds | (00...59) |
| %T | Time, 24-hour | (hh:mm:ss) |
| %u | Week, where Monday is the first day of the week | (0...52) |
| %U | Week, where Sunday is the first day of the week | (0...52) |
| %v | Week, where Monday is the first day of the week; used with \`%x’ \| (1...53) | |
| %V | Week, where Sunday is the first day of the week; used with\`%X’ \| (1...53) | |
| %w | Day of the week | (0=Sunday...6=Saturday) |
| %W | Weekday name | (Sunday...Saturday) |
| %x | Year for the week, where Monday is the first day of the week (numeric, four digits);used with \`%v’ \| (yyyy) | |
| %X | Year for the week, where Sunday is the first day of the week (numeric, four digits);used with\`%V’ \| (yyyy) | |
| %y | Year (numeric, two digits) | (yy) |
| %Y | Year (numeric, four digits) | (yyyy) |
| %% | A literal \`%’ | (%) |
* * *
GET_FORMAT(時間格式, 'USA'時間標準) 輸出時間格式(%code)
時間格式：DATE,TIME,DATETIME,TIMESTAMP,YEAR
| 時間標準 | |
| -------- | -------------------------------- |
| EUR | for Europe |
| INTERNAL | for the format in which time |
| ISO | for ISO 9075 standard |
| JIS | for Japanese Industrial Standard |
| USA | for United States |
TIME_TO_SEC(TIME) 時間轉成幾秒(不管日期)
SEC_TO_TIME(SEC(num)) 幾秒轉成時間(只保留時間,最大單位hour)
DATEDIFF(DATE1, DATE2) 日期差距DATE2 - DATE1
TIMEDIFF(TIME1, TIME2) 時間差距TIME2 - TIME1
* * *
### 日期的加減：
DATE_ADD(DATE, INTERVAL '1 DAY') 可以用負數代表DATE_SUB
DATE_SUB(DATE, INTERVAL '1 DAY')
ADDDATE(DATE, NUM) 不需要引數 直接輸入數字 單位DAY
SUBDATE(DATE, NUM)
### 時間的加減：
ADDTIME(TIME, NUM) 不需要引數 直接輸入數字 單位SEC
SUBTIME(TIME, NUM)
### 數值處理函式：
| func | / |
| --------------- | --------------------------- |
| SIGN() | 正負值,輸出1,0,-1 |
| ABS() | 絕對值 |
| SUM() | 總和 |
| AVG() | 平均 |
| COUNT() | 計數(不含Null) |
| MIN() | 最小 |
| MAX() | 最大 |
| STDDEV() | 標準差 |
| VARIANCE() | 變異數 |
| RAND() 0~1 | 隨機小數 |
| ROUND(FLOAT, n) | 丟棄小數點(4捨5入) , n 保留位數, 不設預設0 |
| FLOOR(NUM) | 丟棄小數點(無條件) |
* * *
### 條件函式：
```SQL
CASE WHEN 條件1 THEN Value1
WHEN 條件2 THEN Value2
ELSE Value3
END
IF(條件, T, F)
IFNULL(T, F)
```
* * *
### 其他函式：
| func | / |
| ------------- | -------- |
| DATEBASE() | 目前資料庫 |
| VERSION() | MYSQL 版本 |
| USER() | 目前使用者 |
| PASSWORD(str) | 加密 str |
Sign in with Wallet
Connect another wallet