↓跳到主要内容
  1. Skills/
  2. thinking in SQL | 废话SQL，思维与实践/

在开始之前

··字数 5349·11 分钟
sql 概念
目录

不管你是开发实际的应用程序来操作数据库，或者准备踏入数据科学领域从事数据开发、或者数据开发，作名副其实的 SQL BOY的一员，在开始SQL旅途之前，有必要知道一些背景，以及关联的基础知识。

SQL 的由来 #

数据库是一个伟大的发明，它把我们发现、生产数据的信息，按照它所能提供的格式存储起来。为了方便数据的访问、操作，人们又发明了SQL语句（直接使用编程语句来编写数据访问操作程序，门槛太高，并不是每一个用户都学习过编程语言以及经过编程训练），有了它，SQL用户的技术门槛降低了很多很多。

早期在大数据环境上做数据开发，需要使编程语言来直接，各种概念、步骤，必须由开发人员手工编程，开发效率低到很难想象。现在如果有大数据平台不支持SQL的，那它非常可能不适合你，请忽略它（也许是暂时）。

数据库产品不只一个厂商，在使用过程中，功能不断添加，它是不断发展的。因此就有了 SQL-ISO 标准。标准是公开的，但并不免费（看来是数据库厂商没给够赞助啊🧐）。

实在看不懂ISO官网上的文档列表 Database languages ，或者 语言类标准 。SQL 标准被分成很多部分，分章说明，有悟没有付费，看不到内容，没办法给大家摘要。

不过，可以通过这个网站 modern-sql.com 来跟踪SQL标准的变化。

数据库产品的市场现状 #

在企业级应用中，oracle占绝对的霸主地位，而全球使用最广泛的免费开源数据库要属 mysql（不知有多少是php的功劳）。不过，各位可能不知道，sqlite 因为定位嵌入式场景，随着应用程序一并发布，没有感知而已，其实它的使用量才是最大的，比如 firefox 浏览器用它来保存浏览历史、书签，仅firefox的用户量就可以想象 sqlite 的使用规模。另外，号称全宇宙最先进的数据库 postgres，不少功能特性都是在 postgres 中先出现后才被其它厂商模仿抄袭。postgres 在国外非常流行，国内各种原因，被 mysql 抢在前面。看看腾讯云、阿里云、华为云，哪个云服务商没有 postgres 的 cloud 版。

在大数据时代，不谈点 hadoop、hive 之类怎么行。本《thinking in sql》系列面向的是如何编写SQL，运用SQL。至于是在关系型数据库运行，还是类似于 hadoop 上运行，并不是准备要讨论的。大数据产品的SQL，与是参照标准，实现了大部分常见的SQL功能，对于学习来说，已经足够。所以在选择练习环境时，尽可能选轻巧的、容易部署的，最好可以在桌面通过一个命令行就启动运行一个数据库服务环境的产品。后文会指引如何建立一个简单的练习环境。

数据库排名 DB-Engines Ranking 。

数据库、服务、实例傻傻分不清楚 #

本小节试图解释几个技术术语，并非咬文嚼字。在阅读一些数据库产品文档时，会出现实例、服务、进程这些词汇，读者容易迷失在这些有差异的上下文中。

数据库 一词，在不同的上下文有不同的含义，因为它可以是数据库产品、数据库服务（系统）、数据库实例、数据库进程其中之一，而这些名词所表示的是不同的事物。

数据库产品，是指由各数据库厂商提供的产品或服务，如 oracle、mysql、postges，以及各云厂商提供的各种云数据库服务，需要付费或者下载才能得到的物件，此为软件，类似于商品。当拿到数据库产品时，拿到的是一份数据库产品的软件拷贝；而购买在线云数据库服务时，得到的是云数据库的使用权限。

🙋🏻‍♀ “用什么数据库来保存数据呢？”，这里的数据库指的数据库产品。

数据库实例，是指将数据库产品的软件安装到硬件环境上后，由操作系统调度所运行起来的一系列程序，它由一个或多个操作系统进程、内存、数据存储单元所构成，即软件程序的运行态。学习过面向对象的同学，就非常清楚“实例”的含义。当数据库系统退出时，数据库实例也就消亡。sqlite 的数据库进程只有一个，而 oracle 、mysql、postgres 这些数据库产品，运行后的实例都由多个数据库进程协调工作。

🙋🏻‍♀ “用任务管理器查一查数据库”，此处数据库是数据库进程，与它相关的进程组成了数据库实例。

数据库服务（系统），是指部署了数据库产品后所运行起来后，对外提供了数据操作相关的一系列功能的服务系统，通过程序或者接口来获取这些功能。数据库服务（系统）可由多个数据库实例组成（比如集群）。某些情况，数据库服务与数据库实例很难区分。

🙋🏻‍♀ “启动数据库”，指启动数据库实例，运行数据库服务（系统）。

创建数据库，指在具体数据库服务（系统）中创建用来保存数据的目录或空间。相比之下，这个概念要小很多。平常我们所讲的“创建数据库”，就是指在具体的数据库系统中，使用命令或者客户端，如通过 create database 名称 创建一个实际可以存储数据的空间，然后在里面折腾。

并不需要每次在用到“数据库”一词，都要指明“产品”、“服务（系统）”、“实例”三者之一。当习惯之后，基本都是简称为“数据库”。只有当需要区分定义，或多种定义在同一个上下文中出现时，才会明确所指。

🙋🏻‍♀ “把数据库迁移到另一个数据库”，指把数据库中的数据从一个数据库系统迁移到另一个（可能相同数据库产品，也可能是不同产品）数据库系统
🙋🏻‍♀ “数据库安装好了，需要创建个示例数据库”，指数据库服务系统部署好后，通过命令或者客户端，建表、装载示例数据
🙋🏻‍♀ “喂，把数据库安装好，然后把数据库起起来，我要把数据装到数据库里面去”，真要命 😱😱😱

实验环境 #

学习 SQL，怎能没有一个用于练习的实验环境，具体见文章

如何快速设立一个可用的SQL环境

编写SQL 是一项需要实践的技术，离开环境，只看文章、书籍，可能无法更深刻的理解问题，甚至根本不知道有悟所言何物。有必要准备一个可以运行SQL的环境。在实践中，SQL 主要被用于实际工程项目，而往往缺少演示环境或练习环境，主要的障碍是数据库表设计、可以测试样例数据。 本文将介绍如何建立自己的演示测试学习环境 🖥，并添加一些示例数据库用于在学习《thinking in sql》系列时练习使用。 Get your hand dirty ⌨️。

客户端 #

访问数据库，需要通过客户端，有命令行交互式解析器（interpretor）或者桌面图形程序（GUI）两种。 对于命令行窗口有恐惧症的，建议使用开源免费的桌面图形客户端。

没接触的客户端工具的人，不用害怕。我们只是为了通过客户端访问数据库，获得一个可以输入SQL的窗口，然后执行SQL。不管是命令行 cli、桌面图形客户端，它们连接数据库时所需要提供的信息是一样的，数据库服务的地址、用户、密码、数据库名称等。只是区别在于，命令行 cli 连接到数据库服务后，直接在交互式命令行输入SQL查询或者命令来管理数据库；而桌面GUI客户端，管理功能或者查询，需要打开对应的窗口，免去输入管理命令以及参数而已。

命令行、shell #

通过命令行 cli 连接到数据库服务后，运行SQL。各个命令行 cli 有自己的内置命令，进入交互式执行器（intepretor）后，使用 \?、\help 等可打印出帮助提示。

使用命令行工具时，需要将工具的 bin 目录配置到环境变量 PATH 中。

工具 使用技术 数据库 提供商
psql c postgres postgres，随产品分发
sqlite3 c sqlite sqlite，sqlite3 命令本身
sqlplus c oracle oracle，随产品分发
Oracle SQLcl java oracle oracle，现代化的oracle专用命令行工具。需要 java8+。
mysql c mysql mysql，随产品分发
mysql shell python, js mysql mysql，用不习惯 mysql cli 的可以使用这个 shell，更加现代化
mycli python mysql www.mycli.net ，第三方
pgcli python postgres www.pgcli.com ，第三方

桌面图形化客户端 #

工具 使用技术 平台 数据库
老牌客户端 dbeaver java win, macos, linux 通用型 with jdbc
sqlitestudio c win, macos, linux sqlite
sqlitebrowser c win, macos, linux sqlite
新型客户端 antares-sql js, electron win, macos, linux mysql, postgres, sqlite
新型客户端 beekeeperstudio js, electron win, macos, linux sqlite, mysql, postgres, cockroachdb, sql server, amazon redshift
navicat premium，收费。 c win, macos, linux sqlite, mysql, postgres, oracle, sql server, mongodb, amazon redshift
sqliteflow ，收费 objc macos, ios sqlite
sql developer java win, macos, linux oracle 官方提供
MySQL Workbench c,python win, macos, linux mysql 官方提供

命名 #

在数据库中，每个数据库对象都有名称，即 identifier（标识符），各数据库的规定存在差异。

主要分为两方面规定：标识符长度、标识符限定。

数据库 标识符长度 标识符元素 大小写 原文链接
oracle 11g 不超过30字节1⃣，以下情况例外:数据库名称不超过8字节数据库链接（dblink）最长可128字节 字母、数字、下划线 (_)、美元符号 ($) 和井号 (#)，也可以是 unicode字符 默认大写2⃣ Database Object Names and Qualifiers
postgres 14 最长 63 字节3⃣ 字母或下划线(_)开头，其它可以为字母、数字、下划线 、美元符号 ($)、unicode字符 默认小写4⃣ 4.1.1. Identifiers and Key Words
mysql 8 64 字节 字母、数字、下划线 默认小写5⃣，但会受系统参数 lower_case_table_names 影响 Identifier Length Limits ， Identifier Case Sensitivity ， Mapping of Identifiers to File Names

1⃣ oracle 12.2 及以上的版本，标识符长度 可达128字符 ，并且无数据库名称、数据库链接命名长度约束的例外。

2⃣ 默认情况下，oracle 数据库使用大写字母来保存标识符。如果命名为关键字、或者包含小写、甚至空格，可以用 双引号("") 括起来。即 youwu_today、YOUWU_TODAY、"YOUWU_TODAY" 三者均相同，与 "youwu_today"不同。当标识符为中文时（有悟喜欢用中文命名结果集字段），无须使用双引号。

3⃣ postgres 中标识符命名最长长度为 NAMEDATALE - 1，常量 NAMEDATALEN 在源码 src/include/pg_config_manual.h 中声明。postgres 14 该常量值为 64 。

4⃣ postgres 按小写字母来保存标识符。若SQL中的标识符是大小写敏感，用 双引号("") 括起来。即 youwu_today、YOUWU_TODAY、"youwu_today" 三者相同，与 "YOUWU_TODAY" 不同，与 oracle 标识符的规范正好相反。当使用中文或其它非规定的正常字符做为标识符时，也必须使用双引号。

5⃣ mysql 使用目录来保存数据库与表。由于 windows、unix操作系统对文件名大小写敏感不同，规则也不一样。如 youwu_today 与 YOUWU_TODAY 是不同的，除非设置 lower_case_table_names 且文件系统大小写不敏感。为了避免大小写造成混乱，建议统一小写。若标识符中包含 - 这些非正常字符时，使用反引号 (``) 括起来，如 `youwu-today`。

⚠️ 区分单引号与双引号的差别。如 ‘youwu.today’ 是字符串，而 “youwu.today” 是标识符。

数据类型 #

以防部分原先没有编程基础的人掉坑，有悟有必要提醒一点。虽然SQL是脚本，不是直接执行的编程语言，但最终还是要转译为数据库内部所支持的执行代码。过程中涉及到一个非常重要，又容易被忽略的概念，数据类型。

比如 1 是数字，它的类型为数字类型，对应的8位二进制字节码是 00000001，而 ‘1’ 是字符串，对应的 ascii 值为 49（如果你手上的数据库支持 ascii、chr函数，不妨试一下，ascii('1') 与 chr(49)），若要比较 1 与 '1' 且结果要相同，那么就需要先将其它一个转换类型，待比较双方类型相同后，方可比较大小。若接触过java这种强数据类型编程语言中，大家很能理解（没少吃这种低级错误的亏☹️）。

有时数据库会自动类型转换（隐式），当无法自动转换时，会报错，或给出一些莫名其妙的提示。当然，也可以手工指定强行类型转换。若见到 cast、to_number、to_char 这类函数不要惊慌，cast 声明强制类型转换，有时在SQL查询的选择列表进行函数调用后，列的数据类型会发生变化，这时就很可能需要使用 cast 来强行类型转换。而 to_xxx这种类型的函数，更多用在带有格式化的类型转换。

数据库中的数据类型系统，与一般的编程语言所设计的数据类型是类似的。通过本节，需要自学的两项内容：

  1. 数据库有哪些数据类型
  2. 数据类型之间的转换（有隐式、显式，有时还需要指定目标格式）

有时，数据库的数据类型，还体现了功能的先进，或者在进行架构选型时，重要的参考。大部分流行的数据库，早期都原生支持 xml数据存储，随着现在JSON大规模应用，这些数据库都加入了JSON数据类型的原生支持。

数据库 数据类型官方说明
oracle 11g Data Types ，重点内容为 内置数据类型 、 数据比较规则 、 数据转换
oracle 21c Data Types ，重点内容为 内置数据类型 、 数据比较规则 、 数据转换
postgres 14 Chapter 8. Data Types ，类型非常丰富。
sqlite 3.37.0+ Datatypes In SQLite
mysql 8 Chapter 11 Data Types

数据库手册、文档 #

数据库 文档链接 备注
oracle 11g 文档库主页 在线浏览HTML或下载PDF
oracle 11g SQL手册
oracle 11g 内置函数 各类函数文档的入口
oracle 11g Database Error Messages 错误编码对应定义与解决方法
oracle 21c 文档库主页
oracle 21c SQL手册
postgres 14 文档库主页 没玩花样，sql语言、服务管理、接口、编程，以及一些内部关键部件细节，应有尽有
postgres 14 SQL手册
postgres 14 内置函数
sqlite 3 文档列表 按文章名称按序，目录结构较浅
sqlite 3 SQL文档 sqlite 中的 SQL
sqlite 3 内置函数 sqlite 中的函数
mysql 8 文档库主页 产品系列文档、管理、开发者 API 等
mysql 8 参考手册 语言、程序
hive 文档主页
hive sql 语言手册

没列出的函数文档，可以在对应的数据库产品SQL手册中找到。

数据库文档中，SQL手册是非常重要的内容，也是阅读次数最多的。其中大部分内容会使用 类BNF语法标记范式或 railroad 图来描述。

关于如何看 BNF 学习 SQL 语法，可以看另一篇文章

如何看懂SQL文档中的语法图

学习SQL，不可避免的要查阅数据库产品的SQL手册，它仅仅是数据库文档的一小部分，其它还有数据库编程开发文档，数据库管理维护文档等。而SQL手册文档中，最重要的是语法说明、各种可用函数列表等。语法手册中，通过语法标记或语法图的方式来详解语法。那么，如何看懂这类语法说明，就变成非常重要的基础能力。
↑