[筆記] MySQL 基礎

基本介紹

什麼是 Database

• DBMS 為向 Database 存取的訪問接口
• 通常稱 DBMS + Database 為 Database，EX : MySQL、Oracle、MongoDB…等等

SQL 與 NoSQL

• 區別在於有沒有使用 SQL 語言，NoSQL 的資料儲存可以不需要固定的表格模式
• SQL : MySQL、Oracle…等等
• NoSQL : MongoDB…等等

安裝

Window :

• 建議用 Installer 下載安裝 : MySQL Installer 5.7.26

  中間跳過的步驟默認就好

  • 建議選擇 Developer Default
  • 這步檢查依賴，Execute 就好
  • 這邊設定密碼
  • 這邊輸入剛剛設定的密碼，點擊 Check
  • 之後在這裡進行操作，輸入剛剛設定的密碼

P.S.後來在另一台電腦一直無法安裝，有閃退的情況，後來安裝 8.0.16 就正常了

macOS :

• 下載地址 : MySQL 官網 - MySQL Community Server 5.7

Database 與 Table

• Database 是由很多 Table 組成的
• Table 存放資料，且是有結構的

Database 基本操作

• show databases; : 顯示目前有哪些 Database

• create database <name>; : 創建 Database

• drop database <name>; : 刪除 Database

• use <database name>; : 切換 Database

• select database(); : 查看目前正在使用的是哪個 Database

• delimiter <結束符號> : 預設結束符號是 ;，這行命令可以做修改 ( 不建議 )

• show warnings; : 查看錯誤訊息

  SQL 大小寫都可運行，建議關鍵字大寫

Table

• column : 行，相同的資料類型
• row : 列，一筆一筆的數據

Table Data Type

有三種大分類 : Numeric、String、Date

• 資料類型官方文檔

Table 的創建

CREATE TABLE person
(
    name VARCHAR(20),
    phone VARCHAR(20),
    age INT
);

Table 基本操作

• show tables : 顯示目前有哪些 Table

• show columns from <table name> : 顯示 Table 的每個 columns

• desc <table name> : 描述 Table 的資訊 ( 顯示跟上面的命令一樣，可以用這條命令就好 )

• drop table <table name> : 刪除 Table

Data Insert

如何插入資料

insert into <table_name>(<column1_name>, <column2_name>) 
values(<column1_data>, <column2_data>),
      (<column1_data>, <column2_data>);

查看資料

SELECT * from <table_name>;

• 查看 Table 裡的所有資料

SELECT <column_name> from <table_name>;

• 查看 Table 裡某個 column 的所有資料

創建特殊要求的 Table

• NULL 和 NOT NULL :

  • NULL 默認為 YES
  • 創建時 <column name> <data type> NOT NULL，NULL 就會變 NO
  • 插入資料時，NULL 為 NO 的資料不能為空

• Default Value :

  • Default 默認為 NULL
  • 創建時 <column name> DEFAULT <value>

• PRIMARY KEY :

  • 唯一性，不能重複，不可 NULL
  • 創建時 <column name> <data type> PRIMARY KEY
  • 如果需要兩個以上 PRIMARY KEY，可以設置聯合 PRIMARY KEY
  • 聯合 PRIMARY KEY : PRIMARY KEY(<column1 name>, <column2 name>)

• UNIQUE :

  • 唯一性，不能重複，可 NULL
  • 創建時 <column name> <data type> UNIQUE

• AUTO_INCREMENT :

  • 設置時，必須同時是個 Key
  • 創建時 <column name> <data type> <PRIMARY KEY or UNIQUE> AUTO_INCREMENT ，常用在 ID 之類，不填值時會累加

CRUD 基本的增刪改查

• Create
• Read
• Update
• Delete

使用 SQL 文件 Create

• 創建一個使用 SQL 語法的文件 test.sql
• source <SQL 文件路徑 EX : /Users/user/SQL-training/test.sql>; 注意分隔要 /

CREATE TABLE IF NOT EXISTS person
(
    name VARCHAR(20),
    phone VARCHAR(20),
    age INT
);

• IF NOT EXISTS 可以在創建 Table 時，查看 Table 是否存在，不存在就創建，存在則否

Read

SELECT <column_name> as <別名> from <table_name>;

• column 的名稱會以別名顯示

SELECT * from <table_name> WHERE <column1_name> = "想找到的資料" <AND 或 OR> <NOT> <column2_name> = "想找到的資料";

• WHERE 像是 if，可以過濾

Update

UPDATE <table_name> set <column_name> = "想改成這個資料" WHERE ...;

• 通常會加上 WHERE 來過濾要改的資料

• 較安全步驟為

  1. 先 SELECT + WHERE 找出想改的資料
  2. 再來 UPDATE + 剛剛的 WHERE 來更動

Delete

DELETE from <table_name> WHERE ...;

• 跟 UPDATE 一樣通常加上 WHERE 過濾
• 跟 UPDATE 一樣，先 SELECT + WHERE 較安全

SQL 字串相關處理方法

拼接 CONCAT

• CONCAT :

  ​​​​SELECT CONCAT(<column1_name>, <column2_name>) as <別名> from <table_name>;
  

  • 輸出會是 <column1_name><column2_name>

• CONCAT_WS :

  ​​​​SELECT CONCAT_WS("<間格符>", <column1_name>, <column2_name>, <column3_name>) as <別名> from <table_name>;
  

  • 輸出會是 <column1_name><間格符><column2_name><間格符><column2_name>

剪輯 SUBSTRING、REPLACE

• SUBSTRING :

  • SUBSTRING("Hello World", 7) : 從前面數，輸出為 World
  • SUBSTRING("Hello World", -3) : 從後面數，輸出為 rld
  • SUBSTRING("Hello World", 1, 4) : 第 n 個到第 m 個，輸出為 Hell

• REPLACE :

  • REPLACE("Hello World", "World", "MySQL") : 替換，輸出為 Hello MySQL

反轉 REVERSE

• REVERSE("Hello World") : 反轉，輸出為 dlroW olleH

長度 CHAR_LENGTH

• CHAR_LENGTH("Hello World") : 字串長度，輸出為 11

大小寫 LOWER、UPPER

• LOWER("Hello World") : 轉小寫
• UPPER("Hello World") : 轉大寫
• 數字符號都不會變

SELECT 進階

按規則排序 ORDER BY

SELECT * from <table_name> ORDER BY <column_name>;

• 會按照 column_name 進行升序排序
• 如要進行降序排序 : ORDER BY <column_name> desc
• 字母、日期也可排序

SELECT <column1_name>, <column2_name>, <column3_name> from <table_name> ORDER BY 3, 1;

• 按照 column3_name 與 column1_name 進行排序
• 如果 column3_name 相同才按照 column1_name 排序

限制搜尋資料顯示的資料數目 LIMIT

SELECT * from <table_name> ORDER BY <column_name> LIMIT 3;

• 顯示排序後前三個
• LIMIT 2, 4 : 顯示從 2 開始後 4 筆資料
• LIMIT 2, 18446744073709551615 : 顯示從 2 開始後的所有資料

模糊搜尋 LIKE

SELECT * from <table_name> WHERE <column_name> LIKE "%i%";

• 顯示包含 i 的
• % 代表任意字符
• _ 代表一個任意字符
• 如果要找的資料包含 % 或 _，可以寫成 \% 和 \_
• SQL 裡搜尋是不區分大小寫的，如要區分需要額外設置

資料聚合處理

計數 COUNT

SELECT COUNT(*) from <table_name>;

• 計算資料數量

統計唯一值 DISTINCT

SELECT DISTINCT <column_name> from <table_name>;

• 將 column_name 內重複的資料去除後顯示出來

資料整合 GROUP BY

SELECT COUNT(<column_name>) from <table_name> GROUP BY <想合併的 column_name>;

• 將 想合併的 column_name 分組後算出每組 column_name 的資料數量

最大值與最小值 MAX、MIN

• MAX(<column_name>) : column_name 的最大值
• MIN(<column_name>) : column_name 的最小值

和與平均值 SUM、AVG

• SUM(<column_name>) : column_name 的和
• AVG(<column_name>) : column_name 的平均值

HAVING

WHERE 只能對原始資料過濾，假如要對 GROUP BY 之後的資料過濾就要用到 HAVING

SELECT COUNT(<column_name>) from <table_name> GROUP BY <想合併的 column_name> HAVING <column1_name> = "想找到的資料";

Data Type 詳細介紹

數值類型

• 整數 ( Integer ) - 準確的

  • 標準類型 :

    • INTEGER ( INT )
    • SMALLINT

  • 擴充類型 :

    • TINYINT
    • MEDIUMINT
    • BIGINT

  • 後面加 UNSIGNED 表示為無負號

• 定點數 ( Fixed-Point ) - 準確的

  • DECIMAL ( 用於金錢 )

    • DECIMAL(m, d) :

      • 總共 m 位，小數點後 d 位
      • 小數點後不足補 0，多的四捨五入
      • 可負數，不算位數

  • NUMERIC : 跟 DECIMAL 是一樣的

• 浮點數 ( Floating-Point ) - 不準確的

  • FLOAT

    • FLOAT(m, d) :

      • 總共 m 位，小數點後 d 位

  • DOUBLE : 用法跟 FLOAT 是一樣的，差別在於 DOUBLE 可存字節較多

• 位 ( Bit-Value )

  • BIT

    • BIT(m) :

      • 總共 m 位
      • m 最小 1，最大 64，默認為 1

    • 插入方法 :

      ​​​​​​​​​​​​insert into <table_name> values(b'1010')
      

      • 純數字為十進位
      • b'1010' 為二進位

    • 顯示方法 :

      ​​​​​​​​​​​​SELECT <column_name>+0 from <table_name>
      

      • <column_name>+0 : 顯示十進位數值
      • bin(<column_name>+0) : 顯示二進位數值
      • oct(<column_name>+0) : 顯示八進位數值
      • hex(<column_name>+0) : 顯示十六進位數值

• 數值類型的官方文件

時間類型

• DATE

  • 日期 ( YYYY-MM-DD )
  • 範圍 : 1000-01-01 ~ 9999-12-31
  • 可插入 INT 型 20181001，也可不需 - "20181001"，但不可缺少年月日

• TIME

  • 時間 ( HH:MM:SS )

  • 範圍 : -838:59:59 ~ 838:59:59，小時範圍那麼大的原因是，也可以表示時間差

  • 插入規則同 DATE，但可缺少時分秒

    • 12:10 為 12:10:00
    • 1210 為 00:12:10
    • 10 為 00:00:10

• YEAR

  • 年，為四個字符

  • 範圍 : 1901 ~ 2155

  • 插入兩位 INT :

    • 1 ~ 69 為 2001 ~ 2069
    • 70 ~ 99 為 1970 ~ 1999
    • 0 為 0000

  • 插入兩位 STRING :

    • "0" ~ "69" 為 2000 ~ 2069
    • "70" ~ "99" 為 1970 ~ 1999

• DATETIME

  • 日期 + 時間 ( YYYY-MM-DD HH:MM:SS )
  • 範圍 : 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59

• TIMESTAMP

  • 範圍 : "1970-01-01 00:00:01" UTC ~ "2038-01-19 03:14:07" UTC
  • 會隨著 timezone 改變
  • 默認當前時間
  • SELECT NOW() : 查看系統當前時間
  • 以下兩條程式碼是相同功能的
  ​​​​CREATE TABLE <table_name>(<column_name> TIMESTAMP)
  ​​​​CREATE TABLE <table_name>(<column_name> TIMESTAMP DEFAULT NOW() ON UPDATE NOW())
  

• timezone

  • show variables LIKE "%time_zone%" : 查看包含 time_zone 字串的變量
  • SET time_zone = "-12:00" : 更改 time_zone，根據 UTC 時間減 12 小時
  • SET time_zone = "system" : 更改 time_zone 為系統當前時區
  • time_zone 也可用國家名稱下去設置

• 時間類型的官方文件

• 時間與日期函式

字符串類型

• CHAR 與 VARCHAR

  • 後面帶的數字代表字串長度

  • CHAR : 適用於固定長度

    • 較不省空間

  • VARCHAR : 適用於不固定長度

    • 更新效率較慢，不如 CHAR
    • 平常會多 1 bytes 儲存字串長度

• BINARY 與 VARBINARY

  • 後面帶的數字代表 bytes 長度

  • BINARY : 適用於固定長度

    • 較不省空間
    • 插入的字串不足會補上 \0

  • VARBINARY : 適用於不固定長度

    • 更新效率較慢，不如 BINARY

• BLOB 與 TEXT

  • 適用於儲存文章
  • BLOB : 較大的 BINARY
  • TEXT : 較大的 CHAR
  • SET max_sort_length = n : 設置此變量後，排序時只對前 n 個字符排序

• ENUM

  • 給予特定字串選擇 ENUM("F", "M")
  • 類似單選

• SET

  • 跟 ENUM 很像，也是一個 list
  • 但 SET 可以選擇一個集合
  • 類似複選

• 字符串類型的官方文件

對已經定義好的 Table ，更改 Column 的資料類型

ALTER TABLE <table_name> MODIFY <column_name> <想更改成的資料類型>

• 禁止 varchar 改成 int
• 修改資料類型前一定要備份

SQL 邏輯操作符

EQUAL 與 NOT EQUAL

• EQUAL : <column_name> = 3000
• NOT EQUAL : <column_name> != 3000 或 NOT <column_name> = 3000

LIKE 與 NOT LIKE

• 模糊搜尋

GREATER THAN 與 LESS THAN

• GREATER THAN : <column_name> > 3000
• LESS THAN : <column_name> < 3000
• 包含 : >=、<=

AND 與 OR

• AND : 條件都要滿足
• OR : 條件只要滿足一項

BETWEEN

• <column_name> BETWEEN 1000 and 3000

IN 與 NOT IN

• 搜尋 list 裡面有的值
• IN : <column_name> IN (5000, 6000, 7000, 8000)
• NOT IN : <column_name> NOT IN (5000, 6000, 7000, 8000)

CASE STATEMENT

SELECT <column_name>, 
    case
        when <column_name> >= 7000 then "high"
        else "low"
    end as <別名>
from <table_name>;

MySQL 內建函式

數值處理函式

• CEIL
• FLOOR
• DIV : 整數除法
• MOD
• POWER
• ROUND :四捨五入

日期時間函式

• NOW
• CURDATE : 當前日期
• CURTIME : 當前時間
• DATE_FORMAT
• DATE_ADD : 時間加減
• DATEDIFF : 求時間差

字串處理函式

• CONCAT
• CONCAT_WS
• LOWER
• UPPER
• LEFT : 擷取頭
• RIGHT : 擷取尾
• LENGTH
• LTRIM : 去頭
• RTRIM : 去尾
• TRIM : 去頭去尾
• REPLACE
• SUBSTRING

信息函式

• CONNECTION_ID : 當前與 MySQL 連線的 ID
• DATABASE : 當前使用的 database
• LAST_INSERT_ID : 最後插入資料的資料 ID
• USER : 當前用戶
• VERSION

聚合函式

• AVG
• COUNT
• MAX
• MIN
• SUM

加密函式

• MD5 : 用來存取密碼
• PASSSWORD : 通常用於修改 MySQL 密碼 SET PASSSWORD = PASSSWORD('ABC123')

關聯 ( 一對多 )

通過 ID 關聯兩個 Table

CREATE TABLE customers(
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    email VARCHAR(100) 
);

INSERT INTO customers(first_name, last_name, email) VALUES
    ('Robin', 'Jackman', '[e-mail]'),
    ('Taylor', 'Edward', '[e-mail]'),
    ('Vivian', 'Dickens', '[e-mail]'),
    ('Harley', 'Gilbert', '[e-mail]');

CREATE TABLE orders(
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_date DATE,
    amount DECIMAL(8,2),
    customer_id INT
);

INSERT INTO orders(order_date, amount, customer_id) VALUES
    ('2001-10-12', 99.12, 1),
    ('2001-09-21', 110.99, 2),
    ('2001-10-13', 12.19, 1),
    ('2001-11-29', 88.09, 3),
    ('2001-11-11', 205.01, 4);

• 這邊有兩張 Table，客戶訊息與訂單訊息，訂單訊息以 customer_id 與客戶訊息的 id 做關聯

SELECT * from orders WHERE customer_id = (SELECT id from customers WHERE email = "[e-mail]");

• 查找 Robin Jackman 的訂單

使用 FOREIGN KEY 約束關聯字段

FOREIGN KEY (customer_id) REFERRENCES customers(id)

• 在創建 Table 時，加上這段可以約束訂單訊息的 customer_id 與客戶訊息的 id

關聯表的操作

使用 JOIN 合併關聯表

• INNER JOIN : 顯示出有關聯的所有資料 ( 沒關聯的不會顯示 )

SELECT * from customers INNER JOIN orders WHERE customers.id = orders.customer_id;

WHERE 可改成 ON

• LEFT JOIN : 顯示出所有 SELECT 的 Table 以及他們的關聯資料

SELECT * from customers LEFT JOIN orders WHERE customers.id = orders.customer_id;

IFNULL(SUM(amount), 0) : 如果值為 NULL 則顯示 0

• RIGHT JOIN : 顯示出所有 JOIN 的 Table 以及他們的關聯資料

SELECT * from customers RIGHT JOIN orders WHERE customers.id = orders.customer_id;

• ON DELETE : 在刪除被參照的 Table 內資料時，也一起刪除其他 Table 參照此 Table 的相關資料

ON DELETE CASCADE

• 在創建 Table 時，除了 FOREIGN KEY，另外加上這段

關聯 ( 多對多 )

兩張多對多的 Table 會有一張 Table 把他們關聯起來

• books 與 reviewer 為多對多

MySQL 存入中文，編碼問題

Window

• 指令同下
• 但因為 MySQL 5.7 Command Line Client 不支援中文，所以必須以其他方式插入中文資料
• MySQL Workbench、VS code 都可以

Mac

CREATE database <database_name> DEFAULT CHARSET = utf8 COLLATE = utf8_general_ci;

• 創建資料庫時修改默認編碼

ALTER database <database_name> CHARSET = utf8 COLLATE = utf8_general_ci;

• 已存在的 database 修改默認編碼

ALTER table <table_name> CONVERT TO CHARACTER SET utf8;

• 若是修改了已存在的 database，其本身已存在 table，必須一併更改默認編碼

tags: 筆記 程式語言 SQL 資料庫 MySQL