SQL Cheat Sheet

Hello, friends!

Learning this cheat sheet will not make you a SQL master, but it will give you a general understanding of this programming language and the capabilities it provides. The capabilities discussed in the cheat sheet are common to all or most SQL dialects.

For a more complete immersion in SQL, I recommend studying these MySQL and PostgreSQL guides from Metanit. They are good because they are easy to learn and allow you to quickly get started with the mentioned DBMSs.

• Official MySQL documentation.
• Official PostgreSQL documentation (in Russian).
• Fresh SQL tutorial from Codecamp.
• Fresh SQL cheat sheet in PDF format.

What is SQL?

SQL is a Structured Query Language that allows you to store, manipulate, and retrieve data from relational databases (hereinafter - RDB, DB).

Why SQL?

SQL allows you to:

• access data in RDBMS systems
• describe data (its structure)
• define and manage data in a database
• interact with other languages through SQL modules, libraries, and precompilers
• create and delete databases and tables
• create views, stored procedures, and functions in a database
• set permissions for accessing tables, procedures, and views

SQL Process

When any SQL command is executed in any RDBMS (Relational Database Management System - such as PostgreSQL, MySQL, MSSQL, SQLite, etc.), the system determines the best way to execute the query, and the SQL engine determines how to interpret the task.

Several components participate in this process:

• Query Dispatcher
• Optimization Engines
• Classic Query Engine
• SQL Query Engine, etc.

The classic engine handles all non-SQL queries, while the SQL query engine does not process logical files.

SQL Commands

The standard commands for interacting with an RDB are CREATE, SELECT, INSERT, UPDATE, DELETE, and DROP. These commands can be classified as follows:

• DDL - Data Definition Language

• DML - Data Manipulation Language

• DCL - Data Control Language

Note: Using uppercase in SQL command names is just a convention; most DBMSs are case-insensitive. Nevertheless, the practice of writing instructions with command names in uppercase and table names, columns, etc. in lowercase makes it easy to quickly determine the purpose of the operation being performed on the data.

What is a table?

Data in a DBMS is stored in database objects called tables. A table usually consists of a collection of related data and consists of a certain number of columns and rows.

A table is the most common and simple form of storing data in an RDB. Here is an example of a table with users:

What is a field?

Each table consists of small parts called fields. The fields in the users table are userId, userName, age, city, and status. A field is a column in the table intended to store specific information about each record in the table.

Note: Instead of userId and userName, you could use id and name, respectively. But when working with multiple objects that have an id property, it can be difficult to understand which object the identifier belongs to, especially if, like me, you often use destructuring. As for the word name, it is often reserved, i.e., already used in the environment where the code is executed, so I try not to use it.

What is a record or row?

A record or row is any single occurrence that exists in a table. The users table has 5 records. In other words, a record is a horizontal entry in a table.

What is a column?

A column is a vertical entry in a table that contains all information related to a specific field. In the users table, one of the columns is city, which contains the names of cities where users live.

What is a NULL value?

A NULL value is a field value that is empty, i.e., a NULL value is a field value that has no value. It is important to understand that a NULL value is different from the value 0 and from a field value containing spaces. A field with a NULL value is a field that remained empty when the record was created. Also note that in some DBMSs an empty string is NULL, while in others they are different values.

Constraints

Constraints are rules applied to data. They are used to restrict the data that can be written to a table. This ensures the accuracy and reliability of data in the database.

Constraints can be set at both the column level and the table level.

Among the most common constraints are:

• NOT NULL - a column cannot have a NULL value;
• DEFAULT - the default value of a column;
• UNIQUE - all values in a column must be unique;
• PRIMARY KEY - a primary or main key, a unique identifier of a record in the current table;
• FOREIGN KEY - a foreign key, a unique identifier of a record in another table (a table associated with the current one);
• CHECK - all values in a column must satisfy a certain condition;
• INDEX - fast writing and retrieving data.

Any constraint can be removed using the ALTER TABLE command and DROP CONSTRAINT + constraint name. Some implementations provide shortcuts for removing constraints and the ability to disable constraints instead of removing them.

Data Integrity

Each DBMS has the following categories of data integrity:

• Entity Integrity - there should be no duplicates in the table (no two or more rows with the same values)
• Domain Integrity - filtering values by type, format, or range
• Referential Integrity - rows used by other records (rows that are referenced in other records) cannot be deleted
• User-Defined Integrity - additional rules

Database Normalization

Normalization is the process of efficiently organizing data in a database. There are two main reasons for the need for normalization:

• preventing redundant data from being written to the database, for example, storing the same data in different tables
• ensuring justified relationships between data

Normalization involves following several forms. A form is a database structuring format. There are three main forms: first, second, and third. I will not go into detail about these forms; if you wish, you can easily find the necessary information.

SQL Syntax

Syntax is a unique set of rules and recommendations. All SQL statements must begin with a keyword such as SELECT, INSERT, UPDATE, DELETE, ALTER, DROP, CREATE, USE, SHOW, etc., and end with a semicolon (;) (the semicolon is not part of SQL syntax, but console DBMS clients usually require it to mark the end of command input). SQL is case-insensitive, i.e., SELECT, select, and SeLeCt are identical statements. An exception to this rule is MySQL, where the case of table names is taken into account.

Syntax examples

-- selection
SELECT col1, col2, ...colN FROM tableName;
SELECT DISTINCT col1, col2, ...colN FROM tableName;
SELECT col1, col2, ...colN FROM tableName WHERE condition;
SELECT col1, col2, ...colN FROM tableName WHERE condition1 AND|OR condition2;
SELECT col2, col2, ...colN FROM tableName WHERE colName IN (val1, val2, ...valN);
SELECT col1, col2, ...colN FROM tableName WHERE colName BETWEEN val1 AND val2;
SELECT col1, col2, ...colN FROM tableName WHERE colName LIKE pattern;
SELECT col1, col2, ...colN FROM tableName WHERE condition ORDER BY colName [ASC|DESC];
SELECT SUM(colName) FROM tableName WHERE condition GROUP BY colName;
SELECT COUNT(colName) FROM tableName WHERE condition;
SELECT SUM(colName) FROM tableName WHERE condition GROUP BY colName HAVING (function condition);

-- creating a table
CREATE TABLE tableName (
  col1 datatype,
  col2 datatype,
  ...
  colN datatype,
  PRIMARY KEY (one or more columns)
);

-- deleting a table
DROP TABLE tableName;

-- creating an index
CREATE UNIQUE INDEX indexName ON tableName (col1, col2, ...colN);

-- deleting an index
ALTER TABLE tableName DROP INDEX indexName;

-- getting table structure description
DESC tableName;

-- clearing a table
TRUNCATE TABLE tableName;

-- adding/deleting/modifying columns
ALTER TABLE tableName ADD|DROP|MODIFY colName [datatype];

-- renaming a table
ALTER TABLE tableName RENAME TO newTableName;

-- inserting values
INSERT INTO tableName (col1, col2, ...colN) VALUES (val1, val2, ...valN)

-- updating records
UPDATE tableName SET col1 = val1, col2 = val2, ...colN = valN [WHERE condition];

-- deleting records
DELETE FROM tableName WHERE condition;

-- creating a database
CREATE DATABASE [IF NOT EXISTS] dbName;

-- deleting a database
DROP DATABASE [IF EXISTS] dbName;

-- selecting a database
USE dbName;

-- completing a transaction
COMMIT;

-- canceling changes
ROLLBACK;

Data Types

Each column, variable, and expression in SQL has a specific data type. The main categories of data types:

Exact numeric

Approximate numeric

Date and time

Character strings

Character strings (Unicode)

Binary

Mixed

Operators

An operator is a keyword or symbol that is mainly used in WHERE statements to perform operations. They are used both to define conditions and to combine multiple conditions in a statement.

In the following examples, we will assume that the variable a has a value of 10, and b - 20.

Arithmetic

Comparison operators

Logical operators

Expressions

An expression is a combination of values, operators, and functions for evaluating (calculating) a value. Expressions are similar to formulas written in a query language. They can be used to retrieve a specific set of data from the database.

The basic syntax of an expression looks like this:

SELECT col1, col2, ...colN
FROM tableName
WHERE [condition|expression];

There are different types of expressions: logical, numeric, and date expressions.

Logical

Logical expressions retrieve data based on matching a single value.

SELECT col1, col2, ...colN
FROM tableName
WHERE expression for finding a match with a single value;

Suppose the users table has the following records:

Let’s search for active users:

SELECT * FROM users WHERE status = active;

Result:

Numeric

Used to perform arithmetic operations in a query.

SELECT numericalExpression as operationName[FROM tableNameWHERE condition];

A simple example of using a numeric expression:

SELECT (10 + 5) AS addition;

Result:

There are several built-in functions such as count(), sum(), avg(), min(), max(), etc. for performing so-called aggregate calculations of table data or a column.

SELECT COUNT(*) AS records FROM users;

Result:

• AVG - calculates the average value;
• SUM - calculates the sum of values;
• MIN - calculates the minimum value;
• MAX - calculates the maximum value;
• COUNT - calculates the number of records in the table.

There are also several built-in functions for working with strings:

• CONCAT - concatenates strings;
• LENGTH - returns the number of characters in a string;
• TRIM - removes spaces at the beginning and end of a string;
• SUBSTRING - extracts a substring from a string;
• REPLACE - replaces a substring in a string;
• LOWER - converts string characters to lowercase;
• UPPER - converts string characters to uppercase, etc.

Functions for working with numbers:

• ROUND - rounds a number;
• TRUNCATE - truncates a decimal number to a specified number of decimal places;
• CEILING - returns the smallest integer greater than or equal to the current value;
• FLOOR - returns the largest integer less than or equal to the current value;
• POWER - raises a number to a specified power;
• SQRT - returns the square root of a number;
• RAND - generates a random floating-point number in the range from 0 to 1.

Date expressions

These expressions usually return the current date and time.

SELECT CURRENT_TIMESTAMP;

Result:

CURRENT_TIMESTAMP is both an expression and a function (CURRENT_TIMESTAMP()). Another function for getting the current date and time is NOW().

Other functions for getting the current date and time:

• CURDATE/CURRENT_DATE - returns the current date;
• CURTIME/CURRENT_TIME - returns the current time, etc.

Functions for parsing date and time:

• DAYOFMONTH(date) - returns the day of the month as a number;
• DAYOFWEEK(date) - returns the day of the week as a number;
• DAYOFYEAR(date) - returns the number of the day in the year;
• MONTH(date) - returns the month;
• YEAR(date) - returns the year;
• LAST_DAY(date) - returns the last day of the month as a date;
• HOUR(time) - returns the hour;
• MINUTE(time) - returns the minutes;
• SECOND(time) - returns the seconds, etc.

Functions for manipulating dates:

• DATE_ADD(date, interval) - performs addition of a date and a specific time interval;
• DATE_SUB(date, interval) - performs subtraction of a specific time interval from a date;
• DATEDIFF(date1, date2) - returns the difference in days between two dates;
• TO_DAYS(date) - returns the number of days from the 0th day of the year;
• TIME_TO_SEC(time) - returns the number of seconds since midnight, etc.

The functions DATE_FORMAT(date, format) and TIME_FORMAT(date, format) are used for formatting date and time, respectively.

Creating a Database

To create a database, use the CREATE DATABASE statement.

CREATE DATABASE dbName;

-- or

CREATE DATABASE IF NOT EXISTS dbName;

The IF NOT EXISTS condition helps avoid getting an error when attempting to create a database that already exists.

The database name must be unique within the DBMS.

Let’s create the testDB database:

CREATE DATABASE testDB;

Get a list of databases:

SHOW DATABASES;

Result:

Deleting a Database

To delete a database, use the DROP DATABASE statement.

DROP DATABASE dbName;

-- or

DROP DATABASE IF EXISTS dbName;

The IF EXISTS condition helps avoid getting an error when attempting to delete a non-existent database.

Delete the testDB database:

DROP DATABASE testDB;

Note: When you delete a database, all data stored in it is destroyed, so be very careful when using this command.

Check that the database is deleted:

SHOW DATABASES;

To get a list of tables, use the SHOW TABLES statement.

Result:

Selecting a Database

If there are multiple databases, before performing any operations, you need to select a database. To do this, use the USE statement.

USE dbName;

Suppose we did not delete testDB. Then we can select it like this:

USE testDB;

Creating a Table

Creating a table involves specifying the table name and defining the table columns and their data types. To create a table, use the CREATE TABLE statement.

CREATE TABLE tableName (
  col1 datatype,
  col2 datatype,
  ...
  colN datatype,
  PRIMARY KEY (one or more columns)
);

To create a table by copying another table, use the combination of CREATE TABLE and SELECT.

An example of creating a users table where the primary key is user identifiers, and the fields for the user’s name and age cannot be NULL:

CREATE TABLE users (
  userId INT,
  userName VARCHAR(20) NOT NULL,
  age INT NOT NULL,
  city VARCHAR(20),
  status VARCHAR(8),
  PRIMARY KEY (id)
);

Check that the table was created:

DESC users;

Result:

Deleting a Table

To delete a table, use the DROP TABLE statement.

Note: When you delete a table, all data stored in it, indexes, triggers, constraints, and permissions are permanently deleted, so be very careful when using this command.

Delete the users table:

DROP TABLE users;

Now, if we try to get a description of users, we will get an error:

DESC users;
-- ERROR 1146 (42S02): Table 'testDB.users' doesn't exist

Adding Columns

To add columns to a table, use the INSERT INTO statement.

INSERT INTO tableName (col1, col2, ...colN)
VALUES (val1, val2, ...valN);

Column names can be omitted, but in this case, values must be listed in the correct order.

INSERT INTO tableName VALUES (val1, val2, ...valN);

To avoid errors, it is recommended to always list the column names.

Suppose we did not delete the users table. Let’s fill it with users:

INSERT INTO users (userId, userName, age, city, status)
VALUES (1, 'Igor', 25, 'Moscow', 'active');

INSERT INTO users (userId, userName, age, city, status)
VALUES (2, 'Vika', 26, 'Ekaterinburg', 'inactive');

INSERT INTO users (userId, userName, age, city, status)
VALUES (3, 'Elena', 27, 'Ekaterinburg', 'active');

You can add multiple rows at once to a table.

INSERT INTO users (userId, userName, age, city, status)
VALUES
  (1, 'Igor', 25, 'Moscow', 'active'),
  (2, 'Vika', 26, 'Ekaterinburg', 'inactive'),
  (3, 'Elena', 27, 'Ekaterinburg', 'active');

Also, as noted, when adding a row, field names can be omitted:

INSERT INTO users
VALUES (4, 'Oleg', 28, 'Moscow', 'inactive');

Result:

Filling a table using another table

INSERT INTO tableName [(col1, col2, ...colN)]
SELECT col1, col2, ...colN
FROM anotherTable
[WHERE condition];

Selecting Fields

To select fields from a table, use the SELECT statement. It returns data as a result table (result set).

SELECT col1, col2, ...colNFROM tableName;

To select all fields, use this syntax:

SELECT * FROM tableName;

Let’s select the fields userId, userName, and age from the users table:

SELECT userId, userName, age FROM users;

Result:

The WHERE Clause

The WHERE clause is used to filter returned data. It is used together with SELECT, UPDATE, DELETE, and other statements.

SELECT col1, col2, ...col2
FROM tableName
WHERE condition;

The condition that returned records must satisfy is defined using comparison operators or logical operators such as >, <, =, NOT, LIKE, etc.

Let’s select the fields userId, userName, and age of active users:

SELECT userId, userName, age
FROM users
WHERE status = 'active';

Result:

Let’s select the fields userId, age, and city of a user named Vika.

SELECT userId, age, city
FROM users
WHERE userName = 'Vika';

Result:

Note: Strings in the WHERE clause must be enclosed in single quotes ('), while numbers are specified as they are.

The AND and OR Operators

The conjunctive operator AND and the disjunctive operator OR are used to join multiple conditions when filtering data.

AND

SELECT col1, col2, ...colN
FROM tableName
WHERE condition1 AND condition2 ... AND conditionN;

Returned records must satisfy all specified conditions.

Let’s select the fields userId, userName, and age of active users over 26 years old:

SELECT userId, userName, age
FROM users
WHERE status = active AND age > 26;

Result:

OR

SELECT col1, col2, ...colN
FROM tableName
WHERE condition1 OR condition2 ... OR conditionN;

Returned records must satisfy at least one condition.

Let’s select the same fields of inactive users or users under 27 years old:

SELECT userId, userName, age
FROM users
WHERE status = inactive OR age < 27;

Result:

Updating Fields

To update fields, use the UPDATE ... SET statement. This statement is usually used in conjunction with the WHERE clause.

UPDATE tableName
SET col1 = val1, col2 = val2, ...colN = valN
[WHERE condition];

Let’s update the age of a user named Igor:

UPDATE users
SET age = 30
WHERE username = 'Igor';

If we omit the WHERE clause in this case, the age of all users will be updated.

Deleting Records

To delete records, use the DELETE statement. This statement is also usually used in conjunction with the WHERE clause.

DELETE FROM tableName
[WHERE condition];

Delete inactive users:

DELETE FROM users
WHERE status = 'inactive';

If we omit the WHERE clause in this case, all records will be deleted from the users table.

The LIKE and REGEX Clauses

LIKE

The LIKE clause is used to compare values using wildcard operators. There are two types of such operators:

• percent signs (%);
• underscore (_).

% means 0, 1, or more characters. _ means exactly 1 character.

SELECT col1, col2, ...colN FROM tableName
WHERE col LIKE 'xxx%'
-- or
WHERE col LIKE '%xxx%'
-- or
WHERE col LIKE '%xxx'
-- or
WHERE col LIKE 'xxx_'
-- and so on.

Examples:

Let’s select inactive users:

SELECT * FROM users
WHERE status LIKE 'in%';

Result:

Let’s select users 30 years old and older:

SELECT * FROM users
WHERE age LIKE '3_';

Result:

REGEX

The REGEX clause allows you to define a regular expression that a record must match.

SELECT col1, col2, ...colN FROM tableName
WHERE colName REGEXP regular expression;

The following special characters can be used in a regular expression:

• ^ - start of line;
• $ - end of line;
• . - any character;
• [characters] - any of the characters specified in brackets;
• [start-end] - any character in the range;
• | - separates patterns.

Let’s select users named Igor and Vika:

SELECT * FROM users
WHERE userName REGEXP 'Igor|Vika';

Result:

The TOP/LIMIT/ROWNUM Clause

These clauses allow you to extract a specified number or percentage of records from the beginning of a table. Different DBMSs support different clauses.

SELECT TOP number|percent col1, col2, ...colN
FROM tableName
[WHERE condition];

Let’s select the first three users:

SELECT TOP 3 * FROM users;

Result:

In mysql:

SELECT * FROM users
LIMIT 3, [offset];

The offset parameter determines the number of records to skip. For example, you can extract the first two users starting from the third:

SELECT * FROM users
LIMIT 2, 2;

In oracle:

SELECT * FROM users
WHERE ROWNUM <= 3;

The ORDER BY and GROUP BY Clauses

ORDER BY

The ORDER BY clause is used to sort data in ascending (ASC) or descending (DESC) order. Most DBMSs sort in ascending order by default.

SELECT col1, col2, ...colN
FROM tableName
[WHERE condition]
[ORDER BY col1, col2, ...colN] [ASC | DESC];

Note: The columns to sort by must be specified in the list of columns for selection.

Let’s select users sorted by city and age:

SELECT * FROM users
ORDER BY city, age;

Result:

Now let’s perform sorting in descending order:

SELECT * FROM users
ORDER BY city, age DESC;

Let’s define our own sort order in descending order:

SELECT * FROM users
ORDER BY (CASE
  WHEN city = 'Ekaterinburg' THEN 1
  WHEN city = 'Moscow' THEN 2
  ELSE 100
END) ASC, city DESC;

GROUP BY

The GROUP BY clause is used together with the SELECT statement to group records. It is specified after WHERE and before ORDER BY.

SELECT col1, col2, ...colN
FROM tableName
WHERE condition
GROUP BY col1, col2, ...colN
ORDER BY col1, col2, ...colN;

Let’s group active users by city:

SELECT city, COUNT(city) AS amount FROM users
WHERE status = active
GROUP BY city
ORDER BY city;

Result:

The DISTINCT Keyword

The DISTINCT keyword is used together with the SELECT statement to return only unique records (without duplicates).

SELECT DISTINCT col1, col2, ...colN
FROM tableName
[WHERE condition];

Let’s select the cities where users live:

SELECT DISTINCT city
FROM users;

Result:

Joins

Joins are used to combine records from two or more tables.

Suppose we have a table orders with user orders in addition to users:

Let’s select the fields userId, userName, age, and amount from our tables by joining them:

SELECT userId, userName, age, amount
FROM users, orders
WHERE users.userId = orders.userId;

Result:

When joining tables, operators such as =, <, >, <>, <=, >=, !=, BETWEEN, LIKE, and NOT can be used, but = is the most common.

There are different types of joins:

• INNER JOIN - returns records that exist in both tables;
• LEFT JOIN - returns records from the left table even if such records do not exist in the right table;
• RIGHT JOIN - returns records from the right table even if such records do not exist in the left table;
• FULL JOIN - returns all records of the joined tables;
• CROSS JOIN - returns all possible combinations of rows from both tables;
• SELF JOIN - used to join a table with itself.

The UNION Clause

The UNION clause is used to combine the results of two or more SELECT statements. Only unique records are returned.

With UNION, each SELECT statement must have:

• the same set of columns for selection;
• the same number of expressions;
• the same data types of columns;
• the same order of columns.

However, they can be of different lengths.

SELECT col1, col2, ...colN
FROM table1
[WHERE condition]
UNION
SELECT col1, col2, ...colN
FROM table2
[WHERE condition];

Let’s combine our users and orders tables:

SELECT userId, userName, amount, date
FROM users
LEFT JOIN orders
ON users.useId = orders.userId
UNION
SELECT userId, userName, amount, date
FROM users
RIGHT JOIN orders
ON users.userId = orders.userId;

Result:

The UNION ALL Clause

The UNION ALL clause is also used to combine the results of two or more SELECT statements. All records are returned, including duplicates.

SELECT col1, col2, ...colN
FROM table1
[WHERE condition]
UNION ALL
SELECT col1, col2, ...colN
FROM table2
[WHERE condition];

There are two more clauses similar to UNION:

• INTERSECT - used to combine the results of two or more SELECT statements, but only rows from the first SELECT that match rows from the second SELECT are returned;
• EXCEPT|MINUS - only rows from the first SELECT that are missing in the second SELECT are returned.

Aliases

Aliases allow you to temporarily change the names of tables and columns. “Temporarily” means that the new name is used only in the current query, and the name in the database remains the same.

Syntax for table alias:

SELECT col1, col2, ...colN
FROM tableName AS aliasName
[WHERE condition];

Syntax for column alias:

SELECT colName AS aliasName
FROM tableName
[WHERE condition];

Example of using table aliases:

SELECT U.userId, U.userName, U.age, O.amount
FROM users AS U, orders AS O
WHERE U.userId = O.userId;

Result:

Example of using column aliases:

SELECT userId AS user_id, userName AS user_name, age AS user_age
FROM users
WHERE status = active;

Result:

Indexes

Creating indexes

Indexes are special lookup tables used by the database engine to retrieve data more quickly. In other words, an index is a pointer or reference to data in a table.

Indexes speed up the SELECT statement and the WHERE clause, but slow down UPDATE and INSERT statements. Indexes can be created and deleted without affecting the data.

To create an index, use the CREATE INDEX statement, which allows you to define the index name, indexed columns, and the order of indexing (ascending or descending).

The UNIQUE constraint can be applied to indexes to ensure their uniqueness.

Syntax for creating an index:

CREATE INDEX indexName ON tableName;

Syntax for creating an index for one column:

CREATE INDEX indexName ON tableName (colName);

Syntax for creating unique indexes (such indexes are used not only to improve performance but also to ensure data consistency):

CREATE UNIQUE INDEX indexName ON tableName (colName);

Syntax for creating indexes for multiple columns (composite index):

CREATE INDEX indexName ON tableName (col1, col2, ...colN);

The decision to create indexes for one or more columns should be based on which columns will often be used in the WHERE query as a condition for sorting rows.

Implicit indexes are automatically created for PRIMARY KEY and UNIQUE constraints.

Deleting indexes

To delete indexes, use the DROP INDEX statement:

DROP INDEX indexName;

Although indexes are designed to improve database performance, there are situations where their use is best avoided.

Such situations include:

• indexes should not be used in small tables;
• in tables that are often and extensively updated or overwritten;
• in columns that contain a large number of NULL values;
• in columns on which operations are frequently performed.

Updating a Table

The ALTER TABLE command is used to add, delete, and modify columns in an existing table. This command is also used to add and delete constraints.

Syntax:

-- adding a new column
ALTER TABLE tableName ADD colName datatype;

-- deleting a column
ALTER TABLE tableName DROP COLUMN colName;

-- changing the data type of a column
ALTER TABLE tableName MODIFY COLUMN colName newDatatype;

-- adding a `NOT NULL` constraint
ALTER TABLE tableName MODIFY colName datatype NOT NULL;

-- adding a `UNIQUE` constraint
ALTER TABLE tableName
ADD CONSTRAINT myUniqueConstraint UNIQUE (col1, col2, ...colN);

-- adding a `CHECK` constraint
ALTER TABLE tableName
ADD CONSTRAINT myUniqueConstraint CHECK (condition);

-- adding a primary key
ALTER TABLE tableName
ADD CONSTRAINT myPrimaryKey PRIMARY KEY (col1, col2, ...colN);

-- deleting a constraint
ALTER TABLE tableName
DROP CONSTRAINT myUniqueContsraint;

-- mysql
ALTER TABLE tableName
DROP INDEX myUniqueContsraint;

-- deleting a primary key
ALTER TABLE tableName
DROP CONSTRAINT myPrimaryKey;

-- mysql
ALTER TABLE tableName
DROP PRIMARY KEY;

Let’s add a new column to the users table - the user’s gender:

ALTER TABLE users ADD sex char(1);

Delete this column:

ALTER TABLE users DROP sex;

Clearing a Table

The TRUNCATE TABLE command is used to clear a table. Its difference from DROP TABLE is that the table structure is preserved (DROP TABLE completely removes the table and all its data).

TRUNCATE TABLE tableName;

Let’s clear the users table:

TRUNCATE TABLE users;

Check that users is empty:

SELECT * FROM users;
-- Empty set (0.00 sec)

Views

A view is nothing more than a statement stored in a database under a specific name. In other words, a view is a composition of a table in the form of a pre-defined query.

Views can contain all or only some rows of a table. A view can be created based on one or more tables (depending on the query to create the view).

Views are virtual tables that allow you to:

• structure data in a way that users find most natural or intuitive;
• restrict access to data so that a user can view and (sometimes) modify only what he needs and nothing more;
• combine data from multiple tables to form reports.

Creating a view

To create a view, use the CREATE VIEW statement. As noted, views can be created based on one or more tables, and even based on another view.

CREATE VIEW viewName AS
SELECT col1, col2, ...colN
FROM tableName
[WHERE condition];

Let’s create a view for user names and ages:

CREATE VIEW usersView AS
SELECT userName, age
FROM users;

Get data using the view:

SELECT * FROM usersView;

Result:

WITH CHECK OPTION

WITH CHECK OPTION is a setting for the CREATE VIEW statement. It ensures that all UPDATE and INSERT operations comply with the condition defined in the view.

If the condition is not satisfied, an exception is raised.

CREATE VIEW usersView AS
SELECT userName, age
FROM users
WHERE age IS NOT NULL
WITH CHECK OPTION;

Updating a view

A view can be updated if the following conditions are met:

• SELECT does not contain the DISTINCT keyword;
• SELECT does not contain aggregate functions;
• SELECT does not contain value-setting functions;
• SELECT does not contain value-setting operations;
• SELECT does not contain an ORDER BY clause;
• FROM does not contain more than one table;
• WHERE does not contain subqueries;
• the query does not contain GROUP BY or HAVING;
• computed columns are not updated;
• all non-zero columns from the base table are included in the view in the same order as specified in the INSERT query.

Example of updating the age of a user named Igor in a view:

UPDATE usersView
SET age = 31
WHERE userName = 'Igor';

Note: Updating a row in a view results in it being updated in the base table.

New rows can be added to a view using the INSERT command. When executing this command, the same rules apply as when executing the UPDATE command.

Rows can be deleted from a view using the DELETE command.

Delete from the view a user whose age is 26:

DELETE FROM usersView
WHERE age = 26;

Note: Deleting a row from a view results in it being deleted from the base table.

Deleting a view

To delete a view, use the DROP VIEW statement:

DROP VIEW viewName;

Delete the usersView view:

DROP VIEW usersView;

HAVING

The HAVING clause is used to filter grouping results. WHERE is used to apply conditions to columns, and HAVING - to groups created with GROUP BY.

HAVING must be specified after GROUP BY but before ORDER BY (if present).

SELECT col1, col2, ...colN
FROM table1, table2, ...tableN
[WHERE condition]
GROUP BY col1, col2, ...colN
HAVING condition
ORDER BY col1, col2, ...colN;

Transactions

A transaction is a unit of work or operation performed on a database. It is a sequence of operations performed in logical order. These operations can be initiated either by a user or by a program running in the database.

A transaction is the application of one or more changes to a database. For example, when we create/update/delete a record, we perform a transaction. It is important to control the execution of such operations to ensure data consistency and handle possible errors.

In practice, queries are usually not sent to the database one by one; they are grouped and executed as part of a transaction.

Transaction properties

Transactions have 4 standard properties (ACID):

• atomicity - all operations of a transaction must complete successfully. Otherwise, the transaction is aborted and all changes are rolled back (reverted to the previous state);
• consistency - the state must change in full accordance with the operations of the transaction;
• isolation - transactions are independent of each other and do not affect each other;
• durability - the result of a completed transaction must be preserved in case of system failure.

Transaction management

The following commands are used to manage transactions:

• BEGIN|START TRANSACTION - start a transaction;
• COMMIT - save changes;
• ROLLBACK - cancel changes;
• SAVEPOINT - checkpoint for undoing changes;
• SET TRANSACTION - set the characteristics of the current transaction.

Transaction management commands can only be used together with queries such as INSERT, UPDATE, and DELETE. They cannot be used during table creation and deletion, as these operations are automatically sent to the database.

Delete a user whose age is 26 and send the changes to the database:

BEGIN TRANSACTION
DELETE FROM users
WHERE age = 26;
COMMIT;

Delete a user named Oleg and cancel this operation:

BEGIN
DELETE FROM users
WHERE username = 'Oleg';
ROLLBACK;

Checkpoints are created using this syntax:

SAVEPOINT savepointName;

Return to a checkpoint like this:

ROLLBACK TO savepointName;

Let’s execute three delete queries on users, creating checkpoints before each deletion:

START TRANSACTION
SAVEPOINT sp1;
DELETE FROM users
WHERE age = 26;
SAVEPOINT sp2;
DELETE FROM users
WHERE userName = 'Oleg';
SAVEPOINT sp3;
DELETE FROM users
WHERE status = 'inactive';

Let’s undo the last two deletions by returning to checkpoint sp2 created after the first deletion:

ROLLBACK TO sp2;

Let’s select users:

SELECT * FROM users;

Result:

As we can see, only the user aged 26 was deleted from the table.

To delete a checkpoint, use the RELEASE SAVEPOINT command. Naturally, after deleting a checkpoint, you cannot return to it using ROLLBACK TO.

The SET TRANSACTION command is used to initialize a transaction, i.e., to start its execution. In this case, you can define certain characteristics of the transaction. For example, you can define the transaction access level (read-only or for writing too):

SET TRANSACTION [READ WRITE | READ ONLY];

Temporary Tables

Some DBMSs support so-called temporary tables. Such tables allow you to store and process intermediate results using the same queries as when working with regular tables.

Temporary tables can be very useful when you need to store temporary data. One of the main features of such tables is that they are deleted when the current session ends. When running a script, the temporary table is deleted after the script finishes executing. When accessing the database using a client program, the table will be deleted after the program is closed.

A temporary table is created using the CREATE TEMPORARY TABLE statement; otherwise, the syntax for creating such tables is identical to the syntax for creating regular tables.

A temporary table is deleted in the same way as a regular table, using the DROP TABLE statement.

Cloning a Table

There may be a situation where you need to get an exact copy of an existing table, and CREATE TABLE or SELECT may not be sufficient because you want to get not only an identical structure, but also indexes, default values, etc. of the copied table.

In mysql, for example, you can do this like this:

• call the SHOW CREATE TABLE command to get the statement executed when creating the table, including indexes and more;
• change the table name and execute the query. We get an exact copy of the table;
• optionally: if you need the contents of the copied table, you can also use INSERT INTO or SELECT statements.

Subqueries

A subquery is an inner (nested) query of another query embedded using WHERE or other statements.

A subquery is used to get data that will be used by the main query as a condition for filtering returned records.

Subqueries can be used in SELECT, INSERT, UPDATE, and DELETE statements, as well as with operators such as =, <, >, >=, <=, IN, BETWEEN, etc.

Rules for using subqueries:

• they must be enclosed in parentheses;
• a subquery must contain only one column for selection if the main query does not contain several such columns that are compared in the subquery;
• you cannot use the ORDER BY command in a subquery; you can do this in the main query. In a subquery, you can use GROUP BY to replace ORDER BY;
• subqueries returning multiple values can only be used with operators that work with sets of values, such as IN;
• the SELECT list cannot contain references to values that are evaluated as BLOB, ARRAY, CLOB, or NCLOB;
• a subquery cannot be passed directly to a function for setting values;
• the BETWEEN command cannot be used together with a subquery. However, you can use the specified command within the subquery itself.

Subqueries are usually used in a SELECT statement.

SELECT col1, col2, ...colN
FROM table1, table2, ...tableN
WHERE colName operator (
  SELECT col1, col2, ...colN
  FROM table1, table2, tableN
  [WHERE condition]
);

Example:

SELECT * FROM users
WHERE userId IN (
  SELECT userId FROM users
  WHERE status = 'active'
);

Result:

Subqueries can be used in an INSERT statement. This statement adds data returned by the subquery to the table. In this case, the data returned by the subquery can be modified in any way.

INSERT INTO tableName (col1, col2, ...colN)
SELECT col1, col2, ...colN
FROM table1, table2, ...tableN
[WHERE operator [value]];