【MySQL】MySQL 表的增删改查(进阶)

欢迎关注个人主页：逸狼

---

创造不易，可以点点赞吗~

如有错误，欢迎指出~

---

---

目录

约束类型 

not null 非空

unique 唯一

default 指定默认值

primary key 主键

foreign key 外键

check字句  检查

表设计

确定实体  

实体之间的关系

聚合查询

聚合函数

count  计算列的行数

sum  计算列的和

group by 分组查询

搭配条件使用group by

分组之前的条件  用where

分组之后的条件  用having

同时包含 

联合查询(多表查询)

内连接  外连接 和 自连接

内连接

外连接 

左外连接

右外连接

​编辑

自连接

练习 

多表查询的步骤

多表查询 使用join

自连接 练习 

子查询

合并查询

union

union all 

---

约束类型 

not null 非空

指示某列不能存储 NULL 值。

unique 唯一

保证某列的每行必须有唯一的值。

default 指定默认值

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

primary key 主键

not null 和 unique 的结合。确保某列（或两个列多个列的结合）有唯一标 识，有助于更容易更快速地找到表中的一个特定的记录。 大部分情况下,都是用数字作为主键(当然有时候会使用是字符串).

 但是数据库 允许把多个列共同作为一个主键(联合主键)

mysql 提供" 自增主键" 自动分配(auto_increment) 主键,自增主键只针对 int/ bigint 整数.

foreign key 外键

保证一个表中的数据匹配另一个表中的值的参照完整性。

 父表约束子表,子表也会对父表反向约束~

若要删除父子表,  确保先删除 子表,在删除 父表 

check字句  检查

保证列中的值符合指定的条件。对于MySQL数据库，对CHECK子句进行分析，但是忽略 CHECK子句。(MySQL不支持check,但是写了check 不会报错 ,检测条件不会生效)

表设计

根据实际需求, 创建几个表, 每个表有哪些列,以及多个表之间有哪些联系 (即确定 需求中的"实体" 和"联系")

确定实体  

针对"实体" 抽象出其 特点,提取出需要用到的关键信息,

有几个实体,一般就会有几个表,每个实体用一个表来表示 

实体之间的关系

1. 一对一关系, 如:一个学生对应一个学号,一个学号只能属于一个学生
2. 一对多关系, 如:一个学生只能属于一个班级,但一个班级可以包含多个学生
3. 多对多关系, 如:一个学生可以选多个课程,一门课程也可以被多个学生选择
4. 没关系

聚合查询

聚合函数

count  计算列的行数

下图myql语句是 先执行select * from exam_result , 根据第一句的结果再执行count

结果中包含null ,可能会对上面的结果 产生影响(会把null也算在结果中) 但select 列名,遇到空值 不会进行计数.

sum  计算列的和

需要确保该列是数字用于计算

null和其他数值进行各种运算,结果都为null,  但sum会跳过null,不参与计算.

若是字符串,会出问题

avg() ,max() ,min() 等函数同理~

group by 分组查询

使用group by 指定一个列,会把列的值相同行归到一组

在上图的基础上排个序

搭配条件使用group by

分组之前的条件  用where

查询每个岗位的平均薪资,但是除去张三

分组之后的条件  用having

查询每个岗位的平均薪资,排除平均薪资超过5w的记录

同时包含 

查询每个岗位的平均薪资,排除张三,也排除平均薪资超过5w的记录

联合查询(多表查询)

笛卡尔积:将两个表按照一定的规律 排列组合

如图先创建两个表,一个学生表,一个班级表,它们通过classId联系在一起

内连接  外连接 和 自连接

 mysql支持内连接和 外连接,下面用两个表举例

内连接

内连接查询结果 只包含两个表 同时具备的数据

也可以使用 inner join 来写 内连接,其中inner 可以省略 

外连接 

左外连接

以左表为基准,确保左表的每个记录都出现在最终结果中,若左表的记录在右表中 没有对应的记录,就会把右表相关字段填成 null

右外连接

 mysql不支持 全外连接~

自连接

自己和自己进行 笛卡尔积,本质上是 把 行关系转换成 列 关系 

如果发现 要查询的条件 是针对两行 而不是两列时 就可以考虑使用 自连接

但是自连接的时候 ,表非常大, 此时的连接开销会非常大,容易把数据库 搞挂

练习 

多表查询的步骤

1. 明确要查询的信息 来自那些表
2. 对这些表进行笛卡尔积(前提是可以进行 笛卡尔积)
3. 指定连接条件,将有意义的数据筛选出来
4. 根据需要进一步指定条件,对数据进行筛选(新加的条件 往往通过and 方式连接)
5. 进一步针对查询的列 进行精简 

1. 查询“许仙”同学的 成绩

多表查询 使用join

使用join on和使用 where 本质上都是一样的,where是全部写在了一起,也是两两连接,join on 更突出筛选的过程

2. 查询所有同学的总成绩，及同学的个人信息：

select student.id,student.name,sum(score.score) from student join score

on student.id = score .student_id group by student.name;使用join实现上面功能

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

使用join 实现上图效果 

自连接 练习 

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

子查询

实际上就是 两个简单查询的套娃,也叫 嵌套查询, 不建议 使用

练习:  查询与“不想毕业” 同学的同班同学：

若将其拆成 两个 查询, 显然是更方便 程序员思考

 多子行查询

此时不能使用 = > < 这样的运算符 直接比较了,但可以使用 in

例如 查询“语文”或“英文”课程的成绩信息

若分成3个sql语句

合并查询

union

该操作符用于取得两个结果集的并集。会 自动去掉结果集中的重复行。

使用or达到相同效果,但是适用范围没有union 广泛

  select * from course where id <3 or name = '英文';

union all 

该操作符用于取得两个结果集的并集。不会去掉结果集中的重复行。