mysql开始全部日记,MySQL学习日记—节选SQLZOO习题（1）

前言

最近的开发任务是要从零开始搭建一个网站，刚开始是准备做一个纯前端页面，但是出于后期维护和拓展的考虑，最后还是准备使用MVC框架来搭建，那么开发的第一步就是要建立数据库了，因此这段时间也在学习MySQL。

之后也会记录下网站开发的动态，相信这次开发可以让我熟悉掌握整个网站开发的流程，也能更加深入地理解MVC、数据库等相关技术。

这个系列主要记录在SQLZOO上的部分经典和困难的习题答案，并附上相关知识点的解析。

书写规范

在正式开始之前，先交代一下SQL的常用书写规范：

多条SQL语句是使用分号(;)隔开的(注意是英文分号)。单条语句的句尾可以不加分号，但如果是使用命令行输入时，则必须加上分号表示结束该语句。本系列将使用分号结束每个语句。

SQL是不区分大小写的，不过为了使得SQL语句易于阅读和调试，SQL关键字最好还是使用大写。本系列关键字将会大写标注(图形化界面往往会自动识别关键字并转化为大写)。

在处理SQL语句时，所有空格都会被忽略掉。将SQL语句按关键字分配成多行将更容易阅读和调试。

在SQL语句中如果遇到表或者数据项名称与关键字名称雷同，应当给操作对象名添加引号(’ '/ " ")。SQL不大区分单引号和双引号，不过如果字符串本身带有了单引号，那么就只能添加双引号了。

SELECT basics

查询数据

通用查询语法如下：

SELECT column_name1,column_name2，...

FROM table_name

[WHERE condition1 [and/or] condition2 ...]

首先，SELECT语句可以查询单列和多列数据，查询多列时使用逗号(,)将不同的column_name隔开。如果要查询表中的所有列，直接将column_name替换为星号通配符(*)即可。

WHERE语句可以筛选出指定条件的数据项。同样也能添加多个条件，同时满足基本的逻辑关系：与和或。

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

SELECT population

FROM world

WHERE name='Germany';

同时，在SQL语句中，SELECT后的项目也是可以直接进行数值计算的。

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

SELECT name,gdp/population

FROM world

WHERE area>5000000;

条件筛选(过滤数据)

IN操作符常在WHERE子句中出现，用来指定条件范围，对范围中每个条件都可以进行匹配，满足一个即可。

题3.顯示“Ireland 愛爾蘭”,“Iceland 冰島”,“Denmark 丹麥”的國家名稱和人口。

SELECT name,population

FROM world

WHERE name in ("Ireland","Iceland","Denmark");

BETWEEN与AND连用作为范围查询条件，将检索出包含边界以及边界内的数据项。

题4.修改此例子,以顯示面積為 200,000 及 250,000 之間的國家名稱和該國面積。

SELECT name,area

FROM world

WHERE area BETWEEN 200000 AND 250000;

SELECT from WORLD

LIKE子句

LIKE操作符常在WHERE子句中出现，与通配符进行搭配对数据进行模糊查询。LIKE可以使用OR关联多个模糊条件进行查询。

百分号通配符(%)可以代替任意长度的字符，而下划线通配符(_)则只能代替一位字符。

题6.顯示包含單詞“United”為名稱的國家。

SELECT name

FROM world

WHERE name LIKE '%United%';

复合条件

WHERE子句中的条件可以有多层嵌套。

题8.顯示以人口或面積為大國的國家，但不能同時兩者。顯示國家名稱，人口和面積。

SELECT name,population,area

FROM world

WHERE (area>=3000000 AND population<250000000)

OR (population>=250000000 AND area<3000000);

ROUND()函数

ROUND() 函数用于把数值字段舍入为指定的小数位数。语法如下：

SELECT ROUND(column_name,decimals)

FROM table_name;

第一个参数填入要舍入的字段，第二个参数(decimals)填入要返回的小数位数。如果填入参数只有第一个，那么将会返回该参数四舍五入的整数。

题9.對於南美顯示以百萬計人口，以十億計2位小數GDP。

SELECT name,ROUND(population/1000000,2),

ROUND(gdp/1000000000,2)

FROM world

WHERE continent='South America';

如果第二个参数填入负数，则表示用科学计数法，以十进制为基础，参数大小表示乘方次数。

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

SELECT name,ROUND(gdp/1000000000,-3)

FROM world

WHERE gdp>=1000000000000;

流程控制CASE

流程控制不是很常用(因为MySQL必知必会这本书都没收录)，可以做一个了解。

CASE语句相当于常用编程语言中的If…else语句，用于条件分支选择，也经常与ELSE进行搭配使用。语法为：

SELECT CASE WHEN condition1 THEN value1

[WHEN condition2 THEN value2]

...

ELSE def_value

END

FROM table_name;

题12.Show the name and the continent - but substitute Eurasia for Europe and Asia; substitute America - for each country in North America or South America or Caribbean. Show countries beginning with A or B

SELECT name,

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

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

ELSE continent

END AS continent

FROM world

WHERE name like 'A%' or 'B%';

其中使用了AS命令给这个分支语句的列重新命名为continent。

题13.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' AND name LIKE 'B%' THEN 'North America'

WHEN continent='Caribbean' AND name NOT LIKE 'B%' THEN 'South America'

ELSE continent

END AS new_continent

FROM world;

题13无论如何提交总是会报错，但是答案应该没错(最近这个网站更新了，现在没错了)。里面的NOT LIKE表示排除该模糊条件的查询。

SELECT from Nobel

不等号

WHERE子句的不等号使用大于小于号(<>)来表示，当然也可以用不等号(!=)来表示。

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

SELECT *

FROM nobel

WHERE yr=1980 AND subject <> 'Chemistry' AND subject <> 'Medicine';

双引号

使用双引号来标识具有单引号的字符串。

题12.查找尤金•奧尼爾EUGENE O’NEILL得獎的所有細節 Find all details of the prize won by EUGENE O’NEILL

SELECT *

FROM nobel

WHERE winner="EUGENE O'NEILL";

排序

使用order by关键字对检索后的数据进行排序，在后面添加关键字ASC表示升序排列，DESC表示降序排列。可以添加多个排序项，用逗号隔开，具体的语法如下：

SELECT field1, field2,...fieldN

FROM table_name1, table_name2...

[WHERE condition]

ORDER BY field1 [ASC [DESC][默认 ASC]], [field2...]

当然不一定排序条件要求必须是要查询的列。

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

SELECT winner,yr,subject

FROM nobel

WHERE winner like "Sir%"

ORDER BY yr DESC,winner ASC;

如果需要实现诸如将某些特定的项排到最前或者最后，需要借助IN操作符使排序列数据量化为0或1的值。举例说明：

题14.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;

其实本质上IN操作符可以看做是一个判断符，满足条件的项就会返回1，不满足则返回0，在order by后面添加上该项计算结果，那么值为0的就会排在值为1的前面了。

总结

这一章节主要介绍了检索查询数据中最常规的一些操作，总体来看还是比较简单的。所以当一个CRUD boy还是很容易的，不是吗？

参考资料

《MySQL必知必会》