SQL: Difference between revisions

From NovaOrdis Knowledge Base
Jump to navigation Jump to search
(80 intermediate revisions by the same user not shown)
Line 1: Line 1:
=External=
* [https://learning.oreilly.com/library/view/learning-sql-3rd/9781492057604 Learning SQL, 3rd Edition] by Alan Beaulieu
=Internal=
=Internal=
* [[Relational Databases]]
* [[Relational Databases]]
Line 5: Line 8:


=Overview=
=Overview=
This article documents standard SQL. For database-specific extensions, we'll link to the databases-specific DML operations page.
SQL is a standard non-procedural language designed to work with relational databases, allowing the users to interact with the database instance by issuing [[#SQL_Schema_Statements_(DDL)|schema statements]], [[#SQL_Data_Statements_(DML)|data statements]] and [[#SQL_Transaction_Statements|transaction statements]].
SQL statements define the necessary inputs and outputs for the database interaction, but it does not define how the statements should be executed. The details of the execution are left to the database engine component known as the optimizer. The optimizer parses the SQL statements and depending on how the tables are configured and what [[SQL_Indexes#Overview|indexes]] exist, it decides the most efficient execution path. Most database engines will allow you to influence the optimizer's decisions by accepting optimizer hints.
The first standard for the SQL language was published by the American National Standards Institute (ANSI) in 1986. Subsequent refinements led to new releases of the SQL standard in 1989, 1992, 1999, 2003, 2006, 2008, 2011, and 2016.
=<span id='Table'></span>Relational Database Terminology=
In a relational model, data is stored in a set of [[Relational_Databases#Table_(Relation)|tables]], also known as [[Relational_Databases#Table_(Relation)|relations]]. Within a relation, data is organized in [[Relational_Databases#Row|rows]] ([[Relational_Databases#Record_(Row)|records]]) and [[Relational_Databases#Column|columns]]. Each row describes an [[Relational_Databases#Entity|entity]]. Each is uniquely identified by a [[Relational_Databases#Primary_Key|primary key]]. The rows may contain columns that are copies of other tables' primary keys. These are knowns as [[Relational_Databases#Foreign_Key|foreign keys]] and are used in [[SQL_Querying_Multiple_Tables#Join|joining]] related data together.
Also see: {{Internal|Relational_Databases#Table|Relational Databases}}
==Result Set==
The result of an [[SQL_SELECT#Overview|SQL query]], known as a '''result set''', is also a [[Relational_Databases#Table_(Relation)|table]] maintained in memory.
==View==
=SQL Schema Statements (DDL)=
SQL schema statements are used to define the data structures stored in the database. This SQL subset is also known as Data Definition Language (DDL). All database elements (tables, constraints, etc.) create with the SQL schema statements are stored in a special set of table called the '''data dictionary''', or '''metadata'''. Also see: {{Internal|SQL_Database_Metadata#Overview|Database Metadata}}
==<span id='CADT'></span><tt>CREATE | ALTER | DROP TABLE</tt>==
{{Internal|SQL_CREATE_ALTER_DROP_TABLE#Overview|<tt>CREATE</tt> &#124; <tt>ALTER</tt> &#124; <tt>DROP</tt> <tt>TABLE</tt>}}
=SQL Data Statements (DML)=
SQL data statements are used to manipulate the data structures perviously defined using the [[#SQL_Schema_Statements_(DDL)|SQL schema statements]]. This SQL subset is also known as Data Manipulation Language (DML).
==<tt>INSERT</tt>==
{{Internal|SQL_INSERT#Overview|<tt>INSERT</tt>}}
==<tt>UPDATE</tt>==
{{Internal|SQL_UPDATE#Overview|<tt>UPDATE</tt>}}
==<tt>DELETE</tt>==
{{Internal|SQL_DELETE#Overview|<tt>DELETE</tt>}}
==<span id='SELECT'></span>Queries with <tt>SELECT</tt>==
{{Internal|SQL_SELECT#Overview|Queries with <tt>SELECT</tt>}}
=SQL Transaction Statements=
The SQL transaction statements are used to begin, end, and rollback transactions.
{{Internal|SQL_Transaction_Statements#Overview|Transaction Statements}}


=JOIN=
=Standard SQL Data Types=
{{Internal|SQL_Data_Types#Overview|Standard SQL Data Types}}


The SQL <code>JOIN</code> clause is used to combine rows from two or more tables, based on a related column between them.
=SQL <tt>NULL</tt>=
<font color=darkkhaki>
<code>NULL</code> in conditions.
</font>
=Indexes=
{{Internal|SQL_Indexes#Overview|SQL Indexes}}


==INNER JOIN==
=Constraints=
Return records that have matching values in both tables.
=Set Operations=
==LEFT (OUTER) JOIN==
=SQL Conditional Logic=
Return all records from the left table and the matching records from the right table.
==RIGHT (OUTER) JOIN==
Return all records from the right table and the matching records from the left table.
==FULL (OUTER) JOIN==
Returns all records when there is a match in either left or right table.

Revision as of 23:06, 22 May 2024

External

Internal

Overview

This article documents standard SQL. For database-specific extensions, we'll link to the databases-specific DML operations page.

SQL is a standard non-procedural language designed to work with relational databases, allowing the users to interact with the database instance by issuing schema statements, data statements and transaction statements.

SQL statements define the necessary inputs and outputs for the database interaction, but it does not define how the statements should be executed. The details of the execution are left to the database engine component known as the optimizer. The optimizer parses the SQL statements and depending on how the tables are configured and what indexes exist, it decides the most efficient execution path. Most database engines will allow you to influence the optimizer's decisions by accepting optimizer hints.

The first standard for the SQL language was published by the American National Standards Institute (ANSI) in 1986. Subsequent refinements led to new releases of the SQL standard in 1989, 1992, 1999, 2003, 2006, 2008, 2011, and 2016.

Relational Database Terminology

In a relational model, data is stored in a set of tables, also known as relations. Within a relation, data is organized in rows (records) and columns. Each row describes an entity. Each is uniquely identified by a primary key. The rows may contain columns that are copies of other tables' primary keys. These are knowns as foreign keys and are used in joining related data together.

Also see:

Relational Databases

Result Set

The result of an SQL query, known as a result set, is also a table maintained in memory.

View

SQL Schema Statements (DDL)

SQL schema statements are used to define the data structures stored in the database. This SQL subset is also known as Data Definition Language (DDL). All database elements (tables, constraints, etc.) create with the SQL schema statements are stored in a special set of table called the data dictionary, or metadata. Also see:

Database Metadata

CREATE | ALTER | DROP TABLE

CREATE | ALTER | DROP TABLE

SQL Data Statements (DML)

SQL data statements are used to manipulate the data structures perviously defined using the SQL schema statements. This SQL subset is also known as Data Manipulation Language (DML).

INSERT

INSERT

UPDATE

UPDATE

DELETE

DELETE

Queries with SELECT

Queries with SELECT

SQL Transaction Statements

The SQL transaction statements are used to begin, end, and rollback transactions.

Transaction Statements

Standard SQL Data Types

Standard SQL Data Types

SQL NULL

NULL in conditions.

Indexes

SQL Indexes

Constraints

Set Operations

SQL Conditional Logic