Non classé

Qu'est-ce que SQL ? Apprendre les tendances en matière d'analyse et d'entreposage de données

collation et SQL

Qu'est-ce que SQL ?

Qu'est-ce que SQL ? SQL est l'abréviation de Structured requête Language (langage de requête structuré). Les développeurs d'applications, les professionnels du reporting et de la BI utilisent SQL pour insérer, récupérer, mettre à jour et supprimer des données dans les bases de données. IBM a initialement développé SQL dans ses laboratoires de Santa Teresa à San Jose, en Californie.

Qu'est-ce que SQL et comment fonctionne-t-il ?

SQL (prononcé "see-qu-el") est un langage procédural qui comporte souvent des extensions procédurales non standard. Les organismes de normalisation de SQL comprennent l'ANSI (American National Standards Institute), et c'est le langage standard pour les systèmes de gestion de bases de données relationnelles. SQL est principalement utilisé pour gérer des bases de données conformes au modèle relationnel et gérées par un SGBDR système de gestion de base de données relationnelles). Le langage SQL utilise un interprète pour analyser la déclaration. Les résultats de l'étape d'analyse, qui identifie les erreurs syntaxiques, sont transmis à l'optimiseur de base de données. L'optimiseur crée un plan de requête , qui est un ensemble d'instructions spécifiant la manière dont le moteur de base de données exécutera la requête de manière optimale. Le plan de requête indique quelles clés seront utilisées pour obtenir des ensembles de résultats intermédiaires et comment ils seront fusionnés et triés pour obtenir l'ensemble de résultats, qui est renvoyé à l'application appelante.

À quoi sert SQL ?

Le langage SQL permet aux applications d'interagir avec une base de données. L'utilisateur une application accède généralement à une collection d'objets de base de données comprenant des tables, des vues et des index. Dans une base de données relationnelle, une table se compose de lignes et de colonnes logiques. Certains systèmes SGBDR stockent les tables sous forme de lignes (row store), tandis que d'autres peuvent stocker les colonnes (column store). Dans les deux cas, l'application fonctionne sur la base de lignes, quel que soit le format de stockage.

L'instruction SELECT CUSTOMER-NAME FROM CUSTOMERS WHERE CUSTOMER-ID =10 ; récupère les noms des clients dont l'ID est 10 dans la table CUSTOMERS. Si la table contient une colonne STATE, l'instruction SQL suivante ne récupère que les clients du Nevada :

SELECT * FROM CUSTOMERS WHERE STATE = 'NV' ;

Plusieurs tables peuvent être reliées entre elles par des colonnes communes. Ces colonnes peuvent être indexées pour permettre un accès plus rapide ou simplement être entièrement analysées par le SGBDR si elles ne sont pas trop volumineuses.

Les tables peuvent être jointes logiquement au moment de l'exécution, et la nature de la jointure peut être exprimée en SQL.

Voici les types de jointures où la gauche et la droite correspondent à l'ordre des tables nommées dans l'instruction SELECT :

JOINTE INNER : cette jointure renvoie les enregistrements dont les valeurs correspondent dans les deux tables.

JOINTE COMPLÈTE : cette jointure renvoie tous les enregistrements qui ont une correspondance dans la table de gauche ou de droite.

JOINTE DE GAUCHE : ce type de jointure renvoie les enregistrements de la table de gauche, ainsi que les enregistrements qui satisfont aux conditions de la table de droite.

JOINTE DE DROITE : ce type de jointure renvoie les enregistrements de la table de droite, ainsi que les enregistrements qui satisfont à la condition de la table de gauche.

Les tables peuvent être reliées entre elles par des relations de clés primaires et étrangères. Par exemple, une table de clients peut avoir pour clé primaire l'identifiant du client. Une table Commandes contenant une colonne ID-Client sera considérée comme une clé étrangère dans cette table.

Les clés primaires sont généralement indexées et contiennent des valeurs uniques.

Les applications utilisent SQL pour récupérer et insérer des données dans des bases de données, y compris des bases de données distribuées.

Voici quelques exemples d'instructions SQL :

  • SELECT - permet d'obtenir des données à partir d'une table ou d'une vue
  • CREATE TABLE - permet de créer une table de données
  • INSERT - permet d'insérer de nouvelles lignes dans un tableau
  • UPDATE - permet de modifier les données contenues dans une table de base de données.
  • DELETE - est utilisé pour supprimer des lignes de données
  • GRANT - est utilisé pour donner aux utilisateurs la permission de voir ou de modifier les objets de la base de données tels que les tables.
  • REVOKE - permet de retirer des autorisations à des utilisateurs ou à des groupes d'utilisateurs.
  • ALTER - permet à l'utilisateur d'ajouter ou de supprimer des colonnes d'un objet afin de mettre à jour les autorisations.
  • DROP TABLE - supprime une table
  • CREATE INDEX - crée un index pour permettre un accès efficace aux données à l'aide d'une clé-valeur.
  • TRUNCATE TABLE - permet à l'utilisateur de vider le contenu d'une table.

L'instruction SELECT peut contenir des fonctions d'agrégation, par exemple :

  • COUNT() - renvoie le nombre total de lignes répondant aux critères de l'instruction.
  • MIN() - renvoie la plus petite valeur de l'ensemble de résultats
  • MAX() - renvoie la plus grande valeur de l'ensemble de résultats
  • AVG() - renvoie la moyenne des valeurs de l'ensemble de résultats
  • GROUP BY - regroupe les résultats en fonction de la valeur d'une colonne donnée.
  • ORDER BY - permet de trier les résultats en fonction de la colonne spécifiée.

Les commentaires en SQL aident à la compréhension et à la maintenance des applications. Vous trouverez ci-dessous des exemples de commentaires sur une ou plusieurs lignes :

-- Voici mon commentaire

/* Ceci est 

ligne multiple 

commentaire */

Il est utile de visualiser le schéma d'une base de données à l'aide d'un diagramme de Venn, en particulier lors de l'utilisation d'opérateurs d'ensemble tels que UNION, INTERSECT et EXCEPT dans les instructions SELECT.

UNION - Cet opérateur est utilisé pour combiner l'ensemble des résultats de deux ou plusieurs instructions SELECT.

Exemple :

SELECT Columns FROM Table1

UNION

SELECT Columns FROM Table2 ;

INTERSECT - Cette clause est utilisée pour combiner deux instructions SELECT et retourner l'intersection des ensembles de données des deux instructions SELECT.

Exemple :

SELECT Colonne1 , Colonne2 ....

FROM NomTable

WHERE Condition

INTERSECT

SELECT Colonne1, Colonne2 ....

FROM NomTable

WHERE Condition

SAUF - Cet opérateur renvoie les lignes qui sont renvoyées par la première opération SELECT et qui ne sont pas renvoyées par la deuxième opération SELECT.

Exemple :

SELECT NomColonne

FROM NomTable

SAUF

SELECT NomColonne

FROM NomTable ;

Qu'est-ce qu'une base de données en SQL ?

À un niveau élevé, une base de données relationnelle consiste en un stockage (généralement des fichiers) et un ensemble de processus qui se connectent à des sessions d'application qui envoient des requêtes SQL au serveur SGBDR , et le serveur renvoie des enregistrements ou des messages. Par exemple, l'instruction SQL SELECT * FROM EMP ; renverrait toutes les lignes de la table EMP. La construction d'un schéma de base de données commence généralement par un processus de conception, dans lequel toutes les entités impliquées dans l'application sont dessinées et connectées d'une manière qui illustre leurs relations.

Vous êtes peut-être en train de créer une application RH qui suit les employés et les départements. Vous dessinez donc une boîte représentant l'objet du département et une deuxième boîte représentant les employés. Ensuite, vous tracerez une ligne qui les relie, avec une seule ligne à l'extrémité du département et trois lignes (comme dans une patte d'oie) à l'extrémité de la boîte des employés de la connexion. Vous pourriez nommer la connexion "travaille dans", ce qui indiquerait que de nombreux employés travaillent dans un département. Un processus appelé normalisation permet d'optimiser la conception.

Maintenant que vous savez quelles tables vous voulez, vous pouvez commencer à décider quels attributs vous voulez suivre à propos des entités et quels index doivent être créés. La table des départements peut contenir un numéro de département qui peut être sa clé primaire, et la table des employés peut avoir un numéro d'employé comme clé d'indexation primaire. Une table peut avoir plusieurs index, il est donc logique d'indexer les employés par leur nom, car il s'agira d'un moyen courant d'accéder aux informations sur les employés.

L'étape suivante consiste à créer un schéma de base de données. Chaque objet table peut être créé à l'aide d'une instruction SQL CREATE TABLE qui spécifie le nom de la table, les noms des colonnes, les types de données et les longueurs. Les bases de données sont généralement alimentées en chargeant des données à partir de fichiers plats ou à l'aide d'un outil ETL (Extract, Transform, Load) qui génère les instructions INSERT appropriées.

Les applications permettent aux utilisateurs de modifier les données à l'aide d'instructions UPDATE et de supprimer des lignes à l'aide d'instructions DELETE. L'instance SGBDR gère la mise à jour de l'index et veille à ce que les relations entre les tables, telles que les contraintes référentielles, soient respectées afin de maintenir l'intégrité de la base de données.

Un administrateur de base de données (DBA) utilise des instructions telles que DROP TABLE, TRUNCATE TABLE, CREATE INDEX et CREATE TABLE dans un sous-ensemble de SQL connu sous le nom de Data Manipulation Language (DML) pour modifier la structure du schéma de la base de données. Heureusement, la plupart des systèmes SGBDR modernes, qu'ils soient sur site ou qu'il s'agisse de l'offre SaaS d'Actian, sont dotés d'une excellente interface utilisateur (UI) qui simplifie l'écriture des requêtes SQL/DML.

Une fois que la base de données est alimentée en données, les applications commerciales peuvent l'utiliser.

Qu'est-ce que SQL et quels sont les exemples ?

Nous avons vu comment les bases de données sont créées et utilisées. Voyons maintenant quelques exemples d'instructions SQL.

Toutes les bases de données n'utilisent pas la commande CREATE DATABASE. Ingres, par exemple, dispose d'une méthode en ligne de commande appelée ci-dessous :

createdb -e mydb

Exemple d'Oracle :

CREATE DATABASE mynewdb

utilisateur SYS IDENTIFIED BY sys_password

utilisateur SYSTEM IDENTIFIED BY system_password

EXTENT MANAGEMENT LOCAL

TABLESPACE TEMPORAIRE par défaut temp

UNDO TABLESPACE undotbs1

TABLESPACE par défaut users ;

Qu'est-ce qu'un serveur SQL ?

Le serveur SQL trouve son origine dans Sybase. Microsoft a accordé une licence à Sybase lorsqu'il s'est lancé sur le marché des serveurs PC avec NT pour concurrencer des produits tels que Novell Netware et SCO Unix qui fonctionnaient sur du matériel Intel x86.

Le serveur SQL est doté d'une interface graphique qui en facilite l'installation et la maintenance. La version sur site du serveur SQL ne prend en charge les clusters que pour le basculement, pas pour l'évolutivité.

Voici un exemple de création d'une base de données SQL Server :

USE master ;

GO

-- Obtenir le chemin d'accès aux données du serveur SQL.

DECLARE @data_path nvarchar(256) ;

SET @data_path = (SELECT SUBSTRING(nom_physique, 1, CHARINDEX(N'master.mdf', LOWER(nom_physique)) - 1)

      FROM master.sys.master_files

      WHERE database_id = 1 AND file_id = 1) ;

 -- Exécute l'instruction CREATE DATABASE.

EXECUTE ('CREATE DATABASE FileStreamDB

ON PRIMARY

    (NOM = FileStreamDB_data

    ,FILENAME = ''' + @data_path + 'FileStreamDB_data.mdf'')

    ,SIZE = 10MB,MAXSIZE = 50MB,FILEGROWTH = 15%),

FILEGROUP FileStreamPhotos CONTAINS FILESTREAM DEFAULT

    (NOM = FSPhotos

    FILENAME = ''C:MyFSfolderPhotos'')

, MAXSIZE = 5000 MB),

    (NOM = FSPhotos2

      , FILENAME = ''D:MyFSfolderPhotos''

      , MAXSIZE = 10000 MB

     ),

FILEGROUP FileStreamResumes CONTAINS FILESTREAM

    (NOM = Reprise du flux de fichiers

    ,FILENAME = ''C:MyFSfolderResumes'')

LOG ON

    (NOM = FileStream_log

    ,FILENAME = ''' + @data_path + 'FileStreamDB_log.ldf'')

    ,SIZE = 5MB ,MAXSIZE = 25MB ,FILEGROWTH = 5MB)') ;

GO

Qu'est-ce que SQL dans les SGBD ?

Comme nous l'avons vu plus haut, SQL est utilisé pour accéder aux enregistrements d'un SGBDR par les applications et gérer la base de données. La commande ALTER permet de modifier des objets après leur création. Vous pouvez utiliser la commande ALTER pour ajouter des colonnes à une table ou modifier son type de données (si elle n'est pas remplie).

Un EXPLAIN PLAN donne un aperçu de ce que fait un SGBDR avec une requête SQL. Il est utilisé par les développeurs et les administrateurs de bases de données pour optimiser instructions SQL. Un professionnel compétent en matière d optimisation des performances recherchera les index corrects utilisés. Les informations fournies par un plan d'exécution comprennent l'exécution d'un balayage d'index en grappe, l'utilisation d'opérateurs de tri, la direction du flux de données à partir des opérateurs de tri et l'utilisation d'opérateurs de sélection.

Les premières bases de données qui utilisaient un optimiseur heuristique pouvaient être guidées par la façon dont une instruction SQL était écrite pour optimiser le chemin d'exécution. Les bases de données modernes utilisent une approche basée sur les coûts pour optimiser les instructions. Les bases de données conservent des métadonnées sur les données stockées pour décider de leur sélectivité et de leur volume afin de déterminer comment optimiser le flux de données. Par exemple, si l'on fusionne des ensembles de résultats intermédiaires, il est judicieux de déplacer le résultat le plus petit vers les données les plus volumineuses, ce qui est important dans une architecture de base de données distribuée ou en grappe.

Tags associés