数据库 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