MySQL约束与设计(MySQL看这三篇足以)

第二篇

1. 排序

   select 字段名 from 表名 where 字段=值 order by 字段名 [ASC][DESC]

2. 组合排序

   SELECT 字段名 FROM 表名 WHERE 字段=值 ORDER BY 字段名 1 [ASC|DESC], 字段名 2 [ASC|DESC];

3. 聚合函数

   • 五个聚合函数

     • max(列名)：求这一列的最大值
     • min(列名)：求这一列的最小值
     • avg(列名)：求这一列的平均值
     • count(列名)：统计这一列有多少条记录
     • sum(列名)：对这一列求总和

   • 需要注意的是count()函数不会统计null的记录，如果要统计则需要给他指定值

     select count(ifnull(id,0))from student

4. 分组查询：分组查询是指使用GROUP BY语句对查询信息进行分组，相同的数据作为一组

   select 字段1，字段2..from 表名 GROUP BY 分组字段[having 条件]

   GROUP BY 将分组字段结果中相同内容作为一组，并且返回每组的第一条数据，所以单独分组没什么用处。 分组的目的就是为了统计，一般分组会跟聚合函数一起使用

   -- 按性别进行分组，求男生和女生数学的平均分

   select sex, avg(math) from student3 group by sex;

5. having和where的区别

   • where子句
     • 对查询结果进行分组前，将不符合 where 条件的行去掉，即在分组之前过滤数据，即先过滤再分组。
     • where 后面不可以使用聚合函数
   • having子句
     • having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，即先分组再过滤
     • having 后面可以使用聚合函数

6. limit子句

   SELECT *|字段列表 [as 别名] FROM 表名 [WHERE子句] [GROUP BY子句][HAVING子句][ORDER BY子 句][LIMIT 子句];

   limit子句的语法格式：

   • Limit offset，length

     offset：起始行数，从 0 开始计数，如果省略，默认就是 0

     length： 返回的行数

7. 数据库的备份与还原

   • 备份: DOS下，未登录的时候。这是一个可执行文件exe，在bin文件夹

     mysqldump -u 用户名 -p 密码 数据库 > 文件的路径

   • 还原：mysql中的命令，需要登录后才可以操作 USE

     USE 数据库；
     SOURCE 导入文件的路径; 
     

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

9. 约束的种类

   • 主键 primary key
   • 唯一 unique
   • 非空 not null
   • 外键 foreign key
   • 检查约束 check(mysql不支持)

10. 主键约束

    作用：用来唯一标识数据库中的每一条记录

    选取方式：通常不用业务字段作为主键，单独给每张表设计一个 id 的字段，把 id 作为主键。主键是给数据库和程序使用 的，不是给最终的客户使用的。所以主键有没有含义没有关系，只要不重复，非空就行

11. 创建主键的方式

    • 在创建表的时候给字段添加主键

      字段名 字段类型 PRIMARY KEY

    • 在已有表中添加主键

      alter table 表名 add primary key(字段名)

    • 删除主键

      alter table 表名 drop primary key(字段名)

    • 主键自增

      `AUTO_INCREMENT 表示自动增长(字段类型必须是整数类型)

      默认的 AUTO_INCREMENT 的开始值是 1，修改方式如下：

      •   CREATE TABLE 表名(  列名 int primary key AUTO_INCREMENT ) AUTO_INCREMENT=起始值; 
        

      • 创建好后修改起始值

        ALTER TABLE 表名 AUTO_INCREMENT=起始值;

12. delete与truncate对自增长的影响

    • delete：删除所有的记录对自增长没有影响
    • truncate：删除以后，自增长又重新开始

13. 唯一约束

    表中某一列不能出现重复的值

    字段名 字段类型 unique

14. 非空约束

    表中某一列不能为空

    字段名 字段类型 not null

15. 默认值

    字段名 字段类型 default 默认值

16. 唯一非空约束和主键的区别

    • 主键数在一个表中，只能有一个。不能出现多个主键。主键可以单列，也可以是多列
    • 自增长只能用在主键上

17. 外键约束

    什么是外键：在从表中与主表主键对应的那一列

    主表：一方，用来约束别人的表

    从表：多方，被别人约束的表

18. 创建约束的语法

    • 新建表时增加外键

      [constraint][外键约束名称]foreign key(外键字段名)references主表名(主键字段名)

    • 已有表增加外键

      alter tanle 从表 add [constraint] [外键约束名称]foreign key（外键字段名）references主表(主键字段名)

19. 外键的级联操作

    在修改和删除主表的主键时，同时更新或删除副表的外键值，称为级联操作

    • 级联更新 on update cascade:级联更新，只能是创建表的时候创建级联关系。更新主表中的主键，从表中的外键 列也自动同步更新
    • 级联删除 on update cascade:级联删除

    PS：constraint emp_depid_fk foreign key (dep_id) references department(id) on update cascade on delete cascade

20. 多对多关系建表原则：需要创建第三张表，中间表中至少两个字段，这两个字段分别作为外键值指向各自一方的主键

21. 数据库设计

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

    • 三大范式

        目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF） 、 第四范式(4NF）和第五范式（5NF，又称完美范式）。
        满足最低要求的范式是第一范式（1NF）。在第一范式的基础上进一步满足更多规范要求的称为第二范式（2NF） ， 其余范式以次类推。一般说来，数据库只需满足第三范式(3NF）就行了
      

      ​

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

    • 2NF:在满足第一范式的前提下，表中的每一个字段都完全依赖于主键。

      ​ 所谓完全依赖是指不能存在仅依赖主键一部分的列。简而言之，第二范式就是在第一范式的基础上所有列完全依赖于主键列。当存在一个复合主键包含多个主键列的时候，才会发生不符合第二范式的情况。比如有一个主键有两个列，不能存在这样的属性，它只依赖于其中一个列，这就是不符合第二范式。

    • 3NF:在满足第二范式的前提下，表中的每一列都直接依赖于主键，而不是通过其它的列来间接依赖于主键。 简而言之，第三范式就是所有列不依赖于其它非主键列，也就是在满足 2NF 的基础上，任何非主列不得传递 依赖于主键

      ​

    • 三大范式的特点

      1NF：原子性：表中的列不可拆分

      2NF：不产生局部依赖，一张表只描述一件事

      3NF：不产生传递依赖，表中每一列都直接依赖主键

      ​

      ​