MySQL表的增删改查【进阶】

数据库的约束

约束就是数据库在使用的时候，对于里面能够存储的数据提出的要求和限制。
我们可以借助约束来完成更好的校验。

NOT NULL

指示某列不能存储NULL值。

NOT NULL是可以给任意列来进行设置的，不仅仅是这一个列。

UNIQUE

保证某列的每一行必须有唯一的值。
也就是数据唯一。如果尝试插入重复的值，也会报错。

DEFAULT

规定没有给赋值时的默认值。

My SQL中，默认的默认值是NULL

• 也可以吧默认值修改为其他需要的内容。
  

PRIMARY KEY

NOT NULL 和UNIQUE的结合，确保某列（或两列多列的结合）有唯一标识，有助于更容易的找到表中的一个记录。

这是日常开发的时候，最常用的约束，最重要的约束。
创建表的时候，很多时候需要指定主键。

主键不能为空的情况。

重复插入的情况

关于主键，典型的用法，就是直接使用1,2，3,4整数递增的方式来进行表示。My SQL里面对于递增视为主键，是有内置支持的，称为“自增主键”。

当设置好自增主键之后，此时插入的记录，就可以不指定自增主键的值了，（直接使用null表示），交给mysql自行分配即可。

也可手动指定id.

FOREIGN KEY

外键约束，针对两张表，进行了关联

接下来创建两张表（学生表、班级表）

在学生表中成功插入一条记录

在学生表中非法插入一条记录

修改学生表中的classId


外键约束同样也在约束着父表，当父表中的某个记录被子表中依赖着的时候，此时尝试进行删除或者修改，都会失败

这个操作就可以删除成功，针对classId=1的记录来说，并没有在student表中被依赖，就可以删除。

直接删除父表也是不可以的

表的设计

1.所谓的“数据库的设计” “表的设计”，其实就是根据实际问题场景，把表给创建出来了。

给你一个问题场景，如何设计数据库，如何设计表？
一个典型的办法，先找出这个场景中涉及的“实体”，然后再来分析“实体之间的关系”。
实体：就相当于“对象”。实体可以视为需求中的一些关键性的名词。

一对一

• 以教务系统为例
  student表（学生的id,学生姓名，班级…） uesr_id
  user表（用户账户，密码…）student_id

一个账户对应一个学生，一个学生只有一个账户

在数据库中如何表示这种一对一的关联关系呢？
方法一：可以把这两个实体用一张表来表示
方法二：可以用两张表来表示，其中一张表包含了另一个表的Id.
根据这个对应关系，就随时可以找到某个账户对应的学生是谁，也能找到某个学生对应的账户

一对多

• 以教务系统为例
  student表（学号，姓名…）
  class表（班级编号，班级名称…）

一个学生应该处于一个班级中，一个班级可以包含多个学生

多对多

• 学生和课程为例
  学生表（学号，姓名…）
  课程表（课程编号，课程名称）
  学生和课程就是多对多的关系

一个学生可以选择多门课程，一个课程可以包含多名学生

新增

和查询结合在一起的新增操作
把从上一个表的查询结果，作为下一个表要插入的数据
如：把A的记录插入到B 中

如果把B的列的顺序调换一下，能否完成吧A的数据插入到B中？？

插入的实际就是select执行结果的临时表。
查询的结果是在临时表里的，insert改的是硬盘中的数据（是把临时表中的数据插入到数据库服务器的硬盘里了）

查询

聚合查询

把多个行的数据进行了关联

区别：查询带表达式扥，属于列和列之间的查询

聚合函数

1.count的使用

2.sum的使用

sum这个操作只能针对数字进行运算，不能针对字符串

聚合函数，还可以搭配where字句来进行（可以基于条件进行筛选，把筛选结果在进行聚合）

分组操作（GROUP BY）

根据行的值，对数据进行分组，把值相同的行都归为一组

• 查询每个角色的最高工资、最低工资和平均工资
  
  

HAVING

group by 字句进行分组之后，需要对分组结果在进行条件过滤时，不能使用where，要使用HAVING

group by 中where 和 having的区别
where 在分组之前执行，如果要对分组之后的条件进行筛选，就要使用having

• 分组之前指定条件
  求每种角色平均薪资，要去掉马云
  
• 分组之后进行条件筛选
  求每种角色的平均薪资，只保留平均薪资1w以下的
  

联合查询（多表查询）

把多个表的记录往一起合并，一起进行查询
实际开发中一般禁止使用多表查询

笛卡尔积

• 查询同学姓名和对应班级名字
  

内连接

• 查询“许仙”同学的 成绩
  
  
  
• 查询所有同学的总成绩，及同学的个人信息

• 查询所有同学的成绩，及同学的个人信息

from 表1 join 表2 on 条件 join 表3 on 条件

外连接

上面的[ from 表1，表2 where 条件]的写法叫“内连接”
使用join on既可以表示内连接，也可以表示外连接
select * from 表1 (inner) join 表2 on 条件;

外连接分为左外连接和右外连接。如果联合查询，左侧的表完全显示我们就说是左外连接；右侧的表完全显示我们就说是右外连接.
语法：
左外连接：select 列 from 表1 left join 表2 on 条件;

右外连接：select 列 from 表1 right join 表2 on 条件;

自连接

自连接就是把自己和自己进行笛卡尔积。（用的不多，只是用来处理一些特殊场景的问题）

适用场景：把行和行转换成列和列

• 显示所有“计算机原理”成绩比“Java”成绩高的成绩信息
  
  

子查询（了解）

把多个select合并成一个

• 单行子查询：返回一行记录的子查询
  查询与“不想毕业” 同学的同班同学
  
  此结果与下面的结果相同
  
• 多行子查询：返回多行记录的子查询
  有时候子查询可能会查询出多 条记录，就不能直接使用=，可能需要用到 in 这样的一些操作。
  查询“语文”或“英文”课程的成绩信息
  
  

合并查询

就是把多个查询语句的结果给合并到一起了

通过union把两个sql的查询结果给合并到一起
合并的前提是两个sql查询的列得是对应的

• 查询id小于3，或者名字为“英文”的课程：

使用union不一定针对同张表

使用 or 的时候，必须保证针对同一张表来指定的多个条件查询。