11.21 MySQL學習記錄

2017-11-21 17:51:04

select *
from world.city;

select countrycode
from world.city;

/*distinct關鍵字作用於所有列，不僅僅跟著的後面那列*/
select distinct countrycode
from world.city;

/*檢索前五行數據*/
select district
from world.city
limit 5;

/*從第十行開始，檢索五行數據*/
select district
from world.city
limit 10,5;

/*按照字母順序排列*/
select Name
from world.city
order by name;

/*先按GNP排列 再按Continent排列 (order by 2,3可以達到同樣的效果，但更改時候容易出錯)*/
select name, GNP, Continent
from world.country
order by GNP, Continent;

/*想在多個列上進行降序，需要在目標列後加desc*/
select name, region, population
from world.country
order by population desc;

/*order by語句需要放在where語句之後*/
select name, region, population
from world.country
where population >= 1000000
order by population;

select name, region, population
from world.country
where population between 5000000 and 10000000;

/*and關鍵字鏈接兩個條件 or關鍵字滿足其中一個條件就可以把數據返回*/
select name, district, population
from world.city
where CountryCode = 'PHL' and Population >= 400000

/*任何時候使用具有or和and操作符的where字句，都應該使用圓括弧)*/
select name, district, population
from world.city
where (CountryCode = 'PHL' or CountryCode = 'BRA' )
and Population >= 400000