MySQL 實用技巧總結：密碼修改、遠端連線、資料庫操作、授權管理、表結構變更與索引建立

1. 修改 Root 密碼

mysql -u root -p
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpass');

2. 遠端連線

mysql -h host -u user -p

前提：伺服器已授權該使用者從該主機連線 (GRANT ... TO user@host)。

3. 基本資料庫操作

4. 建立超級管理員 (可從任何主機連線)

GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost' IDENTIFIED BY 'pass' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%' IDENTIFIED BY 'pass' WITH GRANT OPTION;

5. 撤銷授權與刪除帳號

REVOKE ALL PRIVILEGES ON *.* FROM 'root'@'%';
USE mysql;
DELETE FROM user WHERE User='root' AND Host='%';
FLUSH PRIVILEGES;

6. 建立特定權限帳號

-- 從 weiqiong.com 連線，存取 bankaccount 庫
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP 
ON bankaccount.* TO 'custom'@'weiqiong.com' IDENTIFIED BY 'pass';

7. 表結構變更 (ALTER TABLE) 綜合範例

-- 重命名表
ALTER TABLE t1 RENAME t2;

-- 修改欄位型別 + 重命名欄位
ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20);

-- 新增 TIMESTAMP 欄位
ALTER TABLE t2 ADD d TIMESTAMP;

-- 新增索引 + 設定主鍵
ALTER TABLE t2 ADD INDEX (d), ADD PRIMARY KEY (a);

-- 刪除欄位
ALTER TABLE t2 DROP COLUMN c;

-- 新增 AUTO_INCREMENT 主鍵欄位 (需索引 + NOT NULL)
ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT, ADD INDEX (c);

8. 資料操作 (DML)

-- 刪除符合條件記錄
DELETE FROM t1 WHERE c > 10;

-- 批次更新 (謹慎：無 WHERE 會更新全表)
UPDATE t1 SET user='weiqiong', password='weiqiong' WHERE id=1;

-- 建立前綴索引 (節省空間，加速 LIKE 'prefix%' 查詢)
CREATE INDEX idx_name_prefix ON customer (name(10));

9. 索引建立策略提醒

• AUTO_INCREMENT 欄位必須建立索引 (通常設為 PRIMARY KEY)。
• 索引欄位不可為 NULL (除非明確允許)，建立索引前確保 NOT NULL。
• 前綴索引 name(10) 適用於長字串欄位，大幅縮減索引體積。
• 複合索引遵循 最左前綴原則，順序依查詢 WHERE/JOIN/ORDER BY 條件決定。