当前位置：首页 > news >正文

关系型数据库核心概念与 MySQL 操作

news 2026/4/14 22:37:36

1. 核心术语

关系：二维表结构，类似 Excel 表格，行列顺序无关紧要
行：单条数据记录，例如"张三，20岁，男"
列：数据字段，如"姓名"字段
主键：表唯一标识，不可重复且非空（类似身份证号）
唯一键：可设置多个，允许空值但不可重复（如邮箱、手机号）
域：字段取值范围限定，如性别只能为"男"或"女"

表关系处理原则

一对一：任选一方添加外键
一对多：在"多"的一方添加外键
多对多：必须建立中间关联表

2. 数据库三大范式

第一范式（1NF）：字段原子性，不可再分
第二范式（2NF）：非主键字段必须完全依赖主键（复合主键时需特别注意）
第三范式（3NF）：消除非主键字段间的传递依赖

提示：范式设计虽能减少冗余，但实际应用中可根据性能需求适当反范式化

3. 数据类型选择指南

3.1 字符串类型

类型	特性	适用场景
`CHAR(n)`	定长存储，速度快但占用空间固定	身份证号、手机号等固定长度数据
`VARCHAR(n)`	变长存储，节省空间	姓名、地址等长度不固定数据
`TEXT`	大文本存储	文章内容等长文本数据

3.2 数值类型修饰

AUTO_INCREMENT：自增属性，需配合主键或唯一键使用
UNSIGNED：无符号数值，可扩大正数存储范围

3.3 通用字段约束

NOT NULL：非空约束
DEFAULT：默认值设置
PRIMARY KEY/UNIQUE KEY：主键与唯一键约束

4. 常用管理命令

\s-- 查看服务器状态SHOWDATABASES;-- 列出所有数据库SHOWTABLES;-- 显示当前库所有表DESC表名;-- 查看表结构SHOWCREATETABLE表名;-- 查看建表语句SELECTDATABASE();-- 显示当前使用数据库-- 实用技巧：使用\G竖向显示表信息SHOWTABLESTATUSLIKE'表名'\G

5. 表结构操作

5.1 建表示例

CREATETABLEstudents(idINTUNSIGNEDAUTO_INCREMENTPRIMARYKEYCOMMENT'学号',nameVARCHAR(20)NOTNULLCOMMENT'姓名',ageTINYINTUNSIGNEDCOMMENT'年龄',genderENUM('M','F')DEFAULT'M'COMMENT'性别')AUTO_INCREMENT=10;

5.2 表复制方式

仅复制结构（保留约束）：

CREATETABLE新表LIKE旧表;

复制结构及数据（不保留约束）：

CREATETABLE新表ASSELECT*FROM旧表;

5.3 表结构修改

ALTERTABLE表名ADD字段名 类型AFTER某字段;-- 添加字段ALTERTABLE表名DROP字段名;-- 删除字段ALTERTABLE表名MODIFY字段名 新类型;-- 修改字段类型ALTERTABLE表名 CHANGE 旧名 新名 类型;-- 重命名字段及类型-- 生产环境修改表结构前请务必备份数据

6. 数据操作

6.1 数据插入

-- 指定字段插入INSERTINTOstudents(name,age)VALUES('小明',20);-- SET语法插入INSERTINTOstudentsSETname='小红',age=18;-- 从其他表导入数据INSERTINTOstudents(name,age)SELECTname,ageFROMteachers;

6.2 数据更新

UPDATEstudentsSETage=21WHEREname='wei';-- 注意：未加WHERE条件将更新全表数据

6.3 数据删除

DELETEFROMstudentsWHEREstuid=30;-- 条件删除TRUNCATETABLEstudents;-- 清空表并重置自增值

7. 数据查询

7.1 基础查询

SELECTname,ageFROMstudents;-- 指定字段查询SELECTDISTINCTageFROMstudents;-- 去重查询SELECT*FROMstudentsWHEREageBETWEEN18AND25;-- 范围查询SELECT*FROMstudentsWHEREnameLIKE'张%';-- 模糊查询

7.2 排序与分页

SELECT*FROMstudentsORDERBYageDESCLIMIT3,4;-- 按年龄降序，跳过3条取4条

7.3 聚合与分组

SELECTgender,AVG(age)FROMstudentsGROUPBYgenderHAVINGAVG(age)>20;-- WHERE用于行过滤，HAVING用于分组结果过滤

8. 多表关联查询

8.1 内连接

SELECTs.name,t.nameFROMstudents sINNERJOINteachers tONs.teacherid=t.tid;

8.2 左外连接

SELECTs.name,IFNULL(t.name,'无老师')FROMstudents sLEFTJOINteachers tONs.teacherid=t.tid;

8.3 自连接

SELECTe.name 员工,IFNULL(l.name,'无上级')领导FROMemp eLEFTJOINemp lONe.leader_id=l.emp_id;

8.4 联合查询

SELECTstuid,nameFROMstudentsUNIONSELECTtid,nameFROMteachers;

8.5 三表关联

SELECTs.name,c.Course,sc.ScoreFROMstudents sJOINscores scONs.StuID=sc.StuIDJOINcourses cONsc.CourseID=c.CourseID;

9. 视图应用

将复杂查询保存为虚拟表：

CREATEVIEWv_score_detailASSELECTs.name,c.Course,sc.ScoreFROMstudents sJOINscores scONs.StuID=sc.StuIDJOINcourses cONsc.CourseID=c.CourseID;-- 视图使用SELECT*FROMv_score_detailWHEREScore>80;