文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

MySQL表的高级增删改查

2023-09-08 21:43

关注

在这里插入图片描述

文章目录

聚合查询是针对行与行之间的计算,常见的聚合函数有:

函数作用
COUNT(expr)查询数据的数量
SUM(expr)查询数据的总和
AVG(expr)查询数据的平均值
MAX(expr)查询数据的最大值
MIN(expr)查询数据的最小值
create table stu(id int primary key,name varchar(50),math int,english int);insert into stu values(001,"张三",80,90),(002,"李四",75,80),(003,"王五",85,90),(004,"小王",90,80),(005,"小孙",null,null);

count函数:
顾名思义,count函数就是用来统计我们表的行数的。
在这里插入图片描述
但注意的是,我们再给count函数传参数时,这一列不能有null值。
在这里插入图片描述
我们发现当传入math参数时,因为math有一行的数据是null,count函数在统计时,自动省略这一行。
在这里插入图片描述
当然我们还可以传入全列,count传入全列时,只要这一列有不为null的值就会被统计上,但时间会相对增大,一般建议传入主键或者not null的列。
SUM函数:
用来计算某一列数值的综合,null自动省略。
在这里插入图片描述
也可以进行表达式进行聚合计算。
在这里插入图片描述
AVG函数:
avg函数对某一列求平均值,我们可以发现计算平均值是,null既不计入分子也不计入分母。
在这里插入图片描述
MAX函数:
求某一列的最大值
在这里插入图片描述
MIN函数:
求某一列的最小值
在这里插入图片描述

有时候单纯使用聚合查询没啥意思,我们需要先分组在进行聚合计算。

create table stu(id int,name varchar(20),class varchar(20),math int,english int);insert into stu values(001,"张三","计算机1班",80,95),                      (002,"李四","计算机1班",90,76),                      (003,"王五","计算机2班",86,77),                      (004,"小王","计算机2班",92,86),                      (005,"张良","计算机2班",86,96);

在这里插入图片描述
我们来计算平均数学成绩
在这里插入图片描述
这样的平均成绩没啥意思,我们来求一下每个班的数学平均成绩

select class,avg(math) from stu group by class;

在这里插入图片描述
我们在来求一下,每班的数学最高分。

select name,class,max(math) from stu group by class;

在这里插入图片描述
分组查询,也可以指定条件
1.分组之前指定条件,先筛选在分组,WHERE
2.分组之后指定条件,先分组在筛选, HAVING
3.分组之前和分组之后都指定条件,WHERE HAVING都使用。

分组之前: 查询每个班的平均数学成绩,但是去掉小王的成绩

select class,avg(math) from stu where name != '小王' group by class;

在这里插入图片描述
分组之后: 查询每个班级的平均数学成绩,但去除平均成绩为85的班级。

select class,avg(math) from stu group by class having avg(math) != 85;

在这里插入图片描述
分组之前和分组之后都指定条件: 查询班级的平均成绩,去掉小王的成绩,并且去除计算机1班的平均数学成绩

select class,avg(math) from stu where name != '小王' group by class having class != '计算机1班';

在这里插入图片描述

当我们多张表建立联系时,我们就可以进行联合查询,多表查询就是对多张表取笛卡尔积。
在这里插入图片描述
笛卡尔的结果列数是两张表列数之和,行数是两张表的行数之积.

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);
select * from student,classes

在这里插入图片描述

大家轻易可以发现,笛卡尔积里的结果很多都是无效的数据,因此我们需要将一部分无意义的数据给去掉。
在这里插入图片描述
我们通过这两个变量来建立关系,多表查询时,我们访问表中的变量时用表名点(.)变量表示。

 select * from student,classes where classes.id = student.classes_id;

在这里插入图片描述
在这里插入图片描述
当我们加上条件(这个条件我们成为连接条件)之后,剩下的都是“正确"的数据.
我们也可以指定列查询。

 select student.id,student.name,student.classes_id,classes.name from student,classes where classes.id = student.classes_id;

在这里插入图片描述

内连接

我们现在构造了四张表出来,student(学生表),classes(班级表),course(课程表),score(分数表).
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
我们查询一下白素贞的班级:
我们在进行联合查询的时候,不必急于求成,一步一步进行。

-- 1.先计算笛卡尔积select * from student,classes;

在这里插入图片描述

-- 2.引入连接条件select * from student,classes where classes.id = student.classes_id;

在这里插入图片描述

-- 3.引入名字为白素贞的条件select * from student,classes where classes.id = student.classes_id and student.name = '白素贞';

在这里插入图片描述

-- 4.只保留必要的列 select student.name,classes.name from student,classes where classes.id = student.classes_id and student.name = ' 白素贞';

在这里插入图片描述
联合查询也可以用join来完成:

 select student.name,classes.name from student join classes on classes.id = student.classes_id and student.name = '白素贞';

在这里插入图片描述
内连接还可以使用inner join完成。

 select student.name,classes.name from student inner join classes on classes.id = student.classes_id and student.name = '白素贞';

在这里插入图片描述
我们还可以进行多张表进行联合查询。
在这里插入图片描述

select * from student,score,course where student.id = score.student_id and course.id = score.course_id;

在这里插入图片描述
我们可以省略部分列,使用别名,join来查询

select student.name as 学生姓名,course.name as 课程名称,score.score as 分数 from student join score on student.id = score.student_id join course on score.course_id = course.id;

在这里插入图片描述

外连接

内连接和外连接在一些情况下,查询的结果没有差异(当两个表一一对应时),如果没有一一对应那么就有区别了。
在这里插入图片描述
在这里插入图片描述
我们可以用这两张表,建立一下内外连接看一下效果。

-- 内连接select * from student join score on student.id = score.student_id;

在这里插入图片描述

-- 外连接 select * from student left join score on student.id = score.student_id;

在这里插入图片描述
我们可以发现内外连接查询的结果是一样的。因为我们两个表的内容是一一对应的。
在这里插入图片描述
这时我们发现student表id为6的数据在score无对应

在这里插入图片描述
在这里插入图片描述
这时我们发现,内外查询的结果就有所差异了。
外连接:
当进行外连接时,如果是左连接,会把左表所有的数据查询到总结果中,如果右表没有对应数据,就是用NULL补充(右连接同理)。

自连接

SQL中无法对行和行之间使用条件比较,当我们要进行行行运算时,我们可以使用自连接进行调整。
在这里插入图片描述
在这里插入图片描述
我们想查询那个同学的java成绩比英文成绩高。
在这里插入图片描述
我们可以发现至今将表明写两遍,会报一个表名不唯一的错误。正确的做法是为表名起别名。
在这里插入图片描述
这里我们是自己和自己比,所以我们加上student_id相等的条件
在这里插入图片描述
在这里插入图片描述
然后对score1的科目进行限制为java,score2的科目限制为英文

select * from score as score1,score as score2 where score1.student_id = score2.student_id and score1.course_id = 1 and score2.course_id = 6;

在这里插入图片描述
我们发现只有两名学生即选择了java,又选择了英文。
我们再加上java比英文高的条件。

select * from score as score1,score as score2 where score1.student_id = score2.student_id and score1.course_id = 1 and score2.course_id = 6 and score1.score > score2.score;

在这里插入图片描述
我们发现没有java比英文高的数据
在这里插入图片描述
所以我们查出来的是空集合。

在实际应用中,为了合并多个select的执行结果,可以使用集合操作符 union,union all。使用UNION和UNION ALL时,前后查询的结果集中,字段需要一致。
在这里插入图片描述

-- union select * from course where id < 4 union select * from course where name != 'java';

在这里插入图片描述

-- union all select * from course where id < 4 union all select * from course where name != 'java';

在这里插入图片描述
这里我们可以发现union可以去掉重复数据,而union all不去重。
大家需要注意or 与 union的区别,or的查询只能针对同一个表,而union可以来自于多张表,只要查询的结果能够对应列即可。

子查询最本质就是套娃,将多个SQL组合起来。
实际开发中,子查询的使用要小心(子查询会构造出来一些非常复杂并且不好理解的SQL,对于代码的可读性,执行效率都有可能造成很大的影响。
查询许仙的同班同学
在这里插入图片描述
正常思路,先去查询许仙的班级号,再去按照班级号去查那些同学和他一个班

select classes_id from student where name = '许仙';

在这里插入图片描述

select name from student where classes_id = 1 and name != '许仙';

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

select name from student where classes_id = (select classes_id from student where name = '许仙') and name != '许 仙';

在这里插入图片描述
子查询返回一条记录,才可以写等号
在这里插入图片描述
查询java或者英文课的成绩信息
先查询java或者英文课的课程号,再根据课程号去查询课程分数

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

在这里插入图片描述

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

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

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

在这里插入图片描述
在这里插入图片描述
EXISTS关键字: 可读性比较差,效率也大大的比in低,适用于解决特殊情况

还是更推荐大家分步查询。

来源地址:https://blog.csdn.net/buhuisuanfa/article/details/127907559

阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯