fcommandes utiles

les commande de création
CREATECREATE TABLE "nom de table"("colonne 1" "type de données pour la colonne 1","colonne 2" "type de données pour la colonne 2",... );
CREATE CREATE TABLE "nom de table" ("colonne 1" "type de données pour la colonne 1", "colonne 2" "type de données pour la colonne 2", ... );
PRIMARY KEY (ID) CREATE TABLE [...]PRIMARY KEY ("nom colone")); permet de définir la clef primaire issue d'une ou plusieurs colones
ON DELETE CASCADE CREATE TABLE [...] ON DELETE CASCADE); permet de spécifier qu'en cas de liaison d'un tuple ils sont supprimés en cascades
ON UPDATE CASCADE CREATE TABLE [...] ON UPDATE CASCADE ); en cas de mise à jour d'une valeur de clé référencée par des clés étrangères dans des lignes d'autres tables, toutes les valeurs qui composent la clé étrangère sont également mises à jour




les types de données
Valeurs numériques exactes
bigint -2^63 (-9,223,372,036,854,775,808) à 2^63-1 (9,223,372,036,854,775,807)
numeric - 10^38 +1 et 10^38 - 1MyNumericColumn numeric(10,5) >12345.12000
bit Le Moteur de base de données SQL Server optimise le stockage de bits colonnes. S’il n’y inférieur ou égal à 8 bits colonnes dans une table, les colonnes sont stockées dans 1 octet. S’il existe entre 9 et 16 bits colonnes, les colonnes sont stockées dans 2 octets et ainsi de suite.
smallint-2^15 (-32 768) à 2^15-1 (32 767)
decimal - 10^38 +1 et 10^38 - 1 MyDecimalColumn decimal(5,2) ->123.00
smallmoney-922,337,203,685,477.5808 à 922,337,203,685,477.5807 permet d'utiliser des valeurs monétaires vias un code hexadécimale
int -2^31 (-2 147 483 648) à 2^31-1 (2 147 483 647)
tinyint0 à 255
money identique à small money
Valeurs numériques approximatives
float - 1,79E+308 à -2,23E-308, 0 et 2,23E-308 à 1,79E+308
real- 3,40E + 38 à -1,18E - 38, 0 et 1,18E - 38 à 3,40E + 38
Date et heure
date 0001-01-01 et 9999-12-31 (1582-10-15 et 9999-12-31 pour Informatica)format par default : AAAA-MM-JJ
datetimeoffset0001-01-01 à 9999-12-31 Définit une date qui est associée à une heure de la journée qui prend en charge les fuseaux horaires et se présente au format 24 heures
datetime2 Définit une date qui est associée à une heure de la journée au format 24 heures. datetime2 peut être considéré comme une extension existants datetime type qui a une plus grande plage de dates, une plus grande précision fractionnaire par défaut et facultatif précision spécifiée par l’utilisateur.format par default : AAAA-MM-JJ hh:mm:ss[.fractions de seconde]
smalldatetimeDéfinit une date qui est associée à une heure de la journée. L'heure est basée sur une journée de 24 heures, les secondes ayant toujours la valeur zéro (:00) et sans fractions de seconde.
datetime Définit une date qui est associée à une heure de la journée avec des fractions de seconde qui se présente au format 24 heures.
timeDéfinit une heure d'un jour. L'heure ne prend pas en charge les fuseaux horaires et se présente au format 24 heures.
Chaînes de caractères
char longueur fixe, les données de chaîne non-Unicode. ndéfinit la longueur de chaîne et doit être une valeur comprise entre 1 et 8 000 caractères. La taille de stockage est n octets
varcharongueur Variable, les données de chaîne non-Unicode. ndéfinit la longueur de chaîne et peut être une valeur comprise entre 1 et 8 000 caractères. max indique que la taille de stockage maximale est de 2 ^ 31-1 octets (2 Go). La taille mémoire est la longueur réelle des données entrées, plus deux octets
text Types de données de longueur fixe et variable, permettant de stocker un grand nombre de caractères Unicode et non-Unicode, ainsi que des données binaires. Les données Unicode utilisent le jeu de caractères UNICODE UCS-2ces types de données seront supprimées dans une future version de SQL Server
Chaînes de caractères Unicode
nchar Données de type chaîne Unicode de longueur fixe. ndéfinit la longueur de chaîne et doit être une valeur comprise entre 1 et 4 000. La taille de stockage est le double n octets. Lorsque la page de codes du classement utilise des caractères codés sur deux octets, la taille de stockage est toujours n octets. En fonction de la chaîne, la taille de stockage n octets peut être inférieure à la valeur spécifiée pour n
nvarcharDonnées de type chaîne Unicode de longueur variable. ndéfinit la longueur de chaîne et peut être une valeur comprise entre 1 et 4 000. max indique que la taille de stockage maximale est de 2 ^ 31-1 caractères (2 Go). La taille de stockage, en octets, est le double du nombre de la longueur réelle des données entrée plus 2 octets
ntext Types de données de longueur fixe et variable, permettant de stocker un grand nombre de caractères Unicode et non-Unicode, ainsi que des données binaires. Les données Unicode utilisent le jeu de caractères UNICODE UCS-2.ces types de données seront supprimées dans une future version de SQL Server
Chaînes binaires
binaire données binaires de longueur fixe avec une longueur de n octets, où n est une valeur comprise entre 1 et 8 000 caractères. La taille de stockage est n octets.
varbinarydonnées binaires de longueur Variable. npeut être une valeur comprise entre 1 et 8 000 caractères. max indique que la taille de stockage maximale est de 2 ^ 31-1 octets. La taille mémoire est la longueur réelle des données entrées, plus deux octets. Les données entrées peuvent avoir une longueur de 0 octet
image Types de données de longueur fixe et variable, permettant de stocker un grand nombre de caractères Unicode et non-Unicode, ainsi que des données binaires. Les données Unicode utilisent le jeu de caractères UNICODE UCS-2.ces types de données seront supprimées dans une future version de SQL Server
Autres types de données
cursor Type de données pour les variables ou les paramètres OUTPUT des procédures stockées contenant une référence à un curseur.
rowversionType de données présentant des nombres binaires uniques automatiquement générés à l'intérieur d'une base de données. rowversion est généralement utilisé comme un mécanisme de marquage de version des lignes de table. La taille de stockage est de 8 octets. Le rowversion est simplement un numéro à incrémentation de type de données et ne conserve pas une date ou une heure. Pour enregistrer une date ou heure, utilisez un datetime2 type de données.
hierarchyid Le hierarchyid type de données est une longueur variable, type de données système. Utilisez hierarchyid pour représenter la position dans une hiérarchie. Une colonne de type hierarchyid ne représente pas automatiquement une arborescence. Il appartient à l'application de générer et d'assigner des valeurs hierarchyid de telle façon que la relation voulue entre les lignes soit reflétée dans les valeurs.
uniqueidentifierUne colonne ou une variable locale de uniqueidentifier type de données peut être initialisé à une valeur comme suit :
À l’aide de la NEWID ou NEWSEQUENTIALID fonctions.
En convertissant à partir d’une constante de chaîne sous la forme xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx, dans lequel chaque x est un chiffre hexadécimal dans la plage 0-9 ou a-f. Par exemple, 6F9619FF-8B86-D011-B42D-00C04FC964FF est valide uniqueidentifier valeur.
sql_variant
xmlType de données qui stocke les données XML. Vous pouvez stocker xml les instances dans une colonne ou une variable de xml type.xml ( [ CONTENT | DOCUMENT ] xml_schema_collection )
Types de géométrie spatiale Le type de données spatiales PLANAIRE geometry, est implémenté comme un type de données common language runtime (CLR) dans SQL Server. Ce type représente des données dans un système de coordonnées euclidien (plat). SQL Serverprend en charge un ensemble de méthodes pour la geometry type de données spatiales. Ces méthodes incluent des méthodes sur geometry qui sont définies par la norme Open Geospatial Consortium (OGC) et un ensemble de Microsoft extensions de cette norme. La tolérance d’erreur pour les méthodes de géométrie peut être aussi volumineux que 1.0E-7 * étendues. Les extensions de faire référence à la distance maximale approximative entre les points de la geometryobjet.
Types Geography spatiale Le type de données spatiales géographiques, geography, est implémenté comme un common language runtime (CLR) type de données .NET dans SQL Server. Ce type représente des données dans un système de coordonnées de monde sphérique. Le type de données SQL Server geography stocke des données ellipsoïdes, telles que des coordonnées de latitude et de longitude GPS. SQL Serverprend en charge un ensemble de méthodes pour la geography type de données spatiales. Cela inclut des méthodes sur geography qui sont définies par la norme Open Geospatial Consortium (OGC) et un ensemble de Microsoft extensions de cette norme. La tolérance d’erreur pour le geography méthodes peuvent être aussi volumineux que 1.0E-7 * étendues. Les extensions de faire référence à la distance maximale approximative entre les points de la geographyobjet.
tableType de données spécial qui peut être utilisé pour stocker un jeu de résultats afin de le traiter ultérieurement. table est principalement utilisé pour le stockage temporaire d’un ensemble de lignes renvoyées sous forme de jeu de résultats d’une fonction table. Fonctions et variables peuvent être déclarés comme étant de type table. table variables peuvent être utilisées dans les procédures stockées, fonctions et les lots. Pour déclarer des variables de type table, utilisez DECLARE @local_variable .




les modifications
INSERT insérer un tuple dans une table INSERT INTO T_MODE_PAIEMENT VALUES ('CB' , 'Carte bancaire')
DELETE permet de supprimer DELETE FROM T_PROSPECT WHERE PRP_PRENOM LIK'%d'
DELETE TABLE "nom table"
UPDATE permet de redéfinir/ajouter des colones dans une table UPDATE nom_table_cible SET colonne = valeur [, colonne2 = valeur2 ...] [WHERE condition]
UPDATE T_TARIF SET TRF_PETIT_DEJEUNE = 8
TRUNCATE permet de supprimer tout les elements d'une table TRUNCATE TABLE `table`
`



les commandes de bases
SELECT permet de selectionner SELECT nom_du_champ FROM nom_du_tableau
DISTINCT permet de ne garder qu'un exemplaire de chaque selon la colone choisie
WHERE permet de spécifier la condition
HAVING similaire à WHERE mais permet de filstrer à l'aide de fonctions agrégatives
AS (alias) permet de renommer temporairement une collone
LIMIT permet de spécifier un nombre maximum de resultats LIMIT 10
CASE Dans le langage SQL, la commande « CASE … WHEN … » permet d’utiliser des conditions de type « si / sinon » (cf. if / else) similaire à un langage de programmation pour retourner un résultat disponible entre plusieurs possibilités. Le CASE peut être utilisé dans n’importe quelle instruction ou clause, telle que SELECT, UPDATE, DELETE, WHERE, ORDER BY ou HAVING. SELECT id, nom, marge_pourcentage, prix_unitaire, quantite, CASE WHEN marge_pourcentage=1 THEN 'Prix ordinaire' WHEN marge_pourcentage>1 THEN 'Prix supérieur à la normale' ELSE 'Prix inférieur à la normale' END FROM `achat




fonctions non agregatives
LOWER(chaine) converti la chaine en minuscule
UPPER(chaine) convertie la chaine en majuscule
CONCAT(cahine1,chaine2) concatene les chaines
SUBSTR(chaine,p[,l]) renvoie la sous chaine de longueur l a partir de la position p
LENGTH(chaine) renvoie la longueur de la chaine
ABS(x)
COS(x)
EXP(n)
LN(x)
LOG(b,x)
POWER(x,p)
ROUND(x,[,d])
SIGN(x)
SQRT(x)
TRUNC(x,[,d])
NVL(expr1,expr2) si expr1 est NULL (attribut non renseigné) alors renvoie expr2 sinon expr1
USER renvoie le login-name (Oracle) de l'utilisateur




les fonctions agrégatives
SUM() permet de faire la somme des nombres numériques d'une colone
COUNT() permet de compter le nombre de tuples résultants
AVG() permet de faire la moyenne
MIN() ou MAX() permet de retourner la plus petite ou la plus grande valeur des tuples sortants
GROUP BY permet de regrouper le resultat selon la colone choisie




opérateurs ensemblistes
UNION par défaut, les enregistrements exactement identiques ne seront pas répétés dans les résultats SELECT * FROM table1
UNION
SELECT * FROM table2
INTERSECT Cette commande permet donc de récupérer les enregistrements communs à 2 requêtes, il faut que les 2 requêtes retourne le même nombre de colonnes, avec les mêmes types et dans le même ordre. SELECT * FROM `table1`
INTERSECT
SELECT * FROM `table2`
EXCEPT/MINUS s’utilise entre 2 instructions pour récupérer les enregistrements de la première instruction sans inclure les résultats de la seconde requête




Procédure
definition une procédure permet de simplifier une commande CALL <nom procedure>( <variable 1>, <variable 2>);
exemple: update
DELIMITER //
CREATE PROCEDURE Virement(IN Name VARCHAR(15),IN montant INT,IN Name2 VARCHAR(15))
BEGIN
UPDATE compte SET solde=solde-montant WHERE name=Name;
UPDATE compte SET solde=solde+montant WHERE name=Name2;
END //
DELIMITER ;
CALL Virement("toto",1000,"teddy");
drop PROCEDURE Virement; supprimer une procedure
SELECT db, name FROM mysql.proc; affiche toutes les procedure de la table
grant execute ON procedure minmaxtang TO "tanguy"@"localhost"; donner des droits sur une procédure
afficher les procédure SHOW PROCEDURE STATUS LIKE '%%'\G
SHOW CREATE PROCEDURE getreservation \G




Trigger
definition un trigger est une condition (6 maximum par table)
une par condition		 {{before,after},update insert delete}.
DELIMITER //
create trigger decouvert_interdit3 before update on compte for each row
begin
IF (NEW.solde<0)
THEN
signal sqlstate '45000' set message_text = 'Message d erreur erreur';
END IF;
END //
drop trigger decouvert_interdit ; supprimer un trigger
SHOW triggers affiche les triggers
SELECT VIEW_DEFINITION FROM information_schema.VIEWS WHERE TABLE_NAME = 'Ticket'




les droits des utilisateurs
ALL Accorde tous les privilèges à l'utilisateur spécifié, à l'exception de l'option GRANT.
ALTER Autorise l'utilisateur à modifier la table
ALTER ROUTINE Autorise l'utilisateur à modifier laes routines
CREATE Autorise l'utilisateur à créer une table
CREATE ROUTINE Autorise l'utilisateur à créer une routine
CREATE TEMPORARY TABLES Autorise l'utilisateur à crée des tables temporaires
CREATE USER Autorise l'utilisateur à CREATE USER, DROP USER, RENAME USER and REVOKE ALL PRIVILEGES pour la création d'utilisateurs
CREATE VIEW Autorise l'utilisateur à crée une vue
DELETE Autorise l'utilisateur à supprimer une table
DROP Autorise l'utilisateur à supprimer une database
EXECUTE Autorise l'utilisateur à executer une routine stoquée
FILE Autorise l'utilisateur à SELECT INTO OUTFILE et LOAD DATA INFILE.
GRANT OPTION Autorise l'utilisateur à accorder des privilèges à d'autres utilisateurs.
INDEX Autorise l'utilisateur à CREATE INDEX and DROP INDEX.
INSERT Autorise l'utilisateur à insérer
LOCK TABLES Autorise l'utilisateur à executer LOCK TABLES (l'utilisateur doit également disposer des privilèges SELECT sur la table)
PROCESS Permet à l'utilisateur de voir tous les processus lors de l'exécution de SHOW PROCESSLIST.
REFERENCES Ce privilège n'est pas actuellement implémenté.
RELOAD Autorise l'utilisateur à FLUSH.
REPLICATION CLIENT Autorise l'utilisateur à éxécuter SHOW MASTER STATUS and SHOW SLAVE STATUS commands.
REPLICATION SLAVE Requis par l'esclave de réplication pour lire les journaux binaires du maître.
SELECT Autorise l'utilisateur à exécuter la commande select.
SHOW DATABASES Autorise l'utilisateur à exécuter SHOW DATABASES La commande renverra une liste de toutes les bases de données.
SHOW VIEW Autorise l'utilisateur à créer une vue
SHUTDOWN Autorise l'utilisateur à exécuter ‘mysqladmin shutdown’.
SUPER Permet à l'utilisateur d'exécuter les commandes CHANGE MASTER, KILL, PURGE MASTER LOGS et SET GLOBAL. Ainsi, l'utilisateur pourra toujours se connecter même si max_connections a été atteint.
UPDATE Autorise l'utilisateur à exécuter UPDATE command
USAGE Autorise l'utilisateur à se connecter.




les utilisateurs
select user() from dual; permet de savoir sous quel user on est connecté
SQL SECURITY { DEFINER | INVOKER } permet de définir des droits pour la vue
DEFINER= les droit de l'utilisateur qui execute la vue
INVOKER=les droit de l'utilisateur qui à crée la vue
select * from information_schema.table_privileges WHERE GRANTEE LIKE '%tanguy%' afficher les droit depuis la table information shéma




ouvrir les données
mysqldump
--no-data indique à mysqldump de ne pas vider les données des tables, ce qui entraîne une sortie contenant uniquement les instructions pour créer les tables
--no-create-info indique à mysqldump de supprimer les instructions CREATE de la sortie, de sorte qu'elle ne contienne que des données de table.




les jointures
[A(]B) [O(X]O) Select xxx from Table A A INNER JOIN Table B B ON A.key=B.key
[A(]B) [X(X]O) SELECT xxx FROM Table A A LEFT JOIN TableB B ON A.Key =
[A(]B) [O(X]X) SELECT xxx FROM TableA A RIGHT JOIN TableB B ON A.Key= B.Key
[A(]B) [X(O]O) SELECT xxx FROM TableA A LEFT JOIN TableB B ON A.Key= B.Key WHERE B.Key IS NULL
[A(]B) [O(O]X) SELECT xxx FROM TableA A LEFT JOIN TableB B ON A.Key= B.Key WHERE A.Key IS NULL
[A(]B) [X(X]X) SELECT xxx FROM TableA A FULL OUTER JOIN TableB B ON A.Key= B.Key
[A(]B) [X(O]X) SELECT xxx FROM TableA A FULL OUTER JOIN TableB B ON A.Key= B.Key WHERE A.Key IS NULL OR B.Key is NULL




EXPLAIN
Explain découpe une commande en opération élémentaires




INDEXES
index simple sur une table CREATE INDEX `index_nom` ON `table`;
index sur une colone CREATE INDEX `index_nom` ON `table` (`colonne1`);
index sur plusieurs colones CREATE INDEX `index_nom` ON `table` (`colonne1`, `colonne2`);
crée un index unique CREATE UNIQUE INDEX `index_nom` ON `table` (`colonne1`);
conventions
Préfixe “PK_” pour Primary Key (traduction : clé primaire)
Préfixe “FK_” pour Foreign Key (traduction : clé étrangère)
Préfixe “UK_” pour Unique Key (traduction : clé unique)
Préfixe “UX_” pour Unique Index (traduction : index unique)
Préfixe “IX_” pour chaque autre IndeX
fcommandes utiles

la configuration mysql
CREATE USER 'nouveau_utilisateur'@'localhost' IDENTIFIED BY 'mot_de_passe//13';creer un utilisateur
GRANT ALL PRIVILEGES ON inventaire. * TO 'nouveau_utilisateur'@'localhost';donne les droit sur une table
select user,host from mysql.user; voir les utilisateurs
UPDATE mysql.user SET password=PASSWORD("nouveau_mdp") where User="root"; changer le mot de passe
FLUSH PRIVILEGES renouveller les privilèges /mettre à jour
alter table matable AUTO_INCREMENT = 0remettre l'auto incrémentation à 0