sql简介_SQL简介

sql简介

Today, data is the basis of any business. The world of enterprise computing is the epitome of data-driven businesses. The importance of structured storage of data is unquestionably in the focus. And at this time, with the computing model quickly shifting towards the cloud, and storage costs falling rapidly, enterprises are leveraging data more and more to tune their businesses.

如今，数据已成为任何业务的基础。 企业计算的世界是数据驱动型企业的缩影。 毫无疑问，结构化数据存储的重要性是重点。 而此时，随着计算模型Swift向云转移，存储成本Swift下降，企业越来越多地利用数据来调整业务。

It is, then, imperative that a ground-zero understanding of this structured model of storage and retrieval of data is of paramount importance. A beginner, however, naturally feels lost in the sea of information available out in the wild world. This series, Introduction to SQL, comes to the rescue, by starting at the very base of SQL — the very fabric — and then, launching from there, towards the bigger picture.

因此，至关重要的是，对这种存储和检索数据的结构化模型进行零基础的了解至关重要。 但是，初学者自然会迷失在野外获得的大量信息中。 从SQL的最基本部分（即结构）开始，然后从那里开始，朝更大的方向发展，这一系列的《 SQL入门》可助您一臂之力。

为什么要在2020年使用SQL ( Why You Should Use SQL in 2020 )

The concept of SQL has been around for four decades now. However, it has been evolving and continues to evolve. We all studied SQL at some point or another in our education. We are here to make it better by moving away from the archaic tools and practices. When you learn a certain system using archaic tools and practices, you need to assimilate it and rethink the application of this system in the modern industry when you become part of it. With this book, on the other hand, you would be learning the concepts using industry-standard tools and practices.

SQL的概念已经存在了四十年了。 但是，它一直在发展并继续发展。 在我们的教育中，我们都曾经学习过SQL。 我们在这里通过远离古老的工具和实践来使其变得更好。 当您使用过时的工具和实践学习某个系统时，您需要对其进行吸收，并在成为现代系统的一部分时重新考虑该系统在现代行业中的应用。 另一方面，通过本书，您将使用行业标准的工具和实践来学习这些概念。

Understand that SQL is not going anywhere. If anything, it has only become a hot cake with a further push in the competition by some strategic drivers like Microsoft, who have now made SQL available for Linux as well. The different cloud offerings such are Microsoft Azure and Amazon Web Services — the two largest players in the field — have a very dedicated approach to the relational database model, and SQL, subsequently. And this is just the beginning of a new era.

了解SQL不会走到任何地方。 如果有的话，它只是成为一个热门蛋糕，进一步加剧了诸如Microsoft之类的战略驱动因素的竞争，这些战略驱动因素现在也使SQL适用于Linux。 Microsoft Azure和Amazon Web Services等不同的云产品（该领域的两个最大参与者）对关系数据库模型和SQL采用了非常专门的方法。 这仅仅是新时代的开始。

When you enter into this era, it is important that you know how what works, so that you can propel the business you own or support, to new heights.

当您进入这个时代时，重要的是要知道如何运作，以便将自己拥有或支持的业务推向新的高度。

为什么SQL重要？ 它要解决什么问题？ ( Why is SQL important? What problem is it solving? )

Data management or database administration is incomplete without the SQL. To feel comfortable using the powerful SQL as part of your administration or development requires that you understand the basics of SQL, which will take you a long way in your career.

没有SQL，数据管理或数据库管理是不完整的。 在管理或开发过程中使用强大SQL感到自在时，需要了解SQL的基础知识，这将使您的职业生涯很长一段路。

In this book, therefore, we start with the introduction to the SQL itself, and then, understand the important features of SQL Server. The chapters will take you through a demonstration of the internal workings of SQL, starting from SQL standards, evolution, history (this is important so that you know how it grew and became the beast it is today, and so that you can leverage its potential powers) and progresses to creating tables, understanding and defining relationships, writing Transact‑SQL commands, and so on.

因此，在本书中，我们首先介绍SQL本身，然后了解SQL Server的重要功能。 这些章节将带您从SQL标准，演变，历史开始对SQL的内部工作方式进行演示（这很重要，这样您才能知道它是如何成长并成为当今的野兽，以便您可以利用它的潜力功能），并逐步创建表，理解和定义关系，编写Transact-SQL命令等等。

You will also understand that SQL is a special-purpose programming language; special-purpose, as in, it is different from the general-purpose programming languages such as C, C++, Java/JavaScript, etc., meaning, it has a very particular purpose: manipulation of datasets. And this manipulation happens using what is known as Relational Calculus.

您还将了解SQL是一种专用的编程语言。 例如，它与C，C ++，Java / JavaScript等通用编程语言不同，这意味着它具有非常特殊的目的：操纵数据集。 并且这种操纵是使用所谓的关系演算发生的 。

But isn’t studying SQL alone, restrictive? Turns out, it isn’t. Of course, we can use SQL on any kind of database or data source, but even if we cannot directly use SQL, most query languages of today have some relationship to SQL. In general, once you know SQL, you can effortlessly pick up other query languages.

但是，难道不是仅在研究SQL方面有限制性吗？ 事实并非如此。 当然，我们可以在任何类型的数据库或数据源上使用SQL，但是即使我们不能直接使用SQL，当今大多数查询语言也与SQL有一定关系。 通常，一旦您了解SQL，就可以轻松选择其他查询语言。

Standards are vital because every relational database must build its framework around this framework in order to ensure compatibility. This means that the learning curve is greatly reduced. SQL is ANSI as well as ISO-compliant, along with other standards, which emphasizes the fact that you have to learn the concept only once.

标准至关重要，因为每个关系数据库都必须围绕此框架构建其框架，以确保兼容性。 这意味着学习曲线大大减少。 SQL是ANSI以及与ISO兼容的标准以及其他标准，它强调了您只需要学习一次该概念这一事实。

还有哪些其他工具？ 为什么SQL更好？ ( What other tools are out there? Why is SQL better? )

There are different ways in which data can be stored. One of the modes of storage is to establish a specific structure based on the blocks you would use to store data, and store the data in that structure. This model of storage is the one with a predefined schema. SQL is best suited for this model of data storage.

有多种存储数据的方式。 存储模式之一是根据要用于存储数据的块建立特定的结构，然后将数据存储在该结构中。 这种存储模型是具有预定义模式的存储模型。 SQL最适合这种数据存储模型。

There are several tools available to manage, compare, administer and develop SQL databases. Different tools are built for different purposes, with their own sets of pros and cons. However, the underlying fabric is SQL.

有几种工具可用于管理，比较，管理和开发SQL数据库。 针对不同的目的构建了不同的工具，各有其优缺点。 但是，基础结构是SQL。

SQL has been around for a long time: the first SQL product available for public use was launched in 1979—Oracle version 2— and Oracle remains one of the premier database systems today. And the underlying concepts are the same since then, with a majority of SQL operations (and commands) involving four basic verbs: Select, Insert, Update and Delete. Also, SQL is generally whitespace-independent, which means that adding spaces within or between clauses will not matter. Most SQL queries are standardized to seem like a question you direct towards a database object, which the said database object knows how to respond to. SQL uses a command interpreter to parse the SQL query. And since SQL, combined with the interpreter is so powerful that it has been adopted into many database products.

SQL已经存在很长时间了：1979年发布了第一个可供公众使用SQL产品-Oracle版本2-Oracle仍然是当今主要的数据库系统之一。 从那时起，基本概念是相同的，大多数SQL操作（和命令）涉及四个基本动词：选择，插入，更新和删除。 而且，SQL通常是与空格无关的，这意味着在子句中或子句之间添加空格将无关紧要。 大多数SQL查询都经过标准化处理，看起来像是您直接针对数据库对象的问题，该数据库对象知道如何响应。 SQL使用命令解释器来解析SQL查询。 而且由于SQL与解释器的结合非常强大，因此已被许多数据库产品采用。

Of course, there are other SQL products, not among the conventionally used tools and systems. Some are barebones SQL, while others have other features on top of them. There are some exceptions, which may support only a part—not all—of the SQL standard. Therefore, when you start to specialize in a database product, you would need to learn those features, but a majority of what you do will revolve around what you learn in this book; the additional features will only act as the exterior coat.

当然，还有其他SQL产品，不在常规使用的工具和系统中。 一些是准系统SQL，而另一些则具有其他功能。 有一些例外，它们可能仅支持SQL标准的一部分，而不是全部。 因此，当您开始专门研究数据库产品时，您需要学习这些功能，但是您所做的大部分工作都将围绕本书中的内容进行； 其他功能将仅用作外涂层。

SQL如何帮助开发人员？ ( How can SQL help developers? )

In an industry where data is everything, data tools are among the must-haves. SQL is unquestionably on the list. Any data science professional will tell you, for example, how important SQL is. In a survey of analytics, data mining and data science software, conducted in 2015, SQL was placed third in the list of usage, after Python and the R language. Notice how SQL is the first database tool/framework on the list.

在数据是一切的行业中，数据工具是必不可少的。 毫无疑问，SQL在清单上。 例如，任何数据科学专业人士都会告诉您SQL的重要性。 在2015年进行的一项分析，数据挖掘和数据科学软件调查中 ，SQL在使用量列表中排名第三，仅次于Python和R语言。 请注意，SQL是列表上第一个数据库工具/框架。

SQL can work on varied systems and platforms. Therefore, knowing SQL is important for developers who deal with the data. Understanding SQL will take you a long way in creating fast, efficient software, which leverages the benefits of well-structured data.

SQL可以在各种系统和平台上工作。 因此，了解SQL对于处理数据的开发人员很重要。 对SQL的理解将使您在创建快速，高效的软件方面走很长的路要，它利用了结构良好的数据的优势。

It can be said, without a doubt, that SQL is the only transferrable skill that can find a place in the toolbox of any developer or database administrator. The SQL is standardized, and therefore, is useful across systems that deal with databases. Once you know SQL, all you would need is to adopt very minor syntax adjustments from one database system to another—the model remains the same, the fabric remains the same.

可以肯定地说，SQL是唯一可以在任何开发人员或数据库管理员的工具箱中找到位置的可转移技能。 SQL是标准化的，因此在处理数据库的系统中很有用。 一旦了解了SQL，您所要做的就是从一个数据库系统到另一个数据库系统进行非常小的语法调整-模型保持不变，结构保持不变。

给SQL开发人员和管理员的建议 ( Advice for SQL developers and administrators )

Let me start with data munging. Data munging, or wrangling, is the process of transforming data into various states based on what state would be better suited for a situation at hand. In other words, how you should change the representation of data to make it more understandable to an application or user. For instance, when you use APIs to download content, or scrape a website or use an existing data set for prediction, how would you transform the data so it becomes a well-defined stream, that can be readily consumed by a certain data analysis tool?

让我从数据处理开始。 数据处理或争用是根据哪种状态更适合当前情况将数据转换为各种状态的过程。 换句话说，应如何更改数据的表示形式以使应用程序或用户更容易理解。 例如，当您使用API​​下载内容，抓取网站或使用现有数据集进行预测时，如何转换数据，使其成为定义明确的数据流，某些数据分析工具可以很容易地使用它？

The primary issue with data munging is data duplication. A few wrong SQL joins during munging can potentially generate thousands of duplicate data. These may be because of SQL code or because of issues in the backend database. A quality assessment after every step of data munging is important to avoid these issues.

数据整理的主要问题是数据重复。 清除期间的一些错误SQL连接可能会生成数千个重复数据。 这些可能是由于SQL代码或由于后端数据库中的问题。 在进行数据整理的每个步骤之后进行质量评估对于避免这些问题很重要。

More important than what good comes out of learning SQL is what bad comes out of not learning SQL. We see in the industry, that in general, very little time is spent in learning or practicing the skills required to manage SQL databases. This leads to a series of bad things, the biggest ones of which are:

比学习SQL带来的好处更重要的是，不学习SQL带来的不好。 我们在行业中看到，通常，很少有时间花在学习或练习管理SQL数据库所需的技能上。 这导致了一系列坏事，其中最大的是：

资料建模 ( Data modelling )

Think of your data as the bricks you use to construct a building. Think of the SQL schema as the blueprint of your building. When you are constructing a simple structure of four walls, twelve feet in height and fifteen feet in width each, and no roof, you do not need much of a schematic. However, when your data gets more complicated, and a situation where you need a hundred tables (or walls, in our analogy), plus the roof, and if the blueprint does not account for the structural engineering and the myriad of standards and requirements, you end up building a structure that collapses in no time, which is, loads of duplicate data, over-complicated modeling, non-scalable structures, etc., which leads to a lot of overhead in the long run: on you, as well as the business you manage, support, or own.

将数据视为用于建造建筑物的砖头。 将SQL模式视为建筑物的蓝图。 当您构建一个简单的结构，该结构由四堵墙，每个高12英尺，宽15英尺，没有屋顶构成时，则不需要太多原理图。 但是，当您的数据变得更加复杂，并且您需要一百张桌子（或类似的墙壁）加上屋顶时，并且如果该蓝图无法说明结构工程以及无数的标准和要求，您最终会构建一个立即崩溃的结构，即重复数据的负载，过于复杂的建模，不可伸缩的结构等，从长远来看，这会导致大量开销：对您而言，作为您管理，支持或拥有的业务。

数千条SQL查询 ( Thousands of SQL queries )

ORMs or Object-Relational Mapping is used in software code written in an object-oriented language with the relational database. In other words, ORMs convert data between the application and the database. In general, if the data is not modeled properly, these ORMs may create hundreds of SQL queries when mapping the data with the application, which will hammer the database and slow down the systems drastically.

ORM或对象关系映射用于关系数据库中以面向对象语言编写的软件代码中。 换句话说，ORM在应用程序和数据库之间转换数据。 通常，如果数据建模不正确，则这些ORM在与应用程序映射数据时可能会创建数百个SQL查询，这将严重打击数据库并严重降低系统速度。

运行缓慢的查询 ( Slow running queries )

This is a contrasting situation: developers often do something complex with the ORM, ending up creating a monster SQL query. This query would take several seconds to run, and therefore, hinder the performance of the database, as well as the application.

这是一种相反的情况：开发人员经常对ORM做一些复杂的事情，最终创建了一个怪异SQL查询。 该查询将花费几秒钟来运行，因此会影响数据库以及应用程序的性能。

数据的完整性 ( Data integrity )

This is also an important part of data modeling. Data in a SQL database is stored with constraints. These constraints are an integral part of the architecture. If your database does not have thought-out constraints, your data will get messy. Typically, on projects that span years, you usually end up with records that do not get cleaned up when not required anymore.

这也是数据建模的重要部分。 SQL数据库中的数据存储有约束。 这些约束是体系结构的组成部分。 如果您的数据库没有考虑周全的约束，则数据将变得混乱。 通常，在跨多年的项目中，您通常会得到不再需要时不再清理的记录。

Every developer (and tomorrow it is going to be you) must take the time to understand SQL in full. Only then should you graduate to using an ORM. A deeper understanding of table modeling, relationships, constraints, and joins will help you avoid these pitfalls in the future.

每个开发人员（将来都会成为您）必须花一些时间全面了解SQL。 只有这样，您才能毕业使用ORM。 对表建模，关系，约束和联接的深入了解将帮助您避免将来遇到这些陷阱。

Get started with SQL with this course. We understand the systems, we understand the industry, and we understand — through our years of experience — the different points-of-failure. This course covers key topics in SQL, such as aggregate functions, constraints, joins, subqueries, etc., which will be invaluable when working with databases. The course starts from the very basics — like “what CRUD is” — and assumes no knowledge of databases at the reader’s end. A perfect beginner’s course in SQL.

通过本课程开始使用SQL。 我们了解系统，了解行业，并通过多年的经验了解不同的故障点。 本课程涵盖SQL的关键主题，例如聚合函数，约束，联接，子查询等，这些主题在使用数据库时将非常宝贵。 本课程从最基本的内容（例如“ CRUD是什么”）开始，并且假定读者不了解数据库。 完美SQL初学者课程。

顶级SQL神话 ( Top SQL Myths )

The world’s favorite SQL myths are those when comparing SQL to NoSQL. It is often said that NoSQL will replace SQL. This is not true. In fact, NoSQL came into being in the sixties — even before SQL was born. SQL and NoSQL are two complementary solutions. One is better-suited than the other, based on the system that will utilize them.

将SQL与NoSQL进行比较时，世界上最受欢迎SQL神话。 人们常说NoSQL将取代SQL。 这不是真的。 实际上，NoSQL诞生于六十年代-甚至在 SQL诞生之前 。 SQL和NoSQL是两个互补的解决方案。 基于将利用它们的系统，一个比另一个更适合。

Also, it is a common idea that SQL and NoSQL come with a clear distinction. The fact is, the line that divides NoSQL from SQL is blurring more and more as the two systems evolve. What is emerging is NewSQL, which is a hybrid between the two. And the fact remains: you need to know SQL to work with these new systems.

同样，SQL和NoSQL有明显的区别是一个普遍的想法。 事实是，随着两个系统的发展，将NoSQL与SQL分开的界限越来越模糊。 新兴的是NewSQL，它是两者之间的混合体。 事实仍然存在：您需要了解SQL才能与这些新系统一起使用。

Outside of the SQL – NoSQL war is the idea that “SQL is all about fetching data”. This stems from the fact that most resources used to teach beginners concentrate only on the syntax part of SQL. The subtle message that beginners get is that SQL is all about querying. This leads to the idea that if a query executed without syntax errors, it is the right query for the job. This is a wrong notion that springs from a nonunderstanding of the SQL standards, and more importantly data modeling. The overlooking of these have placed SQL among the most underrated tools.

在SQL之外– NoSQL战争的思想是“ SQL完全是关于获取数据”。 这是由于这样的事实，即大多数用于教导初学者的资源仅集中在SQL的语法部分。 初学者得到的微妙信息是，SQL全部与查询有关。 这导致了这样一个想法，即，如果执行的查询没有语法错误，则它是该作业的正确查询。 这是对SQL标准的理解，更重要的是对数据建模的错误理解。 这些方面的忽视使SQL成为最被低估的工具之一。

If you would like to test out this myth yourself, take a relational database with about three to ten tables on different topics (e-commerce, airline, retail, etc.) and ask some tough business questions. Try to find answers to them by executing several SQL blocks. These will form a good baseline to test your skills.

如果您想亲自检验一下这个神话，可以获取一个包含约三到十个有关不同主题（电子商务，航空公司，零售等）的表格的关系数据库，并提出一些棘手的业务问题。 尝试通过执行几个SQL块来找到答案。 这些将构成测试您技能的良好基准。

摘要 ( Summary )

SQL is a beautiful beast, which has seen a significant growth in usage in the last couple of years. SQL, among languages, is like water among solvents. With an understanding of SQL, you enhance your capabilities and specialize as a programmer, developer, project manager and more. SQL is an open-source. A number of databases that use SQL — such as MySQL, MariaDB, and PostgreSQL — are open source with a thriving community of users.

SQL是一头美丽的野兽，在最近几年中，SQL的使用量显着增长。 在语言中，SQL就像溶剂中的水。 通过了解SQL，您可以增强自己的能力，并专门从事程序员，开发人员，项目经理等工作。 SQL是开源的。 许多使用SQL的数据库（例如MySQL，MariaDB和PostgreSQL）都是开放源代码，并且拥有大量的用户社区。

SQL may be assumed to handle rows and columns of data, and I hear many undermining it to atrocious extents by comparing it with spreadsheets. Comparing spreadsheets to SQL databases in terms of handling millions of records is like comparing a pinwheel to a windfarm. Whether there are 1,000 records or a hundred million, SQL is equipped to handle data pools of virtually any size.

可以假设使用SQL处理数据的行和列，并且我听说通过与电子表格进行比较，许多SQL都受到了严重破坏。 在处理数百万条记录方面，将电子表格与SQL数据库进行比较就像将风车与风电场进行比较。 无论有1000条记录还是一亿条记录，SQL都可以处理几乎任何大小的数据池。

Also, SQL is omnipresent in the world of computers. Apart from large corporations, small companies, banks, hospitals and colleges, SQL is used virtually everywhere, including your iOS or Android smartphone—and I’m not talking just apps.

而且，SQL在计算机领域无处不在。 除了大型公司，小型公司，银行，医院和学院外，SQL几乎在所有地方都使用，包括您的iOS或Android智能手机-我并不是在谈论应用程序。

One major drawback with SQL is that the basic SQL standard does not have any decision structures or looping constructs such as if-else or for or while. Therefore, vendors have built solutions — or rather, language additions — that extend the vanilla SQL, such as Microsoft’s Transact-SQL (T-SQL) or Oracle’s Procedural Language SQL (PLSQL).

SQL的一个主要缺点是基本SQL标准没有任何决策结构或循环结构（如if-else或for或while）。 因此，供应商已经构建了解决方案（或者更确切地说是语言添加）来扩展原始SQL，例如Microsoft的Transact-SQL（T-SQL）或Oracle的过程语言SQL（PLSQL）。

Surprisingly, however, there aren’t many individuals who have an understanding of SQL, beyond a mere working knowledge of it. Knowing SQL makes you invaluable in the field. Especially because more and more businesses are becoming digitized, and any and every business journal talks about analytics or business intelligence. As the usage of analytics and Bi grows (which it will, as the data grows), requirements for professionals to deal with data will increase. And SQL is the key skill that will empower you to be that professional that the businesses continue to seek.

但是，令人惊讶的是，除了对SQL有实际了解之外，没有多少人了解SQL。 知道SQL会使您在该领域变得无价。 尤其是因为越来越多的企业正在数字化，并且每本商业期刊都谈论分析或商业智能。 随着分析和Bi的使用量的增长（随着数据的增长，它的使用也会增加），专业人员处理数据的需求也会增加。 SQL是使您成为企业不断寻求的专业技能的关键技能。

翻译自: https://www.sqlshack.com/introduction-to-sql/

sql简介