MySQL 基礎 CRUD 操作速查表

連線與庫表管理

mysql -h host -u user -p
CREATE DATABASE dbname;
SHOW DATABASES;
USE dbname;
CREATE TABLE t (c1 INT PRIMARY KEY, c2 VARCHAR(20));
DESC t;
SHOW TABLES;
ALTER TABLE t ADD COLUMN c3 DATE;
ALTER TABLE t ADD PRIMARY KEY (c1);
DROP DATABASE dbname;
DROP TABLE t;

資料操作 (DML)

INSERT INTO t VALUES (1, 'abc');
SELECT * FROM t WHERE c1 = 1;
CREATE INDEX idx_c2 ON t (c2);
DELETE FROM t WHERE c1 = 1;
UPDATE t SET c2 = 'xyz' WHERE c1 = 1;

匯入/匯出/備份

-- 執行 SQL 腳本
mysql < input.sql
mysql dbname < input.sql
-- 載入文字檔
LOAD DATA LOCAL INFILE 'file.txt' INTO TABLE t;
-- 備份
mysqldump --opt dbname > backup.sql