Structured Query Language (SQL) ¶

The Structured Query Language (SQL) is the most popular query language. The fundamentals are the same for many RDBMS.

A Request/Query 👮 is a statement sent to the database to perform some task/get some result. It looks like this:

SELECT *
FROM users
WHERE username = 'admin';

➡️ You can put everything on one line. The semicolon (;) is optional for simple queries, but you should always use it.

Where to Learn? 🎓

• W3Schools/SQL (⛪)
• SQL.sh and fxjollois (French 🐔)
• learnsql (👻)

Useful tools 🐲

• dbeaver (36.1k ⭐): multi-database GUI client

Types ¶

Common types are:

• varchar(1): one character (a char)
• varchar(n): a fixed-size string
• text: a dynamically-sized string
• int/int(n)/number: an integer
• int(1): a common type for a boolean
• float/real: a floating-point number
• decimal(n,p): a common type for a price
• date: a date
• enum('v1', 'v2', ...): a type taking values in a list

Strings ¶

• You can use single or double quotes for strings
• To escape a quote, use either 'a''b' or 'a\'b'
• To concatenate, use || giving us 'a' || 'b'
• Some functions you may use: STRCMP(str1, str2), LENGTH('str'), LOWER('str'), UPPER('str'), LEFT(string, count), and RIGHT(string, count).

Comments ¶

-- comment
/* comment */

Useful functions ¶

Refer to W3Schools - SQL Server Functions.

• MOD(a, b): a modulus b
• CAST(value as new_type): cast a value to another type
• EXTRACT(element from some_date): extract XXX from a date

A clause 👷 is an instruction within the request, such as SELECT. There are 3 categories of clauses: DML, DDL, and DCL.

• Declaration SELECT>FROM>WHERE>GROUP BY>HAVING>ORDER BY>LIMIT
• Execution FROM>WHERE>GROUP BY>HAVING>SELECT>ORDER BY>LIMIT

Note that clause names are case-insensitive (select=SELECT=Select).

DDL (Data Definition) 💼: define a database/table

• CREATE DATABASE and CREATE TABLE
• ALTER DATABASE and ALTER TABLE
• DROP DATABASE and DROP TABLE

DCL (Data control) 🔐: define users and permissions

• CREATE USER and DROP USER
• CREATE ROLE and DROP ROLE
• GRANT
• REVOKE

DML (Data Manipulation) 💰: manipulate data

Use USE database_name first to select a database.

• SELECT (define what is queried)
• FROM (define which tables are queried)
• WHERE (define which records are queried)
• GROUP BY and HAVING (group results)
• ORDER BY (sort results)
• LIMIT (skip results, limit the number of results)

These statements will change the database (new record/...):

• INSERT
• UPDATE
• DELETE

Sometimes, you need to chain the queries in order to keep the database coherent. Every query in the transaction will be executed before another transaction (or query) is proceeded.

-- sequential
set transaction serializable

The transaction "ends" when you use either COMMIT or ROLLBACK. You can also use transactions to only allow some kinds of changes.

-- select
set transaction read only /*code*/
set transaction read committed /*code*/
-- update/insert/delete...
set transaction read write /*code/

SELECT now();
SELECT DATE('2020-12-03');
SELECT @@global.time_zone;

• ROLLBACK/COMMIT
• UNION/INTERSECT/EXCEPT (same number of values in SELECT/NULL). ORDER, or LIMIT can only be applied on the whole request.