SQL常用语法自用总结

---

前言

最近公司开始使用DataBricks这款产品，而DataBricks支持多种语言编程。同时我司的运用场景是用来链接DataLake与本地数据库。作为数学、统计背景出身的程序员，平时工作也更多是使用python，所需的SQL语言是我的弱项。因此根据一个链接学习了一下SQL，并在此做一下笔记。

---

`

学习链接

SQLbot，可供在线实时练习，感觉还挺方便。

语言规范

1. sql不要求关键字全大写（可小写），但通常习惯是全大写
2. 单行注释是两个减号 --，可用(crtl + /)
3. 多行注释是用 /* */

一. 查询query

（1）单表基础查询query

1. 基础筛选列

功能：
可用*全筛选，
可单独筛选某几列，
可给筛选出的列命名Alias,
对筛选列可进行数值运算：如乘除加减，ABS()绝对值

SELECT * 
FROM mytable;

-- 筛选具体列和数值运算
SELECT column1, column2 / 2.0 , ...
FROM mytable;

-- 使用代称
SELECT col_expression AS expr_description, …
FROM mytable;

2. 条件筛选及符号、语法

SELECT * 
FROM mytable;
WHERE condition
    AND/OR another_condition
    AND/OR …;

Operation	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 (…)	数字在list中	col_name IN (2, 4, 6) col_name IN (‘A’, ‘B’, ‘C’)
NOT IN (…)	数字不在list(可理解为python的set)中	col_name NOT IN (2, 4, 6) col_name NOT IN (‘A’, ‘B’, ‘C’)
=	字符串string相同	col_name = “abc”
!= or <>	字符串 string不同	col_name != “abcd”
LIKE	字符串不区分大小写下的string相同	col_name LIKE “abc”
NOT LIKE	字符串不区分大小写下 string不同	col_name NOT LIKE “abcd”
%	用于正则表达式匹配，代表0个或多个字符，匹配只可使用LIKE/NOT LIKE	col_name LIKE “%AT%”(可匹配 “AT”, “ATTIC”, "CATBD"等)
_	用于正则表达式匹配，代表1个字符，匹配只可使用LIKE/NOT LIKE	col_name LIKE “AT_” (可匹配 “ATd”, 不可匹配AT)
IN (…)	字符串在list中	col_name IN (“A”, “B”, “C”)
NOT IN (…)	字符串不在list中	col_name NOT IN (“A”, “B”, “C”)

3. 去重、排序，数量限制

关键词
DISTINCT：去重
ORDER BY
ASC/DESC: 根据某列进行排序， ASC：升序/DESC：降序
LIMIT num_limit：根据num_limit，筛选num_limit的数量的样本
OFFSET： 决定筛选的起始位置

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

（2）多表进行查询

1. inner join

此处的INNER JOIN可用JOIN替代，但为后续阅读方便推荐使用INNER JOIN。INNER JOIN 为求两个表的交集。

SELECT column_name, 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;

2. outer joins

from table_A LEFT JOIN table_B: 所有的table_A行
from table_A RIGHT JOIN table_B: 所有的table_B行
from table_A FULL JOIN table_B: 两table的并集

SELECT column_name, 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;

（3）补充语法

1. NULL相关

当多表外连接，容易出现NULL的情况。或者可以设定NULL的默认值，如0或者其余numerical data,或空字符串。
WHERE column IS/IS NOT NULL

SELECT column, another_column, …
FROM mytable
WHERE column_name IS/IS NOT NULL
AND/OR another_condition
AND/OR …;

2. aggregates聚合

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

常见聚合方程

方程	描述
COUNT(*) /COUNT(column)	COUNT(*)用于统计行数/ COUNT(column)用于统计column中的非NULL行数
MIN（column）	最小值
MAX(column)	最大值
AVG(column)	平均值
SUM(column)	求和

3. GROUP BY和 HAVING

使用HAVING来对进行group by之后的列进行筛选，跟WHERE的用法相同。

SELECT group_by_column, AGG_FUNC(column_expression) AS aggregate_result_alias, …
FROM mytable
WHERE condition
GROUP BY column_name
HAVING group_condition;

（4）query的执行顺序

完整query常用语句

SELECT DISTINCT column_name, 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
4. HAVING
5. SELECT
6. DISTINCT
7. ORDER BY
8. LIMIT/OFFSET

二. 增、改、删 （对于行）

（1）增加行INSERTING

根据当前表每一行中所包含列的数据格式Schema进行对应并添加行。

INSERT INTO mytable
VALUES (value_or_expr, another_value_or_expr, …),
       (value_or_expr_2, another_value_or_expr_2, …),
       …;

若数据不完整，只有部分行的值，需要注明添加的列名，并在添加数据时能够对应。

INSERT INTO mytable
(column_name, another_column, …)
VALUES (value_or_expr, another_value_or_expr, …),
      (value_or_expr_2, another_value_or_expr_2, …),
      …;

（2）更新行updating rows

根据WHERE查询到需要改的部分，再通过逐列修改进行更新。最好是先用select确认修改的部分，否则容易犯错。

UPDATE mytable
SET column_name = value_or_expr, 
    other_column = another_value_or_expr, 
    …
WHERE condition;

（3）删除行

DELETE FROM mytable
WHERE condition;

若无WHERE，则为删除所有行。

三、创建、修改和删除（表）

（1）创建

首先是mytable: 表名字，然后逐列创建表

必需	含义
column	列名
DataType	数据格式

可选	含义
TableConstraint	一些限制，如主键，非空等
DEFAULT default_value	默认值

CREATE TABLE IF NOT EXISTS mytable (
    column_name DataType TableConstraint DEFAULT default_value,
    another_column DataType TableConstraint DEFAULT default_value,
    …
);

1. 常见数据格式

data type	描述
INTEGER, boolean	0 or 1可被理解为boolean下的值
FLOAT, DOUBLE, REAL	根据精度确认
CHARACTER(num_chars), VARCHAR(num_chars), TEXT	string，不同的最大长度等不同，前两种需注明最大字符数
DATE, DATETIME
BOLB	binary data，可能需要metadata

2. TableConstraint表格限制条件

Constraint	描述
PRIMARY KEY	主键，unique
AUTOINCREMENT	对于数字自动增加
UNIQUE	无重复，不可添加荣福数据
NOT NULL	不可为NULL
CHECK(expression)	可用于测试
FOREIGH KEY	每个值对应零一个表的值

（2）修改表

1. 增加列

与创建列相同，column_name, DataType必须，OptionalTableConstraint 和 DEFAULT default_value可选。

ALTER TABLE mytable
ADD column_name DataType OptionalTableConstraint 
    DEFAULT default_value;

2. 去除列

ALTER TABLE mytable
DROP column_to_be_deleted;

3. 重新命名列

ALTER TABLE mytable
RENAME TO new_table_name;

（3）删除表

若有另外一张表依赖于要删除的表，可能需要先更新依赖关系，再进行删除。

DROP TABLE IF EXISTS mytable;

总结

本文为学习的笔记，主要为自己后续使用进行参考，如果能帮助到大家也欢迎点赞收藏。如果有侵权，马上删除。