【SQL学习+练习篇】基础训练，适用于初学者（二）

上一篇：【SQL学习+练习篇】基础训练，适用于初学者（一）_小慌慌的博客-CSDN博客

今天继续学习，加油！坚持一定很酷哦～

学习7：关于特殊关键字 NULLs

之前我们已经接触过NULL. 在数据库中，NULL表达的是 "无"的概念，或者说没有东西。因为 NULL的存在，我们需要在编写SQL时考虑到某个属性列可能是 NULL的情况， 这种特殊性会造成编写SQL的复杂性，所以没有必要的情况下，我们应该尽量减少 NULL的使用，让数据中尽可能少出现 NULL的情况。

如果某个字段你没有填写到数据库，很可能就会出现NULL 。所有一个常见的方式就是为字段设置默认值,比如 数字的默认值设置为0，字符串设置为 ""字符串. 但是在一些NULL 表示它本来含义的场景，需要注意是否设置默认值还是保持NULL。 (f比如, 当你计算一些行的平均值的时候，如果是0会参与计算导致平均值差错，是NULL则不会参与计算).

还有一些情况很难避免 NULL 的出现, 比如之前说的 outer-joining 多表连接，A和B有数据差异时，必须用 NULL 来填充。这种情况，可以用IS NULL和 IS NOT NULL 来选在某个字段是否等于 NULL.

在查询条件中处理 NULL 

SELECT column, another_column, …
FROM mytable
WHERE column IS/IS NOT NULL
AND/OR another_condition
AND/OR …

练习

还是会用到 Employees 和 Buildings 两个表，这一次我们雇佣了一些雇员但还没有分配办公室. 你需要综合之前的知识来解决这些问题！

练习7：关于特殊关键字 NULLs

• 1.找到雇员里还没有分配办公室的(列出名字和角色就可以)

SELECT name,role
FROM employees
where building is null

• 2.找到还没有雇员的办公室

SELECT distinct a.building_name
FROM buildings a
left join employees b
on a.Building_name=b.Building
where b.name is null

学习8：在查询中使用表达式

之前在SQL中的出现col_name（属性名）的 地方，都只是写上col_name自身。其实在SQL中可以用col_name的地方，都可以用表达式 来指定对属性进行一定的计算或处理。举个例子：假设有一个col_name是出生日期，现在要求SQL返回当前的年龄，这就可以用一个时间计算表达式对出生日期做计算得到年龄。表达式可以对 数字运算，对字符串运算，也可以在表达式中只包含常量不包含col_name(如：SELECT 1+1）

包含表达式的例子
SELECT particle_speed / 2.0 AS half_particle_speed (对结果做了一个除2）
FROM physics_data
WHERE ABS(particle_position) * 10.0 >500
（条件要求这个属性绝对值乘以10大于500）

每一种数据库（mysql，sqlserver等）都有自己的一套函数，包含常用的数字，字符串，时间等处理过程.具体需要参看相关文档。
当我们用表达式对col属性计算时，很多事可以在SQL内完成，这让SQL更加灵活，但表达式如果长了则很难一下子读懂。所以SQL提供了AS关键字， 来给表达式取一个别名.

AS使用别名
SELECT col_expression AS expr_description, …
FROM mytable

实际上AS不仅用在表达式别名上，普通的属性列甚至是表（table）都可以取一个别名，这让SQL更容易理解.

属性列和表取别名的例子
SELECT column AS better_column_name, …
FROM a_long_widgets_table_name AS mywidgets
INNER JOIN widget_sales
ON mywidgets.id = widget_sales.widget_id

练习8：在查询中使用表达式

Table: Movies 

Id	Title	Director	Year	Length_minutes
1	Toy Story	John Lasseter	1995	81
2	A Bug's Life	John Lasseter	1998	95
3	Toy Story 2	John Lasseter	1999	93
4	Monsters, Inc.	Pete Docter	2001	92
5	Finding Nemo	Finding Nemo	2003	107
6	The Incredibles	Brad Bird	2004	116
7	Cars	John Lasseter	2006	117
8	Ratatouille	Brad Bird	2007	115
9	WALL-E	Andrew Stanton	2008	104
10	Up	Pete Docter	2009	101
11	Toy Story 3	Lee Unkrich	2010	103
12	Cars 2	John Lasseter	2011	120
13	Brave	Brenda Chapman	2012	102
14	Monsters University	Dan Scanlon	2013	110

Table: Boxoffice 

Movie_id	Rating	Domestic_sales	International_sales
5	8.2	380843261	555900000
14	7.4	268492764	475066843
8	8	206445654	417277164
12	6.4	191452396	368400000
3	7.9	245852179	239163000
6	8	261441092	370001000
9	8.5	223808164	297503696
11	8.4	415004880	648167031
1	8.3	191796233	170162503
7	7.2	244082982	217900167
10	8.3	293004164	438338580
4	8.1	289916256	272900000
2	7.2	162798565	200600000
13	7.2	237283207	301700000

• 1.列出所有的电影ID,名字和销售总额(以百万美元为单位计算)

SELECT a.id,a.title,(Domestic_sales+International_sales)/1000000 as sales_sum
FROM movies a
join boxoffice b
on a.id=b.Movie_id

• 2.列出所有的电影ID,名字和市场指数(Rating的10倍为市场指数)

SELECT a.id,a.Title,b.Rating*10 as Rating   
FROM movies a
join Boxoffice b
on a.id=b.Movie_id;

• 3.列出所有偶数年份的电影，需要电影ID,名字和年份

SELECT id,title,year
FROM movies 
where year %2=0

year %2=0表示年份除以2余数为0，即：偶数

• 4.John Lasseter导演的每部电影每分钟值多少钱,告诉我最高的3个电影名和价值就可以

SELECT a.Title,(b.Domestic_sales+b.International_sales)/a.Length_minutes as movies_value
FROM movies a
join Boxoffice b
on a.id=b.Movie_id
where a.Director='John Lasseter'
order by movies_value desc
limit 3

• 5.电影名最长的3部电影和他们的总销量是多少

SELECT a.Title,(b.Domestic_sales+b.International_sales) as sales_sum
FROM movies a
join Boxoffice b
on a.id=b.Movie_id
order by length(title) desc
limit 3;

学习9：在查询中进行统计I (Pt. 1)

SQL默认支持一组统计表达式，他们可以完成数据统计，如：计数，求平均等。 以Movies表数据为例，这些统计表达式可以帮我们回答以下问题："Pixar公司生产了多少电影？", 或 "每一年的票房冠军是谁?".

对全部结果数据做统计
SELECT AGG_FUNC(column_or_expression) AS aggregate_description, …
FROM mytable
WHERE constraint_expression

如果不指明如何分组，那统计函数将对查询结果全部数据进行统计，当然每一个统计也可以像之前用AS来取一个别名，以增加可读性.

常见统计函数

GROUP BY 数据分组语法可以按某个col_name对数据进行分组，如：GROUP BY Year指对数据按年份分组， 相同年份的分到一个组里。如果把统计函数和GROUP BY结合，那统计结果就是对分组内的数据统计了.
GROUP BY 分组结果的数据条数，就是分组数量，比如：GROUP BY Year，全部数据里有几年，就返回几条数据， 不管是否应用了统计函数.

用分组的方式统计
SELECT AGG_FUNC(column_or_expression) AS aggregate_description, …
FROM mytable
WHERE constraint_expression
GROUP BY column

练习9：在查询中进行统计I (Pt. 1)

Table(表）: Employees

Role	Name	Building	Years_employed
Engineer	Becky A.	1e	4
Engineer	Dan B.	1e	2
Engineer	Sharon F.	1e	6
Engineer	Dan M.	1e	4
Engineer	Malcom S.	1e	1
Artist	Tylar S.	2w	2
Artist	Sherman D.	2w	8
Artist	Jakob J.	2w	6
Artist	Lillia A.	2w	7
Artist	Brandon J.	2w	7
Manager	Scott K.	1e	9
Manager	Shirlee M.	1e	3
Manager	Daria O.	2w	6
Engineer	Yancy I.	null	0
Artist	Oliver P.	null	0

• 1.【统计】找出就职年份最高的雇员(列出雇员名字+年份）

SELECT Name,max(Years_employed) 
FROM employees

• 2.【分组】按角色(Role)统计一下每个角色的平均就职年份

SELECT Role,avg(Years_employed) 
FROM employees
GROUP BY Role

• 3.【分组】按办公室名字总计一下就职年份总和

SELECT Building,sum(Years_employed) 
FROM employees
GROUP BY Building

• 4.【较难】每栋办公室按人数排名,不要统计无办公室的雇员

SELECT Building,count(name)as sum
FROM employees
WHere Building is not null
GROUP BY Building
order by sum desc

• 5.【难】就职1,3,5,7年的人分别占总人数的百分比率是多少(给出年份和比率"50%" 记为 50)

SELECT
	Years_employed,
	name_sum * 100 / ( SELECT count( 1 ) FROM employees ) AS rating 
FROM
	( SELECT Years_employed, count( NAME ) AS name_sum 
     FROM employees
     WHERE Years_employed IN ( 1, 3, 5, 7 ) 
     GROUP BY Years_employed )

学习10：在查询中进行统计II (Pt. 2)

在 GROUP BY分组语法中，我们知道数据库是先对数据做WHERE，然后对结果做分组，如果我们要对分组完的数据再筛选出几条如何办？ （想一下按年份统计电影票房，要筛选出>100万的年份？） 

一个不常用的语法 HAVING 语法将用来解决这个问题，他可以对分组之后的数据再做SELECT筛选.

用HAVING进行筛选

SELECT group_by_column, AGG_FUNC(column_expression) AS aggregate_result_alias, …
FROM mytable
WHERE condition
GROUP BY column
HAVING group_condition

HAVING 和 WHERE 语法一样，只不过作用的结果集不一样. 在我们例子数据表数据量小的情况下可能感觉HAVING没有什么用，但当你的数据量成千上万属性又很多时也许能帮上大忙 

小贴士?

如果你不用`GROUP BY`语法, 简单的`WHERE`就够用了.

练习10：在查询中进行统计II (Pt. 2)

还是Employee表

• 1.统计一下Artist角色的雇员数量

SELECT count(Name)
FROM employees
where role ='Artist'
group by role

• 2.按角色统计一下每个角色的雇员数量

SELECT role,count(Name)
FROM employees
group by role

• 3.算出Engineer角色的就职年份总计

SELECT sum(Years_employed)
FROM employees
where role = 'Engineer'
--group by role

• 4.【难】按角色分组算出每个角色按有办公室和没办公室的统计人数(列出角色，数量，有无办公室,注意一个角色如果部分有办公室，部分没有需分开统计）

SELECT
	role,
	count( * ),
CASE	
	WHEN building IS NULL THEN
	'无' ELSE '有' 
	END AS 有无办公室 
FROM
	employees 
GROUP BY
	role,building IS NULL

• 5.【难】按角色和就职年份统计人数,年份按0-3，3-6，6-9这种阶梯分组，最后按角色+阶梯分组排序

SELECT
	Role,
CASE
	
	WHEN Years_employed < 3 THEN
	'0-3'
	WHEN Years_employed < 6 THEN
	'3-6' ELSE '6-9'
	END AS 就职年份,
	count( ) 
FROM
	employees 
WHERE
	1 
GROUP BY
Role,就职年份

学习11：查询执行顺序

把之前的所有语法集中到一个句子中.

SELECT DISTINCT column, AGG_FUNC(column_or_expression), …
FROM mytable
JOIN another_table
ON mytable.column = another_table.column
WHERE constraint_expression
GROUP BY column
HAVING constraint_expression
ORDER BY column ASC/DESC
LIMIT count OFFSET COUNT

查询执行顺序

• 1.FROM 和 JOINs

FROM 或 JOIN会第一个执行，确定一个整体的数据范围. 如果要JOIN不同表，可能会生成一个临时Table来用于 下面的过程。总之第一步可以简单理解为确定一个数据源表（含临时表)

• 2.WHERE

确定了数据来源 WHERE 语句就将在这个数据源中按要求进行数据筛选，并丢弃不符合要求的数据行，所有的筛选col属性 只能来自FROM圈定的表. AS别名还不能在这个阶段使用，因为可能别名是一个还没执行的表达式.

• 3.GROUP BY

如果用了 GROUP BY 分组，那GROUP BY 将对之前的数据进行分组，统计等，并将是结果集缩小为分组数.这意味着 其他的数据在分组后丢弃.

• 4.HAVING

如果用了 GROUP BY 分组, HAVING 会在分组完成后对结果集再次筛选。AS别名也不能在这个阶段使用.

• 5.SELECT

确定结果之后，SELECT用来对结果col简单筛选或计算，决定输出什么数据.

• 6.DISTINCT

如果数据行有重复DISTINCT 将负责排重.

• 7.ORDER BY

在结果集确定的情况下，ORDER BY 对结果做排序。因为SELECT中的表达式已经执行完了。此时可以用AS别名.

• 8.LIMIT / OFFSET

最后 LIMIT 和 OFFSET 从排序的结果中截取部分数据.

结论

不是每一个SQL语句都要用到所有的句法，但灵活运用以上的句法组合和深刻理解SQL执行原理将能在SQL层面更好的解决数据问题，而不用把问题 都抛给程序逻辑.

练习11：查询执行顺序

Table: Movies Table: Boxoffice 

• 1.统计出每一个导演的电影数量（列出导演名字和数量）

SELECT director as "导演", count(id) as "电影数量"
FROM movies 
group by director

• 2.统计一下每个导演的销售总额(列出导演名字和销售总额)

SELECT a.director as "导演", SUM(b.Domestic_sales+International_sales) as "销售总额"
FROM movies a
join boxoffice b
on a.id=b.movie_id
group by a.director
order by "销售总额" desc

• 3.按导演分组计算销售总额,求出平均销售额冠军（统计结果过滤掉只有单部电影的导演，列出导演名，总销量，电影数量，平均销量)

SELECT DIRECTOR,COUNT(ID) AS "电影数量",
SUM(b.Domestic_sales+b.International_sales) as "销售总额",
(SUM(b.Domestic_sales+b.International_sales)/COUNT(ID)) as "平均销量"
FROM movies a
JOIN boxoffice b
ON a.id=b.movie_id
GROUP BY a.director
HAVING count(id)> 1
ORDER BY "平均销量" desc
LIMIT 1

• 4.找出每部电影和单部电影销售冠军之间的销售差，列出电影名，销售额差额

SELECT title as "电影" ,
(SELECT max(international_sales+domestic_sales) FROM boxoffice)
-(international_sales+domestic_sales) as "差额"
FROM movies 
LEFT JOIN boxoffice 
ON movies.id=boxoffice.movie_id

学习练习网站参考：

自学SQL网(教程 视频 练习全套)

2021-12-10学习回顾篇，随手记录！