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? 🎓


Useful tools 🐲

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

SQL Basics 🃏

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

SQL Clauses 👷

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

DCL (Data control) 🔐: define users and permissions

DML (Data Manipulation) 💰: manipulate data

Use USE database_name first to select a database.

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


Transactions

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/

đŸ‘ģ To-do đŸ‘ģ

Stuff that I found, but never read/used yet.

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.
Views

Views are virtual tables, that may be used to make things easier to access complex tables, or to prevent users from accessing some attributes in a table. Basically, a view is a virtual table created from a SQL request.

-- create
CREATE VIEW viewName [Attributes] AS sqlRequest
-- check delete/update before creating a view
CREATE VIEW viewName [ Attributes ] AS sqlRequest WITH CHECK OPTION
-- delete
DROP VIEW viewName
Schema/Domain

A schema 🗃ī¸ is a sort of namespace in which there are tables, and other related stuff. See the table INFORMATION_SCHEMA (TABLES, USERS, COLUMNS/DOMAINS, CHECK_CONSTRAINTS).

➡ī¸ You may have to use schema_name.table_name in your requests.

A domain 🌍 refers to the values that an attribute can take. This is determined by the type, and the constraints on it.