一、SQL入门

| 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 |

1.【初体验】这是第一题,请你先将左侧的输入框里的内容清空,然后请输入下面的SQL,您将看到所有电影标

SELECT * FROM movies;

2.【初体验】请输入如下SQL你将看到4条电影(切记先清空数据框且出错要耐心比对):
SELECT title,director FROM movies WHERE Id < 5

3.【初体验】输入如下SQL你将看到电影总条数:

SELECT count(*) FROM movies 

4.【初体验】SQL可以直接做计算,下面的SQL计算1+1的和,请输入:

SELECT 1+1 

二、SELECT 查询

SELECT 语句, 通常又称为 查询queries ), 正如其名, SELECT 可以用来从数据库中取出数据. 一条 SELECT 语句或者叫一个查询, 可以描述我们要从什么表取数据, 要取哪些数据,在返回之前怎么对结果做一些转化计算等等. 我们接下来会说明 SELECT 的语法,看 SELECT 是怎么来实现上述的取数据任务的。

你可以把一个表(Table)想象成一个类别的事物,比如 狗 (Dogs), 表里的每一行就是 一条狗,每一列代表了狗的一种属性,比如: 颜色,长度等等)

现在有了这么一张表,最常见的一种查询就是取出表中的 一个或某几个属性列(注意:是所有数据的某几个属性列)

Select 查询某些属性列(specific columns)的语法

<span class="hljs-operator"><span class="hljs-keyword">SELECT</span> column(列名), another_column, … <span class="hljs-keyword">FROM</span> mytable(表名);</span>

查询的结果是一个二维的表格,由行(rows)和列(columns)组成, 看起来像是复制了一遍原有的表(Table),只不过列是我们选定的,而不是所有的列.

如果我们想取出所有列的数据, 当然可以把所有列名写上,不过更简单的方式用星号 (*) 来代表所有列.如下:

Select 查询所有列

<span class="hljs-operator"><span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> mytable(表名);</span>

| 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 |

1.【简单查询】找到所有电影的名称title

SELECT title FROM movies;

2.【简单查询】找到所有电影的导演

SELECT Director FROM movies;

3.【简单查询】找到所有电影的名称和导演

SELECT Director,title FROM movies;

4.【简单查询】找到所有电影的名称和上映年份

SELECT year,title FROM movies;

5.【简单查询】找到所有电影的所有信息

SELECT * FROM movies;

6.【简单查询】找到所有电影的名称,Id和播放时长

SELECT title,Length_minutes,id FROM movies;

三、条件查询

我们很少直接查所有行,即使查询出来也看不完。为了更精确的查询出特定数据,我们需要学习一个新的SQL语法:SELECT查询的 WHERE 子句. 一个查询的 WHERE子句用来描述哪些行应该进入结果,具体就是通过 condition条件 限定这些行的属性满足某些具体条件。

比如:WHERE 体重大于 10KG的狗。你可以把 WHERE想象成一个 筛子,每一个特定的筛子都可以筛下某些豆子。

条件查询语法
SELECT column, another_column, …
FROM mytable
WHERE condition
    AND/OR another_condition
    AND/OR …;

注:这里的 condition 都是描述属性列的,具体会在下面的表格体现。

可以用 AND or OR 这两个关键字来组装多个条件(表示并且,或者) (ie. num_wheels >= 4 AND doors <= 2 这个组合表示 num_wheels属性 大于等于 4 并且 doors 属性小于等于 2). 下面的具体语法规则,可以用来筛选数字属性列(包括 整数,浮点数) :

Operator(关键字)Condition(意思)SQL Example(例子)
=, !=, < <=, >, >=Standard numerical operators 基础的 大于,等于等比较col_name**!=** 4
BETWEEN … AND …Number is within range of two values (inclusive) 在两个数之间col_nameBETWEEN 1.5 AND 10.5
NOT BETWEEN … AND …Number is not within range of two values (inclusive) 不在两个数之间col_nameNOT BETWEEN 1 AND 10
IN (…)Number exists in a list 在一个列表col_nameIN (2, 4, 6)
NOT IN (…)Number does not exist in a list 不在一个列表col_nameNOT IN (1, 3, 5)

越是精确的条件筛选,会让结果更容易理解,同时因为条件在返回之前筛掉不必要的结果,SQL的运行速度也会快很多(想象一下你只是想看下最近有哪些大片上映,你没必要下载整个电影看一遍).

| 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 |

1.【简单条件】找到 id为6的电影

SELECT * FROM movies where id=6

2.【简单条件】找到在2000-2010年间 year上映的电影

SELECT * FROM movies where year>=2000 and year <=2010
SELECT * FROM movies where year between 2000 and 2010

3.【简单条件】找到不是在2000-2010年间 year上映的电影

SELECT * FROM movies where year not between 2000 and 2010

4.【简单条件】找到头5部电影

SELECT * FROM movies where id<6

5.【简单条件】找到2010(含)年之后的电影里片长小于两个小时的片子

SELECT * FROM movies where year>=2010 and Length_minutes<120

四、条件查询 (Pt. 2)

我们已经学会了 WHERE 语句来筛选数字类型的属性,如果属性是字符串, 我们会用到字符串相关的一些操作符号,其中 LIKE(模糊查询) 和 %(通配符) 是新增的两个. 下面这个表格对字符串操作符有详细的描述:

Operator(操作符)Condition(解释)Example(例子)
=Case sensitive exact string comparison (notice the single equals )完全等于col_name**=** "abc"
!= or <>Case sensitive exact string inequality comparison 不等于col_name**!=** "abcd"
LIKECase insensitive exact string comparison 没有用通配符等价于 =col_nameLIKE "ABC"
NOT LIKECase insensitive exact string inequality comparison 没有用通配符等价于 !=col_nameNOT LIKE "ABCD"
%Used anywhere in a string to match a sequence of zero or more characters (only with LIKE or NOT LIKE) 通配符,代表匹配0个以上的字符col_nameLIKE "%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_nameLIKE "AN_"``(matches "AND", but not "AN")
IN (…)String exists in a list 在列表col_nameIN ("A", "B", "C")
NOT IN (…)String does not exist in a list 不在列表col_nameNOT IN ("D", "E", "F")

在字符串表达式中的字符串需要用引号 " 包含,如果不用引号,SQL会认为是一个属性列的名字,如:col_name = color 表示 col_name和color两个属性一样的行 col_name = "color" 表示 col_name 属性为字符串 "color"的行.

| 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 |

1.【复杂条件】找到所有 Toy Story系列电影

SELECT * FROM movies where title like "Toy Story%"

2.【复杂条件】找到所有John Lasseter导演的电影

SELECT * FROM movies where Director like "John Lasseter%"

3.【复杂条件】找到所有不是 John Lasseter导演的电影

SELECT * FROM movies where Director != "John Lasseter"

4.【复杂条件】找到所有电影名为 "WALL-" 开头的电影

SELECT * FROM movies where Title like "WALL-%"

5.【复杂条件】有一部98年电影中文名《虫虫危机》请给我找出来

SELECT * FROM movies where Title = "A Bug's Life" and year =1998

五、查询结果Filtering过滤 和 sorting排序

DISTINCT 语法介绍,我们拿之前的 Movies表来说,可能很多电影都是同一年Year发布的,如果你想要按年份排重,一年只能出现一部电影到结果中, 你可以用 DISTINCT 关键字来指定某个或某些属性列唯一返回。写作:DISTINCT Year

选取出唯一的结果的语法
SELECT DISTINCT column, another_column, …
FROM mytable
WHERE condition(s);

因为 DISTINCT 语法会直接删除重复的行, 我们还会学习 GROUP BY 语句, GROUP BY 也会返回唯一的行,不过可以对具有相同的 属性值的行做一些统计计算,比如:求和.

结果排序 (Ordering results)

在实际的数据表中,数据添加的时候不是完全顺序的,比如我们实际的Dog表,不会是按狗的身高从小到大去添加数据,当数据量成千上万之后,如果结果不做任何排序,结果会看起来很错乱.

为了解决结果排序问题, 我们可以用 ORDER BY col_name<span> </span>排序的语法来让结果按一个或多个属性列做排序.

结果排序(ordered results)
SELECT column, another_column, …
FROM mytable
WHERE condition(s)
ORDER BY column ASC/DESC;

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

通过Limit选取部分结果

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

limited查询
SELECT column, another_column, …
FROM mytable
WHERE condition(s)
ORDER BY column ASC/DESC
LIMIT num_limit OFFSET num_offset;

你可以想象一下一个新闻网站的新闻条目数据,他们在页面上是按热度和时间排序的,每一个页面只显示10条数据,在所有这些属性都是不断变化的情况下。我们可以想见通过SQL的 ORDER LIMIT OFFSET 句法,我们可以根据要求从数据库筛选出需要的新闻条目.

本节介绍了 DISTINCT, ORDER, LIMIT这几个简单的语法,它们都是对结果集做一些简单的处理。我们可以用这些知识来处理 Movies 表的一些实际问题了, 详细请看下面的练习Tasks.

| 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 |

1.【结果排序】按导演名 排重列出所有电影(只显示导演),并按导演名正序排列

SELECT distinct Director FROM movies order by Director asc

2.【结果排序】列出按上映年份最新上线的4部电影

SELECT * FROM movies order by year desc limit 4

3.【结果排序】按电影名字母序升序排列,列出前5部电影

SELECT * FROM movies order by Title asc limit 5

4.【结果排序】按电影名字母序升序排列,列出上一题 之后的5部电影

SELECT * FROM movies order by Title asc limit 5 offset 5

5.【结果排序】如果按片长排列,John Lasseter导演导过片长第3长的电影是哪部,列出名字即可

SELECT title FROM movies where Director="John Lasseter" order by Length_minutes desc limit 1 offset 2