SQL - Structured Query Language - a language designed to allow both technical and non-technical users query, manipulate, and transform data from a relational database.
relational database - a collection of related(2 dimensional) tables. Each of the tables are similar to an Excel spreadsheet, with a fixed number of named columns (the attributes or properties of the table) and any number of rows of data.
query - a statement which declares what data we are looking for, where to find it in the database, and optionally, how to transform it before it is returned
SELECT statement - a query
A table in SQL is a type of an entity (ie. Dogs), and each row in that table as a specific instance of that type (ie. A pug, a beagle, a different colored pug, etc).The columns would then represent the common properties shared by all instances of that entity (ie. Color of fur, length of tail, etc).
The ‘*” is used as shorthand in place of listing all the column names individually
Example of a SELECT statement
SELECT column, another_column
FROM mytable
WHERE clause - applied to each row of data by checking specific column values to determine whether it should be included in the results or not.
Example of WHERE clause:
SELECT column, another_column
FROM mytable
WHERE condition
AND/OR another_condition
AND/OR …;
More complex clauses can be constructed by joining numerous AND or OR logical keywords
More useful operators:
All strings must be quoted so that the query parser can distinguish words in the string from SQL keywords.
When doing a full text search it is best to use dedicated Libraries like Apache Lucene or Sphinx
DISTINCT - discards rows that have a duplilcate column value
example:
SELECT DISTINCT column, another_column, …
FROM mytable
WHERE condition(s);
ORDER BY - each row is sorted alpha-numerically based on the specified column’s value.
example:
SELECT column, another_column, …
FROM mytable
WHERE condition(s)
ORDER BY column ASC/DESC;
other ORDER BY clauses:
example:
SELECT column, another_column, …
FROM mytable
WHERE condition(s)
ORDER BY column ASC/DESC
LIMIT num_limit OFFSET num_offset;
Schema - describes the structure of each table and the datatypes that each colun of the table can contain
example - values in a Year column must be an Integer and values in the Title column must be a string
INSERT - which declares which table to write into, the columns of data that we are filling, and one or more rows of data to insert. In general, each row of data you insert should contain values for every corresponding column in the table. You can insert multiple rows at a time by just listing them sequentially.
example
INSERT INTO mytable
VALUES (value_or_expr, another_value_or_expr, …),
(value_or_expr_2, another_value_or_expr_2, …),
…;
if you have incomplete data and the table contains columns that support default values you can insert rows with only the columns of data you have by specifying them explicitly
example:
INSERT INTO mytable
(column, another_column, …)
VALUES (value_or_expr, another_value_or_expr, …),
(value_or_expr_2, another_value_or_expr_2, …),
…;
The number of values need to match the number of columns specified
UPDATE - updates data by taking multiple column/value pairs and applies those changes to each and every row that satifies the contraint in the WHERE clause
DELETE - deletes data from a table in the database example:
DELETE FROM mytable
WHERE condition;
f you decide to leave out the WHERE constraint, then all rows are removed, which is a quick and easy way to clear out a table completely
CREATE TABLE - creates a new database table
example:
CREATE TABLE IF NOT EXISTS mytable (
column DataType TableConstraint DEFAULT default_value,
another_column DataType TableConstraint
DEFAULT default_value,
…
);
INTEGER, BOOLEAN - The integer datatypes can store whole integer values like the count of a number or an age. In some implementations, the boolean value is just represented as an integer value of just 0 or 1.
FLOAT, DOUBLE, REAL - The floating point datatypes can store more precise numerical data like measurements or fractional values. Different types can be used depending on the floating point precision required for that value.
**CHARACTER(num_chars), VARCHAR(num_chars), TEXT - Both the CHARACTER and VARCHAR (variable character) types are specified with the max number of characters that they can store (longer values may be truncated), so can be more efficient to store and query with big tables.
DATE, DATETIMESQL can also store date and time stamps to keep track of time series and event data.
BLOB - SQL can store binary data in blobs right in the database. These values are often opaque to the database, so you usually have to store them with the right metadata to requery them.
PRIMARY KEY - he values in this column are unique, and each value can be used to identify a single row in this table.
AUTOINCREMENT - he value is automatically filled in and incremented with each row insertion. Not supported in all databases.
UNIQUE - The values in this column have to be unique, so you can’t insert another row with the same value in this column as another row in the table. Differs from the PRIMARY KEY in that it doesn’t have to be a key for a row in the table
NOT NULL - the inserted value can not be NULL.
CHECK (expression) - This allows you to run a more complex expression to test whether the values inserted are valid. For example, you can check that values are positive, or greater than a specific size, or start with a certain prefix, etc.
FOREIGN KEY - This is a consistency check which ensures that each value in this column corresponds to another value in a column in another table.
ALTER TABLE - update the table by adding removing or modigying columns and table constraints
example:
ALTER TABLE mytable
ADD column DataType OptionalTableConstraint
DEFAULT default_value;
example:
ALTER TABLE mytable
DROP column_to_be_deleted;
some databases do not support this feature so you might have to create a new table and migrate the data over
RENAME TO renames the whole table
example:
ALTER TABLE mytable
RENAME TO new_table_name;
DROP TABLE - removes an entire table including all of its data and metadata. It is different from DELETE in that it also removes the schema from the database entirely
example:
DROP TABLE IF EXISTS mytable;
If you have another table that is dependent on columns in table you are removing (for example, with a FOREIGN KEY dependency) then you will have to either update all dependent tables first to remove the dependent rows or to remove those tables entirely.