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

✨前言✨

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

文章目录

数据库约束的类型

NOT NULL型

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

下面插入数据：

UNIQUE 型

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

测试用例：

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函数:

这里的聚合函数搭配了条件语句，计算总分但是是语文成绩大于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;

与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','如来','[email protected]',1),('00835','孙悟空',null,1),('00391','唐僧',null,1),('00031','猪八戒','[email protected]',1),('00054','沙和尚',null,1),('51234','牛魔王','[email protected]',2),('83223','铁扇公主',null,2),('09527','白龙马','[email protected]',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 从第二步就可以看出我们要的是孙悟空同学的成绩，所以我们就可以查出来了

查询思路：

对于刚学习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 条件；

所以可以看出，内连接其实就是两个表的交集

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语句有助于我们高效的工作