目录
一、数据库的约束
NOT NULL约束
UNIQUE(唯一约束)
DEFAULT:默认值约束
PRIMARY KEY:主键约束
FOREIGN KEY:外键约束
由外键约束而延伸出来的问题——电商
check约束(可以直接对表中的值进行限制) (了解)
二、表的设计
三、新增
四、聚合查询
聚合函数(不区分大小写)
分组查询(GROUP BY)
五、联合查询
一、数据库的约束
约束类型(以下不区分大小写)
- NOT NULL - 指示某列不能存储 NULL 值。
- UNIQUE - 保证某列的每行必须有唯一的值。
- DEFAULT - 规定没有给列赋值时的默认值。
- PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列(或两个列多个列的结合)有唯一标识,有助于更容易更快速地找到表中的一个特定的记录
- FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。
- CHECK - 保证列中的值符合指定的条件。对于MySQL数据库,对CHECK子句进行分析,但是忽略CHECK子句
NOT NULL约束 初始情况下,一个表允许为NULL
文章图片
如果我们在创建表的时候,指定某指定某列不为空,则该表的这列的值不能为空——即插入空值就会报错
文章图片
UNIQUE(唯一约束)
默认情况下,表里的数据是可以重复的,当如果该列的数据被unique修饰,则该列的数据不能重复,如果插入重复的数据就会报错
文章图片
DEFAULT:默认值约束
当某一列的值为空时,默认的默认值为NULL,但我们可以通过default约束来修改这里的默认值的取值
文章图片
PRIMARY KEY:主键约束
首先我们要知道 primary key 主键,这是我们在设计表的时候,一个非常重要的列栗子
主键表示一条记录的身份标识,用来区分这条记录和别的记录
他有以下特点
1)不能为空, 相当于NOT NULL
2)不能重复, 相当于UNIQUE
3)一个表中只能有一个主键
文章图片
~~~我是分割线~~~~
文章图片
栗子
由于主键必须自己填,还不能重复,MySQL为了方便大家填写主键,内置了一个功能——自动主键(从1开始依次进行累加),帮助我们自动生成主键的值。
-- 主键是 NOT NULL 和 UNIQUE 的结合,可以不用 NOT NULL
id int primary key auto_increment
文章图片
FOREIGN KEY:外键约束 外键用于关联其他表的主键或唯一主键,它描述了两张表之间的关联关系
其中负责约束的一方,称为父表。被约束的一方,称为子表。
foreign key (字段名) references 父表(列);比如我们要表示学生表和班级表的对应关系,那么班级编号就是一个关键点
其中foreign key(字段名)填写的是本表(子表)的外键, references 父表(列)填写的是其他表的主键
一般来说,都是负责约束的一方(父表)先进行构造,再来构建约束的一方(子表),想想看是先有了约束条件才能被约束
文章图片
眼尖的伙伴可能会发现父表和子表好像都有一个classId,不是说列名不能重复吗?那两个表产生关联和约束,有什么用呢?
在同一张表内,列名的确不能重复,但这是在两个不同的表中呀!在这里我们学生表中的classId是外键,关联了班级表的主键classId。
首先当你在被约束表(子表)中插入数据的时候,在外键约束下,每次你进行插入或修改操作的时候,都会触发在负责约束的父表中的查询。只有该外键在父表中存在时才能插入或修改成功,不信你看~~~
如果先该父表中创建了子表中该外键所对应的关联主键,就可以插入了
文章图片
父表对子表的限制是不能随意的插入和修改,就比如说人家父表中根本没有这个班级,那你在子表中还怎么插入到这个班级中。
同样的负责约束的父表也会收到被约束的子表的限制,子表对父表的限制是不能随意的修改和删除,就比如说人家子表中的学生都在这个班级中了,你在父表中突然把这个班级的编号给改了、甚至直接给删除了,这合理吗?
文章图片
注意
外键约束要搭配索引,我们上面的是主键,主键自动带索引
文章图片
由外键约束而延伸出来的问题——电商
商品表(good_id, name, price)
订单表(order_id, time, good_id)
这里面其实也存在着外键约束,商品表对订单表就有着约束。其中商品表是父表,订单表是子表。你不能订商品表中没有的商品(good_id),商品表中的商品也不能随意的下架(可能人家还在订购你这个商品呢)
那么想象一下这样一个情景:在一家店铺中,你下过了很多订单,订单表中已经有了一些商品数据,比如你买了一堆衣服,衣服的good_id是100。然后过了一段时间,要换季了,需要之前的商品给下架。在上述外键约束的条件下,如何删除商品表中100的记录。
我们可以进行假的删除===》逻辑上的删除(物理上还存在)
可以在商品表中设置一个判断商品是否下架的字段
商品表(good_id, name, price, ok);
约定ok为1的时候表示商品在线
约定ok为0的时候表示商品下架
check约束(可以直接对表中的值进行限制) (了解)【MySQL学习|《MySQL学习笔记》数据库增删查改(进阶)】
文章图片
二、表的设计
在工作中,我们通常会根据一些时间的业务场景,来设计表——主要就是确定有几个表,每个表干什么,每个表有哪几个字段在需求中,我们要学会提取出需求中的实体,实体可以认为是:"关键性的名词",一般来说每个实体都对应着一张表,我们要看到实体与实体间的对应关系——即表与表之间的对应关系
比如当面试官问你:你的项目里的数据库是如何设计的?
其实面试官是想让你回答,你的数据库里有哪几个表,每个表是干什么的,以及表中有哪些字段
这些关系可以分为:一对一、一对多、多对多、没关系
文章图片
一对一
文章图片
一对多(和多对一一个意思)
文章图片
多对多
文章图片
总结
文章图片
三、新增 把查询结果作为新增的数据
insert into 表名 (列名, 列名....) select 列名 from 表名;
insert into 表一 select * from 表二;
insert into 表一 select * from 表二 where 条件
栗子
先执行查询查询操作,查询出来的结果,插入到另外一个表里
这里需要注意的是:我们要保证查询结果的临时表的列和要插入的表的列要匹配上
文章图片
栗子
文章图片
栗子
文章图片
四、聚合查询
聚合函数(不区分大小写)
常见的统计总数、计算平局值等操作,可以使用聚合函数来实现,常见的聚合函数有:
函数 | 说明 |
COUNT([DISTINCT] expr) | 返回查询到的数据的 数量 |
SUM([DISTINCT] expr) | 返回查询到的数据的 总和,不是数字没有意义 |
AVG([DISTINCT] expr) | 返回查询到的数据的 平均值,不是数字没有意义 |
MAX([DISTINCT] expr) | 返回查询到的数据的 最大值,不是数字没有意义 |
MIN([DISTINCT] expr) | 返回查询到的数据的 最小值,如果不是数字就没有意义 |
文章图片
分组查询(GROUP BY)
把表格的若干行,分成好几组~~,指定某一列作为分组的依据。在分组时,如果该列的值相同,则被归为一组。
分成多个组之后,还可以针对每个组,分别使用聚合函数
文章图片
在进行聚合查询的时候,也能指定条件进行筛选
1)在聚合之前,进行筛选——即先筛选,再聚合==》where 子句
2)再聚合之后,进行筛选——即先聚合,再筛选==》having 子句
栗子
文章图片
上述的过程就是
1、先按照where的条件进行筛选
2、把筛选出来的结果按照group by来分组
3、最后调用聚合函数avg、max
栗子
文章图片
栗子
同时再前面和后面进行筛选(查询平均分大于80的情况)去除张三
文章图片
五、联合查询
联合查询也叫多表查询,把多个表的记录合并到一起,综合进行查询~~~
联合查询的核心概念——笛卡儿积(把要查询的各个表中的记录进行排列组合,穷举出所有可能的情况~~~
文章图片
文章图片
sql中计算笛卡儿积
select * from 表1,表2;
文章图片
栗子
比如你现在有以下几张表,你想要查询许仙同学的成绩
许仙是名字,student表中有名字
成绩是分数,score表中有分数
文章图片
第一步:建立计算产生关联的这两个表的笛卡儿积
文章图片
第二步:初步筛选
文章图片
在筛选的时候,我们要注意多张表的列名,很可能是一样的!!可以通过:表名.列名的方式来做区分
文章图片
第三步:按名字进行进一步的筛选
当我们初步过滤掉无效数据后,剩下的数据已经清晰多了~~每个同学,每门课的成绩~~
接下来,在对名字进行筛选,我们要的是许仙同学的成绩
文章图片
第四步:省略不必要的列,只保留必要的列
文章图片
总结
文章图片
推荐阅读
- 数据库|数据库增删查改进阶
- C++后台开发|7 张图,轻松掌握零拷贝原理
- postgresql|一小时搭建属于属于自己的博客系统网站
- php|用xampp搭建PHP运行环境(一)
- 数据库|微信小程序·云开发 云数据库的使用教程
- 微信小程序|微信小程序-如何获取云数据库某集合数据并显示在页面
- linux|如何进行mysql下的严格模式修改,使得使用插入用户表方式添加新用户成功
- 网络安全|mysql中的视图、触发器和存储过程
- 函数|sql中的substr与substring函数用法