后台-插件-广告管理-内容页广告位一(手机)

您现在的位置是:首页 > 新兴行业 > 云计算云计算

玩转MYSQL(2) 人类高质量数据库的约束、聚合查询、联合查询以及三种表的设计

2021-09-15 00:15:02云计算人已围观

简介目录标题一、MYSQL的那些约束你掌握了几种?一、MYSQL的那些约束你掌握了几种?在MYQSL中,有那么一些约束。比如不能为null,或者是必须唯一的。就像身份证一样,每个人的身份证号一定是唯一的,性别可以是男,也可以是女,但不是为null。下面介绍MYSQL中的6中约束1、NOT NULL :约束某列不能为空。2、UNIQUE : 约束某列的某行有唯一值。3、DEFAULT : 没有给列赋值时给一个默认值。4、PRIMARY KEY :NOT NUL和 UNIQUE的结合,即不能为空.

在这里插入图片描述


在这里插入图片描述

一、MYSQL的那些约束你掌握了几种?

在MYQSL中,有那么一些约束。比如不能为null,或者是必须唯一的。就像身份证一样,每个人的身份证号一定是唯一的,性别可以是男,也可以是女,但不是为null。

下面介绍MYSQL中的6种约束

1、NOT NULL :约束某列不能为空。

-- 创建一个图书表,id不能为空
create table book(id int  not null);

2、UNIQUE : 约束某列的某行有唯一值。

-- 创建一个图书表,id唯一,不能重复
create table book(id int  unique);

3、DEFAULT : 没有给列赋值时给一个默认值。

-- 创建一个语文书表,默认name为unknown
create table book(name varchar(20) default
'unknown');

4、PRIMARY KEY :NOT NUL和 UNIQUE的结合,即不能为空, 值必须唯一。一般表的ID会设成primary key,这样的好处是为表创立一个唯一的标识。能够更方便的找到表中特殊的信息。一般常与auto_increment连用,表示自增主键,

-- 创建一个图书,每个图书有自己的名称,价格,但序列号是唯一的
create table book(
id int primary key auto_increment,
name varchar(20),
price int );

5、FOREIGN KEY :外键约束。一个表的数据和另一个表匹配,增加耦合性。

-- 创建班级表,
create table class(
id int primary key auto_increment,
name varchar(20)
);
-- 学生表,一个学生对应一个班级,一个班级可以有多个学生
create table student(
id int primary key auto_increment,
name varchar(20),
classes_id int ,
foreign key (classes_id) references class(id)
);

6、CHECK : 保证列中的值符合特定的条件。

-- MySQL使用时不报错,但忽略该约束:
create table student(
id int primary key auto_increment,
name varchar(20),
sex varchar(1),
check (sex='男' or sex='女')
);

二、表与表之间的三种关系

1、一对一关系
eg:人和身份照号码

一个人只能拥有一个身份证号码
在这里插入图片描述
2、一对多关系
eg:学生和班级的关系

一个班级可以有多个学生,一个学生只属于一个班级
在这里插入图片描述
3、多对多关系
eg:老师和班级的关系

一个班级可以有多个老师,一个老师也可以教多个班。

三、查询

3.1 :聚合查询

聚合查询通过4中聚合函数完成。他们分别是:**
1、count() :返回查询到的数量,和查询类型是不是数字无关。

-- 查询图书表有多少书
select  count(*) from book;

2、sum() :返回查询数据的总和,如果数据不是数字就没有意义

-- 查询三年二班有多少学生
select sum(student) from class;

3、avg() : 返回查询的数据的平均数,如果数据不是数字没有意义

-- 查询三年二班学生数学的平均分
select avg(math) from class;

4、max() : 返回查询的数据的最大值,如果数据不是数字没有意义

-- 查询三年二班数学的最高分
select max(math) from class;

5、min(): 返回查询的数据的最小值,如果数据不是数字没有意义

-- 查询三年二班数学的最低分
select min(math) from class;

3.2、GROUP BY

使用group by字句可以对指定列进行分组查询。但是需要满足,select后的
字段必须是分组依据的字段。如果其他字段想出现就必须使用聚合函数。

eg:
准备数据,创建一个学生表,含有学生id,姓名,数学和语文成绩。
在这里插入图片描述

create table  student(
-- 学生id
id int primary key auto_increment,
-- 学生姓名
student_name varchar(20),
-- 学生数学成绩
math int,
-- 学生语文成绩
chinese int 
);

插入数据
在这里插入图片描述

insert into student(student_name,math,chinese) values
('张三',66,67),
('李四',44,98),
('王五',88,89);

查看当前表的数据
在这里插入图片描述

使用GROUP BY查询,学生的最高、平均、最低数学成绩
在这里插入图片描述

3.3、HAVING和group by 搭配使用

在使用group by 字句后,如果还需要条件筛选的话,就不能用where了,需要用having来筛选条件。

-- 查询总成绩大于150的同学
select student_name , sum(math+chinese) from student group by student_name having sum(math+chinses)>150;


结果只有王五同学总分大于150.
在这里插入图片描述

3.4、联合查询

上面的查询都是在一张表中进行查询,但实际应用的过程中,经常是在几张表中查询数据,这就用到了联合查询。多张表查询就是对这些表取笛卡尔积,在筛选有用的数据。学习联合查询之前,需要先了解什么笛卡尔积。

在这里插入图片描述
总得来说,笛卡尔积就是罗列出两张表所有可能出现的数据。

准备数据:

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','黑旋风李逵','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);

3.4.1内连接

语法:
1、select 字段名 from 表1 +别名(可不写) inner join 表2+别名(可不写) on
连接条件 and 其他条件

示例:
查询白素贞同学的成绩
在这里插入图片描述
查询所有同学的总成绩:
当我们写一些比较复杂的SQL语句时,非常建议大家不要一次性写完。这样很容易写错,而SQL调试不了,所以在实际写的时候可以像下面这样,一行一行的写。每次写一个条件,这样就很清楚了。

select student.sn,student.name,student.qq_mail,
sum(score.score) from student
join score
on student.id=score.student_id 
group by score.student_id;

在这里插入图片描述

2、select 字段名 from 表1 +别名(可以不写) ,表2+别名(可以不写) where 连接条件 and 其他条件

示例:
查询白素贞同学的成绩:
在这里插入图片描述

查询所有学生的qq邮箱和总成绩:
在这里插入图片描述

这两种方式查询的结果相同。

3.4.2 外连接

内连接和外连接的区别,在于对"空值"的区别。如果表里没有"空值"(不是单纯的NULL,而是泛指两张表的数据对不上),那内连接和外连接就没有区别了。

eg:
先删除成绩和学生表,新建一份在加上3个同学。

create table student(id int primary key auto_increment,name varchar(20));
create table score(studentId int ,score int );
insert into student values(null,'张三');
insert into student values(null,'李四');
insert into student values(null,'王五');

先来看一下当前表的数据

select * from student;

在这里插入图片描述
再给成绩表中插入三行数据,但是做一点点区别。

insert into score values(1,55);
insert into score values(2,85);
insert into score values(4,91);

这里故意将本该是3的王五的id改成4。
在这里插入图片描述
这时候发生了数据不对应的情况。王五同学在分数表中没有成绩,id为4的同学在学生表中,没有信息。
在这里插入图片描述
这时候如果我们查询两张表的结果会怎么样呢?
查询每位同学的成绩,姓名信息。

select student.name,score.score from student join 
score on student.id=score.studentId;

内连接最后的结果既没有王五同学的成绩和姓名信息,也没有id为4的同学的信息。
在这里插入图片描述

内连接相当于对上面两张表取了交集。
请添加图片描述

外连接相对于内连接来说,分为左外连接和右外连接。在写法上和内连接很相似,只需要在原来的语句中对于join前再加上left或者是right就表示内连接或者外连接了

左外连接:最终结果以join左侧的表为主,尽可能的显示左侧表的信息。

select student.name,score.score from student left
 join 
score on student.id=score.studentId;

在这里插入图片描述
右外连接:最终结果以join右侧的表为主,尽可能的显示右侧表的信息。

select student.name,score.score from student right
 join 
score on student.id=score.studentId;

在这里插入图片描述

3.4.3 自连接

自连接就是同一张表连接自身进行查询。

eg:查询语文成绩比英语成绩高的信息

这次查询和以往不同的是这是针对行和行的查询,之前的例如查询每位同学的成绩,姓名信息。这都是列与列之间的关系。

既然不好解决问题,不妨换个思路,那我就不进行行和行比较了,我想办法转换成列和列比较。

1、先看看它的笛卡尔积长什么样
需要注意的是如果你直接select * from score,score ;查询的话是会报错的。需要你使用as起一个别名。

select * from score as a,score as b;

最终结果有四百行,我这里放一部分。
在这里插入图片描述

接下来在进一步筛选有用的信息,挑选出同一个同学的信息

select * from score as a,score as b where a.student_id=b.student_id;

现在缩减成了62行
在这里插入图片描述
最后直接一步到位,但是需要先回头看看语文和英语的id,一个是4,另一个是6

insert into course(name) values
('Java'),('中国传统文化'),('计算机原理'),('语文'),('高阶数学'),('英文');

这样我们在通过课程id筛选

select * from score as a,score as b 
where a.student_id=b.student_id and a.course_id=4 and b.course_id=6;

最终结果就只剩下一了。
在这里插入图片描述
再来练习一下java比计算机原理分数高的同学的信息,我们这次直接到上图的那一步。

select * from score as a,score as b
 where a.student_id=b.student_id and a.course_id=1 and b.course_id=3;

course_id为1的是java的分数,course_id为3的是计算机原理的分数
在这里插入图片描述
这样下来,一行不仅包含了java的列,还包含了计算机原理的列。通过自连接就完成了行转列的效果。

来看看最终结果,计算机原理成绩比JAVA高的信息

select * from score as a,score as b
 where a.student_id=b.student_id and a.course_id=1 and b.course_id=3 and a.score<b.score;

在这里插入图片描述

3.5、子查询

子查询就是其他sql语句中的查询语句。所以也叫做嵌套查询。



3.5.1单行子查询

返回一行子查询的就是单行子查询。
eg:查询和“不想毕业”同学的同班同学。

还是一步步来,先查询不想毕业同学的班级id。

select classes_id from student where name='不想毕业';

在这里插入图片描述
不想毕业同学在一班。在通过一班这个信息找他的同学。

select name from student where classes_id=1;

在这里插入图片描述
我们用两次查询得到了想要的结果,那试试子查询怎么样。

select name from student where
 classes_id=(select classes_id from student where name='不想毕业');

在这里插入图片描述
在上面的SQL语句中先执行第二个SQL语句作为前面查询name的条件,这就是子查询。

3.5.2多行子查询

多行子查询返回的是多条记录

eg:查询语文或者英语成绩的信息
1、先查询语文或者英语成绩的课程id

select id from course where name='语文' or name='英文';

在这里插入图片描述

2、再在课程表中,根据课程id找对应的信息

select * from score where course_id=4 or course_id=6;

在这里插入图片描述
再用多行子查询试试
多行子查询得到的是多条记录,外层查询需要使用in关键字。单行子查询只返回一条记录直接用=即可。

 select * from score where course_id in(
select id from course where name='语文' or name='英文');

在这里插入图片描述

3.6、合并查询

把多个select查询的结果合并成一个结果就是合并查询。有些像取并集。它包含两个关键字
union : 针对重复的进行去重
union all 不会针对重复的去重
union这有一个前提是两边的查询得到的结果的列是一致的,包括数量和类型。

eg:查询id小于3,或者名字为“英文”的课程

select * from course where id<3 or name ='英文';

在这里插入图片描述

使用union查询

select * from course where id<3 union select * from course where name='英文';

效果是一样的。
在这里插入图片描述

四、结尾

上面就是这次的所有内容了,祝大家工作顺利,学习顺利。冲冲冲
在这里插入图片描述

Tags:MySQL bigdata 数据库 

很赞哦! ()

后台-插件-广告管理-内容页广告位二(手机)

相关文章

后台-插件-广告管理-内容页广告位三(手机)

随机图文

后台-插件-广告管理-内容页广告位四(手机)

文章评论

留言与评论(共有 0 条评论)
   
验证码:

本栏推荐

站点信息

  • 文章统计141427篇文章
  • 浏览统计17469次浏览
  • 评论统计1个评论
  • 标签管理标签云
  • 统计数据:统计代码
  • 微信公众号:扫描二维码,关注我们