11.26 MySQL学习记录

select *
from world.country;

select *
from world.city;

select *
from world.countrylanguage;

/*分组与排序*/
select continent, count(*) as num_country
from world.country
group by continent
having count(*) >= 25
order by num_country desc;

/*利用子查询进行过滤两个不同表的信息*/
select Name
from world.country
where Code in (select countrycode
from world.countrylanguage
where language in ('English'));

/*inner join创建联结*/
select world.city.*, world.countrylanguage.*
from world.city inner join world.countrylanguage
on world.city.countrycode = world.countrylanguage.countrycode;

/*自联结*/
select a.*, b.*
from world.city a, world.countrylanguage b
where a.countrycode = b.countrycode
and b.countrycode in ('nld');

/*使用union组合查询*/
select name, code, population
from world.country
where continent in ('Oceania', 'Antarctica')
union
select name, code, population
from world.country
where name in ('sudan')
order by population desc;