背景

目前复习sql语言，在前几篇博客详细描述了sql的语法，但是在刷sqlzoo过程中遇到一些没有学习的知识点，在这篇博客进行总结。

之前的复习笔记，可以作为参考：https://blog.zhanbaozhan.com/Foools/article/details/109248894

---

---

一、round函数

ROUND 函数用于把数值字段舍入为指定的小数位数。decimals可以设置为负数。

SQL ROUND() 语法
SELECT ROUND(column_name,decimals) FROM table_name

例题
Show the name and population in millions and the GDP in billions for the countries of the continent ‘South America’. Use the ROUND function to show the values to two decimal places.
For South America show population in millions and GDP in billions both to 2 decimal places.

select name,round(population/1000000,2),round(gdp/1000000000,2) from world 
where continent='South America'
1
2

二、left函数

You can use the function LEFT to isolate the first character.本质上是一个截断操作，string类型的数据，进行从左端开始截取。

例题
Show the name and the capital where the first letters of each match. Don’t include countries where the name and the capital are the same word.

SELECT name,  capital
FROM world
where left(capital,1) = left(name,1) and name!= capital
1
2
3

三、查询内容中存在 ’

需要将’前面加一个/，就可以解决了，否则会出现很多’，导致查询出错

例题
Find all details of the prize won by EUGENE O’NEILL

select * from nobel where winner = 'EUGENE O\'NEILL'
1

四、排序中，先对一些数据排序，再对另外的数据排序

The expression subject IN (‘Chemistry’,‘Physics’) can be used as a value - it will be 0 or 1.在order by后面使用，即可实现先排序其他的，再排序chemistry，physics。

例题
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
1
2
3

查询结果如下：

五、concat函数

concat函数用于连接单元，用于将两个字符串连接起来，形成一个单一的字符串。试试下面的例子：

例题：Show the name and the population of each country in Europe. Show the population as a percentage of the population of Germany.

select name,concat(round(population/(select population from world where name = 'Germany')*100,0),'%')
  from world where continent = 'Europe'
1
2

六、各个州的最大面积的国家

例题：Find the largest country (by area) in each continent, show the continent, the name and the area:

SELECT continent, name, area FROM world x
  WHERE area >= ALL
    (SELECT area FROM world y
        WHERE y.continent=x.continent
          AND area>0)
1
2
3
4
5

七、group by和order by

这俩的区别在于，group by是分组查询，而order by是排序查询，记清这一点，就不会搞混了。

例题List the films released in the year 1978 ordered by the number of actors in the cast, then by title.

select f.name from (select movieid from casting c 
join actor a on a.id=c.actorid where name='Art Garfunkel') as e 
join (select movieid, name from casting c 
join actor a on a.id=c.actorid where name!='Art Garfunkel') as f 
on e.movieid=f.movieid
1
2
3
4
5

八、coalesce

用于填补空值，格式如下。

SELECT name, party
      ,COALESCE(party,'None') AS aff
  FROM msp WHERE name LIKE 'C%'
1
2
3

九、group by 1

这个子句的意思是，group by后面的1指代的是select后面的第一个。

十、case when

基本语句，一看就会：

CASE WHEN SCORE = 'A' THEN '优'
     WHEN SCORE = 'B' THEN '良'
     WHEN SCORE = 'C' THEN '中' ELSE '不及格' END
1
2
3

---

总结

之后刷到别的sql有意思的知识点也会在这里更新。