当前位置：首页 > 数据库 > 正文

数据库怎么写

admin
数据库
2025-08-13
4

设计表结构，含字段名/类型/约束；用CREATE TABLE建表，INSERT INTO插入数据，通过ALTER修改

核心概念奠基

1 数据库本质认知

2 关键术语对照表

术语	定义	示例
实体(Entity)	现实世界中可区分的对象或概念	学生、课程、订单
属性(Attribute)	描述实体特征的数据项	学号、姓名、成绩
主键(Primary Key)	唯一标识实体的属性组合	`student_id`
外键(Foreign Key)	指向另一表主键的属性，建立表间关联	`course_id`→课程表主键
约束(Constraint)	限制数据完整性的规则	NOT NULL, UNIQUE, CHECK
索引(Index)	加速数据查询的数据结构	为`name`字段创建B树索引

完整设计流程

1 需求分析阶段

核心任务：明确业务目标与数据需求

方法：与业务方深度访谈，梳理业务流程，绘制用例图
输出物：《数据需求规格说明书》，包含：
- 需要存储哪些数据项？
- 数据间存在何种关联关系？
- 预期查询模式有哪些？
- 并发访问量预估？

2 概念结构设计（ER建模）

三步法构建ER图：

识别实体：从需求文档提取名词性词汇（如”用户””商品”）
定义属性：补充实体特征（如用户实体含手机号、注册时间）
建立联系：标注实体间关系（一对一/一对多/多对多）

案例示范：电商系统核心实体关系
| 实体 | 主键 | 主要属性 | 关联关系 |
|————|—————|———————————–|————————|
| 用户 | user_id | phone, email, reg_time | 1:N → 订单 |
| 商品 | product_id | name, price, stock | N:M ↔ 分类 |
| 订单 | order_id | total_amount, status | M:1 ← 用户 |
| 分类 | category_id | parent_id (自关联) | M:N ↔ 商品 |

️ 常见错误：忽略弱实体（如订单详情必须依赖订单存在）、遗漏复合属性（收货地址=省+市+街道）

数据库怎么写第1张

3 逻辑结构设计（范式化处理）

规范化理论应用：

1NF：消除重复组，确保每列原子性（如将”兴趣爱好”拆分为单选框）
2NF：所有非主属性必须完全依赖主键（移除部分依赖）
3NF：消除传递依赖（创建中间表替代冗余字段）
BCNF：加强版的3NF，适用于多候选码场景

反规范化时机：当查询性能成为瓶颈时，可适当增加冗余字段（如订单表中直接存储客户姓名）

4 物理结构设计

️ 实施要点：

表结构设计：
- 字段命名：采用snake_case（如order_detail），避免保留字
- 数据类型选择：
  | 应用场景 | 推荐类型 | 范围/精度说明 |
  |—————–|——————-|———————–|
  | 自增ID | BIGINT UNSIGNED | 最大值约9e18 |
  | 金额 | DECIMAL(10,2) | 精确到分，防浮点误差 |
  | IP地址 | VARCHAR(45) | IPv6最长字符数 |
  | 布尔状态 | TINYINT(1) | 0=否，1=是 |
- 默认值设置：created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
索引策略：
- 单列索引：高频查询条件（如user_id）
- 复合索引：多条件联合查询（如country + city）
- 全文索引：文本搜索场景（MySQL的FULLTEXT）
分区方案：
- 范围分区：按时间区间分割（如按月分区的日志表）
- 哈希分区：均匀分散大数据量（如用户行为记录）
- 列表分区：特定枚举值分组（如地区维度）

具体实现步骤（以MySQL为例）

1 DDL语句编写规范

-创建用户表（含注释）
CREATE TABLE `users` (
    `user_id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `username` VARCHAR(50) NOT NULL UNIQUE, -用户名唯一约束
    `password_hash` CHAR(60) NOT NULL, -bcrypt加密后的密码哈希
    `email` VARCHAR(100) NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_email` (`email`), -邮箱登录查询优化
    CHECK (LENGTH(`password_hash`) = 60), -确保密码格式正确
    CONSTRAINT `fk_role` FOREIGN KEY (`role_id`) REFERENCES `roles`(`role_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

2 DML操作最佳实践

操作类型	注意事项	示例代码
插入数据	批量插入优于单条插入，使用`LOAD DATA INFILE`处理大文件	`INSERT INTO products(...) VALUES (...),(...);`
更新数据	添加`ORDER BY primary_key DESC`防止死锁，控制每次更新行数	`UPDATE orders o, payment p ...`
删除数据	软删除优先（添加`is_deleted`标记），硬删除需谨慎	`DELETE FROM temp_logs WHERE create_time < '2023-01-01';`
查询数据	避免`SELECT` ，显式指定所需字段；复杂查询优先执行EXPLAIN分析	`EXPLAIN SELECT u.name, o.amount FROM users u JOIN orders o ON u.id=o.user_id;`

3 事务与锁机制

事务隔离级别选择：
| 级别 | 脏读 | 不可重复读 | 幻读 | 适用场景 |
|—————|——|————|——|————————|
| Read Uncommitted | ️ | ️ | ️ | 极少使用 |
| Read committed | | ️ | ️ | 多数场景默认 |
| Repeatable read | | | ️ | 报表统计类操作 |
| Serializable | | | | 银行转账等严格场景 |

死锁解决方案：

按固定顺序访问资源（如总是先锁A再锁B）
设置超时参数innodb_lock_wait_timeout=50
捕获死锁错误号1213,自动重试机制

高级优化技巧

1 查询性能调优

慢查询定位流程：

开启慢查询日志：set global slow_query_log='ON';
分析慢查询文件：pt-query-digest slow.log > report.html
典型优化手段：
- 替换子查询为JOIN操作
- 对排序字段建立索引
- 分解复杂SQL为多个简单查询
- 使用覆盖索引（Query execution plan显示Using index）

2 存储引擎选择

引擎	特点	适用场景
InnoDB	支持事务/行级锁/外键，MVCC实现	绝大多数业务场景
MyISAM	全文索引性能好，表级锁	纯读操作为主的归档表
MEMORY	内存存储，速度极快	临时缓存表
TokuDB	列压缩存储，适合超大数据集	数据仓库历史数据