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

1.SELECT --group by 子句

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

 

 

 

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

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

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

聚合函数  ???

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

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

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

 

 

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;

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

 

 

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

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

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

 

 

 

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;

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

 

 

 

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

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

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

 

 

2.SELECT - HAVING子句

 数据库select group by 和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没有

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

 

 

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

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

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

 

 

 

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

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

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

 

 

 

 

 

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

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

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

 

 

或者

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

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

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

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

 

 

 

 

 

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

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

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

 

 

也可以写为:

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

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

 

 

也可以写为:

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

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

 

 

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

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

 

 

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

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

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

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

 

 

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

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

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

 

 

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

 

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

相同点:

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

不同点:

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

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

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

 

 

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

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

创建视图语法:

CREATE VIEW vtbl_name as select_statement;

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

 

 

 

查看视图

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

 

 

视图主要作用:

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

2.简化查询

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

 

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

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

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

 

查看有哪些视图:

show table status\G

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

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

 

 

 

查看视图的定义:

show create table view_name;

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

 

 

4.多表连接查询

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

 

 

 

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

通常需要指明连接条件

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

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

 

语法

不使用表别名

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

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

 

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

 

 

 

 

 

 

 

1.交叉连接  笛卡儿尔积

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

select * from a cross join b;

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

 

 

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

 

 

 

 

2.等值连接--内连接

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

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

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

 

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

 

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

 

 

 

 

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

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

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

 

3.等值连接-左连接

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

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

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

 

 

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

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

 

 

 

4.等值连接-右连接

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

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

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

 

 

 

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

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

 

 

 

5.非等值连接

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

等值连接条件

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

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

 

 

 

非等值连接条件

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

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

 

 

 

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

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

 

 

以上 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;;

......

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

 

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

 

 


6.多表连接查询-示例
 

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

 

 

 

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

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;

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

 

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

 

 

 

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

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;

 

 

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

 

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

 

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

 

 

 

 

 

显示所有学生的学号,姓名,性别,身高,体重,班号,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;

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

 

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

 

 

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

 

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

 

 

 

 

 

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

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

 

 

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=‘肥胖‘;

 

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

 

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

 

 

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

 

 

 

5.复合查询

mysql> select sno,sname,cno from stu union select cno,cname,teacher from class;

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

 

 

 

 

 

 

 

复合查询指用集合运算符对多个查询结果集进行运算,产生新的查询结果集。

 

MySQL常用集合运算符包括以下2种:

union对两个结果集进行并集操作,重复行只取一次,同时进行默认规则的排序

union all对两个结果集进行并集操作,包括所有重复行,不进行排序

 

MySQL不支持交集和差集运算,可以通过多表连接的方式实现

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

 

 

 

创建示例表

create table class1 as select * from class where cno=1;

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

 

 

insert into class1 values(5,‘5班‘,‘董卓‘);

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

 

 

 

select * from class;

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

 

 

 

select * from class1;

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

 

 

 

 

1.并集union

求表class和class1的并集,重复记录只显示一次

mysql> select * from class union select * from class1;

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

 

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

 

 

 

 

默认排序为增序,顺序列1、列2、列3......

可以自定义排序,order by写在语句的最后

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

 

去掉重复

 

2.union all 重复显示俩表记录

求表class和class1的并集,重复记录重复显示

mysql>  select * from class union all select * from class1;

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

 

 

 

结果默认不排序

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

 

 

 

 

 

3.求表class和class1的交集

mysql无intersect集合运算符,交集运算可以通过多表连接实现

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

 

 

mysql> select c.* from class c join class1 c1 on c.cno=c1.cno;

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

 

 

 

4.求表class和class1的差集,即显示class表中在class1表中没有的行记录

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

 

 

mysql> select * from class c left join class1 c1 on c.cno=c1.cno;

mysql> select * from class c left join class1 c1 on c.cno=c1.cno where c1.cno is null;

mysql> select c.cno,c.cname,c.teacher from class c left join class1 c1 on c.cno=c1.cno where c1.cno is null;

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

 

 

5.学生表按照班号分组统计各班人数,也要显示出合计总人数

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

 

 

select ifnull(cno,‘-‘) 班号,count(*) 人数 from stu group by cno union select ‘合计:‘,count(*) from stu;

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

 

 

学生表按照班号分组统计各班人数,

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

 

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

 

 

 

 

1.SELECT --group by 子句

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

 

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

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

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

聚合函数  ???

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

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

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

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;

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

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

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

 

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

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;

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

 

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

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

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

2.SELECT - HAVING子句

数据库select group by 和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没有

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

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

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

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

 

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

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

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

 

 

 

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

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

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

或者

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

 

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

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

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

 

 

 

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

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

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

也可以写为:

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

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

也可以写为:

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

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

 

 

 

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

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

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

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

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

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

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

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

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

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

 

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

相同点:

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

不同点:

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

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

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

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

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

创建视图语法:

CREATE VIEW vtbl_name as select_statement;

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

 

查看视图

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

视图主要作用:

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

2.简化查询

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

 

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

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

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

 

查看有哪些视图:

show table status\G

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

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

 

查看视图的定义:

show create table view_name;

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

4.多表连接查询

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

 

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

通常需要指明连接条件

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

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

 

语法

不使用表别名

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

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

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

1.交叉连接  笛卡儿尔积

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

select * from a cross join b;

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

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

 

 

2.等值连接--内连接

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

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

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

 

数据库select group by 和having语句 多表连接查询 复合查询结果集显示符合连接条件的

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

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

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

3.等值连接-左连接

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

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

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

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

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

 

4.等值连接-右连接

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

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

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

 

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

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

 

5.非等值连接

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

等值连接条件

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

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

 

非等值连接条件

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

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

 

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

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

以上 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;;

......

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

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

 

6.多表连接查询-示例

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

 

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

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;

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

 

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

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;

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

 

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

 

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

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

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;

 

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

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

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

 

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

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

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=‘肥胖‘;

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

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

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

 

5.复合查询

mysql> select sno,sname,cno from stu union select cno,cname,teacher from class;

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

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

复合查询指用集合运算符对多个查询结果集进行运算,产生新的查询结果集。

 

MySQL常用集合运算符包括以下2种:

union对两个结果集进行并集操作,重复行只取一次,同时进行默认规则的排序

union all对两个结果集进行并集操作,包括所有重复行,不进行排序

 

MySQL不支持交集和差集运算,可以通过多表连接的方式实现

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

 

创建示例表

create table class1 as select * from class where cno=1;

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

 

insert into class1 values(5,‘5班‘,‘董卓‘);

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

 

select * from class;

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

 

select * from class1;

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

 

1.并集union

求表class和class1的并集,重复记录只显示一次

mysql> select * from class union select * from class1;

 

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

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

默认排序为增序,顺序列1、列2、列3......

可以自定义排序,order by写在语句的最后

 

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

去掉重复

 

 

 

2.union all 重复显示俩表记录

求表class和class1的并集,重复记录重复显示

mysql>  select * from class union all select * from class1;

 

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

结果默认不排序

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

 

 

 

3.求表class和class1的交集

mysql无intersect集合运算符,交集运算可以通过多表连接实现

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

mysql> select c.* from class c join class1 c1 on c.cno=c1.cno;

 

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

4.求表class和class1的差集,即显示class表中在class1表中没有的行记录

 

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

mysql> select * from class c left join class1 c1 on c.cno=c1.cno;

mysql> select * from class c left join class1 c1 on c.cno=c1.cno where c1.cno is null;

mysql> select c.cno,c.cname,c.teacher from class c left join class1 c1 on c.cno=c1.cno where c1.cno is null;

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

5.学生表按照班号分组统计各班人数,也要显示出合计总人数

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

select ifnull(cno,‘-‘) 班号,count(*) 人数 from stu group by cno union select ‘合计:‘,count(*) from stu;

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

学生表按照班号分组统计各班人数,

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

 

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

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

上一篇:centos7下YUM mysql5.7安装与配置


下一篇:(二十三)数据库:主从一致性,主主一致性如何保障?