开开心心带你学习MySQL数据库之第六篇下

秒速五厘米 2023-10-15 19:09 23阅读 0赞

![在这里插入图片描述][523f0d833d20422e8baf3553ea88d4f2.jpeg_pic_center]

#### 插入查询结果 ####

把查询和新增联合起来.  
把查询结果作为新增的数据  
例子:把student1表的查询结果作为新增数据插入到student2表中.

create table student1(id int, name varchar(20));
     create table student2(id int, name varchar(20));
     insert into student1 values(1, '张三'), (2, '李四'), (3, '王五');
     insert into student2 select * from student1;

约束要求:

*  查询结果得到的列数,类型需要和插入的表的列数,类型匹配 => 表结构相同

#### 聚合查询 ####

在查询过程中,表的行与行之间进行一定的运算  
依赖聚合函数 => SQL提供的库函数  
常见的聚合函数

<table> 
 <thead> 
  <tr> 
   <th>函数</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>COUNT([DISTINCT] expr)</td> 
   <td>返回查询到的数据的数量 =&gt; 查询结果的行数</td> 
  </tr> 
  <tr> 
   <td>SUM([DISTINCT] expr)</td> 
   <td>返回查询到的数据的总和,不是数字没有意义 =&gt; 求和</td> 
  </tr> 
  <tr> 
   <td>AVG([DISTINCT] expr)</td> 
   <td>返回查询到的数据的平均值,不是数字没有意义 =&gt; 平均值</td> 
  </tr> 
  <tr> 
   <td>MAX([DISTINCT] expr)</td> 
   <td>返回查询到的数据的最大值,不是数字没有意义 =&gt; 最大值</td> 
  </tr> 
  <tr> 
   <td>MIN([DISTINCT] expr)</td> 
   <td>返回查询到的数据的最小值,不是数字没有意义 =&gt; 最小值</td> 
  </tr> 
 </tbody> 
</table>

count => 计算一下按select \* from 表名得到的结果行数  
这个操作可以理解成先执行select \*, 然后去计算select \*查询结果有多少行

create table student(id int, name varchar(20));
    insert into student values(1, '张三'), (2, '李四'), (3, '王五'), (4, NULL);
    select count(*) from student;

sum求和 => 只是针对数子列有效,如果是字符串列,则无法计算  
求一个班级里面所有同学的语文成绩总分和是多少

-- 创建考试成绩表
    drop table if exists exam_result;
    create table exam_result (
    	id int,
    	name varchar(20),
    	chinese decimal(3,1),
    	math decimal(3,1),
    	english decimal(3,1)
    );
    
    -- 插入测试数据
    insert into exam_result (id, name, chinese, math, english) values
    	(1,'唐三藏', 67, 98, 56),
    	(2,'孙悟空', 87.5, 78, 77),
    	(3,'猪悟能', 88, 98.0, 90),
    	(4,'曹孟德', 82, 84, 67),
    	(5,'刘玄德', 55.5, 85, 45),
    	(6,'孙权', 70, 73, 78.5),
    	(7,'宋公明', 75, 65, 30);
    
    -- 求全班语文成绩和
    select sum(chinese) from exam_result;
    
    -- 往表中插入含有null的一行数据
    insert into exam_result values (8, '如来佛主', null, null, null);
    select sum(chinese) from exam_result;
    -- 数据结果没变,正常显示

sum操作会自动跳过结果null的行  
avg计算语文平均值  
![image-20230908113008248][]  
聚合函数还能搭配表达式使用  
求总分的平均值  
![image-20230908115329333][]  
求班上语文的最高分和最低分  
![image-20230908121114106][]

~~sql是有一定的"统计计算"能力的!这就像excel一样 => 正因为sql有这样的统计能力,有的时候, sql也是非技术岗(产品经理)要掌握的技能  
能不能再聚合函数里面再放一个聚合函数?  
答:(1)sql表达逻辑的能力是有限的,如果有这样的需求,可以使用java操作sql，复杂逻辑用java来表达, sql只是做简单的查询和统计 => 常见做法⭐️ ⭐️ ⭐️  
(2)其实sql也能写复杂的,但是可读性和执行效率都会很差

这些聚合函数,默认都是针对这个表里的所有类进行了聚合  
有时候,还需要分组聚合(按照制定的字段,把记录分成若干组.每一组分别使用聚合函数)

-- 创建员工表
    create table employee(id int, name varchar(20), post varchar(20), salary int);
    
    -- 往表中插入几条记录
    insert into employee values (1, '初一', '讲师', 10000);
    insert into employee values (2, '初二', '讲师', 11000);
    insert into employee values (3, '初三', '讲师', 12000);
    insert into employee values (4, '初四', '学管师', 10000);
    insert into employee values (5, '初五', '学管师', 9000);
    insert into employee values (6, '如来佛祖', '老板', 100000);
    insert into employee values (7, '太上老君', '老板', 120000);

求出每个岗位的平均薪资  
~~就需要使用分组查询  
~~使用group by进行分组  
指定一个列,把列里相同的值,相同的分到同一个组中  
![image-20230908155901036][]  
![image-20230908155752335][]

> select 指定的列,要么是带有聚合函数的,要么就得是指定的group by的列…不能指定一个非聚合非group by 的列~~比如写个name , name 不是group by 的列也不带聚合函数,此时这里的查询结果无意义!!!(不会报错)

分组的时候,可以指定条件筛选,  
~~先搞清楚,筛选条件是分组前,还是分组后  
求出除孙悟空外的每个岗位的平均薪资  
1.分组前,筛选,使用where条件.  
求每个岗位的平均薪资,但是刨除初一同学  
![image-20230908162814632][]  
2.分组后,筛选,使用having条件  
求每个岗位的平均薪资,但是刨除老板  
![image-20230908163212437][]  
3.可以同时在分组前和分组后筛选!!  
求每个岗位的平均薪资,但是刨除初一同学和老板这个岗位  
![image-20230908163940500][]

#### 联合查询(多表查询) ####

把多个表联合到一起进行查询  
~~笛卡尔积(一种运算),联合查询就是基于这个运算进行拓展的  
~~我们熟悉的平面直角坐标系也叫笛卡尔坐标系笛卡尔积,其实就是一种排列组合.把两张表的记录尽可能的排列组合出N种情况

[523f0d833d20422e8baf3553ea88d4f2.jpeg_pic_center]: https://img-blog.csdnimg.cn/523f0d833d20422e8baf3553ea88d4f2.jpeg#pic_center
[image-20230908113008248]: https://img-blog.csdnimg.cn/img_convert/188e90a576567b9b4a5d5de12b340531.png
[image-20230908115329333]: https://img-blog.csdnimg.cn/img_convert/41cb04340cf7e1ed50b837dff03974c6.png
[image-20230908121114106]: https://img-blog.csdnimg.cn/img_convert/1f1d5c7edce5b5879296daa759fc97e2.png
[image-20230908155901036]: https://img-blog.csdnimg.cn/img_convert/f5fdd5e4566e83425f8efe62239cc4c7.png
[image-20230908155752335]: https://img-blog.csdnimg.cn/img_convert/e6c25284f5ae1797e0cc62c4f4208a74.png
[image-20230908162814632]: https://img-blog.csdnimg.cn/img_convert/b62741c652677a70dd95b600a3d45fbf.png
[image-20230908163212437]: https://img-blog.csdnimg.cn/img_convert/60eea3216b10d8166c88256ef7c4f7e4.png
[image-20230908163940500]: https://img-blog.csdnimg.cn/img_convert/ea0da786fcd9953fe039e51b7b1d54a8.png