MySQL 常用命令大全：從啟動到權限管理的腳本之家整理版

一、啟動與退出

• 啟動：MySQL Command Line Client → 輸入密碼 → mysql>
• 退出：quit 或 exit

二、庫操作

CREATE DATABASE xhkdb;
SHOW DATABASES;
DROP DATABASE xhkdb;
USE xhkdb;
SELECT DATABASE();          -- 當前庫
SHOW TABLES;                -- 當前庫所有表

三、表操作（先 USE 庫）

-- 建表
CREATE TABLE MyClass(
  id INT(4) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  name CHAR(20) NOT NULL,
  sex INT(4) NOT NULL DEFAULT '0',
  degree DOUBLE(16,2)
);

-- 結構
DESC MyClass;
SHOW COLUMNS FROM MyClass;

-- 刪表
DROP TABLE MyClass;

-- 增
INSERT INTO MyClass VALUES(1,'Tom',96.45),(2,'Joan',82.99),(3,'Wang',96.59);

-- 查
SELECT * FROM MyClass;
SELECT * FROM MyClass ORDER BY id LIMIT 0,2;  -- 前2行

-- 刪
DELETE FROM MyClass WHERE id=1;

-- 改
UPDATE MyClass SET name='Mary' WHERE id=1;

-- 加欄位
ALTER TABLE MyClass ADD passtest INT(4) DEFAULT '0';

-- 改表名
RENAME TABLE MyClass TO YouClass;

四、進階更新技巧

-- 全欄位替換
UPDATE article SET content = REPLACE(content, '舊內容', '新內容');

-- 文章前加 4 空格
UPDATE article SET content = CONCAT('    ', content);

五、常用欄位類型

• INT[(M)]、DOUBLE[(M,D)]、DATE (1000-01-01 ~ 9999-12-31)
• CHAR(M) 定長、VARCHAR 變長、BLOB/TEXT 65535 字符

六、mysqldump 備份導出

# 全庫
mysqldump -h IP -u user -p --opt db > backup.sql

# 僅結構
mysqldump -h IP -d -u user -p db > struct.sql

# 單表
mysqldump --opt --add-drop-table -u user -p db table > dump.sql

# 不輸入密碼（不安全，僅測試用）
mysqldump -h IP -u user --password=123456 --opt db > backup.sql

七、實用查詢片段

-- 結果導出文件
SELECT title FROM book INTO OUTFILE '/tmp/outfile.txt';

-- 查重複（按 peopleId）
SELECT * FROM people WHERE peopleId IN (
  SELECT peopleId FROM people GROUP BY peopleId HAVING COUNT(*) > 1
);

-- 去重查詢
SELECT * FROM phome_ecms_wma WHERE title IN (
  SELECT DISTINCT title FROM phome_ecms_wma
);

-- 刪除重複（保留一條）
SELECT *, COUNT(DISTINCT title) INTO OUTFILE '/tmp/table.bak'
FROM phome_ecms_wma GROUP BY title;
DELETE FROM phome_ecms_wma;
LOAD DATA INFILE '/tmp/table.bak' REPLACE INTO TABLE phome_ecms_wma CHARACTER SET utf8;

-- 編碼查看
SHOW VARIABLES LIKE "character_set%";

-- 修改欄位類型
ALTER TABLE t CHANGE last_action last_action DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00';

-- 加/刪欄位
ALTER TABLE host ADD ks_mac VARCHAR(100);
ALTER TABLE t DROP field_name;

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

-- 索引操作
ALTER TABLE t ADD INDEX idx_name (name);
ALTER TABLE t ADD PRIMARY KEY (id);
ALTER TABLE t ADD UNIQUE uk_card (cardnumber);
ALTER TABLE t DROP INDEX idx_name;

八、遠程授權

GRANT ALL PRIVILEGES ON db_test.* TO root@192.168.1.9 IDENTIFIED BY '123456';
FLUSH PRIVILEGES;

九、安全初始化（必做）

USE mysql;
DELETE FROM user WHERE User = '';                    -- 刪匿名用戶
UPDATE user SET Password = PASSWORD('newpwd') WHERE User = 'root';  -- 改root密碼
FLUSH PRIVILEGES;

十、用戶權限體系

十一、GRANT 常用模板

GRANT ALL ON mydb.* TO user@host IDENTIFIED BY "pwd";
GRANT USAGE ON *.* TO user@host IDENTIFIED BY "pwd";
GRANT SELECT,INSERT,UPDATE ON mydb.* TO user@host IDENTIFIED BY "pwd";
GRANT UPDATE,DELETE ON mydb.TestTable TO user@host IDENTIFIED BY "pwd";
-- 加 WITH GRANT OPTION 可授權給他人

十二、導入導出速查

# 導出整庫
mysqldump -u user -p --default-character-set=latin1 db > file.sql
# 導出單表
mysqldump -u user -p db table > file.sql
# 導出結構
mysqldump -u user -p -d --add-drop-table db > struct.sql
# 導入
mysql -u root -p
USE db;
SOURCE /path/file.sql;