MySQL 常用指令大全：從連線、資料庫操作到備份還原

MySQL 常用指令大全：從連線、資料庫操作到備份還原

整理 MySQL 日常運維與開發最常用的指令分類速查，涵蓋連線管理、資料庫/資料表操作、CRUD、備份還原等核心場景。

0. 密碼設定與登入

# 登入
mysql -u root -h localhost -p

# 進入 mysql 系統庫
USE mysql;

# 設定 root 密碼
UPDATE user SET password=PASSWORD('新密碼') WHERE user='root';
FLUSH PRIVILEGES;

# 離開
QUIT;

1. 服務管理

2. 資料庫操作

CREATE DATABASE db_name;
SHOW DATABASES;
USE db_name;
DROP DATABASE db_name;

3. 資料表操作

-- 建表範例
CREATE TABLE tb_people (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user VARCHAR(30) NOT NULL,
  password VARCHAR(30) NOT NULL,
  email VARCHAR(50) NOT NULL,
  createtime DATETIME
);

-- 查看結構
SHOW TABLES;
DESC tb_people;
SHOW COLUMNS FROM db_name.tb_people;

-- 修改結構
ALTER TABLE tb_people ADD COLUMN phone VARCHAR(20);
ALTER TABLE tb_people MODIFY user VARCHAR(40);
ALTER TABLE tb_people DROP COLUMN phone;

-- 重命名 / 刪除
RENAME TABLE tb_people TO tb_users;
DROP TABLE tb_users;

4. 資料增刪查改 (CRUD)

-- 插入 (支援多行)
INSERT INTO tb_people (user, password, email, createtime)
VALUES ('czy','11111111','2384439266@qq.com','2016-10-10 02:36:50'),
       ('cml','22222222','22222222@qq.com','2016-10-10 02:40:36');

-- 查詢
SELECT * FROM tb_people;
SELECT id, user, email FROM tb_people WHERE id = 1;
SELECT * FROM db_name.tb_people;

-- 更新 (務必加 WHERE)
UPDATE tb_people SET password='12345678', email='new@mail.com' WHERE user='czy';

-- 刪除
DELETE FROM tb_people WHERE user='wha';

5. 備份與還原

# 備份單一資料庫
mysqldump -u root -p db_name > backup.sql

# 備份多資料庫
mysqldump -u root -p --databases db1 db2 > backup.sql

# 備份全庫 (含系統庫)
mysqldump -u root -p --all-databases > full_backup.sql

# 還原
mysql -u root -p db_name < backup.sql

# 只匯出結構
mysqldump -u root -p -d db_name > schema.sql

# 只匯出資料
mysqldump -u root -p -t db_name > data.sql

6. 進階查詢片段

-- 分頁
SELECT * FROM tb_people LIMIT 10 OFFSET 20;

-- 聯結
SELECT u.user, o.order_no FROM users u JOIN orders o ON u.id = o.user_id;

-- 彙總
SELECT COUNT(*), AVG(price) FROM products GROUP BY category_id;

-- 子查詢
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000);