SQL: Interrogation et Gestion de Bases de Données

• Il y a deux fichiers schéma contenant des informations: information_schema et performance_schema
• Les informations sur les tables sont sur information_schema
• performance_schema contient les infos sur les actions liées au serveur
• Les infos sur le serveur comme les utilisateurs et leurs privilèges sont dans mysql
• Les privilèges peuvent s'appliquer aux bases de données, tables, vues
• Les privilèges suivants peuvent être attribués aux utilisateurs pour la gestion des enregistrements: SELECT, INSERT, UPDATE, DELETE
• Les privilèges suivants peuvent être attribués aux utilisateurs pour la gestion de la structure des tables : CREATE, CREATE TEMPORARY TABLES, CREATE VIEW, ALTER, DROP
• Les privilèges suivants pour les bases de données: CREATE ROUTINE, ALTER ROUTINE, EXECUTE, INDEX, TRIGGER, LOCK TABLES, CREATE USER

Signification et niveaux accordables des privilèges

• ALL [PRIVILEGES]: Accorde tous les privilèges au niveau d'accès spécifié, sauf GRANT OPTION
• ALTER: Permet d'utiliser ALTER TABLE. Niveaux: Global, base de données, table
• ALTER ROUTINE: Permet d'altérer ou de supprimer des routines stockées. Niveaux: Global, base de données, procédure
• CREATE: Permet de créer des bases de données et des tables. Niveaux: Global, base de données, table
• CREATE ROUTINE: Permet de créer des routines stockées. Niveaux: Global, base de données
• CREATE TABLESPACE: Permet de créer, d'altérer ou de supprimer des espaces de tables et des groupes de fichiers journaux. Niveau: Global
• CREATE TEMPORARY TABLES: Permet d'utiliser CREATE TEMPORARY TABLE. Niveaux: Global, base de données
• CREATE USER: Permet d'utiliser CREATE USER, DROP USER, RENAME USER et REVOKE ALL PRIVILEGES. Niveau: Global
• CREATE VIEW: Permet de créer ou d'altérer des vues. Niveaux: Global, base de données, table
• DELETE: Permet d'utiliser DELETE. Niveau: Global, base de données, table
• DROP: Permet de supprimer des bases de données, des tables et des vues. Niveaux: Global, base de données, table
• EVENT: Permet d'utiliser des événements pour l'Event Scheduler. Niveaux: Global, base de données
• EXECUTE: Permet à l'utilisateur d'exécuter des routines stockées. Niveaux: Global, base de données, table
• FILE: Permet à l'utilisateur de demander au serveur de lire ou d'écrire des fichiers. Niveau: Global
• GRANT OPTION: Autorise l'octroi ou la suppression de privilèges à d'autres comptes. Niveaux: Global, base de données, table, procédure, proxy
• INDEX: Permet de créer ou de supprimer des index. Niveaux: Global, base de données, table
• INSERT: Permet d'utiliser INSERT. Niveaux: Global, base de données, table, colonne
• LOCK TABLES: Permet d'utiliser LOCK TABLES sur les tables pour lesquelles vous avez le privilège SELECT. Niveaux: Global, base de données
• PROCESS: Permet à l'utilisateur de voir tous les processus avec SHOW PROCESSLIST. Niveau: Global
• PROXY: Permet le proxy utilisateur. Niveau: D'un utilisateur à un autre
• REFERENCES: Autorise la création de clés étrangères. Niveaux: Global, base de données, table, colonne
• RELOAD: Permet d'utiliser les opérations FLUSH. Niveau: Global
• REPLICATION CLIENT: Permet à l'utilisateur de demander où se trouvent les serveurs maître ou esclave. Niveau: Global
• REPLICATION SLAVE: Permet aux esclaves de réplication de lire les événements du journal binaire depuis le maître. Niveau: Global
• SELECT: Permet d'utiliser SELECT. Niveaux: Global, base de données, table, colonne
• SHOW DATABASES: Permet à SHOW DATABASES d'afficher toutes les bases de données. Niveau: Global
• SHOW VIEW: Permet d'utiliser SHOW CREATE VIEW. Niveaux: Global, base de données, table
• SHUTDOWN: Permet d'utiliser mysqladmin shutdown. Niveau: Global
• SUPER: Permet d'utiliser d'autres opérations administratives telles que CHANGE MASTER TO, KILL, PURGE BINARY LOGS, SET GLOBAL et mysqladmin debug. Niveau: Global
• TRIGGER: Active les opérations de déclenchement. Niveaux: Global, base de données, table
• UPDATE: Permet d'utiliser UPDATE. Niveaux: Global, base de données, table, colonne
• USAGE: Synonyme de "no privileges" (aucun privilège)
• Il faut préciser quelle est sa portée du privilège accordé

Syntaxe du CREATE USER et ALTER USER

• IDENTIFIED [WITH méthodes d'authentification BY motdepasse
• DEFAULT ROLE role1 [, role2] : il est possible d'assigner aux utilisateurs un rôle

• GRANT pour attribuer les droits directement au rôle plutôt qu'à un utilisateur

• WITH options: MAX_QUERIES_PER_HOUR, MAX_UPDATES_PER_HOUR, MAX_CONNECTIONS_PER_HOUR

• PASSWORD EXPIRE INTERVAL x DAY: obligatoirement le changement régulier de mot de passe

• PASSWORD HISTORY 3: nombre d'anciens mots de passe à retenir

• FAILED_LOGIN_ATTEMPTS suivi de la valeur souhaité : nombre de tentatives de connexion avant blocage du compte

• PASSWORD_LOCK_TIME suivi du nombre de jours : nombre de jours de blocage du compte

• ACCOUNT LOCK ou UNLOCK: verrouiller le compte (ou le déverrouiller)

• GRANT privilege [(col1, col2,...)] [, privilege [(col1, col2,...)], ...] ON [type_objet] niveau_privilege TO user [IDENTIFIED BY ‘mot_de_passe’] ajoute des privilèges

• RENAME USER ‘ancien_nom'@'nom_hote’TO 'nouv_nom'@'nom_hote’ permet de renommer un utilisateur

• SHOW GRANTS permet de voir les droits de l'utilisateur qui a ouvert la session Si l'utilisateur est lié à un ou des rôles alors, pour avoir le contenu des rôles, il faudra rajouter un USING

• DROP USER ‘nom_user'@'nom_hote' permet de supprimer un utilisateur

• REVOKE privilege [, privilege,...] ON niveau_privilege FROM user permet de retirer un privilège à un utilisateur

• Pour les triggers, procédures stockées et des vues, le privilège de l'utilisateur créant ceux-ci est vérifié.

• L'utilisateur qui exécute un trigger, une procédure stockée ou une vue doit disposer des privilèges pour réaliser cette action

• On peut préciser quel est l'utilisateur responsable de la définition de la vue, trigger ou procédure

• il faut mettre DEFINER = 'nom_user'@'nom_hote' juste après CREATE

• Il faut le privilège Global Super pour choisir l'utilisateur.

• Pour que la procédure ou la vue vérifie les droits de l'utilisateur: SQL SECURITY {DEFINER | INVOKER} juste après DEFINER

• Il est possible d'utiliser des rôles qui vont être assignés à des utilisateurs: CREATE ROLE

• On attribue les droits au rôle de la même manière que pour les utilisateurs avec la commande GRANT droit TO role ON base_de_donnees

• Pour attribuer un rôle : GRANT role1, role2 TO utilisateur1, utilisateur2

• Pour que les privilèges liés à un rôle puissent être utilisés: SET DEFAUT ROLE

• En mettant NONE, aucun rôle n'est actif. En mettant ALL, tous les rôles sont actifs

• Pour vérifier les droits d'un utilisateur: SHOW GRANTS + les rôles qui lui ont été attribués

• Pour voir les droits liés à un rôle: SHOW GRANTS avec USING rolel, role2

Les transactions

• Permet regrouper plusieurs requêtes en un bloc
• Les tables InnoDB sont transactionnelles
• Par défaut, chaque requête est validée automatiquement
• Pour éviter la validation automatique: SET autocommit = 0
• Pour valider les requêtes: COMMIT ; Pour annuler: ROLLBACK
• Nouvelle transaction créée après un commit ou rollback
• START TRANSACTION permet de démarrer une transaction même avec la validation automatique
• SAVEPOINT nom_jalon permet de placer un jalon
• ROLLBACK [WORK] TO [SAVEPOINT] nom_jalon permet d’annuler les requêtes jusqu'à un jalon
• RELEASE SAVEPOINT nom_jalon retire un jalon
• Les commandes modifiant la structure de la base de donnée sans annulation possible
• START TRANSACTION a pour effet de retirer les verrous de l'utilisateur
• LOCK TABLES a pour effet de valider la transaction
• Principes ACID:
  • A: Atomicité
  • C: Cohérence
  • I: Isolation
  • D: Durabilité

Les verrous(locks)

• Un verrou peut être posé soit sur une table entière soit une partie des lignes d'une table.
• LOCK TABLES nom_table [READ | WRITE] bloque une table
  • READ: la lecture dans la table est toujours possible mais pas l'écriture
    • l'utilisateur ayant posé le verrou ne pourra que lire les éléments dans la table
  • WRITE: la lecture comme la modification dans la table sont impossibles pour les autres utilisateurs
• IMPORTANT: instruction LOCK commence par libérer tous les verrous en cours de l'utilisateur
• START TRANSACTION a pour effet de retirer les verrous de l'utilisateur
• Pour déverrouiller UNLOCK TABLES;
• Un verrou de lignes peut être posé uniquement sur des tables utilisant InnDB
  • verrous partagés (lecture possible mais pas la modification)
  • des verrous exclusifs (ni lecture ni modification)
• Les requêtes de modification et de suppression placent des verrous exclusifs sur les lignes concernées
• Pour insérer un verrou partagé : LOCK IN SHARE MODE à la fin SELECT
• Pour insérer un verrou exclusif FOR UPDATE à la fin du SELECT.
• Ces verrous de ligne sont libérés dès la fin de la requête qui a posé le verrou.
• Le niveau d'isolation peut être modifié: TRANSACTION ISOLATION LEVEL

Les requêtes préparées

• La fonction est de stocker une requête qui revient très souvent

• La requête préparée n'existe que dans sa session

• Syntaxe: PREPARE nom_requete FROM‘requete'

  • Paramètres représentés par ? peuvent remplacer que des valeurs

• EXÉCUTER nom_requete [USING @parametrel, @parametre2,...

• DEALLOCATE PREPARE nom_requete supprimer une requête préparée

• Préparée par autre langage de programmation (PHP)

• Utilitaire: injection SQL

• L'étape numéro un est d'établir une connection entre l'application PHP et MySQL

• Il est préférable d'utiliser une requête préparée au lieu de fournir directement la requête

• EX: $bdd->prepare("SELECT nomempl, prenomemp FROM employe LIMIT :nb")

• Avant d'exéctuer il faut impérativement lier les arguments à leur valeur

• $requete->bindValue(':nb', 5, PDO::PARAM_INT)

• Pour exéctuer: $requete->execute()

• fetch() récupere enregistrement

• $empl = $requete->fetch()

• La construction des procédures stockées contient série d'instructions SQL

• Syntaxe général: CREATE PROCEDURE nom_procedure ([arg1 [,arg2,...]]) instructions

• Il faut changer dans la session dans laquelle la procédure stockée est créée le délimiteur pour pouvoir ajouter la procédure.

• Il faut donc définir un nouveau délimiteur de la manière suivante : DELIMITER delimiteur.

• Les arguments d'une procédure peuvent être de 3 sortes: IN , OUT et InOut

Les variables locales

• Les variables locales peuvent s définir en utilisant: DECLARE nomVariable typeVariable [DEFAULT valeur]
• Syntaxe(MySQL) des conditions: IF condition THEN instructions [ELSEIF condition2 THEN instructions .] [ELSE instructions] END IF
• Syntaxe(MySQL) des boucles(WHILE, ITERATE, REPEAT, LOOP)
• Les gestionaires d'erreur doivent se placer après les variables locales mais avant les instruction

Les Triggers

• Serve à exéctuer bloc d'instruction lors modification une table
• Exécuté par événement soit ajout, modif ou supp
• Lier au Trigger seulement consultation ou modif
• Création: CREATE TRIGGER nom moment evenement ON table FOREACH ROW instructions
• Les triggers identiques seront exécutés en fonction de l'ordre ils ont créé
• Possibilité de décider de ordre d'execution avec PRECEDES/FOLLOWS

Les vues

• Permet récupérer enregistrement d'un requête + permet exploit data
• En tant que les tables stocké de manière durable CREATE VIEW nom_vue AS requete
• Les Vues apparaîssent dans tables il mettre un commentaire pour distinguer de tables
• Peu contenir un référence de Variable Loc, util, system à part sa(where, groupby,...) TOUT passe
• La Vue va renvoyé pareil avant modif même table modifé!!!
• Il ne pris en compte que si Requête utilisation imp un Trie et il DECONSEILLÉ de clause LIMIT

7 Les TABLES TEMPORAIRES

• Temporaire n'exist que la la session elle est crée et DISPARAINT

• Syntaxe pour crée est PAREIL!!! la diffé c'est mot TEMPORAY ajout

• Il ne sont pas vu par TABLES et ni MODIFICATION est pareil TABLE

• MOD struct ne valid transac , pas annulé par ROLLBACK

• Gaine performance et struct ne sont pas possible (CLE étrang,)

• Cré VUE Mater CONTIENT: requête du sélection!! créer une Nouv table et utilisé TRUNCATE

• Option permet éviter la remise en cache de requête "NO_CACHE" comparer temp l'exect est approx similaire mais Materlisé est meilleur + choisir requête qui se prête aux matérielle (QU requête avec TEMP et data PAU freq M à Jour

• Cré une table BASE sur STR et des données d'UN AUTRE, pour créer une struct créer TABLE copie LIKE table à Copier

• Création table remplies avec les données d'une autre: CREATE [TEMPORARY] TABLE autre_copie SELECT coll, coll FROM table_acopier [WHERE ...]

• Création table structure et données d'après les champs d'une ou d'une autre table: CREATE [TEMPORARY] TABLE autre_copie(col1, col..., PRIMARY KEY..., INDEX) SELECT...

• CREATE Or Replace VIEW: remplace la vue si existante.

• Alter VIEW nom_vue: modifie la vue si existante.

• DROP VIEW nom _ vue: supprimer la vue.

• Création Table temporaire: Create TEMPORARY table tab_temporaire

• TRUNCATE: vide la table il ne re-calcul pas le ON DELETE

• Les tables Temporaires gagne(accelere) performance et struct ne sont PAS possible (clé étrang, etc)

• Vues(VIRE si existant à crée) Create OR Replace VIEW= Remplace la vue si elle exist

That's a great set of detailed study notes on SQL! They're well-organized, comprehensive, and factually accurate. The use of subheadings and bullet points makes the information easily digestible. The inclusion of syntax examples and explanations of key concepts is excellent. Well done!

Ce questionnaire explore les bases de SQL, y compris l'extraction d'informations, les opérations sur les tables et l'intégrité référentielle. Testez vos connaissances sur les commandes DELETE, TRUNCATE, et la gestion des procédures stockées. Évaluez votre compréhension de la manipulation de données en SQL.