SQL学习之路（一）

首先一开始推荐一个SQL的学习网站，个人认为非常有用，里面也有练习题
英文网址：https://sqlbolt.com/lesson
中文网址：http://xuesql.cn/lesson/introduction

这是我学习sql的第一篇笔记,这篇笔记主要写的是（Query）查询

数据库如下：数据库名称为dog

id	name	age
1	dog1	1
2	dog2	11
3	dog3	5
4	dog4	5
5	dog5	8

一、查询语句，SELECT

1. 查询一个属性

select 列名 from 表名   
如： select id from dog

2. 查询多个属性用英文逗号隔开

select 列名1,列名2 from 表名  
如：select id,name from dog

3. 查询一个属性且设置一个值为数据库中没有的属性，可用as

select 列名，‘属性值’ as 新的属性名 from 表名
如：select id,'美国' as country from dog

二、加入判断条件，WHERE

下面的表中的例子就是判断条件的例子

Operator（关键字）	Condition（意思）	SQL Example(例子）
=, !=, < <=, >, >=	Standard numerical operators 基础的 大于，等于等比较	col_name != 4
BETWEEN … AND …	Number is within range of two values (inclusive) 在两个数之间	col_name BETWEEN 1.5 AND 10.5
NOT BETWEEN … AND …	Number is not within range of two values (inclusive) 不在两个数之间	col_name NOT BETWEEN 1 AND 10
IN (…)	Number exists in a list 在一个列表	col_name IN (2, 4, 6)
NOT IN (…)	Number does not exist in a list 不在一个列表	col_name NOT IN (1, 3, 5)
LIKE	Case insensitive exact string comparison 没有用通配符等价于 =	col_name LIKE “ABC”
NOT LIKE	Case insensitive exact string inequality comparison 没有用通配符等价于 !=	col_name NOT LIKE “ABCD”
%	Used anywhere in a string to match a sequence of zero or more characters (only with LIKE or NOT LIKE) 通配符，代表匹配0个以上的字符	col_name LIKE “%AT%”(matches “AT”, “ATTIC”, “CAT” or even “BATS”) “%AT%” 代表AT 前后可以有任意字符
_	Used anywhere in a string to match a single character (only with LIKE or NOT LIKE) 和% 相似，代表1个字符	col_name LIKE “AN_”(matches “AND”, but not “AN”)

SELECT 列名1, 列名2, …
FROM 表名
WHERE 条件1
    AND/OR 条件2
    AND/OR …;

如: select * from dog where id > 3 and age < 5
如: select * from dog where age in (1,2,6)
如：SELECT * FROM dog where name like "%dog%"  //匹配含有dog的
如：SELECT * FROM dog where name like 'dog'  //匹配等于dog的
如：SELECT * FROM dog where name like 'd%'  //匹配以d开头的
其他例子：
select * from movies where director = "John Lasseter" and (title like "Toy%" OR title like "car%") and year >= 2000
select * from north_american_cities where longitude < (select longitude from north_american_cities where city = 'Chicago')

三、查询结果处理

1. 排重，DISTINCT

你可以用 DISTINCT 关键字来指定某个或某些属性列唯一返回

选取出唯一的结果的语法
SELECT DISTINCT 列名1, 列名2, …
FROM 表名
WHERE 条件
如 select distinct age from dog where id > 1

2. 排序,ORDER BY

可以用 ORDER BY col_name 排序的语法来让结果按一个或多个属性列做排序

SELECT 列名1, 列名2, …
FROM 表名
WHERE 条件
ORDER BY column ASC/DESC; 

如：如果有多个排序要求，使用，隔开  order by 列名1 desc,列名2，asc,...
select *
from movies
order by director asc,year desc
limit 10 

ORDER BY col_name 这句话的意思就是让结果按照 col_name 列的具体值做 ASC升序 或 DESC 降序，对数字来说就是升序 1，2，3，… 或降序 … 3,2,1 . 对于文本列，升序和降序指的是按文本的字母序。

3. 获取部分结果，LIMIT / OFFSET

LIMIT 和 OFFSET 子句通常和ORDER BY 语句一起使用，当我们对整个结果集排序之后，我们可以 LIMIT来指定只返回多少行结果 ,用 OFFSET来指定从哪一行开始返回。你可以想象一下从一条长绳子剪下一小段的过程，我们通过 OFFSET 指定从哪里开始剪，用 LIMIT 指定剪下多少长度。

SELECT 列名1, 列名2, …
FROM 表名
WHERE 条件
ORDER BY 列名1 ASC/DESC
LIMIT num_limit OFFSET num_offset;
如：（下列例子来自刚开始推荐的学习网站中的习题,因为博主也是第一次使用LIMIT和OFFSET两个关键字，所以多举了几个例子）

select * 
from movies 
order by year desc
limit 4

select *
from movies
order by title asc
limit 5

select *
from movies
order by title asc
limit 5
offset 5

四、多表查询，JOINs

主键(primary key), 一般关系数据表中，都会有一个属性列设置为 主键(primary key)。主键是唯一标识一条数据的，不会重复复（想象你的身份证号码)。一个最常见的主键就是auto-incrementing integer(自增ID，每写入一行数据ID+1, 当然字符串，hash值等只要是每条数据是唯一的也可以设为主键.

借助主键(primary key)（当然其他唯一性的属性也可以），我们可以把两个表中具有相同主键ID的数据连接起来（因为一个ID可以简要的识别一条数据，所以连接之后还是表达的同一条数据）（你可以想象一个左右连线游戏）。具体我们用到JOIN 关键字。我们先来学习 INNER JOIN.

用INNER JOIN 连接表的语法:

SELECT column, another_table_column, …
FROM mytable （主表）
INNER/LEFT/RIGHT/FULL JOIN another_table （要连接的表）
    ON mytable.id = another_table.id (想象一下刚才讲的主键连接，两个相同的连成1条)
WHERE condition(s)
ORDER BY column, … ASC/DESC
LIMIT num_limit OFFSET num_offset;

通过ON条件描述的关联关系;INNER JOIN 先将两个表数据连接到一起. 两个表中如果通过ID互相找不到的数据将会舍弃。此时，你可以将连表后的数据看作两个表的合并，SQL中的其他语句会在这个合并基础上 继续执行（想一下和之前的单表操作就一样了）.

小提示：
INNER JOIN 可以简写做 JOIN. 两者是相同的意思，但我们还是会继续写作 INNER JOIN 以便和后面的 LEFT JOIN， RIGHT JOIN等相比较.

INNER JOIN、LEFT JOIN、RIGHT JOIN和FULL JOIN的区别

1. 把 INNER JOIN 想成两个集合的交集。
   
   2.左连接LEFT JOIN,右连接RIGHT JOIN 和 全连接FULL JOIN. 这几个 连接方式都会保留不能匹配的行。
   (1). LEFT JOIN
   
   (2). RIGHT JOIN
   
   (3) FULL JOIN
   

小提示：
这些Join也可以写作 LEFT OUTER JOIN, RIGHT OUTER JOIN, 或 FULL OUTER JOIN, 和 LEFT JOIN, RIGHT JOIN, and FULL JOIN 等价.

五、关键字 NULL

之前我们已经接触过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 列名1, 列名2, …
FROM 表名
WHERE 列名1 IS/IS NOT NULL
AND/OR 条件
AND/OR …;

六、在查询中使用表达式

之前我们在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）
            
SELECT id,title,year
FROM movies inner join boxoffice
on movies.id = boxoffice.movie_id
where year%2 = 0

SELECT title,(Domestic_sales+International_sales)/Length_minutes
FROM movies inner join boxoffice
on movies.id = boxoffice.movie_id
where director = 'John Lasseter'
order by (Domestic_sales+International_sales)/Length_minutes desc
limit 3

每一种数据库（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;

七、在查询中进行统计

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

对全部结果数据做统计
SELECT 统计的函数(列名或表达式) AS 别称, …
FROM 表名
WHERE 条件;

统计的函数有如下表所示

函数	描述
COUNT(*), COUNT(column)	计数！COUNT(*) 统计数据行数，COUNT(column) 统计column非NULL的行数.
MIN(column)	找column最小的一行.
MAX(column)	找column最大的一行.
AVG(column)	对column所有行取平均值.
SUM(column)	对column所有行求和.

在统计的时候我们也可以通过分组进行统计，分组的关键字是 GROUP BY

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

SELECT 统计的函数(列名或表达式) AS 别称, …
FROM 表名
WHERE 条件
GROUP BY 列名

SELECT building,sum(Years_employed)
FROM employees
group by building

判断条件要写在分组之前，不然判断条件无效

SELECT distinct building,count(building)
FROM employees
where building is not null
group by building

到目前为止，我们的Query查询已经有点复杂了，不过还好我们已经基本把查询语法介绍完了。在 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;

网站练习中的两个题目

1. 按角色分组算出每个角色按有办公室和没办公室的统计人数(列出角色，数量，有无办公室,注意一个角色如果部分有办公室，部分没有需分开统计
2. 按角色和就职年份统计人数,年份按0-3，3-6，6-9这种阶梯分组，最后按角色+阶梯分组排序

答案1：
SELECT role,count(*),building is not null
FROM employees
group by role,building is null
解析：
1. group by role,building is null 按照角色和building是否为空进行分组
2. building is not null  获取布尔值，判断building是否为空

答案2：
select role,years_employed,years_employed/3 as year_3,count(role)
from employees
group by role,year_3 
解析：
使用 years_employed/3 将years_employed按3的倍数进行分组，且别称为 year_3，然后用role和它进行分组

八、最后，来总结一下这些跟查询语句有关关键字的执行顺序

用一条sql语句来表示他们的执行顺序
SELECT DISTINCT 列名（属性）, AGG_FUNC(column_or_expression), …
FROM 表名1
    JOIN 表名2
      ON 表名1.属性= 表名2.属性
    WHERE 判断条件
    GROUP BY 列名（属性）
    HAVING 判断条件
    ORDER BY 列名（属性） 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层面更好的解决数据问题，而不用把问题 都抛给程序逻辑.