超级会员免费看
SQL数据库操作入门指南
1. 数据准备
首先，解压
.tgz
文件后会生成一个
playground
目录，其中包含数据集、示例代码和实用工具。我们要处理的数据集是关于 Ubuntu 18.04 系统上已安装软件包的信息，涵盖软件包名称、简短描述、所含文件列表以及文件大小。
数据集中的文件为
.tsv
格式，即制表符分隔值文件。具体有两个文件：
-
package_descriptions.tsv
：包含两列数据，分别是软件包名称和软件包描述。
-
package_files.txv
：包含三列数据，分别是软件包名称、软件包安装的文件名称以及文件大小。
2. 启动 SQLite
启动 SQLite 很简单，只需在命令行输入
sqlite3
，还可以选择性地指定一个用于存储数据库表的文件名。若不指定文件名，SQLite 会在内存中创建一个临时数据库。示例如下：
me@linuxbox:~/advemture-sql$ sqlite3
SQLite version 3.22.0 2018-01-22 18:45:57
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>
加载完成后，SQLite 会显示一个提示符，我们可以在此输入命令。命令可以是 SQL 语句，也可以是用于控制 SQLite 本身的点命令。输入
.help
可查看可用点命令列表，输入
.quit
则可退出 SQLite。
虽然我们可以通过
sqlite>
提示符直接与
sqlite3
程序交互，但
sqlite3
也能通过标准输入接收点命令和 SQL 语句流，这也是 SQLite 最常用的使用方式。
3. 创建表并插入数据
要开始使用数据库，需先将
.tsv
文件转换为 SQL 语句流。我们的数据库最初包含两个表：
Package_Descriptions
和
Package_Files
。
为创建
Package_Descriptions
表的 SQL 流，可使用
playground
存档中提供的
insert_Package_Descriptions.awk
程序：
me@linuxbox:~/advemture-sql$ ./insert_Package_Descriptions.awk \
< package_descriptions.tsv > insert_Package_Descriptions.sql
查看生成的 SQL 流的前几行和后几行：
me@linuxbox:~/advemture-sql$ head insert_Package_Descriptions.sql
DROP TABLE IF EXISTS Package_Descriptions;
CREATE TABLE Package_Descriptions (
package_name VARCHAR(60),
description VARCHAR(120)
);
BEGIN TRANSACTION;
INSERT INTO Package_Descriptions
VALUES ( 'a2ps',
'GNU a2ps - ''Anything to PostScript'' converter and pretty-printer');
INSERT INTO Package_Descriptions
VALUES ( 'accountsservice',
'query and manipulate user account information');
me@linuxbox:~/advemture-sql$ tail insert_Package_Descriptions.sql
VALUES ( 'zlib1g:amd64',
'compression library - runtime');
INSERT INTO Package_Descriptions
VALUES ( 'zlib1g:i386',
'compression library - runtime');
INSERT INTO Package_Descriptions
VALUES ( 'zlib1g-dev:amd64',
'compression library - development');
INSERT INTO Package_Descriptions
VALUES ( 'zsh',
'shell with lots of features');
INSERT INTO Package_Descriptions
VALUES ( 'zsh-common',
'architecture independent files for Zsh');
COMMIT;
从上述代码可以看出，SQL 较为冗长且类似英语。通常，语言关键字使用大写，但并非强制要求，因为 SQL 不区分大小写。空格虽不重要，但常用于使 SQL 语句更易读。语句可以跨多行，以分号结尾。
SQL 支持两种注释形式：
- 单行注释：以两个连字符
--
开头。
- 多行注释：采用 C 语言风格，使用
/* */
。
需要注意的是，虽然有 SQL 的 ANSI 标准，但每个数据库服务器实现 SQL 的方式不同，各有略微不同的方言。SQLite 支持大部分标准 SQL，但并非全部，还添加了一些独特功能。
4. 创建和删除表
SQL 流的前两行会删除任何现有的
Package_Descriptions
表，并创建一个新的同名表。
DROP TABLE
语句用于删除表，可选的
IF EXISTS
子句可防止在表不存在时出错。
CREATE TABLE
语句用于定义新表，此表有两列，第一列
package_name
定义为最长 60 个字符的可变长度字符串。
常见的 SQL 数据库支持的数据类型如下：
| 数据类型 | 描述 |
| ---- | ---- |
| INTEGER | 整数 |
| CHAR(n) | 固定长度字符串 |
| VARCHAR(n) | 可变长度字符串 |
| NUMERIC | 十进制数 |
| REAL | 浮点数 |
| DATETIME | 日期和时间值 |
SQLite 支持的通用数据存储类型有：
| 数据类型 | 描述 |
| ---- | ---- |
| INTEGER | 根据需要使用 1、2、3、4、6 或 8 字节的有符号整数 |
| REAL | 8 字节 IEEE 浮点数 |
| TEXT | 文本字符串 |
| BLOB | 二进制大对象（如 JPEG 或 MP3 文件） |
在上述示例中，我们为列指定了
VARCHAR
数据类型，SQLite 会将这些列的值存储为
TEXT
，并忽略数据类型规范中设置的长度限制。SQLite 对数据类型非常宽松，甚至允许在单个列中混合不同的数据类型，但这与其他数据库完全不兼容，在后续操作中我们应遵循传统数据类型和行为。
5. 插入数据
SQL 流的大部分内容是
INSERT
语句，用于向表中添加行。实际上，
INSERT
语句是将行追加到表中。
为提高性能，我们使用
BEGIN TRANSACTION
和
COMMIT
包围
INSERT
语句。若不这样做，每行插入都会被视为一个单独的事务，会大大增加插入大量行所需的时间。而且，在收到
COMMIT
语句之前，SQL 不会将事务应用到数据库，因此可以编写 SQL 代码，在出现问题时放弃事务并回滚更改，使数据库保持不变。
执行以下命令创建第一个表并添加软件包名称和描述：
me@linuxbox:~/advemture-sql$ sqlite3 adv-sql.sqlite \
< insert_Package_Descriptions.sql
6. 执行查询
现在我们有了一个数据库，接下来启动
sqlite3
并与之交互：
me@linuxbox:~/advemture-sql$ sqlite3 adv-sql.sqlite
SQLite version 3.22.0 2018-01-22 18:45:57
Enter ".help" for usage hints.
sqlite>
首先使用 SQLite 点命令查看数据库结构：
sqlite> .tables
Package_Descriptions
sqlite> .schema Package_Descriptions
CREATE TABLE Package_Descriptions (
package_name VARCHAR(60),
description VARCHAR(120)
);
.tables
点命令显示数据库中的表列表，
.schema
点命令列出用于创建指定表的语句。
接下来使用
SELECT
语句进行查询，这是最常用的 SQL 语句之一：
sqlite> SELECT * FROM Package_Descriptions;
a2ps|GNU a2ps - 'Anything to PostScript' converter and pretty-printer
accountsservice|query and manipulate user account information
acl|Access control list utilities
acpi-support|scripts for handling many ACPI events
acpid|Advanced Configuration and Power Interface event daemon
adduser|add and remove users and groups
adium-theme-ubuntu|Adium message style for Ubuntu
adwaita-icon-theme|default icon theme of GNOME (small subset)
aisleriot|GNOME solitaire card game collection
alsa-base|ALSA driver configuration files
...
这是
SELECT
语句的最简单形式，语法为
SELECT
后跟所需的列或计算值列表，以及
FROM
子句指定数据来源。
*
表示所有列，也可以显式指定列名：
sqlite> SELECT package_name, description FROM Package_Descriptions;
7. 控制输出
默认输出格式适合用
awk
等工具进一步处理，但对人类阅读不太友好。可以使用点命令调整输出格式，同时添加
LIMIT
子句限制输出行数：
sqlite> .headers on
sqlite> .mode column
sqlite> SELECT * FROM Package_Descriptions LIMIT 10;
package_name description
------------ ----------------------------------------------------------------
a2ps GNU a2ps - 'Anything to PostScript' converter and pretty-printer
accountsserv query and manipulate user account information
acl Access control list utilities
acpi-support scripts for handling many ACPI events
acpid Advanced Configuration and Power Interface event daemon
adduser add and remove users and groups
adium-theme- Adium message style for Ubuntu
adwaita-icon default icon theme of GNOME (small subset)
aisleriot GNOME solitaire card game collection
alsa-base ALSA driver configuration files
.headers on
命令添加列标题，
.mode column
命令将输出改为列格式，这些设置会一直生效，直到更改。
.mode
点命令还有其他有趣的设置：
| 模式 | 描述 |
| ---- | ---- |
| csv | 逗号分隔值 |
| column | 左对齐列，可使用
.width n1 n2…
设置列宽 |
| html | HTML
<table>
代码 |
| insert | 表的 SQL 插入语句 |
| line | 每行一个值 |
| list | 由
.separator
字符串分隔的值，这是默认模式 |
| tabs | 制表符分隔值 |
| tcl | TCL（工具控制语言）列表元素 |
设置模式和列宽后查询：
sqlite> .mode column
sqlite> .width 20 60
sqlite> SELECT * FROM Package_Descriptions LIMIT 10;
package_name description
-------------------- --------------------------------------------------------
a2ps GNU a2ps - 'Anything to PostScript' converter and pretty
accountsservice query and manipulate user account information
acl Access control list utilities
acpi-support scripts for handling many ACPI events
acpid Advanced Configuration and Power Interface event daemon
adduser add and remove users and groups
adium-theme-ubuntu Adium message style for Ubuntu
adwaita-icon-theme default icon theme of GNOME (small subset)
aisleriot GNOME solitaire card game collection
alsa-base ALSA driver configuration files
除了列出列，
SELECT
语句还可用于执行各种输出任务，如计算
Package_Descriptions
表中的行数：
sqlite> SELECT COUNT(package_name) FROM Package_Descriptions;
count(package_name)
--------------------
1972
8. 选择性查询
可以根据某些选择条件让
SELECT
输出特定行，支持精确匹配和使用 SQL 通配符的部分匹配：
-- 精确匹配
sqlite> SELECT * FROM Package_Descriptions WHERE package_name = 'bash';
package_name description
-------------------- --------------------------------------------------------
bash GNU Bourne Again SHell -- 部分匹配
sqlite> SELECT * FROM Package_Descriptions WHERE description LIKE '%bash%';
package_name description
-------------------- --------------------------------------------------------
bash-completion programmable completion for the bash shell
command-not-found Suggest installation of packages in interactive bash ses
SQL 支持两个通配符：下划线
_
匹配任何单个字符，百分号
%
匹配零个或多个任意字符。注意，字符串要用单引号包围，SQL 会将这种方式引用的值视为字符串。
9. 排序输出
若不指定排序，
SELECT
输出的数据将按插入表的顺序列出。可以使用
ORDER BY
子句指定用于排序的列，默认排序顺序为升序，可在列名后添加
DESC
实现降序排序，也可指定多个列并使用
ASC
指定升序：
-- 按描述升序排序
sqlite> SELECT * FROM Package_Descriptions ORDER BY description LIMIT 10;
package_name description
-------------------- -------------------------------------------------------
network-manager-conf
fonts-noto-cjk "No Tofu" font families with large Unicode coverage (CJ
fonts-noto-mono "No Tofu" monospaced font family with large Unicode cov
udev /dev/ and hotplug management daemon
procps /proc file system utilities
alsa-base ALSA driver configuration files
libasound2-plugins:a ALSA library additional plugins
libhyphen0:amd64 ALTLinux hyphenation library - shared library
apcupsd APC UPS Power Management (daemon)
apcupsd-doc APC UPS Power Management (documentation/examples)
-- 按描述降序排序
sqlite> SELECT * FROM Package_Descriptions ORDER BY description DESC LIMIT 10;
package_name description
------------ --------------------------------------------------
xsane-common xsane architecture independent files
libx264-152: x264 video coding library
libevdev2:am wrapper library for evdev devices
wireless-reg wireless regulatory database
crda wireless Central Regulatory Domain Agent
libmutter-2- window manager library from the Mutter window mana
libwayland-s wayland compositor infrastructure - server library
libwayland-c wayland compositor infrastructure - cursor library
libwayland-c wayland compositor infrastructure - client library
libwayland-e wayland compositor infrastructure - EGL library
10. 添加另一个表
为了展示
SELECT
语句更多的功能，我们需要一个更大的数据库。可以添加第二个
.tsv
文件，将其过滤为 SQL 并直接通过管道输入到
sqlite3
：
me@linuxbox:~/advemture-sql$ ./insert_package_files.awk \
< package_files-deb.tsv \
| sqlite3 adv-sql.sqlite
第二个表名为
Package_Files
，其结构如下：
sqlite> .schema Package_Files
CREATE TABLE Package_Files (
package_name VARCHAR(60),
file VARCHAR(120),
size_bytes INTEGER
);
该表有三列，分别是软件包名称、软件包安装的文件名称以及文件大小。进行查询查看该表的工作方式：
sqlite> .headers on
sqlite> .mode column
sqlite> .width 15 50 -10
sqlite> SELECT * FROM Package_Files WHERE package_name = 'bash';
package_name file size_bytes
--------------- ------------------------------------------------- ----------
bash /bin/bash 1113504
bash /etc/bash.bashrc 2319
bash /etc/skel/.bash_logout 220
bash /etc/skel/.bashrc 3771
bash /etc/skel/.profile 807
bash /usr/bin/bashbug 7115
bash /usr/bin/clear_console 10312
bash /usr/share/doc/bash/COMPAT.gz 7853
bash /usr/share/doc/bash/INTRO.gz 2921
bash /usr/share/doc/bash/NEWS.gz 27983
bash /usr/share/doc/bash/POSIX.gz 3702
bash /usr/share/doc/bash/RBASH 1693
bash /usr/share/doc/bash/README 3839
bash /usr/share/doc/bash/README.Debian.gz 1919
bash /usr/share/doc/bash/README.abs-guide 1105
bash /usr/share/doc/bash/README.commands.gz 3021
bash /usr/share/doc/bash/changelog.Debian.gz 1357
bash /usr/share/doc/bash/copyright 10231
bash /usr/share/doc/bash/inputrc.arrows 727
bash /usr/share/lintian/overrides/bash 156
bash /usr/share/man/man1/bash.1.gz 86656
bash /usr/share/man/man1/bashbug.1.gz 804
bash /usr/share/man/man1/clear_console.1.gz 1194
bash /usr/share/man/man1/rbash.1.gz 154
bash /usr/share/man/man7/bash-builtins.7.gz 508
bash /usr/share/menu/bash 194
bash /bin/rbash 4
注意
.width
点命令，负值会使相应列右对齐。
11. 子查询
SELECT
语句可用于产生多种输出，例如打印文字字符串、进行计算等。为使复杂表达式更易读，可使用
AS
子句为结果分配别名：
-- 打印文字字符串
sqlite> .mode column
sqlite> .header off
sqlite> SELECT 'String 1', 'String 2';
string 1 string 2
-- 进行计算
sqlite> .header on
sqlite> SELECT 2 + 2;
2 + 2
--------------------
4
-- 计算列的数量和平均值并使用别名
sqlite> SELECT COUNT(file) AS Files,
...> AVG(size_bytes) AS 'Average Size'
...> FROM Package_Files;
Files Average Size
---------- ----------------
153506 33370.3488658424
SELECT
语句的一个重要特性是能够通过组合多个表的数据产生结果，这可通过执行连接和子查询来实现。下面通过一个子查询示例展示如何生成包含软件包名称、软件包中的文件数量以及软件包总大小的表。
创建
subquery_demo1.sql
文件，内容如下：
-- subquery_demo1.sql
-- Query to list top 20 packages with the greatest numbers of files
.mode column
.header on
.width 20 40 10 10
SELECT package_name, description,
(SELECT COUNT(file)
FROM Package_Files
WHERE Package_Descriptions.package_name = Package_Files.package_name)
AS files,
(SELECT SUM(size_bytes)
FROM Package_Files
WHERE Package_Descriptions.package_name = Package_Files.package_name)
AS size
FROM Package_Descriptions ORDER BY files DESC LIMIT 20;
执行该查询：
me@linuxbox:~/adventure-sql$ sqlite3 adv-sql.sqlite < subquery_demo1.sql
package_name description files size
-------------------- -------------------------------- ---------- ----------
linux-headers-4.15.0 Header files related to Linux ke 14849 63991787
linux-headers-4.15.0 Header files related to Linux ke 14849 64001943
humanity-icon-theme Humanity Icon theme 8014 14213715
linux-headers-4.15.0 Linux kernel headers for version 7861 9015084
linux-headers-4.15.0 Linux kernel headers for version 7860 9025673
linux-modules-extra- Linux kernel extra modules for v 4173 165921470
linux-modules-extra- Linux kernel extra modules for v 4172 165884678
libreoffice-common office productivity suite -- arc 3551 76686149
gnome-accessibility- High Contrast GTK+ 2 theme and i 3464 3713621
ubuntu-mono Ubuntu Mono Icon theme 3025 3755093
ncurses-term additional terminal type definit 2727 1987483
manpages-dev Manual pages about using GNU/Lin 2101 2192620
linux-firmware Firmware for Linux kernel driver 1938 331497257
tzdata time zone and daylight-saving ti 1834 1210058
vim-runtime Vi IMproved - Runtime files 1642 27941732
codium Code editing. Redefined. 1307 271907088
zsh-common architecture independent files f 1256 12261077
perl-modules-5.26 Core Perl modules 1144 18015966
adwaita-icon-theme default icon theme of GNOME (sma 1127 4848678
gimp-data Data files for GIMP 1032 45011675
该查询会花费一些时间，结果包含四列：软件包名称、描述、软件包中的文件数量以及软件包总大小。
子查询也可用于
WHERE
子句，例如创建
subquery_demo2.sql
文件：
-- subquery_demo2.sql
-- Query to list all packages containing more than 1000 files
.mode column
.header on
.width 20 60
SELECT package_name, description
FROM Package_Descriptions
WHERE 1000 < (SELECT COUNT(file)
FROM Package_Files
通过以上步骤，我们完成了从数据准备到数据库创建、表操作、数据插入、查询等一系列 SQL 数据库操作，对 SQL 的基本使用有了较为全面的了解。在实际应用中，可以根据具体需求灵活运用这些知识进行更复杂的数据库操作。
SQL数据库操作入门指南
12. 子查询的深入剖析
上面的子查询示例
subquery_demo1.sql
看起来较为复杂，下面我们详细剖析它是如何工作的。在最上层，该查询遵循
SELECT
语句的常规模式：
SELECT list_of_items
FROM Package_Descriptions
ORDER BY files DESC
LIMIT 20;
基本结构简单明了，关键在于
list_of_items
部分。这是一个用逗号分隔的输出项列表，具体如下：
1.
package_name
：软件包名称列。
2.
description
：软件包描述列。
3.
(SELECT COUNT(file) FROM Package_Files WHERE Package_Descriptions.package_name = Package_Files.package_name) AS files
：这是一个子查询，用于统计每个软件包中的文件数量，并将结果命名为
files
。
4.
(SELECT SUM(size_bytes) FROM Package_Files WHERE Package_Descriptions.package_name = Package_Files.package_name) AS size
：同样是子查询，用于计算每个软件包中所有文件的总大小，并将结果命名为
size
。
通过这种方式，我们将
Package_Descriptions
表和
Package_Files
表的数据进行了关联和整合，从而得到所需的输出。
13. 子查询在 WHERE 子句中的应用
在
subquery_demo2.sql
中，子查询用于
WHERE
子句，以筛选出文件数量超过 1000 的软件包。完整的查询语句如下：
-- subquery_demo2.sql
-- Query to list all packages containing more than 1000 files
.mode column
.header on
.width 20 60
SELECT package_name, description
FROM Package_Descriptions
WHERE 1000 < (SELECT COUNT(file)
FROM Package_Files
WHERE Package_Descriptions.package_name = Package_Files.package_name);
执行该查询时，SQL 会先执行子查询，计算每个软件包的文件数量，然后将结果与 1000 进行比较，只返回文件数量大于 1000 的软件包的名称和描述。
14. 总结与操作流程回顾
通过前面的学习，我们掌握了 SQL 数据库操作的一系列基本技能。下面是整个操作流程的回顾和总结：
14.1 数据准备
-
解压
.tgz文件，获取包含数据集、示例代码和工具的playground目录。 -
数据集为
.tsv文件，包括package_descriptions.tsv和package_files.txv。
14.2 启动 SQLite
-
输入
sqlite3命令启动，可选择性指定数据库文件名。 -
使用
.help查看可用点命令，.quit退出。
14.3 创建表并插入数据
-
使用
insert_Package_Descriptions.awk程序将.tsv文件转换为 SQL 语句流。 -
执行 SQL 流创建
Package_Descriptions表并插入数据。
14.4 创建和删除表
-
使用
DROP TABLE和CREATE TABLE语句进行表的删除和创建。 - 了解常见 SQL 数据类型和 SQLite 支持的数据存储类型。
14.5 插入数据
-
使用
INSERT语句向表中添加行，用BEGIN TRANSACTION和COMMIT提高性能。
14.6 执行查询
-
使用
.tables和.schema点命令查看数据库结构。 -
使用
SELECT语句进行基本查询。
14.7 控制输出
-
使用
.headers和.mode点命令调整输出格式。 -
使用
LIMIT子句限制输出行数。
14.8 选择性查询
-
使用
WHERE子句进行精确匹配和部分匹配查询。 -
了解 SQL 通配符
_和%的使用。
14.9 排序输出
-
使用
ORDER BY子句指定排序列，可设置升序或降序。
14.10 添加另一个表
-
将第二个
.tsv文件转换为 SQL 并输入到sqlite3创建Package_Files表。
14.11 子查询
-
使用
SELECT语句进行子查询，组合多个表的数据。 -
在
WHERE子句中使用子查询进行筛选。
15. 操作流程图
graph TD;
A[数据准备] --> B[启动 SQLite];
B --> C[创建表并插入数据];
C --> D[创建和删除表];
D --> E[插入数据];
E --> F[执行查询];
F --> G[控制输出];
G --> H[选择性查询];
H --> I[排序输出];
I --> J[添加另一个表];
J --> K[子查询];
16. 注意事项
- SQL 虽然有 ANSI 标准，但不同数据库服务器实现方式不同，SQLite 有其独特之处。
- SQLite 对数据类型较为宽松，但在实际应用中应遵循传统数据类型和行为。
- 字符串要用单引号包围，SQL 会将其视为字符串。
- 子查询可能会影响查询性能，尤其是涉及大量数据时，需要谨慎使用。
通过以上内容，我们对 SQL 数据库操作有了全面的了解，从数据准备到复杂查询，掌握了一系列实用技能。在实际应用中，可以根据具体需求灵活运用这些知识，进行更高效、更复杂的数据库操作。
扫一扫
10万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
