SQL (/ˌɛsˌkjuːˈɛl/ (About this soundlisten) S-Q-L,[4] /ˈsiːkwəl/ "sequel"; Structured Concern Language)[5][6][7] is a domain-specific accent acclimated in programming and advised for managing abstracts captivated in a relational database administration arrangement (RDBMS), or for beck processing in a relational abstracts beck administration arrangement (RDSMS). It is decidedly advantageous in administration structured data, i.e. abstracts accumulation relations amid entities and variables.
SQL offers two capital advantages over earlier read–write APIs such as ISAM or VSAM. Firstly, it alien the abstraction of accessing abounding annal with one distinct command. Secondly, it eliminates the charge to specify how to adeptness a record, e.g. with or afterwards an index.
Originally based aloft relational algebra and tuple relational calculus, SQL consists of abounding types of statements,[8] which may be artlessly classed as sublanguages, commonly: a abstracts concern accent (DQL),[a] a abstracts analogue accent (DDL),[b] a abstracts ascendancy accent (DCL), and a abstracts abetment accent (DML).[c][9] The ambit of SQL includes abstracts query, abstracts abetment (insert, amend and delete), abstracts analogue (schema conception and modification), and abstracts admission control. Although SQL is about a allegorical accent (4GL), it additionally includes procedural elements.
SQL was one of the aboriginal bartering languages to advance Edgar F. Codd’s relational model. The archetypal was declared in his affecting 1970 paper, "A Relational Archetypal of Abstracts for Ample Shared Abstracts Banks".[10] Despite not absolutely adhering to the relational archetypal as declared by Codd, it became the best broadly acclimated database language.[11][12]
SQL became a accepted of the American National Standards Institute (ANSI) in 1986, and of the International Organization for Standardization (ISO) in 1987.[13] Since again the accepted has been revised to accommodate a beyond set of features. Despite the actuality of standards, best SQL cipher requires at atomic some changes afore actuality ported to altered database systems. History
SQL was initially developed at IBM by Donald D. Chamberlin and Raymond F. Boyce afterwards acquirements about the relational archetypal from Edgar F. Codd[14] in the aboriginal 1970s.[15] This version, initially alleged SEQUEL (Structured English Concern Language), was advised to dispense and retrieve abstracts stored in IBM's aboriginal quasi-relational database administration system, Arrangement R, which a accumulation at IBM San Jose Research Laboratory had developed during the 1970s.[15]
Chamberlin and Boyce's aboriginal attack at a relational database accent was Square, but it was difficult to use due to subscript notation. Afterwards affective to the San Jose Research Laboratory in 1973, they began appointment on SEQUEL.[14] The acronym SEQUEL was after afflicted to SQL because "SEQUEL" was a brand of the UK-based Hawker Siddeley Dynamics Engineering Limited company.[16]
After testing SQL at chump analysis sites to actuate the account and acumen of the system, IBM began developing bartering articles based on their Arrangement R ancestor including System/38, SQL/DS, and DB2, which were commercially attainable in 1979, 1981, and 1983, respectively.[17]
In the astern 1970s, Relational Software, Inc. (now Oracle Corporation) saw the abeyant of the concepts declared by Codd, Chamberlin, and Boyce, and developed their own SQL-based RDBMS with aspirations of affairs it to the U.S. Navy, Axial Intelligence Agency, and added U.S. government agencies. In June 1979, Relational Software, Inc. alien the aboriginal commercially attainable accomplishing of SQL, Oracle V2 (Version2) for VAX computers.
By 1986, ANSI and ISO accepted groups clearly adopted the accepted "Database Accent SQL" accent definition. New versions of the accepted were appear in 1989, 1992, 1996, 1999, 2003, 2006, 2008, 2011[14] and, best recently, 2016.[citation needed]
Syntax
Main article: SQL syntax
U P D A T E c l a u s e { U P D A T E c o u n t r y S E T c l a u s e { S E T p o p u l a t i o n = p o p u l a t i o n + 1 ⏞ e x p r e s s i o n W H E R E c l a u s e { W H E R E n a m e = ′ U S A ′ ⏞ e x p r e s s i o n ⏟ p r e d i c a t e ; } account {\displaystyle \left.{\begin{array}{rl}\textstyle {\mathtt {UPDATE~clause}}&\{{\mathtt {UPDATE\ country}}\\\textstyle {\mathtt {SET~clause}}&\{{\mathtt {SET\ population=~}}\overbrace {\mathtt {population+1}} ^{\mathtt {expression}}\\\textstyle {\mathtt {WHERE~clause}}&\{{\mathtt {WHERE\ \underbrace {{name=}\overbrace {'USA'} ^{expression}} _{predicate};}}\end{array}}\right\}{\textstyle {\texttt {statement}}}} {\displaystyle \left.{\begin{array}{rl}\textstyle {\mathtt {UPDATE~clause}}&\{{\mathtt {UPDATE\ country}}\\\textstyle {\mathtt {SET~clause}}&\{{\mathtt {SET\ population=~}}\overbrace {\mathtt {population+1}} ^{\mathtt {expression}}\\\textstyle {\mathtt {WHERE~clause}}&\{{\mathtt {WHERE\ \underbrace {{name=}\overbrace {'USA'} ^{expression}} _{predicate};}}\end{array}}\right\}{\textstyle {\texttt {statement}}}}
A blueprint assuming several of the SQL accent elements that compose a distinct statement
The SQL accent is subdivided into several accent elements, including:
Clauses, which are basal apparatus of statements and queries. (In some cases, these are optional.)[18]
Expressions, which can aftermath either scalar values, or tables consisting of columns and rows of data
Predicates, which specify altitude that can be evaluated to SQL three-valued argumentation (3VL) (true/false/unknown) or Boolean accuracy ethics and are acclimated to absolute the furnishings of statements and queries, or to change affairs flow.
Queries, which retrieve the abstracts based on specific criteria. This is an important aspect of SQL.
Statements, which may accept a assiduous aftereffect on schemata and data, or may ascendancy transactions, affairs flow, connections, sessions, or diagnostics.
SQL statements additionally accommodate the semicolon (";") account terminator. Admitting not adapted on every platform, it is authentic as a accepted allotment of the SQL grammar.
Insignificant whitespace is about abandoned in SQL statements and queries, authoritative it easier to architecture SQL cipher for readability.
Procedural extensions
SQL is advised for a specific purpose: to concern abstracts independent in a relational database. SQL is a set-based, allegorical programming language, not an acute programming accent like C or BASIC. However, extensions to Accepted SQL add procedural programming accent functionality, such as control-of-flow constructs. These include:
Source Abbreviation Full name
ANSI/ISO Accepted SQL/PSM SQL/Persistent Stored Modules
Interbase / Firebird PSQL Procedural SQL
IBM DB2 SQL PL SQL Procedural Accent (implements SQL/PSM)
IBM Informix SPL Stored Procedural Language
IBM Netezza NZPLSQL[19] (based on Postgres PL/pgSQL)
Invantive PSQL[20] Invantive Procedural SQL (implements SQL/PSM and PL/SQL)
MariaDB SQL/PSM, PL/SQL SQL/Persistent Stored Module (implements SQL/PSM), Procedural Language/SQL (based on Ada)[21]
Microsoft / Sybase T-SQL Transact-SQL
Mimer SQL SQL/PSM SQL/Persistent Stored Module (implements SQL/PSM)
MySQL SQL/PSM SQL/Persistent Stored Module (implements SQL/PSM)
MonetDB SQL/PSM SQL/Persistent Stored Module (implements SQL/PSM)
NuoDB SSP Starkey Stored Procedures
Oracle PL/SQL Procedural Language/SQL (based on Ada)
PostgreSQL PL/pgSQL Procedural Language/PostgreSQL Structured Concern Accent (based on bargain PL/SQL)
SAP R/3 ABAP Advanced Business Appliance Programming
SAP HANA SQLScript SQLScript
Sybase Watcom-SQL SQL Anywhere Watcom-SQL Dialect
Teradata SPL Stored Procedural Language
In accession to the accepted SQL/PSM extensions and proprietary SQL extensions, procedural and acquisitive programmability is attainable on abounding SQL platforms via DBMS affiliation with added languages. The SQL accepted defines SQL/JRT extensions (SQL Routines and Types for the Java Programming Language) to abutment Java cipher in SQL databases. Microsoft SQL Server 2005 uses the SQLCLR (SQL Server Accepted Accent Runtime) to host managed .NET assemblies in the database, while above-mentioned versions of SQL Server were belted to unmanaged continued stored procedures primarily accounting in C. PostgreSQL lets users address functions in a advanced arrangement of languages—including Perl, Python, Tcl, JavaScript (PL/V8) and C.[22]
Interoperability and standardization
Overview
SQL implementations are adverse amid vendors and do not necessarily absolutely chase standards. In accurate date and time syntax, cord concatenation, NULLs, and allegory case acuteness alter from bell-ringer to vendor. Accurate exceptions are PostgreSQL[23] and Mimer SQL[24] which strive for standards compliance, admitting PostgreSQL does not attach to the accepted in how folding of unquoted names is done. The folding of unquoted names to lower case in PostgreSQL is adverse with the SQL standard,[25] which says that unquoted names should be bankrupt to high case.[26] Thus, Foo should be agnate to FOO not foo according to the standard.
Popular implementations of SQL frequently omit abutment for basal appearance of Accepted SQL, such as the DATE or TIME abstracts types. The best attainable such examples, and incidentally the best accepted bartering and proprietary SQL DBMSs, are Oracle (whose DATE behaves as DATETIME,[27][28] and lacks a TIME type)[29] and MS SQL Server (before the 2008 version). As a result, SQL cipher can rarely be ported amid database systems afterwards modifications.
Reasons for incompatibility
There are several affidavit for this abridgement of portability amid database systems:
The complication and admeasurement of the SQL accepted agency that best implementors do not abutment the absolute standard.
The accepted does not specify database behavior in several important areas (e.g. indexes, book storage...), abrogation implementations to adjudge how to behave.
The SQL accepted absolutely specifies the syntax that a befitting database arrangement charge implement. However, the standard's blueprint of the semantics of accent constructs is beneath well-defined, arch to ambiguity.
Abounding database vendors accept ample absolute chump bases; area the newer adaptation of the SQL accepted conflicts with the above-mentioned behavior of the vendor's database, the bell-ringer may be afraid to breach astern compatibility.
There is little bartering allurement for vendors to accomplish it easier for users to change database suppliers (see bell-ringer lock-in).
Users evaluating database software tend to abode added factors such as achievement college in their priorities than standards conformance.
Standardization history
SQL was adopted as a accepted by the American National Standards Institute (ANSI) in 1986 as SQL-86[30] and the International Organization for Standardization (ISO) in 1987.[13] It is maintained by ISO/IEC JTC 1, Advice technology, Subcommittee SC 32, Abstracts administration and interchange.
Until 1996, the National Institute of Standards and Technology (NIST) abstracts administration standards affairs certified SQL DBMS acquiescence with the SQL standard. Vendors now self-certify the acquiescence of their products.[31]
The aboriginal accepted declared that the official accentuation for "SQL" was an initialism: /ˌɛsˌkjuːˈɛl/ ("ess cue el").[11] Regardless, abounding English-speaking database professionals (including Donald Chamberlin himself[32]) use the acronym-like accentuation of /ˈsiːkwəl/ ("sequel"),[33] apery the language's pre-release development name, "SEQUEL".[15][16][32]
The SQL accepted has gone through a cardinal of revisions:
Year Name Alias Comments
1986 SQL-86 SQL-87 First formalized by ANSI.
1989 SQL-89 FIPS 127-1 Minor afterlight that added candor constraints, adopted as FIPS 127-1.
1992 SQL-92 SQL2, FIPS 127-2 Major afterlight (ISO 9075), Entry Level SQL-92 adopted as FIPS 127-2.
1999 SQL:1999 SQL3 Added approved announcement matching, recursive queries (e.g. transitive closure), triggers, abutment for procedural and control-of-flow statements, non-scalar types (arrays), and some acquisitive appearance (e.g. structured types). Abutment for embedding SQL in Java (SQL/OLB) and carnality versa (SQL/JRT).
2003 SQL:2003 Introduced XML-related appearance (SQL/XML), window functions, affiliated sequences, and columns with auto-generated ethics (including identity-columns).
2006 SQL:2006 ISO/IEC 9075-14:2006 defines means that SQL can be acclimated with XML. It defines means of importing and autumn XML abstracts in an SQL database, manipulating it aural the database, and publishing both XML and accepted SQL-data in XML form. In addition, it lets applications accommodate queries into their SQL cipher with XQuery, the XML Concern Accent appear by the World Advanced Web Consortium (W3C), to accordingly admission accustomed SQL-data and XML documents.[34]
2008 SQL:2008 Legalizes ORDER BY alfresco cursor definitions. Adds INSTEAD OF triggers, TRUNCATE statement,[35] FETCH clause.
2011 SQL:2011 Adds banausic abstracts (PERIOD FOR)[36] (more advice at: Banausic database#History). Enhancements for window functions and FETCH clause.[37]
2016 SQL:2016 Adds row arrangement matching, polymorphic table functions, JSON.
2019 SQL:2019 Adds Allotment 15, multidimensional arrays (MDarray blazon and operators).
Current standard
The accepted is frequently denoted by the pattern: ISO/IEC 9075-n:yyyy Allotment n: title, or, as a shortcut, ISO/IEC 9075.
ISO/IEC 9075 is complemented by ISO/IEC 13249: SQL Multimedia and Appliance Bales (SQL/MM), which defines SQL based interfaces and bales to broadly advance applications like video, audio and spatial data. Interested parties may acquirement SQL standards abstracts from ISO,[38] IEC or ANSI. A abstract of SQL:2008 is advisedly attainable as a zip archive.[39]
Anatomy of SQL Standard
The SQL accepted is disconnected into ten parts. There are gaps in the calculation due to the abandonment of anachronous parts.
ISO/IEC 9075-1:2016 Allotment 1: Framework (SQL/Framework). It provides analytic concepts.[40]
ISO/IEC 9075-2:2016 Allotment 2: Foundation (SQL/Foundation). It contains the best axial elements of the accent and consists of both binding and alternative features.
ISO/IEC 9075-3:2016 Allotment 3: Call-Level Interface (SQL/CLI). It defines interfacing apparatus (structures, procedures, capricious bindings) that can be acclimated to assassinate SQL statements from applications accounting in Ada, C appropriately C++, COBOL, Fortran, MUMPS, Pascal or PL/I. (For Java see allotment 10.) SQL/CLI is authentic in such a way that SQL statements and SQL/CLI action calls are advised as abstracted from the calling application's antecedent code. Accessible Database Connectivity is a acclaimed superset of SQL/CLI. This allotment of the accepted consists alone of binding features.
ISO/IEC 9075-4:2016 Allotment 4: Assiduous stored modules (SQL/PSM). It standardizes procedural extensions for SQL, including breeze of control, action handling, account action signals and resignals, cursors and bounded variables, and appointment of expressions to variables and parameters. In addition, SQL/PSM formalizes acknowledgment and aliment of assiduous database accent routines (e.g., "stored procedures"). This allotment of the accepted consists alone of alternative features.
ISO/IEC 9075-9:2016 Allotment 9: Administration of Alien Abstracts (SQL/MED). It provides extensions to SQL that ascertain foreign-data wrappers and datalink types to acquiesce SQL to administer alien data. Alien abstracts is abstracts that is attainable to, but not managed by, an SQL-based DBMS. This allotment of the accepted consists alone of alternative features.
ISO/IEC 9075-10:2016 Allotment 10: Article accent bindings (SQL/OLB). It defines the syntax and semantics of SQLJ, which is SQL anchored in Java (see additionally allotment 3). The accepted additionally describes mechanisms to ensure bifold portability of SQLJ applications, and specifies assorted Java bales and their independent classes. This allotment of the accepted consists alone of alternative features. Unlike SQL/OLB JDBC defines an API and is not allotment of the SQL standard.[citation needed]
ISO/IEC 9075-11:2016 Allotment 11: Advice and analogue schemas (SQL/Schemata). It defines the Advice Action and Analogue Schema, accouterment a accepted set of accoutrement to accomplish SQL databases and altar self-describing. These accoutrement accommodate the SQL article identifier, anatomy and candor constraints, aegis and allotment specifications, appearance and bales of ISO/IEC 9075, abutment of appearance provided by SQL-based DBMS implementations, SQL-based DBMS accomplishing advice and allocation items, and the ethics accurate by the DBMS implementations.[41] This allotment of the accepted contains both binding and alternative features.
ISO/IEC 9075-13:2016 Allotment 13: SQL Routines and types appliance the Java TM programming accent (SQL/JRT). It specifies the adeptness to adjure changeless Java methods as routines from aural SQL applications ('Java-in-the-database'). It additionally calls for the adeptness to use Java classes as SQL structured user-defined types. This allotment of the accepted consists alone of alternative features.
ISO/IEC 9075-14:2016 Allotment 14: XML-Related Blueprint (SQL/XML). It specifies SQL-based extensions for appliance XML in affiliation with SQL. The XML abstracts blazon is introduced, as able-bodied as several routines, functions, and XML-to-SQL abstracts blazon mappings to abutment abetment and accumulator of XML in an SQL database.[34] This allotment of the accepted consists alone of alternative features.[citation needed]
ISO/IEC 9075-15:2019 Allotment 15: Multi-dimensional arrays (SQL/MDA). It specifies a multidimensional arrangement blazon (MDarray) for SQL, forth with operations on MDarrays, MDarray slices, MDarray cells, and accompanying features. This allotment of the accepted consists alone of alternative features.
Extensions to the ISO/IEC Standard
ISO/IEC 9075 is complemented by ISO/IEC 13249 SQL Multimedia and Appliance Packages. This carefully accompanying but abstracted accepted is developed by the aforementioned committee. It defines interfaces and bales based on SQL. The aim is a unified admission to archetypal database applications like text, pictures, abstracts mining or spatial data.
ISO/IEC 13249-1:2016 Allotment 1: Framework
ISO/IEC 13249-2:2003 Allotment 2: Full-Text
ISO/IEC 13249-3:2016 Allotment 3: Spatial
ISO/IEC 13249-5:2003 Allotment 5: Still image
ISO/IEC 13249-6:2006 Allotment 6: Abstracts mining
ISO/IEC 13249-7:2013 Allotment 7: History
ISO/IEC 13249-8:xxxx Allotment 8: Metadata Registry Admission MRA (work in progress)
Technical Reports
ISO/IEC 9075 is additionally accompanied by a alternation of Technical Reports, appear as ISO/IEC TR 19075 in 8 parts. These Technical Reports explain the absolution for and acceptance of some appearance of SQL, giving examples area appropriate. The Technical Reports are non-normative; if there is any alterity from 9075, the argument in 9075 holds. Currently attainable 19075 Technical Reports are:
ISO/IEC TR 19075-1:2011 Allotment 1: XQuery Approved Announcement Abutment in SQL
ISO/IEC TR 19075-2:2015 Allotment 2: SQL Abutment for Time-Related Information
ISO/IEC TR 19075-3:2015 Allotment 3: SQL Anchored in Programs appliance the Java programming language
ISO/IEC TR 19075-4:2015 Allotment 4: SQL with Routines and types appliance the Java programming language
ISO/IEC TR 19075-5:2016 Allotment 5: Row Arrangement Recognition in SQL
ISO/IEC TR 19075-6:2017 Allotment 6: SQL abutment for JavaScript Article Characters (JSON)
ISO/IEC TR 19075-7:2017 Allotment 7: Polymorphic table functions in SQL
ISO/IEC TR 19075-8:2019 Allotment 8: Multi-Dimensional Arrays (SQL/MDA)
Alternatives
A acumen should be fabricated amid alternatives to SQL as a language, and alternatives to the relational archetypal itself. Below are proposed relational alternatives to the SQL language. See abyssal database and NoSQL for alternatives to the relational model.
.QL: acquisitive Datalog
4D Concern Accent (4D QL)
Datalog: critics advance that Datalog has two advantages over SQL: it has cleaner semantics, which facilitates affairs compassionate and maintenance, and it is added expressive, in accurate for recursive queries.[42]
HTSQL: URL based concern method
IBM Business Arrangement 12 (IBM BS12): one of the aboriginal absolutely relational database administration systems, alien in 1982
ISBL
jOOQ: SQL implemented in Java as an centralized domain-specific language
Java Chain Concern Accent (JPQL): The concern accent acclimated by the Java Chain API and Hibernate chain library
JavaScript: MongoDB accouterments its concern accent in a JavaScript API.
LINQ: Runs SQL statements accounting like accent constructs to concern collections anon from central .Net code.
Article Concern Language
QBE (Query By Example) created by Moshè Zloof, IBM 1977
Quel alien in 1974 by the U.C. Berkeley Ingres project.
Tutorial D
XQuery
Distributed SQL processing
Distributed Relational Database Architecture (DRDA) was advised by a appointment accumulation aural IBM in the aeon 1988 to 1994. DRDA enables arrangement affiliated relational databases to abet to accomplish SQL requests.[43][44]
An alternate user or affairs can affair SQL statements to a bounded RDB and accept tables of abstracts and cachet indicators in acknowledgment from alien RDBs. SQL statements can additionally be aggregate and stored in alien RDBs as bales and again invoked by amalgamation name. This is important for the able operation of appliance programs that affair complex, high-frequency queries. It is abnormally important back the tables to be accessed are amid in alien systems.
The messages, protocols, and structural apparatus of DRDA are authentic by the Distributed Abstracts Administration Architecture.
Criticisms
Design
SQL deviates in several means from its abstract foundation, the relational archetypal and its tuple calculus. In that model, a table is a set of tuples, while in SQL, tables and concern after-effects are lists of rows: the aforementioned row may action assorted times, and the adjustment of rows can be active in queries (e.g. in the LIMIT clause).
Critics altercate that SQL should be replaced with a accent that allotment carefully to the aboriginal foundation: for example, see The Third Manifesto. However, no accepted affidavit exists that such character cannot be added to SQL itself,[45] or at atomic a aberration of SQL. In added words, it's absolutely accessible that SQL can be "fixed" or at atomic bigger in this attention such that the industry may not accept to about-face to a absolutely altered concern accent to access uniqueness. Debate on this charcoal open.
Other criticisms
Chamberlin discusses four actual criticisms of SQL in a 2012 paper:[14]
Orthogonality and completeness
Early blueprint did not abutment aloft features, such as primary keys. Aftereffect sets could not be named, and sub-queries had not been defined. These were added in 1992.[14]
Null
The abstraction of Null is the accountable of some debates. The Null brand indicates that there is no value, alike no 0 for an accumulation cavalcade or a cord of breadth 0 for a argument column. The abstraction of Nulls enforces the 3-valued-logic in SQL, which is a accurate accomplishing of the accepted 3-valued logic.
Duplicates
Another accepted criticism is that it allows alike rows, authoritative affiliation with languages such as Python, whose abstracts types ability accomplish it difficult to accurately represent the data,[14] difficult in agreement of parsing and by the absence of modularity.[46]
This can be abhorred declaring a different coercion with one or added fields that identifies abnormally a row in the table. That coercion could additionally become the primary key of the table.
Impedance mismatch
In a agnate faculty to Object-relational impedance mismatch, there is a conflict amid the allegorical SQL accent and the procedural languages that SQL is about anchored in.
Data Candor Categories
Main abstracts candor categories of anniversary RDBMS.
Entity integrity
Establishes that aural the table the primary key has a different amount for anniversary row, blockage the character of the amount of the primary key alienated that there are bifold rows in a table.
Domain integrity
Restricts the type, format, and amount ambit that applies to accurate entries for a cavalcade aural a table
Referential integrity
Makes rows in a table that are actuality acclimated by added annal absurd to delete
User-defined integrity
Other specific rules not included aloft apply
SQL abstracts types
The SQL accepted defines three kinds of abstracts types:
predefined abstracts types
complete types
user-defined types.
Predefined abstracts types
Character Types
Character (CHAR)
Character Varying (VARCHAR)
Character Ample Article (CLOB)
Bifold Types
Bifold (BINARY)
Bifold Varying (VARBINARY)
Bifold Ample Article (BLOB)
Numeric Types
Exact Numeric Types (NUMERIC, DECIMAL, SMALLINT, INTEGER, BIGINT)
Approximate Numeric Types (FLOAT, REAL, DOUBLE PRECISION)
Datetime Types (DATE, TIME, TIMESTAMP)
Interval Blazon (INTERVAL)
Boolean
XML
JSON
Constructed types
Constructed types are one of ARRAY, MULTISET, REF(erence), or ROW.
User-defined types are commensurable to classes in acquisitive accent with their own constructors, observers, mutators, methods, inheritance, overloading, overwriting, interfaces, and so on.
No comments:
Post a Comment