前言
最近公司开始使用DataBricks这款产品,而DataBricks支持多种语言编程。同时我司的运用场景是用来链接DataLake与本地数据库。作为数学、统计背景出身的程序员,平时工作也更多是使用python,所需的SQL语言是我的弱项。因此根据一个链接学习了一下SQL,并在此做一下笔记。
`
学习链接
SQLbot,可供在线实时练习,感觉还挺方便。
语言规范
- sql不要求关键字全大写(可小写),但通常习惯是全大写
- 单行注释是两个减号 --,可用(crtl + /)
- 多行注释是用 /* */
一. 查询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;
执行顺序:
- FROM 和 JOIN
- WHERE
- GROUP BY
- HAVING
- SELECT
- DISTINCT
- ORDER BY
- 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;
总结
本文为学习的笔记,主要为自己后续使用进行参考,如果能帮助到大家也欢迎点赞收藏。如果有侵权,马上删除。