SQL ( Ã, ( listen ) SQL , "sequels"; Structured Query Language ) is a domain-specific language used in programming and is designed to manage hold of data in a relational database management system (RDBMS), or for stream processing in a relational data flow management system (RDSMS). This is very useful in handling structured data where there is a relationship between different entities/data variables. SQL offers two major advantages over older read/write APIs such as ISAM or VSAM: first, it introduces the concept of accessing multiple records with a single command; and secondly, it eliminates the need to specify how reaches the recording, e.g. with or without index.
Originally based on relational algebra and tuple relational calculus, SQL consists of many types of statements, which can be classified informally as sub-languages, usually: data query language (DQL), data definition language (DDL), data control language (DCL) and data manipulation language (DML). The scope of SQL includes data requests, data manipulation (insert, update and delete), data definition (schema creation and modification), and data access control. Although SQL is often described as, and most are, declarative languages ​​(4GL), it also includes procedural elements.
SQL is one of the first commercial languages ​​for Edgar F. Codd's relational model, as described in his influential paper in 1970, "The Relational Data Model for Large Joint Data Banks". Although it does not fully follow the relational model as described by Codd, it becomes the most widely used database language.
SQL became the standard of the American National Standards Institute (ANSI) in 1986, and from the International Organization for Standardization (ISO) in 1987. Since then, standards have been revised to include a larger set of features. Although there are such standards, most of the SQL code is not fully portable among different database systems without any adjustments.
Video SQL
History
SQL was originally developed at IBM by Donald D. Chamberlin and Raymond F. Boyce after learning about the relational model of Ted Codd in the early 1970s. This version, originally called SEQUEL ( Structural Structured English ), is designed to manipulate and retrieve data stored in IBM's original quasi-relational database management system, System R, the group at IBM San Jose Research Laboratory has been developed during the 1970s.
Chamberlin and Boyce's first attempt for relational database languages ​​is Square, but it's hard to use because of subscript notation. After moving to the San Jose Research Laboratory in 1973, they started work on SEQUEL. The SEQUEL acronym is then converted to SQL because "SEQUEL" is a trademark of the UK-based Hawker Siddeley aircraft company.
In the late 1970s, Relational Software, Inc. (now Oracle Corporation) sees the potential concepts described by Codd, Chamberlin, and Boyce, and develops their own SQL-based RDBMS with aspirations to sell them to the US Navy, Central Intelligence Agencies, and other US government agencies. In June 1979, Relational Software, Inc. introduced the SQL implementation, Oracle V2 (Version2) for the first commercially available VAX computer. In 1986, ANSI and the ISO standards group formally adopted the standard definition of "Language Database SQL". The new version of this standard was published in 1989, 1992, 1996, 1999, 2003, 2006, 2008, 2011, and most recently, 2016.
After testing SQL on a customer testing site to determine the usefulness and practicality of the system, IBM began to develop commercial products based on their System R prototypes including/38, SQL/DS and DB2, commercially available systems in 1979, 1981, and 1983, respectively -something.
Maps SQL
Design
SQL diverges in several ways from its theoretical basis, its relational model and its tuple calculus. In that model, the table is a collection of tuples, whereas in SQL, query tables and results are row lists: the same row may appear multiple times, and the order of rows can be used in queries (eg in LIMIT clauses).
Critics argue that SQL should be replaced by a language that strictly returns to its original foundation: for example, see Third Manifesto .
Syntax
The SQL language is divided into several language elements, including:
- Clause , which is a constituent component of the statement and question. (In some cases, this is optional.)
- Expressions , which can generate scalar values, or tables consisting of columns and rows of data
- Predicate , which specifies the conditions that can be evaluated to the logic of three SQL (3/V) values ​​(true/false/unknown) or Boolean truth values ​​and is used to limit the effect of statements and queries, or to alter the flow program.
- Queries , which retrieve data based on certain criteria. This is an important element of SQL .
- Statements , which may have a fixed effect on schemata and data, or can control transactions, program flow, connections, sessions, or diagnostics.
- The SQL statement also includes a semicolon terminator (";"). Although not required on any platform, it is defined as a standard part of the SQL grammar.
- Inconsiderate white space is generally ignored in SQL statements and queries, making it easier to format the SQL code to read.
Extension procedure
SQL is designed for a specific purpose: to query the data contained in the relational database. SQL is a set-based declarative programming language, not an imperative programming language like C or BASIC. However, extensions for Standard SQL add procedural programming language functionality, such as flow-control constructs. These include:
In addition to standard SQL/PSM extensions and proprietary SQL extensions, procedural and object-oriented programming is available on many SQL platforms through the integration of DBMS with other languages. The SQL standard defines SQL/JRT extensions (Routines and SQL Types for Java Programming Language) to support Java code in SQL databases. SQL Server 2005 uses SQLCLR (SQL Server Common Language Runtime) to host managed.NET assemblies in the database, while previous versions of SQL Server are limited to extended extended extended procedures especially those written in C. PostgreSQL allows users to write functions in various languages ​​- including Perl, Python, Tcl, JavaScript (PL/V8) and C.
Interoperability and standardization
The SQL implementation is incompatible between vendors and does not fully follow the standard. In certain date and time syntax, string concatenation, NULL s, and the letter sensitivity ratio varies from vendor to vendor. Specific exceptions are PostgreSQL and SQL Mimer that strive to meet the standards.
The popular implementation of SQL usually removes support for basic features of Standard SQL, such as data type DATE or TIME . The most obvious examples, and by chance the most popular commercial and commercial DBMS, are Oracle (which DATE behaves as DATETIME , and does not have TIME type) and MS SQL Server (before version 2008). As a result, SQL code is rarely transferable between database systems without modification.
There are several reasons for the lack of portability between database systems:
- The complexity and size of SQL standards means that most implementers do not support all standards.
- The standard does not specify database behavior in some important areas (eg indexes, file storage...), allowing implementations to decide how to behave.
- The SQL standard precisely specifies the syntax that should be applied by the appropriate database system. However, the standard semantic specs of language constructs are poorly defined, which causes ambiguity.
- Many database vendors have a large existing customer base; where newer versions of the SQL standard conflict with the previous behavior of vendor databases, vendors may not want to undermine compatibility.
- There is little commercial incentive for vendors to make it easier for users to change database suppliers (see vendor lock-in).
- Users who evaluate database software tend to place other factors such as higher performance in their priorities than standard conformance.
SQL was adopted as standard by the American National Standards Institute (ANSI) in 1986 as SQL-86 and International Organization for Standardization (ISO) in 1987. It is managed by ISO/IEC JTC 1, Information Technology, Subcommittee SC 32, Management and data exchange ISO/IEC 9075-n: yyyy Section n: title , or, as a shortcut, ISO/IEC 9075 .
ISO/IEC 9075 is equipped with ISO/IEC 13249: SQL Multimedia and Application Packages (SQL/MM), which defines SQL-based interfaces and packages for widespread applications such as video , audio and spatial data.
Until 1996, the National Institute of Standards and Technology (NIST) standardized data management program certified SQL DBMS compliance with SQL standards. Vendors now certify their own product compliance.
The original standard states that the official pronunciation for "SQL" is the initialism: ("ess cue el"). In spite of this, many English-language database professionals (including Donald Chamberlin himself) used a sponge-like pronunciation ("sequel"), reflecting the name of the pre-release development of the "SEQUEL" language. The SQL standard has gone through a number of revisions:
Interested parties can purchase standard SQL documents from ISO, IEC or ANSI. The SQL draft: 2008 is available for free as a zip archive.
The SQL standard is divided into nine sections.
- ISO/IEC 9075-1: 2016 Part 1: Template (SQL/Templates). It provides a logical concept.
- ISO/IEC 9075-2: 2016 Part 2: Foundation (SQL/Foundation). It contains the most central elements of the language and consists of both mandatory and optional features.
- ISO/IEC 9075-3: 2016 Part 3: Call Level Interface (SQL/CLI). It defines the interfacing components (structures, procedures, bindings of variables) that can be used to execute SQL statements from applications written in Ada, C respectively C, COBOL, Fortran, MUMPS, Pascal or PL/I. (For Java see section 10.) SQL/CLI is defined in such a way that SQL statements and call SQL/CLI procedures are treated as separate from the call source application's code. Open Database Connectivity is a famous superset of SQL/CLI. This section of the standard consists only of the required feature.
- ISO/IEC 9075-4: 2016 Part 4: Persistent stored module (SQL/PSM). It standardizes procedural extensions for SQL, including control streams, handling conditions, signal condition and resignation statements, cursors and local variables, and assigning expressions to variables and parameters. In addition, SQL/PSM formalizes declarations and maintenance of persistent database routines (eg, "stored procedures"). This section of the standard consists only of the optional feature.
- Part 6: Support for JavaScript Objects Notation (JSON) . In 2017 ISO/IEC publishes the first technical report on efforts to integrate JSON data types into SQL standards. Please consider that technical reports reflect the current state of the discussion and are not part of the standard.
- ISO/IEC 9075-9: 2016 Part 9: External Data Management (SQL/MED). It provides extensions for SQL that define foreign data wrappers and datalink types to allow SQL to manage external data. External data is data that can be accessed, but not managed by, SQL-based DBMS. This section of the standard consists only of the optional feature.
- ISO/IEC 9075-10: 2016 Part 10: Language object binding (SQL/OLB). It defines SQLJ syntax and semantics, which SQL is embedded in Java (see also section 3). This standard also describes mechanisms to ensure binary portability of SQLJ applications, and specifies the various Java packages and classes contained. This section of the standard consists only of the optional feature. Unlike SQL/OLB the JDBC defines the API and is not part of the SQL standard.
- ISO/IEC 9075-11: 2016 Part 11: Information and definition schemes (SQL/Schemata). It defines the Information Scheme and the Definition Schema, provides a common set of tools for creating SQL databases and self-describing objects. These tools include SQL object identifiers, structure and integrity constraints, security and authorization specifications, features and packages ISO/IEC 9075, feature support provided by SQL-based DBMS implementations, SQL-based DBMS implementation information and item sizes, and values which is supported by DBMS implementation. Part of this standard contains mandatory and optional features .
- ISO/IEC 9075-13: 2016 Part 13: SQL routines and types using the Java TM programming language (SQL/JRT). It specifies the ability to call static Java methods as routines from within a SQL application ('Java-in-the-database'). It also requires the ability to use Java classes as a user-defined structured SQL type. This section of the standard consists only of the optional feature.
- ISO/IEC 9075-14: 2016 Part 14: XML-Related Specification (SQL/XML). It specifies SQL-based extensions to use XML along with SQL. The XML data types are introduced, as well as some of the routines, functions, and mapping of XML-to-SQL data types to support the manipulation and storage of XML in SQL databases. This section of the standard consists only of the optional feature.
ISO/IEC 9075 is equipped with ISO/IEC 13249 SQL Multimedia and Application Packages . These closely-linked but separate standards are developed by the same committee. It defines interfaces and packages based on SQL. The goal is integrated access to typical database applications such as text, images, data mining or spatial data.
- ISO/IEC 13249-1: 2016 Part 1: Templates
- ISO/IEC 13249-2: 2003 Part 2: Full Text
- ISO/IEC 13249-3: 2016 Part 3: Spatial
- ISO/IEC 13249-5: 2003 Section 5: Still images
- ISO/IEC 13249-6: 2006 Section 6: Data excavation
- ISO/IEC 13249-7: 2013 Section 7: History
- ISO/IEC 13249-8: xxxx Part 8: Metadata Registry Access MRA (in progress)
Alternative
Differences must be made between alternatives to SQL as a language, and alternatives to the relational model itself. Below is proposed a relational alternative to the SQL language. View the navigation database and NoSQL for alternatives to the relational model.
- .QL: Object-oriented Datalog
- Query Language 4D (4D QL)
- BQL: a compiled superset to SQL
- Datalog: critics point out that Datalog has two advantages over SQL: it has a cleaner semantic, which facilitates program understanding and maintenance, and is more expressive, especially for recursive questions.
- HTSQL: URL-based query method IBM Business System 12 (IBM BS12): one of the first fully relational database management systems, introduced in 1982
- ISBL
- jOOQ: SQL is implemented in Java as a domain-specific language
- Java Persistence Query Language (JPQL): The query language used by the Java Persistence API and Hibernate persistency libraries
- LINQ: Runs a written SQL statement like a language construct to request a collection directly from within. Code clean.
- Object Request Language
- QBE (Query By Example) created by MoshÃÆ'¨ Zloof, IBM 1977
- Quel was introduced in 1974 by U.C. Berkeley Ingres Project.
- Tutorial D
- XQuery distributed SQL processing
- Comparison of object-relational database management systems
- Comparison of relational database management system
- D (data language specification)
- D4 (programming language)
- Hierarchical model
- List of relational database management systems
- MUMPS
- NoSQL
- Transact-SQL
- Online analytical processing (OLAP)
- Online transaction processing (OLTP)
- Data warehouse Relational data flow management system
- Star scheme
- Snowflake Scheme
- SQL: draft 2011
- SQL-92 draft
- 1995 SQL Reunion: People, Projects, and Politics , by Paul McJones (ed.): transcripts of reunion meetings aimed at the personal history of relational databases and SQL.
- The American National Standards Institute. X3H2 Records, 1978-1995 Charles Babbage Institute Collection documents the development of the H2 committee on NDL and SQL standards.
- Oral history interview with Donald D. Chamberlin Charles Babbage Institute In this oral history, Chamberlin recounts his early life, his education at Harvey Mudd College and Stanford University, and his work on relational database technology. Chamberlin is a member of the R System research team and, with Raymond F. Boyce, developed the SQL database language. Chamberlin also briefly discusses his more recent study of XML query languages.
- Comparison of Different SQL Implementations Comparison of various SQL implementations is intended to serve as a guide for those interested in porting SQL code between various RDBMS products, and includes a comparison between SQL: 2008, PostgreSQL, DB2, MS SQL Server, MySQL, Oracle , and Informix.
- Process event stream with SQL - Introduction to real-time stream data processing with continuous SQL queries
- BNF Grammar for ISO/IEC 9075: 2003, part 2 SQL/Framework
Distributed Relational Database Architecture (DRDA) was designed by a working group within IBM from 1988 to 1994. DRDA allows connected network relational databases to work together to meet SQL requests.
Users or interactive programs can issue SQL statements to local RDBs and receive data tables and status indicators in response from remote RDBs. SQL statements can also be compiled and stored in RDB remotely as packets and then called by package names. This is important for efficient operation of application programs that issue complex, high frequency questions. This is especially important when the tables to be accessed are on the remote system.
Messages, protocols, and structural components of the DRDA are defined by the Distributed Data Management Architecture.
Criticism
Chamberlin's critique of SQL includes:
Orgogonality and completeness
The initial specification does not support the main feature, like the primary key. The result set can not be named, and the subquery has not been specified. It was added in 1992.
Nulls
The controversial "null" value of SQL is incorrect or false (predicates with terms that return a null value returning null instead of true or false). Features such as outer-join depend on the null value.
More
Another popular criticism is to allow duplicate rows, integrate with languages ​​like Python, whose data types are not the same, difficulty parse and absence of modularity.
See also
Note
References
Source
standard SQL document
ITTF standard and publicly available technical reports
The IT/IEC Information Technology Task Force publishes publicly available standards including SQL. Technical Corrections (Corrections) and Technical Reports (discussion documents) are published there.
SQL - Part 1: The Framework (SQL/Framework)
Draft document
Formal SQL standards are available from ISO and ANSI for a fee. For informative use, as opposed to strict compliance with standards, late drafts are often sufficient.
External links
Source of the article : Wikipedia
0 Comments