Création de bases de données et de tables en SQL - Guide complet étape par étape

Êtes-vous prêt à commencer à créer des bases de données et des tables avec le langage de requête structuré ? Dans cet article, nous explorons le processus de création manuelle de tables avec les commandes CREATE DATABASE et CREATE TABLE. Si vous êtes nouveau dans SQL, vous souhaiterez peut-être passer d'abord en revue certaines bases de SQL .

Besoins de l'entreprise

Avant de nous asseoir au clavier, nous devons nous assurer que nous avons une solide compréhension des exigences du client. Quelle est la meilleure façon d'obtenir ces informations? Parler au client, bien sûr! Après nous être entretenus avec le directeur des ressources humaines de XYZ, nous avons appris qu'il s'agissait d'une société de vente de widgets et qu'il était principalement intéressé par le suivi des informations sur son personnel de vente.

XYZ Corporation divise sa force de vente en régions de l'est et de l'ouest, chacune étant divisée en de nombreux territoires couverts par des représentants des ventes individuels. Le service RH souhaite suivre le territoire couvert par chaque salarié ainsi que les informations salariales et la structure de supervision de chaque salarié. Pour répondre à ces exigences, nous avons conçu une base de données composée de trois tables, illustrées dans le diagramme Entité-Relation sur cette page.

Choisir une plateforme de base de données

Nous avons décidé d'utiliser un système de gestion de base de données (ou SGBD) basé sur le SQL (Structured Query Language). Par conséquent, toutes nos commandes de création de bases de données et de tables doivent être écrites avec le SQL ANSI standard à l'esprit.

Comme avantage supplémentaire, l'utilisation de SQL compatible ANSI garantira que ces commandes fonctionneront sur tout SGBD prenant en charge la norme SQL, y compris Oracle et Microsoft SQL Server. Si vous n'avez pas encore sélectionné de plate-forme pour votre base de données, les options du logiciel de base de données vous guident tout au long du processus de sélection.

Création de la base de données

Notre première étape consiste à créer la base de données elle-même. De nombreux systèmes de gestion de base de données offrent une série d'options pour personnaliser les paramètres de la base de données à cette étape, mais notre base de données ne permet que la simple création d'une base de données. Comme pour toutes nos commandes, vous pouvez consulter la documentation de votre SGBD pour déterminer si des paramètres avancés pris en charge par votre système spécifique répondent à vos besoins. Utilisons la commande CREATE DATABASE pour configurer notre base de données:

CREATE DATABASE personnel

Prenez note de la capitalisation utilisée dans l'exemple ci-dessus. Il est courant parmi les programmeurs SQL d'utiliser toutes les majuscules pour les mots-clés SQL tels que "CREATE" et "DATABASE" tout en utilisant toutes les lettres minuscules pour les noms définis par l'utilisateur comme le nom de la base de données "personnel". Ces conventions permettent une lisibilité aisée.

Maintenant que nous avons conçu et créé notre base de données, nous sommes prêts à commencer à créer les trois tables utilisées pour stocker les données personnelles de XYZ Corporation.

Créer notre première table

Notre premier tableau comprend les données personnelles de chaque employé de notre entreprise. Nous devons inclure le nom, le salaire, la pièce d'identité et le responsable de chaque employé. Il est recommandé de séparer le nom et le prénom dans des champs distincts pour simplifier la recherche et le tri des données à l'avenir. De plus, nous garderons une trace du responsable de chaque employé en insérant une référence à l'ID d'employé du responsable dans chaque fiche d'employé. Jetons d'abord un coup d'œil à la table des employés souhaitée.

L'attribut ReportsTo stocke l'ID de responsable de chaque employé. À partir des exemples d'enregistrements présentés, nous pouvons déterminer que Sue Scampi est la gérante de Tom Kendall et de John Smith. Cependant, il n'y a aucune information dans la base de données sur le manager de Sue, comme indiqué par l'entrée NULL dans sa ligne.

Nous pouvons maintenant utiliser SQL pour créer la table dans notre base de données du personnel. Avant de le faire, assurons-nous que nous sommes dans la bonne base de données en émettant une commande USE:

USE personnel;

Alternativement, le "personnel DATABASE;" commande exécuterait la même fonction. Nous pouvons maintenant jeter un œil à la commande SQL utilisée pour créer la table de nos employés:

CREATE TABLE employees
 (employeeid INTEGER NOT NULL,
 lastname VARCHAR(25) NOT NULL,
 firstname VARCHAR(25) NOT NULL,
 reportsto INTEGER NULL);

Comme pour l'exemple ci-dessus, notez que la convention de programmation veut que nous utilisions toutes les majuscules pour les mots clés SQL et les minuscules pour les colonnes et les tables nommées par l'utilisateur. La commande ci-dessus peut sembler déroutante au début, mais il y a en fait une structure simple derrière elle. Voici une vue généralisée qui pourrait éclaircir un peu les choses:

CREATE TABLE table_name
 (attribute_name datatype options,
 ...,
 attribute_name datatype options);

Attributs et types de données

Dans l'exemple précédent, le nom de la table est employee et nous incluons quatre attributs : employeeid, lastname, firstname et reportsto. Le type de données indique le type d'informations que nous souhaitons stocker dans chaque champ. L'ID d'employé est un simple nombre entier, nous allons donc utiliser le type de données INTEGER pour le champ employeeid et le champ reportsto. Les noms des employés seront des chaînes de caractères de longueur variable et nous ne nous attendons pas à ce qu'un employé ait un prénom ou un nom de plus de 25 caractères. Par conséquent, nous utiliserons le type VARCHAR (25) pour ces champs.

Valeurs NULL

Nous pouvons également spécifier  NULL ou NOT NULL  dans le champ d'options de l'instruction CREATE. Cela indique simplement à la base de données si les valeurs NULL (ou vides) sont autorisées pour cet attribut lors de l'ajout de lignes à la base de données. Dans notre exemple, le service RH exige qu'un identifiant d'employé et un nom complet soient stockés pour chaque employé. Cependant, tous les employés n'ont pas de responsable (le PDG ne relève de personne!), Nous autorisons donc les entrées NULL dans ce champ. Notez que NULL  est la valeur par défaut et que l'omission de cette option autorisera implicitement des valeurs NULL pour un attribut.

Construire les tables restantes

Jetons maintenant un œil au tableau des territoires. D'un rapide coup d'œil à ces données, il semble que nous devions stocker un entier et deux chaînes de longueur variable. Comme dans notre exemple précédent, nous ne nous attendons pas à ce que l'ID de région consomme plus de 25 caractères. Cependant, certains de nos territoires ont des noms plus longs, nous allons donc étendre la longueur autorisée de cet attribut à 40 caractères.

Regardons le SQL correspondant:

CREATE TABLE territories
 (territoryid INTEGER NOT NULL,
 territory Description VARCHAR(40) NOT NULL,
 regionid VARCHAR(25) NOT NULL);

Enfin, nous utiliserons la table EmployeeTerritories pour stocker les relations entre les employés et les territoires. Des informations détaillées sur chaque employé et territoire sont stockées dans nos deux tableaux précédents. Par conséquent, nous n'avons besoin de stocker que les deux numéros d'identification entiers dans cette table. Si nous devons développer ces informations, nous pouvons utiliser un JOIN dans nos commandes de sélection de données pour obtenir des informations à partir de plusieurs tables.

Cette méthode de stockage des données réduit la redondance dans notre base de données et garantit une utilisation optimale de l'espace sur nos disques de stockage. Nous aborderons en détail la commande JOIN dans un prochain tutoriel. Voici le code SQL pour implémenter notre table finale:

CREATE TABLE employeeterritories
 (employeeid INTEGER NOT NULL,
 territoryid INTEGER NOT NULL);

Le mécanisme fourni par SQL pour modifier la structure d'une base de données après sa création

Si vous êtes particulièrement astucieux aujourd'hui, vous avez peut-être remarqué que nous avons "accidentellement" omis l'une des exigences de conception lors de l'implémentation de nos tables de base de données. Le directeur des ressources humaines de XYZ Corporation a demandé que la base de données suive les informations sur les salaires des employés et nous avons négligé de fournir cela dans les tables de base de données que nous avons créées.

Cependant, tout n'est pas perdu. Nous pouvons utiliser la commande ALTER TABLE pour ajouter cet attribut à notre base de données existante. Nous voulons stocker le salaire sous forme de valeur entière. La syntaxe est assez similaire à celle de la commande CREATE TABLE, la voici:

ALTER TABLE employees
 ADD salary INTEGER NULL; 

Notez que nous avons spécifié que les valeurs NULL sont autorisées pour cet attribut. Dans la plupart des cas, il n'y a pas d'option lors de l'ajout d'une colonne à une table existante. Cela est dû au fait que la table contient déjà des lignes sans entrée pour cet attribut. Par conséquent, le SGBD insère automatiquement une valeur NULL pour combler le vide.