MySQL 常用 SQL 語句速查手冊：DDL、DCL、DML、連接、導入導出

連接命令

# 本地
mysql -h localhost -u root -p123
# 遠程
mysql -h 172.16.xx.xx -P 3306 -u root -p

DDL（數據定義）

建表示例

CREATE TABLE device_label (
  id INT NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  origin_model VARCHAR(64) NOT NULL COMMENT '收集機型',
  origin_vendor VARCHAR(64) NOT NULL COMMENT '收集廠商',
  vendor VARCHAR(32) NOT NULL COMMENT '標注廠商',
  model VARCHAR(32) NOT NULL COMMENT '標注品牌',
  PRIMARY KEY (id),
  UNIQUE KEY device_key (origin_model, origin_vendor)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='設備標注表';

常用 ALTER

-- 修改列類型
ALTER TABLE device_label MODIFY origin_model VARCHAR(32);
-- 修改列名+類型
ALTER TABLE device_label CHANGE origin_model device_model VARCHAR(16);
-- 追加列
ALTER TABLE device_label ADD os_type VARCHAR(8) NOT NULL COMMENT '操作系統' AFTER id;
-- 調整列順序
ALTER TABLE device_label MODIFY os_type VARCHAR(8) AFTER origin_model;
-- 重定義主鍵
ALTER TABLE device_label DROP PRIMARY KEY, ADD PRIMARY KEY (origin_model, origin_vendor);
-- 清空表
TRUNCATE TABLE device_label;

DCL（權限控制）

-- 建用戶並授權全庫
CREATE USER 'hive'@'localhost' IDENTIFIED BY 'mypass';
GRANT ALL ON db1.* TO 'hive'@'localhost';
-- 僅授權單表 SELECT
GRANT SELECT ON db2.invoice TO 'hive'@'localhost';

DML（數據操作）

增

-- 單行
INSERT INTO device_label (origin_model, origin_vendor, vendor, model)
VALUES ('h9', 'bbk', '步步高', 'H9');
-- 表間複製
INSERT INTO device_label_copy (origin_model, origin_vendor, vendor, model)
SELECT origin_model, origin_vendor, vendor, model FROM device_label;
-- CSV 導入
LOAD DATA LOCAL INFILE 'dvc-label.csv' INTO TABLE device_label
FIELDS TERMINATED BY ',' IGNORE 1 LINES
(origin_model, origin_vendor, vendor, model);

改

UPDATE device_label SET origin_model='t2', origin_vendor='xiami' WHERE vendor='錘子';

刪

DELETE FROM device_label WHERE origin_vendor='alps';

查（聚合 + HAVING）

SELECT vendor, COUNT(DISTINCT model) AS models
FROM device_label GROUP BY vendor HAVING models > 10;

總結

涵蓋連接、建表、修改結構、權限、增刪改查、批量導入等高頻操作，適合日常開發速查。