Création de bases de données et de tables en SQL

Ê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 à l'aide des commandes CREATE DATABASE et CREATE TABLE. Si vous débutez dans SQL, vous voudrez peut-être passer en revue quelques notions de base de SQL.

Besoins de l'entreprise

Avant de nous asseoir au clavier, nous devons nous assurer de bien comprendre les exigences du client. Quel est le meilleur moyen d’obtenir cette idée? Parler au client, bien sûr! Après avoir rencontré le directeur des ressources humaines de XYZ, nous avons appris qu’il s’agissait d’une société de vente de widgets et que son principal intérêt était de rechercher des informations sur son personnel de vente.

XYZ Corporation divise sa force de vente en régions est et ouest, chacune étant divisée en plusieurs territoires couverts par des représentants individuels. Le département des ressources humaines souhaite suivre le territoire couvert par chaque employé, ainsi que les informations salariales et la structure de supervision de chaque employé. 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 de cette page.

Choisir une plate-forme 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 langage SQL (Structured Query Language). Par conséquent, toutes nos commandes de création de base de données et de table doivent être écrites dans l’esprit SQL ANSI standard.

En outre, l'utilisation de SQL conforme à la norme ANSI garantira que ces commandes fonctionneront sur tout SGBD prenant en charge le standard 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 base de données à ce stade, mais notre base de données ne permet que la création simple d'une base de données. Comme pour toutes nos commandes, vous souhaiterez peut-être consulter la documentation de votre SGBD pour déterminer si les 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 chez les programmeurs SQL d’utiliser toutes les lettres majuscules pour les mots-clés SQL tels que "CREATE" et "DATABASE" tout en utilisant des lettres minuscules pour les noms définis par l’utilisateur, comme le nom de base de données "personnel". Ces conventions permettent une lisibilité facile.

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 relatives au personnel de XYZ Corporation.

Créer notre première table

Notre première table comprend les données personnelles de chaque employé de notre entreprise. Nous devons inclure le nom, le salaire, l'identifiant et le responsable de chaque employé. Il est recommandé de séparer les noms et prénoms en champs distincts afin de simplifier la recherche et le tri des données. En outre, nous allons suivre le responsable de chaque employé en insérant une référence à l'ID d'employé du responsable dans chaque fiche d'employé. Regardons d'abord la table des employés désirés.

L'attribut ReportsTo stocke l'ID de responsable pour chaque employé. À partir des exemples d'enregistrements présentés, nous pouvons déterminer que Sue Scampi est la directrice de Tom Kendall et de John Smith. Cependant, la base de données ne contient aucune information sur le responsable 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, vérifions que nous sommes dans la base de données correcte en émettant une commande USE:

UTILISER le personnel;

Sinon, le "personnel de base de données;" La commande aurait la même fonction. Nous pouvons maintenant examiner la commande SQL utilisée pour créer la table de nos employés:

Employés de CREATE TABLE (employeeid INTEGER NOT NULL, nom de famille VARCHAR (25) NOT NULL, prénom VARCHAR (25) NOT NULL, signaler à INTEGER NULL);

Comme dans l'exemple ci-dessus, notez que selon les conventions de programmation, nous utilisons toutes les lettres majuscules pour les mots clés SQL et les lettres minuscules pour les colonnes et les tableaux nommés par l'utilisateur. La commande ci-dessus peut sembler déroutante au début, mais elle repose en réalité sur une structure simple. Voici une vue générale qui pourrait éclaircir un peu les choses:

CREATE TABLE nom_table (options nom_attribut options, …, nom_attribut options de type de données);

Attributs et types de données

Dans l'exemple précédent, le nom de la table est employee et nous incluons quatre attributs: id_employé, nom de famille, prénom et rapport. Le type de données indique le type d'informations que nous souhaitons stocker dans chaque champ. L'ID employé est un simple nombre entier. Nous allons donc utiliser le type de données INTEGER pour les champs employeeid et reportsto. Les noms d'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 famille supérieur à 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 options de l'instruction CREATE. Cela indique simplement à la base de données si des 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 des ressources humaines exige qu'un ID d'employé et un nom complet soient enregistrés pour chaque employé. Cependant, tous les employés n’ayant 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 les valeurs NULL pour un attribut.

Construire les tables restantes

Regardons maintenant la table des territoires. En regardant rapidement ces données, il apparaît que nous devons stocker un entier et deux chaînes de longueur variable. Comme dans notre exemple précédent, nous ne prévoyons pas 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 territoires (Territoryid INTEGER NOT NULL, territoire Description VARCHAR (40) NOT NULL, regionid VARCHAR (25) NON NUL);

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 que de stocker les deux numéros d'identification d'entier dans ce tableau. 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 disponible sur nos lecteurs de stockage. Nous couvrirons la commande JOIN en profondeur dans un prochain tutoriel. Voici le code SQL pour implémenter notre table finale:

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

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

Si vous êtes particulièrement astucieux aujourd'hui, vous avez peut-être remarqué que nous avions omis «accidentellement» l'une des exigences de conception lors de la mise en œuvre de nos tables de base de données. Le directeur des ressources humaines de XYZ Corporation a demandé à la base de données de suivre les informations sur les salaires des employés et nous avons omis de fournir ces informations dans les tables 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:

Employés de ALTER TABLE AJOUTER salaire 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.