SQL (Structured Query Language)

rappel sur les SGBDR

Les Systèmes de Gestion de Base de Données Relationnelles (SGBDR) permettent d’avoir :

Lien sur un classement des SGBD

Les langages que l’on utilise classiquement (java) sont des langages dit “procédural” ou “impératif”
SQL est un langage “déclaratif” : on ne définit pas d’étape pour arriver au résultat, on décrit le résultat

Dans la documentation, SQL est composé de “sous ensembles”, “sous langages”


Cas particulier :

Le NOSQL, exemple “MongoDB”


configuration et stockage de données

mysql> select @@datadir;

mysql>SHOW VARIABLES LIKE "%datadir%";

mysql>SHOW VARIABLES LIKE "%dir%";

mysql --help | grep cnf

(sur windows (gemini) : mysql --help | findstr /i "cnf")

 2001  sudo xed /etc/mysql/my.cnf
 2002  sudo xed /etc/mysql/mariadb.conf.d/50-server.cnf

 1142  ls /var/lib/mysql/
 1143  sudo ls /var/lib/mysql/
 1144  sudo ls /var/lib/
 1145  sudo ls /var/lib/mysql
 1146  sudo ls /var/lib/mysql/S3_BDD
 1147  sudo ls /var/lib/mysql/S2_BDD
 1148  sudo cat /var/lib/mysql/S2_BDD/ADHERENT.ibd

 1149  sudo cat /var/lib/mysql/S2_BDD/ADHERENT.frm

https://www.digitalocean.com/community/tutorials/how-to-move-a-mysql-data-directory-to-a-new-location-on-ubuntu-16-04

les types de langages (sous ensembles de commandes, instructions) en SQL

Connaître ces mots clés facilite la recherche d’informations dans la documentation d’un SGBDR.

origine du SQL : algèbre relationnelle

Le SQL est issu de l’Algèbre Relationnelle introduite par CODD en 1970 (IBM). L’Algèbre Relationnelle permet de formaliser les opérations sur les ensembles. C’est une approche un peu plus mathématique avec un formalisme un peu différent.

                  -- ALGEBRE RELATIONNELLE      
SELECT            -- PROJECTION                     
FROM                                               
WHERE             -- RESTRICTION                   
GROUP BY
HAVING
ORDER BY


Création d’une table

les clés

l’Intégrité référentielle est une situation dans laquelle pour chaque information d’une table A qui fait référence à une information d’une table B, l’information référencée existe dans la table B. L’intégrité référentielle est un gage de cohérence du contenu de la base de données.


Exemple particulier : Un employé passe des commandes et quitte l’entreprise. Si on supprime l’employé de la base de données, on a plus de référence avec ses commandes. Une solution consiste à ajouter un champ départ pour signaler la date à laquelle l’employé a quitté l’entreprise.


schéma




Tester la commande ci dessous :

SELECT * FROM INFORMATION_SCHEMA.TABLES;


TEST : accès aux données d’une BDD à une autre BDD


RAPPEL : SELECT * FROM schema_name.table_name;

types de données en SQL

Documentations

les dates

format dates voir les exemples de la documentation ci-dessus (sensiblement identiques dans les différents SGBDR)

type “date/heure”

Access MySQL Oracle PostgreSQL
date date date date
time time date time
datetime datetime date timestamp

les numériques


type “Numérique exact : entier”

Access MySQL Oracle PostgreSQL
int int number(10) integer
byte tinyint number(3) smallint
smallint smallint number(5) smallint


type “Numérique exact : avec virgule”

Access MySQL Oracle PostgreSQL SQL server
decimal(n,d) decimal(n,d) number(n) numeric(n) decimal(n,d)


type “Numérique approximatif” : réel

Access MySQL Oracle PostgreSQL SQL server
real float binary_float real float
double doble binary_double double precision real


Pour enregistrer une donnée qui est un numérique avec une virgule comme un prix, utiliser de préférence un type numérique exact (DECIMAL ou NUMBER(ORACLE)), sinon la valeur stockée ne sera pas exacte.

les chaînes de caractères

type “TEXTE”

Access MySQL Oracle PostgreSQL
varchar(n) varchar(n) varchar2(n) varchar(n)
char(n) char(n) char(n) char(n)
text text text clob

http://www.chine-nouvelle.com/outils/dictionnaire.html

Tester la requête ci-dessous:

SELECT '你好';

Le TD suivant porte sur les problèmes liés aux chaînes de caractères

gros objets

les booleens

valeur “vrai” ou “faux” codée sur un octet

Access MySQL Oracle PostgreSQL SQL server
logical tinyint(1) number(1) booleen byte

les autres types

currency : valeur monétaire codée sur 8 octets comprenant 15 chiffres + 4 décimales

Access MySQL Oracle PostgreSQL SQL server
currency decimal(19,4) number(19,4) money money

Propriété AUTO_INCREMENT sur MSQL

AUTO_INCREMENT : on doit mettre à NULL le champ lors de la création d’un enregistrement. Il est interdit de mettre une valeur sur SQLSERVER, c’est autorisé sur MYSQL.

Access MySQL Oracle PostgreSQL SQL server
AUTOINCREMENT int AUTO_INCREMENT number(10) + trigger serial identity(seed,increment)

documentation mariadb

ORACLE : c’est plus compliqué, utilisation d’un mécanisme de séquenceur (objet qui gère l’incrémentation des numéros => déclencheur(comme un TRIGGER qui incrémente ce numéro)


Ce mot clé prend une forme différente selon le SGBDR, voir AUTO_INCREMENT ou sur postgresql 

DROP TABLE IF EXISTS TD_demo1;
CREATE TABLE TD_demo1(
id INT AUTO_INCREMENT,
CONSTRAINT PK_id_TD_demo1 PRIMARY KEY (id)
);

INSERT INTO TD_demo1 VALUES (NULL);
INSERT INTO TD_demo1 VALUES (NULL);
SELECT LAST_INSERT_ID();

SELECT * FROM TD_demo1;
SHOW CREATE TABLE TD_demo1;

DELETE FROM TD_demo1;

INSERT INTO TD_demo1 VALUES (NULL);
INSERT INTO TD_demo1 VALUES (NULL);
SELECT LAST_INSERT_ID();

SELECT * FROM TD_demo1;
SHOW CREATE TABLE TD_demo1;

TRUNCATE TABLE  TD_demo1;

INSERT INTO TD_demo1 VALUES (NULL);
SELECT LAST_INSERT_ID();

SELECT * FROM TD_demo1;
SHOW CREATE TABLE TD_demo1;
DROP TABLE IF EXISTS TD_demo1;

rôle de : ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 en fin de création de table



LAST_INSERT_ID

SELECT LAST_INSERT_ID();

TEST :

DROP TABLE IF EXISTS TD_demo1;
CREATE TABLE TD_demo1(
id SERIAL
);
DESCRIBE TD_demo1;
SHOW CREATE TABLE TD_demo1;
INSERT INTO TD_demo1 VALUES (NULL);
DROP TABLE IF EXISTS TD_demo1;

REMARQUE : ATTENTION, les clés étrangéres doivent avoir le même type que les clés primaires

les clés étrangéres

-- pas d'utilisation de compteur : AUTO_INCREMENT

DROP TABLE IF EXISTS TD_groupe;
DROP TABLE IF EXISTS TD_etudiant;

CREATE TABLE TD_groupe (
    idGroupe int
    , libelle varchar(25)
    , PRIMARY KEY (idGroupe)
);
SHOW CREATE TABLE TD_groupe;

CREATE TABLE TD_etudiant (
    noEtudiant int
    , nomEtudiant varchar(25)
    , idGroupe int
    , PRIMARY KEY (noEtudiant)
    , CONSTRAINT fk_TD_etudiant_groupe
        FOREIGN KEY (idGroupe) 
        REFERENCES TD_groupe(idGroupe)
);
SHOW CREATE TABLE TD_etudiant;

INSERT INTO TD_groupe VALUES(1,'S2A1');  -- id 1
INSERT INTO TD_groupe VALUES(2,'S2A2'); -- id 2
INSERT INTO TD_groupe VALUES(3,'S2bisA1'); -- id 3

INSERT INTO TD_etudiant VALUES(1,'paul',1);
INSERT INTO TD_etudiant VALUES(2,'pierre',2);
INSERT INTO TD_etudiant VALUES(3,'toto',3);

SELECT * FROM TD_etudiant;
UPDATE TD_groupe SET idGroupe=25 WHERE idGroupe=2;
SELECT * FROM TD_etudiant;
DELETE FROM  TD_groupe  WHERE idGroupe=1;
SELECT * FROM TD_etudiant;
SELECT * FROM TD_groupe;

DROP TABLE IF EXISTS TD_etudiant;
DROP TABLE IF EXISTS TD_groupe;
SHOW TABLES;






UUID : universally Unique Identifier

wikipedia : Il existe plusieurs algorithmes (versions) pour calculer un UUID


UUID existe en python, javascript

documentation SQL uuid ; SQL uuid type


DROP TABLE IF EXISTS TD_etudiant;


CREATE TABLE TD_etudiant (
    noEtudiant varchar(255)
    , nomEtudiant varchar(25)
    , idGroupe int
    , PRIMARY KEY (noEtudiant)
);
SHOW CREATE TABLE TD_etudiant;


INSERT INTO TD_etudiant VALUES(UUID(),'paul',1);
INSERT INTO TD_etudiant VALUES(UUID(),'pierre',2);
INSERT INTO TD_etudiant VALUES(UUID(),'toto',3);

SELECT * FROM TD_etudiant;

DROP TABLE IF EXISTS TD_etudiant;
SHOW TABLES;

Avantages :


Inconvénient :


REFLEXION :