MySQL表的高级增删改查

桃扇骨 2023-09-27 12:43 126阅读 0赞

![在这里插入图片描述][8da4cc0072cd496486e432f875c2bb9f.gif_pic_center]

#### 文章目录 ####

*  一、聚合查询
 *  二、分组查询
 *  三、联合查询
 *   *  内连接
     *  外连接
     *  自连接
 *  四、合并查询
 *  五、子查询

## 一、聚合查询 ##

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

<table> 
 <thead> 
  <tr> 
   <th>函数</th> 
   <th>作用</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>COUNT(expr)</td> 
   <td>查询数据的数量</td> 
  </tr> 
  <tr> 
   <td>SUM(expr)</td> 
   <td>查询数据的总和</td> 
  </tr> 
  <tr> 
   <td>AVG(expr)</td> 
   <td>查询数据的平均值</td> 
  </tr> 
  <tr> 
   <td>MAX(expr)</td> 
   <td>查询数据的最大值</td> 
  </tr> 
  <tr> 
   <td>MIN(expr)</td> 
   <td>查询数据的最小值</td> 
  </tr> 
 </tbody> 
</table>

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函数就是用来统计我们表的行数的。  
![在这里插入图片描述][26387ceef75d469ba4444832e002bfe7.png]  
但注意的是，我们再给count函数传参数时，这一列不能有null值。  
![在这里插入图片描述][cd5d2f59139b4594ab1a43329bc679cb.png]  
我们发现当传入math参数时，因为math有一行的数据是null，count函数在统计时，自动省略这一行。  
![在这里插入图片描述][a44f8e64de9540a9be8881130b87b5f9.png]  
当然我们还可以传入全列，count传入全列时，只要这一列有不为null的值就会被统计上，但时间会相对增大，一般建议传入主键或者not null的列。  
**SUM函数:**  
用来计算某一列数值的综合，null自动省略。  
![在这里插入图片描述][e7a7f2d8aa7c4394a923fcb179d9c29c.png]  
也可以进行表达式进行聚合计算。  
![在这里插入图片描述][2f2f77d6ceb545509baa09cbc2a99844.png]  
**AVG函数:**  
avg函数对某一列求平均值，我们可以发现计算平均值是，null既不计入分子也不计入分母。  
![在这里插入图片描述][aa682592d9fd45ce9939b0ce9e75fccc.png]  
**MAX函数:**  
求某一列的最大值  
![在这里插入图片描述][f8d2854176dc4aca8ec4f1bc409e1054.png]  
**MIN函数:**  
求某一列的最小值  
![在这里插入图片描述][4d51e55e8ef24ea0b93dd2b623384f21.png]

## 二、分组查询 ##

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

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);

![在这里插入图片描述][3c4c7fbbdb7a4199957705a817b613a1.png]  
我们来计算平均数学成绩  
![在这里插入图片描述][b8832690d99a4c3f996193269e2e0d73.png]  
这样的平均成绩没啥意思，我们来求一下每个班的数学平均成绩

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

![在这里插入图片描述][f1aa0e7ceb4e431db34d08885672b228.png]  
我们在来求一下，每班的数学最高分。

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

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

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

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

![在这里插入图片描述][56fa8775fed149aea1fb73fabe057b43.png]  
分组之后: 查询每个班级的平均数学成绩，但去除平均成绩为85的班级。

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

![在这里插入图片描述][68b60796ef704a11aa295d6d87dc348a.png]  
分组之前和分组之后都指定条件： 查询班级的平均成绩，去掉小王的成绩，并且去除计算机1班的平均数学成绩

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

![在这里插入图片描述][332db4fb97504a02b9511c8a93fe2ce6.png]

## 三、联合查询 ##

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

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

![在这里插入图片描述][6bbf7e08f1dd46d8bb3fbf06ff23a77f.png]

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

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

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

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

![在这里插入图片描述][a1dd0b6351f44ac1bacdbbd99c0d74d1.png]

### 内连接 ###

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

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

![在这里插入图片描述][4d45e087559f41dd8dd84e6c82762d02.png]

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

![在这里插入图片描述][a754e7495008451d8ef1cc276245f020.png]

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

![在这里插入图片描述][3bb363c123874e74950b56d4bf0c28a6.png]

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

![在这里插入图片描述][88c3d7a2736b415f9a377225fac9e274.png]  
**联合查询也可以用join来完成:**

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

![在这里插入图片描述][08cf94af0abd44558037dea1243203e7.png]  
内连接还可以使用inner join完成。

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

![在这里插入图片描述][465a9329e874419d90f8ec74ad5ce982.png]  
我们还可以进行多张表进行联合查询。  
![在这里插入图片描述][52f14d9a97fb44dd94f813d301b9bb11.png]

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

![在这里插入图片描述][815dbc69c2d24f71830e6e9583b7c424.png]  
我们可以省略部分列，使用别名，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;

![在这里插入图片描述][12a50e7bda654322bd0d4318fd380154.png]

### 外连接 ###

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

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

![在这里插入图片描述][5dfe8513de384b54a9f599a2878c6b6d.png]

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

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

![在这里插入图片描述][c0b84abd94b84f768ac5b9688661c404.png]  
![在这里插入图片描述][6600f3f51fd5486088e1534475ac1f6f.png]  
这时我们发现，内外查询的结果就有所差异了。  
**外连接:**  
当进行外连接时，如果是左连接，会把左表所有的数据查询到总结果中，如果右表没有对应数据，就是用NULL补充（右连接同理）。

### 自连接 ###

SQL中无法对行和行之间使用条件比较，当我们要进行行行运算时，我们可以使用自连接进行调整。  
![在这里插入图片描述][f3e210c7c5ff448f92cfb0a4ec914716.png]  
![在这里插入图片描述][b65075aca42f43a7ab7e5127ca86cf08.png]  
我们想查询那个同学的java成绩比英文成绩高。  
![在这里插入图片描述][141f234252e54084a83565f00e431e61.png]  
我们可以发现至今将表明写两遍，会报一个表名不唯一的错误。正确的做法是为表名起别名。  
![在这里插入图片描述][a555592b92d340668676cfc444092503.png]  
这里我们是自己和自己比，所以我们加上student\_id相等的条件  
![在这里插入图片描述][286f3438286c4a8aa13758f04c874af5.png]  
![在这里插入图片描述][beaa20913a2347f1a06f0e1cc7b43288.png]  
然后对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;

![在这里插入图片描述][363d1722bf244c53804ad0a7865f6608.png]  
我们发现只有两名学生即选择了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;

![在这里插入图片描述][82610917c18640bdacc86f390e51c455.png]  
我们发现没有java比英文高的数据  
![在这里插入图片描述][4f3bdc37abcd456e9b3ef0e2365466f1.png]  
所以我们查出来的是空集合。

## 四、合并查询 ##

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

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

![在这里插入图片描述][3d56c7d494a64038b1edba1950727bb1.png]

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

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

## 五、子查询 ##

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

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

![在这里插入图片描述][677072b090194efb8bf99454e6352e75.png]

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

![在这里插入图片描述][f060743edd1b4fc483874a3438d98986.png]  
**子查询:**

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

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

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

![在这里插入图片描述][534c7993e58c4000873de8d5b07d5824.png]

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

![在这里插入图片描述][a2a4c8f9664f46888d0f1aeea5f0ba16.png]  
**子查询:**

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

![在这里插入图片描述][b485c798e5234ac79b9cd71bb66918e9.png]  
![在这里插入图片描述][a886a4440776474dbecbacd45b06163c.png]  
**EXISTS关键字**: 可读性比较差，效率也大大的比in低，适用于解决特殊情况

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

[8da4cc0072cd496486e432f875c2bb9f.gif_pic_center]: https://img-blog.csdnimg.cn/8da4cc0072cd496486e432f875c2bb9f.gif#pic_center
[26387ceef75d469ba4444832e002bfe7.png]: https://img-blog.csdnimg.cn/26387ceef75d469ba4444832e002bfe7.png
[cd5d2f59139b4594ab1a43329bc679cb.png]: https://img-blog.csdnimg.cn/cd5d2f59139b4594ab1a43329bc679cb.png
[a44f8e64de9540a9be8881130b87b5f9.png]: https://img-blog.csdnimg.cn/a44f8e64de9540a9be8881130b87b5f9.png
[e7a7f2d8aa7c4394a923fcb179d9c29c.png]: https://img-blog.csdnimg.cn/e7a7f2d8aa7c4394a923fcb179d9c29c.png
[2f2f77d6ceb545509baa09cbc2a99844.png]: https://img-blog.csdnimg.cn/2f2f77d6ceb545509baa09cbc2a99844.png
[aa682592d9fd45ce9939b0ce9e75fccc.png]: https://img-blog.csdnimg.cn/aa682592d9fd45ce9939b0ce9e75fccc.png
[f8d2854176dc4aca8ec4f1bc409e1054.png]: https://img-blog.csdnimg.cn/f8d2854176dc4aca8ec4f1bc409e1054.png
[4d51e55e8ef24ea0b93dd2b623384f21.png]: https://img-blog.csdnimg.cn/4d51e55e8ef24ea0b93dd2b623384f21.png
[3c4c7fbbdb7a4199957705a817b613a1.png]: https://img-blog.csdnimg.cn/3c4c7fbbdb7a4199957705a817b613a1.png
[b8832690d99a4c3f996193269e2e0d73.png]: https://img-blog.csdnimg.cn/b8832690d99a4c3f996193269e2e0d73.png
[f1aa0e7ceb4e431db34d08885672b228.png]: https://img-blog.csdnimg.cn/f1aa0e7ceb4e431db34d08885672b228.png
[709fe39ece6f4adf92b55b75723fd25b.png]: https://img-blog.csdnimg.cn/709fe39ece6f4adf92b55b75723fd25b.png
[56fa8775fed149aea1fb73fabe057b43.png]: https://img-blog.csdnimg.cn/56fa8775fed149aea1fb73fabe057b43.png
[68b60796ef704a11aa295d6d87dc348a.png]: https://img-blog.csdnimg.cn/68b60796ef704a11aa295d6d87dc348a.png
[332db4fb97504a02b9511c8a93fe2ce6.png]: https://img-blog.csdnimg.cn/332db4fb97504a02b9511c8a93fe2ce6.png
[77670d6b26614e61a67c22f499a3ba5a.png]: https://img-blog.csdnimg.cn/77670d6b26614e61a67c22f499a3ba5a.png
[6bbf7e08f1dd46d8bb3fbf06ff23a77f.png]: https://img-blog.csdnimg.cn/6bbf7e08f1dd46d8bb3fbf06ff23a77f.png
[4918f4500c4249389967b6e1e5469108.png]: https://img-blog.csdnimg.cn/4918f4500c4249389967b6e1e5469108.png
[2eeded5959604d2ca968032f445a65b5.png]: https://img-blog.csdnimg.cn/2eeded5959604d2ca968032f445a65b5.png
[53508544f4794cbc91058db4b882535b.png]: https://img-blog.csdnimg.cn/53508544f4794cbc91058db4b882535b.png
[a1dd0b6351f44ac1bacdbbd99c0d74d1.png]: https://img-blog.csdnimg.cn/a1dd0b6351f44ac1bacdbbd99c0d74d1.png
[ba35a6ab3eed44fda11fd56d979a524a.png]: https://img-blog.csdnimg.cn/ba35a6ab3eed44fda11fd56d979a524a.png
[e3111e7b7b0d47e6b8707f68973dff86.png]: https://img-blog.csdnimg.cn/e3111e7b7b0d47e6b8707f68973dff86.png
[5c8ad3b7fcc34a1986e0238d4989c680.png]: https://img-blog.csdnimg.cn/5c8ad3b7fcc34a1986e0238d4989c680.png
[e1a82cd9d9f5475b8a73f3800a4f493f.png]: https://img-blog.csdnimg.cn/e1a82cd9d9f5475b8a73f3800a4f493f.png
[4d45e087559f41dd8dd84e6c82762d02.png]: https://img-blog.csdnimg.cn/4d45e087559f41dd8dd84e6c82762d02.png
[a754e7495008451d8ef1cc276245f020.png]: https://img-blog.csdnimg.cn/a754e7495008451d8ef1cc276245f020.png
[3bb363c123874e74950b56d4bf0c28a6.png]: https://img-blog.csdnimg.cn/3bb363c123874e74950b56d4bf0c28a6.png
[88c3d7a2736b415f9a377225fac9e274.png]: https://img-blog.csdnimg.cn/88c3d7a2736b415f9a377225fac9e274.png
[08cf94af0abd44558037dea1243203e7.png]: https://img-blog.csdnimg.cn/08cf94af0abd44558037dea1243203e7.png
[465a9329e874419d90f8ec74ad5ce982.png]: https://img-blog.csdnimg.cn/465a9329e874419d90f8ec74ad5ce982.png
[52f14d9a97fb44dd94f813d301b9bb11.png]: https://img-blog.csdnimg.cn/52f14d9a97fb44dd94f813d301b9bb11.png
[815dbc69c2d24f71830e6e9583b7c424.png]: https://img-blog.csdnimg.cn/815dbc69c2d24f71830e6e9583b7c424.png
[12a50e7bda654322bd0d4318fd380154.png]: https://img-blog.csdnimg.cn/12a50e7bda654322bd0d4318fd380154.png
[4cae5f69308c49b3b9645db26320b2f8.png]: https://img-blog.csdnimg.cn/4cae5f69308c49b3b9645db26320b2f8.png
[e00c87a6ba1a458db2d5473bd900368d.png]: https://img-blog.csdnimg.cn/e00c87a6ba1a458db2d5473bd900368d.png
[5dfe8513de384b54a9f599a2878c6b6d.png]: https://img-blog.csdnimg.cn/5dfe8513de384b54a9f599a2878c6b6d.png
[7fe2c2b7efbd49bba1612bd151c1a716.png]: https://img-blog.csdnimg.cn/7fe2c2b7efbd49bba1612bd151c1a716.png
[4723fc6c121249dbab8d11fbb926ba3c.png]: https://img-blog.csdnimg.cn/4723fc6c121249dbab8d11fbb926ba3c.png
[c0b84abd94b84f768ac5b9688661c404.png]: https://img-blog.csdnimg.cn/c0b84abd94b84f768ac5b9688661c404.png
[6600f3f51fd5486088e1534475ac1f6f.png]: https://img-blog.csdnimg.cn/6600f3f51fd5486088e1534475ac1f6f.png
[f3e210c7c5ff448f92cfb0a4ec914716.png]: https://img-blog.csdnimg.cn/f3e210c7c5ff448f92cfb0a4ec914716.png
[b65075aca42f43a7ab7e5127ca86cf08.png]: https://img-blog.csdnimg.cn/b65075aca42f43a7ab7e5127ca86cf08.png
[141f234252e54084a83565f00e431e61.png]: https://img-blog.csdnimg.cn/141f234252e54084a83565f00e431e61.png
[a555592b92d340668676cfc444092503.png]: https://img-blog.csdnimg.cn/a555592b92d340668676cfc444092503.png
[286f3438286c4a8aa13758f04c874af5.png]: https://img-blog.csdnimg.cn/286f3438286c4a8aa13758f04c874af5.png
[beaa20913a2347f1a06f0e1cc7b43288.png]: https://img-blog.csdnimg.cn/beaa20913a2347f1a06f0e1cc7b43288.png
[363d1722bf244c53804ad0a7865f6608.png]: https://img-blog.csdnimg.cn/363d1722bf244c53804ad0a7865f6608.png
[82610917c18640bdacc86f390e51c455.png]: https://img-blog.csdnimg.cn/82610917c18640bdacc86f390e51c455.png
[4f3bdc37abcd456e9b3ef0e2365466f1.png]: https://img-blog.csdnimg.cn/4f3bdc37abcd456e9b3ef0e2365466f1.png
[b0dc03628766468a891b40d9c5201743.png]: https://img-blog.csdnimg.cn/b0dc03628766468a891b40d9c5201743.png
[3d56c7d494a64038b1edba1950727bb1.png]: https://img-blog.csdnimg.cn/3d56c7d494a64038b1edba1950727bb1.png
[8a94c8b6fba34511a4a4f709db4c0bef.png]: https://img-blog.csdnimg.cn/8a94c8b6fba34511a4a4f709db4c0bef.png
[bb243684077548e38e45222d2fcfb5f8.png]: https://img-blog.csdnimg.cn/bb243684077548e38e45222d2fcfb5f8.png
[677072b090194efb8bf99454e6352e75.png]: https://img-blog.csdnimg.cn/677072b090194efb8bf99454e6352e75.png
[f060743edd1b4fc483874a3438d98986.png]: https://img-blog.csdnimg.cn/f060743edd1b4fc483874a3438d98986.png
[8d217014d7af48b38dcb3d804d731bd7.png]: https://img-blog.csdnimg.cn/8d217014d7af48b38dcb3d804d731bd7.png
[bb792f792b744dd7b9004b3187cd40d3.png]: https://img-blog.csdnimg.cn/bb792f792b744dd7b9004b3187cd40d3.png
[534c7993e58c4000873de8d5b07d5824.png]: https://img-blog.csdnimg.cn/534c7993e58c4000873de8d5b07d5824.png
[a2a4c8f9664f46888d0f1aeea5f0ba16.png]: https://img-blog.csdnimg.cn/a2a4c8f9664f46888d0f1aeea5f0ba16.png
[b485c798e5234ac79b9cd71bb66918e9.png]: https://img-blog.csdnimg.cn/b485c798e5234ac79b9cd71bb66918e9.png
[a886a4440776474dbecbacd45b06163c.png]: https://img-blog.csdnimg.cn/a886a4440776474dbecbacd45b06163c.png