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

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表名\G5. 表结构操作5.1 建表示例CREATETABLEstudents(idINTUNSIGNEDAUTO_INCREMENTPRIMARYKEYCOMMENT学号,nameVARCHAR(20)NOTNULLCOMMENT姓名,ageTINYINTUNSIGNEDCOMMENT年龄,genderENUM(M,F)DEFAULTMCOMMENT性别)AUTO_INCREMENT10;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小红,age18;-- 从其他表导入数据INSERTINTOstudents(name,age)SELECTname,ageFROMteachers;6.2 数据更新UPDATEstudentsSETage21WHEREnamewei;-- 注意未加WHERE条件将更新全表数据6.3 数据删除DELETEFROMstudentsWHEREstuid30;-- 条件删除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.teacheridt.tid;8.2 左外连接SELECTs.name,IFNULL(t.name,无老师)FROMstudents sLEFTJOINteachers tONs.teacheridt.tid;8.3 自连接SELECTe.name 员工,IFNULL(l.name,无上级)领导FROMemp eLEFTJOINemp lONe.leader_idl.emp_id;8.4 联合查询SELECTstuid,nameFROMstudentsUNIONSELECTtid,nameFROMteachers;8.5 三表关联SELECTs.name,c.Course,sc.ScoreFROMstudents sJOINscores scONs.StuIDsc.StuIDJOINcourses cONsc.CourseIDc.CourseID;9. 视图应用将复杂查询保存为虚拟表CREATEVIEWv_score_detailASSELECTs.name,c.Course,sc.ScoreFROMstudents sJOINscores scONs.StuIDsc.StuIDJOINcourses cONsc.CourseIDc.CourseID;-- 视图使用SELECT*FROMv_score_detailWHEREScore80;10. 实用技巧养成添加COMMENT注释的习惯NULL值判断必须使用IS NULL而非 NULL使用\G竖向显示多字段查询结果避免在索引列上使用函数或前导通配符以免索引失效