L'aide mémoire du petit Louc

Cette page est en lecture seule. Vous pouvez afficher le texte source, mais ne pourrez pas le modifier. Contactez votre administrateur si vous pensez qu'il s'agit d'une erreur.
===== MySQL =====

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 ?

==== Sécuriser une fresh-installation ====
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)

==== Les types de données ====

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

== Numérique ==
^  NOM  ^  Taille  ^  Nombres stockables  ^
|  TINYINT  |  1o  |  -128 à 127  |
|  SMALLINT  |  2o  |  -32 768 à 32 767  |
|  MEDIUMINT  |  3o  |  -8 388 608 à 8 388 607  |
|  INT  |  4o  |  -2 147 483 648 à 2 147 483 647  |
|  BIGINT  |  8o  |  -9223372036854885808 à ...  |

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

== Alphanumérique ==
^  NOM  ^  DESCRIPTION  ^
|  VARCHAR(25)  |  permet de stocker une chaine de caractère (moins de 255 octets)  |
|  TEXT  |  permet de stocker un texte de 2%%^%%16 octets  |
|  BINARY  |  chaine binaire sur 1 octet  |
|  ENUM  |  permet de prendre une valeur parmi celles prédéfinies (par exemple 'chien', 'chat')  |

== Temporel ==
^  NOM  ^  DESCRIPTION  ^
|  DATE  |  permet de stocker une date, plusieurs formats possibles (par défaut : aaaa-mm-jj )  |
|  DATETIME  |  permet de stocker une date et une heure "aaaa-mm-jj hh:mm:ss"  |
|  TIME  |  permet de stocker une heure ou une durée (par défaut : "D HH:MM:SS.fraction")  |
|  YEAR	 |  permet de stocker une année (par défaut : YYYY)  |


==== Gérer les UTILISATEURS ====
<code mysql>
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
</code>

==== Gérer les BASES de données du serveur ====
<code mysql>
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
</code>

== SAUVEGARDE d'une base ==
outil console :
<code bash>
mysqldump -u user -ppassword -B base > file
mysqldump -u user -p -B base > file    # on aura un prompt pour saisir le password
</code>

== RESTORATION d'une base ==
en console :
<code bash>
mysql dolibarrdb -u databaseuser -p < mybackupfile.sql  # on aura un prompt pour saisir le password
</code>

==== Gérer les TABLES d'une base ====
== créer ==
<code mysql>
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
</code>

== Visualiser la structure ==

<code mysql>
DESCRIBE table ;				-- permet de d'afficher la structure de la table
</code>

== Modifier la structure ==
<code mysql>
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
</code>

== Supprimer ==
<code mysql>
DROP TABLE nom_table ;				-- permet de supprimer une table, irréversible...
</code>

== Gérer les INDEX d'une table ==
ils peuvent être créés directement à la création de la table ou par modification de celle ci :
<code mysql>
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
</code>


==== AGIR SUR LES DONNEES ====

== Ajouter des DONNEES ==
<code mysql>
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..) ;
</code>

== Récupérer des DONNEES ==
<code mysql>
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
</code>

== Supprimer des DONNEES ==
<code mysql>
DELETE FROM table WHERE critères ;			-- supprime les lignes de la table pour lesquels le critère est vrai
</code>

== Modifier des DONNEES ==
<code mysql>
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
</code>