数据人必须知道的 SQL 概念（A—Z）

作为数据从业者，大家用的最多的应该就是SQL语言了，那么除了日常的select，你还了解哪些SQL相关的概念呢？今天就按照字母表的顺序(A-Z)来给大家简单介绍一下数据人应该知道的SQL概念。



A:Alias

别名，包含表的别名和列的别名。在哪些场景下我们需要用到别名呢？通常是多表join的时候表名过长，多次引用书写比较麻烦的时候我们会给表起一个别名；另外就是表join的时候，如果多个表中有重复的列名而且我们都需要提取出来的时候也需要别名；再则就是列级的别名，通常我们是给表中的列取一个“有意义” 的名字的联合as一起使用。例子如下：

select st.id as st_id,sc.id as sc_id --列级别名
from student st --表级别名
left join
score sc --表级别名
on st.id=st.id



B：Begin Transaction

开始事务操作，SQL里面的事务是是一个操作序列，也就是一次执行的最小单位。一次执行，同一个事务里面的操作要么全部执行要么全部不执行，不存在部分执行部分未执行的可能。依赖事务，我们在数据库里面的部分误操作才有机会被回滚（恢复到本次事务执行之前的状态）。如：



begin transaction 
update orders set status = 'sent' where order_id = '12345' 
update orders set status = 'sent' where order_id = '54321' 
commit transaction



C：CTEs（Common Table Expressions）

通用表达式，说人话就是我们通常用使用的WITH语句。如果我们需要join的表比较多，用到的列也比较多，而且同一个表会被join多次的时候通常会在SQL语句的开头使用WITH语句把需要参与join的表先用with形成一个临时结果集，然后在后面的SQL语句找那个直接引用WITH后的临时表即可。如：

; -- start the CTE with a semicolon to terminate anything above 
with sent as 
-- here is where you name the dataset 
(select emailaddress, emailid, senddate from marketing.emailsent where senddate between '2018-01-01' and '2018-01-31' ), 
-- add a comma if you need to add a subsequent CTE 
unsubs as (select emailaddress, emailid, senddate from marketing.emailunsubscribe where senddate between '2018-01-01' and '2018-01-31' ) 
-- no comma for the last CTE 
select 'January' as [monthdelivered], c.country, count(distinct sent.emailaddress) as [countofdelivered], count(distinct unsubs.emailaddress) as [countofunsubd] 
from sent left join marketing.customers c on sent.email



D：Design

设计，这里的设计主要针对数据模型的设计。例如在星型模型中，有两类表，分别是实时表（Facts）和维度表（Dimensions）,一个事实表周围会对应多张维度表，顾名思义星型模型。



E：ETL

ETL简单来说就是把数据从一个地方经过一定的处理转移到另外一个地方的过程。具体来说包含三个大的步骤，分别是：



• E，Extract，数据的加载，把原生数据从数据源加载到表的过程。



• T:Transform,对原始的数据赋予一定的加工逻辑，使之最终形成目标表想要的结果形式。



• L：Load，把加工处理后的结果写入到目标表的过程。



F:Function

函数，这个大家都不陌生，无论是mysql里面的函数还是oracle中的存储过程或者是hive中的UDF,UDTF等都可以称之为函数。



G：Group by

分组聚合，通常用来对数据列做一些计算，如max,min,sum,count等，属于特别常用的一个功能。



H：Heaped Storage

堆存储，这个大家可能比较陌生，主要是针对数据在库里面的存储的。具体来说，堆存储是在非聚簇索引的数据库中数据的存放方式，数据是按照先来后到存储的而不是按照通常的排好序的方式存储的。关于聚簇索引和非聚簇索引这里不再详细介绍。



I：Integrity

完整性，主要保证数据的高质量，确保数据是可追踪、可搜索和可恢复的。完整性，包含三类，分别是



• 实体完整性：每个表必须有唯一的主键。



• 参照完整性：表中引用的外键（foreign keys）在对应的主表中是非空的。



• 域完整性：表中的列有数据类型，长度，取值范围上的限制。



J：Join

JOIN大家都比较熟悉了，我们前面也介绍过了，重点要区分LEFT JOIN,RIGHT JOIN,JOIN以及FULL JOIN



K：Key

这里主要是指主键（primary key）：用来确保记录值的唯一性和字段的非空性。使用上可以在建表的时候指定，如

create table students 
( id int not null primary key,
firstname varchar(255) not null,
lastname varchar(255) not null, );



L：Lock

 锁，锁的出现，主要是为了防止多个用户同时对同一个表或记录进行操作时可能造成互相影响以至于出现不正确的数据。比如说，你的银行账户里面只有100元钱，但是你和你的另一半同时在不同地方的ATM机上同时取出这100元，如果没有所的话你们都操作成功的话，银行就倒闭了。



所的分类比较多：表级锁、行级锁；乐观锁、悲观锁等，这里不再详细介绍。



M：Massive Parallel Processing

大规模并行处理，这是目前大数据的核心思想，采用并行处理，每个节点（机器）只处理整体数据中的一部分，这样就可以分解大任务进而显著提高数据处理的速度，而且容易横向扩展。



N：Normalisation

规范化，规范化增强了数据的完整性，允许在不改变原有数据结构的情况下增加新的数据。主要包含三个方面的好处：



1. 消除或减少重复，消除是在同一张表中不要出现两条完全相同的记录；减少重复是指不要把一些维度的详细信息存储在事实表中。如订单表中，只需要存放user的ID，不需要存放user的详细地址，电话等，这样可以显著减少使用的磁盘空间。

2. 使update操作变得容易，拿上面的例子来说，如果再订单表和支付表中都存放了user的详细地址情况，一旦用户的地址发生了变化就需要在订单表和支付表中分别更新对应的记录，如果只有一处更新了就会造成数据不一致的情况。

3. 查询速度更快，规范化后的数据没有重复，避免了去重操作，排序的时候也大大节省了时间。



O:OLTP v OLAP

• OLTP，Online Transaction Processing ，在线交易处理程序，可理解为业务数据库，主要是为了满足线上业务使用的，要求高并发且处理速度快。



• OLAP，Offline Analytics Processing，离线分析处理系统，可理解为数据分析系统，主要数数据仓库，为满足分析师，数据挖掘等相关人员分析业务情况，挖掘隐藏信息使用的，偏离线，对实时性要求较低。



P：Privileges

权限，就是说也可以把一个库或者表的查询、修改等权限授权给某一个角色或者用户，当然也包括从已有权限的用户、角色出回收权限。如：



GRANT ALL ON <schemaname.tablename> TO <username> 
-- 给某个用户授予所有的权限
​
SELECT ON <schemaname.tablename> TO <username> 
--只把select权限授予某个用户



权限，就是说也可以把一个库或者表的查询、修改等权限授权给某一个角色或者用户，当然也包括从已有权限的用户、角色出回收权限。如：



Q：Query Plan

执行计划，也就是SQL的详细执行过程，如果你的SQL比较复杂或者想查看SQL是如果处理多表JOIN,是否使用了索引等，都可以使用执行计划。查看执行计划的命令如下：

EXPLAIN -- 显示执行计划 
EXPLAIN ANALYZE --使查询计划更易读



R：Recovery

Recovery，数据恢复，这是一个相对来说不是很常见的操作，但是一旦使用了这个操作一定是发生了大事。数据恢复，主要在数据库发生错误，地震，火山等灾难发生造成当前数据不可用时采取一些措施使数据恢复到可用状态的过程。这就要求在灾难发生之前未雨绸缪，如写日志，数据库镜像，异地备份等，这样灾难发生时才能做到有“备”无患。



S：System Tables

系统表，或者叫做元数据表，主要是用来存放一些关于数据库本身的一些信息。如数据库中表的个数，每个表索引的情况，表的大小，数据库的用户等相关信息。



T：Truncate v Drop

删除数据和删除表，Truncate是DDL操作，在删除数据的同时会保留表的原有结构，但是删除后数据不可使用rollback恢复；Drop操作会删除整个表，包括表中的数据和表的结构。再说一下Delete操作，这是DML操作，可指定where条件要删除的数据，删除后可使用rollback恢复，但是数据量较大时效率较低。



U：Union

这也是一个常用的操作，使用的时候要主要union 和union all的区别，推荐使用union all。



V：View

数据视图，大家通常使用的都是非物化视图，也就是没有对应的真实数据存在。视图本质上来说就是一段为执行的SQL语句，这段SQL语句平常只是安静的躺在那里，只有当时发出指令(select) 的时候这段SQL才会真正的执行。视图有什么好处呢，一般使用视图是指只提取用户需要的数据，防止数据泄露且可以避免底层复杂SQL的书写。



W：Window Function

窗口函数，为什么叫窗口函数呢，因为他不像聚合函数那样对数据做聚合，他保证每一行数据的完整而且在每一行上加上一列行号。一般我们在数据在某个列上有重复想要去重时使用，例如：



select orderid, ordername order_date, 
rank() over(order by amount_due desc) as num
from order ;



X：XML

这里主要想说的部分数据库支持把XML格式的数据导入到数据库中。



Y：Year

这里的Year主要是想指SQL里面日期时间相关函数的处理，我们早另一篇里面说的很清楚了，有疑问的可以参见另外一篇的单独介绍。



Z：Zero

这里想用Zero提醒大家不论在哪里0都是一个特殊的存在。具体在SQL里面，0和NULL以及空并不是是一回事，他不代表不存在，而是存在但是数值是0.另外一点大家需要注意的地方是，在做数学运算时要对分母是0的情况做单独的处理，或者程序会报错。





说明：部分内容参考自https://dev.to/helenanders26/sql-series-from-a-to-z-2pk9