SQL 查询

将练习数据 world.sql 导入数据库中

SQL
source world.sql;

查看表

SQL
show tables;

+-----------------+
| Tables_in_world |
+-----------------+
| city            |
| country         |
| countrylanguage |
+-----------------+
3 rows in set (0.00 sec)

查看表 city 结构

SQL
desc city

+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name        | char(35) | NO   |     |         |                |
| CountryCode | char(3)  | NO   | MUL |         |                |
| District    | char(20) | NO   |     |         |                |
| Population  | int(11)  | NO   |     | 0       |                |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

city 有多少个中国的城市？

SQL
select * from city where CountryCode='CHN';

查询 city 表中，山西省的城市名？

SQL
select * from city where district='shanxi';

查询 city 表中，山西省和河北省的城市名？

SQL
select * from city where district='shanxi' or district='hebei' ;

查询 city 表中，山西省和河北省的城市中人口大于 100w？

SQL
select * from city where (district='shanxi' or district='hebei') and Population >1000000 ;

查询 city 表中，要求只显示城市名和人口数量，山西省和河北省的城市名按人口数量排序，升序？

SQL
select Name,Population from city where district='shanxi' or district='hebei'order by Population;

查询city表中，要求只显示城市名和人口数量，山西省和河北省的城市名按人口数量排序，降序？

SQL
select Name,Population from city where district='shanxi' or district='hebei' order by Population desc ;

查询 city 表中，要求只显示城市名和人口数量，山西省和河北省的城市名按人口数量前5名；

SQL
select Name,Population from city where district='shanxi' or district='hebei' order by Population desc limit 5;

查询 city 表中，要求只显示城市名和人口数量，山西省和河北省的城市名按人口数量第2名和第3名；

SQL
select Name,Population from city where district='shanxi' or district='hebei' order by Population desc limit 1,2;

查询 city 表中,所有中国省份中带 an 的城市

SQL
select * from city where countrycode='chn' and district like '%an%' ;

查询 city 表中，所有中国的城市人口在 89000 和 89999 之间的城市

SQL
select * from city where countrycode='chn' and Population  between 89000 and 89999 ;

练习：

查询 city 表中，要求只显示城市名和人口数量,查询CHN人口最多的前5个城市？

查询 city 表中，要求只显示城市名和人口数量,查询CHN人口最少的前5个城市？

查询中国的城市数量？

SQL
select count(name) as 中国城市总数 from city where countrycode='CHN';

查询世界的国家数量？

SQL
select count(name) from country;

查询中国的总人口？

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

把多行合并成一行

SQL
select group_concat(name) from city where countrycode='chn' and district='hebei';

把多列合并成一列

SQL
select concat(Name,"#",CountryCode,"#",District) from city where countrycode='chn' and district='hebei';

UNION

合并两个 select 查询结果

SQL
CREATE TABLE `c1` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `Name` char(35) NOT NULL DEFAULT '',
  `District` char(20) NOT NULL DEFAULT '',
  `Population` int NOT NULL DEFAULT '0',
 PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `c2` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `Name` char(35) NOT NULL DEFAULT '',
  `District` char(20) NOT NULL DEFAULT '',
  `Population` int NOT NULL DEFAULT '0',
 PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

insert into c1(ID,Name,District,Population) select ID,Name,District,Population from city where CountryCode='CHN' and District='Hebei';

insert into c2(ID,Name,District,Population) select ID,Name,District,Population from city where CountryCode='CHN' and District='Henan';

select * from c1 union select * from c2 order by Population;