【MySQL】-- 表的增删改查（进阶）

1. 数据库的约束

1.1 not null约束

创建表时，可以指定某列不能为空

• 创建学生表且id列不能为空：
  

1.2 unique约束

创建表时，指定某列唯一、不能重复

• 创建学生表，id不能为空且不能重复：
  

1.3 default约束

默认值约束：创建表时，设置某列为指定默认值

• 创建学生表，name默认值为"无名氏" :
  

1.4 primary key约束

主键约束：指定某列为主键；primary key = not null + unqiue

• 创建学生表，指定id列为主键
  
  自增主键：搭配 auto_increment 关键字。插入数据对应的字段不给值时，使用最大值+1；
• 创建学生表，指定id为自增主键：
  

1.5 foreign key约束

外键约束：用于关联其它表的主键或者唯一键

foreign key 字段名 references 主表名(字段名)；

• 创建学生表和班级表，每个学生对应一个班级，每个班级对应多个学生，班级表id为主键，学生表class_id为外键：
  
  插入数据到班级表：
  
  
  插入数据到学生表：
  
  

2. 表的设计

一对一

例：一个学生只能有一个学号，一个学号对应一个学生

一对多

例：一个学生对应一个班级，一个班级对应多名学生

多对多

例：一个学生可以选择多门课程，一个课程也可提供给多名学生

3. 新增

insert into 表名 [(字段1，字段2…)] select …；

• 把student表的数据复制到student2表中：
  

4. 查询

4.1 聚合查询

4.1.1 聚合函数

函数	说明
count([字段值])	返回查询到数据的数量
sum([字段值])	返回查询到数据的总和
avg([字段值])	返回查询到数据的平均数
max([字段值])	返回查询到数据的最大值
min([字段值])	返回查询到数据的最小值

• 统计共有多少同学：
  

• 查询语文成绩的总分：
  

• 计算平均总分：
  

• 查询英语最高分的学生：
  

• 查询总分最低分的学生：
  

4.1.2 group by

在查询中使用group by可以对指定列进行分组查询，需要满足分组查询的字段是可分组的字段

• 查询各个岗位的平均薪资：
  
• 查询每个岗位的最高薪资与最低薪资：
  

4.1.3 having

分组后进行条件过滤使用having子句；

• 显示平均薪资大于十万的岗位和他的平均薪资：
  

4.2 联合查询

4.2.1 内连接

select 字段1，字段2… from 表1，表2… where 连接条件 and 其它条件；
select 字段1，字段2… from 表1 join 表2 on 连接条件 and 其它条件；

• 查询许仙同学的成绩：
  
• 查询所有同学的总成绩：
  
• 查询所有同学每一门课程的成绩：
  
  

4.2.2 外连接

外连接分为左外连接和右外连接

select 字段1，字段2… from 表1 [left] [right] join 表2 on 连接条件 and 其它条件；

现有两张表：

• 左外连接
  
• 右外连接
  

当左右两张表数据一一对应此时内连接与外连接无区别

4.2.3 自连接

在同一张表进行查询，自己连接自己
课程表：

• 查询计算机原理大于Java的学生成绩
  

4.2.4 子查询

也叫嵌套查询，在其它SQL中嵌套的select语句

• 查询“不想毕业”同学的同班同学
  
• 查询语文和英文的课程的成绩信息
  

4.2.5 合并查询

使用union关键字把两个查询语句的结果合并到一起

• 查询id小于3 或者 名字为“英文“的课程
  
  or只能针对一张表 ；union可以把多个表的查询结果合并（要求多个结果列得对应）

union会自动去重 union all不会去重