上一篇:【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