萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 常用操作大全:用戶權限、DDL、DML、查詢優化與備份還原

前言

MySQL 作為最流行的開源關係型數據庫,掌握核心 SQL 語法是開發與運維的基本功。本文系統整理用戶管理、庫表操作、數據增刪改查、備份還原、進階查詢技巧,附帶執行示例,適合作為速查手冊。

一、用戶與權限管理

1. 連接與斷開

# 連接(本地/遠程)
mysql -h 127.0.0.1 -u root -p
# 斷開
EXIT;  或  \q

2. 創建用戶

CREATE USER 'dev'@'%' IDENTIFIED BY 'ComplexPass#123';
-- % 允許任意主機,localhost 僅本地

3. 授權與回收

-- 單庫單表權限
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;

4. 修改密碼與刪除用戶

ALTER USER 'dev'@'%' IDENTIFIED BY 'NewPass#456';
DROP USER 'dev'@'%';

二、庫表結構操作 (DDL)

1. 數據庫級

SHOW DATABASES;
CREATE DATABASE `shop` CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE `shop`;
DROP DATABASE `shop_old`;

2. 表級

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`;

三、數據操作 (DML)

1. 插入

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;

2. 更新與刪除

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

3. 條件插入技巧

-- 唯一鍵衝突時更新
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);

四、查詢進階 (DQL)

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;

2. 聚合與分組

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;

3. 多表關聯

-- 內連接
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`;

4. 子查詢與 CTE (MySQL 8.0+)

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`);

5. 分頁優化(大偏移量問題)

-- 避免 LIMIT 1000000, 20
-- 方案:記住上一頁最大 ID
SELECT * FROM `orders` WHERE `id` > 1000000 ORDER BY `id` LIMIT 20;

五、備份與還原

1. 邏輯備份 mysqldump

# 全庫備份(含結構+數據)
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

2. 邏輯還原

mysql -uroot -p < full_2024-01-15.sql
# 或進入 mysql 後
SOURCE /path/to/backup.sql;

3. 物理備份(大數據量首選)

使用 Percona XtraBackup 實現熱備份、增量備份、流式傳輸,不鎖表、恢復快。參考文章 180217、180226。

六、性能優化鍵值

  • 索引:EXPLAIN 分析執行計劃,避免全表掃描;聯合索引遵循最左前綴
  • 避免 SELECT *:只查所需列,減少網絡與 I/O
  • 分頁優化:延遲關聯或游標分頁
  • 慢查詢日誌:開啟 slow_query_log=1,long_query_time=1,定期分析
  • 參數調優:innodb_buffer_pool_size 設為物理內存 60-70%

七、總結

熟練掌握上述核心語法,覆蓋 90% 日常開發運維場景。建議建立個人 Snippets 庫,結合 EXPLAIN、SHOW PROFILE、performance_schema 深入分析疑難 SQL,持續提升數據庫工程能力。

copyright © 萬盛學電腦網 all rights reserved