Mirajv1.0
EN
EnglishFrançais

3. Getting Started

This chapter guides you step by step: starting a MIRAJ server, connecting to it, creating a database and a table, inserting data, querying it, joining it with another table, indexing and inspecting the schema. By the end, you will be able to run a complete MIRAJ use case.

The examples use miraj-cli, the SQL console provided with MIRAJ, but everything carries over unchanged to the network protocol of miraj-server with the SQL client of your choice.

3.1 Starting the Server#

miraj-server.exe --root D:\data --log
MIRAJ 1.0.0 Enterprise - console server on 127.0.0.1:7007, folder "D:\data".

The D:\data folder is created if it does not exist, with a local root account with no password. Leave this window open: the server remains active in it.

3.2 Connecting with miraj-cli#

Open a second window and launch the SQL console, embedded directly on the same data folder (without going through the network):

miraj-cli.exe --root D:\data
MIRAJ 1.0.0 Enterprise - tapez \help pour l'aide, \q pour quitter.
miraj>

You are now in interactive mode (REPL): each SQL statement ends with a semicolon, and can be written across several lines.

3.3 Creating a Database#

CREATE DATABASE boutique;
USE boutique;
OK, 0 ligne(s) affectée(s)
OK, 0 ligne(s) affectée(s)

The prompt now displays the name of the current database:

boutique>

3.4 Creating a Table#

Let's create a customers table, with an auto-incremented primary key, a UNIQUE constraint and a required column:

CREATE TABLE clients (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    nom         VARCHAR(80) NOT NULL,
    email       VARCHAR(120) NOT NULL UNIQUE,
    cree_le     DATETIME DEFAULT CURRENT_TIMESTAMP
);
OK, 0 ligne(s) affectée(s)

Let's also create an orders table, linked to customers by a foreign key:

CREATE TABLE commandes (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    client_id   INT NOT NULL,
    montant     DECIMAL(10,2) NOT NULL,
    passee_le   DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (client_id) REFERENCES clients(id)
);
OK, 0 ligne(s) affectée(s)

3.5 Inserting Data#

INSERT INTO clients (nom, email) VALUES
    ('Amel Kaci', '[email protected]'),
    ('Youssef Ben Ali', '[email protected]'),
    ('Claire Dupont', '[email protected]');
OK, 3 ligne(s) affectée(s)
INSERT INTO commandes (client_id, montant) VALUES
    (1, 59.90),
    (1, 12.50),
    (2, 249.00);
OK, 3 ligne(s) affectée(s)

An insertion attempt that violates the UNIQUE constraint on email is rejected:

INSERT INTO clients (nom, email) VALUES ('Doublon', '[email protected]');
ERREUR 1062 (23000) : Duplicate entry '[email protected]' for key 'email'

3.6 Querying Data: WHERE and ORDER BY#

SELECT id, nom, email FROM clients WHERE nom LIKE '%a%' ORDER BY nom;
+----+------------------+----------------------------+
| id | nom              | email                      |
+----+------------------+----------------------------+
|  1 | Amel Kaci        | [email protected]      |
|  3 | Claire Dupont    | [email protected]  |
+----+------------------+----------------------------+
2 ligne(s)

3.7 Performing a Join#

Let's list the orders with the corresponding customer name, sorted by descending amount:

SELECT c.nom, o.id AS commande, o.montant
FROM commandes o
JOIN clients c ON c.id = o.client_id
ORDER BY o.montant DESC;
+------------------+-----------+---------+
| nom              | commande  | montant |
+------------------+-----------+---------+
| Youssef Ben Ali  |         3 |  249.00 |
| Amel Kaci        |         1 |   59.90 |
| Amel Kaci        |         2 |   12.50 |
+------------------+-----------+---------+
3 ligne(s)

An aggregate per customer, with GROUP BY:

SELECT c.nom, COUNT(*) AS nb_commandes, SUM(o.montant) AS total
FROM clients c
LEFT JOIN commandes o ON o.client_id = c.id
GROUP BY c.id
ORDER BY total DESC;
+------------------+--------------+--------+
| nom              | nb_commandes | total  |
+------------------+--------------+--------+
| Youssef Ben Ali  |            1 | 249.00 |
| Amel Kaci        |            2 |  72.40 |
| Claire Dupont    |            0 |   NULL |
+------------------+--------------+--------+
3 ligne(s)

3.8 Creating an Index#

An index speeds up searches on a frequently filtered column that is neither a primary key nor UNIQUE:

CREATE INDEX idx_commandes_client ON commandes (client_id);
OK, 0 ligne(s) affectée(s)

It is removed with:

DROP INDEX idx_commandes_client ON commandes;

3.9 Viewing the Schema#

List the tables of the current database:

SHOW TABLES;
+---------------------+
| Tables_in_boutique  |
+---------------------+
| clients             |
| commandes           |
+---------------------+
2 ligne(s)

Describe a table:

DESCRIBE clients;
+---------+--------------+------+-----+-------------------+----------------+
| Field   | Type         | Null | Key | Default           | Extra          |
+---------+--------------+------+-----+-------------------+----------------+
| id      | int          | NO   | PRI | NULL              | auto_increment |
| nom     | varchar(80)  | NO   |     | NULL              |                |
| email   | varchar(120) | NO   | UNI | NULL              |                |
| cree_le | datetime     | YES  |     | CURRENT_TIMESTAMP |                |
+---------+--------------+------+-----+-------------------+----------------+
4 ligne(s)

View the complete definition of a table:

SHOW CREATE TABLE commandes;

In the miraj-cli console, the shortcuts \d (without argument: equivalent to SHOW TABLES; with a table name: equivalent to DESCRIBE) and \l (equivalent to SHOW DATABASES) save you from retyping these statements; \help lists all console commands.

3.10 Next Steps#

You now know how to create a database, a table with constraints, insert data, query it with joins and aggregation, index and inspect the schema. The following chapters detail each topic: