Structured Query Language (SQL)
SQL, meaning Structured Query Language, is a programming language used to communicate with a relational database.
It uses simple English sentences that allow to
UPDATE (change) or
DELETE (remove) data.
SQL is a declarative language: in a SQL statement you describe what needs to be done, but not how to do it; the later, as well as the execution, is handled by the.
One can think of SQL as three separate sub-languages which their specificities:
- DDL: Data Definition Language: define the structure of a database and the tables and columns within it
- DML: Data Manipulation Language: retrieve or modify data stored in a database (ex:
- DCL: Data Control Language: determine what various users are allowed to do when interacting with a database, in other words, the “security” of a database
Data vs Schema
A RDBMS such asknows how to interpret the syntax of an SQL statement thanks to the role that data and schema plays in a database.
Schema relates to the structure of a database, such as the name of a table and its columns, data types etc.
Data relates to the contents of a database, i.e., the actual values associated with rows and columns in a database table.
The combination of the two is what creates the structured data that we can interact with.
SQL is case-insensitive, meaning it doesn’t care if its statement is written in lowercase or uppercase. This is not true for data, though.
The convention is to use uppercase for commands and lowercase for names, for clarity.
Database names shall be self-descriptive, no abbreviation nor acronym, and names shall be written in snake_case.
Tables, sometimes called relations and the relationships between those are what provide structure to our data. Data are saved in the columns of our table.
Tables can be used to represent real world abstractions of business logic, such as customer or an order.
A basic table is create with the following statement:
CREATE TABLE table_name ( column_1_name column_1_data_type [constraints, ...], column_2_name column_2_data_type [constraints, ...], -- ... );
Each line (finished by a coma
,) represents one column of our table. The first part is the name of the column. Except for the convention described above, there is nothing particular to be aware of. The second part is the data type of the column (see below). The third part is one or more constraints (optional).
A data type describes the values that is allowed for that column of the table. It’s both for organization and for protection of the database (to refrain from entering invalid type). Commons data types are described below:
|(Deprecated) Create identifier columns |
|New feature replacing |
|Column can only contain a string of |
|Same as |
|Boolean data type (|
|Integer data type|
|Decimal number with a total digits of |
|Date and tie in |
|Date without the time|
Constraints are rules to put on columns in order to define what data values are allowed. While optional, most of the time it is recommended to add them as it helps maintaining the integrity and quality of the data that the column is storing.
Often used constraints include:
|Prevents duplicate value in that column|
|Cannot be left empty|
|If no value is set at creation time, default value of |
Alter a Table
One can modify a table using the
ALTER TABLE statement. The syntax is as follows:
ALTER TABLE table_name modify_clause arguments;
To rename a table, one can use the following:
ALTER TABLE foo RENAME TO bar;
To rename a column, one can use the following:
ALTER TABLE bar RENAME COLUMN foobar TO barfoo;
Alter a Column’s Datatype
To alter a column’s datatype, one can use
ALTER COLUMN in conjunction with
ALTER TABLE bar ALTER COLUMN barfoo TYPE char(10);
Constraints are added/removed rather than changed. This is because you can have several constraints in a column.
ALTER TABLE bar ADD [CONSTRAINT constraint_name] constraint_clause;
Depending on the type of constraint, the syntax can vary. This is true for so called “table constraints” and “column constraints”.
The difference is mainly syntactic.
NOT NULL for example is always a column constraint, which is why it has its special command for adding constraint to an existing table:
ALTER TABLE bar ALTER COLUMN barfoo SET NOT NULL;
To remove a constraint, we use the following syntax:
ALTER TABLE bar DROP CONSTRAINT constraint_name;
To drop the
DEFAULT clause on data type
serial, we use the following:
ALTER TABLE bar ALTER COLUMN id DROP DEFAULT;
Adding/Removing a Column
The syntax is the
ADD COLUMN clause to
ALTER TABLE statement:
ALTER TABLE bar ADD COLUMN last_login timestamp NOT NULL DEFAULT NOW();
Removing a column uses
ALTER TABLE bar DROP COLUMN piyo;
This dangerous action can be done with the following command:
DROP TABLE bar;
Insertion Statement Syntax
The most basic insertion statement is as follows:
INSERT INTO table_name (column1_name, column2_name,...) VALUES (data_for_column1, data_for_column2,...);
Three pieces of information are to be provided:
- The table name
- The columns’ names
- The values to be stored in each columns
Will columns’ names can be omitted under certain conditions, it’s generally best to write both the column and its data to avoid error down the road.
A successful insertion will produce the following result (meaning is in comment):
INSERT 0 1 -- | \ -- oid | -- count
To insert several data sequentially in one command, you can separate each row with a comma
INSERT INTO table_name (column1_name, column2_name,...) VALUES (data_for_column1, data_for_column2,...), (data_for_column1, data_for_column2,...);
Select Query Syntax
The basic syntax is as follow:
SELECT column_name, ... FROM table_name WHERE condition [ORDER BY column_name [ASC]];
List of often used operators:
|less than or equal to|
|greater than or equal to|
There are also comparison predicates that have a special syntax:
|between two values|
|not between two values|
|is distinct from|
|is not distinct from|
|is not null|
NULL is a special value in SQL. Although it is a value, it cannot be used for selection like
WHERE column_name = NULL. Instead, we use
WHERE column_name IS NULL.
There are three:
There use with
WHERE clause are pretty self-explanatory, for example with
SELECT column_name FROM table_name WHERE condition OR other_condition;
String Matching Operators
String or pattern matching allows to conditionally search fro string patterns. We use
LIKE (case-sensitive) and
SELECT column_name FROM table_name WHERE condition LIKE '%foo';
% is the wildcard character for any number of characters. For one character, we can use underscore
There is also a
SIMILAR TO that works like
LIKE but for regular expression pattern.