MySQL核心语法：从基础约束到高级查询

一、数据定义与约束规范

1. 主键约束（PRIMARY KEY）

主键是表中唯一标识每条记录的列或列组合，具有NOT NULL和UNIQUE的双重特性。创建表时可通过以下方式定义：

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(50) NOT NULL,
    hire_date DATE
);

当尝试插入重复主键时会触发错误：

-- 正确插入
INSERT INTO employees VALUES (1001, '张三', '2020-05-01');

-- 错误示例：主键重复
INSERT INTO employees VALUES (1001, '李四', '2021-06-01');

复合主键的创建方式：

CREATE TABLE order_details (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

此时两个字段组合必须唯一，单个字段允许重复值存在。

2. 外键约束（FOREIGN KEY）

外键用于建立表间关联，确保数据参照完整性。创建包含外键的表：

CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(50)
);

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

插入数据时必须保证外键值存在于主表：

-- 正确顺序：先插入部门
INSERT INTO departments VALUES (10, '技术部');
INSERT INTO employees VALUES (1002, 10);

-- 错误示例：插入不存在的部门
INSERT INTO employees VALUES (1003, 20);  -- 违反外键约束

3. 唯一约束（UNIQUE）

确保某列的值不重复但允许NULL值：

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

测试唯一性约束：

INSERT INTO users VALUES (1, 'user@example.com', '13800138000');
INSERT INTO users VALUES (2, 'user@example.com', '13900139000');  -- 邮箱重复报错
INSERT INTO users VALUES (3, NULL, '13600136000');  -- 允许插入多个NULL

4. 检查约束（CHECK）

MySQL 8.0+版本完整支持列级检查约束：

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    price DECIMAL(10,2) CHECK (price > 0),
    stock INT CHECK (stock >= 0)
);

违反约束的插入操作：

INSERT INTO products VALUES (101, -5.99, 10);  -- 价格违规
INSERT INTO products VALUES (102, 19.99, -5);  -- 库存违规

二、数据操作关键语法

1. 删除操作注意事项

危险操作示范：

-- 删除所有记录（不可逆！）
DELETE FROM audit_log;

-- 安全删除（带WHERE条件）
DELETE FROM orders 
WHERE order_date < '2020-01-01';

建议使用事务保障数据安全：

START TRANSACTION;
DELETE FROM temp_data WHERE expired = 1;
-- 确认删除结果后再执行
COMMIT;

2. 数据插入类型匹配

字段类型严格匹配示例：

CREATE TABLE financial_records (
    record_id INT PRIMARY KEY,
    amount DECIMAL(12,2) NOT NULL,
    transaction_date DATE
);

-- 正确插入
INSERT INTO financial_records 
VALUES (2001, 50000.00, '2023-08-15');

-- 类型不匹配错误
INSERT INTO financial_records 
VALUES (2002, '五十万', '2023-08-16');  -- 金额字段类型错误

三、数据查询进阶技巧

1. 基础条件筛选

WHERE子句使用示例：

SELECT product_name, unit_price 
FROM products
WHERE category = '电子产品' 
  AND stock_quantity > 10;

2. 逻辑运算符组合

复杂条件查询：

SELECT employee_name, department, salary
FROM employees
WHERE (department = '销售部' AND salary >= 10000)
   OR (department = '技术部' AND salary >= 15000);

3. 范围查询优化

BETWEEN与IN的高效使用：

-- 日期范围查询
SELECT * FROM orders
WHERE order_date BETWEEN '2023-01-01' AND '2023-03-31';

-- 多值匹配查询
SELECT product_id, product_name 
FROM inventory
WHERE warehouse_id IN (101, 205, 307);

4. 模糊查询与正则表达式

LIKE操作符高级用法：

-- 查找以"A"开头的客户名称
SELECT customer_name 
FROM customers 
WHERE customer_name LIKE 'A%';

-- 使用正则表达式匹配
SELECT email 
FROM user_profiles 
WHERE email REGEXP '^[a-zA-Z0-9._%+-]+@example\.com$';

四、表结构设计最佳实践

1. 索引创建规范

合理创建索引提升查询效率：

-- 单列索引
CREATE INDEX idx_lastname ON employees(last_name);

-- 复合索引
CREATE INDEX idx_dept_salary ON employees(department_id, salary DESC);

2. 范式化设计原则

第三范式（3NF）应用示例：

-- 原始表（包含冗余数据）
CREATE TABLE customer_orders (
    order_id INT PRIMARY KEY,
    customer_name VARCHAR(100),
    customer_address VARCHAR(200),
    product_name VARCHAR(50)
);

-- 规范化后的设计
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100),
    address VARCHAR(200)
);

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(50)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    product_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

五、事务管理与并发控制

1. 事务基础操作

ACID特性实现示例：

START TRANSACTION;

UPDATE accounts 
SET balance = balance - 500 
WHERE account_id = 1001;

UPDATE accounts 
SET balance = balance + 500 
WHERE account_id = 1002;

-- 确认无误后提交
COMMIT;

-- 出现错误时回滚
ROLLBACK;

2. 隔离级别设置

查看和修改事务隔离级别：

-- 查看当前隔离级别
SELECT @@transaction_isolation;

-- 设置读已提交隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

六、存储引擎对比选择

1. InnoDB vs MyISAM

关键差异对比：

特性	InnoDB	MyISAM
事务支持	✅	❌
行级锁	✅	表级锁
外键约束	✅	❌
崩溃恢复	支持	需修复表
全文索引(MySQL5.6+)	✅	✅

2. 引擎指定方法

建表时显式指定存储引擎：

CREATE TABLE archive_data (
    id INT PRIMARY KEY,
    content TEXT
) ENGINE=MyISAM;

七、性能优化策略

1. 查询执行计划分析

使用EXPLAIN诊断查询：

EXPLAIN SELECT *
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE c.country = '中国'
ORDER BY o.order_date DESC;

2. 慢查询日志配置

启用慢查询日志监控：

-- 在my.cnf配置文件中设置
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
log_queries_not_using_indexes = 1

八、备份与恢复方案

1. mysqldump使用

全量备份示例命令：

mysqldump -u root -p --all-databases > full_backup.sql

2. 二进制日志恢复

通过binlog进行增量恢复：

mysqlbinlog binlog.000003 | mysql -u root -p