1. 修改 root 密碼
mysql -u root -p
SET PASSWORD FOR root = PASSWORD('newpass');
2. 遠程登入
mysql -h host -u user -p
3. 基礎查詢指令
USE dbname;
SHOW DATABASES;
USE mysql; SHOW TABLES;
DESCRIBE user;
4. 建立超級用戶（慎用）
GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost' IDENTIFIED BY 'something' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%' IDENTIFIED BY 'something' WITH GRANT OPTION;
5. 移除授權
REVOKE ALL PRIVILEGES ON *.* FROM 'root'@'%';
USE mysql; DELETE FROM user WHERE User='root' AND Host='%'; FLUSH PRIVILEGES;
6. 建立特定主機/資料庫用戶
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON bankaccount.*
TO 'custom'@'weiqiong.com' IDENTIFIED BY 'stupid';
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. 資料操作
- 刪除：
DELETE FROM t1 WHERE c > 10;
- 更新：
UPDATE t1 SET user='weiqiong', password='weiqiong';
9. 前綴索引
CREATE INDEX part_of_name ON customer (name(10));
