《MYSQL 8 教學–DAY06–常用字串函數》操作分享 | 學習筆記

本次的教學，主要分享MySQL在對資料表(Table)有了基本操作的相關知識後，後續開始針對用戶管理操作，在做更進階的討論，主要我們會談用戶與權限管理的實際操作，相關指令會圍繞在ASCII、CHAR_LENGTH、CONCAT、INSTR、LOWER、LPAD、LTRIM、POSITION、REPLACE、SUBSTR，本教學是以Mysql 8.0為出發的教學，當然一些通用語法Mysql 8.0之前的版本也適用喔，所以大家不用擔心，只要一起做，相信你也可以了解MySQL 中的資料表操作是怎麼一回事。

本教學的範例說明

在MySQL中，用戶和權限管理是關於管理資料庫用戶及其訪問權限的工作。以下是一些與MySQL用戶和權限管理相關的操作

資料庫名稱:rdpapashop

CREATE DATABASE `rdpapashop` /*!40100 COLLATE 'utf8mb4_unicode_ci' */

可設定權限的資料表名稱:employees員工資料表、department部門資料表

建立資料表(employees)

use `rdpapashop`;
CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    department_id INT,
    salary DECIMAL(10, 2),
    age INT,
    hire_date DATE
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC;

建立範例資料(employees)

INSERT INTO employees (first_name, last_name, department_id, salary, age, hire_date)
VALUES
    ('John', 'Doe', 1, 55000.00, 30, '2020-01-15'),
    ('Jane', 'Smith', 2, 60000.50, 28, '2019-11-20'),
    ('Michael', 'Johnson', 3, 75000.75, 35, '2018-05-10'),
    ('Emily', 'Brown', 4, 50000.25, 27, '2021-02-18'),
    ('William', 'Jones', 1, 60000.00, 29, '2017-09-03'),
    ('Olivia', 'Davis', 3, 70000.50, 32, '2016-12-22'),
    ('James', 'Wilson', 2, 62000.75, 31, '2020-08-14'),
    ('Sophia', 'Taylor', 4, 51000.25, 26, '2019-03-25'),
    ('Benjamin', 'Anderson', 3, 77000.00, 33, '2015-06-12'),
    ('Emma', 'Martinez', 1, 58000.50, 28, '2022-04-30'),
    ('Mason', 'Hernandez', 4, 52000.75, 27, '2018-10-05'),
    ('Ava', 'Lopez', 2, 61000.25, 30, '2020-07-02'),
    ('Liam', 'Garcia', 3, 76000.00, 34, '2017-02-11'),
    ('Isabella', 'Rodriguez', 1, 59000.50, 29, '2019-09-19'),
    ('Noah', 'Martinez', 4, 53000.75, 26, '2021-01-08'),
    ('Oliver', 'Gonzalez', 3, 78000.25, 35, '2016-04-17'),
    ('Sophia', 'Hernandez', 2, 63000.50, 31, '2020-11-27'),
    ('Mia', 'Smith', 1, 60000.00, 28, '2018-08-07'),
    ('Liam', 'Anderson', 3, 79000.75, 36, '2017-03-15'),
    ('Ava', 'Johnson', 4, 54000.25, 27, '2019-06-28'),
    ('Lucas', 'Brown', 1, 65000.75, 29, '2022-01-05'),
    ('Harper', 'Garcia', 2, 72000.00, 32, '2017-11-12'),
    ('Evelyn', 'Davis', 3, 59000.50, 28, '2018-03-19'),
    ('Logan', 'Taylor', 4, 56000.25, 27, '2021-04-14'),
    ('Avery', 'Martinez', 1, 61000.00, 30, '2019-09-22'),
    ('Jackson', 'Wilson', 2, 68000.75, 33, '2016-02-28'),
    ('Penelope', 'Lopez', 3, 60000.25, 28, '2020-05-17'),
    ('Sebastian', 'Hernandez', 4, 57000.50, 27, '2021-08-10'),
    ('Luna', 'Gonzalez', 1, 62000.00, 29, '2019-12-03'),
    ('Mateo', 'Johnson', 2, 70000.75, 34, '2017-04-25'),
    ('Grace', 'Smith', 3, 61000.50, 30, '2018-06-28');

✒RDPAPA Tip

安裝好以上範例資料後，我們就可以開始我們的教學練習，本範例資料為employees的資料表，包含員工的基本資訊，如姓名、部門、薪水等欄位

操作實例

返回查詢結果第一個字元的ASCII值-ASCII

select 
		ASCII(first_name) as ASCII,first_name,last_name
from 
		employees 

計算字串長度-CHAR_LENGTH

select 
		CHAR_LENGTH(first_name) as LENGTH,first_name,last_name 
from 
		employees 

連接字串函數-CONCAT

select 
		CONCAT(first_name," ",last_name ) AS FULL_Name
from 
		employees 
#將first_name與last_name 組合成完整姓名

將數值格式化回傳-FORMAT

SELECT
    FORMAT(111111, 2),#輸出格式為###,###.##，小數點後沒有值直接補0
    FORMAT(111111.156, 2),#輸出格式為###,###.##，取到小數點後2位直接惠四捨五入
    FORMAT(111111.111, 0)#輸出格式為###,###.##，捨去小數點，回傳僅整數

#將數字四捨五入到指定的小數位數，並按照#,###,###.##之類的格式進行格式化，然後將結果作為字串傳回

回傳某個特定字在字串中的第一個索引位置-INSTR

SELECT
    INSTR('Hello World', 'World')
#傳回一個子字串在一個字串中第一次出現的位置的索引值。INSTR()函數是不區分大小寫的

將回傳的英文字串大小寫一致性轉換-LOWER轉小寫、UPPER轉大寫

SELECT 
	LOWER('Hello'), 
	UPPER('Hello')

將回傳左邊補上指定的字串-LPAD

SELECT
    LPAD('0123456789', 10, '0'),
    LPAD('123456789', 10, '0'),
    LPAD('23456789', 10, '0'),
    LPAD('3456789', 10, '0'),
    LPAD('456789', 10, '0')
#在字串的右側補上指定的字串，設定達到指定的長度。如果想要在字串的右側填充字串，請使用 RPAD() 函數

删除字串的左側空白-LTRIM、RTRIM、TRIM

SELECT
        LTRIM('   Hello '),
		RTRIM('   Hello '),
		TRIM('   Hello ')
#LTRIM刪除字串左邊空格、RTRIM刪除字串右邊空格、TRIM刪除字串前後空格。

搜尋關鍵字在字串中的位置-POSITION

SELECT
    POSITION('He' IN 'Hello World'),
    POSITION('he' IN 'Hello World'),
    POSITION('or' IN 'Hello World'),
    POSITION('co' IN 'Hello World')
#返回一個字串在另一個字串中第一次出現的位置的數位索引

將指定的字串重覆輸出-REPEAT

SELECT
    REPEAT('MySQL', 3),
    REPEAT('Go ', 3)

將字串中出現的所有子字串替換成新的子字串-REPLACE

SELECT
    REPLACE('Hello World', 'World', 'rdpapa'),
    REPLACE('Hello World', 'l', 'L')

從字串中提取子字串並回傳-SUBSTR

SELECT
    SUBSTR('Hello', 1, 2),
    SUBSTR('Hello', 3, 2),
    SUBSTR('Hello', 1),
    SUBSTR('Hello', 1, 8)

Reference

• MySQL 8.0 Reference Manual String Functions and Operators