MySQL基础与核心操作

数据库与SQL基础概念

1.1 数据库系统组成

• ​关系型数据库：理解表（Table）、字段（Column）、记录（Row）、主键（Primary Key）、外键（Foreign Key）等概念
• ​SQL分类：DDL（数据定义语言）、DML（数据操作语言）、DQL（数据查询语言）、DCL（数据控制语言）
• ​存储引擎：InnoDB（支持事务）与MyISAM（读密集型场景）的区别及适用场景

2. MySQL安装与环境配置

2.1 基础部署

• Windows/macOS/Linux环境下的安装方式（推荐使用Docker容器化部署）
• 命令行工具使用：mysql -u root -p 登录
• 图形化工具：Navicat/DBeaver/Workbench基础操作

3. 数据定义语言（DDL）

3.1 数据库操作

CREATE DATABASE bigdata_db CHARACTER SET utf8mb4;  -- 创建数据库
ALTER DATABASE bigdata_db CHARACTER SET utf8;      -- 修改字符集
DROP DATABASE test_db;                            -- 删除数据库

3.2 数据表设计

• ​字段类型：重点掌握数值类型（INT/BIGINT/DECIMAL）、字符串类型（VARCHAR(255)/TEXT）、时间类型（DATETIME/TIMESTAMP）

CREATE TABLE user_info (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    age TINYINT UNSIGNED CHECK (age >= 18),
    reg_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_reg_time (reg_time)  -- 创建普通索引
) ENGINE=InnoDB;

4. 数据操作语言（DML）

4.1 CRUD操作

-- 插入数据（批量插入优化）
INSERT INTO orders (order_id, amount) 
VALUES (1001, 299.00), (1002, 599.00);

-- 更新数据（带WHERE条件）
UPDATE products SET stock = stock - 1 WHERE product_id = 5;

-- 删除数据（注意事务控制）
DELETE FROM logs WHERE create_time < '2023-01-01';

5. 数据查询语言（DQL）

5.1 基础查询

-- 多表JOIN查询（电商示例）
SELECT u.username, o.order_amount
FROM users u 
JOIN orders o ON u.user_id = o.user_id
WHERE o.create_time BETWEEN '2024-01-01' AND '2024-03-31'
ORDER BY o.order_amount DESC
LIMIT 10;

5.2 聚合与分组

-- 统计每月销售TOP3品类
SELECT 
    DATE_FORMAT(order_time, '%Y-%m') AS month,
    category,
    SUM(amount) AS total_sales
FROM sales
GROUP BY month, category
HAVING total_sales > 100000
ORDER BY month ASC, total_sales DESC;

6. 约束与索引

6.1 完整性约束

• 主键约束：PRIMARY KEY
• 唯一约束：UNIQUE KEY
• 外键约束：FOREIGN KEY (user_id) REFERENCES users(user_id)
• 检查约束：CHECK (gender IN ('M','F'))

6.2 索引优化

• B+树索引原理图解
• 组合索引最左前缀原则：INDEX (city, age)
• 执行计划分析：EXPLAIN SELECT ...

7. 基础函数与操作符

7.1 常用函数

• 字符串函数：CONCAT(), SUBSTRING(), LENGTH()
• 数值函数：ROUND(), ABS(), RAND()
• 日期函数：NOW(), DATE_ADD(), DATEDIFF()
• 条件函数：CASE WHEN score > 90 THEN 'A' ELSE 'B' END

8. 数据导入导出

-- 导出CSV（大数据量使用SELECT INTO OUTFILE）
SELECT * FROM sensor_data 
INTO OUTFILE '/tmp/sensor.csv'
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

-- 导入CSV文件
LOAD DATA INFILE '/tmp/user.csv' 
INTO TABLE users 
FIELDS TERMINATED BY ',';

---

学习重点提示：

1. 重点掌握多表JOIN查询和聚合分析，这是大数据分析的基础场景
2. 理解事务的ACID特性（原子性、一致性、隔离性、持久性）
3. 学会通过EXPLAIN分析慢查询，特别是type列（ALL/index/range等）和Extra列的Using filesort等提示
4. 大数据场景下特别注意批量插入的优化方式（如关闭autocommit、使用LOAD DATA替代INSERT）

Python关联点：
后续会学习使用pymysql或SQLAlchemy进行数据库连接，重点掌握：

• 连接池配置
• 防止SQL注入的参数化查询
• 结合Pandas实现DataFrame到数据库的批量写入