MySQL 資料庫操作命令大全：從入門到進階完整參考

本文整理 MySQL 核心操作命令，涵蓋連線、帳號管理、資料庫/資料表 CRUD、備份還原等實務技巧，適合開發與運維人員快速查閱。

一、連線 MySQL

# 本地連線
mysql -u root -p
# 遠端連線
mysql -h 110.110.110.110 -u root -p123456
# 退出
exit

二、修改密碼

# 首次設定密碼
mysqladmin -u root password 'ab12'
# 變更密碼
mysqladmin -u root -p'ab12' password 'djg345'

三、使用者管理

建立使用者 (需在 MySQL 內執行)

-- 允許任意主機存取所有資料庫 (高風險)
GRANT SELECT,INSERT,UPDATE,DELETE ON *.* TO 'test1'@'%' IDENTIFIED BY 'abc';

-- 僅允許本地存取特定資料庫 (建議)
GRANT SELECT,INSERT,UPDATE,DELETE ON mydb.* TO 'test2'@'localhost' IDENTIFIED BY 'abc';

-- 移除密碼
GRANT SELECT,INSERT,UPDATE,DELETE ON mydb.* TO 'test2'@'localhost' IDENTIFIED BY '';

四、資料庫操作

建立資料庫

CREATE DATABASE xhkdb;
-- 建庫並授權
CREATE DATABASE dbname;
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER ON dbname.* TO 'dbname'@'localhost' IDENTIFIED BY '密碼';
SET PASSWORD FOR 'dbname'@'localhost' = OLD_PASSWORD('密碼');

查看/切換/刪除資料庫

SHOW DATABASES;
USE dbname;
DROP DATABASE dbname;
DROP DATABASE IF EXISTS dbname; -- 安全刪除

五、資料表操作

建立資料表

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

修改資料表結構

-- 新增欄位
ALTER TABLE MyClass ADD passtest INT(4) DEFAULT '0';
-- 新增索引
ALTER TABLE employee ADD INDEX emp_name (name);
-- 新增主鍵
ALTER TABLE employee ADD PRIMARY KEY (id);
-- 新增唯一索引
ALTER TABLE employee ADD UNIQUE emp_name2 (cardnumber);
-- 刪除索引
ALTER TABLE employee DROP INDEX emp_name;
-- 修改欄位名稱/型別
ALTER TABLE table_name CHANGE old_field_name new_field_name field_type;
-- 刪除欄位
ALTER TABLE table_name DROP field_name;
-- 重命名資料表
RENAME TABLE MyClass TO YouClass;

資料操作 (CRUD)

-- 插入
INSERT INTO MyClass VALUES (1,'Tom',96.45), (2,'Joan',82.99);
-- 查詢
SELECT * FROM MyClass;
SELECT * FROM MyClass ORDER BY id LIMIT 0,2;
-- 更新
UPDATE MyClass SET name='Mary' WHERE id=1;
-- 刪除
DELETE FROM MyClass WHERE id=1;

六、備份與還原

mysqldump 備份

# 備份整個資料庫
mysqldump -u user -p123456 database_name > backup.sql
# 備份單張表
mysqldump -u user -p database_name table_name > backup.sql
# 只備份結構 (無資料)
mysqldump -u user -p -d --add-drop-table database_name > structure.sql
# 指定字元集備份
mysqldump -uroot -p --default-character-set=latin1 --set-charset=gbk --skip-opt database_name > backup.sql

還原資料

mysql -u root -p < backup.sql
# 或在 MySQL 內
SOURCE /path/to/backup.sql;

七、實用腳本範例

DROP DATABASE IF EXISTS school;
CREATE DATABASE school;
USE school;
CREATE TABLE teacher (
  id INT(3) AUTO_INCREMENT NOT NULL PRIMARY KEY,
  name CHAR(10) NOT NULL,
  address VARCHAR(50) DEFAULT '深圳',
  year DATE
);
INSERT INTO teacher VALUES (NULL,'allen','大連一中','1976-10-10');
INSERT INTO teacher VALUES (NULL,'jack','大連二中','1975-12-23');

執行腳本：mysql -uroot -p密碼 < c:\school.sql

八、字元集設定避免亂碼

# my.cnf 設定
default-character-set=gbk
# Java JDBC
jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=gbk
# PHP
header("Content-Type:text/html;charset=gb2312");