2020-12-16 07:12

CP363 : Introduction to SQL

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

SQL Data Types (for MySQL)

Data Type	Description
String
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
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.
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	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`
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.
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

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.

SQL as a DDL

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

The four basic statements are SELECT, INSERT, UPDATE, and DELETE.