sqlzoo练习 | 含答案和错题

练习网址

---

https://sqlzoo.net/

SQLZOO答案

---

主要是看@crazy__chen的mysql专栏，全套答案。

https://blog.csdn.net/kangaroo835127729/category_2153583.html

SQLZOO学习笔记

---

列举做题时遇到不会或者容易混淆的题。

做这些题之前，《SQL必知必会》已看过2遍，第1遍看书没装数据环境基本没看懂，第2遍才开始真正学习，实践思考。

1、basics/name

---

LIKE

LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式。

SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern

CONCAT

CONCAT(str1,str2,…)  

SQL CONCAT函数用于将两个字符串连接起来，形成一个单一的字符串。
返回结果为连接参数产生的字符串。如有任何一个参数为NULL ，则返回值为 NULL。

顯示所有國家名字,其首都和是國家名字加上”City”

SELECT name FROM world WHERE capital = concat(name,'City')

REPLACE

REPLACE ( string_expression , string_pattern , string_replacement )

网上摘录的这段解释觉得很好：

REPLACE(String,from_str,to_str)

即：将String中所有出现的from_str替换为to_str。

如果任何一个参数为 NULL，则返回 NULL。

address字段里的 “区” 替换为 “呕” 显示

select*,replace(address,'区','呕') as rep from test_tb

​

"Monaco-Ville"是合併國家名字 "Monaco" 和延伸詞"-Ville".
顯示國家名字，及其延伸詞，如首都是國家名字的延伸。

SELECT name, replace(capital,name,'') FROM world WHERE capital LIKE concat(name,'_%') 

2、world

---

ROUND

https://www.cnblogs.com/ChengDong/articles/2601340.html

​

使用ROUND(population/1000000,2)保留小数点右起第2位；

ROUND(gdp/population,-3)保留小数点左起第3位，也就是到千位；

SELECT name, ROUND(population/1000000,2), ROUND(gdp/1000000000,2) FROM world WHERE continent = 'South America' 

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

>>>>补充：如果 Round（列名） 也是可行的，默认Round（列名,0）

CASE WHEN THEN ELSE

https://blog.csdn.net/konglongaa/article/details/80250253

上面的链接解释的很全面，分为2种搜索方式。

--简单case函数

case sex
when '1' then '男'
when '2' then '女'
else '其他' end

--case搜索函数

case 
when sex = '1' then '男'
when sex = '2' then '女'
else '其他' end

>>>>要注意，case when else end是完整函数，中间不需要逗号（，）隔开

IN

IN 操作符允许我们在 WHERE 子句中规定多个值。

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...)

3、nobel

---

字符串中的单引号'

不能把一個單引號直接的放在字符串中，可連續使用兩個單引號在字符串中當作一個單引號。也就是如果字符串中含有'，你要输入''（两个单引号）才能正常执行。

字符串或串(String)是由数字、字母、下划线组成的一串字符，它是编程语言中表示文本的数据类型。

ORDER BY

asc 是升序，也就是从小到大；

desc 是降序，也就是从大到小；

下面列举对两列数据进行排序的三种输入方法。

-- order by中如果定义了多个字段，则按照字段的先后顺序排序。
order by winner asc, name desc 
order by winner,yr desc
order by subject in('男','女'), winner asc, name desc

DISTINCT

SELECT DISTINCT 列名称 FROM 表名称

選擇代碼以顯示有多少年沒有頒發醫學獎。

​

正确答案是2。

当时看这两个选项都有些混淆，选择2而非1的原因在于：

按照1的筛选方法，子查询中会把不是医学奖，但是跟医学奖同年的年份筛选出来，所以数据会错误。

>>>>>> 练习发现：DISTINCT不是非得和SELECT连着使用。

4、select

---

​

解题思路：

1、先找出英国的人均GDP值

2、然后再找出人均GDP值大于【英国的人均GDP值】的欧洲国家

​

解题思路：

1、先找出阿根廷和澳大利亚所在的州份

2、然后再找出在这个两个州份的国家

BEWTEEN 

操作符 BETWEEN ... AND 会选取介于两个值之间的数据范围。这些值可以是数值、文本或者日期。

​

这里引起的主要问题在于：BETWEEN 是包含确定范围值的两个值。

>>>>>>  练习发现：BETWEEN 值1 AND 值2  其实是 值1 ≤ 未知数x ≤值2，也就是会含2个值，所以图中方法2的解法正确。

​

这里又犯了相同的错误，又没有查找Germany的值，应该默认所有值都是在数据库检索出来的。

>>>>>>>  练习发现：ROUND（列名，小数点），如果写成ROUND（列名）也是可行的，小数点默认为0。这点没找到相关说明，但是我用数据库跑了一下，可执行，且不带小数点和0小数位数的执行结果相同。

5、sum

---

HAVING语句

下面这段来自百度，感觉解释的很好。

HAVING语句通常与GROUP BY语句联合使用，用来过滤由GROUP BY语句返回的记录集。

HAVING语句的存在弥补了WHERE关键字不能与聚合函数联合使用的不足。

语法：

同样使用本文中的学生表格，如果想查询平均分高于80分的学生记录可以这样写：

HAVING AVG(score)>=80;

在这里，如果用WHERE代替HAVING就会出错。

SELECT id, COUNT(course) as numcourse, AVG(score) as avgscore

FROM student

GROUP BY id

- QUIZ

​

这里主要是 'IN' 'LIKE' '=' 三者之间的区别，三者之间的区别是？

答：经过数据库进行测试

'=' 只能对一个值    >>>>>>    = '1987'

'IN'对多个值，且值必须用（）圈起来    >>>>>>   IN ('1987','1988')

'LIKE'搜索特定模式，经常会和'%'一起用   >>>>>>   LIKE '19%'

IN 操作符

允许我们在 WHERE 子句中规定多个值。

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...)

---

LIKE 操作符

用于在 WHERE 子句中搜索列中的指定模式。

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern

6、join

---

https://www.cnblogs.com/reaptomorrow-flydream/p/8145610.html

​

连表查询时：

1、筛选条件一定要记得指定哪个表中的哪个列

2、JOIN 默认跟ON一起使用，ON后面放的是匹配条件

​

使用COUNT函数时，千万要注意分组 Group by。不分组，输出的数据不准确，如图。

​

突然有1个疑问点：DISTINCT后面有2列时，是去重1列还是2列？

找到答案：https://www.cnblogs.com/pearsonlee/p/11653035.html

也就是对2列都起作用，去掉后跟列名同时重复的行记录。

DISTINCT这个关键字来过滤掉多余的重复记录只保留一条，但往往只用它来返回不重复记录的条数，而不是用它来返回不重记录的所有值。其原因是distinct只能返回它的目标字段，而无法返回其它字段，

​

>>>>练习发现：HAVING是只能对聚合好的数值进行计算。比如聚合后共5个ID 15行，计算的是5个ID5组的数值。

显示每个地区的总人口数和总面积

SELECT region, SUM(population), SUM(area) FROM bbc GROUP BY region

先以region把返回记录分成多个组，这就是GROUP BY的字面含义。分完组后，然后用聚合函数对每组中的不同字段（一或多条记录）作运算。

7、more join

---

​

三个表的连接，下面来自百度：

inner join(等值连接) 只返回两个表中联结字段相等的行
left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录
right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录

INNER JOIN 语法：

INNER JOIN 连接两个数据表的用法：
SELECT * FROM 表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号

INNER JOIN 连接三个数据表的用法：
SELECT * FROM (表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号

练习发现：

1、三个表的连接不加（）也可以，数据库测试如果有t1t2t3三张表，默认t1跟t2连，然后t3跟t2连

2、JOIN 和INNER JOIN查询出来的结果一样，默认两者都是等值查询

3、LEFT JOIN 就是 LEFT OUTER JOIN，同理 RIGHT JOIN 就是 RIGHT OUTER JOIN，FULL JOIN就是 FULL OUTER JOIN

8、Using Null

---

COALESCE

COALESCE takes any number of arguments and returns the first value that is not null.

-- 此函数返回第一个不是空值的数值
COALESCE(x,y,z) = x if x is not NULL 
-- x如果不是空值就返回x
COALESCE(x,y,z) = y if x is NULL and y is not NULL
-- x是空值，y不是空值就返回y
COALESCE(x,y,z) = z if x and y are NULL but z is not NULL
-- x,y是空值，z不是空值就返回z
COALESCE(x,y,z) = NULL if x and y and z are all NULL
-- x,y,z都是空值，返回NULL

​

看这个实例，用coalesce返回mobile列的数值，有值就返回值，没值就返回指定值。

​

count后面对一个以上的列，要分开计算 >>>>>>> count（值1）,count（值2）

9、self join

---

就是自连接，自己跟自己连接。下面文档的解释看起来最清晰。

https://www.cnblogs.com/Sky_KWolf/archive/2010/12/15/1906325.html

A self-join is a query in which a table is joined (compared) to itself. Self-joins are used to compare values in a column with other values in the same column in the same table. The SQL self-join can be done by using table aliases to cheat one table like a different table and then join them together.

易混淆的知识点总结：

1、distinct 对其后的每列都去重，输出的是不完全重复的行

2、count只对紧跟其后的1列进行计算，如要计算2列，需要2个count ，比如count(colume1),count(colume2)

3、order by 会对其后的多个列按照顺序进行升序展示（降序需指定）

4、group by 后跟多个列，会对其后的所有列按照顺序进行分组（分组的使用一般伴随聚合函数SUM、COUNT等）

5、having一般和group by一起使用，作用相当于where，会以group by分好的组为单位进行 行过滤