MySQL 學習筆記

# 前言

我的 MySQL 技能成長日記




# 未開始先談移除

  1. 打開 terminal
  2. 使用 mysqldump 備份你的資料庫

    mysqldump -u你的使用者名 -p --all-databases > /tmp/backup.sql
  3. 尋找是否還有 MySQL process, 若有的話, 使用 kill -9 processId 砍掉

    ps -ax | grep mysql
  4. 若使用 brew, 使用 brew 刪除

    brew remove mysql
    brew cleanup
  5. 移除以下檔案

    sudo rm /usr/local/mysql
    sudo rm -rf /usr/local/var/mysql
    sudo rm -rf /usr/local/mysql*
    sudo rm ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
    sudo rm -rf /Library/StartupItems/MySQLCOM
    sudo rm -rf /Library/PreferencePanes/My*
  6. 卸載之前的 MySQL 自動登入

    launchctl unload -w ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
  7. 移除之前的 MySQL 設定

    vim /etc/hostconfig
    # Remove the line MYSQLCOM=-YES-
  8. 移除以下檔案

    rm -rf ~/Library/PreferencePanes/My*
    sudo rm -rf /Library/Receipts/mysql*
    sudo rm -rf /Library/Receipts/MySQL*
    sudo rm -rf /private/var/db/receipts/*mysql*
  9. 重啟電腦, 確保沒有任何程序在跑

  10. 執行 mysql, 應該不能執行




# 中文亂碼問題

# 先確認 locale 狀態:

  1. 確認 charset 狀態, 在 mysql 當中:

    show variables like 'char%';

  2. 確認 database locale 狀態:

    SELECT default_character_set_name FROM information_schema.SCHEMATA 
    WHERE schema_name = "databaseName";
  3. 確認 table locale 狀態:

    SELECT CCSA.character_set_name FROM information_schema.`TABLES` T,
    information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA
    WHERE CCSA.collation_name = T.table_collation
    AND T.table_schema = "databaseName"
    AND T.table_name = "tableName";
  4. 確認 column locale 狀態:

    show full columns from tableName;
    ```
    5. 確認 `collation`
    ```bash
    show variables like 'collation%';


# locale 修改

# 確認 mysql 設定檔位置並修改

  • 取得 ‘my.cnf’ 位置, 通常,檔案會在 /etc/mysql/my.cnf

    mysql --help -verbose | grep 'my.cnf'
  • 編輯檔案

    vim /etc/mysql/my.cnf
  • 貼上以下設定

    [client]
    default-character-set=utf8mb4

    [mysql]
    default-character-set=utf8mb4


    [mysqld]
    collation-server = utf8_unicode_ci
    init-connect='SET NAMES utf8'
    character-set-server = utf8mb4

# 修改 database locale

ALTER DATABASE database_name CHARACTER SET utf8 COLLATE utf8_general_ci;

# 修改 table locale

ALTER TABLE table_name CONVERT TO CHARACTER SET utf8;

# 完成修改後, 重啟 mysql

service mysql restart

# 別忘了 PHP locale

如果有用使用 PHP 的話,記得也將 PHP 那邊的 locale 設為 uft8, 可參考文章

mysqli_set_charset($dbc,"utf8");


# 還是亂碼?

理論上,上面的都做完了,應該就不會有亂碼了,如果問題尚未解決,可以試試下面的方法

  • 查看資料庫 locale 設定

    show create database databaseName
  • 查看 table locale 設定

    show create table tableName
  • 其餘設定

    set names uft8;




# 匯入大量資料

# 從 sql 檔匯入

mysql --max_allowed_packet=100M -u root -p database < dump.sql


# 修改 my.cnf 或 my.ini 檔

[mysqld]
max_allowed_packet=100M


# 暫時性的變更

set global net_buffer_length=1000000;
set global max_allowed_packet=1000000000;




# 查單一資料庫 size

SELECT table_schema "databaseName", ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" FROM information_schema.tables where table_schema="databaseName" GROUP BY table_schema;




# where 子句操作符




# Questions and Answers

以下的 MySQL example code 的意思是?
  • Example:

    SELECT FIND_IN_SET('Anahit Vanetsyan', Names) AS Position
    FROM
    (SELECT GROUP_CONCAT(Name ORDER BY join_date) AS Names
    FROM
    ( SELECT CONCAT(name_first, SPACE(1), name_last) AS Name,
    join_date
    FROM humans
    WHERE country_id = 'ru')
    AS derived_1 )
    AS derived_2;
  • Answer:
    GROUP_CONCAT 會將 Name column 的所有姓名連接起來, 並 ORDER BY join_date, 然後 FIND_IN_SET() 從 Name column 中找到 ‘Anahit Vanetsyan’ 的位置

以下的 MySQL example code 的意思是?
  • Example:

    SELECT
    SUBSTRING(common_name, 1, LOCATE(' Avocet', common_name) ) AS 'Avocet'
    FROM birds
    JOIN bird_families USING(family_id)
    WHERE bird_families.scientific_name = 'Recurvirostridae'
    AND birds.common_name LIKE '%Avocet%';
  • Answer:
    LOCATE 會取得 ‘ Avocet’ 前面那一個空格的位置, 而 SUBSTRING 從第 1 個位置開始取, 取 LOCATE 得到的結果, 正好把 ‘Avocet’ 濾掉

以下的 MySQL query 的意思是?
  • Example:

    # +--------------------------------------------------------+
    # | prospect_name |
    # +--------------------------------------------------------+
    # | Ms.| Caryn-Amy | Rose |
    # | Mr.| Colin | Charles |
    # | Mr.| Kenneth | Dyer |
    # | Ms.| Sveta | Smirnova |
    # +--------------------------------------------------------+

    SELECT SUBSTRING_INDEX(prospect_name, '|', 1) AS title,
    SUBSTRING_INDEX( SUBSTRING_INDEX(prospect_name, '|', 2), '|', -1) AS first_name,
    SUBSTRING_INDEX(prospect_name, '|', -1) AS last_name
    FROM prospects WHERE prospect_id = 7;
  • Answer:

    // 取到第一個 '|' 之前的所有字串
    SELECT SUBSTRING_INDEX(prospect_name, '|', 1) AS title,
    // 內函數取得第兩個 '|' 之前的所有字串, 外函數則是從尾端往前找, 直到找到 '|', 取這之間的字串
    SUBSTRING_INDEX( SUBSTRING_INDEX(prospect_name, '|', 2), '|', -1) AS first_name,
    // 從尾端往前找, 直到找到 '|', 取這之間的字串
    SUBSTRING_INDEX(prospect_name, '|', -1) AS last_name
    FROM prospects WHERE prospect_id = 7;
以下的 MySQL query 的意思是?
  • Example:

    # +--------------------------------------------------------+
    # | prospect_name |
    # +--------------------------------------------------------+
    # | Ms. Caryn-Amy Rose |
    # | Mr. Colin Charles |
    # | Mr. Kenneth Dyer |
    # | Ms. Sveta Smirnova |
    # +--------------------------------------------------------+

    SELECT SUBSTRING(prospect_name, 1, 2) AS title,
    SUBSTRING(prospect_name FROM 5 FOR 25) AS first_name,
    SUBSTRING(prospect_name, -25) AS last_name
    FROM prospects LIMIT 3;
  • Answer:

    // 從第 1 個開始取, 取 2 個
    SELECT SUBSTRING(prospect_name, 1, 2) AS title,
    // 從第 5 個開始取, 取 25 個
    SUBSTRING(prospect_name FROM 5 FOR 25) AS first_name,
    // 取後面算來 25 個
    SUBSTRING(prospect_name, -25) AS last_name
    FROM prospects LIMIT 3;
以下的 MySQL query 的意思是?
  • Example:

    # +--------------------------------------------------------+
    # | prospect_name |
    # +--------------------------------------------------------+
    # | Ms. Caryn-Amy Rose |
    # | Mr. Colin Charles |
    # | Mr. Kenneth Dyer |
    # | Ms. Sveta Smirnova |
    # +--------------------------------------------------------+

    SELECT LEFT(prospect_name, 2) AS title,
    MID(prospect_name, 5, 25) AS first_name,
    RIGHT(prospect_name, 25) AS last_name
    FROM prospects LIMIT 4;
  • Answer:
    LEFT: 從左邊開始取, 取 2 個
    MID: 從第 5 個開始取, 取 25 個
    RIGHT: 從後面往前取, 取 25 個

以下的 MySQL query 的意思是?
  • Example:

    SELECT CONCAT(RPAD(common_name, 20, '.' ),
    RPAD(Families.scientific_name, 15, '.'),
    Orders.scientific_name) AS Birds
    FROM birds
    JOIN bird_families AS Families USING(family_id)
    JOIN bird_orders AS Orders
    WHERE common_name != ''
    AND Orders.scientific_name = 'Ciconiiformes'
    ORDER BY common_name LIMIT 3;

    # +--------------------------------------------------+
    # | Birds |
    # +--------------------------------------------------+
    # | Abbott's Babbler....Pellorneidae...Ciconiiformes |
    # | Abbott's Booby......Sulidae........Ciconiiformes |
    # | Abbott's Starling...Sturnidae......Ciconiiformes |
    # +--------------------------------------------------+
  • Answer:
    利用 RPAD 將空格填滿, arg1 為作用的 column, arg2 為該 column 預期的字串長度, arg3 為不足字串長度時, 要使用什麼字符將之補滿

以下的 MySQL query 的意思是?
  • Example:

    SELECT QUOTE(common_name)
    FROM birds
    WHERE common_name LIKE "%Prince%"
    ORDER BY common_name;
  • Answer:

    # 將資料夾上 quote 輸出, 並 escape 特殊符號
    SELECT QUOTE(common_name)
    FROM birds
    WHERE common_name LIKE "%Prince%"
    ORDER BY common_name;

    # +----------------------------------+
    # | QUOTE(common_name) |
    # +----------------------------------+
    # | 'Prince Henry\'s Laughingthrush' |
    # | 'Prince Ruspoli\'s Turaco' |
    # | 'Princess Parrot' |
    # +----------------------------------+
以下的 MySQL query 的意思是?
  • Example:

    SELECT LCASE(common_name) AS Species,
    UCASE(bird_families.scientific_name) AS Family
    FROM birds
    JOIN bird_families USING(family_id)
    WHERE common_name LIKE '%Wren%'
    ORDER BY Species
    LIMIT 5;
  • Answer:

    ## 將 common_name column 的值轉為小寫
    SELECT LCASE(common_name) AS Species,
    ## 將 scientific_name column 的值轉為大寫
    UCASE(bird_families.scientific_name) AS Family
    FROM birds
    ## 經由 family_id JOIN 兩張表
    JOIN bird_families USING(family_id)
    WHERE common_name LIKE '%Wren%'
    ORDER BY Species
    LIMIT 5;

    # +-------------------------+---------------+
    # | Species | Family |
    # +-------------------------+---------------+
    # | apolinar's wren | TROGLODYTIDAE |
    # | band-backed wren | TROGLODYTIDAE |
    # | banded wren | TROGLODYTIDAE |
    # | bar-winged wood-wren | TROGLODYTIDAE |
    # | bar-winged wren-babbler | TIMALIIDAE |
    # +-------------------------+---------------+
以下的 MySQL query 的意思是?
  • Example:

    mysql -p --skip-column-names -e \
    "SELECT CONCAT_WS('|', IFNULL(formal_title, ' '), IFNULL(name_first, ' '),
    IFNULL(name_last, ' '), IFNULL(street_address, ' '),
    IFNULL(city, ' '), IFNULL(state_province, ' '),
    IFNULL(postal_code, ' '), IFNULL(country_id, ' '))
    FROM birdwatchers.humans WHERE membership_type = 'premium'
    AND membership_expiration > CURDATE();" > rookery_patch_mailinglist.txt
  • Answer:

    mysql -p --skip-column-names -e \
    # 跳過欄位那一欄不做處理, -p 代表 password, -e 代表執行 "" 內的程式碼
    # 使用 '|' 來連接並隔開每個 column 的value
    # CONCAT_WS, WS 為 with seprarator 的意思
    "SELECT CONCAT_WS('|', IFNULL(formal_title, ' '), IFNULL(name_first, ' '),
    # 加上 IFNULL, 若 NULL 的話也會輸出空字串, 否則 CONCAT_WS 會不處理直接跳過
    # 造成 select 8 個欄位, 但卻只輸出 4 個欄位的值, 也不知道哪個欄位沒值
    IFNULL(name_last, ' '), IFNULL(street_address, ' '),
    IFNULL(city, ' '), IFNULL(state_province, ' '),
    IFNULL(postal_code, ' '), IFNULL(country_id, ' '))
    FROM birdwatchers.humans WHERE membership_type = 'premium'
    # 將結果輸出到 txt 檔
    AND membership_expiration > CURDATE();" > rookery_patch_mailinglist.txt
以下的 MySQL query 的意思是?
  • Example:

    SELECT common_name AS 'Bird',
    conservation_state AS 'Status'
    FROM birds
    LEFT JOIN conservation_status USING(conservation_status_id)
    WHERE common_name LIKE '%Egret%'
    ORDER BY Status, Bird;
  • Answer:
    從 birds table select common_name column (命名呈現的 column name 為 Bird), conservation_state column (命名呈現的 column name 為 Status)
    left join conservation_status table, 透過 conservation_status_id
    left join 會先取出 birds table, 所以如果並沒有對應的 conservation_status_id 的話, 就會顯示 null
    像是

    +--------------------+-----------------+
    | Bird | Status |
    +--------------------+-----------------+
    | Great Egret | NULL |
    | Cattle Egret | Least Concern |
    | Intermediate Egret | Least Concern |
    | Little Egret | Least Concern |
    | Snowy Egret | Least Concern |
    | Reddish Egret | Near Threatened |
    | Chinese Egret | Vulnerable |
    | Slaty Egret | Vulnerable |
    +--------------------+-----------------+
以下的 MySQL query 的意思是?
  • Example:

    SELECT book_id, title, status_name
    FROM books
    JOIN status_names USING(status_id);
  • Answer:
    JOIN books 跟 status_names 兩張 table, 因為要用來 connect 兩張 table 的 key 都一樣, 因此使用 USING

以下的 MySQL query 的意思是?
  • Example:

    SELECT book_id, title, status_name
    FROM books
    JOIN status_names ON(status = status_id);
  • Answer:
    JOIN books 跟 status_names 兩張 table, 用 ON 連結 status 以及 status_id 欄位

以下的 MySQL query 的意思是?
  • Example:

    SELECT 'Pelecanidae' AS 'Family',
    COUNT(*) AS 'Species'
    FROM birds, bird_families AS families
    WHERE birds.family_id = families.family_id
    AND families.scientific_name = 'Pelecanidae'
    UNION
    SELECT 'Ardeidae',
    COUNT(*)
    FROM birds, bird_families AS families
    WHERE birds.family_id = families.family_id
    AND families.scientific_name = 'Ardeidae';
  • Answer:

        ## 'Pelacanidae' 為不存在的欄位, 以純文字賦予欄位值, 結果如下
    SELECT 'Pelecanidae' AS 'Family',
    ## Species 為另一個 column
    COUNT(*) AS 'Species'
    FROM birds, bird_families AS families
    WHERE birds.family_id = families.family_id
    AND families.scientific_name = 'Pelecanidae'
    UNION
    ## 因為使用 Union, 這邊不須再加 alias, 會自動歸類到 'Family' column
    SELECT 'Ardeidae',
    COUNT(*)
    FROM birds, bird_families AS families
    WHERE birds.family_id = families.family_id
    AND families.scientific_name = 'Ardeidae';

    ## +-------------+---------+
    ## | Family | Species |
    ## +-------------+---------+
    ## | Pelecanidae | 10 |
    ## | Ardeidae | 157 |
    ## +-------------+---------+
以下的 MySQL query 的意思是?
  • Example:

    DELETE FROM humans, prize_winners
    USING humans JOIN prize_winners
    WHERE name_first = 'Elena'
    AND name_last = 'Bokova'
    AND email_address LIKE '%yahoo.com'
    AND humans.human_id = prize_winners.human_id;
  • Answer:

    // 同時 delete humans 以及 prize_winners 的資料
    // 如果不加 prize_winners, 那該 table 上的資料就
    // 不會被刪除
    DELETE FROM humans, prize_winners
    // JOIN prize_winners table
    USING humans JOIN prize_winners
    WHERE name_first = 'Elena'
    AND name_last = 'Bokova'
    AND email_address LIKE '%yahoo.com'
    AND humans.human_id = prize_winners.human_id;
以下的 MySQL query 的意思是?
  • Example:

    CREATE TEMPORARY TABLE possible_duplicates
    (name_1 varchar(25), name_2 varchar(25));
  • Answer:
    建立一個臨時的 table, 當退出 MySQL client 時, 該 table 會自動銷毀

以下的 MySQL query 的意思是?
  • Example:

    INSERT INTO humans
    (formal_title, name_first, name_last, email_address, better_birders_site)
    VALUES('Mr','Barry','Pilson', 'barry@gomail.com', 1),
    ('Ms','Lexi','Hollar', 'alexandra@mysqlresources.com', 1),
    ('Mr','Ricky','Adams', 'ricky@gomail.com', 1)
    ON DUPLICATE KEY
    UPDATE better_birders_site = 2;
  • Answer:
    因為 email_address 有 unique 特性, 所以當 insert 過程中發現有 duplicate 的 row 時, 會 update 該 row 的 better_birders_site column 為 2

以下的 MySQL query 錯誤的原因是?
  • Example:

    UPDATE prize_winners, humans
    SET winner_date = CURDATE()
    WHERE winner_date IS NULL
    AND country_id = 'uk'
    AND prize_winners.human_id = humans.human_id
    ORDER BY RAND()
    LIMIT 2;
  • Answer:
    order by 及 limit 只適用於單一 table, 當 update 複數的 table 時就會出錯

以下的 MySQL query 的意思是?
  • Example:

    UPDATE prize_winners, humans
    SET winner_date = NULL,
    prize_chosen = NULL,
    prize_sent = NULL
    WHERE country_id = 'uk'
    AND prize_winners.human_id = humans.human_id;
  • Answer:
    從 human table 中取得 ‘country_id = uk’ 的 row, 再經此找到符合 ‘prize_winners.human_id = humans.human_id’ 的 prize_winners
    最後 update 這些 prize_winners 的 winner_date, prize_chosen, prize_sent columns

以下的 MySQL query 的意思是?
  • Example:

    ALTER TABLE humans
    CHANGE COLUMN formal_title formal_title ENUM('Mr.','Ms.','Mr','Ms');

    UPDATE humans
    SET formal_title = SUBSTRING(formal_title, 1, 2);

    ALTER TABLE humans
    CHANGE COLUMN formal_title formal_title ENUM('Mr','Ms');
  • Answer:

    ## 修改 humans table
    ALTER TABLE humans
    ## 將 column formal_title 改成 formal_title, type 為 ENUM('Mr.', 'Ms.', 'Mr', 'Ms');
    CHANGE COLUMN formal_title formal_title ENUM('Mr.','Ms.','Mr','Ms');

    ## 更新 humans table
    UPDATE humans
    ## 更新 formal_title column, 取 formal_title 的值, 從第一個字開始取, 共取兩個字
    ## 因此 "Mr." 會變成 "Mr"
    SET formal_title = SUBSTRING(formal_title, 1, 2);

    ## 同上, 修改 formal_title colum 的 type
    ALTER TABLE humans
    CHANGE COLUMN formal_title formal_title ENUM('Mr','Ms');
以下的 MySQL query 的意思是?
  • Example:

    select common_name as 'hawks'
    from birds
    where common_name regexp '[[:space:]]hawk|[[.hyphen.]]hawk'
    and common_name not regexp 'hawk-owl|hawk owl'
    order by family_id;
  • Answer:

    ## select common_name column, 並定義新欄位名稱為 Hawks
    select common_name as 'hawks'
    ## 從 birds table
    from birds
    ## common_name 的值必須是 " hawk", 或是 "-hawk"
    where common_name regexp '[[:space:]]hawk|[[.hyphen.]]hawk'
    ## common_name 的值必須不是 "hawk_owl" 或 "hawk owl"
    and common_name not regexp 'hawk-owl|hawk owl'
    ## 經由 family_id column 排序
    order by family_id;
以下的 MySQL column information 中, latin1_bin 的 bin 是什麼意思?
  • Example:

    SHOW FULL COLUMNS
    FROM birds LIKE 'common_name' \G

    *************************** 1. row ***************************
    Field: common_name
    Type: varchar(255)
    Collation: latin1_bin
    Null: YES
    Key:
    Default: NULL
    Extra:
    Privileges: select,insert,update,references
    Comment:
  • Answer:
    binary 的意思, 表示儲存在該欄位的資料都會以 binary 定序

以下的 MySQL example code 是什麼意思?
  • Example:

    SELECT common_name AS 'Hawks'
    FROM birds
    WHERE common_name REGEXP BINARY 'Hawk'
    AND common_name NOT REGEXP 'Hawk-Owl'
    ORDER BY family_id LIMIT 10;
  • Answer:
    一般來說, REGEXP 不會區分大小寫, 若要區分, 可加上 BINARY, 會以二進制定序, h 跟 H 的二進制碼不同, 所以可以區分大小寫

以下的 MySQL example code 是什麼意思?
  • Example:

    SELECT common_name, scientific_name, family_id
    FROM birds
    WHERE family_id IN(103, 160, 162, 164)
    AND common_name != ''
    ORDER BY common_name
    LIMIT 3, 2;
  • Answer:
    從第三筆之後開始查詢, 取得兩筆資料, 相當於 offset 3, limit 2

以下的 MySQL example code 是什麼意思?
  • Example:

    INSERT HIGH_PRIORITY INTO bird_sightings
  • Answer:
    MySQL 預設 insert 的 priority 就大於 select, 但如果預設值被改掉, 就可以使用上面的語法

以下的 MySQL example code 有個與 MySQL Client 不方便的地方, 是什麼?
  • Example:

    INSERT LOW_PRIORITY INTO bird_sightings
  • Answer:
    在 low priority instruction 被執行之前, client 會一直卡住

以下的 MySQL example code 是什麼意思?
  • Example:

    INSERT LOW_PRIORITY INTO bird_sightings
  • Answer:
    先完成手邊的工作, 再處理 low priority 的工作

以下的 MySQL example code 是什麼意思?
  • Example:

    REPLACE INTO bird_families
    (scientific_name, brief_description, order_id)
    VALUES('Viduidae', 'Indigobirds & Whydahs', 128),
    ('Estrildidae', 'Waxbills, Weaver Finches, & Allies', 128),
    ('Ploceidae', 'Weavers, Malimbe, & Bishops', 128);
  • Answer:
    將指定的 value insert 到指定的欄位, 如果遇到重複的, 則覆寫, 相當於 Laravel 中的 insertOrUpdate

以下的 MySQL example code 是什麼意思?
  • Example:

    UPDATE bird_families, bird_orders
    SET bird_families.order_id = bird_orders.order_id
    WHERE bird_families.order_id IS NULL
    AND cornell_bird_order = bird_orders.scientific_name;
  • Answer:
    將 bird_families table 中的 order_id update 為 bird_orders table 中的 order_id
    只更新 where clause 中限制的 record

以下的 MySQL example code 是什麼意思?
  • Example:

    INSERT IGNORE INTO bird_families
    (scientific_name, brief_description, cornell_bird_order)
    SELECT bird_family, examples, bird_order
    FROM cornell_birds_families_orders;
  • Answer:
    從 cornell_birds_families_orders table, select bird_family, examples, bird_order 三個欄位, 並將 select 的結果 insert 到 bird_families table 的 scientific_name, brief_description, cornell_bird_order 欄位, 同時, 若過程中出現錯誤, ignore 錯誤繼續執行

以下的 MySQL example code 中, 為什麼會失敗?
  • Example:

    ALTER TABLE conservation_status
    CHANGE status_id conservation_status_id INT AUTO_INCREMENT PRIMARY KEY;

    ERROR 1068: Multiple primary key defined
  • Answer:
    status_id 欄位原本已經有 primary_key, 這是一個 index
    上面的語法在原本已經有一個 index 的同時, 又要增加一個 index, 所以報錯
    應該要先把原本的 index 拿掉

以下的 MySQL example code 中, 為什麼最後一行的 common_name 要重複兩次?
  • Example:

    ALTER TABLE birds_new
    ADD COLUMN body_id CHAR(2) AFTER wing_id,
    ADD COLUMN bill_id CHAR(2) AFTER body_id,
    ADD COLUMN endangered BIT DEFAULT b'1' AFTER bill_id,
    CHANGE COLUMN common_name common_name VARCHAR(255);
  • Answer:
    第一次是指要變更的 column 名稱
    第二次是指要變更成哪個名稱

以下的 MySQL example code 中, 在建立 bill_id column 時, 理論上 body_id column 應該還沒創建出來, 那為什麼可以指定 bill_id after body_id 呢?
  • Example:

    ALTER TABLE birds_new
    ADD COLUMN body_id CHAR(2) AFTER wing_id,
    ADD COLUMN bill_id CHAR(2) AFTER body_id,
    ADD COLUMN endangered BIT DEFAULT b'1' AFTER bill_id,
    CHANGE COLUMN common_name common_name VARCHAR(255);
  • Answer:
    在 MySQL ALTER 的過程中, 會先建立一張虛擬的表格, 如果語法執行上都沒有問題, 會用這張虛擬表格取代原本的
    如果語法有錯, 則會刪除這張虛擬表格

MySQL 的 column property character set 以及 collation, 分別代表的意思是?

character set: 該欄位會儲存的語言屬於哪一種編碼
collation: 該欄位該以什麼樣的語種排序

以下的 MySQL command 的意思是?
  • Example:

    SHOW CREATE TABLE birds \G
  • Answer:
    顯示建立該 table 的語法

MySQL 中, CHAR 跟 VARCHAR, 使用的大原則是?

如果確切知道有幾個 character, 就使用 CHAR, 否則則使用 VARCHAR

MySQL 中, CHAR 跟 VARCHAR, 哪個較不會產生 fragmentation?

VARCHAR

MySQL 中, CHAR 跟 VARCHAR, 哪個使用 index 的 performance 較佳?

CHAR

MySQL 中, 當我使用 mysql client select, 有時會因為資料太多而資料欄位沒有排列好, 像下圖一樣, 這時候可以使用哪個 command 來顯示正確的格式?
  • Example:
  • Answer:
    可在最後用 \G 取代 ;
MySQL 中, virtualAs column type 的作用是?

虛擬 column, 內容來自於其他 column 的計算結果(自定義), 比如說, 新增一個 virtualAs column 叫做 full_name, 自定義內容來自於 first_name + last_name

command line 中, 如果 filesystem 的目前登入者跟 MySQL 的 user 是同一個, 那在登入時我還需要使用 mysql -u username -p password 嗎?

不需要, 直接使用 mysql -p 即可

以下的 mysql command 的意思是?
  • Example:

    mysql -u root -p -e "GRANT ALL ON *.* TO 'russell'@'localhost';"
  • Answer:
    賦予 russell, host 為 localhost 所有的操作權限

以下的 mysql command 的意思是?
  • Example:

    mysql -u root -p -e "SHOW GRANTS FOR 'russell'@'localhost' \G"
  • Answer:
    顯示 russell 的所有權限

以下的 mysql command 的意思是?
  • Example:

    mysql -u root -p -e "GRANT SELECT ON *.* TO 'russell'@'localhost';"
  • Answer:
    給予 russell user, host 為 localhost, select 所有資料庫所有 table 的權限

以下的 mysql command 的意思是?
  • Example:

    mysql -u root -p -e "GRANT USAGE ON *.*
    TO 'russell'@'localhost'
    IDENTIFIED BY 'Rover#My_1st_Dog&Not_Yours!';"
  • Answer:

    ## 給予存取所有資料庫所有 table 的權限, 這裡是指可存取的 database 以及 table
    ## 但並沒有給予任何實際上的權限, 如 select, update, insert
    mysql -u root -p -e "GRANT USAGE ON *.*
    ## 賦予上面的權限給 user russell, host 為 localhost
    TO 'russell'@'localhost'
    ## 密碼為 Rover#My_1st_Dog&Not_Yours!
    IDENTIFIED BY 'Rover#My_1st_Dog&Not_Yours!';"
以下的 mysql command 的意思是?
  • Example:

    mysqladmin -u root -p flush-privileges
  • Answer:
    當完成一些設定更新後, 下達上面的指令讓設定生效

以下的 mysql command 的意思是?
  • Example:

    mysql -u root -p -e "SELECT User,Host FROM mysql.user;"
  • Answer:
    列出所有的 user 以及 host

以下的 mysql command 的意思是?
  • Example:

    mysql -u root -p -e "SET PASSWORD FOR 'root'@'127.0.0.1' PASSWORD('new_pwd');"
    mysql -u root -p -e "SET PASSWORD FOR 'root'@'localhost' PASSWORD('new_pwd');"
    mysql -u root -p -e "DROP USER 'root'@'%';"
    mysql -u root -p -e "DROP USER ''@'localhost';"
  • Answer:
    1, 2 行給 root 的不同 host 設定新密碼, 儘管 127.0.0.1 等於 localhost, 但也必須分別設定密碼
    3, 'root'@'%' 表示不管從任何 host 都可以 claim 自己是 root, 這樣的設定並不安全, 必須刪除
    4, ''@'localhost' 表示只要是來自 localhost, 任何 username 都被接受, 這樣的設定也不安全, 應該要刪除這樣的 anonymous user

Mysql 中, 如果搜尋的字串是 % 開頭, 那可以用 index 嗎?

不行

Mysql 中, set column type 的特性是?

可指定一個允許輸入值列表, 所有 insert 的值都必須在這列表內才允許輸入, 與 enum 不同之處在於, enum 只允許 insert 一個值, set 允許 insert 零個或多個

如何在已知 root 現有密碼的情況下重新設定 root 密碼?
mysqladmin -u root -p flush-privileges password
Mysql 中, enum column type 的特性是?

可指定一個允許輸入值列表, 所有 insert 的值都必須在這列表內才允許輸入, 與 enum 不同之處在於, enum 只允許 insert 一個值, set 允許 insert 零個或多個

Mysql 中, date, datetime, timestamp, 三種 data type 差異分別是?

date: yyyy-mm-dd
datetime: yyyy-mm-dd hh:mm:ss
timestamp: yyyy-mm-dd hh:mm:ss 除此之外, timestamp 會將收到的時間轉成 UTC, 當 query 此欄位時, 會再從 UTC 轉成目前時區

Mysql 中, float, double, decimal, 由小至大的排列順序為?

float, double, decimal

Mysql 中, 若要精準的儲存一個數字(含小數點), 建議使用哪個 type?

decimal

Mysql 中, double type 有效位數約為?

17 位

Mysql 中, float type 有效位數約為?

7 位數

Mysqld 通常代表什麼?

MySQL Daemon, 通常代表 MySQL Server

以下的 SQL 語法中的意思是?
  • Example:

    CREATE INDEX prod_name_ind
    ON PRODUCTS (prod_name);
  • Answer:
    建立一個 index, 名為 prod_name_ind, table 為 PRODUCTS, 欄位為 prod_name

以下的 SQL 語法中的意思是?
  • Example:

    BEGIN TRANSACTION
    INSERT INTO Customers(cust_id, cust_name)
    VALUES('1000000010', 'Toys Emporium');
    SAVE TRANSACTION StartOrder;
    INSERT INTO Orders(order_num, order_date, cust_id)
    VALUES(20100,'2001/12/1','1000000010');
    IF @@ERROR <> 0 ROLLBACK TRANSACTION StartOrder;
    INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
    VALUES(20100, 1, 'BR01', 100, 5.49);
    IF @@ERROR <> 0 ROLLBACK TRANSACTION StartOrder;
    INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
    VALUES(20100, 2, 'BR03', 100, 10.99);
    IF @@ERROR <> 0 ROLLBACK TRANSACTION StartOrder;
    COMMIT TRANSACTION
  • Answer:
    在第一個 insert 建立一個 savepoint StartOrder, 如果之後的語法失敗, 回到 savepoint

以下的 SQL 語法中的意思是?
  • Example:

    CREATE VIEW ProductCustomers AS
    SELECT cust_name, cust_contact, prod_id
    FROM Customers, Orders, OrderItems
    WHERE Customers.cust_id = Orders.cust_id
    AND OrderItems.order_num = Orders.order_num;
  • Answer:
    建立一個 VIEW, 為一張虛擬的 table, 可藉此簡單化省略重複複雜 SQL 語法

以下的 SQL 語法中的意思是?
  • Example:

    SELECT *
    INTO CustCopy
    FROM Customers;
  • Answer:
    從 Customers table 撈出所有 record, 再 insert 到 CustCopy table

以下的 SQL 語法中的意思是?
  • Example:

    INSERT INTO Customers(cust_id,
    cust_contact,
    cust_email,
    cust_name,
    cust_address,
    cust_city,
    cust_state,
    cust_zip,
    cust_country)
    SELECT cust_id,
    cust_contact,
    cust_email,
    cust_name,
    cust_address,
    cust_city,
    cust_state,
    cust_zip,
    cust_country
    FROM CustNew;
  • Answer:
    從 CustNew table 中 select 出 record, 在 insert 進 Customers table

以下的 SQL 語法中的意思是?
  • Example:

    SELECT cust_name, cust_contact, cust_email
    FROM Customers
    WHERE cust_state IN ('IL','IN','MI')
    UNION
    SELECT cust_name, cust_contact, cust_email
    FROM Customers
    WHERE cust_name = 'Fun4All'
    ORDER BY cust_name, cust_contact;
  • Answer:
    只在最後一個 SELECT 使用 ORDER BY, 因為 UNION 會將它用來排序所有 SELECT 語句返回的結果

以下的 SQL 語法中的意思是?
  • Example:

    SELECT cust_name, cust_contact, cust_email 
    FROM Customers
    WHERE cust_state IN ('IL','IN','MI')
    UNION ALL
    SELECT cust_name, cust_contact, cust_email
    FROM Customers
    WHERE cust_name = 'Fun4All';
  • Answer:
    使用 UNION 語法同時執行兩個 query, UNION 默認取消重複的 record, ALL 表示不默認取消

以下的 SQL 語法中的意思是?
  • Example:

    SELECT cust_name, cust_contact
    FROM Customers AS C, Orders AS O, OrderItems AS OI
    WHERE C.cust_id = O.cust_id
    AND OI.order_num = O.order_num
    AND prod_id = 'RGAN01';
  • Answer:
    使用 AS 來縮短 SQL 語法

以下的 SQL 語法中的意思是?
  • Example:

    SELECT prod_name, vend_name, prod_price, quantity
    FROM OrderItems, Products, Vendors
    WHERE Products.vend_id = Vendors.vend_id
    AND OrderItems.prod_id = Products.prod_id
    AND order_num = 20007;
  • Answer:
    從三個 table 中取出指定的 column, 並指定連結

以下的 SQL 語法會取出多少行的 record?
  • Example:

    SELECT vend_name, prod_name, prod_price
    FROM Vendors, Products;
  • Answer:
    因為沒有指定關聯, 所以共會取出 Vendors 與 Products 行數的相乘

以下的 SQL 語法中的意思是?
  • Example:

    SELECT cust_name, 
    cust_state,
    (SELECT COUNT(*)
    FROM Orders
    WHERE Orders.cust_id = Customers.cust_id) AS orders
    FROM Customers
    ORDER BY cust_name;
  • Answer:
    利用子查詢取得 orders 欄位, 即在 orders table 中, cust_id 跟 Customers table 中的 cust_id 相同

以下的 SQL 語法中, 作為子查詢, 可以 select 多個 column 嗎?
  • Example:

    SELECT cust_id
    FROM Orders
    WHERE order_num IN (SELECT order_num
    FROM OrderItems
    WHERE prod_id = 'RGAN01');
  • Answer:
    不行

以下的 SQL 語法的意思是?
  • Example:

    SELECT cust_id
    FROM Orders
    WHERE order_num IN (SELECT order_num
    FROM OrderItems
    WHERE prod_id = 'RGAN01');
  • Answer:
    從子查詢得到的結果來 filter

下面的 select 子句的排列順序是?
  • Example
    SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY
  • Answer:
SQL 語法中, WHERE 跟 HAVING 差別是?

where 過濾行
having 過濾分組

SQL 語法中, count(*) 會否忽略 NULL 值?

不會

SQL 語法中, 當使用 + 把不同 column 的值串在一起, 卻發現有多餘的空白如下 example, 在 example 2 當中, 該怎麼去掉這些空白呢?
  • Example output


    -----------------------------------------------------------
    Bear Emporium (USA )
    Bears R Us (USA )
    Doll House Inc. (USA )
    Fun and Games (England )
    Furball Inc. (USA )
    Jouets et ours (France )
  • Example 2

    SELECT RTRIM(vend_name) + ' (' + 這裡是?(vend_country) + ')'
    FROM Vendors
    ORDER BY vend_name;
  • Answer

    SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')'
    FROM Vendors
    ORDER BY vend_name;
SQL 語法中, 如果要把兩個不同 column 的 value 串在一起成一個字串輸出, 如下 example, 該使用什麼符號??
  • Example

    SELECT vend_name 這裡是? ' (' 這裡是? vend_country 這裡是? ')'
    FROM Vendors
    ORDER BY vend_name;
  • Answer

    SELECT vend_name + ' (' + vend_country + ')'
    FROM Vendors
    ORDER BY vend_name;
SQL 語法中, wildcard 是否會耗費比較多的時間?

會的

SQL 語法中, 當使用 wildcard 時, 最好將語法置於開頭處或結尾處?

結尾處

SQL 語法中, 假如我要找出所有名字以 J 或 M 起頭的聯絡人, 在以下的 example 中, 該使用哪個 wildcard?
  • Example

    SELECT cust_contact
    FROM Customers
    WHERE cust_contact LIKE '這裡是?%'
    ORDER BY cust_contact;
  • Answer

    SELECT cust_contact
    FROM Customers
    WHERE cust_contact LIKE '[JM]%'
    ORDER BY cust_contact;
SQL 語法中, % 代表多字 wildcard, 如果我要單個字的 wildcard, 要使用哪個符號?

_, 是一個 word, 不是 character

SQL 語法中, 以下 example 中的語法會撈出 value 為 null 的 row 嗎?
  • Example:

    SELECT prod_name
    FROM Products
    WHERE prod_name LIKE '%';
  • Answer:
    不會

以下的 SQL 語法是什麼意思?
  • Example:

    SELECT prod_name
    FROM Products
    LIMIT 5 OFFSET 5;
  • Answer:
    抵銷 5 行, 也就是從第 6 行開始撈
    撈 5 行資料

SQL 語法中, 有區分大小寫嗎?

SQL 語法中, inner join 跟 join 差在哪?

一樣

SQL 語法中, left outer join 跟 left join 差在哪?

一樣

SQL 語法中, right outer join 跟 right join 差在哪?

一樣

SQL 語法中, join 語法的用途是?

同時撈出兩張表的資料

SQL 語法中, left join 跟 right join 語法的差別是?

left join 除了 join 兩張表有相關的資料外, 還會把左邊表的所有不相關資料也撈出來, right join 則是會把右邊表的所有不相關資料取出

以下的 MySQL example command 的意思是?
  • Example:

    show variables like 'validate_password%';
  • Answer:
    顯示密碼驗證相關訊息, 如下

    # validate_password.check_user_name	ON
    # validate_password.dictionary_file
    # validate_password.length 8
    # validate_password.mixed_case_count 1
    # validate_password.number_count 1
    # validate_password.policy MEDIUM
    # validate_password.special_char_count 1
以下的 MySQL example command 的意思是?
  • Example:

    SELECT user,authentication_string,plugin,host FROM mysql.user;
  • Answer:
    確認密碼驗證方式

以下的 MySQL example command 的意思是?
  • Example:

    ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
    FLUSH PRIVILEGES;
  • Answer:
    更改 root 密碼

以下的 MySQL example command 的意思是?
  • Example:

    set global validate_password_length=3;
  • Answer:
    修改密碼長度規則

以下的 MySQL example command 的意思是?
  • Example:

    set global validate_password_policy=0;
  • Answer:
    修改密碼驗證原則

以下的 MySQL example command 的意思是?
  • Example:

    CREATE DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  • Answer:
    建立資料庫 mydatabase, 設定 character set 為 utf8mb4, collate 為 utf8mb4_unicode_ci

以下的 MySQL example command 的意思是?
  • Example:

    mysql -uroot -pYourPassword -Bse "use test; delete from testTable;"
  • Answer:
    從 shell 不進入 MySQL client 直接下達 command

以下的 MySQL example command 的意思是?
  • Example:

    set global log_timestamps = 'system';
  • Answer:
    更改 log 的時區為系統時間

以下的 MySQL example command 的意思是?
  • Example:

    DROP DATABASE `databaseName`;
  • Answer:
    刪除資料庫




# 參考書目

手把手教你理解並建立 GCP 平衡負載 Gitlab 學習筆記

留言

Your browser is out-of-date!

Update your browser to view this website correctly. Update my browser now

×