L'aide mémoire du petit Louc

Cela fait trop longtemps que je traine un fichier texte qui contient mes notes à propos du langage SQL, dans le but à l'époque d'être utilisé avec MySQL, puis MariaDB suite au rachat de Sun par Oracle… Une volonté (inutile ?) d'avoir un outil le plus ouvert possible ?

Après avoir installé mariadb et mariadb-client (ou mysql), il convient de sécuriser l'installation. On utilise le script mariadb-secure-installation ou mysql_secure_installation

Note: Le script propose d'utiliser une authentification par unix-socket :
Cela limite l'identification à un mécanisme de socket-unix
Avantages :
- plus rapide quue TCP/IP
- plus sécurisé, car limite l'accès en local
Inconvénients :
- l'authentification à distance n'est plus possible (pb si on veut plug un BI comme Metabase)

Avant de commencer le vif du sujet, il faut connaître quel type de données on peut stocker

UNSIGNED permet de ne pas avoir de nombre négatif et donc une plage positive 2x plus grand

NUMERIC ou DECIMAL : définit un nombre de chiffres avant et après la virgule, valeurs exactes conservées
FLOAT, DOUBLE REAL : valeur approchée stockée sous forme scientifique

SELECT user FROM mysql.user;                             -- Afficher les utilisateurs
 
CREATE USER 'nom'@'localhost' IDENTIFIED BY 'password' ; -- crée un utilisateur
 
GRANT ALL ON nomBase.* TO 'nom'@'localhost' ;            -- donne les pleins droits à un utilisateur sur les bases de données

SHOW DATABASES;                                 -- lister les bases de données
CREATE DATABASE nom [CHARACTER SET 'utf8'] ;	-- permet de créer une base de données [avec un encdage en utf-8]
DROP DATABASE nom ;				-- permet de supprimer une base de données
USE nom ;					-- spécifie quelle base de données on doit utiliser

outil console :

mysqldump -u user -ppassword -B base > file
mysqldump -u user -p -B base > file    # on aura un prompt pour saisir le password

en console :

mysql dolibarrdb -u databaseuser -p < mybackupfile.sql  # on aura un prompt pour saisir le password

CREATE TABLE nom (				-- permet la création d'une table qui s'appelera nom
	colonne1 description_colonne1,		-- par exemple : id INT NOT NULL AUTO_INCREMENT
	..					-- par exemple : prenom VARCHAR(25)
	[PRIMARY KEY (colonnex)]
)
[ENGINE=moteur] ;				-- MYISAM, INNODB

DESCRIBE table ;				-- permet de d'afficher la structure de la table

ALTER TABLE nom_table 				    -- permet de modifier une table, plusieurs fonctions sont disponibles :
	ADD nom_colonne description ;		    -- ajouter une colonne
	DROP nom_colonne ;			    -- supprimer une colonne
	CHANGE ancien_nom nouveau_nom desciption ;  -- changer le nom de la colonne et de la description (sauf si identique)
	MODIFY nom_colonne nouvelle_description ;   -- changer le type de données

DROP TABLE nom_table ;				-- permet de supprimer une table, irréversible...

ils peuvent être créés directement à la création de la table ou par modification de celle ci :

ALTER TABLE nom-table                          -- méthode 1 pour créer un index
	ADD INDEX [nom index](col,..)	       -- un index tout simple, pour accélérer les recherches
	ADD UNIQUE			       -- un index avec contrainte d'unité
	ADD FULLTEXT			       -- MyISAM seulement, sur les colonnes de texte; caractères...
 
CREATE [UNIQUE/FULLTEXT] INDEX nom_index       -- méthode 2 pour créer un index
	ON nom_table (col,..)
 
ALTER TABLE nom_table			       -- requete pour supprimer un index
	DROP INDEX nom_index

INSERT INTO table (col1, col2)				-- requête pour l'insertion de données
	VALUES (valeur1,valeur2) [,(valeur,valeur)] ;	-- ajout de plusieurs lignes possibles
 
INSERT							-- requête permettant l'ajout d'une ligne ou la mise à jour d'un ligne
	INTO table (col1,col2)				-- si l'ajout causerait un doublon (selon un index/clé UNIQUE)
	VALUES (valeur, valeur)
	ON DUPLICATE KEY UPDATE col=valeur, ..
 
SOURCE fichier.sql;					-- effectue toutes les requêtes contenues dans le fichier
 
LOAD DATA LOCAL INFILE 'fichier.csv'			-- permet de charger des valeurs à partir d'un fichier au format spécifié
	INTO TABLE nom_table
	FIELDS TERMINATED BY ';'
	LINES TERMINATED BY '\n'
	IGNORE 1 LINES
	(col1,col2..) ;

SELECT 'xxx' ;						-- renvoie 'xxx'
SELECT col1, Col2 FROM nom_table ;			-- renvoie toutes les valeurs contenu dans col1 et col2 de la table nom_table
SELECT * FROM nom_table ;				-- renvoie tout le contenu de la table (tout le contenu de toutes les colonnes)
SELECT colonne						-- renvoie le contenu de colonne de la table dans le cas où col=...
	FROM table 
	WHERE col=... AND/OR ...
 
=  <  <=  >  >=  !=  <=>(égal pour NULL)
AND OR XOR NOT
IS NOT NULL
 
SELECT col FROM table WHERE .. ORDER BY ..		-- permet de trier les données avant de les afficher
ORDER BY col DESC/ASC					-- permet de définir l'ordre croissant/décroissant
ORDER BY col1 , col2					-- trier selon col1, puis col2
 
SELECT DISTINCT col FROM table				-- permet de ne pas afficher de doublon
 
SELECT col FROM table LIMIT n OFFSET o			-- limite le nombre de ligne affichée, éventuellement avec un offset

DELETE FROM table WHERE critères ;			-- supprime les lignes de la table pour lesquels le critère est vrai

UPDATE table						-- permet de modifier toutes les lignes pour lesquelles le critère est vrai, la modification porte sur les arguments spécifiés
	SET col1=valeur, col2=valeur
	WHERE critère