Introduction

SQL (Structured Query Language) is both a DDL (Data Definition Language) and a DML (Data Manipulation Language)
As a DDL, SQL is used to define tables and views.
As a DML, SQL is used to select, insert, update, and delete data in a DBMS
First developed by IBM in the mid 1970s, SQL was adopted as a standard in 1986
Different implementations of SQL represent different 'dialects' - DB2, Sybase SQL, Oracle, MS SQL Server, etc.
All are based on ANSI (American National Standards Institute) and ISO (International Organization for Standardization)
SQL:2008 is is the sixth revision of the language
Benefits of SQL:
- no need for explicit locking statements (concurrency dealt with by SQL interpreter)
- no need to reference physical location of data
- no need to specify indexing or search strategy

Data Types

This is not an exhaustive list of SQL or MySQL data types, nor are the descriptions complete. Carefully read the full description of each type before using it in a database. Note that different dialects of SQL may have different data types or different defintions for data types with the same name.

Data Type	Description
String
Character comparisons ignore case unless the database is set to be case sensitive Character sets can be assigned to each text type. Note that multi-byte character sets can reduce the number of characters that can be stored in each type.
`CHAR(max-length)`	fixed length string, max-length <= 255 characters
`VARCHAR(max-length)` `TEXT`	variable length string, max-length <= 65,535 characters
`LONGTEXT`	a very large string with a maximum size of 4,294,967,295 or 4GB (2³²-1) characters
Numeric	Signed	Unsigned
`TINYINT` (1 byte)	-128 to 127	0 to 255
`SMALLINT` (2 bytes)	-32,768 to 32,767	0 to 65535
`INT` (4 bytes)	-2,147,483,647 to 2,147,483,647	0 to 4,294,967,295
`FLOAT`	-3.402823466E+38 to -1.175494351E-38	2.2250738585072014E-308 to 1.7976931348623157E+308
`DOUBLE`	-1.7976931348623157E+308 to -2.2250738585072014E-308	2.2250738585072014E-308 to 1.7976931348623157E+308
`DECIMAL [ ( precision [ , scale ] ) ]`	has precision total digits and scale decimal digits. Useful for the accurate storage of decimal numbers such as currency. (Guarantees scale decimal digits of accuracy).
Date and Time
Date and time representation as a string depends on the format. We recommend the ISO format of `YYYY-MM-DD HH:MM:SS` because it is unambiguous.
`DATE`	A calendar date with a year, month and day. The year can be from 1000 to 9999. The default format is YYYY-MM-DD.
`TIME`	Time of day, containing hour, minute, and second under a 24-hour clock. The default format is HH:MM:SS.
`DATETIME`	A combination of DATE and TIME. The default format is YYYY-MM-DD HH:MM:SS.
`TIMESTAMP`	A timestamp represents the number of seconds since `'1970-01-01 00:00:00' UTC`
Binary
`BINARY(max-length)`	fixed length binary, max-length <= 255 bytes
`VARBINARY(max-length)` `BLOB`	variable length binary, max-length <= 65,535 bytes
`LONGBLOB`	a very large binary with a maximum size of 4,294,967,295 or 4GB (2³²-1) bytes
`BOOL / BOOLEAN`	Synonyms for `TINYINT(1)`. A value of zero is considered false. Nonzero values are considered true.

TRUE/FALSE

The following discussion is with respect to the MySQL dialect of SQL, and may not be true in other dialects.

As noted in Data Types, the BOOL data type is not a true type, it is a synonym for TINYINT(1), i.e. a single byte. The named constant FALSE = 0, and the named constant TRUE = 1. A non-zero value is considered to be true for boolean purposes, but that is not necessarily the same as TRUE. The comparisons = and IS do not behave the same, depending on the value being compared. The following code demonstrates this:

      
SET @A = FALSE;      # assigns 0 to a system variable
SELECT @A IS FALSE;  # returns 1 (TRUE)
SELECT @A = FALSE;   # returns 1 (TRUE)
SET @B = TRUE;       # assigns 1 to a system variable
SELECT @B IS TRUE;   # returns 1 (TRUE)
SELECT @B = TRUE;    # returns 1 (TRUE)
SET @C = 99;         # assigns 99 (not FALSE) to a system variable
SELECT @C IS TRUE;   # returns 1 (TRUE)
SELECT @C = TRUE;    # returns 0 (FALSE)

Note in particular the final result. 99 is 'true' in the boolean sense in that it is non-zero, but it is not the same as TRUE because 99 != 1.

The following comparison code should work in all dialects of SQL:

      
SELECT @C = 0;       # returns 0 (FALSE)
SELECT @C <> 0;      # returns 1 (TRUE)
SELECT @C != 0;      # returns 1 (TRUE)

In SELECT statements that work with tables, such a statement uses a boolean comparison as part of a WHERE clause, and would look more like:

      
SELECT ... FROM table-name WHERE column-name <> 0

NULL

NULL is a special data value in SQL. It is not the string 'NULL'. It is similar in usage to the Python value None, in that there is only one NULL defined in SQL and it is compared using IS rather than =.

The following code illustrates the differences between comparing NULL with IS and =:

      
SET @X = NULL;          # assigns NULL to a system variable
SELECT @X = NULL;       # returns NULL
SELECT @X IS NULL;      # returns 1 (TRUE)
SELECT @X != NULL;      # returns NULL
SELECT @X IS NOT NULL;  # returns 0 (FALSE)

As the results above show, = NULL and != NULL return the same thing (NULL), so use IS for all comparisons against NULL for consistency.

You can think of NULL as meaning 'unknown', so the result of comparing 'unknown' to 'unknown' is 'unknown', whereas asking if a value is 'unknown' is either true or false.

If allowed as part of a table definition (the default), a column may be given a value of NULL for any data type. If not allowed (`column`NOT NULL) then a NULL value is rejected.

In SELECT statements that work with tables, such a statement uses a boolean comparison as part of a WHERE clause, and would look more like:

      
SELECT ... FROM table-name WHERE column-name IS NOT NULL

SQL as a DDL

Data Definition Language (DDL) is a subset of SQL for describing data and its relationships in a database - i.e. define tables and views.

See Table Creation.
Tables can be deleted by using the DROP TABLE command.
Depending on the DBMS, SQL statements can be entered by hand or created using a menu-driven front end that constructs and executes the SQL statements for the user.
- The front end is quicker than constructing statements by hand for the novice user, and helps avoid errors.
- A good user front end allows complete functionality. A poor one makes it necessary to edit the constructed statements.
User-defined data types
- Some database dialects allow a database developer to define their own attribute domains. Example:
  
  CREATE DISTINCT TYPE SHOESIZE AS INTEGER WITH COMPARISONS
  
  (The WITH COMPARISONS clause allows the use of the [=, <>, <, <=, >, >=] comparison operations on this data type.)
Note that MySQL does not support user-defined data types.

SQL as a DML

Data Manipulation Language (DML) is a subset of SQL for inserting, deleting, and updating data in a database - i.e. using the SQL commands INSERT, DELETE, and UPDATE.