学习四:高级查询语句
标签(空格分隔): mysql
一,聚合函数
- count
select count(*) FROM school.student; //查询总个数
select count(distinct name) FROM school.student; //去除name重复的数据个数
- max
select max(birthday) FROM school.student;
- min
select min(birthday) FROM school.student;
- sum 求和
select sum(sid) FROM school.student;
- avg 求平均值
select avg(sid) FROM school.student;
二,分组查询
- group by
select CountryCode,count(*) FROM world.city group by CountryCode limit 5;
- group by having
where是在分组前对数据进行分组
having是在分组后进行分组
select CountryCode,count(*) cnt FROM world.city group by CountryCode having cnt>2 limit 5;
select CountryCode,count(*) cnt FROM world.city group by CountryCode having cnt>2 order by cnt desc limit 5;
三,联合查询 union
- union
select * FROM world.city where id<5
union
select * FROM world.city where id>10 and id<15
union前后连接的表的字段类型必须相同,显示的列名和前一个表保持一致。
三,交叉查询
- 交叉连接
select * from world.city t_city,world.country t_country;
- 内连接 inner join on
select count(*) from world.city t_city inner join world.country t_country on t_city.CountryCode=t_country.Code; //注释:只返回符合条件的table1和table2的列
- 外连接
select count(*) from world.country t_country left join world.city t_city on t_city.CountryCode=t_country.Code;//包含t_country的所有子句,根据指定条件返回t_country相应的字段,不符合的以null显示
select count(*) from world.country t_country right join world.city t_city on t_city.CountryCode=t_country.Code; //包含t_country的所有子句,根据指定条件返回t_city相应的字段,不符合的以null显示
use world;
select count(*) from country full join city on Code=city.CountryCode;
//完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时,则另一个表的选择列表列包含空值。如果表之间有匹配行,则整个结果集行包含基表的数据值
四,子查询
- in
select * from world.city where CountryCode in (select code FROM world.country where Name='Afghanistan');
- exists
select * from world.city where exists (select code FROM world.country where Name='Afghanistan');
EXISTS用于检查子查询是否至少会返回一行数据,该子查询实际上并不返回任何数据,而是返回值True或False。