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

本文會列出MySQL/MariaDB在建立、修改及刪除資料表時，會用到的一些語法及範例，包含建立資料表語法、建立資料表欄位語法、設定鍵值(Key)、修改資料表語法、刪除資料表時語法、刪除之前檢查資料表是否存在。

1 建立資料表

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

2 資料表欄位

        
            -- 建立一個名為 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： 日期型別欄位。

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

3 設定鍵值(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的各種考量，可以參考以下連結。

4 修改資料表

        
            -- 在 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 `欄位名稱`;

5 刪除資料表

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

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

Facebook 留言