SQL必知必会学习笔记

... 2021-07-01 21:46:47 数据库
  • SQL学习
大约 7 分钟

学极客时间的 SQL必知必会专栏,做记录

# 初步了解

我们可以把 SQL 语言按照功能划分成以下的 4 个部分:

  1. DDL,英文叫做 Data Definition Language,也就是数据定义语言,它用来定义我们的数据库对象,包括数据库、数据表和列。通过使用 DDL,我们可以创建,删除和修改数据库和表结构。
  2. DML,英文叫做 Data Manipulation Language,数据操作语言,我们用它操作和数据库相关的记录,比如增加、删除、修改数据表中的记录。
  3. DCL,英文叫做 Data Control Language,数据控制语言,我们用它来定义访问权限和安全级别。
  4. DQL,英文叫做 Data Query Language,数据查询语言,我们用它查询想要的记录,它是 SQL 语言的重中之重。在实际的业务中,我们绝大多数情况下都是在和查询打交道,因此学会编写正确且高效的查询语句,是学习的重点。

SQL 语言定义了我们的需求,而不同的 DBMS(数据库管理系统)则会按照指定的 SQL 帮我们提取想要的结果。采用ER图(Entity Relationship Diagram / 实体 - 关系图)来设计数据库,ER 图评审通过后,我们再用 SQL 语句或者可视化管理工具(如 Navicat)创建数据表。ER图是我们用来描述现实世界的概念模型,在这个模型中有 3 个要素:实体、属性、关系,实体就是我们要管理的对象,属性是标识每个实体的属性,关系则是对象之间的关系,实体之间可以是一对一、一对多或多对多的关系。

SQL 书写规范:

  1. 表名、表别名、字段名、字段别名等都小写;
  2. SQL 保留字、函数名、绑定变量等都大写。
-- 例子
SELECT name, hp_max FROM heros WHERE role_main = '战士'
1
2

底下留言中有在争执数据库系统大小写的规范,我们使用大小写的规范是为了使代码可读性提高,即使数据库系统本身不区分大小写,也不影响我们的书写规范,通常情况下,我们的「代码写出来都是给人读的,附带在机器上运行」。

MySQL在Windows下都不区分大小写。 Oracle中,SQL语句是不区分大小写,如果查询中有字符,是区分大小写的 比如 SELECT * FROM heros WHERE name = 'guanyu' 和 SELECT * FROM heros WHERE name = 'GUANYU' 在Oracle中会认为是不同的查询,而在MySQL中是相同的查询 同时,我们可以通过修改系统参数来进行配置,比如在MySQL可以通过参数lower_case_table_names来配置 数据库和数据表的大小写敏感性

# 了解 DBMS

比如 MySQL、SQL Server、PostgreSQL、DB2 和 MongoDB 等,这些都是我们比较熟知的 DBMS ,我们使用 SQL 和这些 DBMS 沟通进行数据的存取。

  • DBMS 的英文全称是 DataBase Management System,数据库管理系统,实际上它可以对多个数据库进行管理,所以你可以理解为 DBMS = 多个数据库(DB) + 管理程序。
  • DB 的英文是 DataBase,也就是数据库。数据库是存储数据的集合,你可以把它理解为多个数据表。
  • DBA 的英文是 DataBase Administrator,数据库管理员。一般企业里的 DBA 主要负责数据库的权限控制、维护及一些偏底层的事情,像表设计查询优化这种业务相关的通常是业务上的后端处理。
  • DBS 的英文是 DataBase System,数据库系统。它是更大的概念,包括了数据库、数据库管理系统以及数据库管理人员 DBA。

# 数据库类型

  • 关系型数据库(RDBMS)就是建立在关系模型基础上的数据库,SQL 就是关系型数据库的查询语言。
  • 键值型数据库通过 Key-Value 键值的方式来存储数据,其中 Key 和 Value 可以是简单的对象,也可以是复杂的对象。Key 作为唯一的标识符,优点是查找速度快,在这方面明显优于关系型数据库,同时缺点也很明显,它无法像关系型数据库一样自由使用条件过滤(比如 WHERE),如果你不知道去哪里找数据,就要遍历所有的键,这就会消耗大量的计算。键值型数据库典型的使用场景是作为内容缓存。Redis 是最流行的键值型数据库。
  • 文档型数据库用来管理文档,在数据库中文档作为处理信息的基本单位,一个文档就相当于一条记录,MongoDB 是最流行的文档型数据库。
  • 搜索引擎也是数据库检索中的重要应用,常见的全文搜索引擎有 Elasticsearch、Splunk 和 Solr。虽然关系型数据库采用了索引提升检索效率,但是针对全文索引效率却较低。搜索引擎的优势在于采用了全文搜索的技术,核心原理是“倒排索引”。
  • 列式数据库是相对于行式存储的数据库,Oracle、MySQL、SQL Server 等数据库都是采用的行式存储(Row-based),而列式数据库是将数据按照列存储到数据库中,这样做的好处是可以大量降低系统的 I/O(行式存储是把一行的数据都串起来进行存储,然后再存储下一行。同样,列式存储是把一列的数据都串起来进行存储,然后再存储下一列。这样做的好处,就是相邻的数据的数据类型是一样的,因此也更容易压缩。压缩之后就自然降低了IO),适合于分布式文件系统,不足在于功能相对有限。
  • 图形数据库,利用了图这种数据结构存储了实体(对象)之间的关系。最典型的例子就是社交网络中人与人的关系,数据模型主要是以节点和边(关系)来实现,特点在于能高效地解决复杂的关系问题。

相比于 SQL,NoSQL 泛指非关系型数据库,包括了上述的键值型数据库、文档型数据库、搜索引擎、列存储和图形数据库。

# SQL 执行

# Oracle

SQL 在 Oracle 中的执行过程:

SQL 在 Oracle 中的执行过程

从上面这张图中可以看出,SQL 语句在 Oracle 中经历了以下的几个步骤。

  1. 语法检查:检查 SQL 拼写是否正确,如果不正确,Oracle 会报语法错误。
  2. 语义检查:检查 SQL 中的访问对象是否存在。比如我们在写 SELECT 语句的时候,列名写错了,系统就会提示错误。语法检查和语义检查的作用是保证 SQL 语句没有错误。
  3. 权限检查:看用户是否具备访问该数据的权限。
  4. 共享池检查:共享池(Shared Pool)是一块内存池,最主要的作用是缓存 SQL 语句和该语句的执行计划。Oracle 通过检查共享池是否存在 SQL 语句的执行计划,来判断进行软解析,还是硬解析。那软解析和硬解析又该怎么理解呢?在共享池中,Oracle 首先对 SQL 语句进行 Hash 运算,然后根据 Hash 值在库缓存(Library Cache)中查找,如果存在 SQL 语句的执行计划,就直接拿来执行,直接进入“执行器”的环节,这就是软解析。如果没有找到 SQL 语句和执行计划,Oracle 就需要创建解析树进行解析,生成执行计划,进入“优化器”这个步骤,这就是硬解析。
  5. 优化器:优化器中就是要进行硬解析,也就是决定怎么做,比如创建解析树,生成执行计划。
  6. 执行器:当有了解析树和执行计划之后,就知道了 SQL 该怎么被执行,这样就可以在执行器中执行语句了。

共享池是 Oracle 中的术语,包括了库缓存,数据字典缓冲区等。库缓存区,它主要缓存 SQL 语句和执行计划。而数据字典缓冲区存储的是 Oracle 中的对象定义,比如表、视图、索引等对象。当对 SQL 语句进行解析的时候,如果需要相关的数据,会从数据字典缓冲区中提取。

库缓存这一个步骤,决定了 SQL 语句是否需要进行硬解析。为了提升 SQL 的执行效率,我们应该尽量避免硬解析,因为在 SQL 的执行过程中,创建解析树,生成执行计划是很消耗资源的。

上次编辑于: 2021年7月23日 20:12