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

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

Operator（操作符） Condition（解释） Example（例子） = Case sensitive exact string comparison (

)完全等于 col_name = “abc” != or <> Case sensitive exact string inequality comparison 不等于 col_name != “abcd” 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”) IN (…) String exists in a list 在列表 col_name IN (“A”, “B”, “C”) NOT IN (…) String does not exist in a list 不在列表 col_name NOT IN (“D”, “E”, “F”) 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

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

选取出唯一的结果的语法:

SELECT DISTINCT column, another_column, ...

FROM mytable
WHERE condition(s);

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

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

结果排序（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 和 OFFSET 子句通常和 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 句法，我们可以根据要求从数据库筛选出需要的新闻条目.

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

Table(表）: North_american_cities

City Country Population Latitude Longitude Guadalajara Mexico 1500800 20.659699 -103.349609 Toronto Canada 2795060 43.653226 -79.383184 Houston United States 2195914 29.760427 -95.369803

在这个数据表中，你需要熟悉一下latitudes（纬度）和 longitudes（经度）的概念， latitudes在赤道以北是正数，以南是负数；longitudes在子午线东部是正数，以西是负数， 在查询中需要注意 经纬度和东西南北方向的对应关系。

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

用INNER JOIN 连接表的语法

SELECT column, another_table_column, ...

FROM mytable （主表）
INNER JOIN another_table （要连接的表）
    ON mytable.id = another_table.id
WHERE condition(s)
ORDER BY column, ... ASC/DESC
LIMIT num_limit OFFSET num_offset;

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

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

Table: Boxoffice

Movie_id Rating Domestic_sales International_sales 5 8.2 380843261 555900000 14 7.4 268492764 475066843 8 8 206445654 417277164 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

INNER JOIN 只会保留两个表都存在的数据（还记得之前的交集吗），这看起来意味着一些数据的丢失，在某些场景下会有问题.

真实世界中两个表存在差异很正常，所以我们需要更多的连表方式，也就是本节要介绍的左连接 LEFT JOIN,右连接 RIGHT JOIN 和 全连接 FULL JOIN. 这几个 连接方式都会保留不能匹配的行。

用LEFT/RIGHT/FULL JOINs 做多表查询

SELECT column, another_column, ...

FROM mytable
INNER/LEFT/RIGHT/FULL JOIN another_table
ON mytable.id = another_table.matching_id
WHERE condition(s)
ORDER BY column, ... ASC/DESC
LIMIT num_limit OFFSET num_offset;

和 INNER JOIN 语法几乎是一样的. 我们看看这三个连接方法的工作原理：
在表A 连接 B, LEFT JOIN保留A的所有行，不管有没有能匹配上B 反过来 RIGHT JOIN则保留所有B里的行。最后 FULL JOIN 不管有没有匹配上，同时保留A和B里的所有行

Table: Employees

Role Name Building Years_employed Engineer Becky A. 1e 4 Engineer Dan B. 1e 2 Engineer Sharon F. 1e 6

Table: Buildings

Building_name Capacity 1e 24 1w 32 2e 16

在使用left jion时，on和where条件的区别如下：

Table: Employees

Role Name Building Years_employed Engineer Becky A. 1e 4 Engineer Dan B. 1e 2 Engineer Sharon F. 1e 6

Table: Buildings

Building_name Capacity 1e 24 1w 32 2e 16

Table: Movies (Read-Only)

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

Table: Boxoffice (Read-Only)

Movie_id Rating Domestic_sales International_sales 5 8.2 380843261 555900000 14 7.4 268492764 475066843 8 8 206445654 417277164

对全部结果数据做统计

SELECT
AGG_FUNC(column_or_expression) AS aggregate_description, ... FROM mytable
WHERE constraint_expression;

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

以下是一些常见的统计函数：

Function Description

column*

计数！COUNT(*) 统计数据行数，COUNT(column) 统计column非NULL的行数.

找column最小的一行.

找column最大的一行.

) 对column所有行取平均值.

对column所有行求和.

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;

Table(表）: Employees 全表查看

Role Name Building Years_employed Engineer Becky A. 1e 4 Engineer Dan B. 1e 2 Engineer Sharon F. 1e 6

在 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没有什么用，但当你的数据量成千上万属性又很多时也许能帮上大忙 .

Table(表）: Employees 全表查看

Role Name Building Years_employed Engineer Becky A. 1e 4 Engineer Dan B. 1e 2 Engineer Sharon F. 1e 6

在介绍了所有与查询相关的语法之后，让我们将前面的所有语法放在一句话中。

这才是完整的SELECT查询

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;

一个查询SQL的执行总是先从数据里按条件选出数据，然后对这些数据再次做一些整理处理，按要求返回成结果，让结果尽可能是简单直接的。因为一个 查询SQL由很多部分组成，所以搞清楚这些部分的执行顺序还挺重要的，这有助于我们更深刻的理解SQL执行过程.

Table: Movies (Read-Only)

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

Table: Boxoffice (Read-Only)

Movie_id Rating Domestic_sales International_sales 5 8.2 380843261 555900000 14 7.4 268492764 475066843 8 8 206445654 417277164

Original: https://www.cnblogs.com/li-shui/p/16326714.html
Author: 理水
Title: 自学SQL网题目解答与笔记