数据库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='肥胖';
5.复合查询
mysql> select sno,sname,cno from stu union select cno,cname,teacher from class;
复合查询指用集合运算符对多个查询结果集进行运算,产生新的查询结果集。
MySQL常用集合运算符包括以下2种:
union对两个结果集进行并集操作,重复行只取一次,同时进行默认规则的排序
union all对两个结果集进行并集操作,包括所有重复行,不进行排序
MySQL不支持交集和差集运算,可以通过多表连接的方式实现
创建示例表
create table class1 as select * from class where cno=1;
insert into class1 values(5,'5班','董卓');
select * from class;
select * from class1;
1.并集union
求表class和class1的并集,重复记录只显示一次
mysql> select * from class union select * from class1;
默认排序为增序,顺序列1、列2、列3......
可以自定义排序,order by写在语句的最后
去掉重复
2.union all 重复显示俩表记录
求表class和class1的并集,重复记录重复显示
mysql> select * from class union all select * from class1;
结果默认不排序
3.求表class和class1的交集
mysql无intersect集合运算符,交集运算可以通过多表连接实现
mysql> select c.* from class c join class1 c1 on c.cno=c1.cno;
4.求表class和class1的差集,即显示class表中在class1表中没有的行记录
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;
5.学生表按照班号分组统计各班人数,也要显示出合计总人数
select ifnull(cno,'-') 班号,count(*) 人数 from stu group by cno union select '合计:',count(*) from stu;
学生表按照班号分组统计各班人数,