Navicat 博客

2019 年 7 月 30 日,由 Robert Gravelle 撰写

SQL LIMIT 子句可以约束 SELECT 语句返回的行数。对于 SQL Server 或 MS Access 等 Microsoft 数据库,可以使用 SELECT TOP 语句来限制结果,这是 Microsoft 专有的 SELECT LIMIT 语句。但是,对于大多数关系数据库(DBMS),包括 MySQL/MariaDB、PostgreSQL 和 Oracle,SQL LIMIT 子句可以解决多个问题。在今天的文章中,我们将使用 Navicat for PostgreSQL 来探索其中的一些 LIMIT 的用法。

保持结果集易于管理

在许多生产和测试数据库中,表大小通常达到数百万行并且有数十列。因此,对数据库运行 SELECT * 查询绝不是一个好主意。将结果保持在一百或一千行可以将结果集保持在更易消化的大小。

Navicat 开发和管理工具默认自动限制结果集,以防止对数据库服务器造成过度负担。当打开表时,你可以看到它的运行情况。在应用程序窗口的底部,显示 Navicat 获取表行时运行的SQL。它以“LIMIT 1000 OFFSET 0”结束,这意味着只显示前 1000 条记录。

你可以在“记录”选项屏幕上更改默认要显示的记录数或关闭限制:

最前 N 个的查询

顾名思义,最前 N 的查询是那些试图从结果集中查找最前数目的记录的查询。这可能是最前 1 、最前 3 、最前 5 、最前 10 或最前 [任意] 数字。一些常见的例子是:

  • 查找薪酬最高的前 10 名员工
  • 查找盈利最多的前 20 名客户
  • 查找系统中的前 3 个用户

仅使用 ORDER BY 和WHERE 子句而不使用 LIMIT 子句是很难处理这些查询。这是一个例子:

最前 5 个唯一的 Job ID

假设我们想在表中找到最前的唯一 Job ID。这是执行此操作的查询:

DISTINCT 关键字确保从结果中删除重复的 ID。

给定日期最接近的行

你可以使用 LIMIT 找到给定日期最接近的行。只需将行日期与给定日期进行比较,对结果进行排序,并将结果限制为你希望查看的行数。以下的查询返回 creation_date 大于 '2018-01-01' 的行:

在这种情况下,2018-01-02 是最接近的日期。

最后 N 个的查询

最前 N 个的查询的必然产物是是最后 N 个的查询。这是尝试从结果集中查找最后数目的记录的查询。我们可以很容易地将我们的“最前”查询转换为等效的“最后”查询!

最后 5 个唯一的 Job ID

若要返回最后 5 个唯一 Job ID,你需要做的就是删除 ORDER BY 子句中的 DESC 修饰符。默认情况下,这将按升序(ASC)顺序排序记录:

给定日期之前最接近的行

在给定日期之前找到最接近的行同样相当容易。我们只需要将大于 '>' 的运算符更改为小于 '<',并按降序(DESC)顺序重新排序结果:

总结

在今天的文章中,我们使用 Navicat for PostgreSQL 探索了 LIMIT 子句的一些用法。想试试 Navicat for PostgreSQL 吗?你可以完全免费试用它 14 天!

2019 年 7 月 24 日,由 Robert Gravelle 撰写

有时你可能需要获取位于同一个表中的相关数据。为此,你可以使用一种特殊的联接,称为自联接(Self Join)。在今天的文章中,我们将学习如何使用 Navicat Premium 作为数据库客户端编写包含自联接的查询。

语法

自联接的基本语法如下:

SELECT a.column_name, b.column_name...
FROM table1 a, table1 b
WHERE a.common_field = b.common_field;

除了通用字段的链接之外,WHERE 子句还可以根据你的特定要求包含其他表达式。

例子

在 Sakila 示例数据库中,有一个 customer 表,其中包含与客户相关的信息,例如他们的姓名,电子邮箱和地址。以下是 Navicat 表设计器中的列:

我们可以使用自联接来检索姓氏与其他客户的名字相匹配的所有客户。我们通过为 customer 表分配别名来实现这一目标。别名允许我们将表与自身联接,因为它们为表提供了两个唯一的名称,这意味着我们可以当它们是两个不同的表进行查询。然后以 last_name 和 first_name 字段将它们联接:

SELECT
        c1.customer_id as customer_1_id,
        c1.first_name as customer_1_first_name,
        c1.last_name as customer_1_last_name,
  c2.customer_id as customer_2_id,
        c2.first_name as customer_2_first_name,
        c2.last_name
FROM customer c1,
     customer c2
WHERE c1.last_name = c2.first_name
ORDER BY c1.last_name;

Navicat 的自动完成功能在编写查询时非常有用,因为它有助于避免拼写错误并猜测列名。因此,它对于选择字段特别有用:

运行查询会生成以下结果:

使用 INNER JOINN

将表与自身联接的另一种方法是使用 INNER JOIN。如果你不确定如何做到这一点,Navicat 可以帮助你!它提供了一个名为“查询创建工具”的有用工具,用于直观地构建查询。它允许你在不太了解 SQL 的情况下创建和编辑查询。数据库对象显示在左侧窗格中。而在右侧窗格分为两部分:上面的“图表设计”窗格和下面的“语法”窗格。

我们只需将第一个表别名的 last_name 字段拖到第二个表别名的 first_name,查询创建工具就会为我们生成联接!

这是生成的 SQL 语句

SELECT
c1.customer_id AS customer_1_id,
c1.first_name AS customer_1_first_name,
c1.last_name AS customer_1_last_name,
c2.customer_id AS customer_2_id,
c2.first_name AS customer_2_first_name,
c2.last_name
FROM
customer AS c1
INNER JOIN customer AS c2 ON c1.last_name = c2.first_name
ORDER BY
customer_1_last_name ASC
;

总结

在今天的文章中,我们学习了如何使用 Navicat Premium 编写包含自联接的查询。请尝试 Navicat Premium。你可以完全免费试用它 14 天!

2019 年 7 月 16 日,由 Robert Gravelle 撰写

即使你的公司仍然相对较小型,数据库的增長可能已经超出开始时所订的范围。当这种情况发生时,新的应用程序将与更大型、更强大的数据库连接。同时,原本的数据库仍将在业务活动中(减少)发挥作用。最终,您需要管理各种数据库,而每个数据库都有自己的功能、专用语法和连接协议。

管理多个数据库要么需要使用多个客户端应用程序,要么找到一个可以容纳你使用的所有数据库的应用程序。其中一个工具是 Navicat Premium。它不仅支持大多数主要的数据库管理系统(DBMS),而且它是可以同时连接到所有这些系统的少数工具之一!

在本篇文章中,我们将研究管理多个数据库的一些挑战,并提供如何使用 Navicat Premium 克服这些挑战的一些实际示例。

连接多个数据库

建立与多个数据库的连接并非易事,因为每个数据库产品都有自己的连接参数。例如,某些数据库需要默认数据库,而其他数据库则不需要。Navicat 通过为每种数据库类型提供一致的“连接”对话框来抚平这些差异,屏幕之间只有一些微小的变化。以下是 Windows 上 MySQL 的“新建连接”对话框和 macOS 上 SQL Server 的“新建连接”对话框的比较:

若要了解有关连接到多个数据库的更多信息,请参阅 一篇最近的文章

查询多个数据库

在 SQL 查询方面,大多数 DBMS 支持一组标准化的 SQL 语句和函数。除此之外,许多数据库供应商试图通过包含一组额外的扩展 SQL 功能来将其产品区分开来。例如,数据透视表是一个统计表,它汇总了(例如数据库,电子表格或商业智能程序中)更广泛的表的数据。此摘要可能包括总和、平均值或其他统计信息,数据透视表以有意义的方式将这些信息组合在一起。

对于数据透视表的数据库支持在各 DBMS 之间差异很大,如下所述:

  • PostgreSQL 是一种对象关系数据库管理系统,允许使用 tablefunc 模块创建数据透视表。
  • MariaDB 是一个 MySQL 分支,允许使用 CONNECT 存储引擎的数据透视表。
  • Microsoft Access 支持名为“交叉表”查询的数据透视查询。
  • Oracle 数据库和 SQL Server 支持 PIVOT 操作。
  • 一些不直接支持数据透视功能的流行数据库,例如 SQLite,通常可以使用嵌入式函数、动态 SQL 或子查询来模拟数据透视功能。

在 Navicat 中,你可以使用一个语句查询多个数据库,只要你可以用两个公共字段联接各个表,并且查询中的所有数据库都支持该语法:

这里有一篇关于查询多个数据库的文章。

总结

在今天的文章中,我们研究了管理多个数据库的一些挑战,并回顾了如何使用 Navicat Premium 克服这些挑战的一些实际示例。

Navicat Premium 适用于 Windows、macOS 和 Linux 操作系统,并支持 MySQL、MariaDB、MongoDB、SQL Server、Oracle、PostgreSQL 和 SQLite 数据库。它还兼容 Amazon RDS、Amazon Aurora、Amazon Redshift、Microsoft Azure、Oracle Cloud、Google Cloud 和 MongoDB Atlas 等云数据库。今天试试吧!

2019 年 6 月 19 日,由 Robert Gravelle 撰写

不久前,我们探索了 你必须知道的一些 SELECT 查询。其中包括确定列的最小值或最大值,以及按类别对结果进行分组。今天的文章将介绍更多查询,以及几乎是自动编写查询的一个技巧!

获取所有用户创建的表

这些包括属于用户创建的数据库的表,也就是说,它们不是系统数据库模式的一部分。确切的语法因供应商而异,但这里有几个示例可让你有一个概念。

在 SQL Server 中,这一行简单的语句就可完成工作:

SELECT NAME FROM sys.objects WHERE TYPE='U'

MySQL 的语法有点冗长,因为你必须指定系统数据库才能省略它们的表:

SELECT * from information_schema.tables
WHERE table_schema not in ('information_schema', 'mysql', 'performance_schema')
ORDER BY table_schema, table_name;

那为什么要查询用户表呢?除了表名,MySQL 查询还返回有关每个表的大量有用信息,包括行数、存储引擎、它们的大小、最后一个 auto_increment 值等等!

如果你只想要在 MySQL 中的表名,那就很容易了。你可以使用 WHERE 子句缩窄列表范围,或者,你可以发出以下命令:

SHOW FULL TABLES IN [database_name] WHERE TABLE_TYPE LIKE 'BASE TABLE';

获取所有视图名

同样,确切的语法因供应商而异,但是这几个例子将提供一个良很的起点。

这是 SQL Server 的语法:

SELECT * FROM sys.views

在 MySQL 中,我们可以通过将 TABLE_TYPE 限制为 'VIEW' 来将列表缩窄到视图。我们仍然必须排除 sys 数据库,因为它包含许多视图:

SELECT * FROM information_schema.`TABLES`
WHERE TABLE_TYPE = 'VIEW'
AND table_schema != 'sys';

以下是在 Navicat Premium 的结果:

要寻找特定数据库的视图?你只需将 WHERE 子句更改为:

AND TABLE_SCHEMA LIKE '[database_name]'

以下命令也可以做到:

SHOW FULL TABLES IN [database_name] WHERE TABLE_TYPE LIKE 'VIEW';

这将返回视图名称及其类型“VIEW”:

温馨提示:使用表别名

编写 SQL 查询既是一门艺术,也是一门科学。你可以养成一些良好的习惯,这些习惯会在生产力和/或写作的简易性方面带来好处。例如,表(或SQL)别名用于为表或表中的列提供仅在查询期间存在的临时名称。可以使用别名来使列名更具可读性并且更不容易出错。

你需要做的就是在 FROM 子句中的表名后面加上“AS [alias_name]”:

SELECT column_name(s)
FROM table_name AS alias_name;

当你使用像 Navicat 这样的查询编辑器时,别名真的很值得使用。假设我们要从 actor 表中选择一些字段。首先,我们将列列表留空,然后输入 FROM 子句,并使用表别名:

SELECT

FROM actor as a

现在,当我们输入较短的表别名时,Navicat 会显示一个包含所有表列的自动完成列表:

以这种方式编写查询不仅快速,而且还消除了列名拼写错误的可能性!

总结

在今天的文章中,我们学习了几个查询和技巧,使用 Navicat PremiumNavicat Premium 作为数据库客户端使编写 SELECT 语句几乎全自动。Navicat 的自动完成代码和自定义的代码段功能透过关键字建议和减少重复输入相同的代码,令编码更加快速。你可以免费试用 14 天进行评估。

2019 年 6 月 12 日,由 Robert Gravelle 撰写

许多数据库管理和开发工具支持连接同构的多个数据库,即它们都是相同类型的例如全部都是 MySQL、全部都是 SQL Server、全部都是 Oracle 等。另一方面,很少有支持连接异构数据库服务器,即 MySQL 和SQL Server 和 Oracle 等。不相信我?可以从互联网搜索一下!

Navicat 文章
频道条目
分享
文章归档