作者简介:大家好,我是未央;
博客首页:未央.303
系列专栏:Mysql系列
每日一句:人的一生,可以有所作为的时机只有一次,那就是现在!!!
前言
今天我们将进入到MySQL表的增删改查(进阶)的学习当中,本节内容对MySQL表的增删改查改进行了进一步的学习,在基础上进行一些进阶操作的学习,难度也会有所增加,希望各位能够认真对待,一起学好本节内容!!!!!!!
本节目标
数据库约束表的关系新增删除修改查询
一、数据库约束
约束的定义:数据库可以让程序员定义一些对数据的限制规则,数据库 会在 插入/修改数据的时候,会按照这些规则 对数据进行校验;如果校验不通过,就直接报错;
约束的本质: 就是让我们及时发现数据中的错误,更好的保证数据的正确性;
1.1 约束类型
1.2 null约束
用法示例:
1.此时,先创建一张 student表:
2.这张表,初始情况下,没有任何的约束,允许为null:
3.此时,我们完全可以插入空值:
但是,如果我们加上 not null,那么就不会插入成功了
1.删除上面所创建的 student表:
2.重新创建一个 student表(有约束条件 not null):
3.查看 student表 的结构,发现此时 id列不允许为空(2中仅仅约束了id):
4.此时,如果再插入空值的时候,会提示报错:id列不允许为空值:
1.3 unique约束
定义:
用法示例:
1.首先,删除 原来的 student表:
2.先创建一个普通的表(无任何约束):
3.当插入多条相同的记录,发现插入记录都成功了:
4.此时,查询 student表 的全列数据:
解析:
可以看到的是,创建的是普通的表(无unique约束),
此时,是完全可以插入普通的数据的,默认情况下,表里的数据都是可以重复的;
但是,当重新创建一个 带有unique约束的 student表时,则会发现出错了;
1.先删除原来的表;现在,重新创建一个 带有unique约束的 student表:
2.此时,观察 student表 的结构,发现id只能是唯一值:
3.此时,向 student表里面 插入相同数据,会发现出错了(第一条插入没问题,第二条开始就出错了):
单词注释:duplicate:重复 entry:条目
1.4 default约束
定义:
当我们插入数据的时候,如果不去指定,那么插入的时候就会自动去填写这个默认值
默认情况下的默认值,是null,可以通过 default约束 来修改默认的默认值
代码示例:
1,删除旧的 student表:
2.重新创建一张新的 student表(不加任何限制):
3.此时我们可以查看 student表 的结构:
4. 当我们对表进行指定列插入的时候,就会涉及到默认值的情况的:
5.此时,我们只针对 id列进行插入,那么 剩下的 name列 就按照默认值null来走的:
但是当我们可以通过 default约束 来修改默认值
1.重新删除 student表:
2.创建一张带有 default约束 的 student表:
3.此时,可以查看表的结构:
4.那么,此时可以插入数据来观察:
1):当只插入id=1,那么name就是默认值;
2):只插入name为‘张三’的话,id就是默认值:
1.5 primary key 约束
定义:
primary key 约束,又叫做:主键约束,这是在设计表的时候,非常重要的一个列
主键表示一条记录的 "身份标识" ,用来区分 这条记录 和 其他的记录
- 主键不能为空,相当于 not null
- 主键不能重复,相当于 unique
- 一个表里只能有一个主键
代码示例说明:
1.首先,删除 原来的 student表:
2.创建一张带有主键的 student表(一般情况下 把 id 当作主键):
3.此时,可以查看 student表 的结构,会发现也出现了变化:
注意:
当插入数据的时候,主键不允许为空值,同时主键必须唯一
(这里的主键指的是 普通主键);
1):当主键插入为空时,就会发生报错:
代码示例:
2):当主键插入的数据不唯一时,也会发生报错;
有的人可能会说:主键不能为空,而且必须是唯一的,
也就是说,主键必须填,而且还不能是重复的
那么以后填主键的时候,是不是还必须的要好好琢磨琢磨呢
于是,MySQL为了方便大家填写主键,内置了一个功能 —— "自增主键" ,帮助我们自动生成主键的值,就不用程序员自己来保证了
自增主键说明:
定义:
自增主键 不是 普通主键,可以手动指定,也可自动生成,是从1开始自增;
当我们填入一个具体值的时候,那就是手动指定;
当我们填入null的时候,那就是自动生成(按照自增规则)
代码示例说明:
1.首先,删除原来的 student表:
2.创建一张新的 student表(内含 自增主键,自增主键 不是普通主键):
3.此时,查看 student表 的结构:
4.现在,插入一些数据:
解释说明:
自增规则:
MySQL要想自增,必须要能够记录下来当前 id已经到哪了;
还要保证 自增 之后,得是不能重复的;
MySQL里简单粗暴的做法,直接就是记录当前 自增主键 列里的最大值;
这就能保证自增速度佷快。并且一定是不重复的;
1.6 foreign key 约束
定义:
foreign key 约束,又称外键约束,描述了 两张表之间的关联关系
解释说明:
现在有两张表学术表和班级表;
学生表 里面存储着 每个同学的信息(其中有一列是 班级编号);
班级表 里面存储着班级的具体信息;
学生表中的每个同学的班级编号,需要在班级表中存在,如果不存在 就是非法数据!!!
其中,班级表 是负责约束的一方,称为 父表;
学生表 是被约束的一方,称为 子表;
代码举例说明:
1.首先,删除原来的 student表:
2.接着,创建 class表 和 student表(具有外键约束关系):
class表:
student表:
3.此时,我们可以看看 student表 的结构:
![]()
4.插入数据:
在父表为空的情况下,直接尝试往子表插入,就会报错!!!
5.如果向父表里面插入几条记录之后,再次尝试向子表里面插入数据:
6. 如果 插入子表的数据(4)没有在父表的 classId(1,2,3)中,同样也会报错:
这就意味着 在外键约束下,每次 插入/修改操作,都会先触发在父表中的查询,父表中存在才能 插入/修改成功; 否则就会失败
此处的插入子表的数据(1)在父表classld(1,2,3)中存在,所有插入成功;
二、表的设计
定义:
设计表:主要就是确定 有几个表,每个表有多少个字段;
怎么来进行表的设计?
- 明确需求场景
- 提取出需求的场景;实体可以认为是"关键性的名词"
- 理清楚"实体"与"实体"之间的关系:一对一、一对多、多对多
- 不同的关系,在设计表的时候,有不同的套路!!!
2.1 “一对一”关系
事例说明:
以学校的教务系统来举例,在教务系统里面,有许多的 "学生",也有许多的 "账户" ;
此时,"学生" 和 "账户" 就是 "一对一" 的关系;
一个 "学生" 只能有一个 "账户",一个"账户"只能分配给一个"学生",这种关系就叫做 "一对一" 关系
代码说明:
此时的套路有:
第一种套路:把学生和账户,直接放到一个表里
如:student(id,name,account,password......)
第二种套路:把学生和账户各自放到一个表里,然后使用一个额外的 id 来关联就可以了
如:
当一个系统中包含不同身份角色的时候 ,一般采用第二个套路;
2.2 “一对多”关系
事例说明:
同样, 以学校的教务系统来举例,在教务系统里面,有许多的 "学生",也有许多的 "班级" ,此时,"班级" 和"学生" 就是 "一对多" 的关系
一个 "学生" 只能属于一个 "班级",但是一个"班级" 却可以包含多个"学生",这种关系就叫做 "一对多" 关系
代码说明:
套路1:
套路2:
以上套路都可以表示, 一个 "学生" 只能属于一个 "班级",但是一个"班级" 却可以包含多个"学生"
但是,在MySQL中 没有"数组"这样的类型,所以说,第一种套路 是无法实现的(虽然可以使用 字符创拼接 的方式来凑合着实现,但是实际上是不好的设计,比较低效,也失去了数据库对于数据校验的一些能力)????????????????
2.3 “多对多”关系
事例说明:
同样, 以学校的教务系统来举例,在教务系统里面,有许多的 "学生",也有许多的待选的 "课程" ,此时,"学生" 和 "课程" 就是 "多对多" 的关系
一个 "学生" 可以选择多个 "课程",同时一个"课程" 也可以 被 多个"学生"选择 ,这种关系就叫做 "一对多" 关系 ;
代码实例:
一般就是采用一个中间表,来表示"多对多"的关系:
注意:
如果需求场景比较简单,很容易就能梳理其中的实体和关系;
如果需求场景比较复杂,可能涉及到很多很多实体,很多很多组关系,就容易搞乱;
三、新增
定义:
会把查询的结果 作为 新增的数据;
语法格式:
insert into 表名1 select 列名 from 表名2;
解析:
1.先执行的是 查询操作 select 列名 from 表名2;
2.查询出来的结果 插入到另外一个表1里;
3.需要保证 查询结果的临时表2的列数和类型 要和要插入的表1得匹配!!!
4.不要求列名匹配
代码举例:
1.首先,还是需要把 java数据库里面的表都已经删除干净
![]()
2.此时,可以创建一个 student表 并且插入一些相关的数据:
3.接着,再创建一个 student2表:
4.我们可以来看看 student表 和 student2表 里面的数据:
![]()
5.此时,我们可以用 student表 把数据导入到 student2表 里面:
6.最后,可以观察 student2表 里面的数据:
当然,自己也可以插入自己:
四、查询
4.1 聚合查询
4.1.1 聚合函数
定义:
如果说,之前介绍的 "表达式查询" 是把 列和列之间进行一些运算处理;
那么 现在所要介绍的 "聚合查询" 就是通过行和行之间进行这样的运算;
聚合查询,需要接着一些相关的聚合函数来进行查询
常见的聚合函数有:
各个聚合函数的用法:
1.count
代码举例:
1.首先,我们需要来创建一张 exam_result表,并且插入一些数据:
2.接着,我们可以查看 创建好的exam_result表:
3.此时,我们可以用聚合函数count了:
针对name列
这里有一个小细节,当我们向 exam_result表 里面插入这样一条 全部都是null的记录:
此时,count(*)是把所有的记录都记录起来了,而count(name)是把所有的非空记录都记录起来了:
当然,别名啥的也是可以照常用的:
需要注意的是,count后面的括号 与 count 之间不允许有空格,否则就会报错:
但是,count后面的括号 里面 是可以存在空格的:
2.sum
代码举例:
从此可以看出,sum在运算的时候会自动忽略null而进行一系列运算
当然,此处的聚合查询,也是完全可以指定筛选条件:
带有条件的 聚合查询,会先按照条件进行筛选;
筛选后得到的结果,再进行聚合;
计算英语及格的同学的 英语总成绩:
如果不是数字的话,那么计算结果就没有意义:
3.avg
代码举例:
计算数学成绩的平均分:
平均值 = 总分/个数
此处的 个数 = 7;
联想现在高中考试的时候,如果有个同学因为拉肚子而缺考了,那么老师在计算平均分的时候 肯定是不会把他计算过去的。
4.max
代码举例:
查找数学成绩中最高的值:
5.min
代码举例:
查找数学成绩中最低的值:
4.1.2 分组查询(group by 子句)
定义:
把表中的若干行,分成好几组;指定某一列作为分组的依据;
分组依据的列的值相同,则被归为一组;分成多个组之后,还可以针对每个组,分别使用聚合函数;
注意:
使用 group by 进行分组查询时, select 指定的字段必须是 分组依据字段,其他字段若想出现在 select 中则必须包含在聚合函数中。
比如说,select指定的字段 不是 分组依据字段,查出来的结果不报错,但是查出来的结果 是无意义的结果。
那些其它字段里面的值,如果不用聚合函数来操作的话,就是单纯查一列,得到的就是每一个分组里面的第一条记录。
代码举例:
1.首先,把 java数据库里面的表删除:
2.接着,重新创建一个 student表,并且插入一些数据:
3.然后,我们就可以来看一看 student表 中的数据:
现在,我们就可以来试一试 分组查询了:
需要统计:男生 和 女生 各自的最高分、最低分、平均分
说明:
此时,根据性别,男生是一组,女神是一组;
然后再这两组里面,分别查出各自的最高分、最低分、平均分;
4.1.3 having 子句
定义:group by 子句进行分组以后,需要对分组结果再进行条件过滤时,不能使用 where 语句,而需要用having
在进行聚合查询的时候,也能指定条件筛选:
- 在聚合之前,进行筛选,针对筛选之后的结果,再聚合筛选(使用 where 子句 限制条件)
- 在聚合之后,进行筛选 (使用 having 子句 限制条件)
- 同时在前面和后面都筛选(where 子句 + having 子句)
聚合之前筛选:
查询每个性别平均分,但是除去赵六同学(先把 赵六 排除掉,再聚合筛选)
聚合之后筛选:
查询平均分大于 80 的性别的情况(需要先把平均分算出来,找出平均分大于 80 的)
同时在前面和后面都筛选:
查询平均分大于 80 的性别情况(除去张三同学)
聚合查询的过程:
1.先按照 where 条件进行筛选记录
2.把筛选出来的结果按照 group by 来分组
3.分组之后按照 having 再进行筛选
4.最后再按照指定列中的调用的聚合函数筛选
4.2 联合查询(最复杂的)
定义:联合查询,也叫做 多表查询,即 把多个表的记录 合并到一起,综合进行查询;
4.2.1 笛卡尔积
在介绍 联合查询 之前,需要介绍一下联合查询中的核心概念:笛卡尔积;
通俗的来说,笛卡尔积就是简单的排列组合;
笛卡尔积,就是把这两张表中的所有记录,进行排列组合,穷举出所有的可能情况
即:即:
规律:
笛卡尔积的列数,就是原来两张表的列数之和;
笛卡尔积的行数,就是原来两张表的行数之积;
针对多个表进行联合查询,本质上就是先针对 多个表进行笛卡尔积运算!!!
万一原来的表就很大,再进行多个表的笛卡尔积,就会得到一张更大更复杂的表,这个过程会很低效,所以在实际开发中 进行多表查询的时候,一定要克制!!!
在上述的笛卡尔积中,学生表的classId 和 班级表的classId 相等的记录,肯定是比较合理的(能够符合真实的情况),其他的都是排列组合的副产物;
所以说,单纯的笛卡尔积 可能并不怎么实用,但是 笛卡尔积 加上 一些合理限制条件的筛选,
把有效的数据筛选出来,这个时候就得到了一张非常有效的数据表这个过程,
就正是 "联合查询" 的过程
像上述的 "学生表的classId 和 班级表的classId 相等" ,称为 "连接条件";
联合查询需要涉及到多个表,所以需要构造一下数据
首先需要删除 java数据库 里面的表:
接着就可以创建 classes表、student表、course表、score表,并且插入各种数据:
create table classes ( id int primary key auto_increment, name varchar(20), `desc` varchar(100) ); create table student ( id int primary key auto_increment, sn varchar(20), name varchar(20), qq_mail varchar(20) , classes_id int ); create table course( id int primary key auto_increment, name varchar(20) ); create table score( score decimal(3, 1), student_id int, course_id int ); insert into classes(name, `desc`) values ('计算机系2019级1班', '学习了计算机原理、C和Java语言、数据结构和算法'), ('中文系2019级3班', '学习了中国传统文学'), ('自动化2019级5班', '学习了机械自动化'); insert into student(sn, name, qq_mail, classes_id) values ('09982', '黑旋风李逵', 'xuanfeng@qq.com', 1), ('00835', '菩提老祖', null, 1), ('00391', '白素贞', null, 1), ('00031', '许仙', 'xuxian@qq.com', 1), ('00054', '不想毕业', null, 1), ('51234', '好好说话', 'say@qq.com', 2), ('83223', 'tellme', null, 2), ('09527', '老外学中文', 'foreigner@qq.com',2); insert into course(name) values ('Java'),('中国传统文化'),('计算机原理'),('语文'),('高阶数学'),('英文'); insert into score(score, student_id, course_id) values -- 黑旋风李逵 (70.5, 1, 1),(98.5, 1, 3),(33, 1, 5),(98, 1, 6), -- 菩提老祖 (60, 2, 1),(59.5, 2, 5), -- 白素贞 (33, 3, 1),(68, 3, 3),(99, 3, 5), -- 许仙 (67, 4, 1),(23, 4, 3),(56, 4, 5),(72, 4, 6), -- 不想毕业 (81, 5, 1),(37, 5, 5), -- 好好说话 (56, 6, 2),(43, 6, 4),(79, 6, 6), -- tellme (80, 7, 2),(92, 7, 6);
此时,可以看一下各种表的数据:
由上表可以知道,
4.2.2 内连接
那么,现在就可以来进行一些关联查询了:
1️⃣查询 "许仙" 同学的成绩:
许仙 是名字,student表里面有名字;
成绩 是分数,score表里面有分数;
先笛卡尔积,再条件筛选!!!
--先笛卡尔积,再条件筛选!!! --笛卡尔积 的写法: select * from 表1,表2; --再进行条件筛选(这道题目中,很明显 两张表里面的 id/student_id 是一个意思,所以 可以将其 作为连接条件)
在多表查询中,可能多张表的列名 是一样的(如果两张表 都是 id一样呢)
那么可以通过 表名.列名 的方式来做区分(如上一张表)
过滤掉 无效数据之后,剩下的数据就已经清晰多了
都是每个同学,每门课的成绩
最初是要求查询 "许仙"同学的成绩,还需要加上一个条件:
最后一步,省略不必要的列,只保留必要的列即可:
注意:
多表查询的一般步骤:
- 先根据需求理清楚想要的数据都在哪些表中;
- [核心步骤] 先针对多个表进行笛卡尔积;
- 根据连接条件,筛选出合法数据,过滤掉非法数据;
- 进一步增加条件,根据需求做出更精细的筛选;
- 去掉不必要的列,保留最关注的信息!
--通过
select 列名 from 表1,表2 where 连接条件 and 其他条件;
--这种方式 可以进行多表查询
--除此之外,还有一种写法:
select 列名 from 表1 [inner] join 表2 on 连接条件;
--第二种写法 和 第一种写法 效果上是一样的,但是语法上稍有区别~~
注意:
第一种写法中 select 列名 from 表1,表2;是笛卡尔积的写法
第二种写法中 表1 [inner] join 表2; 也是笛卡尔积的写法
如果只有2张表的话,表1 join 表 2 起到笛卡尔积的效果;
如果是有3张表的话, 表1 [inner] join 表2 [inner] join 表3 就起到了笛卡尔积的效果;
join 多个表之后,最后来一个 on 统一写出筛选条件就可以了
例子:
当使用 join 计算笛卡尔积的时候,后面的条件使用 on 来表示,不是 where,这个相当于"固定搭配"
既然有 from 多个表,已经很简单很好用,为啥还要整个 join on 出来?
join on 默认情况下的行为和 from 多个表 是一致的,但是 join on 还能延伸出一些其他用法,功能就比 from 多个表 更广泛了
2️⃣查询所有同学的总成绩,及同学的个人信息:
在上面的第一题当中,已经知道了每一个同学,每门课的分数情况:
接下来针对行和行之间进行计算,就需要进行聚合查询
不能说提到总成绩,就立即想到 表达式查询加起来,需要根据实际的表结构,见招拆招
在不使用聚合函数的情况下,后面这部分显示出来的结果 相当于每一个分组中的第一条记录
3️⃣查询出每个同学的姓名、课程名、分数
首先,对 student表、course表、score表 进行笛卡尔积:
接着,加上必要的连接条件:
最后,修改所需要留下的列:
4.2.3 外连接
--左外连接
select 列名 from 表1 left join 表2;
--右外连接
select 列名 from 表1 right join 表2;
举例:
1.首先,把之前创建的表删除:
2.然后重新创建 student表、score表,并且插入一些数据:
3.接着,查看 student表 和 score表 的数据:
4.如果说,两张表的数据记录都是一一对应的对应,那么可以看见,内连接 和 外连接 的结果是一样的:
那么,如果把 score表 的 (3,70) 改成 (4,70) ,使得 student表 和 score表 数据记录不是完全一一对应的:
从学生表的角度看,3号同学王五 在 分数表里面没有成绩;
从分数表的角度看,4号同学 没有身份信息;
此时,内连接 和 外连接 的查询结果就会有所区别了:
就好比于这样:
4.2.4 自连接
定义:
自连接,即 自己和自己进行连接,同一张表 自己和自己进行笛卡尔积
这个操作是一种 "奇淫巧技",很少会用到,属于 特定情况下的特定问题的特殊解决手段!!
SQL里面的指定条件筛选,都是按照列和列之间进行筛选的(如 a列 < b列),难以进行行与行之间筛选
自连接操作就能够 把行转化成列
例子:
显示所有 "计算机原理"成绩 比 "Java"成绩 高的成绩信息:
1.现在删除已有的 student表、score表:
2.继续创建刚刚删除的四张表,插入刚刚删除的信息
create table classes ( id int primary key auto_increment, name varchar(20), `desc` varchar(100) ); create table student ( id int primary key auto_increment, sn varchar(20), name varchar(20), qq_mail varchar(20) , classes_id int ); create table course( id int primary key auto_increment, name varchar(20) ); create table score( score decimal(3, 1), student_id int, course_id int ); insert into classes(name, `desc`) values ('计算机系2019级1班', '学习了计算机原理、C和Java语言、数据结构和算法'), ('中文系2019级3班', '学习了中国传统文学'), ('自动化2019级5班', '学习了机械自动化'); insert into student(sn, name, qq_mail, classes_id) values ('09982', '黑旋风李逵', 'xuanfeng@qq.com', 1), ('00835', '菩提老祖', null, 1), ('00391', '白素贞', null, 1), ('00031', '许仙', 'xuxian@qq.com', 1), ('00054', '不想毕业', null, 1), ('51234', '好好说话', 'say@qq.com', 2), ('83223', 'tellme', null, 2), ('09527', '老外学中文', 'foreigner@qq.com',2); insert into course(name) values ('Java'),('中国传统文化'),('计算机原理'),('语文'),('高阶数学'),('英文'); insert into score(score, student_id, course_id) values -- 黑旋风李逵 (70.5, 1, 1),(98.5, 1, 3),(33, 1, 5),(98, 1, 6), -- 菩提老祖 (60, 2, 1),(59.5, 2, 5), -- 白素贞 (33, 3, 1),(68, 3, 3),(99, 3, 5), -- 许仙 (67, 4, 1),(23, 4, 3),(56, 4, 5),(72, 4, 6), -- 不想毕业 (81, 5, 1),(37, 5, 5), -- 好好说话 (56, 6, 2),(43, 6, 4),(79, 6, 6), -- tellme (80, 7, 2),(92, 7, 6);
(1)先找到 "计算机原理" 和 "Java" 的课程的 id ,发现 "Java" 课程 id 是 1,"计算机原理" 课程 id 是 3:
(2)肉眼观察一下 在 score表 中哪些符合情况:
(3)对score表 进行 笛卡尔积 (score表 和 score表 分为起别名为 s1 和 s2):
4)筛选条件 是用 学生id比较合理!!!
因为,需求是找到哪个同学的 3号课程比1号课程成绩高
(5)当学生 id 对齐以后,我们发现 课程 id 仍然在排列组合
但是,我们只想要的是 课程 id 不是 3 就是 1 的记录(左边 id 为 3,右边 id 为1 或者是 左边 id 为 1,右边 id 为3)
(6)接着 需要添加一下比较条件,3号课程 id 分数 大于 1号课程 id 分数 即可:
(7)最后,精简一下列就可以了:
4.2.5 子查询
定义:所谓的子查询,其实就是套娃
单行子查询:返回一行记录的子查询
例子:查询与 "不想毕业" 同学的同班同学:
(1)根据 名字 获取到 班级id
(2)根据 班级id 查询 id 匹配的同学(同班同学)
(3) 接下来就可以把两步SQL 写成 一步SQL:
子查询如果只有1个结果,直接使用 = 即可
子查询如果包含了多个结果,就可以使用 in 了,嗯,有的时候使用的是 not in
查询 "语文"或"英语" 课程的成绩信息:
先根据课程名,知道课程的 id,再拿着课程的 id 去分数表里面查询
4.2.6 合并查询
定义:
合并查询,就是 把两个查询结果的结果集合,合并到一起
主要是使用 关键字 union 这样的关键字来实现
查询id小于3,或者名字为“英文”的课程
使用 or:
使用 union:
区别:
使用 or 只能是针对一张表,使用 union 可以针对的是两(多)张表(保证列需要匹配)
注意:
union 操作 如果有重复的记录,它会自动去重;
如果不想去重的话,把 union 换成 union all 即可