Mysql——CRUD进阶

一、表的设计

1. 数据库的约束

• NOT NULL - 指示某列不能存储 NULL 值。
• UNIQUE - 唯一约束，保证某列的每行必须有唯一的值。
• DEFAULT - 规定没有给列赋值时的默认值。
• NOT NULL - 和 UNIQUE 的结合。确保某列（或两个列多个列的结 合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
• FOREIGN KEY - 外键约束，保证一个表中的数据匹配另一个表中的值的参照完整性。
  例子：一个学生对应一个班级，一个班级对应多个学生。classes_id为外键，关联班级表的主键id FOREIGN KEY (classes_id) REFERENCES classes(id)
• PRIMARY KEY -主键约束，对于整数类型的主键，常配搭自增长auto_increment来使用。插入数据对应字段不给值时，使用最大值+1。
• CHECK - 保证列中的值符合指定的条件。对于MySQL数据库，对CHECK子句进行分析，但是忽略CHECK子句。

2. 设计原则（数据库三大范式）

• 第一范式 —— 列不可再分
• 第二范式 —— 属性完全依赖于主键
• 第三范式 —— 属性不依赖于其他非主属性,直接依赖于主键

3.表的关系

• 一对一
  

• 一对多
  

• 多对多
  1 - m : n - 1
  
  下图为中间表的建表语句
  

在一对一的关系中，外键设计在两张表任意一张中都可以；
在一对多关系中，主表到从表通常是1对多，从表到主表1对1，外键设计在从表 ；
多对多关系中，需要设计一张中间表，设计两个外键分别关联两张主表的主键。

二、新增与复杂查询（难点）

可以使用 insert + select 完成先查询再插入的操作
例：
-- 将学生表中的所有数据复制到用户表 insert into test_user(name, email) select name, qq_mail from student;

1.聚合查询

1） 聚合函数

常见的统计总数、计算平局值等操作，可以使用聚合函数来实现，常见的聚合函数有如下，这些函数都是对结果集进行操作，而不是对表进行查询。是先查到expr的得到结果集，再对这个返回的结果集进行聚合函数，在得到结果。

注意点：
① count可以使用count(*),count(0),count(1),count(字段)。
② sum,avg,max,min都是传入字段或表达式使用。
使用如下：

-- 统计班级共有多少同学
SELECT COUNT(*) FROM student;
SELECT COUNT(0) FROM student;

-- 统计数学成绩总分
SELECT SUM(math) FROM exam_result;
-- 不及格 < 60 的总分，没有结果，返回 NULL