MySQL 表的约束与数据库设计

DQL 查询语句

排序

MySQL 表的约束与数据库设计

# 单列排序
  * 只按某一个字段进行排序,单列排序

# 组合排序
  * 同时对多个字段进行排序,如果第1个字段相等,则按照第2个字段排序,依次类推
  * 语法:
MySQL 表的约束与数据库设计

# 具体操作
  * 查询所有的数据,在年龄降序排序的基础上,如果年龄相同再按照数学成绩升序排序
MySQL 表的约束与数据库设计

聚合函数

之前我们做的查询都是横向查询,都是根据条件一行一行的进行判断。而使用聚合函数查询是纵向查询,是对一列的值进行计算,然后返回一个结果值。聚合函数会忽略空值 NULL。

# 五个聚合函数
MySQL 表的约束与数据库设计
MySQL 表的约束与数据库设计

# 语法(我们发现对于 NULL 的记录不会统计,因此如果统计个数,不要使用有可能为 null 的列)
MySQL 表的约束与数据库设计

# 如果必须统计 NULL,可以使用 IFNULL() 函数,如果记录为 NULL,则给个默认值,这样统计的数据就不会遗漏。
MySQL 表的约束与数据库设计

# 具体操作
MySQL 表的约束与数据库设计

分组

MySQL 表的约束与数据库设计

# GROUP BY 怎么分组的?
  * 将分组字段结果中相同内容作为一组,如:按照性别将学生分成 2 组
MySQL 表的约束与数据库设计

# GROUP BY 将分组字段结果中相同内容作为一组,并且返回每组的第一条数据,所以单独分组没什么用处。
  * 分组的目的就是为了统计,因此分组后查询的字段一般就是 分组字段 和 聚合函数。

# 具体操作
  * 按性别进行分组,求男生和女生的数学平均分
MySQL 表的约束与数据库设计
MySQL 表的约束与数据库设计

  * 查询男女各有多少人,按性别分组
MySQL 表的约束与数据库设计
MySQL 表的约束与数据库设计

  * 查询年龄大于 25 岁的人,按性别分组,统计每组的人数
MySQL 表的约束与数据库设计
MySQL 表的约束与数据库设计

  * 查询年龄大于 25 岁的人,按性别分组,统计每组的人数,并只显示性别人数大于 2 的数据
MySQL 表的约束与数据库设计
MySQL 表的约束与数据库设计

# 注意:如果我们使用某个字段进行分组,那么在查询的时候也需要将这个字段查询出来,否则看不到数据属于哪一组。

# having 和 where 的区别
MySQL 表的约束与数据库设计

# 面试题
  * Orders 表数据如下所示,执行如下 SQL 语句,运行结果是?
MySQL 表的约束与数据库设计
   * SELECT 
    product,
    sum(price)
   FROM
    orders
   GROUP BY
    product 
   WHERE
    sum(price) > 30;
MySQL 表的约束与数据库设计

limit 语句

# 准备数据
MySQL 表的约束与数据库设计
MySQL 表的约束与数据库设计

# limit 的作用
  * LIMIT 是限制的意思,所以 LIMIT 的作用就是限制查询记录的条数
MySQL 表的约束与数据库设计

  * LIMIT 语法格式
MySQL 表的约束与数据库设计

# 具体操作
  * 查询学生表中的数据,从第 3 条开始显示,显示 6 条
MySQL 表的约束与数据库设计
MySQL 表的约束与数据库设计

# LIMIT 的使用场景
  * 分页:比如我们登录淘宝、京东,返回的商品信息可能有几万条,不是一次全部显示出来的,而是一页显示固定的条数。
MySQL 表的约束与数据库设计
MySQL 表的约束与数据库设计
MySQL 表的约束与数据库设计

# 开始索引的公式
  * 开始的索引 = (当前页码 - 1)* 每页显示的条数

# 注意:LIMIT 分页操作是 MySQL 的方言,在别的数据库中都有各自不同的分页语句

数据库备份和还原

备份的应用场景

# 在服务器进行数据传输、数据存储和数据交换,就有可能产生数据故障。比如发生意外停机或存储介质损坏。这时,如果没有采取数据备份和数据恢复手段与措施,就会导致数据的丢失,造成的损失是无法弥补的。

备份与还原语句

# 备份格式:DOS 下,未登录的时候
MySQL 表的约束与数据库设计

# 还原格式:mysql 中的命令,需要登录后才可以操作
MySQL 表的约束与数据库设计

# 具体操作
  * 备份 day21 数据库中的数据到 d:\day21.sql 文件中(数据库中的所有表和数据都会导出成为 SQL 语句)
MySQL 表的约束与数据库设计

  * 还原 day21 数据库中的数据
MySQL 表的约束与数据库设计

图形化界面的备份与还原

# 备份数据库中的数据
  * 选中数据库,右键 “备份 / 导出”
  * 指定导出路径,保存成 .sql 文件即可
MySQL 表的约束与数据库设计
MySQL 表的约束与数据库设计

# 还原数据库中的数据
  * 删除数据库
  * 数据库列表区域右键 “执行 SQL 脚本”,指定要执行的 SQL 文件,执行即可。
MySQL 表的约束与数据库设计
MySQL 表的约束与数据库设计

数据库表的约束

数据库约束的概述

# 约束的作用
  * 对表中的数据进行限制,保证数据的正确性、有效性和完整性。一个表如果添加了约束,不正确的数据将无法插入到表中。约束在创建表的时候添加比较合适。

# 约束的种类
MySQL 表的约束与数据库设计

主键约束

# 主键的作用
  * 用来唯一标识数据库中的每一条记录
MySQL 表的约束与数据库设计

# 哪个字段应该被设置为表的逐渐
  * 通常不用业务字段作为主键,单独给每张表设计一个 id 的字段,把 id 作为主键。主键是给数据库和程序使用的,不是给最终的客户使用的。所以主键有没有含义没有关系,只要不重复且非空即可。
  * 身份证、学号等不建议设置成主键

# 创建主键
  * 关键字:PRIMARY KEY
  * 特点:
    1)非空  not null
    2)唯一 
  * 创建主键的方式
    1)在创建表的时候给字段添加主键
MySQL 表的约束与数据库设计

    2)在已有表中添加主键
MySQL 表的约束与数据库设计

# 具体操作
  * 创建学生表 st5,包含字段(id,name,age),将 id 设置成主键
MySQL 表的约束与数据库设计
MySQL 表的约束与数据库设计
MySQL 表的约束与数据库设计

# 删除主键
MySQL 表的约束与数据库设计

# 主键自增
  * 如果让我们自己添加主键很有可能重复,因此我们希望每次插入新记录时,数据库自动生成主键字段的值
MySQL 表的约束与数据库设计
MySQL 表的约束与数据库设计
MySQL 表的约束与数据库设计

# 修改自增长的默认起始值
  * 默认地 AUTO_INCREMENT 的开始值是 1,如果希望修改起始值,请使用下列 SQL语法
    1)创建表时指定起始值
MySQL 表的约束与数据库设计
MySQL 表的约束与数据库设计

    2)创建好以后修改起始值
MySQL 表的约束与数据库设计
MySQL 表的约束与数据库设计
MySQL 表的约束与数据库设计

# DELETE 和 TRUNCATE 对自增长的影响
  * DELETE:删除所有记录后,自增长没有影响
MySQL 表的约束与数据库设计
  * TRUNCATE:删除以后,自增长又重新开始
MySQL 表的约束与数据库设计

唯一约束

# 什么是唯一约束?
  * 表中某一列不能出现重复的值

# 唯一约束的基本格式
MySQL 表的约束与数据库设计

# 删除唯一约束
  * ALTER TABLE 数据表名 DROP INDEX 列名;

# 实现唯一约束(唯一约束限定的列的值可以有多个 NULL)
MySQL 表的约束与数据库设计

非空约束

# 什么是非空约束?
  * 某一列不能为 NULL

# 非空约束的基本语法格式
MySQL 表的约束与数据库设计

# 具体实现
MySQL 表的约束与数据库设计
MySQL 表的约束与数据库设计

# 默认值
MySQL 表的约束与数据库设计
MySQL 表的约束与数据库设计

# 疑问:如果一个字段设置了非空与唯一约束,该字段与主键的区别?
  * 主键数在一个表中只能有一个。主键可以单列,也可以多列
  * 自增长只能用在主键上

外键约束

# 单表的缺陷
  * 创建一个员工表,包含字段(id,name,age,dep_name,dep_location),id 为主键并自动增长MySQL 表的约束与数据库设计

  * 存在缺陷:
    1)数据冗余
    2)后期会出现增删改问题
MySQL 表的约束与数据库设计

# 解决方案
MySQL 表的约束与数据库设计
MySQL 表的约束与数据库设计
MySQL 表的约束与数据库设计

  * 问题:当我们在 employee 的 dep_id 里面输入不存在的部门,数据依然可以添加,但是并没有对应的部门。employee 的 dep_id 中的数据理论上只能是 department 表中存在的 id
MySQL 表的约束与数据库设计

  * 解决方式:使用外键约束

# 什么是外键约束
  * 一张表的外键是另一张表的主键,所以两张表就形成了关联关系
  * 什么是外键:在从表中与主表主键对应的那一列,如:员工表中的 dep_id
  * 主表:一方,用来约束别人的表
  * 从表:多方,被别人约束的表
MySQL 表的约束与数据库设计

# 创建约束的语法(外键可以为 NULL 但是不可以为主表主键中不存在的值)
  * 新建表时增加的外键
MySQL 表的约束与数据库设计

  * 已有表增加外键
MySQL 表的约束与数据库设计

# 具体操作(必须先创建主表,再创建从表)
MySQL 表的约束与数据库设计
MySQL 表的约束与数据库设计
MySQL 表的约束与数据库设计

# 删除外键
MySQL 表的约束与数据库设计
MySQL 表的约束与数据库设计

# 外键的级联
  * 出现新的问题:
MySQL 表的约束与数据库设计
MySQL 表的约束与数据库设计

  * 什么是级联操作:
     在修改和删除主表的主键时,同时更新或删除副标的外键值,称为级联操作。
MySQL 表的约束与数据库设计
MySQL 表的约束与数据库设计
MySQL 表的约束与数据库设计

# 数据约束小结
MySQL 表的约束与数据库设计

表与表之间的关系

表关系的概念

# 现实生活中,实体与实体之间肯定是有关系的,比如:老公和老婆,部门和员工,老师和学生等。那么我们在设计表的时候,就应该体现出表与表之间的这种关系
MySQL 表的约束与数据库设计

一对多

# 一对多(1 :n)
  * 例如:班级和学生,部门和员工,客户和订单,分类和商品

# 一对多建表原则:在从表(多方)创建一个字段,作为外键指向主表(一方)的主键
MySQL 表的约束与数据库设计

多对多

# 多对多(m :n)
  * 例如:老师和学生,学生和课程,用户和角色

# 多对多关系的建表原则:需要创建第三张表,中间表至少两个字段,这两个字段分别作为外键指向各自一方的主键
MySQL 表的约束与数据库设计

一对一

# 一对一(1 :1)
  * 在实际开发中应用不多,因为一对一可以创建成一张表

# 建表原则
  * 外键唯一:主表的主键和从表的外键(唯一),形成主外键关系,外键唯一 UNIQUE
  * 外键是主键:主表的主键和从表的主键,形成主外键关系
MySQL 表的约束与数据库设计

一对多关系案例

# 需求:一个旅游线路分类中有多个旅游线路
MySQL 表的约束与数据库设计
MySQL 表的约束与数据库设计
MySQL 表的约束与数据库设计

# 具体操作
MySQL 表的约束与数据库设计
MySQL 表的约束与数据库设计
MySQL 表的约束与数据库设计

多对多关系案例

# 需求:一个用户收藏多个线路,一个线路被多个用户收藏
MySQL 表的约束与数据库设计
MySQL 表的约束与数据库设计
MySQL 表的约束与数据库设计

# 具体操作
MySQL 表的约束与数据库设计
MySQL 表的约束与数据库设计
MySQL 表的约束与数据库设计

表和表之间的关系小结
MySQL 表的约束与数据库设计

数据库设计

数据规范化

# 什么是范式
  * 好的数据库设计对数据的存储性能和后期的程序开发,都会产生重要的影响。建立科学的,规范的数据库就需要满足一些规则来优化数据库的设计和存储,这些规则就是范式。

# 三大范式:
  * 目前关系型数据库有六种范式:第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯-科德范式(BCNF)、第四范式(4NF)和第五范式(5NF,又称完美范式)
  * 满足最低要求的范式就是第一范式(1NF)。在第一范式的基础上进一步满足更多规范要求的称为第二范式(2NF),其余范式依此类推。一般说来,数据库只须满足第三范式(3NF)就足够了。

1NF

# 概念:
  * 数据库表的每一列都是不可分割的原子数据项,不能是集合、数组等非原子数据项。即表中的某个列有多个值时,必须拆分为不同的列。简言之,第一范式每一列不可再拆分,称为原子性。

# 示例

 学号  姓名  班级
 1  张三  一年级三班
 2  李四  一年级二班
 3  王五  一年级一班

2NF

# 概念:在满足 1NF 的基础上,非码属性必须完全依赖于候选码(消除非主属性对主码的部分函数依赖)
  * 函数依赖:A -- > B,如果通过 A 属性(属性组)的值可以唯一确定 B 属性的值,则称 B 依赖于 A
    如: 学号 -- > 姓名, (学号 , 课程名称)-- > 分数
  * 完全函数依赖:A -- > B,如果 A 是一个属性组,则 B 属性值的确定需要依赖 A 属性组中的所有的属性值
  * 码:如果在一张表中,一个属性或属性组,被其他所有属性所完全依赖,则称这个属性(属性组)为该表的码
  * 当存在一个复合主键包含多个主键列的时候,才会发生不符合第二范式的情况。比如有一个主键有两个列,不能存在只依赖于其中一个列的情况,这就是不符合第二范式

# 示例:
  * 借书证表

 学生证号   学生名称   学生证办理时间   借书证号   借书名称  借书证办理时间

  * 分成两张表

 学生证号   学生名称   学生证办理时间 
 借书证号   借书名称  借书证办理时间

3NF

# 概念:
  * 在满足第二范式的前提下,表中的每一个列都直接依赖于主键,而不是通过其他列简介依赖于主键
  * 简言之,第三范式就是所有列不依赖于其他非主键列,也就是在满足 2NF 的基础上,任何非主列不得传递依赖于主键。
  * 所谓传递依赖,指的是如果存在“A -> B -> C” 的决定关系,则C传递以来于A。因此满足第三范式的数据库表应该不存在以下的依赖关系:主键列 -> 非主键列x -> 非主键列y

# 示例:
  * 学生信息表

 学号   姓名   年龄   所在学院   学院地点 

  * 存在的决定关系:
  学号(姓名) -> 所在学院 -> 学院地点
  * 拆分成两张表

 学号   姓名   年龄   所在学院的编号(外键)
 学院编号   所在学院   学院地点 

三大范式小结

MySQL 表的约束与数据库设计

上一篇:Scala 基础入门【翻译】


下一篇:C#开源项目