您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 基礎操作命令大全：建庫、建表、查詢、存儲過程與權限管理

編輯：MySQL 綜合教學

MySQL 基礎操作命令大全：建庫、建表、查詢、存儲過程與權限管理

整理 MySQL 日常開發、運維最高頻的基礎命令，涵蓋資料庫/表結構管理、存儲過程、權限控制，附帶實戰範例與最佳實踐註記。

一、資料庫級操作

-- 1. 建立資料庫 (指定字元集、排序規則 - 生產環境必加)
CREATE DATABASE `my_app` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 2. 顯示所有資料庫
SHOW DATABASES;

-- 3. 切換資料庫
USE `my_app`;

-- 4. 刪除資料庫 (謹慎！不可恢復)
DROP DATABASE `my_app`;

二、資料表結構操作

-- 1. 顯示當前資料庫所有表
SHOW TABLES;

-- 2. 查看表結構 (兩種常用方式)
DESC `users`;
-- 或
SHOW COLUMNS FROM `users`;

-- 3. 查看完整建表語句 (含引擎、字元集、分區、索引定義)
SHOW CREATE TABLE `users`;

-- 4. 建立帶自增主鍵的表 (標準範例)
CREATE TABLE `users` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '主鍵ID',
    `username` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '用戶名',
    `password_hash` CHAR(64) NOT NULL DEFAULT '' COMMENT '密碼雜湊',
    `email` VARCHAR(128) NOT NULL DEFAULT '' COMMENT '郵箱',
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
    UNIQUE KEY `uk_username` (`username`),
    KEY `idx_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用戶表';

三、存儲過程管理

-- 1. 顯示所有存儲過程狀態
SHOW PROCEDURE STATUS;

-- 2. 顯示特定存儲過程建立詳情
SHOW CREATE PROCEDURE `sp_get_user`;

-- 3. 刪除存儲過程 (若存在)
DROP PROCEDURE IF EXISTS `sp_get_user`;

-- 4. 建立簡單存儲過程範例
DELIMITER //
CREATE PROCEDURE `sp_get_user`(IN p_id INT)
BEGIN
    SELECT * FROM `users` WHERE `id` = p_id;
END //
DELIMITER ;

四、索引與約束操作

-- 新增索引
ALTER TABLE `users` ADD INDEX `idx_created` (`created_at`);

-- 新增唯一索引
ALTER TABLE `users` ADD UNIQUE INDEX `uk_phone` (`phone`);

-- 刪除索引
ALTER TABLE `users` DROP INDEX `idx_created`;

-- 新增外鍵 (InnoDB)
ALTER TABLE `orders` ADD CONSTRAINT `fk_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE;

五、權限與用戶管理 (關鍵：最小權限原則)

-- 1. 建立應用專用用戶 (禁止 root 直連應用)
CREATE USER 'app_user'@'%' IDENTIFIED BY 'StrongPass123!';

-- 2. 授予最小必要權限 (僅指定庫、表、操作)
GRANT SELECT, INSERT, UPDATE, DELETE ON `my_app`.* TO 'app_user'@'%';
-- 或更細粒度
GRANT SELECT, INSERT ON `my_app`.`users` TO 'app_user'@'%';

-- 3. 刷新權限生效
FLUSH PRIVILEGES;

-- 4. 查看用戶權限
SHOW GRANTS FOR 'app_user'@'%';

-- 5. 回收權限
REVOKE DELETE ON `my_app`.* FROM 'app_user'@'%';

-- 6. 修改用戶密碼 (5.7+)
ALTER USER 'app_user'@'%' IDENTIFIED BY 'NewStrongPass456!';

-- 7. 刪除用戶
DROP USER 'app_user'@'%';

六、常用查詢與維護命令

-- 顯示資料庫版本
SELECT VERSION();

-- 顯示當前連線 ID
SELECT CONNECTION_ID();

-- 顯示支援的儲存引擎
SHOW ENGINES;

-- 顯示資料庫狀態變數 (效能調優必看)
SHOW GLOBAL STATUS LIKE 'Innodb_%';
SHOW GLOBAL VARIABLES LIKE 'innodb_%';

-- 檢查表錯誤
CHECK TABLE `users`;

-- 優化表 (整理碎片、更新統計資訊)
OPTIMIZE TABLE `users`;

-- 顯示進程列表 (排查長查詢、鎖等待)
SHOW FULL PROCESSLIST;

-- 終止進程
KILL 12345;

七、資料操作基礎範例 (CRUD)

-- 插入 (指定欄位，防止結構變更報錯)
INSERT INTO `users` (`username`, `email`, `password_hash`) VALUES ('john', 'john@example.com', 'hash...');

-- 查詢 (避免 SELECT *，指定欄位)
SELECT `id`, `username`, `email` FROM `users` WHERE `id` = 1;

-- 更新 (務必帶 WHERE，防誤更新全表)
UPDATE `users` SET `email` = 'new@example.com' WHERE `id` = 1;

-- 刪除 (務必帶 WHERE，軟刪除優於硬刪除)
DELETE FROM `users` WHERE `id` = 1;
-- 軟刪除建議：ALTER TABLE `users` ADD `deleted_at` TIMESTAMP NULL; UPDATE ... SET `deleted_at`=NOW();

八、最佳實踐備忘錄

命名規範：資料庫/表/欄位全小寫、底線分隔、有意義名稱、加反引號 `。
字元集統一 utf8mb4：支援 Emoji、完整 Unicode，COLLATE utf8mb4_unicode_ci 或 utf8mb4_0900_ai_ci (8.0+)。
主鍵用 BIGINT UNSIGNED AUTO_INCREMENT：預留空間、避免耗盡。
時間戳用 TIMESTAMP / DATETIME(3)：統一 UTC 存儲，應用層處理時區顯示。
軟刪除優於硬刪除：加 deleted_at / is_deleted，避免資料遺失與外鍵級聯風險。
禁止 SELECT *：指定欄位，減少網路傳輸、利用覆蓋索引、防結構變更衝擊。
備份先行：任何 DDL/DML 變更生產庫前，必先備份或快照。