数据库select group by 和having语句多表连接查询复合查询

1.SELECT --group by 子句

group by 子句按照指定的列column_name对表数据进行分组

group by 后面跟的列也叫分组特性列

使用group by后，能 选择的列通常只能包括分组特性列 和 聚合函数

聚合函数 ???

1.按照班号分组，列出学生表中的班号 (注意：按照班号进行分组，班号就不会有重复值)

select cno from stu group by cno;cno分组特性列

2.按照班号分组，列出学生表中的班号，还要列出学生姓名

select sname,cno from stu group by cno;

查询报错

注意：学生有20人，姓名一共20行记录，班号分组去重后有4行记录，20行无法与4行拼接在一起

使用group by后，能选择的列通常只能包括分组特性列和聚合函数

除非用group_concat字符串聚合函数把每个班的学生姓名变成字符串，每个班一行：

select cno,group_concat(sname) from stu group by cno;

3.按照班号分组，列出学生表中的班号，统计每个班的平均身高，平均体重，人数，最高分，不包括未分班的那些同学

select cno 班号,avg(height) 平均身高,avg(weight) 平均体重,count(*) 人数,max(score) 最高分 from stu where cno is not null group by cno;

4.先按照班号分组，再按照性别分组，列出学生表中的班号和性别，统计出每个班男女生的平均身高，平均体重，人数，最高分，不包括未分班的那些同学，结果先按班号，再按照男女s的顺序排序

select cno 班号,sex 性别,avg(height) 平均身高,avg(weight) 平均体重,count(*) 人数,max(score) 最高分 from stu where cno is not null group by cno,sex order by cno,sex desc;

5.按照学生出生年份分组，统计出所有学生每个年份的人数，最高分，最低分，按照年份排序

select year(birth) 出生年份,count(sno) 人数,max(score) 最高分, min(score) 最低分 from stu group by year(birth) order by 1; +分组特性列和函数

2.SELECT - HAVING子句

HAVING子句是对group by产生的结果集的过滤

HAVING子句可以对分组特性列column_name进行过滤，也可以对聚合函数(aggregate_function(列))的值进行过滤

1.按照学生出生年份分组，统计出所有学生每个出生年份的人数，最高分，最低分，按照年份排序，并从结果中找出人数超过2个，并且最高分有超过700分的年份分组

select year(birth) 出生年份,count(sno) 人数,max(score) 最高分, min(score) 最低分 from stu group by year(birth) having count(*)>2 and max(score)>700 order by 1;

分组特性列+分组函数 having有 where没有

2.已分班的学生中，哪些班学生的平均身高超过175，列出其班号和人数

select cno 班号,count(*) 人数 from stu where cno is not null group by cno having avg(height)>175 order by 1;

已分班的学生中，哪些班的学生每个人的体重 都超过50公斤，列出其班号和人数

select cno 班号,count(*) 人数 from stu where cno is not null group by cno having min(weight)>50 order by 1;

统计1班的学生人数，列出班号和人数

select cno 班号,count(*) 人数 from stu group by cno having cno=1;

或者

select 1 班号,count(*) 人数 from stu where cno=1;

第一种方法先使用group by统计，再用having过滤统计结果，统计了和1班不相干的其他班级的人数，浪费了系统CPU资源，效率低；

第二种方法，先用where子句过滤掉了不相干班级的人员，然后直接统计1班的人数，效率高

统计人数超过5个的班号和人数，结果按照班号排序，只显示一行记录

select cno 班号,count(*) 人数 from stu where cno is not null group by cno having 人数>5 order by 1 limit 1;

也可以写为：

select cno 班号,count(*) 人数 from stu where cno is not null group by 班号 having 人数>5 order by 班号 limit 1;

也可以写为：

select cno 班号,count(*) 人数 from stu where cno is not null group by 班号 having 人数>5 order by 班号 limit 1;

MySQL查询语句参考执行步骤：

了解查询语句发到MySQLD后，服务器端的处理步骤有助于降低语句书写错误的可能性（注：不同数据库处理步骤略有不同）。

初学查询常见的错误如下：

select cno,count(*) from stu where count(*)>5;

where子句第二步执行，要调用count(*)第五步产生的结果，结果还没有产生，报错！

select cno 班号 from stu where 班号=1;

where子句第二步执行，调用至少第三步才能产生的别名，报错！

where和having的区别(以例5举例说明)

相同点：

都是对表行按照条件进行的筛选

不同点：

where对原始的stu表按照条件筛选行

having对分组后的新表按照条件筛选行

3.视图 VIEW 创建视图语法视图主要作用视图做DML操作

属于关系型数据库中的一种常用数据对象，保存一段select语句，可以把视图名称当作普通表来使用

创建视图语法：

CREATE VIEW vtbl_name as select_statement;

查看视图

视图主要作用：

1.提高数据安全性（隐藏部分行和列）

2.简化查询

可以将常用的查询语句写成视图的形式被其他查询调用，这样可以降低查询语句的复杂度，提高可读性

1.对视图做DML操作查看有哪些视图查看视图的定义：

视图仅仅保存SELECT语句，其select语句中的表称为基表（或母表），视图对应的数据是放在基表中的，基表中数据产生变化，视图的结果集也会产生变化。即：视图里面的数据发生变化，其实是基表中数据的变化所致

如果视图和其相关基表存在记录一一对应的关系，排除基表上存在约束的条件，通常可以对视图做DML操作，但是不要轻易对视图做DML操作，以防止对基表数据产生意想不到的影响

查看有哪些视图：

show table status\G

其中“comment”值为“view”的表是视图

查看视图的定义：

show create table view_name;

4.多表连接查询

将分散在多个表中的信息（列）横向合并在一起

通常需要指明连接条件

一般会根据表列的实际业务含义进行连接，这样才有实际意义

多表连接查询和单表查询相比会耗费更多的系统资源

语法

不使用表别名

select table1.column, table2.column from

table1 [inner|left|right] join table2

on table1.col1 = table2.col1;

使用表别名

select a.column,b.column from

table1 a [inner|left|right] join table2 b

on a.col1=b.col1;

分类

交叉连接

等值连接

内联接

左连接（左外连接）

右连接（右外连接）

非等值连接

创建2个测试表 a和b

create table a (id int, name char(10));

create table b (id int, loc char(10));

insert into a values (1,'a');

insert into a values (2,'b');

insert into a values (4,'d');

insert into b values (1,'x');

insert into b values (2,'y');

insert into b values (3,'z');

1.交叉连接笛卡儿尔积

无连接条件，结果记录数= 3 * 3 =9

select * from a cross join b;

2.等值连接--内连接

有连接条件，结果记录数= 3 * 3 =9

select * from a inner join b on a.id=b.id;

结果集显示符合连接条件的

a表中的行，b表中的行，a表的id有1,2，b表的id也有1,2；

如果不符合连接条件，结果集将无记录

3.等值连接-左连接

有连接条件，左表的记录会全部显示

select * from a left join b on a.id=b.id;

左指的是，上面语句中a写在了b的左边，结果集显示左表a的所有行，右表b中符合连接条件的行，a表的id有1,2,4，b表的id没有4，b表中不符合连接条件的行会用NULL值来填充

4.等值连接-右连接

有连接条件，右表的记录会全部显示

select * from a right join b on a.id=b.id;

右指的是，上面语句中b写在了a的右边，结果集显示右表b的所有行，左表a中符合连接条件的行，b表的id有1,2,3，a表的id没有3，a表中不符合连接条件的行会用NULL值来填充

5.非等值连接

多表连接条件可以分为等值和非等值两种

等值连接条件

select * from a join b on a.id=b.id; 2条记录

非等值连接条件

select * from a join b on a.id>b.id; 4条记录

select * from a join b on a.id<b.id; 3条记录

以上 a.id=b.id;a.id>b.id;a.id<b.id三种查询记录数加起来是9条

等于2个表交叉连接的数量

还有其他的写法，根据业务需求定，有实际意义最重要

select * from a join b on a.id between 1 and 3 and b.id<4;;

......

6.多表连接查询-示例

显示学生的学号，姓名，性别，班号，班级名称以及他们的班主任姓名，按照班号和学号排序 (等值连接-内连接)

mysql> select s.sno 学号,s.sname 姓名,s.sex 性别,s.cno 班号,c.cname 班名,c.teacher 班主任 from stu s inner join class c on s.cno=c.cno order by s.cno,s.sno;

显示全部学生的学号，姓名，性别，班号，班级名称以及他们的班主任姓名，无班主任的显示‘暂无’，按照班号和学号排序 (等值连接-左连接)

mysql> select s.sno 学号,s.sname 姓名,s.sex 性别,s.cno 班号,c.cname 班名,ifnull(c.teacher,'暂无') 班主任 from stu s left join class c on s.cno=c.cno order by s.cno,s.sno;

显示所有学生的学号，姓名，性别，身高，体重，班号，BMI指数（体重/身高^2）,BMI表中对应的体态，低值，高值和性别 (非等值连接)

mysql> select s.sno 学号,s.sname 姓名,s.sex 性别,s.height 身高,s.weight 体重,s.cno 班号,round(weight/(height/100*height/100),2) BMI指数,b.lval 低值,b.hval 高值,bname 体态,b.sex 性别 from stu s join bmi b on s.sex=b.sex and round(weight/(height/100*height/100),2) between b.lval and b.hval;

基于例3的结果，把肥胖的同学和他们的班主任找出来 (三个表连接)

mysql> select s.sno 学号,s.sname 姓名,s.sex 性别,s.height 身高,s.weight 体重,s.cno 班号,round(weight/(height/100*height/100),2) BMI指数,b.lval 低值,b.hval 高值,bname 体态,b.sex 性别,c.cname 班名,c.teacher 班主任 from stu s left join bmi b on s.sex=b.sex and round(weight/(height/100*height/100),2) between b.lval and b.hval left join class c on s.cno=c.cno where b.bname='肥胖';