数据库 MySQL 练习
一.sql语句基础
1.显示德国 Germany 的人口
SELECT population FROM world
WHERE name = 'Germany'
2.查询面积为 5,000,000 以上平方公里的国家,对每个国家显示她的名字和人均国内生产总值
SELECT name, gdp/population FROM world
WHERE area > 5000000
3.显示“Ireland 爱尔兰”,“Iceland 冰岛”,“Denmark 丹麦”的国家名称和人口。
SELECT name, population FROM world
WHERE name IN ('Ireland', 'Iceland', 'Denmark');
4.显示面积为 200,000 及 250,000 之间的国家名称和该国面积。
SELECT name, area FROM world
WHERE area BETWEEN 200000 AND 250000
二.文字样式匹配查询
1. 找出以 Y 为开首的国家。
SELECT
`name`
FROM
world
WHERE
`name` LIKE 'Y%'
找出以 Y 为结尾的国家。
SELECT
`name`
FROM
world
WHERE
`name` LIKE '%Y'
3. 找出所有国家,其名字包括字母x。
SELECT
`name`
FROM
world
WHERE
`name` LIKE '%x%'
4. 找出所有国家,其名字以 land 作结尾。
SELECT
`name`
FROM
world
WHERE
`name` LIKE '%land'
5. 找出所有国家,其名字以 C 作开始,ia 作结尾。
SELECT
`name`
FROM
world
WHERE
`name` LIKE 'C%ia'
6. 找出所有国家,其名字包括字母oo。
SELECT
`name`
FROM
world
WHERE
`name` LIKE '%oo%'
7. 找出所有国家,其名字包括三个或以上的a。
SELECT
`name`
FROM
world
WHERE
`name` LIKE '%a%a%a%'
8. 找出所有国家,其名字以t作第二个字母。
SELECT
`name`
FROM
world
WHERE
`name` LIKE '_t%'
ORDER BY
`name`
9. 找出所有国家,其名字都有两个字母 o,被另外两个字母相隔着。
SELECT
`name`
FROM
world
WHERE
`name` LIKE '%o__o%'
10. 找出所有国家,其名字都是 4 个字母的。
SELECT
`name`
FROM
world
WHERE
`name` LIKE '____'
11. 显示所有国家名字,其首都和国家名字是相同的。
SELECT
`name`
FROM
world
WHERE
`name` = capital
12. 显示所有国家名字,其首都是国家名字加上”City”。
SELECT
`name`
FROM
world
WHERE
capital = concat(
`name`,
' City'
)
13. 找出所有首都和其国家名字,而首都要有国家名字中出现。
SELECT
capital,
`name`
FROM
world
WHERE
capital LIKE concat(
'%',
`name`,
'%'
)
14. 找出所有首都和其国家名字,而首都是国家名字的延伸。
SELECT
`name`,
capital
FROM
world
WHERE
capital LIKE concat( `name`, '%' )
AND (capital != `name`)
15. 显示国家名字，及其延伸词，如首都是国家名字的延伸。
SELECT
`name`,
REPLACE ( capital, `name`, '' ) AS ext
FROM
world
WHERE
capital LIKE concat( `name`, '%' )
AND (capital != `name`)
三.SELECT from WORLD Tutorial
观察运行一个简单的SQL命令的结果。
`SELECT name, continent, population FROM world`
显示具有至少2亿人口的国家名称。 2亿是200000000，有八个零。
SELECT name FROM world
WHERE population > 200000000
找出有至少200百万(2亿)人口的国家名称，及人均国内生产总值。
SELECT name,gdp/population FROM world
WHERE population > 200000000
显示’South America’南美洲大陆的国家名字和以百万为单位人口数。将人口population 除以一百万(1000000)得可得到以百万为单位人口数。
SELECT name, population/1000000 FROM world
WHERE continent = 'South America'
显示法国，德国，意大利(France, Germany, Italy)的国家名称和人口。
SELECT name, population FROM world
WHERE name IN ('France','Germany','Italy')
显示包含单词“United”为名称的国家。
SELECT name FROM world
WHERE name LIKE '%United%'
成为大国的两种方式：如果它有3百万平方公里以上的面积，或拥有250百万(2.5亿)以上人口。
SELECT name, population, area FROM world
WHERE area > 3000000 OR population > 250000000
美国、印度和中国(USA, India, China)是人口又大，同时面积又大的国家。排除这些国家。
SELECT name, population, area FROM world
WHERE (area > 3000000 AND population < 250000000) OR(area < 3000000 AND population > 250000000)
除以为1000000（6个零）是以百万计。除以1000000000（9个零）是以十亿计。使用 ROUND 函数来显示的数值到小数点后两位。
SELECT name, ROUND(population/1000000, 2), ROUND(gdp/1000000000, 2) FROM world
WHERE continent = 'South America'
显示国家有至少一个万亿元国内生产总值（万亿，也就是12个零）的人均国内生产总值。四舍五入这个值到最接近1000。
SELECT name, ROUND(gdp/population/1000)*1000 FROM world
WHERE gdp > 1000000000000
四.SUM and COUNT 群组函数
1.展示世界的总人口。
SELECT SUM(population)
FROM world
2.列出所有的洲份, 每个只有一次。
SELECT DISTINCT(continent)
FROM world
3.找出非洲(Africa)的GDP总和。
SELECT SUM(gdp)
FROM world
WHERE continent = 'Africa'
4.有多少个国家具有至少百万(1000000)的面积。
SELECT COUNT(name)
FROM world
WHERE area > 1000000
5.(‘France’,‘Germany’,‘Spain’)（“法国”，“德国”，“西班牙”）的总人口是多少？
SELECT SUM(population)
FROM world
WHERE name IN ('France', 'Germany', 'Spain')
6.对于每一个洲份，显示洲份和国家的数量。
SELECT continent, COUNT(name)
FROM world
GROUP BY continent
7.对于每一个洲份，显示洲份和至少有1000万人(10,000,000)口国家的数目。
SELECT continent, COUNT(name)
FROM world
WHERE population > 10000000
GROUP BY continent
8.列出有至少100百万(1亿)(100,000,000)人口的洲份。
SELECT continent
FROM world
GROUP BY continent
HAVING SUM(population)>= 100000000
五.JOIN 合并两个表格
1.修改此SQL以列出 赛事编号matchid 和球员名 player ,该球员代表德国队Germany入球的。要找出德国队球员，要检查: teamid = ‘GER’
SELECT matchid, player FROM goal
WHERE teamid LIKE 'GER'
2.只显示赛事1012的 id, stadium, team1, team2
SELECT id,stadium,team1,team2
FROM game
WHERE id = 1012
3.修改它来显示每一个德国入球的球员名，队伍名，场馆和日期。
SELECT player, teamid, stadium, mdate
FROM game ga JOIN goal go ON (ga.id = go.matchid)
WHERE teamid LIKE 'GER'
4.列出球员名字叫Mario (player LIKE ‘Mario%’)有入球的 队伍1 team1, 队伍2 team2 和 球员名 player
SELECT team1, team2, player
FROM game ga JOIN goal go ON (ga.id = go.matchid)
WHERE player LIKE 'Mario%'
5.列出每场球赛中首10分钟gtime<=10有入球的球员 player, 队伍teamid, 教练coach, 入球时间gtime
SELECT player, teamid, coach, gtime
FROM eteam e JOIN goal g ON (e.id = g.teamid)
WHERE gtime < 10
6.列出’Fernando Santos’作为队伍1 team1 的教练的赛事日期，和队伍名。
SELECT mdate, teamname
FROM eteam e JOIN game g ON (e.id = g.team1)
WHERE coach = 'Fernando Santos'
7.列出场馆 ‘National Stadium, Warsaw’的入球球员。
SELECT player
FROM game ga JOIN goal go ON (ga.id = go.matchid)
WHERE stadium = 'National Stadium, Warsaw'
8.列出全部赛事，射入德国龙门的球员名字。
SELECT DISTINCT(player)
FROM game ga JOIN goal go ON (ga.id = go.matchid)
WHERE teamid!='GER' AND (team1 = 'GER' OR team2 = 'GER')
9.列出队伍名称 teamname 和该队入球总数
SELECT teamname, COUNT(teamid)
FROM goal g JOIN eteam e ON (g.teamid= e.id)
GROUP BY teamname
10.列出场馆名和在该场馆的入球数字。
SELECT stadium, COUNT(player)
FROM game ga JOIN goal go ON (ga.id= go.matchid)
GROUP BY stadium
11.列出赛事编号 matchid, 日期date 和入球数字。
SELECT matchid, mdate, COUNT(teamid)
FROM game ga JOIN goal go ON (ga.id= go.matchid)
WHERE (ga.team1 = 'POL' OR ga.team2 = 'POL')
GROUP BY matchid, mdate
12.列出赛事编号 matchid, 日期date 和德国的入球数字。
SELECT matchid, mdate, COUNT(teamid)
FROM game ga JOIN goal go ON (ga.id= go.matchid)
WHERE (ga.team1 = 'GER' OR ga.team2 = 'GER') AND teamid = 'GER'
GROUP BY matchid, mdate
