MySQL 常用命令行操作大全：服務管理、賬號、庫表、導入導出

服務管理

net stop mysql
net start mysql

登入

mysql -u root -p
# 遠程：mysql -h ip -u root -p

賬號管理

GRANT SELECT,INSERT,UPDATE,DELETE ON *.* TO 'user1'@'localhost' IDENTIFIED BY 'pass';
GRANT ... TO 'user1'@'%' IDENTIFIED BY 'pass';  -- 任意主機
GRANT ... IDENTIFIED BY '';  -- 無密碼

庫表操作

SHOW DATABASES;
USE mysql;
SHOW TABLES;
DESCRIBE table_name;
CREATE DATABASE db_name;
DROP DATABASE db_name;
CREATE TABLE t (id INT, name VARCHAR(20));
DROP TABLE t;
DELETE FROM t;
SELECT * FROM t;

導出導入

# 導出整庫
mysqldump -u root -p db_name > dump.sql
# 導出單表
mysqldump -u root -p db_name table_name > dump.sql
# 導入
mysql -u root -p db_name < dump.sql
# 文本導入
LOAD DATA LOCAL INFILE 'file.txt' INTO TABLE t;

常用查詢片段

SELECT * FROM t ORDER BY id LIMIT 10,20;
UPDATE t SET name='new' WHERE id=1;
ALTER TABLE t ADD COLUMN addr VARCHAR(50);
ALTER TABLE t DROP COLUMN addr;
RENAME TABLE old TO new;