您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 表操作 SQL 大全：建表、修改結構、約束、索引、備份導入導出一網打盡

編輯：MySQL 綜合教學

MySQL 表操作 SQL 大全

表是資料庫核心對象，掌握 DDL (定義)、DML (操作)、DCL (控制) 三大類語句是開發者與 DBA 必備基本功。本文系統整理建表、修改結構、完整性約束、索引維護、數據導入導出、PHP 程式化操作等高頻場景，附帶標準語法與實戰範例。

資料庫與表基礎操作

-- 建庫 (指定字符集排序規則)
CREATE DATABASE school DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- 選擇庫
USE school;

-- 建表 (完整範例)
CREATE TABLE student (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    sex CHAR(2) CHECK (sex IN ('男','女')),
    age INT,
    reg_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    info TEXT,
    tel CHAR(16) UNIQUE,
    remark VARCHAR(500) DEFAULT '學生'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 查看表結構
DESC student;
SHOW CREATE TABLE student;

表結構修改 (ALTER TABLE)

操作	標準語法	範例
新增欄位	ALTER TABLE tbl ADD COLUMN col_name type [constraints];	ALTER TABLE student ADD COLUMN email VARCHAR(50);
刪除欄位	ALTER TABLE tbl DROP COLUMN col_name;	ALTER TABLE student DROP COLUMN remark;
修改欄位名 + 類型	ALTER TABLE tbl CHANGE old_name new_name type [constraints];	ALTER TABLE student CHANGE sex gender CHAR(2) NOT NULL;
僅修改類型	ALTER TABLE tbl MODIFY col_name new_type [constraints];	ALTER TABLE student MODIFY age TINYINT UNSIGNED;
修改預設值	ALTER TABLE tbl ALTER col_name SET DEFAULT 'val';	ALTER TABLE student ALTER gender SET DEFAULT '男';
刪除預設值	ALTER TABLE tbl ALTER col_name DROP DEFAULT;	ALTER TABLE student ALTER gender DROP DEFAULT;
新增主鍵	ALTER TABLE tbl ADD PRIMARY KEY (col);	ALTER TABLE grade ADD PRIMARY KEY (id);
刪除主鍵	ALTER TABLE tbl DROP PRIMARY KEY;	ALTER TABLE grade DROP PRIMARY KEY;
重命名表	RENAME TABLE old_name TO new_name;	RENAME TABLE student TO stu_info;
刪除表	DROP TABLE [IF EXISTS] tbl_name;	DROP TABLE IF EXISTS bak_table;

完整性約束管理

-- 建表時定義
CREATE TABLE grade (
    id INT NOT NULL,
    name VARCHAR(20),
    sex CHAR(2),
    tel CHAR(16),
    address VARCHAR(50),
    -- 主鍵
    PRIMARY KEY (id),
    -- 唯一約束
    UNIQUE (tel),
    -- 檢查約束 (8.0.16+ 生效)
    CONSTRAINT chk_sex CHECK (sex IN ('男','女')),
    -- 預設約束
    address VARCHAR(50) DEFAULT '未填寫'
);

-- 事後新增約束 (需命名)
ALTER TABLE grade ADD CONSTRAINT uk_email UNIQUE (email);
ALTER TABLE grade ADD CONSTRAINT chk_age CHECK (age BETWEEN 0 AND 150);
ALTER TABLE grade ADD CONSTRAINT fk_dept FOREIGN KEY (dept_id) REFERENCES dept(id);

-- 查看約束
SELECT * FROM information_schema.TABLE_CONSTRAINTS WHERE TABLE_NAME='grade';

-- 刪除約束
ALTER TABLE grade DROP CONSTRAINT uk_email;

索引操作

-- 建立普通索引
CREATE INDEX idx_name ON student(name);
ALTER TABLE student ADD INDEX idx_age (age);
CREATE TABLE t (id INT, name VARCHAR(20), INDEX idx_name (name));

-- 建立唯一索引
CREATE UNIQUE INDEX uk_tel ON student(tel);
ALTER TABLE student ADD UNIQUE INDEX uk_email (email);

-- 建立複合索引 (最左前綴原則)
ALTER TABLE student ADD INDEX idx_name_age (name(10), age);

-- 刪除索引
DROP INDEX idx_name ON student;
ALTER TABLE student DROP INDEX idx_age;

-- 全文索引 (InnoDB 5.6+)
ALTER TABLE articles ADD FULLTEXT INDEX ft_content (content);

數據備份與導入導出

場景	命令	說明
邏輯備份 (結構+數據)	mysqldump -u root -p db_name tbl_name > backup.sql	單表備份
全庫備份	mysqldump -u root -p --single-transaction --routines --triggers --events db_name > full.sql	生產標準，含視圖/存儲過程/觸發器/事件
僅導出結構	mysqldump -u root -p -d db_name > schema.sql	-d / --no-data
導出 CSV	SELECT * FROM tbl INTO OUTFILE '/tmp/tbl.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';	需 FILE 權限、secure_file_priv 目錄
導入 SQL	mysql -u root -p db_name < backup.sql	或 SOURCE /path/backup.sql
導入 CSV	LOAD DATA INFILE '/tmp/tbl.csv' INTO TABLE tbl FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES;	IGNORE 1 LINES 跳過表頭

跨表數據合併插入 (INSERT ... SELECT)

-- 多表 JOIN 組合欄位插入
INSERT INTO target (col1, col2)
SELECT a.col1, b.col2
FROM table_a a
JOIN table_b b ON a.id = b.a_id;

-- 衍生表必須別名
INSERT INTO target (col1, col2)
SELECT * FROM (
    SELECT a.col1, b.col2
    FROM table_a a JOIN table_b b ON a.id = b.a_id
) AS tmp;

PHP 程式化操表示例 (PDO 預處理)

<?php
$dsn = "mysql:host=localhost;dbname=school;charset=utf8mb4";
$pdo = new PDO($dsn, 'user', 'pass', [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
]);

// 新增欄位
$pdo->exec("ALTER TABLE article ADD COLUMN keywords VARCHAR(100) NOT NULL DEFAULT ''");
echo "欄位新增成功";

// 批量插入 (事務 + 預處理)
$pdo->beginTransaction();
$stmt = $pdo->prepare("INSERT INTO student (name, sex, age) VALUES (?, ?, ?)");
$data = [
    ['張三', '男', 20],
    ['李四', '女', 19],
];
foreach ($data as $row) { $stmt->execute($row); }
$pdo->commit();
echo "批量插入 " . count($data) . " 條";
?>