MySQL / MariaDB 建立、修改、刪除資料表 SQL 語法教學與範例

建立資料表

            
                -- 建立資料表語法格式
                CREATE TABLE `表格名` (
                    `欄位名稱` `欄位設定`,
                    `欄位名稱` `欄位設定`,
                    `欄位名稱` `欄位設定`,
                    ......
                ) `資料表選項設定`;
            
        

資料表欄位

            
                -- 建立一個名為 test 資料表
                CREATE TABLE `test` (
                  `A` tinyint(3) unsigned zerofill NOT NULL AUTO_INCREMENT COMMENT '流水號',
                  `B` tinyint(3) NOT NULL COMMENT 'TINYINT 型別',
                  `C` smallint(5) NOT NULL COMMENT 'SMALLINT 型別',
                  `D` mediumint(8) NOT NULL COMMENT 'MEDIUMINT 型別',
                  `E` int(10) NOT NULL COMMENT 'INTEGER 型別',
                  `F` bigint(20) NOT NULL COMMENT 'BIGINT 型別',
                  `G` double(5,2) NOT NULL COMMENT 'DOUBLE 型別',
                  `H` decimal(5,2) NOT NULL COMMENT 'DECIMAL 型別',
                  `I` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'VARCHAR 型別',
                  `J` text COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'TEXT 型別',
                  `K` longtext COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'LONGTEXT 型別',
                  `L` enum('y','n') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'y' COMMENT 'ENUM 型別',
                  `M` date NOT NULL COMMENT 'DATE',
                  `N` time NOT NULL COMMENT 'TIME',
                  `O` datetime NOT NULL COMMENT 'DATETIME',
                  PRIMARY KEY (`A`) USING BTREE
                ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='商品';
            
        

• 行 2：指定資料表名稱為 test。
• 行 3~8：整數型別欄位，整數型別可以額外設定 unsigned、zerofill 及 AUTO_INCREMENT 等參數。
  unsigned：表示欄位只能填入 0 及正整數。
  zerofill：如果欄位值尚未達到最大支援長度，則前面補 0 至最大支援長度。
  AUTO_INCREMENT：表示欄位會自動遞增，新增資料時，無需指定此欄位的值。
  
• 行 9~10：浮點數型別欄位，浮點數型別也可以額外設定 unsigned 及 zerofill 參數。
• 行 11~13：字串型別欄位，字串型別可指定欄位定序。
• 行 14：ENUM型別欄位，需要設定一組欄位值的清單，可指定欄位定序。
• 行 15~17：日期型別欄位。

詳細資料型別說明，可以參考以下連結。

• 【資料庫】MariaDB 資料型別

設定鍵值 (Key)

            
                -- 建立一個名為 type 資料表
                CREATE TABLE `type` (
                    `sn` smallint(3) unsigned NOT NULL AUTO_INCREMENT COMMENT '流水號',
                    `number` varchar(5) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '編號',
                    `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '名稱',
                    PRIMARY KEY (`sn`) USING BTREE,
                    UNIQUE KEY `unique_key` (`number`) USING BTREE
                ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='分類';

                -- 建立一個名為 product 資料表
                CREATE TABLE `product` (
                    `sn` mediumint(8) unsigned NOT NULL AUTO_INCREMENT COMMENT '流水號',
                    `guid` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'GUID',
                    `type_number` varchar(5) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '分類編號',
                    `name` text COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '品名',
                    `price` int(10) unsigned NOT NULL COMMENT '單價(元)',
                    `description` text COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '說明',
                    PRIMARY KEY (`sn`) USING BTREE,
                    UNIQUE KEY `unique_key` (`guid`) USING BTREE,
                    CONSTRAINT `product_ibfk_1` FOREIGN KEY (`type_number`) REFERENCES `type` (`number`)
                ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='商品';
            
        

• 行 6、行 18：設定 Primary Key (PK)，一個資料表只能有一個 PK，但 PK 可以由多個欄位組成。
• 行 7、行 19：設定 Unuque Key，一個資料表可以有多個 UNIQUE KEY。
• 行 20：設定 Foreign Key (FK)，一個資料表可以有多個 FK，FK 必須關聯到另外一張表的某個欄位。

設計 Primary key 的各種考量，可以參考以下連結。

• 【資料庫】Primary key 設計的考量

修改資料表

            
                -- 在 test 資料表內新增欄位
                ALTER TABLE `test` ADD COLUMN `欄位名稱` `欄位設定`;

                -- 在 test 資料表的A欄位後，插入新欄位
                ALTER TABLE `test` ADD COLUMN `欄位名稱` `欄位設定` AFTER `A`;

                -- 在 test 資料表內修改欄位名稱及資料形態
                ALTER TABLE `test` CHANGE COLUMN `舊欄位名稱` `新欄位名稱` `欄位設定`;

                -- 移除 test 資料表中的 Primary Key
                ALTER TABLE  DROP PRIMARY KEY;

                -- 移除 test 資料表中的 Index
                ALTER TABLE  DROP INDEX `欄位名稱`;

                -- 移除 test 資料表中的欄位
                ALTER TABLE `test` DROP COLUMN `欄位名稱`;
            
        

刪除資料表

            
                -- 刪除名為 test 資料表
                DROP TABLE `test`;

                -- 刪除資料表之前，先檢查資料表是否存在，如果存在才刪除
                DROP TABLE IF NOT EXISTS `test`;