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

数据库的约束

概念

约束:数据库针对数据进行一系列的校验,如果发现插入的数据不符合约束中描述的校验规则,就会插入失败,为了更好的保证数据的正确性。
1.not null:不能插入空的数据。
在这里插入图片描述


2.unique:该列的所有行的数据是不能重复的。
在这里插入图片描述
两个约束叠加
在这里插入图片描述
3.default 给列执行默认值
在这里插入图片描述
4.primary key主键等价于not null + unique
在这里插入图片描述
5.auto_increment
在这里插入图片描述
自增的特点是:
如果表中没有任何记录,自增从1开始,如果表中已经有记录了,自增从上一条记录往下自增。
如果把中间的某个数据删了之后,再插入数据,刚才删掉的自增主键的值不会被重复利用。
6.foreign key 外键,描述两张表之间的关联关系。
在这里插入图片描述
需要指定三方面信息:
1.指定当前表中哪列进行关联。
2.指定和哪张表关联。
3.指定和目标表中哪一列关联。

表的设计

三大范式

1.一对一
eg:
人->身份证
2.一对多
班级–>拥有–>学生
组织形式
class 表(id,name)
student表(id,name,classid)
student表中可能会存在很多条记录,这很多条记录中其中的classid可能都是相同的记录,这些classid相同的记录表示是存在于同一个班级中.
3.多对多
在这里插入图片描述
多对多的关系,两者之间的对应关系是非常复杂的,多对多这个关系很复杂,需要引入中间表来解决这个问题。
例如,描述每个同学的每个科目的考试成绩~
先创建表来描述同学信息,然后再创建表描述科目信息。
在这里插入图片描述

在这里插入图片描述
在这里插入图片描述
由于是多对多的关系,会看到courseid存在很多重复的(很多同学都可能修了这门课程)
很多studentid也存在重复(一个同学可能修了多门课程)
如果想查找甲这个同学的语文成绩如何?
此时查找过程会更复杂
1.先找到甲的studentld
2.在找到语文的courseld
3.结合这两个id再在score表中查找
可以把其他select查找的结果作为新增的数据
在这里插入图片描述
子查询得到的列的数目,顺序,类型和被插入的表的列的数目,顺序,类型一致,名字不一致无所谓。

新增

可以把其他select查找的结果作为新增的数据.
子查询得到的列的数目,顺序,类型都得和被插入的表的列的数目,顺序,类型一致,列的名字一致不一致无所谓。
在这里插入图片描述

查询

1.count:计算结果的行数

在这里插入图片描述
在这里插入图片描述
count应该是不计算null的值的,本来是一个函数,如果和(name)有空格,相当于把Count当成一个列名。
2.求总和
在这里插入图片描述
3.求最大最小值
在这里插入图片描述

4.求所有分数小于90的同学的平均分。
在这里插入图片描述
5.group by把查询结果集按照一定的规则分组(可能分成多个组)
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
角色相同的会被分到同一组中。查询每个岗位的最高工资,最低工资,平均工资。
在这里插入图片描述
有了group by之后就把role相同的记录放到同一组中,avg就是针对每个组分别来求平均值。
group by 中也可以结合一些条件对数据进行进一步的筛选,不是使用where,而是使用having。
查找除所有平均工资高于250的岗位和平均薪资。
在这里插入图片描述
having是针对group by之后的结果进行筛选。
where是针对原始表中的每条记录都进行筛选。

联合查询/多表查询

实现联合查询的基本机制:笛卡尔积
学生表id name courseid
1 曹操 1
2 刘备 3
3 孙权 4
课程表 id name
1 语文
2 数学
3 英语
4 物理
笛卡尔积的结果
在这里插入图片描述
笛卡儿积就会得到12条记录,两张表每条记录进行排列组合的结果。
多表查询的过程,先计算多个表的笛卡尔积,再基于一些条件针对笛卡尔积中的记录进行筛选。
如果针对两个比较大的表进行联合查询,笛卡尔积的计算开销会很大,最终的查找效率也就较低。
a)查找名为“许仙”的同学的所有成绩
姓名包含在student表中
分数包含在score表中
就需要针对这两个表进行联合查询。
进行多表查询时,写列的时候要写成[表名][列名]
查找过程如果拆分开:
1.先找到名为许仙的id
2.拿着id去score表中查询
在笛卡尔积中发现,大量的数据其实是没啥意义的,student的表中的Id和score表中的student_id 字段对应,才是有意义的。
实际的解决思路
1.先把两张表联合在一起,得到笛卡尔积
2.按照student id 对笛卡尔积的记录进行筛选,保留有意义的数据
3.再针对名字进行筛选。
b)查找所有同学的总成绩,以及该同学的基本信息。
同学信息是在student中
成绩在score表中还是需要针对student和score来进行联合(笛卡尔积)
筛选条件:
1.按照学生id来筛选,干掉笛卡尔积中的不必要的数据
2. 按照学生id进行group by,求每个同学的总成绩
在这里插入图片描述
select中如果没有指定order by语句,得到的结果顺序都是不确定的。
c)查找所有同学的每一科成绩,和同学的相关信息,最终的效果需要显示:同学姓名,科目名称,对应的成绩。

本图文内容来源于网友网络收集整理提供,作为学习参考使用,版权属于原作者。
THE END
分享
二维码

)">
< <上一篇
下一篇>>