Shulou(Shulou.com)05/31 Report--

This article mainly introduces "what is the importance of SQL". In daily operation, I believe many people have doubts about the importance of SQL. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful for you to answer the doubts about "what is the importance of SQL?" Next, please follow the editor to study!

In March 1971, Intel launched the world's first general-purpose microprocessor, Intel 4004, which has 2300 transistors and sells for $60. Fast forward 50 years or so, and the latest iPhone has nearly 12 billion transistors (unfortunately, a little more expensive than $60).

It wasn't until the 1990s that many of the programming languages we use today were introduced (Java was introduced in 1996). However, there is one programming language that was introduced 50 years ago, but it is still as hot as it was then, and that is SQL.

Early History of Database Management-- IDS and CODASYL

In 1962, Charles W. Bachmann (Charles W.Bachman) (not related to Erich Bachmann [ErlichBachman] of Silicon Valley) was a member of a small team at General Electric. A year later, Bachmann's team launched the Integrated data Storage system (IDS), which was later recognized as the first database management system.

Ten years later, Bachmann won the coveted Turing Prize, also known as the Nobel Prize in computer science, for his contribution to IDS computing.

What is IDS?

In the early 1960s, computer science was just beginning to become an academic field. In this context, the American Standard Code for Information Exchange (ASCII) was not introduced until 1963. To understand IDS, we need to understand the two main drivers of its development:

Introduction of disk memory

A shift to a high level of programming

Disk storage

Photo: workers are moving a RAMAC305.

In 1956, IBM launched its first commercial hard disk drive, the RAMAC 305. The introduction of disk drives enables programmers to jump directly to a location on the disk to retrieve and update data. This is a huge improvement over its predecessor tape drive, which needs to move sequentially in the tape to retrieve a particular piece of data.

But for now, developers must find out where records are stored on disk. Because of the limitations of the file management system in early operating systems, this is an advanced task that only experienced programmers can accomplish. Developers need a solution to simplify the operation of disk drives.

High-level programming

At the same time, on the adoption curve, computer science began to shift from innovators to early adopters. Low-level programming languages like Assembly were popular in the early academic world, but ordinary programmers turned to higher-level programming languages, such as COBOL, because they were easier to use.

What is IDS?IDS solves disk storage and advanced programming problems, allowing developers to use high-level programming languages such as COBOL to build applications, enter and retrieve data from disk storage. Because of this feature, IDS became the first database management system.

New Standard of CODASYL-- Database Management

In 1969, the data Systems language Committee (CODASYL) issued a report proposing a standard for database management. Bachmann is a member of the committee, and IDS is heavily cited in the report.

The CODASYL data model introduces many of the core features of database management systems today:

Schema data definition language (DDL)

Data manipulation language (DML)

Most importantly, IDS and CODASYL introduced a new data modeling method, which affected the final development of SQL, namely the network data model.

Network data model-better than today's relational model?

Figure: example of a network model

A data model is a standard method for describing (simulating) the world (data).

The previous hierarchical data model used a tree structure to describe data, but it was limited to one-to-many relationships. The new network model allows child records to have multiple parent records, creating a graphical structure. By allowing multiple parent records, the network model can model many-to-one and many-to-many relationships.

In the network model, the relationships between tables are stored in collections. Each collection has an owner (that is, a teacher) and one or more members (that is, classes and students).

A key advantage of the network model is that the relevant records in the collection are directly connected through pointers. Collections are implemented through next,prior, and owner pointers, and can be traversed as easily as a linked list.

The underlying features of the network data model provide performance benefits, but at a cost. The network data model increases storage costs because each record must store additional pointers to previous records and parent records.

The advent of relational model

Figure: an example of a relational model

In 1970, eight years after IDS, Edgar F. Codd introduced relational models in his groundbreaking paper, "data Relational models for large shared databases" (which, like Bachmann, made him a Turing Award winner).

Coder shows that all data in the database can be represented by tuples (rows in SQL), which are grouped into relationships (tables in SQL). In order to describe database queries, he invented the form of first-order predicate logic, called tuple relational calculus.

Tuple relational calculus introduces a declarative language for querying data. Declarative programming languages allow programmers to say "what they want to do" without describing how to do it.

This new declarative language is easier for developers to use. The relational model exposes all the data publicly. Developers can retrieve all the data from the table or read a single row in a single command (thanks to the query optimizer). Gone are the days when we followed the pointer maze to find data.

Relational data model vs. Network data model

By standardizing the data, the relational database can reduce the high storage cost of the network database. Normalization is a process of decomposing tables to eliminate redundancy, thereby reducing the disk space consumed by data.

However, relational databases increase the cost of CPU. In order to run normalized data, the relational database must load the tables into memory and "join" the tables together using computing power. Let's complete the process of finding all classes and students for a particular teacher through a relational model.

The database system first performs an operation to retrieve all relevant classes. Then, its second step is to retrieve student data. All the data is stored in memory, and it takes the third step to merge the data before returning the results.

Performance comparison diagram of relational model and network model

In the performance case study using real data, Rema data Management (Raima) found that the insertion performance of the network database model is 23 times better than that of real data, and the speed of querying data is 123 times faster than real data.

So why is relational database the leading database solution?

Ease of use

The relational model is more flexible in responding to changes, and its declarative syntax simplifies the work of programmers. Moore's Law plays a magical role behind it. The computational cost continues to decrease, and finally, the increased computational cost in the relational model is offset by the benefits brought by the increase in productivity. Fast forward another 50 years, and today, the most expensive resource in the data center is CPU.

The rise of the SQL era

Four years after Coder's article was published, Donald Chamberlain (Donald Chamberlin) and Raymond Boyce (Raymond Boyce) published SEQUEL: a structured English query language.

They describe SEQUEL as "a set of simple operations on table structure on the exponentiation of first-order predicates". IBM discovered this potential and quickly developed the first version of SEQUEL in the early 1970s as part of its System R project.

It was later renamed SQL because of a trademark dispute with the British supplier Hawker Siddeley. SQL's next leap will be nearly a decade later. In 1986, the American National Standards Institute (ANSI) and the International Organization for Standardization (ISO) issued the first official SQL standard: SQL-86. The standard divides SQL into the following parts:

Data definition language (DDL): commands for defining and modifying schemas and relationships.

Data manipulation language (DML): commands for querying, inserting, and deleting information from a database.

Transaction control: a command that specifies the time of a transaction.

Integrity: a command to set constraints in database information.

View: a command that defines a view.

Permissions: commands that specify user access

Embedded SQL: a command that specifies how to embed SQL in other languages.

Since 1974, numerous competitors have tried to seize market share from the dominant query language SQL. These new syntax are usually targeted at specific new technologies:

Lisp-> CLSQL.NET-> LINQRuby on Rails-> ActiveRecord

35 years later, SQL is still ubiquitous in the database. How does it maintain its dominance as a query language, and what can we learn from its story?

The secret of SQL's 50-year rule-what can we learn from it?

Figure: 2017 stack overflow developer survey

We started with Bachmann's introduction of the first database management system, IDS, and discussed the transition from disk storage to advanced programming, giving rise to the need for new methods of processing data. Then came CODASYL and standardized database management. IDS and CODASYL introduced a new network data model. In the end, Cod abandoned the relationship model. It all took more than eight years to develop.

How did SQL successfully stay popular for the next 50 years? I think there are four main reasons:

Based on basic principles

The basic principle is a basic proposition that cannot be deduced from any other proposition or hypothesis. For example, the combination of hydrocarbons and oxygen to produce a chemical reaction is still the principle that drives every car's internal combustion engine.

In 1970, Cod created a new basic principle of database: tuple relational calculus. The creation of this new logic produces a relational model, which leads to SQL. The tuple relation calculus is the chemical reaction, the relation model is the internal combustion engine, and SQL is the car.

Bushnell's theorem

Success is not guaranteed by being based solely on basic principles. Assembly is the closest program a programmer can make to 1 and 0, but it is still replaced by COBOL (and later C), and the missing ingredient is ease of use.

Similarly, the network model performs better in the transition from a network model to a relational model, but now every company is using a relational database because it is easier to use.

Assembly is not only difficult to master, but also difficult to get started. SQL achieves a perfect balance between the two. With 10 or so SQL commands, anyone can learn 20% and then advance to 80%, but it takes a long process of indexing, view, and optimization to become a master.

Listening and adapting

Query language is not an eternal single language, but a set of adaptive standards that keep pace with the times. The SQL standard has been adjusting over time and absorbing feedback from users.

Since the initial concept, we have seen 10 different SQL standards, each of which has made significant changes. Here are three of the top updates:

1.SQL:1999: added regular expression matching, recursive queries (such as transitive closures), triggers, support for procedure statements and flow control statements, non-scalar types (arrays), and some object-oriented features (such as structured types). Support for embedding SQL (SQL/OLB) in Java and vice versa (SQL/JRT).

2.SQL:2003: introduces XML-related features (SQL/XML), window functions, standardized sequences, and columns that automatically generate values (including identity columns).

3.SQL:2016: add row pattern matching, polymorphic table function, JSON.

SQL also demonstrates the ability to create a track on which other products are built. SQL does not need to enforce syntax, but instead provides a standard for each database to complete self-implementation (T-SQL, MySQL, PSQL, etc).

Adoption of API

The final secret of SQL's success is the rise of application programming interfaces (API). API simplifies programming by abstracting the underlying operations, exposing only the objects or operations that developers need. API enables SQL to use specific syntax to continuously adapt to new technologies.

In 2006, Hadoop introduced a distributed file system (HDFS), which was initially inaccessible by SQL syntax. In 2013, however, Apache created Apache Impala, which allows developers to use SQL to query HDFS databases.

SQL is one of the most commonly used programming languages today, and we should not forget its long history. Its journey began at the dawn of modern computing and came to life with the efforts of two Turing Prize winners.

At this point, the study of "what is the importance of SQL" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.