iT邦幫忙

2021 iThome 鐵人賽

DAY 20
1
Software Development

MYSQL-相關實務操作學習紀錄系列 第 20

Day.20 從零開始 - 實務需求學SQL_1

今天的主題來透過應用實例複習常用SQL語法,普通的解釋相對無聊~所以我們邊舉例邊看過程中可能遇到的問題。

  • 情境模擬

需求: 設計一個投資遊戲活動管控介面。

內容: 活動的(新增/刪除/查詢/更新)。 ( 這邊不會提到程式API設計!!會偏題/images/emoticon/emoticon25.gif


  • 資料表設計

接到需求時,想一下可能需要用到什麼欄位?

Example. 針對此需求會用到以下欄位-

活動ID , 活動名稱 , 活動語言 , 更新時間 , 建立時間 , 活動開始時間 , 活動結束時間 , 遊戲名稱 , 遊戲玩法 , 活動狀態 , 平台ID ...。

  • 新增資料庫
    • active
CREATE DATABASE active;
  • 新增資料表:
    • act(活動狀態)
    • act_game(遊戲項目內容)
    • act_name(活動內容)

(補充)

這邊補充一下新手建表時欄位型別上,一開始會遇到的問題~/images/emoticon/emoticon33.gif

欄位宣告時 INT(11) / DECIMAL(3,2) / VARCHAR(10) 後面帶的數字會影響長度還是?

  1. VARCHAR(10) => 欄位值可以儲存10個字符。
  2. DECIMAL(3,2) => 欄位總共顯示3位數(含小數點後2位)。ex.DECIMAL(3,2)範圍: -9.99~9.99
  3. 這邊以INT型態解釋差異=> INT(11) vs. INT(20) vs. INT(20) UNSIGNED ZEROFILL
類型 字節 最小值(Signed) 最大值(Signed) 最小值(Unsigned) 最大值(Unsigned)
INT 4 -2147483648 2147483647 0 4294967295
  1. 欄位名稱 int(11)
  2. 欄位名稱 int(11) UNSIGNED ZEROFILL。
    • UNSIGNED -> 避免存入負的數值(對於浮點數型態而言只是避免負值),而且對整數型態來說還可以儲存更大的正整數值。ex. INT(11) UNSIGNED (0~4294967295)
    • ZEROFILL -> 表示如果存儲的數字少於N個字符,將在數字左側補零。
      https://ithelp.ithome.com.tw/upload/images/20210914/20130880Gj40nzDNKw.png

重點整理:

  • 像我們一般時的宣告ex. INT(11), INT(20) 不會影響使用上的值範圍!!
    • INT(11) & INT(20) 所代表的欄位儲存最(大/小)範圍值皆為(-2147483648 ~ 2147483647)。
  • 真的會影響是當你加上UNSIGNED ZEROFILL時才有作用! 不僅範圍值會改變,還會有補0作用。

回到建立資料表部分,我們知道資料表上大概要有哪些欄位了那重點來了primary key, index 如何設定 ?

在設計資料表的過程中索引的設計會跟查詢效率有直接的關係,隨著數據量的增加一條查詢SQL語句有無吃到index對執行上的效率差異是很明顯的~

注意: Primary key 在整張表的紀錄中一定是唯一的,不可以重複!!

  • 新增資料表
    • act (紀錄活動狀態)

我們知道clustered index決定我們數據庫中的數據分佈,數據會依照clustered index key值排序,所以這邊選擇自增欄位的唯一性當做我們的主索引鍵(PK)。 ps. 不懂為何選擇自增欄位更好!? 請參考前2天文章/images/emoticon/emoticon31.gif

CREATE TABLE `act` (
 `actives_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '活動ID',
 `status` tinyint(4) NOT NULL COMMENT '活動狀態',
 `start_time` int(11) unsigned NOT NULL COMMENT '活動開始時間',
 `end_time` int(11) unsigned NOT NULL COMMENT '活動結束時間',
 `platform_id` bigint(20) NOT NULL COMMENT '平台ID',
 `updated_at` int(11) unsigned NOT NULL COMMENT '更新時間',
 `created_at` int(11) unsigned NOT NULL COMMENT '建立時間',
 PRIMARY KEY (`actives_id`)
) 

(補充)

  • 複合索引

一般來說我們使用一個欄位作為普通索引,而兩個欄位(含)以上所組成的索引稱為複合索引。

  • 複合索引採最左匹配原則
  1. 欄位順序會關係到索引是否能用上,在查詢條件中索引最左邊的列被匹配到,後面的列才可能繼續匹配。

  2. 假設針對a,b,c欄位建立了複合索引 index(a,b,c) 相當於有index(a)和(a,b)和(a,b,c)會吃到該組索引。

  3. 組合上使用頻率高最具識別性的欄位放置最左,提升索引使用率。

Example. 針對act表建立一個平台&狀態的複合索引~

mysql> ALTER TABLE act ADD INDEX platform_id_status(platform_id,status);

使用查詢語法分析可以看到:

第一句SQL 和 第二句SQL: 符合最左匹配原則,皆吃得到該index。

第三句SQL: 因為條件只有status,根據最左匹配原則platform_id沒被匹配到,所以吃不到該組index。

https://ithelp.ithome.com.tw/upload/images/20210925/20130880GAWUcu9wkY.png


  • 新增資料表
    • act_game (遊戲項目內容)
    • act_name (活動內容)

在act_game這張資料表主要查詢投資活動的遊戲玩法支援,一個活動可以包含很多款遊戲,而同一個活動下是不會有相同遊戲的。所以我們可以選擇使用(actives_id,game_name)的組合唯一性來作為本表primary key,幫助我們快速定位遊戲提供了哪些玩法。

CREATE TABLE `act_game` (
  `actives_id` bigint(20) NOT NULL COMMENT '活動ID',
  `game_name` varchar(40) NOT NULL COMMENT '遊戲名稱',
  `support_rules` text NOT NULL COMMENT '提供玩法',
  `type` tinyint(4) NOT NULL COMMENT '投資類別',
  `created_at` int(11) unsigned NOT NULL COMMENT '建立時間',
  PRIMARY KEY (`actives_id`,`game_name`)
) 

在act_name這張資料表紀錄了活動ID支援各種不同語言的翻譯,所以會有以下狀況(同id多個對應語言),所以選擇使用(actives_id,language)的組合唯一性來作為本表primary key

actives_id actives_name language
1 投資加碼回饋 chinese
1 Investment plus rewards english
1 投資と報酬 japanese
... ... ...
CREATE TABLE `act_name` (
  `actives_id` bigint(20) NOT NULL COMMENT '活動ID',
  `actives_name` varchar(40) NOT NULL COMMENT '活動名稱',
  `language` varchar(40) NOT NULL COMMENT '語言',
  `created_at` int(11) unsigned NOT NULL COMMENT '建立時間',
  PRIMARY KEY (`actives_id`,`language`)
) 
刪除表資料(全部):

mysql> DELETE FROM 表名稱;

mysql> TRUNCATE TABLE 表名稱;


上一篇
Day.19 認識索引 - 二級索引 (Secondary Index)
下一篇
Day.21 從零開始 - 實務需求學SQL_2
系列文
MYSQL-相關實務操作學習紀錄30

尚未有邦友留言

立即登入留言