文章目录insert-select插入查询聚合查询count()sum()avg()max()min()分组查询group byhaving联合查询上篇内连接insert-select插入查询如果我们想把一张表中的一些数据直接复制到另一张表里该怎么做我们就可以使用复制表数据语句insert…select… 它的作用是把select查询出来的结果集直接插入到另一张表里语法insertintotable1[(col1,col2)]selectcol_a,col_bfromtable2where条件;这样的插入语句其实和之前的差不多只是把之前的指定值插入values()换成了插入用select查询的内容列与列一一对应插入select返回的列数量、数据类型必须和insert后面的字段一一对应名字不用一样。聚合查询MySQL内置的有一些聚合函数聚合函数可以针对于表中的某一竖列进行相关运算函数作用注意要点count([distinct] 列)统计某一列或多列有多少行记录count(*)统计这张表整体是多少行count(列名)不统计null值sum([distinct] 列)求和只对数值类型生效忽略nullavg([distinct] 列)求平均值忽略null不会把null当作0参与计算max([distinct] 列)求最大值支持数字、字符串、日期忽略nullmin([distinct] 列)求最小值支持数字、字符串、日期忽略null要注意聚合函数只处理列中的值自动忽略NULL。聚合函数一般搭配 group by 分组使用。​聚合函数都可以加 distinct 进行去重查询不过max、min加了多余最值本来就是一个count()示例表统计该表有多少行selectcount(*)fromexam;查询结果查询指定列有多少行记录selectcount(id)fromexam;会自动忽略掉null值不统计在内sum()计算所有同学语文成绩加起来的总分selectsum(chinese)fromexam;要注意sum()和max()、min()不一样sum()函数不支持字符、日期类型只支持数值类型结果集依然是存入到一个临时表返回给我们的那么对于临时表有以下特点我们定义chinese 字段时可能定义为decimal(5,2)数字最大储存长度为5且保留小数点后2位但是临时表不受此限制所以即使机器计算出语文成绩的总和数的长度大于5临时表依然会完整地把正确答案返回给我们的之前说过null值与任何值运算的结果都是null但是聚合函数在运算时会自动忽略掉null值这是因为MySQL的开发者已经考虑到这个问题了在创建聚合函数时会考虑到业务的真正需求不会那么死板所以在聚合函数运算时会特殊处理掉null值我们在将来的开发过程中也应该学习这样的设计思想应把代码与具体业务需求结合多考虑各种特殊情况当然聚合函数也可以结合where条件查询使用比如这个时候只会对id小于10的列进行聚合查询avg()对所有同学的语文成绩求平均值也是会自动忽略null值聚合函数的参数也可以是表达式也就是对多列进行聚合查询所以我们可以求语文数学英语三门课的总分的平均值同时我们也就可以使用别名增加表头的可读性max()min()找出语文成绩的最高分和英语成绩的最低分分组查询group by当我们使用聚合函数对某一(些)列进行运算、统计的时候可以对该列先进行一些分组然后对每组分别进行原先的聚合查询最后返回每组的查询结果值学生表idnameclassscore1张三1班802李四1班903王五2班85计算出score这一列的平均分但是要分班级进行统计selectclass,avg(score)fromstudentgroupbyclass;SQL执行时就会先对score这一列的数据按班级进行分组然后再该怎么查询就怎么查询avg(score)计算结果classavg(score)1班85.00002班85.0000我们说了分组查询就是针对于聚合函数使用的而select 后面的查询我们加了个 class 字段这是允许的这样查询结果表头就会有class这一列表的可读性就会增加但是要注意SQL标准规定当进行分组查询时select的字段只能有①group by的分组字段 ②聚合函数。说白了当使用group by 字段1时其实select后面就只能写聚合函数只有一种特殊情况可以写一个字段就是select 字段1不然如果在分组查询时select了其他的非group字段就是会造成一个格子里面存放一堆数据的情况比如像上面那样如果select的不是聚合函数也不是字段1而是其他字段那就是查询了一班所有同学的成绩且放在了同一个格子里违反1NF。而聚合函数就不会这样因为它反而是把一堆数据进行运算然后返回一个值不过也可以指定查询结果的小数位数使用round()函数round()作用四舍五入保留指定位小数语法round(数值,保留小数位数)例一selectround(3.1415,2);#结果返回3.14例二第二个参数可以省略 round(4.67) 等价 round(4.67,0) 直接得到整数5。例三和聚合函数搭配--求平均分保留1位小数同时使用别名替换一下表头SELECTclass,ROUND(AVG(score),1)avg_scoreFROMstudentGROUPBYclass;truncate(数值,位数) 直接截断不四舍五入不常用也可继续搭配order by使用selectclass,avg(score)as平均分fromstudentgroupbyclassorderby平均分asc;SQL执行时就会先分组然后对每组进行聚合函数的查询最后对查询结果进行排序哪组在前哪组在后分组字段可以为多个groupby字段1,字段2先按字段1分组然后再在每组内部按照字段2二次细分。原始表 studentclass(班级)gender(性别)name(姓名)score(分数)1班男张三851班男小明751班女小红902班男小李882班女小丽922班女小周82按班级、性别两个字段分组求每组平均分SELECTclass,gender,AVG(score)ASavg_scoreFROMstudentGROUPBYclass,gender;分组执行后返回结果表classgenderavg_score1班男80.00001班女90.00002班男88.00002班女87.0000having之前在使用where子句时是对表中的真实数据进行了过滤select时只查询满足条件的记录。那分组查询的结果其实都是用聚合函数对一组数据进行运算而来的计算值就是不是表中储存的记录了所以对于分组查询的结果我们要使用having去进行条件过滤selectclass,avg(score)asavg_scorefromstudentgroupbyclasshavingavg(score)85;所以要注意where用在from 表名之后也就是分组之前having 跟在group by子句之后也就是分组之后对聚合函数的计算值进行条件过滤所以如果业务需求要对真实数据进行过滤同时也需要对分组查询的计算结果进行过滤那么我们就可以在合适的位置写where和having联合查询上篇联合查询也叫多表查询是工作中用的最多的查询而且面试的时候也非常爱考之前讲过为了满足三大范式在设计表时会对一些关系复杂的表进行拆分从而消除了表中的字段的不合理的依赖关系比如部分函数依赖传递依赖这时会导致在一个表中查询出的数据对于业务需求来说是不完整的完整的数据需要多表的数据进行结合这时我们就可以使用联合查询把多个表的数据结合起来得到完整的业务信息联合查询多表查询 分为内连接、外连接、union联合查询三种内连接内连接是联合查询最常用的一种。它只返回多张表中满足关联条件能够互相匹配上的数据匹配不成功的记录直接舍弃。实际开发中我们经常使用主键‑外键匹配关系下面介绍作为这个关联条件。需要使用联合查询的场景有上两张表而我们真正要查询的信息是这样的需要两表结合先介绍一下联合查询时MySQL是如何执行的查询时会取两张表的笛卡尔积每张表都有着多条记录从两张表分别取一条记录组合为一条新的记录就这样把两张表所有记录的全排列组合得出的所有新的记录组成为一个新的表该新表即为两表的笛卡尔积以上新表就是联合查询的结果语法就是select*from表1,表2;通过观察两张表取笛卡尔积之后有些记录是无效数据所以我们必须要过滤掉这些无效记录我们知道两表是通过主、外键进行关联的上面学生表的外键id 就指向了班级表的主键id所以可以看到上面的笛卡尔积中有些记录的主、外键都不匹配比如第二条一个id1一个id2它就是一个无效的记录。所以我们可以使用where语句来进行主键-外键的匹配校验过滤掉无效数据select*fromstudent,classwherestudent.class_idclass.class_id;而且可以看到其实class_id在两张表中名字重复了所以我们通过表名.字段的方法来指定了字段位置查询结果观察结果集可以发现其实该表中有些列是冗余的所以我们需要通过指定列查询来简化结果表的字段selectstudent.id,student.name,class.namefromstudent,classwherestudent.class_idclass.class_id;当然可以看到既然要查询的字段是分布在不同表的我们在代码中依然使用了表名.字段指定了字段的位置查询结果还可以给表取别名来简化SQL代码selects.id,s.name,c.namefromstudent s,class cwheres.class_idc.class_id因为SQL的执行顺序是先执行from先确定在哪个表操作所以可以在 from 后面为表取个别名其他的语句就都可以用该别名方便简化代码总结一下联合查询的分析步骤首先确定哪几张表要参与查询根据表与表之间的主、外键关系进行主键-外键的匹配校验从而过滤掉无效数据简化查询字段指定列查询这步其实在写代码时最开始就做了联合查询也可以与使用聚合函数的分组查询、order by子句等结合着使用其实并不会增加代码的复杂度因为所谓联合查询就是把多个表拼起来而已其他的查询语句该怎么正常加就怎么正常加就行了就和操作一个表是一样的内连接联合查询还有个更标准的写法但是更常用是还是上面的写法这个不常用select字段1[,字段2]..from表1[inner]join表2on主键-外键校验and其他限制条件相当于把,换成了join把where换成了oninner是可以不写的一般也没人写它三表及以上的多表联合查询注意事项可以先使用desc查看一下各表的结构把需要校验的主、外键匹配先找出来观察课得出上面三表需要验证的主、外键匹配为student.student_idscore.student_id和course.course_id score.course_id之后像之前那样用where语句做好校验即可如果要使用 join on 的标准写法要注意好语法规则内连接可以使用老式写法select ...from 表1,表2,表3 where 关系校验1 and 关系校验2...但后面的外连接就只能使用sql99的新标准了如果使用新标准join-on就一定要注意好语法标准每一个 join 后面必须跟着属于它自己的 on也就是关联校验。并且 on 后面的校验只对它前面刚 join 的两张表有效果比如上图的st.student_id sc.student_id只对score表和student表起作用。但是我们在设计表的时候尽量要保证有关联的表不超过3个原因如下关联表过多会让查询时需要多次 join导致性能下降每次 join 都需要数据库把两个表的数据进行匹配。匹配的过程就是拿一张表的每一行去另一张表里找对应的记录这个过程需要扫描和比较数据。join的次数多了计算量就会增加影响查询速度。理解成本高表关系复杂后业务逻辑难以梳理维护困难。易出错关联越多写错连接条件或产生笛卡尔积的概率越高。所以限制关联表数量是为了保持数据库设计高效、简单、易维护。当然这在复杂业务中并非绝对必要时可适当突破。最后SQL代码也可以学习一下漂亮的代码格式当一句SQL语句较长时就可以这样写代码格式更加美观代码的可读性也更强