MySQL 作為最流行的開源關係型數據庫,掌握核心 SQL 語法是開發與運維的基本功。本文系統整理用戶管理、庫表操作、數據增刪改查、備份還原、進階查詢技巧,附帶執行示例,適合作為速查手冊。
# 連接(本地/遠程)
mysql -h 127.0.0.1 -u root -p
# 斷開
EXIT; 或 \q
CREATE USER 'dev'@'%' IDENTIFIED BY 'ComplexPass#123';
-- % 允許任意主機,localhost 僅本地
-- 單庫單表權限
GRANT SELECT, INSERT, UPDATE ON shop.orders TO 'dev'@'%';
-- 全庫全表權限(慎用)
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' WITH GRANT OPTION;
-- 回收權限
REVOKE INSERT ON shop.orders FROM 'dev'@'%';
-- 刷新生效
FLUSH PRIVILEGES;
ALTER USER 'dev'@'%' IDENTIFIED BY 'NewPass#456';
DROP USER 'dev'@'%';
SHOW DATABASES;
CREATE DATABASE `shop` CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE `shop`;
DROP DATABASE `shop_old`;
SHOW TABLES;
DESC `orders`; -- 查看結構
SHOW CREATE TABLE `orders`; -- 獲取建表語句
CREATE TABLE `orders` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
`user_id` INT UNSIGNED NOT NULL COMMENT '用戶ID',
`amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '金額',
`status` TINYINT NOT NULL DEFAULT 0 COMMENT '0待支付 1已支付 2已取消',
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
INDEX `idx_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='訂單表';
ALTER TABLE `orders` ADD COLUMN `pay_time` DATETIME NULL COMMENT '支付時間';
ALTER TABLE `orders` MODIFY `amount` DECIMAL(12,2) NOT NULL;
ALTER TABLE `orders` DROP COLUMN `pay_time`;
DROP TABLE `orders`;
INSERT INTO `orders` (`user_id`, `amount`, `status`) VALUES (1001, 99.50, 0);
INSERT INTO `orders` (`user_id`, `amount`) SELECT `user_id`, `total` FROM `cart` WHERE `user_id`=1001;
UPDATE `orders` SET `status`=1, `pay_time`=NOW() WHERE `id`=10001;
DELETE FROM `orders` WHERE `status`=2 AND `created_at` < '2023-01-01';
TRUNCATE TABLE `orders`; -- 快速清空,不可回滾,重置自增ID
-- 唯一鍵衝突時更新
INSERT INTO `users` (`uid`, `name`) VALUES (1, 'Alice')
ON DUPLICATE KEY UPDATE `name`=VALUES(`name`);
-- 不存在才插入
INSERT INTO `users` (`uid`, `name`) SELECT 1, 'Alice' FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM `users` WHERE `uid`=1);
SELECT * FROM `orders` WHERE `amount` > 100 AND `status`=1;
SELECT * FROM `users` WHERE `name` LIKE '張%';
SELECT * FROM `logs` WHERE `created_at` BETWEEN '2024-01-01' AND '2024-01-31';
SELECT * FROM `products` ORDER BY `price` DESC, `id` ASC LIMIT 20;
SELECT `user_id`, COUNT(*) AS `cnt`, SUM(`amount`) AS `total`
FROM `orders` WHERE `status`=1
GROUP BY `user_id` HAVING `total` > 1000
ORDER BY `total` DESC LIMIT 10;
-- 內連接
SELECT o.`id`, u.`name`, o.`amount`
FROM `orders` o
JOIN `users` u ON o.`user_id` = u.`id`
WHERE o.`status`=1;
-- 左連接(保留左表所有行)
SELECT u.`name`, o.`id`
FROM `users` u
LEFT JOIN `orders` o ON u.`id` = o.`user_id`;
WITH `high_value_users` AS (
SELECT `user_id` FROM `orders` WHERE `status`=1 GROUP BY `user_id` HAVING SUM(`amount`) > 5000
)
SELECT * FROM `users` WHERE `id` IN (SELECT `user_id` FROM `high_value_users`);
-- 避免 LIMIT 1000000, 20
-- 方案:記住上一頁最大 ID
SELECT * FROM `orders` WHERE `id` > 1000000 ORDER BY `id` LIMIT 20;
# 全庫備份(含結構+數據)
mysqldump -uroot -p --single-transaction --master-data=2 --routines --triggers --events --all-databases > full_$(date +%F).sql
# 單庫單表
mysqldump -uroot -p shop orders order_items > shop_orders_$(date +%F).sql
mysql -uroot -p < full_2024-01-15.sql
# 或進入 mysql 後
SOURCE /path/to/backup.sql;
使用 Percona XtraBackup 實現熱備份、增量備份、流式傳輸,不鎖表、恢復快。參考文章 180217、180226。
slow_query_log=1,long_query_time=1,定期分析innodb_buffer_pool_size 設為物理內存 60-70%熟練掌握上述核心語法,覆蓋 90% 日常開發運維場景。建議建立個人 Snippets 庫,結合 EXPLAIN、SHOW PROFILE、performance_schema 深入分析疑難 SQL,持續提升數據庫工程能力。