Structured Query Language (SQL)It's the most widely used language for managing data in relational databases. The language is easy to learn, declarative, and very convenient to manipulate datasets.
SQL for what?SQL can be helpful to execute queries, create data schemes, manipulate records, and retrieve data. It's often used along with a programming language to power applications.
PostgreSQLIt's an open-source object-relational database management system (RDBMS).
MySQLIt's the most widely used relational database management system. It runs as a server, and you can use it to create databases and give their access to multiple users.
Columns vs. rowsIn a database, all information is stored in tables. Each table is divided into rows and columns. Columns are specific attributes that allow for grouping data (e.g, id, first_name, last_name, age). Rows are records (e.g, 1001001, Kobe, Bryant, 37):
ViewsA view is a virtual table based on the result of an SQL query. It's a pretty convenient feature that allow you to run advanced queries in pre-sorted results.
ACID vs. BASEACID means "atomicity, consistency, isolation, and durability", and BASE stands for “Basically Available, soft state and eventual consistency”. Each approach has its advantages and drawbacks depending on what you want to achieve.
NoSQLThe NoSQL model states that not all applications need a relational database. Redis, Elasticsearch or MongoDB are practical examples of NoSQL systems. You can read this post to understand the difference with SQL.
ANDMultiple conditions in WHERE clauses that are all verified.
ORMultiple conditions in WHERE clauses where at least one condition is verified.
LIKECompare a value to similar values using wildcard operators.
BETWEENSearch between max and min values.
EXISTSSearch if a row is in a table.
INDetermine if a value is in a specified list of literal values.
NOTNegate logical operator (e.g., NOT EXISTS, NOT BETWEEN, NOT IN, and so on).
>=Equal or greater than.
<=Lower than or equal.
!>Not greater than.
!<Not lower than.
CREATE DATABASE my_database;
CREATE TABLE my_table ( my_column my_column_definition, other_column other_column_definition, );
ALTER TABLE my_table ADD my_column my_column_definition;
Select data in table
SELECT my_column FROM my_table;
Select data conditionally
SELECT my_column, other_column FROM my_table WHERE my_column = some_value AND other_column = other_value;
Insert data in table
INSERT INTO my_table (my_column, other_column) VALUES (my_value, other_value);
Update data in table
UPDATE my_table SET my_column = my_value WHERE other_column = other_value;
Delete data table
DELETE FROM my_table WHERE CONDITION;
CONDITIONis often a test for equality, greater than, lower than, etc.
SELECT my_column AS 'mycol' FROM my_table;
SELECT COUNT(my_column) FROM my_table;
A little more advanced commands
Kill doublons in select
SELECT DISTINCT id FROM Players;
Sum values in column
SELECT SUM(salary) FROM Players;
SELECT last_name, age FROM Players GROUP BY age ORDER BY last_name;
SELECT first_name, last_name FROM Players WHERE age <> 42;
CREATE VIEW my_view AS SELECT id, first_name, last_name, age FROM Players;
Select data from a view
SELECT * FROM my_view;
DROP VIEW my_view;
PRIMARY KEY constraints
A primary key is unique identifier for each records in a table. Its value cannot be NULL. It’s often combined with
AUTO_INCREMENT to generate it automatically:
CREATE TABLE `Players` ( id INT PRIMARY KEY NOT NULL AUTO_INCREMENT, last_name varchar(255) NOT NULL, first_name varchar(255), age int, );
A UNIQUE constraint forces the use of different values in a column:
CREATE TABLE `Players` ( id int NOT NULL, last_name varchar(255) NOT NULL, first_name varchar(255), age int, UNIQUE (id) );
SELECT * FROM table_A INTERSECT SELECT * FROM table_B
INTERSECT allows you to get results that match the first query and the second query, and only that.
SELECT * FROM table_A UNION SELECT * FROM table_B
UNION is a basic command that allows for combining several queries and concatenate results. In the example above, you get results that match the first query or the second query, or both.
You can consolidate information from several different tables with a SQL
INNER JOINJoin two tables where a condition is true in the two tables:
SELECT * FROM table_A INNER JOIN table_B ON table_A.id = table_B.pid
LEFT JOINReturn all records from the table on the left even if the condition is not true in the second table:
SELECT * FROM table_A LEFT JOIN table_B ON table_A.id = table_B.pid;
RIGHT JOINReturn all records from the table on the right even if the condition is not true in the first table:
SELECT * FROM table_A RIGHT JOIN table_B ON table_A.id = table_B.pid;
FULL JOIN and FULL OUTER JOINJoin two tables where a condition is true at least in one table:
SELECT * FROM table_A FULL JOIN table_B ON table_A.id = table_B.pid;
SELF JOINJoin a table with itself. It's useful when there are hierarchical relationships between records:
SELECT child.id, child.name, parent.name FROM People as child LEFT OUTER JOIN People AS parent ON child.parent_id = parent.id
CROSS JOINJoin each line of a table with each line of a second table:
SELECT * FROM table_A CROSS JOIN table_B;
NATURAL JOINJoin two tables when they have at least one column with the same name:
SELECT * FROM table_A NATURAL JOIN table_B;
UNION JOINJoin union.
Indexes are incredibly helpful to decrease execution time for SQL selects and WHERE clauses.
It’s pretty much like the index at the end of a book where you can find all pages related to a specific topic.
Creating (or dropping) indexes does not affect data:
CREATE INDEX my_index ON my_table;
You can create an index on a specific column:
CREATE INDEX my_index ON my_table (my_column);
Be careful, though, indexes are not meant for small tables or frequently updated columns. Large operations involving updates and inserts can be significantly slower.