world mysql_MySQL从入门到熟练

把练习写在这里或许更能坚持！！！毕竟要过年了！！！

花了一周的时间学习了Mick老师的《SQL基础教程》的前七章，现在要赶紧练习了，毕竟实践是检验。。。练习网站 SELECT basics - SQLZOO，一开始我是懵逼的。。。

工具：MySQL服务端和MySQL客户端Navicat，服务端是数据库软件用于存放数据，客户端工具用于从服务端获取数据，Navicat客户端是图形界面，操作比较方便。

一、Part1:SELECT basics & SELECT name

1.创建表world (自练部分)

好的，创建就遇到了问题，需要加强理解数据类型。

这里把integer换成了bigint后就ok了!

2. SELECT basic

1)修改此例子,以顯示德國 Germany 的人口。

SELECT population FROM world WHERE name = 'Germany';

2)查詢面積為 5,000,000 以上平方公里的國家,對每個國家顯示她的名字和人均國內生產總值(gdp/population)

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;

3. SELECT names

1)找出以 Y 為開首的國家

SELECT name FROM world WHERE name LIKE 'Y%'；

2)找出以 Y 為結尾的國家

SELECT name FROM world WHERE name LIKE '%Y'；

3)找出所有國家,其名字包括字母x

SELECT name FROM world WHERE name LIKE '%x%';

4)找出所有國家,其名字包括三個或以上的a

SELECT name FROM world where name like '%a&a%a%';

5)找出所有國家,其名字以t作第二個字母

SELECT name FROM world WHERE name LIKE '_t%' ORDER BY name;

6)找出所有國家,其名字都有兩個字母 o,被另外兩個字母相隔着

SELECT name FROM world WHERE name LIKE '%o__o%' ORDER BY name;

7)顯示所有國家名字,其首都是國家名字加上”City”(concat函数，连接字符串)

SELECT name from world where capital = concat(name, ' City');

还有一个将字符串进行拼接的函数 拼接两个字符串(str1+str2)是用“||函数”

8)找出所有首都和其國家名字,而首都名要有國家名出現

select capital ,name from world

where capital like concat('%',name,'%')

9)找出所有首都和其國家名字,而首都是國家名字的延伸。 應顯示 Mexico City,因它比其國家名字 Mexico 長。 不應顯示 Luxembourg,因它的首都和國家名相是相同的

SELECT name,name FROM world

WHERE capital LIKE CONCAT('%',name,'%') AND capital != name;

10)顯示國家名字，及其延伸詞，如首都是國家名字的延伸(replace 函数)

REPLACE(String,from_str,to_str)即：将String中所有出现的from_str替换为to_str

SELECT name,replace(capital,name,'') FROM world

WHERE capital LIKE CONCAT(name,'%_')

二、Part2:select from world(使用SELECT语句，对World表格进行查询)

1)如何使用where来筛选记录，找出至少有2亿人口的国家名称和人均国内生产总值

select name,gdp/population from world

where population>=200000000;

2)查询南美洲国家的名字和人口数(单位为百万)

select name,population/1000000 from world

where continent='South America';

3)顯示法國，德國，意大利(France, Germany, Italy)的國家名稱和人口

select name,population from world

where name in ('France','Germany','Italy');

4)顯示包含單詞“United”為名稱的國家

select name from world

where name like '%United%';

5)展示大國的名稱，人口和面積(如果它有3百萬平方公里以上的面積，或擁有250百萬(2.5億)以上人口)

elect name,population,area from world

where area>3000000 or population>250000000;

6)美國、印度和中國(USA, India, China)是人口又大，同時面積又大的國家。排除這些國家。顯示以人口或面積為大國的國家，但不能同時兩者。顯示國家名稱，人口和面積

SELECT name,population,area FROM world

WHERE (area > 3000000 AND population < 250000000)

OR (area < 3000000 AND population > 250000000);

7)對於南美顯示以百萬計人口，以十億計2位小數GDP(ROUND函数)

SELECT name,ROUND(population/1000000,2),ROUND(gdp/1000000000,2) FROM world

WHERE continent='South America';

8)顯示萬億元國家的人均國內生產總值，四捨五入到最近的$ 1000

SELECT name,ROUND(gdp/population,-3) FROM world

WHERE gdp>1000000000000;

度娘得：round(X,N) 四舍五入到 N 位数：N > 0：舍入到小数点右侧的第 N 位数。

N = 0：四舍五入到最接近的整数。

N < 0：舍入到小数点左侧的第 N 位数

9)Show the name - but substituteAustralasiaforOceania- for countries beginning with N.

SELECT name,

CASE WHEN continent='Oceania' THEN 'Australasia'

ELSE continent

END as sub_continent

FROM world

WHERE name LIKE 'N%';

10)Show the name and the continent - but substituteEurasiafor Europe and Asia; substituteAmerica- for each country inNorth AmericaorSouth AmericaorCaribbean. Show countries beginning with A or B

SELECT name,

(CASE WHEN continent IN ('Europe','Asia') THEN 'Eurasia'

WHEN continent IN ('South America','North America','Caribbean') THEN 'America'

ELSE continent END) AS continent

FROM world

WHERE name LIKE 'A%' OR name LIKE 'B%';

或者

SELECT name,

CASE WHEN continent IN ('Europe','Asia') THEN 'Eurasia'

WHEN continent IN ('South America','North America','Caribbean') THEN 'America'

ELSE continent

END AS sub_continent

FROM world

WHERE name LIKE 'A%' OR name LIKE 'B%';

11)Put the continents right...Oceania becomes Australasia

Countries in Eurasia and Turkey go to Europe/Asia

Caribbean islands starting with 'B' go to North America, other Caribbean islands go to South America

Show the name, the original continent and the new continent of all countries.

SELECT name,continent,

CASE WHEN continent='Oceania'

THEN 'Australasia'

WHEN continent IN ('Eurasia','Turkey')

THEN 'Europe/Asia'

WHEN continent='Caribbean'

THEN CASE WHEN name LIKE 'B%'

THEN 'North America'

ELSE 'South America'

END

ELSE continent

END as new_contient

FROM world

ORDER BY name;

三、Part3：SELECT from Nobel

1)顯示誰贏得了1962年文學獎(Literature)

SELECT winner

FROM nobel

WHERE yr = 1962

AND subject = 'Literature'

2)顯示1980年至1989年(包含首尾)的文學獎(Literature)獲獎者所有細節(年，主題，獲獎者)

SELECT * FROM nobel

WHERE yr BETWEEN 1980 AND 1989 AND subject='Literature';

3)顯示總統獲勝者的所有細節：西奧多•羅斯福 Theodore Roosevelt

伍德羅•威爾遜 Woodrow Wilson

吉米•卡特 Jimmy Carte

select * from nobel

where winner in ('Theodore Roosevelt',

'Woodrow Wilson',

'Jimmy Carter');

4)顯示1980年物理學(physics)獲獎者，及1984年化學獎(chemistry)獲得者

select * from nobel

where (yr='1980'and subject='physics')

or (yr='1984'and subject='chemistry');

5)查看1980年獲獎者，但不包括化學獎(Chemistry)和醫學獎(Medicine)

select* from nobel

where yr='1980' and subject not in ('Chemistry','Medicine');

6)騎士列隊 Knights in order列出爵士的獲獎者、年份、獎頁(爵士的名字以Sir開始)。先顯示最新獲獎者，然後同年再按名稱順序排列

select winner,yr,subject from nobel

where winner like 'Sir%'

order by yr desc,winner;

7)Show the 1984 winners and subject ordered by subject and winner name; but list Chemistry and Physics last

SELECT winner, subject

FROM nobel

WHERE yr=1984

ORDER BY subject IN ('Physics','Chemistry'),subject,winner;

order by 语句未指定asc(ascend) 和 desc (descend)，按升序排列

2019.01.27周天，今天就到这里吧，好困！明天继续！！！

----------------------------------------------------------------------------------------------