MySQL基础【多表查询、单行函数、分组函数，针对查询的一些练习】

1、多表查询

        多表联接：select * form table1,table2;，如表1中有10条数据，表2中有20条数据，执行完语句，会产生10 * 20 = 200条数据，通过这种方法产生的数据称为笛卡尔集，笛卡尔集中的数据绝大多数都是垃圾, 必须使用行过滤（在where中添加条件）。

        查询所有国家名称及首都名称。

select
    co.name,
    ci.name
from
    country co,
    city ci
where
    co.capital = ci.id;

        查询所有国家及这个国家的所有城市.

select
    co.name countryName,
    ci.name cityName
from
    country co,
    city ci
where
    co.code= ci.countrycode;
    

        联接条件重要于过滤条件，SQL99标准, 用内联代替逗号联接。select 表1 join 表2 on 联接条件 where 普通过滤条件

        查询所有亚洲国家名称和首都名称。

select
    co.name countryName,
    ci.name capitalName
from
    country co
join
    city ci
on
    co.capital = ci.id    --联接条件
where    
    co.continent = 'asia';    --普通条件

        内联接的逻辑是从笛尔集中取出来的是满足联接条件的记录. 有可能会导致某张表的数据不完整，使用左（右）外联接, 保证左（右）表数据完整。

        select 表1 left outer join 表2 on 联接条件 where 普通过滤条件，outer可省略，因为内联无左右之分。外联时on不可以用where替换。

        查询<所有>国家及国家的首都，没有首都国家用null表示。

select
    co.name,
    ci.name,
    co.capital,
    ci.id
from
    country co
left join
    city ci
on
    co.capital = ci.id;

        查询国家及国家的首都和官方语言, 如果没有首都和官方语言也要显示国家。

select
    co.name,
    ci.name,
    cl.language,
    cl.isofficial
from
    country co
left join
    city ci
on
    co.capital = ci.id
left join
    countrylanguage cl
on
        co.code = cl.countrycode
    and
        cl.isofficial = 't';

        外联效率更高。

2、单行函数

        作用于结果集中的单行（每一条记录）。

select 
	upper(name),    --将name列变为大写
	now(),
	concat(continent, code2)    --字符拼接
from 
	country;

        查询国家表中的数据, 把国家名称,大洲, 国家代码 连接起来, 每个字段使用'=>'连接。

select
    concat(name,'=>',continent,'=>',code)
from
    country;

3、分组函数

        作用于一组数据，一般为虚表中的所有数据，对这一组数据返回一个值，也叫做统计处理。

        avg() 平均，max() 最大，min() 最小，count() 计数，sum()求和。

        查询世界上的平均人口，最大国土面积。        

select
    avg(population),
    max(surfacearea)
from
    country;

        查询世界上有多少个国家。

select
    count(name)    --如果有null值，计数时忽略，所有组函数都会忽略
from
    country;

        查看全球人均最高和最低寿命。

select
    --name,        --代表个体的列不能出现
    max(LifeExpectancy),    
    min(LifeExpectancy)
from
    country;

        查看中国的人口最少的城市的人口。

select
    min(population)
from
    city
where
    countrycode = 'chn';

        获取表中的记录数, 使用count(*)最好，标准做法。

select
    count(*)
from
    country;

        group by 字句。

select
    continent,        -- 如果有group by , 必须让分组依据的列放在select中.
    max(population)
from
    country
group by
    continent;

        查询全球的各种政府组织形式的个数。

select
    governmentform,
    count(*)
from
    country
group by
    governmentform;

        查看中国各省各有多少个城市及城市总人口。

select
    district,
    count(*),
    sum(population)
from
    city
where
    countrycode = 'chn'
group by
    district
order by
    sum(population);

        对分组的虚表进行过滤, 必须使用having。

        having的执行晚于select, 所以可以使用列的别名。

select 
	GovernmentForm,
	count(*) sum
from 
	country
group by 
	GovernmentForm
having
	sum> 10
order by 
	sum;

        使用having查看中国各省各有多少个城市及城市总人口。having执行依赖于select。

select
	countrycode,
	district,
	count(*),
	sum(population)
from 
	city 
group by 
	district
having 
	countrycode = 'chn';

        两者比较where（第一种）效率更高。

        语句执行顺序 : 
        from            确定基表
        join              如果一张基表不够, 再联接其他表
        on               如果有联接表 必须要有on
        where          过滤总基表中的行
        group by      分组, 分组依据的列.
        select          把分组依据的列放在select后, 再考虑要选择哪些列, 及进行哪些函数调用....
        having         进一步把分组后的虚表行过滤
        order by      最终表的一个排序显示.

练习：

        查询每个州各有多少种政府组织形式和每个州最富有的收入。

select
    continent,
    count(distinct governmentform),
    max(gnp)
from
    country
group by
    continent;

        查询哪些国家城市人口总数大于1亿。

select
    countrycode,
    sum(population)
from
    city
group by
    countrycode
having
    sum(population) > 100000000;

        列出所有在超过10个国家中使用的语言。

select
    language,
    count(*)
from
    countrylanguage cl
group by
    language
having
    count(*) > 10;

        列出不同的国家(country code)有居民超过7,000,000的城市， 它们有多少？

select
    countrycode,
    count(*)
from
    city
where
    population > 7000000
group by
    countrycode;

        每个国家各有多少种语言

select
    countrycode,
    count(*)
from
    countrylanguage
group by
    countrycode;

        Sweden国家说的是什么语言？        

select
    cl.language,
    co.name
from
    countrylanguage cl
join
    country co 
on
    cl.countrycode = co.code
where
    co.name = 'sweden';

        哪些国家没有列出使用任何语言？

        方法一：

select
    co.name,
    cl.language
from
    country co
left join
    countrylanguage cl
on
    co.code = cl.countrycode
where
    cl.language is null;

        方法二：

select
    co.name,
    count(cl.language) ct
from
    country co
left join
    countrylanguage cl
on
    co.code = cl.countrycode
group by
    co.name
having
    ct = 0;

        方法一效率更高。

        列出在城市表中80%人口居住在城市的国家。

        方法一：

select
    co.name,
    sum(ci.population) / co.population as rate
from
    city ci
join
    country co
on
    ci.countrycode = co.code
group by
    co.name
having
    rate > 0.8;

        方法二：

select 
	co.continent,
	co.name,
	sum(ci.population) sumPop,
	co.population
from 
	city ci 
join 
	country co 
on 
	ci.countrycode = co.code 
group by 
	co.name
having 
	sumPop / co.population > 0.8;

        查询哪些国家没有官方语言。

select
    co.name,
    cl.language,
    cl.isofficial
from
    country co
left join
    countrylanguage cl
on
        co.code = cl.countrycode
    and
        cl.isofficial = 'T'
where
    cl.language is null;

        查询亚洲国家的各省的总城市数量和平均人口数, 哪些平均人口大于50万, 降序显示总城市数量。        

select
	co.name,
	district,
	count(*),
	avg(ci.population) avgPop
from 
	country co 
join 
	city ci 
on 
	co.code = ci.countrycode 
where 
	co.continent = 'asia'
group by 
	district 
having 
	avgPop > 500000 
order by 
	count(*) desc;

        查询所有国家的首都和使用率最高的官方语言

select 
	co.name,
	ci.name,
	cl3.language,
	cl3.percentage
from 
	country co 
left join 
	city ci 
on 
	co.capital = ci.id 
left join 
	(select 
		cl.countrycode,
		cl.language,
		cl.percentage,
		cl.isofficial
	from 
		countrylanguage cl 
	join  
		(select countrycode, max(Percentage) maxPer from countrylanguage where isofficial = 'T' group by countrycode) cl2
	on 
			cl.countrycode = cl2.countrycode 
		and 
			cl.percentage = cl2.maxPer
	where 
		cl.isofficial = 'T'
	) cl3
on 
	co.code = cl3.countrycode 
order by 
	cl3.percentage;