表操作

约束

约束用于限制表中的数据，保证数据的完整性和一致性。

主键约束 (PRIMARY KEY)

CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50)
);

CREATE TABLE users (
    id INT,
    username VARCHAR(50),
    PRIMARY KEY (id)
);

CREATE TABLE orders (
    user_id INT,
    order_id INT,
    PRIMARY KEY (user_id, order_id)
);

外键约束 (FOREIGN KEY)

CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    amount DECIMAL(10, 2),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    amount DECIMAL(10, 2),
    FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

外键约束选项：

• CASCADE: 级联操作
• SET NULL: 设置为 NULL
• RESTRICT: 限制（默认）
• NO ACTION: 无操作

唯一约束 (UNIQUE)

CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE,
    phone VARCHAR(20) UNIQUE
);

CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(100),
    phone VARCHAR(20),
    CONSTRAINT uk_email UNIQUE (email)
);

非空约束 (NOT NULL)

CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL
);

默认约束 (DEFAULT)

CREATE TABLE users (
    id INT PRIMARY KEY,
    status VARCHAR(20) DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

检查约束 (CHECK)

CREATE TABLE users (
    id INT PRIMARY KEY,
    age INT CHECK (age >= 0 AND age <= 150),
    gender CHAR(1) CHECK (gender IN ('M', 'F'))
);

自增约束 (AUTO_INCREMENT)

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50)
);

ALTER TABLE users AUTO_INCREMENT = 1000;

索引

创建索引

CREATE INDEX idx_username ON users(username);
CREATE UNIQUE INDEX idx_email ON users(email);
CREATE INDEX idx_name_age ON users(username, age);

查看索引

SHOW INDEX FROM users;
SHOW INDEXES FROM users;

删除索引

DROP INDEX idx_username ON users;
ALTER TABLE users DROP INDEX idx_username;

索引类型

类型	说明
PRIMARY	主键索引
UNIQUE	唯一索引
INDEX	普通索引
FULLTEXT	全文索引
SPATIAL	空间索引

全文索引

CREATE TABLE articles (
    id INT PRIMARY KEY,
    title VARCHAR(200),
    content TEXT,
    FULLTEXT INDEX ft_content (content)
);

SELECT * FROM articles
WHERE MATCH(content) AGAINST('MySQL');

表修改操作

添加列

ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users ADD COLUMN age INT FIRST;
ALTER TABLE users ADD COLUMN email VARCHAR(100) AFTER username;

修改列

ALTER TABLE users MODIFY COLUMN username VARCHAR(100);
ALTER TABLE users CHANGE COLUMN username user_name VARCHAR(100) NOT NULL;

删除列

ALTER TABLE users DROP COLUMN phone;

添加约束

ALTER TABLE users ADD PRIMARY KEY (id);
ALTER TABLE users ADD UNIQUE INDEX idx_email (email);
ALTER TABLE users ADD FOREIGN KEY (dept_id) REFERENCES departments(id);
ALTER TABLE users ADD CONSTRAINT chk_age CHECK (age >= 18);

删除约束

ALTER TABLE users DROP PRIMARY KEY;
ALTER TABLE users DROP INDEX idx_email;
ALTER TABLE users DROP FOREIGN KEY fk_dept;

重命名表

ALTER TABLE users RENAME TO user_info;
RENAME TABLE users TO user_info;

表复制

复制表结构

CREATE TABLE users_copy LIKE users;

复制表结构和数据

CREATE TABLE users_backup AS SELECT * FROM users;

复制部分数据

CREATE TABLE users_active AS
SELECT * FROM users WHERE status = 'active';

临时表

CREATE TEMPORARY TABLE temp_users (
    id INT,
    name VARCHAR(50)
);

DROP TEMPORARY TABLE temp_users;

表信息查看

SHOW TABLES;
SHOW TABLES LIKE 'user%';
DESCRIBE users;
DESC users;
SHOW CREATE TABLE users;
SHOW TABLE STATUS;
SHOW TABLE STATUS LIKE 'users';

存储引擎操作

查看存储引擎

SHOW ENGINES;

修改存储引擎

ALTER TABLE users ENGINE = InnoDB;

表分区

范围分区

CREATE TABLE sales (
    id INT,
    sale_date DATE,
    amount DECIMAL(10, 2)
)
PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

列表分区

CREATE TABLE users (
    id INT,
    region VARCHAR(20)
)
PARTITION BY LIST (region) (
    PARTITION p_north VALUES IN ('北京', '天津'),
    PARTITION p_south VALUES IN ('上海', '广州')
);

哈希分区

CREATE TABLE logs (
    id INT,
    log_date DATE
)
PARTITION BY HASH (id)
PARTITIONS 4;

下一步学习

• 查询语句 - 掌握复杂查询
• 索引优化 - 学习索引优化