sql
-- 創(chuàng)建表時(shí)指定非空約束
CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(20)  -- 允許為空
);

-- 修改表添加非空約束
ALTER TABLE users MODIFY phone VARCHAR(20) NOT NULL;

-- 插入數(shù)據(jù)驗(yàn)證
INSERT INTO users (id, username, email) 
VALUES (1, '張三', 'zhangsan@example.com'); -- 成功

INSERT INTO users (id, username, email) 
VALUES (2, NULL, 'lisi@example.com'); -- 失敗：username不能為NULL
最佳實(shí)踐：

業(yè)務(wù)關(guān)鍵字段如用戶名、郵箱等應(yīng)設(shè)置NOT NULL約束

對(duì)于可選信息字段，可根據(jù)業(yè)務(wù)需求決定是否允許NULL

sql
-- 單字段主鍵
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2)
);

-- 復(fù)合主鍵
CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

-- 添加主鍵約束
ALTER TABLE employees ADD PRIMARY KEY (emp_id);

-- 刪除主鍵約束
ALTER TABLE employees DROP PRIMARY KEY;
特點(diǎn)：

每個(gè)表只能有一個(gè)主鍵

主鍵值必須唯一且不為NULL

主鍵自動(dòng)創(chuàng)建索引，提高查詢效率

sql
-- 創(chuàng)建外鍵約束
CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(50) NOT NULL
);

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(50) NOT NULL,
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
    ON DELETE CASCADE   -- 級(jí)聯(lián)刪除
    ON UPDATE CASCADE   -- 級(jí)聯(lián)更新
);

-- 添加外鍵約束
ALTER TABLE orders 
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
ON DELETE SET NULL;  -- 設(shè)置為NULL

-- 外鍵操作選項(xiàng)：
-- CASCADE: 級(jí)聯(lián)操作
-- SET NULL: 設(shè)置為NULL
-- RESTRICT/NO ACTION: 限制操作
-- SET DEFAULT: 設(shè)置為默認(rèn)值
外鍵使用場(chǎng)景：

一對(duì)多關(guān)系（部門與員工）

多對(duì)多關(guān)系（通過中間表實(shí)現(xiàn)）

確保引用數(shù)據(jù)的存在性

sql
-- 創(chuàng)建檢查約束
CREATE TABLE students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(50) NOT NULL,
    age INT,
    gender CHAR(1),
    score DECIMAL(5, 2),
    -- 單字段檢查約束
    CONSTRAINT chk_age CHECK (age >= 18 AND age <= 60),
    -- 多字段檢查約束
    CONSTRAINT chk_gender CHECK (gender IN ('M', 'F')),
    CONSTRAINT chk_score CHECK (score >= 0 AND score <= 100)
);

-- 添加檢查約束
ALTER TABLE employees 
ADD CONSTRAINT chk_salary 
CHECK (salary >= 3000 AND salary <= 100000);

-- 復(fù)雜的檢查約束
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_date DATE,
    delivery_date DATE,
    CONSTRAINT chk_dates CHECK (delivery_date >= order_date)
);

sql
-- 創(chuàng)建唯一約束
CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50) UNIQUE,  -- 列級(jí)約束
    email VARCHAR(100),
    phone VARCHAR(20),
    -- 表級(jí)約束
    CONSTRAINT uq_email UNIQUE (email),
    -- 復(fù)合唯一約束
    CONSTRAINT uq_user_contact UNIQUE (username, phone)
);

-- 添加唯一約束
ALTER TABLE products 
ADD CONSTRAINT uq_product_code UNIQUE (product_code);

-- 刪除唯一約束
ALTER TABLE users DROP INDEX uq_email;
與主鍵的區(qū)別：

唯一約束允許NULL值，主鍵不允許

一個(gè)表可以有多個(gè)唯一約束，但只有一個(gè)主鍵

唯一約束不自動(dòng)創(chuàng)建聚集索引

sql
-- 使用自增約束
CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    order_number VARCHAR(20) UNIQUE,
    customer_id INT,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    total_amount DECIMAL(10, 2)
) AUTO_INCREMENT = 1000;  -- 設(shè)置起始值

-- 插入數(shù)據(jù)
INSERT INTO orders (order_number, customer_id, total_amount)
VALUES ('ORD2023001', 101, 299.99);  -- order_id自動(dòng)生成

-- 查看當(dāng)前自增值
SELECT AUTO_INCREMENT 
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'your_database' 
AND TABLE_NAME = 'orders';

-- 修改自增起始值
ALTER TABLE orders AUTO_INCREMENT = 2000;
注意事項(xiàng)：

通常與主鍵結(jié)合使用

只能用于整數(shù)類型（INT, BIGINT等）

刪除記錄不會(huì)重置自增值

sql
-- 使用無(wú)符號(hào)約束
CREATE TABLE inventory (
    item_id INT PRIMARY KEY AUTO_INCREMENT,
    item_name VARCHAR(100) NOT NULL,
    quantity INT UNSIGNED NOT NULL DEFAULT 0,  -- 數(shù)量不能為負(fù)數(shù)
    price DECIMAL(10, 2) UNSIGNED,            -- 價(jià)格不能為負(fù)數(shù)
    weight FLOAT UNSIGNED                     -- 重量不能為負(fù)數(shù)
);

-- 范圍對(duì)比
-- TINYINT: -128 ~ 127 (有符號(hào)) / 0 ~ 255 (無(wú)符號(hào))
-- INT: -2147483648 ~ 2147483647 (有符號(hào)) / 0 ~ 4294967295 (無(wú)符號(hào))
適用場(chǎng)景：

年齡、數(shù)量、價(jià)格等不可能為負(fù)值的字段

需要更大正數(shù)范圍的場(chǎng)景

sql
-- 創(chuàng)建默認(rèn)約束
CREATE TABLE employees (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_name VARCHAR(50) NOT NULL,
    hire_date DATE DEFAULT (CURRENT_DATE),  -- 當(dāng)前日期
    status VARCHAR(20) DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    department VARCHAR(50) DEFAULT 'General'
);

-- 添加默認(rèn)約束
ALTER TABLE products 
ALTER COLUMN stock_quantity SET DEFAULT 0;

-- 移除默認(rèn)約束
ALTER TABLE products 
ALTER COLUMN stock_quantity DROP DEFAULT;

-- 使用默認(rèn)值插入數(shù)據(jù)
INSERT INTO employees (emp_name) 
VALUES ('李四');  -- 其他字段使用默認(rèn)值

sql
-- 創(chuàng)建客戶表
CREATE TABLE customers (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_code VARCHAR(20) UNIQUE NOT NULL,
    customer_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    phone VARCHAR(20) UNIQUE,
    status ENUM('active', 'inactive', 'suspended') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT chk_email CHECK (email LIKE '%@%')
) AUTO_INCREMENT = 1000;

-- 創(chuàng)建產(chǎn)品表
CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_code VARCHAR(20) UNIQUE NOT NULL,
    product_name VARCHAR(200) NOT NULL,
    category_id INT,
    price DECIMAL(10, 2) UNSIGNED NOT NULL,
    stock_quantity INT UNSIGNED DEFAULT 0,
    is_available BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT chk_price CHECK (price > 0),
    CONSTRAINT chk_stock CHECK (stock_quantity >= 0)
);

-- 創(chuàng)建訂單表
CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    order_number VARCHAR(30) UNIQUE NOT NULL,
    customer_id INT NOT NULL,
    order_date DATE DEFAULT (CURRENT_DATE),
    total_amount DECIMAL(12, 2) UNSIGNED DEFAULT 0.00,
    status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE
);

-- 創(chuàng)建訂單明細(xì)表
CREATE TABLE order_details (
    order_detail_id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT UNSIGNED NOT NULL DEFAULT 1,
    unit_price DECIMAL(10, 2) UNSIGNED NOT NULL,
    subtotal DECIMAL(12, 2) UNSIGNED GENERATED ALWAYS AS (quantity * unit_price) STORED,
    FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE RESTRICT,
    CONSTRAINT chk_quantity CHECK (quantity > 0),
    CONSTRAINT uq_order_product UNIQUE (order_id, product_id)
);