MySQL表操作：数据新增与查询详解

一、数据新增（Create）操作详解

1.1 INSERT 基础语法

数据插入是数据库操作的基础，MySQL 提供了标准的 INSERT 语句实现数据写入。基本语法结构如下：

INSERT INTO table_name (column1, column2,...)
VALUES (value1, value2,...);

字段列表注意事项：

• 字段顺序不需要与表结构完全一致
• 可以省略自增主键字段
• 允许只插入部分字段（非空字段必须包含）
• 字段名严格区分大小写（建议全小写）

值列表规范：

• 值顺序必须与字段列表严格对应
• 字符串类型必须使用单引号包裹
• 日期类型推荐使用标准格式'YYYY-MM-DD'
• NULL值需要显式声明

1.2 完整字段插入示例

当需要插入所有字段数据时，可以省略字段列表：

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    department VARCHAR(20) DEFAULT 'General',
    salary DECIMAL(10,2),
    hire_date DATE
);

-- 完整字段插入
INSERT INTO employees 
VALUES (DEFAULT, '张三', '技术部', 15000.00, '2023-01-15');

自增主键处理技巧：

• 使用 DEFAULT 关键字自动生成
• 显式设置 NULL 值（需确保字段允许自增）
• 完全省略字段和对应的值

1.3 批量插入优化方案

MySQL 支持单次插入多行数据，显著提升写入效率：

INSERT INTO employees (name, department, salary, hire_date)
VALUES 
    ('李四', '市场部', 12000.50, '2023-02-01'),
    ('王五', '财务部', 13500.00, '2023-03-15'),
    ('赵六', DEFAULT, 11000.00, CURDATE());

批量插入优势：

• 减少网络传输开销
• 降低SQL解析次数
• 事务提交次数优化
• 整体执行时间缩短

1.4 特殊插入方式

1.4.1 插入查询结果

将SELECT查询结果直接插入到目标表：

INSERT INTO employee_archive
SELECT * FROM employees
WHERE hire_date < '2020-01-01';

1.4.2 替代插入语法

使用SET子句进行插入：

INSERT INTO employees
SET name = '陈七',
    department = '人事部',
    salary = 12500.00,
    hire_date = '2023-04-01';

1.5 插入冲突处理

处理主键或唯一索引冲突：

-- 忽略冲突
INSERT IGNORE INTO employees (...) VALUES (...);

-- 更新冲突记录
INSERT INTO employees (...)
VALUES (...)
ON DUPLICATE KEY UPDATE 
    salary = VALUES(salary),
    update_time = NOW();

冲突处理策略对比：

方式	特点	适用场景
INSERT IGNORE	静默跳过冲突记录	批量插入容忍部分失败
ON DUPLICATE UPDATE	更新已有记录	需要维护最新数据
REPLACE INTO	先删除旧记录再插入新数据	需要完全替换记录

二、数据查询（Retrieve）全面解析

2.1 基础查询结构

SELECT 语句基本格式：

SELECT [DISTINCT] column1, column2...
FROM table_name
[WHERE condition]
[ORDER BY column [ASC|DESC]]
[LIMIT count];

2.2 字段选择方案

2.2.1 全字段查询

SELECT * FROM employees;

2.2.2 指定字段查询

SELECT name, department, hire_date FROM employees;

2.2.3 字段别名设置

SELECT 
    name AS 姓名,
    salary * 12 AS 年薪,
    DATEDIFF(NOW(), hire_date) AS 在职天数
FROM employees;

2.3 条件查询（WHERE）

2.3.1 比较运算符

-- 数值比较
SELECT * FROM employees WHERE salary > 10000;

-- 日期范围
SELECT * FROM employees 
WHERE hire_date BETWEEN '2023-01-01' AND '2023-06-30';

-- 空值判断
SELECT * FROM employees WHERE department IS NULL;

2.3.2 逻辑运算符组合

SELECT * FROM employees
WHERE (department = '技术部' OR salary > 13000)
  AND hire_date >= '2023-01-01';

2.3.3 模糊查询

-- % 表示任意多个字符
SELECT * FROM employees WHERE name LIKE '张%';

-- _ 表示单个字符
SELECT * FROM employees WHERE name LIKE '_三';

-- 转义特殊字符
SELECT * FROM employees WHERE name LIKE '100\%';

2.4 结果排序（ORDER BY）

2.4.1 单字段排序

SELECT * FROM employees
ORDER BY hire_date DESC;

2.4.2 多字段排序

SELECT * FROM employees
ORDER BY department ASC, salary DESC;

2.4.3 自定义排序

SELECT * FROM employees
ORDER BY 
    CASE department
        WHEN '技术部' THEN 1
        WHEN '市场部' THEN 2
        ELSE 3
    END;

2.5 分页查询（LIMIT）

2.5.1 基础分页

-- 前5条记录
SELECT * FROM employees LIMIT 5;

-- 跳过前10条取5条
SELECT * FROM employees LIMIT 10, 5;

2.5.2 现代分页语法

SELECT * FROM employees
LIMIT 5 OFFSET 10;

2.5.3 分页性能优化

-- 使用索引覆盖优化
SELECT id FROM employees
ORDER BY hire_date
LIMIT 10000, 20;

-- 然后通过主键获取完整数据
SELECT * FROM employees
WHERE id IN (上述查询结果);

2.6 结果去重（DISTINCT）

2.6.1 单字段去重

SELECT DISTINCT department FROM employees;

2.6.2 多字段去重

SELECT DISTINCT department, YEAR(hire_date) 
FROM employees;

2.6.3 聚合函数去重

SELECT COUNT(DISTINCT department) AS dept_count
FROM employees;

2.7 条件分支（CASE）

SELECT 
    name,
    salary,
    CASE
        WHEN salary > 15000 THEN '高收入'
        WHEN salary BETWEEN 10000 AND 15000 THEN '中等收入'
        ELSE '基础收入'
    END AS income_level
FROM employees;

2.8 JSON格式输出

SELECT JSON_OBJECT(
    'id', id,
    'name', name,
    'department', department
) AS employee_json
FROM employees
LIMIT 3;

（注：由于篇幅限制，此处展示核心内容，实际文章包含更多详细示例和解释）