where group by联合使用


where group by联合使用

前言

昨天学习了表与表之间的关系、多表关联、复制表、单表查询的知识,今天学习的内容是单表查询与关键词的执行顺序、正则表达式以及最重要的多表查询。

 

 

数据库增删改

 

select 列a,聚合函数 from 表名 where 过滤条件 group by 列a having
过滤条件

insert [into] 表名[] values,,...;

into 可以省略,表名后的字段可选,如果写了后面的 values
中的值必须与表名后的字段意义对应,如果没写后面的 values
中的值必须与表的所有字段一一对应,values后面可以给出多组值并用逗号隔开。

 

group by
字句也和where条件语句结合在一起使用。当结合在一起时,where在前,group by
在后。即先对select xx from xx的记录集合用where进行筛选,然后再使用group
by 对筛选后的结果进行分组 使用having字句对分组后的结果进行筛选

delete from 表名[where 条件]; 

如果条件不写的话是删除所有记录,不过这样删除表的效率很低,因为是一行行删除数据,自增的
id不会归零。使用truncate
是重建表,先记录表结构然后删除整个表再重新建表出来,自增的 id 会归零。

2.聚合函数使用时必须有

需要注意having和where的用法区别:

update 表名 set 字段名 = 值,[,字段2 = 值2],[where 条件];

可以一次性修改多个字段的值,值之间需要用逗号隔开;如果不写条件的话就是修改所有记录。

 

1.having只能用在group
by之后,对分组后的结果进行筛选(即使用having的前提条件是分组)。

单表查询

 

2.where肯定在group by 之前

不带关键字的查询

select (*|字段名|四则运算|聚合函数) from 表名 [where 条件]; 

准备数据

mysql> create table stu(id int primary key auto_increment,       name char,       math int,       english int);mysql> insert into stu values(null,'赵云',90,30);mysql> insert into stu values(null,'小乔',90,60);mysql> insert into stu values(null,'小乔',90.60);mysql> insert into stu values(null,'大乔',10,70);mysql> insert into stu values(null,'李清照',100,100);mysql> insert into stu values(null,'铁拐李',20,55);mysql> insert into stu values(null,'小李子',20,55);

表示查询所有字段

mysql> select * from stu;

澳门金沙vip 1

字段名 可以手动指定要查询的字段

mysql> select engish from stu;

澳门金沙vip 2

字段的值可以进行加减乘除

统计总分

mysql> select math+ english from stu;

澳门金沙vip 3

如果觉得 math+english 名字太长也可以取别名

mysql> select math+english [as] 总分 from stu; 

as可以省略

给英语成绩加分

mysql> select english+10 from stu;

澳门金沙vip 4

聚合函数 用于统计

什么是聚合函数,将多个数据进行计算,并得到一个结果,称为聚合

聚合函数:

注意不能再 where 后面使用聚合函数,因为 where
相当于打开文件然后读取文件中的数据,而使用聚合函数很显然需要多个值来聚合,那么没有读取完数据就使用不了聚合函数,所以这时候应该使用
on,所以聚合函数不能写在 where
后面,where会最先执行,它的作用是读取数据并过滤

  • sum

mysql> select sum from emp;

澳门金沙vip 5

  • count

mysql> select count from emp group by dept;

澳门金沙vip 6

  • avg

mysql> select avg from emp;

澳门金沙vip 7

  • max/min

mysql> select max from emp;mysql> select min from emp;

澳门金沙vip 8

结合使用:

mysql> select dept,count from emp group by dept;

澳门金沙vip 9

where 是可选的

比如有一个表

3.where后的条件表达式里不允许使用聚合函数,而having可以。

关键字的执行顺序

澳门金沙vip 10

四、当一个查询语句同时出现了where,group by,having,order
by的时候,执行顺序和编写顺序是:

from

澳门金沙vip,用于打开文件

求平均成绩是用聚合函数

1.执行where xx对全表数据做筛选,返回第1个结果集。

distinct

去除重复数据,所有数据全都重复才算重复

mysql> select distinct * from stu;

澳门金沙vip 11

 

2.针对第1个结果集使用group by分组,返回第2个结果集。

where

对读取的数据进行过滤

where 后面跟的条件比较多:

澳门金沙vip 12

3.分组,排序,再次筛选,取几条执行顺序

3.针对第2个结果集中的每1组数据执行select
xx,有几组就执行几次,返回第3个结果集。

between and

mysql> select * from stu where english between 70 and 80;

澳门金沙vip 13

4.如上表要求查询

4.针对第3个结集执行having xx进行筛选,返回第4个结果集。

in

mysql> select * from stu where math in ;

澳门金沙vip 14

方法一:分步查询

5.针对第4个结果集排序。

like

mysql> select * from stu where name like '李%';

澳门金沙vip 15

澳门金沙vip 16

先查出

例子:

and

mysql> select * from stu where math > 80 and english > 80;

澳门金沙vip 17

mysql> select * from stu where math > 60 and english < 60;

澳门金沙vip 18

然后再查这些同学的平均分,需要

完成一个复杂的查询语句,需求如下:

group by

对数据进行分组,为了进行统计。group by
后面可以有多个分组依据,会按照顺序执行

准备数据

mysql> create table emp(       id int,       name char,       sex char,       dept char,       job char,       salary double);mysql> insert into emp values(       (1,'刘备','男','市场','总监',5800),       (2,'张飞','男','市场','员工',3000),       (3,'关羽','男','市场','员工',4000),       (4,'孙权','男','行政','总监',6000),       (5,'周瑜','男','行政','员工',5000),       (6,'小乔','女','行政','员工',4000),       (7,'曹操','男','财务','总监',10000),       (8,'司马懿','男','财务','员工',6000)       );

便得到表

按由高到低的顺序显示个人平均分在70分以上的学生姓名和平均分,为了尽可能地提高平均分,在计算平均分前不包括分数在60分以下的成绩,并且也不计算贱人(jr)的成绩。

查看所有部门

mysql> select dept from emp group by dept;

澳门金沙vip 19

分组后,组里的详细记录就被隐藏起来了,不能直接查看,dept
分组就变成三条记录,每个组中会包含隐藏的记录,没办法显示,如果一定想显示的话,可以使用
group_concat,可以将多个值拼接为一个值:

mysql> select dept,group_concat from emp group by dept;

澳门金沙vip 20

注意:只有出现在 group by 后面的字段,才可以通过 select
显示出来,其他的都被隐藏了。在
mysql5.6分组后会默认显示每组的第一条记录,5.7不显示,因为5.7中sql_mode中就是ONLY_FULL_GROUP_BY

澳门金沙vip 21

name

张三

李四

 分析:

查看每个部分有多少人

mysql> select dept,count from emp group by dept;

澳门金沙vip 22

 

1.要求显示学生姓名和平均分

计算每个部门的平均薪资

mysql> select dept,avg from emp group by dept;

澳门金沙vip 23

取别名

mysql> select dept,avg 平均工资 from emp group by dept;

澳门金沙vip 24

注意sql的执行顺序

因此确定第1步

计算每个岗位的平均工资

mysql> select job,avg from emp group by job;

澳门金沙vip 25