数据库 MySQL 练习
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