> 文档中心 > MySQL数据库的约束+进阶版新增与查询

MySQL数据库的约束+进阶版新增与查询

✨前言✨

📘 博客主页:to Keep博客主页
🙆欢迎关注,👍点赞,📝留言评论
⏳首发时间:2022年3月21日
📨 博主码云地址:博主码云地址
📕参考书籍:java核心技术 卷1
📢编程练习:牛客网+力扣网
由于博主目前也是处于一个学习的状态,如有讲的不对的地方,请一定联系我予以改正!!!

文章目录

  • 数据库约束的类型
    • NOT NULL型
    • UNIQUE 型
    • DEFAULT:默认值约束
    • PRIMARY KEY:主键约束
    • FOREIGN KEY:外键约束
  • 新增操作
  • 聚合查询
    • 聚合函数
    • group by子句
    • having使用
  • 联合查询(多表查询)
    • 笛卡尔
    • 多表查询(内连接)
      • 示例导入
      • 查询
    • 多表查询(外连接 join on关键字)
      • 连接示例:
      • join on 内连接
      • join on 左外连接
      • join on 右外连接
  • 总结

数据库约束的类型

NOT NULL型

某一列不能存储null值,在创建表的时候,将不能为空的列加上这个约束
create table 表名(属性 类型 not null,…)
本示例就以学生的id不能为空演示

在这里插入图片描述
下面插入数据:

MySQL数据库的约束+进阶版新增与查询

UNIQUE 型

保证某列的每行必须有唯一的值,也是在创建表的时候进行约束

在这里插入图片描述
测试用例:
MySQL数据库的约束+进阶版新增与查询

DEFAULT:默认值约束

在这里插入图片描述
下面设置默认值:
在这里插入图片描述

如果我们在创建表的过程中,如果name输入为空,那么此时表中会把其设置为默认值匿名

PRIMARY KEY:主键约束

是一个很重要的约束条件,对于一个表来说,只能指定一列来作为主键约束,并且我们可以发现主键约束的这一列不允许重复,也不允许为空,结合了unique和not null约束

在这里插入图片描述

下面我们在深入的了解一下,自增主键,这个一般适用于序号的增加
在这里插入图片描述
测试用例:
在这里插入图片描述

注意自增主键也是可以自己手动设置的,如果你这次特别指定了这次id从10开始,那么后面插入的数据将会从11开始插起,10之前没用的就用不了了。

FOREIGN KEY:外键约束

外键约束针对的是两张表而言的,将两张表进行关联,这里我们以学生表和班级表为例,学生表中有classsort对应着班级表的id,通过id就可以把学生表和班级表相关联起来了

在这里插入图片描述

在这里插入图片描述

学生表是依赖于班级表的,所以我们先创建一个班级表,学生表就称为子表,班级表被称为父表,我们可以利用外键约束,将学生表中的classid与班级表中的id相关联即可
本例中的外键约束sql语句如下:

create table student(id int primary key auto_increment,name varchar(20),classid int ,foreign key (classid) references class(id));

在这里插入图片描述
注意:
1 外键约束是两张表之间的关联,所以子表中的记录(classid)要在父表中存在(id)
2 对子表插入(修改),其范围都必须在父表之内(本例中,班级表中的id总共有3个,那么在给子表classid输入值的时候,只能在1到3之间)
3 对于父表,如果子表引用了其中的属性(id),那就不可以删除和更改,除非子表是没有对那个属性引用(如果父表中现在有个id为4的,子表没有引用这个id),那么才可以操作!

外键约束的工作原理

在对子表插入新的数据的时候,会根据对应的值前往父表查询,查询到了之后才可以进行插入操作,这里的查询就是依据索引来完成的(外键约束就要求父表被引用的这一列必须有索引,索引会根据primary key或者unique自动创建索引)

新增操作

这里的新增操作不是之前所学的一列一列插入,而是整表的进行插入,但是有一点必须注意,类型必须对应上

insert into 表1 select 表2中选取对应表1的字段 from 表2;

先创建如下的三个表:
在这里插入图片描述
开始测试:
在这里插入图片描述

第三个表多了一个age,那么我们在插入的时候就和student1的类型匹配上就可以了
另外对于新增操作也可以搭配我们之前学过的分页去重,条件处理

聚合查询

聚合函数

关于聚合查询是不同于我们之前学过的增删查改正对于列而言,这里是针对于行与行的运算

count(字段) 返回查询的数据总量
sum(字段) 返回查询的数据的总和
avg(字段) 返回值是数据的平均值
max(字段) 返回查询数据的最大值
min(字段) 返回查询数据的最小值
这里除了第一个函数意外,其他四个返回值如果不是数字是没有意义的

select 聚合函数 from 表名;

以count为例:
在这里插入图片描述
表示chinese数据有7行
sum函数:
MySQL数据库的约束+进阶版新增与查询
这里的聚合函数搭配了条件语句,计算总分但是是语文成绩大于70分的人的总分

聚合函数对每行的使用,结果计算与null无关,就是假如有第八行数据,第八行的三列成绩设置为null,对其使用聚合函数计算,其计算结果null是不会参与的,依旧还是按照7个人算平均值等
聚合函数也是可以设置称别名的

group by子句

group by子句是根据行的值相等就分为一组,我们可以通过一组示例代码来理解一下什么是group by 子句

测试用例:

create table display(id int primary key auto_increment,name varchar(20) not null,role varchar(20) not null,salary numeric(11,2));insert into display(name, role, salary) values('张三','服务员', 1000.20),('李四','游戏陪玩', 2000.99),('孙悟空','游戏角色', 999.11),('猪八戒','游戏角色', 333.5),('小乔','游戏角色', 700.33),('王五','董事长', 12000.66);

通过角色名来分组: select * from display group by role;

在这里插入图片描述

我们可以发现role这一列有着相同的行,分组之后在第二个表只显示了第一个,这就是根据role来分组,相同的值被分到了同一组,显示了第一个

结合聚合函数对group by进行使用

在这里插入图片描述

从这里可以更好的看出组中的数据,比如游戏角色组中,有三个人,最高的确实是999.11,最低的是333.50

having使用

having是使用在分组之后进行筛选条件,接着引用上面的例子进行使用
执行语句:select role ,avg(salary) as avg from display group by role having avg>1000;

在这里插入图片描述
MySQL数据库的约束+进阶版新增与查询
与where区别

where也是可以筛选条件的,但是where适用的是分组之前,如果对行与行数据分组,那么分组之后必须使用having!

联合查询(多表查询)

笛卡尔积

要想知道多表查询,我们就必须先理解笛卡尔积,笛卡尔积是一种运算,对可能出现的情况进行排列组合,示例如下:

在这里插入图片描述

笛卡尔积的特点是如果表1有n列,表2有m列,那么笛卡尔积后的表有n+m列,以此类推多个表的笛卡尔积的列数,如果表1有a行,表2有b行,那么笛卡尔积后的表有a*b行,以此类推多表进行笛卡尔积的行数

多表查询(内连接)

示例导入

我们先要创建几张表来演示如何进行多表查询,示例如下:

drop table if exists classes;drop table if exists student;drop table if exists course;drop table if exists 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','如来','rulai@qq.com',1),('00835','孙悟空',null,1),('00391','唐僧',null,1),('00031','猪八戒','bajie@qq.com',1),('00054','沙和尚',null,1),('51234','牛魔王','niumowang@qq.com',2),('83223','铁扇公主',null,2),('09527','白龙马','bailongma@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),-- 铁扇公主(80, 7, 2),(92, 7, 6);

进入SQL中查询四张表
学生表:
在这里插入图片描述
班级表:
在这里插入图片描述
分数表:
在这里插入图片描述
课程表:
在这里插入图片描述

查询

语法

select 列… from 表1,表2…;可以在后面加上where对表与表进行笛卡尔积进行筛选

一 查询孙悟空同学的成绩

1 先查询全部的,发现成绩中有student_id与学生中的id对应,所以可以利用这个条件作为筛选条件

在这里插入图片描述

2 条件筛选的时候,我们必须指定表名中的属性与那个表中的属性相对应,本例中student表中id与score表中的student_id相对应,所以有student.id=score.student_id

在这里插入图片描述

3 从第二步就可以看出我们要的是孙悟空同学的成绩,所以我们就可以查出来了

MySQL数据库的约束+进阶版新增与查询
查询思路:

对于刚学习sql多表查询,我们可以先一步一步的写出分析,最后得到一步到位的sql语句

二 查询所有同学的总成绩

这个步骤就是在第一个步骤增加聚合查询
在这里插入图片描述
我们在进行聚合查询,进行分组求和
在这里插入图片描述
三 查询所有同学的成绩和课程以及个人信息

这就是对三张表进行笛卡尔积了,我们可以先查询我们知道了,然后根据条件筛选,

在这里插入图片描述

我们可以发现score.course_id对应的就是course.id所以我们就可以进一步查询

在这里插入图片描述
最终查询:

在这里插入图片描述

多表查询(外连接 join on关键字)

连接示例:

create table student(id int,name varchar(20));
insert into student values(1,‘张三’),(2,‘李四’),(3,‘王五’);
create table score(student_id int,score decimal(3,1));
insert into score values(1,90.0),(2,80.5),(4,70.0);

在这里插入图片描述

我们可以发现两张表内容不一样,表二没有id为3的同学,表1没有id为4的同学,这个是故意这样设置理解的,正常情况下表都是对称的

join on 内连接

select 列 from 表1 inner join 表2 on 条件;其中inner可以省略,其实就是与上一节的内连接展示效果是一样的,如果遇到多个表join on可以嵌套,select 列 from 表1 join 表2 on 条件 join 表3 on 条件

MySQL数据库的约束+进阶版新增与查询

所以可以看出,内连接其实就是两个表的交集
在这里插入图片描述

join on 左外连接

select 列 from 表1 left join 表2 on 条件

在这里插入图片描述
此时我们查询的内容就以左边这张表为主,把左边这张表的内容要全部查出
在这里插入图片描述

join on 右外连接

select 列 from 表1 right join 表2 on 条件;

在这里插入图片描述

此时查询出来的我们就是以右边的表为主,集合形式也就是如下:
在这里插入图片描述

总结

1 对于内外连接,join on都可以做到,但是利用where条件查询只能进行内连接查询
2 还有子查询以及合并查询等操作,这些操作都不常用,这里就不过多介绍
3 对于多表查询而言,其核心依旧还是sql语句,熟练的掌握sql语句有助于我们高效的工作

老江饲料商城