sql null值替换0_SQL学习笔记 | SQL语言分类&杂记

SELECT基础

SELECT [列名] FROM [表名]；

SELECT [列名] FROM [表名]

WHERE <条件表达式>；

“IN”可以检查一个项目是否在列表中

 WHERE name IN ('Ireland', 'Iceland', 'Denmark');

“BETWEEN”检查范围

WHERE area BETWEEN 200000 AND 250000

条件运算优先级：NOT>AND>OR

模糊查询：LIKE

通配符：

‘_’——知道未知字符的数量和位置

‘%' ——只知道位置，不知道数量

WHERE name LIKE 'Y%'
WHERE name LIKE '%o__o%'

CONCAT函數concat 可以用來合拼兩個或以上的字串。

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

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

"Monaco-Ville"是合併國家名字 "Monaco" 和延伸詞"-Ville".

顯示國家名字，及其延伸詞，如首都是國家名字的延伸。

select name,replace(capital,name,'') ext 
from world 
where capital like concat(name,'%_')

replace('替换目标字符'=索引列名-此时不加单引号，'替换的字符'=索引列名-此时不加单引号，'替换为什么'=索引列名-此时不加单引号）

ROUND函数

ROUND(’数值‘，留几位小数（可正可负））

CASE语句

当筛选条件一致时可以使用 IN 函数

SELECT name, 
CASE WHEN continent IN ('Europe','Asia') THEN 'Eurasia' 
WHEN continent IN ('North America','South America','Caribbean') THEN 'America' 
ELSE continent END 
FROM world 
WHERE name LIKE 'A%' OR name LIKE 'B%'

跳脫字符:單引號
你不能把一個單引號直接的放在字符串中。但您可連續使用兩個單引號在字符串中當作一個單引號
可以将表达式 主题IN（“化学”，“物理”）用作值-它可以是 0或 1。

選擇代碼以顯示哪一年有頒發醫學獎，但沒有頒發和平或文學獎。

選擇代碼以顯示哪一年沒有頒發物理獎，亦沒有頒發化學獎

DISTINCT---分类显示

子查询

SELECT查询的结果可以作为一个值用在另一个值中。例如

SELECT name FROM world WHERE continent = 
(SELECT continent 
FROM world WHERE name = 'Brazil')

在某些SQL查询系统中，子查询有时必须使用别名当做新的表格名，只需要加上AS别名在括号之后。

SELECT name FROM world WHERE continent = 
  (SELECT continent FROM world WHERE name='Brazil') AS brazil_continent

运算 可以使用ALL或ANY，当运算子的右面有多于一个值的时候。

=     等於
>     大於
<     小於
>=    大於或等於
<=    小於或等於

SELECT name
  FROM world
 WHERE population >= ALL(SELECT population
                           FROM world
                          WHERE population>0)

本题关键点在颁发了物理奖但没颁发化学奖的年份。某一年可能会颁发多个物理奖，所以需要年份去重。可以先查到颁发物理奖的年份，然后再查找这些年份里没有颁发化学奖的年份。后者需要用到子查询。

我們可以在子查詢，參閱外部查詢的數值。我們為表格再命名，便可以分別內外兩個不同的表格。
在每一個州中找出最大面積的國家，列出洲份 continent, 國家名字 name 及面積 area。 

SELECT continent, name, area FROM world x
WHERE area >= ALL
(SELECT area FROM world y
WHERE y.continent=x.continent
AND area>0)

GROUP BY HAVING 函数的使用

GROUP BY 分组

可同时对多列分组

哪幾年的得獎者人數多於12人呢? 列出得獎人數多於12人的年份，獎項和得獎者。
select * from nobel
 where yr in
  (select yr from nobel
   group by yr having count(winner) > 12)
------GROUP BY按年份分组 ————HAVING筛选组

哪些得獎者獲獎多於1次呢？他們是哪一年獲得哪項獎項呢？ 列出他們的名字，獲獎年份及獎項。先按名字，再按年份順序排序。
SELECT winner, yr ,subject
FROM nobel
WHERE winner IN 
(SELECT winner
FROM nobel
GROUP BY winner HAVING count(winner)>1) ORDER BY winner,yr
——解题思路：关键点在于获奖者次数大于1次，将获奖者分组后，筛选出大于一的获奖者小组
ORDER BY 排序，关键词前后侧重排序

SQL聚合函数

SUM,COUNT,MAX,DISTINCT（分类，类别）,ORDER BY.

对一每一个奖项列出首次颁发的年份
SELECT subject,MIN(yr)
FROM nobel
GROUP BY subject

DISTINCT群组函数

NULL值查询

dept IS NULL
Leader IS NOT NULL

INNER JOIN 内连接 只能返回两张表同时存在的行数据

OUTER JOIN 外连接 即使只有一张表有值，也可以返回

SELECT teacher.name,dept.name
FROM teacher INNER JOIN dept ON (teacher.dept=dept.id)

JOIN ON

RIGHT JOIN 返回右表中都存在值的行。LEFT JOIN返回左表中都存在值的行。

select teacher.name,dept.name
from teacher left join dept on(teacher.dept=dept.id)

SELECT teacher.name, IFNULL(COALESCE(dept.name),'None') 
FROM teacher LEFT JOIN dept on(teacher.dept=dept.id)

COALESCE接受任意数量的参数，并返回不为null的第一个值。

IFNULL(NULL,A)输出为A，IFNULL(A,B)输出为A

SELECT name, IFNULL(COALESCE(mobile),'07986 444 2266')
FROM teacher

CASE允许在不同条件下返回不同的值。如果没有条件匹配（并且没有ELSE），则返回NULL。

一个CASE语句只需一个CASE字段，栗子

SELECT name,
CASE WHEN dept=1 THEN 'Sci'
WHEN dept=2 THEN 'Sci'
ELSE 'Art'
END
FROM teacher

SELECT name,
CASE WHEN dept IN(1,2) THEN 'Sci'
WHEN dept=3 THEN 'Art'
ELSE 'None'
END
FROM teacher

处理NULL值

SELECT DISTINCT(party.name) FROM msp LEFT JOIN party ON (msp.party=party.code)
WHERE msp.party IN (SELECT msp.party
FROM msp
GROUP BY msp.Party HAVING COUNT(msp.Party)>0)