sql语句练习mysqlweixin44390208的博客-

一.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