3.where
mysql> select * from world.city where id=1; +----+-------+-------------+----------+------------+ | ID | Name | CountryCode | District | Population | +----+-------+-------------+----------+------------+ | 1 | Kabul | AFG | Kabol | 1780000 | +----+-------+-------------+----------+------------+ #查询甘肃省所有城市信息 SELECT * FROM city WHERE district=\'gansu\'; #查询中国人口数量大于500w SELECT * FROM city WHERE countrycode=\'CHN\' AND population>5000000; #查询中国或美国城市信息 SELECT * FROM city WHERE countrycode=\'CHN\' OR countrycode=\'USA\';**4.范围查询> 、 < 、<> **
mysql> mysql> select * from world.city where id <10; +----+----------------+-------------+---------------+------------+ | ID | Name | CountryCode | District | Population | +----+----------------+-------------+---------------+------------+ | 1 | Kabul | AFG | Kabol | 1780000 | | 2 | Qandahar | AFG | Qandahar | 237500 | | 3 | Herat | AFG | Herat | 186800 | | 4 | Mazar-e-Sharif | AFG | Balkh | 127800 | | 5 | Amsterdam | NLD | Noord-Holland | 731200 | | 6 | Rotterdam | NLD | Zuid-Holland | 593321 | | 7 | Haag | NLD | Zuid-Holland | 440900 | | 8 | Utrecht | NLD | Utrecht | 234323 | | 9 | Eindhoven | NLD | Noord-Brabant | 201843 | +----+----------------+-------------+---------------+------------+ mysql> select * from world.city where countrycode=\'CHN\'; +------+---------------------+-------------+----------------+------------+ | ID | Name | CountryCode | District | Population | +------+---------------------+-------------+----------------+------------+ | 1890 | Shanghai | CHN | Shanghai | 9696300 | | 1891 | Peking | CHN | Peking | 7472000 | | 1892 | Chongqing | CHN | Chongqing | 6351600 | | 1893 | Tianjin | CHN | Tianjin | 5286800 | | 1894 | Wuhan | CHN | Hubei | 4344600 | | 1895 | Harbin | CHN | Heilongjiang | 4289800 | | 1896 | Shenyang | CHN | Liaoning | 4265200 | | 1897 | Kanton [Guangzhou] | CHN | Guangdong | 4256300 | | 1898 | Chengdu | CHN | Sichuan | 3361500 | | 1899 | Nanking [Nanjing] | CHN | Jiangsu | 2870300 | | 1900 | Changchun | CHN | Jilin | 2812000 | | 1901 | Xi´an | CHN | Shaanxi | 2761400 | | 1902 | Dalian | CHN | Liaoning | 2697000 | ...... #查询世界上人口数量大于100w小于200w的城市信息 SELECT * FROM city WHERE population >1000000 AND population <2000000; SELECT * FROM city WHERE population BETWEEN 1000000 AND 2000000;**5.联合查询 **
mysql> select * from city where countrycode=\'CHN\' union all select * from city where countrycode=\'USA\'; mysql> select * from world.city where countrycode in (\'CHN\',\'USA\'); ...... | 2250 | Huaying | CHN | Sichuan | 89400 | | 2251 | Pingyi | CHN | Shandong | 89373 | | 2252 | Huangyan | CHN | Zhejiang | 89288 | | 3793 | New York | USA | New York | 8008278 | | 3794 | Los Angeles | USA | California | 3694820 | | 3795 | Chicago | USA | Illinois | 2896016 | | 3796 | Houston | USA | Texas | 1953631 | | 3797 | Philadelphia | USA | Pennsylvania | 1517550 | ......6.limit
#取前十 mysql> mysql> select * from world.city limit 10; +----+----------------+-------------+---------------+------------+ | ID | Name | CountryCode | District | Population | +----+----------------+-------------+---------------+------------+ | 1 | Kabul | AFG | Kabol | 1780000 | | 2 | Qandahar | AFG | Qandahar | 237500 | | 3 | Herat | AFG | Herat | 186800 | | 4 | Mazar-e-Sharif | AFG | Balkh | 127800 | | 5 | Amsterdam | NLD | Noord-Holland | 731200 | | 6 | Rotterdam | NLD | Zuid-Holland | 593321 | | 7 | Haag | NLD | Zuid-Holland | 440900 | | 8 | Utrecht | NLD | Utrecht | 234323 | | 9 | Eindhoven | NLD | Noord-Brabant | 201843 | | 10 | Tilburg | NLD | Noord-Brabant | 193238 | +----+----------------+-------------+---------------+------------+ #加步长 ~= 翻页 mysql> select * from world.city limit 0,5; +----+----------------+-------------+---------------+------------+ | ID | Name | CountryCode | District | Population | +----+----------------+-------------+---------------+------------+ | 1 | Kabul | AFG | Kabol | 1780000 | | 2 | Qandahar | AFG | Qandahar | 237500 | | 3 | Herat | AFG | Herat | 186800 | | 4 | Mazar-e-Sharif | AFG | Balkh | 127800 | | 5 | Amsterdam | NLD | Noord-Holland | 731200 | +----+----------------+-------------+---------------+------------+ mysql> select * from world.city limit 5,5; +----+-----------+-------------+---------------+------------+ | ID | Name | CountryCode | District | Population | +----+-----------+-------------+---------------+------------+ | 6 | Rotterdam | NLD | Zuid-Holland | 593321 | | 7 | Haag | NLD | Zuid-Holland | 440900 | | 8 | Utrecht | NLD | Utrecht | 234323 | | 9 | Eindhoven | NLD | Noord-Brabant | 201843 | | 10 | Tilburg | NLD | Noord-Brabant | 193238 | +----+-----------+-------------+---------------+------------+ mysql> select * from world.city limit 10,5; +----+-----------+-------------+---------------+------------+ | ID | Name | CountryCode | District | Population | +----+-----------+-------------+---------------+------------+ | 11 | Groningen | NLD | Groningen | 172701 | | 12 | Breda | NLD | Noord-Brabant | 160398 | | 13 | Apeldoorn | NLD | Gelderland | 153491 | | 14 | Nijmegen | NLD | Gelderland | 152463 | | 15 | Enschede | NLD | Overijssel | 149544 | +----+-----------+-------------+---------------+------------+