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

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

本文会涉及到数据库产品、数据库服务、数据库实例等概念，可看 《在开始之前》的 数据库、服务、实例傻傻分不清楚

⚠️ 文章很长，真正需要操作的很少

选择数据库产品 #

目前工作中最常接触到的数据库有 oracle、DB2、mysql，但如果仅是在个人桌面电脑、笔记本上安装练习环境，oracle、DB2都不是首选，安装过程繁琐，占用磁盘空间大、运行时相比占用更多资源（就练习个普通 group by 语句，别花我几个G内存）。

在 《thinking in sql》 系列里，除了个别功能、技巧是某数据库特有以外，有悟更想通过数据库通用的功能来分享如何编写SQL、使用SQL来解决问题，而不绑定到某款数据库产品上。这些知识主要涵盖语法、分组聚合、分析函数、递归查询、行列转换、字符串分组拼接等。

可以选择你未来可能使用的数据库产品，或者正在使用的数据库产品，或者选择有代表的。如果没有想法，建议把目光放在 mysql、postgres、sqlite 这三款数据库上，没想到有悟会推荐 sqlite 数据库这款看起来很不起眼的轻量型嵌入式数据库吧，也没见到过有谁会推荐它来做为学习的测试环境（往后看，就明白原因）。另外，有悟还考虑到环境安装的便利性，安装文件容易获取、安装步骤简单（最好绿色的，解压后就能用）、没有太多配置则可启动等。

简单好用才是硬道理。

postgres #

别被 官网上的说明 给迷惑了，以前 postgres 数据库软件介质分为 安装器 与 免安装压缩包 两个版本，安装器发布在 enterprisedb 商业网站上。

按照有悟探路最新（2022年5月7日）的情况，postgres 的安装文件只有一个 免安装压缩版，其中包含了 PostgreSQL server、pgAdmin（图形化管理工具）、StackBuilder（下载PostgreSQL增补工具、驱动的包管理器）。

我们目的是要一个简单的练习环境，其中的 postgresSQL server ，最多再要 pgAdmin 作为客户端 就足够。

下载 #

在下载地址 www.enterprisedb.com/download-postgresql-binaries 中选择对应的版本。

注意，如果你的windows 操作系统是32位的，只能使用 postgres 10 以前的版本。

若使用macos，还可以有其它的选择。

1. 到 postgres.app 网站下载方便的桌面免安装版本，它带有一个简单的启动停止控制界面，非常方便，根本感觉不到 postgres 的存在，超级好用。
2. 或者使用 brew 从命令行安装。brew install postgres。

linux 平台的朋友， download-postgresql-binaries 上只提供了 10.20 之前的版本。其它更高版本可以 使用 apt 安装 。

下载好免安装版本后，解压压缩文件，并将 解压文件位置/pgsql/bin 添加到命行的 PATH 环境变量中，我们就可以开始从命令行来创建数据库和访问数据库了。

创建数据库集群并启动 #

这里的数据库集群(database cluster)，与部署分布式数据库集群是不同的概念。 创建数据库集群(database cluster)，包括创建数据库数据所在的目录、生成共享目录表，以及创建 template1 和 postgres 数据库。postgres 数据库是提供给用户、程序使用的默认数据库。如果没有创建新的数据库，连接到postgres实例后，我们操作的数据（建表啊什么的）都位于这个数据库下。template1 是模板数据库，创建新的数据库时，会重复其中的全部内容。

关于创建数据库集群更多的内容，请看 initdb 命令 。

1. 使用命令 initdb 数据库数据保存目录创建数据库集群
2. 使用 pg_ctl -D 上面指定所创建的目录 start 启动 postgres 实例, 也可加上 -l logfile 将打印到终端的日志写到文本文件 logfile 中。

在 postgres 中，可以把存放数据的数据库看成一个目录，为了区分数据归属，可以创建多个数据库来分别存储。这些数据库都在一个 postgres 实例中。

~/youwu.today/sqllab
➜  psql --version
psql (PostgreSQL) 14.2

~/youwu.today/sqllab
➜  initdb --version
initdb (PostgreSQL) 14.2

~/youwu.today/sqllab
➜  initdb pg
The files belonging to this database system will be owned by user "macbook".
This user must also own the server process.

The database cluster will be initialized with locale "zh_CN.UTF-8".
The default database encoding has accordingly been set to "UTF8".
initdb: could not find suitable text search configuration for locale "zh_CN.UTF-8"
The default text search configuration will be set to "simple".

Data page checksums are disabled.

creating directory pg ... ok

tldr ... 🥱

Success. You can now start the database server using:

    pg_ctl -D pg -l logfile start


~/youwu.today/sqllab
➜  ls
pg

启动 postgres 服务，pg_ctl -D pg -l logfile start 。

如果 5432 端口没有占用，postgres 实例几秒之内即可启动。

连接数据库实例并测试-创建数据库 #

postgres 的 initdb 命令已经创建了一个名为 postgres 的数据库，使用命令行客户端 psql 数据库名 直接访问测试是否有效。

\d 是 psql 中的命令，列出当前数据库的表。

如果 postgres 数据库服务使用了默认 “5432” 以外的端口，使用 psql 连接到服务时需要添加端口参数，psql --port=端口 postgres。

可用 《在开始之前》中所列 客户端 ，连接到本机的postgres数据库实例，开始愉快😀的SQL之旅。

停止 postgres 数据库实例 #

命令 pg_ctl -D pg start 所启动数据库进程实例，是一个后台进程，即使关闭窗口也不退出，需要使用命令来关闭 pg_ctl -D pg stop 。

sqlite #

被严重忽视的数据库 sqlite

sqlite 是嵌入式数据库，它的安装过程与其它服务器型数据库产品不太相同，简单到令人惊讶 🙀。

下载 #

从官方网站 下载专页 下载。产品名称规则为： sqlite-product-version.zip

数据库工具对应的 product 为:

• windows，tools-win32-x86
• linux，tools-linux-x86
• macos，tools-osx-x86

version 为版本号，从下载页下载的版本就是最新（撰写本文时，版本号为 3380500，即为 3.38以上的版本）。

zip 压缩包中包含 3 个工具，sqlite3、sqldiff、sqlite3_analyzer。将这个有 sqlite3 命令的目录，添加到 PATH 环境变量中，或者将前面三个命令工具都复制、移动到某个命令行专有目录下。

若是macos的用户，系统已自带 sqlite3:

创建数据库文件 #

类似于 postgres 的 initdb，sqlite 数据库也需要创建一个文件来保存数据。只是 sqlite 数据库将数据保存为一个专有格式的数据文件。

🙀 哪个步骤是创建数据库？没看到啊。

是的，sqlite3 文件名，当文件不存在时，会自动创建。sqlite3 命令启动后，进入交互式命令行客户端。有关的说明请看 Command Line Shell For SQLite 。在 sqlite3 的交互式命令中输入 .quit，即可退出。

如果使用桌面GUI sqlite客户端，连接到 sqlite 数据库文件后，就可以像操作其他数据库一样操作 sqlite，创建、查询、修改数据都可以。

从客户端的连接界面看到，如果想创建一个新的 sqlite3 数据库，选择 new SQLite3，客户端自动在指定的路径创建一个新的数据库文件。

如果没有特殊的需求，只是想快速得到一个关系型数据库运行环境来学习SQL语法，做数据的分组聚合、统计等，sqlite 真是不二选择。数据开发领域的人实在太少提到 sqlite，貌似只有搞程序开发的人才知道。

mysql #

下载 #

从 mysql 的 下载主页 列表上选择 MySQL Community Downloads ，下载免安装版本。在下拉列表中选择对应的操作系统，下载 windows mysql-8.0.29-winx64.zip，Macos 操作系统的下载 mysql-8.0.29-macos12-x86_64.tar.gz，Linux 用户建议下载 Minimal 最小化版本 mysql-8.0.29-linux-glibc2.17-x86_64-minimal.tar.xz，这些版本均不带测试套件、调试信息（都用不着）的版本，体积比较小。

下载安装文件后解压，并将 bin 目录的路径配置到环境变量 PATH 。

Mysql 产品目录，我们只要先关心 bin 目录。

若是 macos 用户，还可以使用 brew 工具安装， brew install mysql。

配置出一个可用的MYSQL环境 #

类似 postgres initdb ，mysql 数据库需要一个目录来保存数据。在正式可以使用之前，需要先初始化数据目录，才能启动数据库。

为了方便，使用无密码登录本地mysql 数据库，用命令 mysqld --initialize-insecure --datadir=数据目录位置 初始化一个目录。

开启命令窗口，执行：

~/youwu.today/sqllab
➜  mysqld --initialize-insecure --datadir=./mysql-data
2022-05-07T08:46:58.018170Z 0 [System] [MY-013169] [Server] /Users/macbook/youwu.today/sqllab/mysql-8.0.28-macos11-x86_64/bin/mysqld (mysqld 8.0.28) initializing of server in progress as process 59936
2022-05-07T08:46:58.029248Z 0 [Warning] [MY-010159] [Server] Setting lower_case_table_names=2 because file system for /Users/macbook/youwu.today/sqllab/mysql-data/ is case insensitive
2022-05-07T08:46:58.066371Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-05-07T08:46:58.345790Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-05-07T08:46:59.556390Z 6 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
2022-05-07T08:47:00.466568Z 0 [System] [MY-013172] [Server] Received SHUTDOWN from user <via user signal>. Shutting down mysqld (Version: 8.0.28).

~/youwu.today/sqllab
➜  ls
hello.sqlite.db             mysql-8.0.28-macos11-x86_64 pg
logfile                     mysql-data

启动数据库并测试 #

命令mysqld启动数据库，使用 --datadir 指定数据目录。

~/youwu.today/sqllab
➜  ls
hello.sqlite.db             mysql-8.0.28-macos11-x86_64 pg
logfile                     mysql-data

~/youwu.today/sqllab
➜  mysqld --datadir=./mysql-data
2022-05-07T09:04:12.128623Z 0 [System] [MY-010116] [Server] /Users/macbook/youwu.today/sqllab/mysql-8.0.28-macos11-x86_64/bin/mysqld (mysqld 8.0.28) starting as process 60479

tldr ... 🥱

2022-05-07T09:04:13.624501Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /tmp/mysqlx.sock
2022-05-07T09:04:13.624534Z 0 [System] [MY-010931] [Server] /Users/macbook/youwu.today/sqllab/mysql-8.0.28-macos11-x86_64/bin/mysqld: ready for connections. Version: '8.0.28'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Server - GPL.

测试数据库实例的方式有很多，只要能连上数据库、或者本地的 mysqladmin 管理命令可以从正在运行的 mysql 实例获取信息，则表明 mysql 数据库实例正在运行。

我们通过命令行的方式来验证，下面所示方法，来自 Testing the Server

• 方法一、使用 mysqladmin 获取服务版本信息

mysqld 启动数据后，保留这个窗口，再开一个新的命令行窗口，执行 mysqladmin version 查询当前 mysql 实例的版本。

首先遇到的是用户、密码不正确的阻断。使用 mysqld --initialize-insecure 初始化时，mysql 创建了 root@localhost 空密码用户。当使用 mysqladmin version 时，实际是使用操作系统当前用户去连接服务而不是 root 用户。因此，不是密码错了，而是没使用正确用户，在命令上加 --user=root 参数即可。

• 方法二、使用 mysqlshow 查询元数据

• 方法三、使用交互式cli

postgres、sqlite 有命令行交互式 cli（psql和sqlite3本身），mysql 也有类似的工具。

或是使用 mysql --user=root 登录到本地的服务实例。

还可以使用桌面GUI客户端，连接到 mysql 数据库实例来测试。

停止 mysql 数据库服务 #

本例使用命令 mysqld --datadir=./mysql-data 启动数据库，该命令不会在后台启动 mysql 服务。当命令终端窗口关闭时，mysql 数据库服务也会停止，此种方式为暴力退出。优雅的退出方式应该是使用命令 mysqladmin --user=root shutdown 管理命令向服务发出停止指令，mysql 服务实例会依次关闭内部的各服务及进程，然后退出。

示例数据库 #

有了数据库环境，再找一些公开的数据集或者专门用来测试的数据集作为示例数据。