本篇内容主要为SQL语言的基本操作。备忘录性质。

关于SQL

SQL, 全称为Structured Query Language（结构化查询语言)。

要讲SQL就绕不开database(数据库), 平时所说的数据库，一般指的关系型数据库。

什么是关系型数据库（Relational databases)？

数据库由若干张表(Table)组成，Table也是由 行(rows)和列(columns)组成

列(columns)是有限固定的，行(rows)是无限不固定的。

SELET-查询

Select 查询某些属性列（specific columns）的语法

1
2
SELECT column（列名）, another_column, …
FROM mytable（表名）;

Select 查询所有列

1
2
SELECT *
FROM mytable（表名）;

条件查询

WHERE condition ;

语法：

1
2
3
4
5
6
SELECT column, another_column, …
FROM mytable
WHERE condition
    AND/OR another_condition
    AND/OR …;
 //注：这里的 condition 都是描述属性列的

可以用 AND or OR 这两个关键字来组装多个条件

关于condition ，

如果是筛选数字类型的属性，可以有如下的关键字

Operator（关键字） Condition（意思） SQL Example(例子）
=, !=, < <=, >, >= 基础的 大于，等于等比较 col_name != 4
BETWEEN … AND … 在两个数之间 col_name BETWEEN 1.5 AND 10.5
NOT BETWEEN … AND … 不在两个数之间 col_name NOT BETWEEN 1 AND 10
IN (…) 在一个列表 col_name IN (2, 4, 6)
NOT IN (…) 不在一个列表 col_name NOT IN (1, 3, 5)

SELECT * FROM movies WHERE Id in (2,4);

就是从表movies中，找到Id == 2和4的那一行的所有值。

如果是筛选字符串类型的属性，可以有如下关键字：

Operator（操作符） Condition（解释） Example（例子）
= 完全等于 col_name = “abc”
!= or <> 不等于 col_name != “abcd”
LIKE 没有用通配符等价于 = col_name LIKE “ABC”
NOT LIKE 没有用通配符等价于 != col_name NOT LIKE “ABCD”
% 通配符，代表匹配0个以上的字符 col_name LIKE “%AT%” (matches “AT”, “ATTIC”, “CAT” or even “BATS”) “%AT%” 代表AT 前后可以有任意字符
_ 和% 相似，代表1个字符 col_name LIKE “AN_” (matches “AND”, but not “AN”)
IN (…) String exists in a list 在列表 col_name IN (“A”, “B”, “C”)
NOT IN (…) 不在列表 col_name NOT IN (“D”, “E”, “F”)

SELECT * FROM movies WHERE TITLE LIKE "%Toy Story%" ;

就是在movies表中，找到TITLE 包含Toy Story的行，可能出现“Toy Story”，“Toy Story 2”，“Toy Story 3” ……

查询结果过滤和排序

DISTINCT

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

1
2
3
SELECT DISTINCT column, another_column, …
FROM mytable
WHERE condition(s);

SELECT Director FROM movies ;

返回Director列的所有值，可能是 ‘张三’ ，‘张三’ ，‘张三’，‘王二’ ，‘王二’

SELECT DISTINCT Director FROM movies ;

返回Director列的所有值的唯一返回，返回‘张三’，‘王二’

ORDER

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

1
2
3
4
SELECT column, another_column, …
FROM mytable
WHERE condition(s)
ORDER BY column ASC/DESC;

ASC升序 , DESC 降序.

对于文本，按照文字字母顺序进行。

LIMIT

LIMIT 和 OFFSET 子句通常和ORDER BY 语句一起使用，

当我们对整个结果集排序之后，我们可以 LIMIT来指定只返回多少行结果 ,

用 OFFSET来指定从哪一行开始返回。

1
2
3
4
5
SELECT column, another_column, …
FROM mytable
WHERE condition(s)
ORDER BY column ASC/DESC
LIMIT num_limit OFFSET num_offset;

用JOINs进行多表联合查询

一般关系数据表中，都会有一个属性列设置为 主键(primary key)。

主键是唯一标识一条数据的，不会重复。

一个最常见的主键就是auto-incrementing integer(自增ID，每写入一行数据ID+1, 当然字符串，hash值等只要是每条数据是唯一的也可以设为主键.

借助主键（当然其他唯一性的属性也可以），我们可以把两个表中具有相同主键ID的数据连接起来。

具体我们用到 JOIN关键字。

分为INNER JOIN 和 OUTER JOIN ，OUTER JOIN又分为LEFT JOIN,RIGHT JOIN 和 FULL JOIN.

INNER JOIN

INNER JOIN 可以简写做 JOIN

1
2
3
4
5
6
7
SELECT column, another_table_column, …
FROM mytable （主表）
INNER 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 只会保留两个表都存在的数据（还记得之前的交集吗），这看起来意味着一些数据的丢失，在某些场景下会有问题.

OUTER JOIN

外连接包括左连接LEFT JOIN,右连接RIGHT JOIN 和 全连接FULL JOIN.

这几个连接方式都会保留不能匹配的行，和INNER JOIN 语法几乎是一样。

1
2
3
4
5
6
7
SELECT column, another_column, …
FROM mytable
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;

在表A 连接 B中

LEFT JOIN保留A的所有行，不管有没有能匹配上B

RIGHT JOIN则保留所有B里的行。

FULL JOIN 不管有没有匹配上，同时保留A和B里的所有行

关于特殊关键字 NULLs

在数据库中，NULL表达的是 “无”的概念，或者说没有东西。

因为 NULL的存在，我们需要在编写SQL时考虑到某个属性列可能是 NULL的情况 这种特殊性会造成编写SQL的复杂性

所以没有必要的情况下，我们应该尽量减少 NULL的使用，让数据中尽可能少出现 NULL的情况。

所以一个常见的方式就是为字段设置默认值,比如 数字的默认值设置为0，字符串设置为””字符串。

但是在一些NULL 表示它本来含义的场景（比如计算一些行的平均值的时候，如果是0会参与计算导致平均值差错，是NULL则不会参与计算），需要注意是否设置默认值还是保持NULL。

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

1
2
3
4
5
SELECT column, another_column, …
FROM mytable
WHERE column IS/IS NOT NULL
AND/OR another_condition
AND/OR …;

在查询中使用表达式

每一种数据库（mysql，sqlserver等）都有自己的一套函数，包含常用的数字，字符串，时间等处理过程.具体需要参看相关文档。

下面这个例子用到了两个表达式

  • 对结果做了一个除2
  • 条件要求particle_position这个属性绝对值乘以10大于500;

另外，AS关键字是用来起别名的 ，可以给表达式、表名、列名起别名，更加易于理解。

1
2
3
4
SELECT  particle_speed / 2.0 AS half_particle_speed 
FROM physics_data
WHERE ABS(particle_position) * 10.0 >500

在查询中进行统计

常用统计函数

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

下面这个AGG_FUNC()可以是某个函数，里面的参数可以是列 或 表达式

1
2
3
SELECT AGG_FUNC(column_or_expression)
FROM mytable
WHERE constraint_expression;

分组统计

GROUP BY 数据分组语法可以按某个col_name对数据进行分组

如：GROUP BY Year 指对数据按年份分组，去除重复的。

GROUP BY 分组结果的数据条数，就是分组数量

比如：GROUP BY Year，全部数据里有几年，就返回几条数据，不管是否应用了统计函数.

如果把统计函数和GROUP BY结合，那统计结果就是对分组内的数据统计了.

select grade , max(salary) from student group by grade

查看按等级划分人员工资最大值

当然，如果想对分组完的数据再筛选，可以使用HAVING

1
2
3
4
5
SELECT group_by_column, AGG_FUNC(column_expression) AS aggregate_result_alias, …
FROM mytable
WHERE condition
GROUP BY column
HAVING group_condition;

查询执行顺序

下面是一个完整的查询语句，下面谈谈它的执行顺序。

1
2
3
4
5
6
7
8
9
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 或 JOIN会第一个执行，确定一个整体的数据范围.
  2. WHERE 语句，我们确定了数据来源之后，就将在这个数据源中按要求进行数据筛选
  3. GROUP BY 分组，GROUP BY 将对之前的数据进行分组，统计等，并将是结果集缩小为分组数.这意味着其他的数据在分组后丢弃.
  4. 如果你用了 GROUP BY 分组, HAVING 会在分组完成后对结果集再次筛选
  5. SELECT用来对结果col简单筛选或计算，决定输出什么数据.
  6. DISTINCT 将负责排重.
  7. ORDER BY 对结果做排序。
  8. LIMIT 和 OFFSET 从排序的结果中截取部分数据.