1. SELECT - Lecture de donnees

1.1 Syntaxe de base

-- Toutes les colonnes
SELECT * FROM T_CLIENT;

-- Colonnes specifiques
SELECT CLI_NOM, CLI_PRENOM, CLI_EMAIL FROM T_CLIENT;

-- Alias de colonnes
SELECT CLI_NOM AS nom, CLI_PRENOM AS prenom FROM T_CLIENT;

-- Alias de table
SELECT c.CLI_NOM, c.CLI_EMAIL FROM T_CLIENT c;

1.2 DISTINCT - Valeurs uniques

SELECT DISTINCT CLI_VILLE FROM T_CLIENT;

-- Nombre de valeurs uniques
SELECT COUNT(DISTINCT CLI_VILLE) AS nb_villes FROM T_CLIENT;

1.3 ORDER BY - Tri

-- Tri ascendant (par defaut)
SELECT * FROM T_CLIENT ORDER BY CLI_NOM ASC;

-- Tri descendant
SELECT * FROM T_CLIENT ORDER BY CLI_DATE_CREATION DESC;

-- Tri multi-colonnes
SELECT * FROM T_CLIENT ORDER BY CLI_VILLE ASC, CLI_NOM DESC;

1.4 LIMIT / OFFSET - Pagination

-- Les 10 premiers
SELECT * FROM T_CLIENT LIMIT 10;

-- Page 2 (enregistrements 11 a 20)
SELECT * FROM T_CLIENT LIMIT 10 OFFSET 10;

-- Equivalent MySQL raccourci
SELECT * FROM T_CLIENT LIMIT 10, 10;

2. WHERE - Filtres et conditions

2.1 Operateurs de comparaison

Operateur	Description	Exemple
=	Egal	WHERE CLI_VILLE = 'Nantes'
!= ou <>	Different	WHERE REMOVED <> '1'
< > <= >=	Comparaison	WHERE CLI_ID > 100
BETWEEN	Intervalle inclusif	WHERE CLI_ID BETWEEN 10 AND 50
IN	Liste de valeurs	WHERE CLI_VILLE IN ('Nantes', 'Paris')
NOT IN	Exclusion de liste	WHERE CLI_VILLE NOT IN ('Lyon')
IS NULL	Valeur nulle	WHERE CLI_EMAIL IS NULL
IS NOT NULL	Valeur non nulle	WHERE CLI_EMAIL IS NOT NULL

2.2 LIKE - Recherche par motif

-- Commence par
SELECT * FROM T_CLIENT WHERE CLI_NOM LIKE 'DUP%';

-- Contient
SELECT * FROM T_CLIENT WHERE CLI_NOM LIKE '%martin%';

-- Un seul caractere joker
SELECT * FROM T_CLIENT WHERE CLI_CODE LIKE '1_A%';

-- Echapper le underscore (chercher un vrai _)
SELECT * FROM T_ADHERENT WHERE ADH_CODE LIKE '1\_%';

2.3 Operateurs logiques

-- AND : toutes les conditions
SELECT * FROM T_CLIENT
WHERE CLI_VILLE = 'Nantes' AND REMOVED = '0';

-- OR : au moins une condition
SELECT * FROM T_CLIENT
WHERE CLI_VILLE = 'Nantes' OR CLI_VILLE = 'Paris';

-- Combiner avec parentheses
SELECT * FROM T_CLIENT
WHERE (CLI_VILLE = 'Nantes' OR CLI_VILLE = 'Paris')
  AND REMOVED = '0';

3. INSERT - Insertion

-- Insertion simple
INSERT INTO T_CLIENT (CLI_NOM, CLI_PRENOM, CLI_VILLE)
VALUES ('Dupont', 'Jean', 'Nantes');

-- Insertion multiple
INSERT INTO T_CLIENT (CLI_NOM, CLI_PRENOM, CLI_VILLE)
VALUES
    ('Dupont', 'Jean', 'Nantes'),
    ('Martin', 'Paul', 'Paris'),
    ('Durand', 'Marie', 'Lyon');

-- Insertion depuis un SELECT
INSERT INTO T_CLIENT_ARCHIVE (CLI_NOM, CLI_VILLE)
SELECT CLI_NOM, CLI_VILLE
FROM T_CLIENT
WHERE REMOVED = '1';

4. UPDATE - Mise a jour

-- Mise a jour simple
UPDATE T_CLIENT
SET CLI_VILLE = 'Paris'
WHERE CLI_ID = 42;

-- Mise a jour multi-colonnes
UPDATE T_CLIENT
SET CLI_VILLE = 'Paris',
    CLI_EMAIL = 'contact@test.fr',
    LAST_MODIFY = NOW()
WHERE CLI_ID = 42;

-- Mise a jour avec calcul
UPDATE T_PRODUIT
SET PRD_PRIX = PRD_PRIX * 1.05
WHERE PRD_CATEGORIE = 'PREMIUM';

-- Mise a jour avec sous-requete
UPDATE T_CLIENT c
SET c.CLI_VILLE = (SELECT a.ADR_VILLE FROM T_ADRESSE a WHERE a.CLI_ID = c.CLI_ID LIMIT 1)
WHERE c.CLI_VILLE IS NULL;

5. DELETE - Suppression

-- Suppression avec condition
DELETE FROM T_CLIENT WHERE CLI_ID = 42;

-- Suppression multi-criteres
DELETE FROM T_LOG
WHERE LOG_DATE < '2025-01-01'
  AND LOG_TYPE = 'DEBUG';

-- Vider une table (plus rapide que DELETE)
TRUNCATE TABLE T_LOG_TEMP;

6. Jointures (JOIN)

6.1 Schema visuel des jointures

  INNER JOIN        LEFT JOIN         RIGHT JOIN        FULL OUTER JOIN
  ┌───┐ ┌───┐      ┌───┐ ┌───┐      ┌───┐ ┌───┐      ┌───┐ ┌───┐
  │   │█│   │      │███│█│   │      │   │█│███│      │███│█│███│
  │ A │█│ B │      │███│█│ B │      │ A │█│███│      │███│█│███│
  │   │█│   │      │███│█│   │      │   │█│███│      │███│█│███│
  └───┘ └───┘      └───┘ └───┘      └───┘ └───┘      └───┘ └───┘
  Intersection      Tout A +         Tout B +          Tout A +
  seulement         match B          match A           Tout B

6.2 INNER JOIN - Intersection

Retourne uniquement les lignes qui ont une correspondance dans les deux tables.

SELECT c.CLI_NOM, v.VTE_DATE
FROM T_CLIENT c
INNER JOIN T_VENTE v ON v.CLI_ID = c.CLI_ID;

6.3 LEFT JOIN - Tout a gauche

Retourne toutes les lignes de la table de gauche, meme sans correspondance (NULL a droite).

-- Tous les clients, meme ceux sans vente
SELECT c.CLI_NOM, v.VTE_DATE
FROM T_CLIENT c
LEFT JOIN T_VENTE v ON v.CLI_ID = c.CLI_ID;

-- Trouver les clients SANS vente
SELECT c.CLI_NOM
FROM T_CLIENT c
LEFT JOIN T_VENTE v ON v.CLI_ID = c.CLI_ID
WHERE v.VTE_ID IS NULL;

6.4 RIGHT JOIN - Tout a droite

SELECT c.CLI_NOM, v.VTE_DATE
FROM T_CLIENT c
RIGHT JOIN T_VENTE v ON v.CLI_ID = c.CLI_ID;

6.5 Jointures multiples

-- 3 tables liees
SELECT a.ADH_CODE, v.VTE_NOM_CLIENT, d.DEV_NUMERO
FROM T_ADHERENT a
JOIN T_VENTE v ON v.ADH_ID = a.ADH_ID
JOIN T_DEVIS d ON d.VTE_ID = v.VTE_ID
WHERE a.ADH_CODE LIKE '1\_%';

6.6 Self JOIN - Jointure sur elle-meme

-- Trouver les adherents enfants de leur parent
SELECT enfant.ADH_CODE AS code_enfant,
       parent.ADH_CODE AS code_parent
FROM T_ADHERENT enfant
JOIN T_ADHERENT parent ON parent.ADH_ID = enfant.PARENT_ADH_ID;

6.7 CROSS JOIN - Produit cartesien

-- Toutes les combinaisons (rarement utilise)
SELECT c.CLI_NOM, p.PRD_NOM
FROM T_CLIENT c
CROSS JOIN T_PRODUIT p;

7. Agregation (GROUP BY, HAVING)

7.1 Fonctions d'agregation

Fonction	Description	Exemple
COUNT(*)	Nombre de lignes	SELECT COUNT(*) FROM T_CLIENT
COUNT(col)	Nombre de non-NULL	SELECT COUNT(CLI_EMAIL) FROM T_CLIENT
SUM(col)	Somme	SELECT SUM(MNT_TTC) FROM T_CNT_MONTANT
AVG(col)	Moyenne	SELECT AVG(MNT_TTC) FROM T_CNT_MONTANT
MIN(col)	Minimum	SELECT MIN(VTE_DATE) FROM T_VENTE
MAX(col)	Maximum	SELECT MAX(VTE_DATE) FROM T_VENTE

7.2 GROUP BY - Regroupement

-- Nombre de clients par ville
SELECT CLI_VILLE, COUNT(*) AS nb_clients
FROM T_CLIENT
GROUP BY CLI_VILLE
ORDER BY nb_clients DESC;

-- Chiffre d'affaires par adherent
SELECT a.ADH_CODE,
       COUNT(d.DEV_ID) AS nb_devis,
       SUM(m.MNT_TTC) AS ca_total
FROM T_ADHERENT a
JOIN T_VENTE v ON v.ADH_ID = a.ADH_ID
JOIN T_DEVIS d ON d.VTE_ID = v.VTE_ID
JOIN T_CNT_MONTANT m ON m.CNT_ID = d.CNT_ID
GROUP BY a.ADH_CODE;

7.3 HAVING - Filtre apres agregation

-- Adherents avec moins de 10 devis
SELECT a.ADH_CODE, COUNT(d.DEV_ID) AS nb_devis
FROM T_ADHERENT a
JOIN T_VENTE v ON v.ADH_ID = a.ADH_ID
JOIN T_DEVIS d ON d.VTE_ID = v.VTE_ID
GROUP BY a.ADH_CODE
HAVING nb_devis < 10;

8. Sous-requetes

8.1 Sous-requete dans WHERE

-- Clients ayant au moins une vente
SELECT * FROM T_CLIENT
WHERE CLI_ID IN (SELECT DISTINCT CLI_ID FROM T_VENTE);

-- Produits plus chers que la moyenne
SELECT * FROM T_PRODUIT
WHERE PRD_PRIX > (SELECT AVG(PRD_PRIX) FROM T_PRODUIT);

8.2 EXISTS - Test d'existence

-- Adherents qui ont au moins un devis
SELECT a.ADH_CODE
FROM T_ADHERENT a
WHERE EXISTS (
    SELECT 1 FROM T_VENTE v
    JOIN T_DEVIS d ON d.VTE_ID = v.VTE_ID
    WHERE v.ADH_ID = a.ADH_ID
);

-- Adherents qui n'ont AUCUN devis
SELECT a.ADH_CODE
FROM T_ADHERENT a
WHERE NOT EXISTS (
    SELECT 1 FROM T_VENTE v
    WHERE v.ADH_ID = a.ADH_ID
);

8.3 Sous-requete dans FROM (table derivee)

SELECT sub.ADH_CODE, sub.nb_devis
FROM (
    SELECT a.ADH_CODE, COUNT(d.DEV_ID) AS nb_devis
    FROM T_ADHERENT a
    JOIN T_VENTE v ON v.ADH_ID = a.ADH_ID
    JOIN T_DEVIS d ON d.VTE_ID = v.VTE_ID
    GROUP BY a.ADH_CODE
) sub
WHERE sub.nb_devis BETWEEN 5 AND 20;

9. Fonctions courantes

9.1 Chaines de caracteres

Fonction	Description	Exemple
CONCAT(a, b)	Concatenation	CONCAT(CLI_NOM, ' ', CLI_PRENOM)
UPPER(s)	Majuscules	UPPER(CLI_NOM)
LOWER(s)	Minuscules	LOWER(CLI_EMAIL)
TRIM(s)	Suppr espaces	TRIM(CLI_NOM)
SUBSTRING(s, pos, len)	Extraire	SUBSTRING(CLI_CODE, 1, 5)
LENGTH(s)	Longueur	LENGTH(CLI_NOM)
REPLACE(s, old, new)	Remplacer	REPLACE(CLI_TEL, ' ', '')
LEFT(s, n)	N premiers chars	LEFT(CLI_CODE, 3)
RIGHT(s, n)	N derniers chars	RIGHT(LIC_CODE, 4)

9.2 Dates et heures

Fonction	Description	Exemple
NOW()	Date/heure actuelle	SELECT NOW()
CURDATE()	Date du jour	WHERE VTE_DATE = CURDATE()
DATE(dt)	Partie date	DATE(LAST_MODIFY)
YEAR(dt)	Annee	YEAR(VTE_DATE)
MONTH(dt)	Mois	MONTH(VTE_DATE)
DAY(dt)	Jour	DAY(VTE_DATE)
DATEDIFF(a, b)	Difference en jours	DATEDIFF(NOW(), VTE_DATE)
DATE_ADD(dt, INTERVAL)	Ajouter	DATE_ADD(NOW(), INTERVAL 30 DAY)
DATE_SUB(dt, INTERVAL)	Soustraire	DATE_SUB(NOW(), INTERVAL 1 YEAR)
DATE_FORMAT(dt, fmt)	Formater	DATE_FORMAT(VTE_DATE, '%d/%m/%Y')

9.3 Conditions

-- IF (MySQL)
SELECT CLI_NOM,
       IF(REMOVED = '1', 'Supprime', 'Actif') AS statut
FROM T_CLIENT;

-- CASE WHEN (standard SQL)
SELECT CLI_NOM,
       CASE
           WHEN LIC_ARRETER = '1' THEN 'Arrete'
           WHEN LIC_BLOQUER = '1' THEN 'Bloque'
           WHEN REMOVED = '1' THEN 'Supprime'
           ELSE 'Actif'
       END AS statut
FROM T_ADH_LICENCE;

-- IFNULL / COALESCE
SELECT IFNULL(CLI_EMAIL, 'pas d email') FROM T_CLIENT;
SELECT COALESCE(CLI_EMAIL, CLI_TEL, 'aucun contact') FROM T_CLIENT;

9.4 Numeriques

Fonction	Description	Exemple
ROUND(n, d)	Arrondir	ROUND(MNT_TTC, 2)
CEIL(n)	Entier superieur	CEIL(3.2) = 4
FLOOR(n)	Entier inferieur	FLOOR(3.8) = 3
ABS(n)	Valeur absolue	ABS(-5) = 5
MOD(n, d)	Modulo	MOD(10, 3) = 1

10. CREATE / ALTER / DROP

10.1 Creer une table

CREATE TABLE T_EXEMPLE (
    EXM_ID       INT NOT NULL AUTO_INCREMENT,
    EXM_NOM      VARCHAR(100) NOT NULL,
    EXM_EMAIL    VARCHAR(255) DEFAULT NULL,
    EXM_PRIX     DECIMAL(18,4) DEFAULT 0,
    EXM_ACTIF    CHAR(1) DEFAULT '1',
    EXM_DATE     DATETIME DEFAULT NOW(),
    REMOVED      CHAR(1) DEFAULT '0',
    ADH_ID       INT NOT NULL,
    PRIMARY KEY (EXM_ID),
    KEY idx_adh (ADH_ID),
    CONSTRAINT fk_adh FOREIGN KEY (ADH_ID) REFERENCES T_ADHERENT(ADH_ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

10.2 Modifier une table

-- Ajouter une colonne
ALTER TABLE T_CLIENT ADD COLUMN CLI_NOTES TEXT DEFAULT NULL;

-- Modifier une colonne
ALTER TABLE T_CLIENT MODIFY COLUMN CLI_NOM VARCHAR(200) NOT NULL;

-- Renommer une colonne (MySQL 8+)
ALTER TABLE T_CLIENT RENAME COLUMN CLI_TEL TO CLI_TELEPHONE;

-- Supprimer une colonne
ALTER TABLE T_CLIENT DROP COLUMN CLI_NOTES;

-- Ajouter un index
ALTER TABLE T_CLIENT ADD INDEX idx_ville (CLI_VILLE);

-- Ajouter une cle etrangere
ALTER TABLE T_VENTE ADD CONSTRAINT fk_cli
    FOREIGN KEY (CLI_ID) REFERENCES T_CLIENT(CLI_ID);

10.3 Supprimer

-- Supprimer une table
DROP TABLE T_EXEMPLE;
DROP TABLE IF EXISTS T_EXEMPLE;

-- Supprimer une base
DROP DATABASE IF EXISTS ma_base;

11. Index et performances

11.1 Creer des index

-- Index simple
CREATE INDEX idx_ville ON T_CLIENT(CLI_VILLE);

-- Index unique
CREATE UNIQUE INDEX idx_email ON T_CLIENT(CLI_EMAIL);

-- Index composite (multi-colonnes)
CREATE INDEX idx_adh_removed ON T_ADH_LICENCE(ADH_ID, REMOVED);

-- Supprimer un index
DROP INDEX idx_ville ON T_CLIENT;

11.2 EXPLAIN - Analyser une requete

EXPLAIN SELECT * FROM T_CLIENT WHERE CLI_VILLE = 'Nantes';

-- Version detaillee (MySQL 8+)
EXPLAIN ANALYZE SELECT * FROM T_CLIENT WHERE CLI_VILLE = 'Nantes';

11.3 Informations sur les tables

-- Structure d'une table
DESCRIBE T_CLIENT;
SHOW COLUMNS FROM T_CLIENT;

-- Index d'une table
SHOW INDEX FROM T_CLIENT;

-- Taille des tables
SELECT TABLE_NAME,
       ROUND(DATA_LENGTH / 1024 / 1024, 2) AS data_mb,
       TABLE_ROWS
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'HPV5_MOD_PROD'
ORDER BY DATA_LENGTH DESC;

12. Transactions

-- Transaction basique
START TRANSACTION;

UPDATE T_COMPTE SET solde = solde - 100 WHERE CPT_ID = 1;
UPDATE T_COMPTE SET solde = solde + 100 WHERE CPT_ID = 2;

-- Si tout est OK
COMMIT;

-- Si erreur, annuler tout
ROLLBACK;

-- SAVEPOINT : point de sauvegarde intermediaire
START TRANSACTION;
UPDATE T_CLIENT SET CLI_VILLE = 'Paris' WHERE CLI_ID = 1;
SAVEPOINT avant_suppression;
DELETE FROM T_COMMANDE WHERE CLI_ID = 1;
-- Oups, annuler seulement le DELETE
ROLLBACK TO avant_suppression;
COMMIT;

13. Vues (VIEW)

-- Creer une vue
CREATE VIEW V_ADHERENT_ACTIF AS
SELECT a.ADH_ID, a.ADH_CODE, al.LIC_CODE
FROM T_ADHERENT a
JOIN T_ADH_LICENCE al ON al.ADH_ID = a.ADH_ID
WHERE al.LIC_ARRETER = '0'
  AND al.LIC_BLOQUER = '0'
  AND al.REMOVED = '0';

-- Utiliser comme une table
SELECT * FROM V_ADHERENT_ACTIF WHERE ADH_CODE LIKE '1\_%';

-- Remplacer une vue existante
CREATE OR REPLACE VIEW V_ADHERENT_ACTIF AS ...;

-- Supprimer une vue
DROP VIEW IF EXISTS V_ADHERENT_ACTIF;

14. CTE - Common Table Expression (WITH)

Les CTE permettent de nommer des sous-requetes pour une meilleure lisibilite.

-- CTE simple
WITH devis_par_adherent AS (
    SELECT v.ADH_ID, COUNT(d.DEV_ID) AS nb_devis
    FROM T_VENTE v
    JOIN T_DEVIS d ON d.VTE_ID = v.VTE_ID
    GROUP BY v.ADH_ID
)
SELECT a.ADH_CODE, dpa.nb_devis
FROM T_ADHERENT a
JOIN devis_par_adherent dpa ON dpa.ADH_ID = a.ADH_ID
WHERE dpa.nb_devis < 10;

-- CTE multiples
WITH
actifs AS (
    SELECT ADH_ID FROM T_ADH_LICENCE
    WHERE LIC_ARRETER = '0' AND REMOVED = '0'
),
stats AS (
    SELECT v.ADH_ID, COUNT(*) AS nb
    FROM T_VENTE v
    JOIN T_DEVIS d ON d.VTE_ID = v.VTE_ID
    GROUP BY v.ADH_ID
)
SELECT a.ADH_CODE, s.nb
FROM T_ADHERENT a
JOIN actifs ac ON ac.ADH_ID = a.ADH_ID
JOIN stats s ON s.ADH_ID = a.ADH_ID;

15. Fonctions fenetrees (OVER)

Les fonctions fenetrees effectuent un calcul sur un ensemble de lignes liees a la ligne courante, sans les regrouper.

15.1 ROW_NUMBER, RANK, DENSE_RANK

-- Numerotation des lignes
SELECT CLI_NOM, CLI_VILLE,
       ROW_NUMBER() OVER (ORDER BY CLI_NOM) AS row_num
FROM T_CLIENT;

-- Rang par groupe (classement par ville)
SELECT CLI_NOM, CLI_VILLE, CLI_CA,
       RANK() OVER (PARTITION BY CLI_VILLE ORDER BY CLI_CA DESC) AS rang
FROM T_CLIENT;

-- Top 3 clients par ville
SELECT * FROM (
    SELECT CLI_NOM, CLI_VILLE, CLI_CA,
           ROW_NUMBER() OVER (PARTITION BY CLI_VILLE ORDER BY CLI_CA DESC) AS rn
    FROM T_CLIENT
) sub WHERE rn <= 3;

15.2 SUM, AVG, COUNT fenetrees

-- Cumul glissant
SELECT VTE_DATE, MNT_TTC,
       SUM(MNT_TTC) OVER (ORDER BY VTE_DATE) AS cumul
FROM T_VENTE;

-- Moyenne par partition
SELECT CLI_NOM, CLI_VILLE, CLI_CA,
       AVG(CLI_CA) OVER (PARTITION BY CLI_VILLE) AS moy_ville
FROM T_CLIENT;

15.3 LAG / LEAD - Lignes precedente/suivante

-- Comparer avec la valeur precedente
SELECT VTE_DATE, MNT_TTC,
       LAG(MNT_TTC) OVER (ORDER BY VTE_DATE) AS precedent,
       MNT_TTC - LAG(MNT_TTC) OVER (ORDER BY VTE_DATE) AS variation
FROM T_VENTE;

16. Administration

16.1 Utilisateurs et privileges (MySQL)

-- Creer un utilisateur
CREATE USER 'app_user'@'localhost'
IDENTIFIED BY 'motdepasse_fort';

-- Donner des droits en lecture seule
GRANT SELECT ON HPV5_MOD_PROD.* TO 'app_user'@'localhost';

-- Donner tous les droits sur une base
GRANT ALL PRIVILEGES ON ma_base.* TO 'app_user'@'%';

-- Appliquer les changements
FLUSH PRIVILEGES;

-- Voir les droits d'un utilisateur
SHOW GRANTS FOR 'app_user'@'localhost';

-- Supprimer un utilisateur
DROP USER 'app_user'@'localhost';

16.2 Exploration de la base

-- Lister les bases
SHOW DATABASES;

-- Lister les tables
SHOW TABLES;
SHOW TABLES LIKE '%CLIENT%';

-- Structure d'une table
DESCRIBE T_CLIENT;

-- DDL complet d'une table
SHOW CREATE TABLE T_CLIENT;

-- Variables serveur
SHOW VARIABLES LIKE '%timeout%';

-- Processus en cours
SHOW PROCESSLIST;

-- Tuer un processus bloquant
KILL 12345;

16.3 Sauvegarde / Restauration (ligne de commande)

# Export d'une base complete
mysqldump -u root -p HPV5_MOD_PROD > backup.sql

# Export d'une table specifique
mysqldump -u root -p HPV5_MOD_PROD T_CLIENT > client_backup.sql

# Export structure seule (sans donnees)
mysqldump -u root -p --no-data HPV5_MOD_PROD > structure.sql

# Restauration
mysql -u root -p HPV5_MOD_PROD < backup.sql

16.4 Export CSV depuis MySQL

-- Methode 1 : INTO OUTFILE (necessite droits FILE)
SELECT * FROM T_CLIENT
INTO OUTFILE '/tmp/clients.csv'
FIELDS TERMINATED BY ';'
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

# Methode 2 : via la ligne de commande (sans droits FILE)
mysql -u user -p -e "SELECT * FROM T_CLIENT" HPV5_MOD_PROD | sed 's/\t/;/g' > clients.csv

17. Exemples HPV5 (HERCULEPRO)

17.1 Schema de liaison principal

T_ADHERENT (ADH_ID, ADH_CODE)
    |
    +--> T_ADH_LICENCE (ADH_ID, LIC_CODE, LIC_ARRETER, LIC_BLOQUER, REMOVED)
    |
    +--> T_VENTE (VTE_ID, ADH_ID, CLI_ID)
             |
             +--> T_DEVIS (DEV_ID, VTE_ID, CNT_ID)
             |        |
             |        +--> T_CNT_MONTANT (CNT_ID, MNT_HT, MNT_TTC)
             |
             +--> T_FACTURE (FAC_ID, VTE_ID, CNT_ID)
             |
             +--> T_COMMANDE (CMD_ID, VTE_ID, CNT_ID)

17.2 Adherents artisans actifs avec moins de N devis

SELECT a.ADH_CODE, COUNT(d.DEV_ID) AS nb_devis
FROM T_ADHERENT a
JOIN T_ADH_LICENCE al ON al.ADH_ID = a.ADH_ID
    AND al.LIC_ARRETER = '0'
    AND al.LIC_BLOQUER = '0'
    AND al.REMOVED = '0'
    AND al.LIC_CODE LIKE '%.4098'   -- Artisans
JOIN T_VENTE v ON v.ADH_ID = a.ADH_ID
JOIN T_DEVIS d ON d.VTE_ID = v.VTE_ID
WHERE a.ADH_CODE LIKE '1\_%'
GROUP BY a.ADH_CODE
HAVING nb_devis < 10
ORDER BY a.ADH_CODE;

17.3 Types de licence connus

Suffixe LIC_CODE	Type
.4098	Artisan
.4112	Standard
.4113	Autre

17.4 Connexion aux bases HPV5

# Base SAAS (production)
mysql -u herculepro -p HPV5_MOD_PROD -h 172.20.0.5

# Base CONFIG
mysql -u herculepro -p HPV5_CONFIG -h 172.20.0.5

# Base LOG
mysql -u herculepro -p HPV5_LOG -h 172.20.0.5

# Base compta-web (HPV5_CONFIG locale)
mysql -u herculepro -p HPV5_CONFIG -h compta-web.herculepro.net