The Structured Query Language

The Structured Query Language (SQL) is a programming language designed for managing and manipulating data held in a relational database management system, like MySQL.

It allows users to interact with the database server to perform specific tasks, like retrieving or inserting data, using a series of commands called SQL statements.

When executed, these statements are sent to the database server, which in turn interprets them, processes the data, and returns the result or confirms / invalidates the action.

The types of SQL statements

In SQL, statements can be informally classed in the following sublanguages:

1. The data definition language (DDL) is used to define, modify, and manage database objects, such as tables, indices, and users.
2. The data manipulation language (DML) is used to manage and manipulate data within the database.
3. The data query language (DQL) is used to retrieve information or data from database tables based on specified criteria.
4. The data control language (DCL) is used to ensure the security of the database and control its access by granting and revoking permissions and privileges to users.

The SQL syntax

The SQL syntax is a set of rules that define how to write SQL statements, which tell the database what operation to perform and how to perform it.

An SQL statement always starts with a keyword, ends with a semicolon ;, and can be composed of:

• Keywords, which are reserved SQL words used to define the action to be performed on the database (e.g., SELECT, INSERT).
• Identifiers, which are unique names that reference database objects, such as tables or column names (e.g., users, email).
• Clauses, which are reserved words or groups of words used to perform specific tasks or define conditions (e.g., FROM, WHERE).
• Predicates, which are conditions specified within a clause that determine which rows to include or exclude from the result set (e.g., id < 3).
• Expressions, which are a combination of symbols, column names, constants, and functions that produce a single value (e.g., "email", id + 1).

Example

Let's consider this SQL statement used to retrieve data:

SELECT email FROM users WHERE id < 3;

Where:

• SELECT is a keyword.
• email is an identifier that references a column name.
• FROM is a clause used to specify where the data should be retrieved from.
• users is an identifier that references a table name.
• WHERE is a clause used to filter the results.
• id < 3 is an expression used as a predicate of the WHERE clause, where id is an identifier, < is a symbol, 3 is a constant.

Which in plain English translates to "Select the values contained in the email column of the users table in the rows where the value of the id column is inferior to 3".

Common SQL operators

Here is a list of the most common SQL operators that can be used in SQL predicates.

Arithmetic operators

Arithmetic operators are used to perform mathematical operations on numeric data.

They include:

• + to add two numbers.
• - to subtract one number from another.
• * to multiply two numbers.
• / to divide one number by another.
• % to return the remainder of a division operation

For example:

SELECT 1 + 4;

Comparison operators

Comparison operators are used to compare two values and return a boolean result (TRUE, FALSE, or NULL).

They include:

• = to check if two values are equal.
• != or <> to check if two values are not equal.
• > to check if one value is greater than another.
• < to check if one value is less than another.
• >= to check if one value is greater than or equal to another.
• <= to check if one value is less than or equal to another.

For example:

SELECT reference FROM products WHERE price >= 200;

Logical operators

Logical operators are used to combine multiple conditions in SQL queries.

They include:

• AND to combine two conditions and return true if both conditions are true.
• OR to combine two conditions and return true if either condition is true.
• NOT to reverse the result of a condition

For example:

SELECT email FROM users WHERE job = 'developer' AND NOT speciality = 'frontend';

Executing SQL statements

To execute an SQL statement, you can type your statement in the MySQL client and press the ENTER key:

mysql> SELECT * FROM users;

Once the statement evaluated and/or executed by the server, the client will either display the result, if the statement is a query:

mysql> SELECT * FROM users;
+-----+-------------------------+-----------------+
| id  | email                   | name            |
+-----+-------------------------+-----------------+
| 110 | razvan@learnbackend.dev | Razvan Ludosanu |
+-----+-------------------------+-----------------+

A confirmation message:

mysql> INSERT INTO users VALUES (null, 'johndoe@learnbackend.dev', 'John Doe');
Query OK, 1 row affected (0.01 sec)

Or an error message:

mysql> SELECT * FROM customers;
ERROR 1146 (42S02): Table 'demo.customers' doesn't exist

Summary

Here's a summary of what you've learned in this lesson:

• SQL is a programming language designed for managing data held in a RDBMS.
• SQL is composed of 4 sublanguages informally called DDL, DML, DQL, and DCL.
• DDL commands are used to manage database objects.
• DML commands are used to manage data within the database.
• DQL commands are used to retrieve data from database tables.
• DCL commands are used to ensure the security of the database.
• SQL statements can be composed of keywords, identifiers, clauses, expressions, and predicates.
• Upon execution of SQL commands, the SQL client will either output the result of the query, a confirmation message, or an error message.