sql vs nosql_初学者参考sql vs nosql

sql vs nosql

Many new developers wonder what the difference is between SQL and NoSQL. The subjects come up often in theoretical discussions and systems design interview preparation.

许多新开发人员想知道SQL和NoSQL之间的区别是什么。 这些主题经常出现在理论讨论和系统设计面试准备中。

In this tutorial, we will be looking at the difference between SQL and NoSQL based on certain parameters. Before we begin, let’s analyze each individually to develop our understanding of these contrasting concepts.

在本教程中，我们将基于某些参数研究SQL和NoSQL之间的区别。 在开始之前，让我们分别分析每个，以加深我们对这些不同概念的理解。

This lesson was originally published at https://algodaily.com, where I maintain a technical interview course and write think-pieces for ambitious developers.

本课程最初在 https://algodaily.com上 发布 ，我 在 那里维护技术面试课程，并为雄心勃勃的开发人员撰写思想著作。

什么是SQL？ (What is SQL?)

Some definitions to start:

开始的一些定义：

SQL stands for Structured Query Language. As evident from the name, it is a querying language that is used to perform various data operations. It is supported on almost all Relational Databases.

SQL代表结构化查询语言 。 从名称可以明显看出，它是一种用于执行各种数据操作的查询语言。 几乎所有关系数据库都支持它。

And what is a Relational Database? A relational database, often synonymous as a SQL Database, is a collection of data records that may have predefined associations or relationships with each other.

什么是关系数据库？ 关系数据库通常是SQL数据库的同义词，它是数据记录的集合，这些数据记录之间可能具有预定义的关联或关系。

By extension, a Relational Database Management System (RDMS) is an interface (application or UI) to manage the records, usually by way of SQL. It's used to store, edit, read, and write such data in the form of tables. It's also often visually shown as a spreadsheet-like shape.

通过扩展， 关系数据库管理系统(RDMS)是通常通过SQL来管理记录的接口(应用程序或UI)。 它用于以表的形式存储，编辑，读取和写入此类数据。 它也经常在视觉上显示为类似于电子表格的形状。

SQL数据库 (SQL Databases)

SQL databases, or Relational Databases, employ the traditional way of storing data (where each database may contain several tables). However, it is worth mentioning that in RDBs, each table contains rows that have the same attributes or "shape". As an example, in a "restaurant" database, you may have one table for customers, another for orders, and yet another for restaurant locations.

SQL数据库或Relational Database采用传统的数据存储方式(每个数据库可能包含多个表)。 但是，值得一提的是，在RDB ，每个表都包含具有相同属性或“形状”的行。 例如，在“餐厅”数据库中，您可能有一个用于customers表，另一个用于orders ，以及另一个用于餐厅locations 。

Now here comes the importance of columns. Each column describes one specific piece of information about the record. The type of data that each column will contain is also predefined. For example, in a customer table, one column may contain the customer id (an integer type column). Another may show the customer order id (another integer), and a third may contain information about customer address (varchar).

现在来谈谈专栏的重要性 。 每一列描述有关记录的一条特定信息。 每列将包含的数据类型也是预定义的。 例如，在customer表中，一列可能包含客户id ( integer类型列)。 另一个可能显示客户订单id (另一个integer )，第三个可能包含有关客户地址的信息( varchar )。

Properly defining tables and columns is upfront work to be done to gainfully use a database. Then, the day to day task becomes adding new rows. This is where the SQL language comes in handy. It offers a convenient way to create tables, define columns, add records, delete them, etc. It also allows us to "connect" or associate (create relationships for) multiple tables by using the concept of primary and foreign keys.

正确定义表和列是为有收益地使用数据库而要做的前期工作。 然后，日常任务将添加新行。 这是SQL语言派上用场的地方。 它提供了一种方便的方法来创建表，定义列，添加记录，删除它们等。它还允许我们使用primary键和foreign键的概念“连接”或associate (为多个表创建关系)。

SQL DB的类型 (Types of SQL DBs)

Here’s a selection of the various SQL Databases.

这里是各种SQL数据库的选择。

什么是NoSQL？ (What is NoSQL?)

Let’s move on to the definition of NoSQL. Several NoSQL database systems have grown rapidly in popularity over the last few years. They are non-relational DMSs that support structured data, unstructured, semi-structured, and polymorphic data.

让我们继续进行NoSQL的定义。 在过去的几年中，一些NoSQL数据库系统已经Swift普及。 它们是非关系DMS ，支持结构化数据，非结构化，半结构化和多态数据。

At a high level, NoSQL just means not only SQL. Thus, it doesn’t speak to a single database offering — rather, it’s a collection of diverse technologies. Don’t assume that the databases are similar in nature either.

在较高的层次上，NoSQL不仅意味着SQL 。 因此，它并不是针对单个数据库产品，而是多种技术的集合。 也不要假设数据库本质上是相似的。

As stated, there are no official rules as to what makes a database NoSQL. The term describes more about what they aren’t.

如前所述，关于什么使数据库NoSQL没有正式的规则。 这个词更多地描述了它们不是 。

NoSQL Databases:

NoSQL数据库：

• typically don’t use SQL
  通常不使用SQL
• usually don’t store data in tables
  通常不将数据存储在表中
• usually don’t care about relationships
  通常不关心人际关系
• usually don’t provide ACID transaction
  通常不提供ACID交易
• usually don’t require formal data schemas (making them more flexible)
  通常不需要正式的数据模式(使它们更灵活)

NoSQL技术的类型 (Types of NoSQL Technologies)

Let’s run through a list of data store offerings within the NoSQL flavor.

让我们遍历NoSQL风格的数据存储产品列表。

1. Document DB/Document Stores: These are organized around the idea that the fundamental thing to store is a self-contained piece of data (called a document). Such a document describes its own schema-- this is in opposition to individual rows of data constrained in well-defined columns. With documents, there generally are no restrictions in shape or structure. It may be in XML format, but often it's JSON-- a loose structure based on plain old Javascript objects.

   文档数据库/文档存储：这些文档的组织是基于这样的思想，即存储的基本内容是一条独立的数据(称为document )。 这样的文档描述了自己的模式，这与约束在定义明确的列中的数据的各个行相反。 对于document ，通常在形状或结构上没有限制。 它可能是XML格式，但通常是JSON基于普通旧Javascript对象的松散结构。

Example: store a simple piece of data consisting of two pieces of information: title and rating. The title is a string and rating is an integer.

示例：存储由两条信息组成的简单数据： title和rating 。 title是字符串， rating是整数。

In the next piece of data, we have different descriptions and utilize nesting of information.

在下一个数据中，我们有不同的描述并利用信息nesting 。

When we are schema-less, we can simply add new documents in this data store freely. The only constraint is that each document will be given a unique id. But beyond that, you often have total flexibility. There's no need to provide a formal schema and no need to define relationships. One downfall of this is that the database needs to provide a more flexible way of querying the data-- thus arrived solutions like CouchDB and MongoDB.

当我们没有模式时，我们可以简单地在该数据存储中自由添加新文档。 唯一的限制是每个document将被赋予唯一的id 。 但是除此之外，您通常具有完全的灵活性。 无需提供正式的架构，也无需定义关系。 这样做的一个缺点是数据库需要提供一种更灵活的查询数据的方式，因此就出现了CouchDB和MongoDB类的解决方案。

2. Key-value databases are another category. Its main emphasis is on having no predefined schema for your data. All it does is store and retrieve everything based on key-value pairs. In some ways, it’s like a two-column table (if you had a key column and value column), and nothing more is enforced. It could be any data type. You could fit in bits of XML or JSON.

2. 键值数据库是另一类。 它的主要重点是没有预定义的数据架构 。 它所做的只是根据键值对存储和检索所有内容。 在某些方面，它就像一个两列的表(如果您有key列和value列)，则无需执行其他任何操作。 它可以是任何数据类型。 您可以放入XML或JSON位。

Furthermore, It is worth mentioning that many products in this category are specifically designed with fault-tolerant distributed architecture. Simplified, this means you can easily install them across multiple machines. As such, no one machine is a point of failure — the database can survive machine failures and continue functioning.

此外，值得一提的是，该类别中的许多产品都是采用容错分布式体系结构专门设计的。 简化后，这意味着您可以轻松地在多台计算机上安装它们。 因此，没有一台机器是故障点-数据库可以在机器故障中幸存下来并继续运行。

3. Graph: Yet another category of NoSQL tech. It is a data store in which everything is kept in the form of connecting nodes, in a graph structure. There is no one "master" point. Although many other NoSQL DBMS tend to de-emphasize relationships, graph databases are all about having nodes of data connected to each other, describing relationships among them.

3. 图： NoSQL技术的另一类。 它是一个数据存储，其中所有数据都以graph结构形式以连接节点的形式保存。 没有一个“主”点。 尽管许多其他NoSQL DBMS倾向于不强调关系，但是graph database都是关于使数据节点相互连接，以描述它们之间的关系。

4. Column-oriented database: In a traditional row-oriented database, when we try to retrieve a specific record, every single row is scanned. Indexing certain columns may improve the lookup speed — however, indexing every column slows down the updating (write) speed. Sometimes it may require you to lock the tables — if not completely, then partially — by locking certain parts of your table.

4.面向列的数据库：在传统的面向行的数据库中，当我们尝试检索特定记录时，将扫描每一行。 为某些列建立索引可能会提高查找速度-但是，为每列建立索引会减慢更新(写入)速度。 有时，可能需要您通过锁定表的某些部分来锁定表(如果不是完全锁定，则部分锁定)。

This is where column-oriented databases come in handy. They store individual columns separately, allowing an efficient scan when we have a limited number of columns (because there is only one data type in each table). Therefore, it is very efficient to add new columns. However, adding an entire record becomes more difficult. Although they may look similar to traditional databases, the method of storing and retrieving data is where the actual difference lies. They are best for analytics. Examples of the column-oriented database include but not limited to Cassandra (released by Facebook as an open-source project), Hypertable, Google BigTable, and Apache HBase.

这是面向列的数据库派上用场的地方。 它们分别存储各个列，当我们的列数有限时(因为每个表中只有一种数据类型)，可以进行有效的扫描。 因此，添加新列非常有效。 但是，添加整个记录变得更加困难。 尽管它们看起来类似于传统的数据库，但是存储和检索数据的方法才是真正的区别所在。 它们最适合分析。 面向列的数据库的示例包括但不限于Cassandra(由Facebook作为开源项目发布)，Hypertable，Google BigTable和Apache HBase。

两者的比较 (Comparison of the Two)

Now that we know the fundamentals of both SQL and NoSQL, we can dive into the technical details and compare them across certain factors. Analyzing across these parameters will help us understand their differences.

既然我们知道SQL和NoSQL的基础知识，我们就可以深入研究技术细节并在某些因素之间进行比较。 分析这些参数将有助于我们了解它们之间的差异。

理论差异 (Theoretical difference)

In short, SQL databases are primarily Relational Database Management Systems, while NoSQL databases are non-relational distributed databases and datastores.

简而言之， SQL数据库主要是Relational Database Management System ，而NoSQL数据库是非关系分布式数据库和数据存储。

架构： (Schema:)

A schema refers to the organization and shape of records within a database. Both of these database types have a very contrasting data storage models.

schema是指数据库中记录的组织和形状 。 这两种数据库类型都有非常不同的数据存储模型。

SQL databases usually deal with structured data that is organized in the form of tables. On the other hand, NoSQL databases, along with support for structured data, offer the convenience of storing unstructured, semi-structured, and polymorphic data as well.

SQL数据库通常处理以表形式组织的结构化数据 。 另一方面， NoSQL数据库以及对结构化数据的支持，也提供了存储非结构化，半结构化和多态数据的便利。

NoSQL databases can store information in the form of document stores, key-value pairs, graphs databases, and column stores. These DBs do not have predefined schemas that they need to adhere to.

NoSQL数据库可以以文档存储，键值对，图形数据库和列存储的形式存储信息。 这些数据库没有需要遵循的预定义架构。

This distinction makes them useful for specific scenarios. For example, RDBs are intuitively useful for accounting systems because they model the accounting ledger well. NoSQL is preferred in "Big Data" scenarios where the flexibility of data types are essential.

这种区别使它们对于特定方案很有用。 例如， RDB在会计系统上直观有用，因为它们可以很好地对会计分类账进行建模。 NoSQL在数据类型的灵活性至关重要的“大数据”方案中是首选。

可扩展性 (Scalability)

Scalability refers to how a database technology adapts to an ever-increasing amount of data without sacrificing performance.

可伸缩性是指数据库技术如何在不牺牲性能的情况下适应不断增长的数据量。

In this regard, singular SQL databases tend to be vertically scalable, in the sense that additional load can be handled by using more efficient and newer hardware (CPUs, RAM and SSD). On the flip side, NoSQL databases tend to be more horizontally scalable (they can automatically handle more traffic by distributing it among more servers in the database cluster).

在这方面，单个SQL数据库倾向于垂直可伸缩 ，这意味着可以使用更高效和更新的硬件(CPU，RAM和SSD)来处理额外的负载。 另一方面，NoSQL数据库往往具有更高的水平可扩展性 (它们可以通过在数据库集群中的更多服务器之间分配流量来自动处理更多流量)。

Note that relational databases can also be scaled via more hardware — however, there is some additional work to be done to unify the various database instances. This is why NoSQL databases are preferred in the case when our data is increasing at a very high rate.

请注意，关系数据库也可以通过更多的硬件进行扩展-但是，还需要做一些额外的工作来统一各种数据库实例。 这就是为什么在我们的数据以非常高的速度增长的情况下，首选NoSQL数据库的原因。

支持 (Support)

Historically, SQL database were in the market long before the NoSQL ones arrived. Therefore, it shouldn’t come as a surprise that almost all the SQL database vendors provide substantial support to their users. Moreover, a huge community of independent consultants is there to help with the large deployment of SQL databases.

从历史上看，SQL数据库在NoSQL之前就已经进入市场。 因此，几乎所有SQL数据库供应商都为其用户提供了实质性的支持，这不足为奇。 此外，庞大的独立顾问社区可以为SQL数据库的大规模部署提供帮助。

Of course, the same is starting to also be true for NoSQL databases. However, many are still in their embryonic stage, and thus depend more (or often solely) on the open source community for support. Furthermore, independent consultants are also more scarce when it comes to the setup and deployment of large-scale NoSQL databases.

当然，NoSQL数据库也开始如此。 但是，许多人仍处于起步阶段，因此更多(或经常单独)依赖开源社区寻求支持。 此外，在大型NoSQL数据库的设置和部署方面，独立顾问也更加稀缺。

使用的语言 (Languages Used)

SQL databases support Structured Query Language (SQL) which allows us to perform various operations on the database. It accomplishes this by providing different types of commands. These commands can be categorized based on their functionality:

SQL数据库 support Structured Query Language (SQL) ，这使我们可以对数据库执行各种操作。 它通过提供不同类型的命令来实现此目的。 这些命令可以根据其功能进行分类：

1. Data Definition Language (DDL)

   数据定义语言 (DDL)

2. Data Manipulation Language (DML)

   数据处理语言 (DML)

3. Data Control Language (DCL)

   数据控制语言 (DCL)

4. Transaction Control Language (TCL)

   交易控制语言 (TCL)

5. Data Query Language (DQL)

   数据查询语言 (DQL)

Though SQL is the most stable and widely used option for performing database operations, it may be restricting in the sense that it requires users to predefine the structure of tables and records. There's also the constraint that all data it stores must be consistent in observing that structure.

尽管SQL是执行数据库操作最稳定和使用最广泛的选项，但从某种意义上说，它要求用户预先定义表和记录的结构，这可能是有限制的。 还有一个约束，即它存储的所有数据在观察该结构时必须保持一致。

On the other hand, NoSQL databases are dynamic and flexible. They allow storing unstructured data in multiple ways. Depending on the nature of data, NoSQL options include document stores, key-value pairs, graph databases, and column-oriented stores. As mentioned, you are not required to come up with the structure of data before-hand. Each document may have its own unique structure. This is referred to as Unstructured Query Language (UnQL) and syntax will vary between products.

另一方面， NoSQL数据库是动态且灵活的 。 它们允许以多种方式存储非结构化数据。 根据数据的性质，NoSQL选项包括文档存储，键值对，图形数据库和面向列的存储。 如前所述，您无需事先提出数据结构。 每个文档可以具有其自己的独特结构。 这称为Unstructured Query Language (UnQL) ，语法在产品之间会有所不同。

ACID与BASE模型： (ACID vs BASE Model:)

SQL databases never compromise on ACID (Atomicity, Consistency, Isolation, Durability) properties. On the flip side, most of the NoSQL DBs observe the Brewers CAP theorem (Consistency, Availability and Partition tolerance) and the BASE Model (Basic Availability, Soft-state, Eventual Consistency).

SQL数据库从不妥协ACID (原子性，一致性，隔离性，耐久性)属性。 另一方面，大多数NoSQL DB遵循Brewers CAP定理(一致性，可用性和分区容限)和BASE模型(基本可用性，软状态，最终一致性)。

例子 (Examples)

Finally, let’s provide some examples. Although there are many RDBs and NoSQL, we will restrict this list to the most popular ones:

最后，让我们提供一些示例。 尽管有许多RDB和NoSQL，但我们将此列表限制为最受欢迎的：

SQL DBs include MS-SQL, Oracle, SQLite, MySQL, and Postgres.

SQL DB包括 MS-SQL，Oracle，SQLite，MySQL和Postgres。

NoSQL DBs include CouchDB, MongoDB, BigTable, Cassandra, Redis, RavenDB, HBase, and Neo4j.

NoSQL数据库包括 CouchDB，MongoDB，BigTable，Cassandra，Redis，RavenDB，HBase和Neo4j。

This lesson was originally published at https://algodaily.com, where I maintain a technical interview course and write think-pieces for ambitious developers.

本课程最初在 https://algodaily.com上 发布 ，我 在 那里维护技术面试课程，并为雄心勃勃的开发人员撰写思想著作。

翻译自: https://levelup.gitconnected.com/a-beginners-reference-to-sql-vs-nosql-1793e2a81905

sql vs nosql