重庆做网站怎么做,wordpress 4.9 php,工程公司财务制度,中国企业500强排名所属专栏#xff1a;MySQL学习 #x1f48e;1. 表的设计原则 1. 从需求中找到类#xff0c;类对应到数据库中的实体#xff0c;实体在数据库中表现为一张一张的表#xff0c;类中的属性对应着表中的字段 2. 确定类与类的对应关系 3. 使用SQL去创建具体的表 范式#xff1… 所属专栏MySQL学习 1. 表的设计原则 1. 从需求中找到类类对应到数据库中的实体实体在数据库中表现为一张一张的表类中的属性对应着表中的字段 2. 确定类与类的对应关系 3. 使用SQL去创建具体的表 范式范式描述的是数据关系的模型一对一关系一对多关系多对多关系
分类第一范式1NF第二范式2NF第三范式(3NF)BC范式BCNF
2. 三大范式
2.1 第一范式
规定表中的数据不能再分在定义表的时候对照数据中的数据类型每一个字段都可以用一个数据类型标识那么当前这个表就满足第一范式
例如定义一个学生表其中的字段有学号姓名年龄班级名学校名学校地址这就符合了第一范式但是如果是学号姓名年龄班级名学校这就不符合第一范式因为无法找到一个数据类型来表示学校这个对象
关系型数据库的一个最基本的要求不满足第一范式就不能称为关系型数据库
2.2 第二范式
在满足第一范式的基础上不存在非关键字段非主键字段对任意候选键主键外键没有主键时的唯一键的部分函数依赖存在与复合主键的情况下就满足第二范式复合主键在上一篇文章中已经介绍过一个表中不能有两个主键但是一个主键中可以包含多个列这时的主键就是复合主键
也就是说如果这个表不含复合主键那么这个表就满足第二范式 先来看一个表中存在复合主键的情况下存在非关键字段对候选键的部分函数依赖的不符合第二范式的反例
学生选修课成绩表 学号姓名年龄课程名称学分成绩202201张三19MySQL3100 其中年龄和姓名依赖学号对应唯一学号学分依赖课程名称成绩通过学生和课程共同区分也就是这个表中可以用学生和课程作为复合主键来确定学生当前的课程成绩对与其他的学分和学号学生姓名等没有关系学生的姓名和课程名等也没有关系 像这样的对于由两个或多个关键字段共同决定一条记录存在复合主键的情况如果一行数据中有些字段只与关键字段中的一个有关系那么就称为只存在部分函数依赖对于这样的情况就不满足第二范式 接下来看一个正面例子 对于这样的设计每张表都有非主键字段都强依赖与主键第三个表存在的复合主键非主键依赖于两个主键的字段不存在部分函数依赖满足第二范式 不符合第二范式的时候的弊端
学生选修课成绩表 学号姓名年龄课程名称学分成绩202201张三19MySQL3100202202李四19MySQL3100202203王五20Java2 95 202204赵六19Java296
1. 数据冗余
学生的年龄和学分大量出现造成数据冗余
2. 更新异常
如果需要修改MySQL的学分那么就需要修改表中所有关于MySQL的记录如果说只有部分数据修改成功剩余的还是原来的数据就会出现数据不一致造成数据混乱
3. 插入异常
当前表格在有学生录入成绩后才能查看课程的学分信息例如如果说这时学校加入一门新课但学生都没有考过试那么这门新课在数据库就就没有记录
4. 删除异常
同插入异常一样如果需要删除学生成绩例如把选Java的两位同学成绩删除那么此时在数据库中就又没有Java这门课程的学分信息了
2.3 第三范式
在第二范式的基础上不存在非关键字段对任意候选键的传递依赖
学生表 学号姓名年龄所在学院学院地址 在这个表中描述的主要对象是学生所以学号可以作为主键此时姓名和年龄与学号是强相关的学院地址与所在学院是强相关的描述学生所在学院只需要把学生和学院建立一个关联关系即可这两个强相关关系存在传递现象 学号-所在学院-学院地址 这种传递关系就称为传递依赖所以说这种设计不满足第三范式 根据学生与学院的关系拆分为两张表就满足了第三范式
学院表 学院编号学院名称学院地址
学生表 学号姓名年龄学院编号
这样设计两张表都依赖与自己表中的主键学生表可以通过外键与学院之间建立关联关系
3. 三种关系 3.1 一对一关系
例如设计一个登录界面输入用户名和密码登录成功之后显示欢迎用户这样的场景一般对应两个实体用户和账号并且一个用户只对应一个账号就是一对一的关系
针对一对一关系设计表时有两种方式
第一种就是把两个实体所有的信息放在一张表中
use_idnamephone_numberusernamepassword
第二种就是设计两张表分别记录用户信息和账号信息再把两张表关联起来
1.第一种关联方式就是通过用户id进行关联场景当输入用户名和密码并校验成功之后再通过用户id去查找用户的name
user_idnamephone_number
account_idusernamepassworduser_id 2.第二种关联方式通过account_id进行关联
account_idusernamepassword
user_idnamephone_numberaccount_id
3.2 一对多关系
一对多关系其实很常见例如学生和班级的关系一个班级中可以有多个学生
创建学生和班级表
班级表 class_idname
学生表 student_idnameclass_id 3.3 多对多关系
例如学生进行选课一个学生可以选多门课一门课可以被多名学生选择
分别创建实体表
课程表 course_idname1 MySQL 2Java
学生表 student_idnameage202201张三19202202李四20 创建关系表
学生选修课程表 idstudent_idcourse_id120220112202202132022022
通过关系表就可以记录每位同学选择的课程并且符合第二范式修改学生的年龄字段时也不会影响到关系表
最后把之前讲到的综合起来创建一张成绩表
-- 班级表
create table class
(class_id bigint primary key auto_increment,name varchar(20) not null
);-- 学生表
create table student
(student_id bigint primary key auto_increment,name varchar(20) not null,age bigint,class_id bigint,-- 设置class_id为class表class_id的外键foreign key (class_id) references class (class_id)
);-- 课程表
create table course
(course_id bigint primary key auto_increment,name varchar(50) not null
);
-- 成绩表
create table score
(score_id bigint primary key auto_increment,student_id bigint,course_id bigint,score decimal(5, 2),-- 设置student_id为student表student_id的外键foreign key (student_id) references student (student_id),-- 设置course_id为course表course_id的外键foreign key (course_id) references course (course_id)
);
4. 新增
需求创建一个新表把原来的表的数据内容复制到新表中 我们有以下几种解决方法 1. 一条一条的插入很明显这种方法很麻烦如果数据量很大就不好操作 2. 把原来的数据导出来再把表名修改一下再改入到目录表中 3. 使用 insert into select 语句 第二个方法就是在 navicat 中直接进行表的复制 下面来看使用 insert into select 语句的方法
-- 新建一张表把旧表导入到新表中
create table new_student
(id bigint primary key auto_increment,name varchar(50)
);
-- 把在原来的表中查到的数据插入到新的表中
insert into new_student
select id, name
from student;
需要注意的就是查询到的列和要插入的列要匹配不然就会报错
5. 聚合函数
函数说明COUNT([DISTINCT] expr) 返回查询到的数据的数量SUM([DISTINCT] expr)返回查询到的数据的总和AVG([DISTINCT] expr)返回查询到的数据的平均值MAX([DISTINCT] expr)返回查询到的数据的最大值MIN([DISTINCT] expr)返回查询到的数据的最小值
5.1 COUNT() 统计所有行
-- 统计表中的行数
select count(*)
from student;
-- 也可以传入常量 1
select count(1)
from student;
星号*并不直接表示表中的任意一列而是作为一个特殊的指示符告诉数据库管理系统DBMS计算表中的行数而不关心表中的列内容或是否有NULL值。
还可以指定某一列进行统计
-- 指定列统计
select count(id)
from student;select count(name)
from student; 5.2 SUM() 求和
创建一张成绩表计算语文的总成绩
create table exam
(id bigint primary key auto_increment,name varchar(20),chinese decimal(5, 2),math decimal(5, 2)
);
insert into exam(id, name, chinese, math)
values (1, 张三, 98, 95),(2, 李四, 97, 99),(3, 王五, 96, 98),(4, 赵六, 97, 94);
-- 计算语文总成绩
select *
from exam;
select sum(chinese)
from exam; 查询到的结果存储在了临时表中不受字段中长度的约束decimal(5, 2)
如果说求和的那一列存在null的话会是像之前表达式相加时null加上任何值都是null的情况吗
insert into exam values (5,钱七,96,null);select sum(math)
from exam; 可以看出最终的值并没有加上null 并且如果是非数值类型求和是没有意义的
5.3 AVG() 求平均值
-- 求平均值
select avg(math)
from exam;-- 参数里边可以包含表达式结果可以使用别名
select avg(math chinese) as 总分平均值
from exam; 5.4 MAX()和MIN()
求指定列中的最大值和最小值
-- 求最大值和最小值
select max(chinese) as 语文最大值,min(math) as 数学最小值
from exam; 可以多个聚合函数使用同时也可以使用别名
