Création et Gestion des objets d'une base de données
I. Introduction
Ce document vous propose d’apprendre à utiliser un sous-ensemble du langage SQL, le LDD pour créer et modifier des objets de base de données, en particulier les tables. Vous apprendrez également à modifier et supprimer des tables.
A la fin de ce chapitre, vous saurez :
- Créer, utiliser et supprimer une base de données.
- Créer des tables
- Décrire les différents types de données utilisables pour les définitions de colonne
- Modifier la définition des tables
- Supprimer, tronquer et copier une table
II. Commandes de gestion de la base de données
Créez une base de données en utilisant l'ordre SQL CREATE DATABASE.
Syntaxe :
CREATE DATABASE nom de la base de données
Exemple
CREATE DATABASE GestCom
Utiliser une base de données en utilisant l’ordre SQL USE.
Syntaxe :
USE nom de la base de données
Exemple
USE GestCom
Supprimer une base de données en utilisant l’ordre SQL DROP DATABASE.
Syntaxe :
DROP DATABASE nom de la base de données
Exemple
DROP DATABASE GestCom
III. Objets d'une Base de Données
|
Objet |
Description |
|
Table |
Unité de stockage élémentaire, composée de lignes et de colonnes |
|
Vue |
Représente de manière logique des sous-groupes de données |
|
Index |
Améliore les performances de certaines requêtes |
Une base de données peut contenir de nombreuses structures de données.
Chaque structure doit être prédéfinie lors de la conception de la base de données pour pouvoir être créée durant la phase de construction de la base.
- Table : stocke les données
- Vue : sous-groupes de données issues d'une ou de plusieurs tables. Elle propose une vue logique des données mais ne stocke pas les données.
- Index : améliore les performances de certaines requêtes
Nommez les tables et colonnes de votre base de données en suivant les règles de dénomination applicables à tous les objets d'une base de données SQL Server.
Les noms de tables et de colonnes doivent commencer par une lettre et peuvent comprendre de 1 à 255 caractères.
Un nom :
- Doit commencer par une lettre
- Peut comporter de 1 à 255 caractères
- Ne peut contenir que les caractères A à Z, a à z, 0 à 9, (_, $, et #, et espaces déconseillés)
- Ne doit pas porter le nom d’un autre objet appartenant au même utilisateur
- Ne doit pas être un mot réservé SQL Server
Utilisez des noms signifiants.
Utilisez une dénomination uniforme pour des colonnes identiques appartenant à des tables différentes.
Par exemple : la colonne numéro du département s'appelle DEPTNO dans la table EMP et dans la table DEPT.
Remarque : les majuscules et minuscules ne sont pas différenciées dans les noms. Par exemple, EMP est identique à eMP ou eMp. D’une manière générale, SQL server n’est pas sensible à la casse des caractères.
IV. L'ordre CREATE TABLE
Créez des tables pour stocker des données en utilisant l'ordre SQL CREATE TABLE.
Syntaxe :
|
table |
nom de la table |
|
column |
nom de la colonne |
|
datatype |
type de données et longueur de la colonne |
CREATE TABLE table
(col1 datatype [taille][identity] [DEFAULT expr] [NOT NULL], Col2 datatype [taille] [DEFAULT expr] [UNIQUE], ...)
Spécifiez :
- Un nom de table
- Le nom, le type de données et la taille des colonnes.
L'exemple ci-dessus crée la table DEPT avec deux colonnes nommées DEPTNO, DEPTNAME :
CREATE TABLE dept (DEPTNO INT, DEPTNAME CHAR (25))
Ou
CREATE TABLE dept
(DEPTNO INT, DEPTNAME CHAR (25) DEFAULT ‘Ressources humain’)
Expression définissant la valeur d'une colonne calculée. Une colonne calculée est une colonne virtuelle qui n'est pas stockée physiquement dans une table. Elle est calculée à partir d'une expression qui utilise d'autres colonnes dans la même table.
Par exemple, une colonne calculée peut avoir la définition suivante : Cout AS Prix * Quantite.
L'expression peut être un nom de colonne non calculée, une constante, une fonction, une variable et toute combinaison de ces éléments reliés par un ou plusieurs opérateurs. L'expression ne peut pas être une sous-requête.
Exemple :
CREATE TABLE Produit
(ProductID int IDENTITY(1,1) NOT NULL,
QteStock int,
PrixU money,
Valeur as QteStock *PrixU)
La requête suivante calcule la colonne valeur :
Select ProductID, QteStock, PrixU, Valeur
From Produit
Dans Microsoft SQL Server, chaque colonne, variable locale, expression et paramètre possède un type de données. L'ensemble des types de données fournis par le système est décrit cidessous.
Les types de données de base pour les valeurs d’une colonne proposés par le système :
|
bigint |
binary |
bit |
char |
datetime |
|
decimal |
float |
image |
int |
money |
|
nchar |
ntext |
numeric |
nvarchar |
real |
|
smalldatetime |
smallint |
smallmoney |
varchar |
text |
|
timestamp |
tinyint |
varbinary |
uniqueidentifier |
xml |
L'affectation d'un type de données à un objet définit quatre attributs de l'objet :
- le type de données contenues par l'objet
- la longueur ou la taille de la valeur stockée
- la précision du nombre (types de données numériques uniquement) ▪ l'échelle du nombre (types de données numériques uniquement).
-
bit
Nombre entier dont la valeur est 1 ou 0. Peut aussi être null
int
Nombre entier dont la valeur est comprise entre -231 (-2 147 483 648) et 231 - 1 (2 147 483 647).
smallint
Nombre entier dont la valeur est comprise entre -215 (-32 768) et 215 - 1 (32 767).
tinyint
Nombre entier dont la valeur est comprise entre 0 et 255.
decimal
Données numériques de précision et d'échelle fixes comprises entre -1038 -1 et 1038 -1.
numeric
Synonyme de decimal.
money
Valeurs de données monétaires comprises entre -263 (-922 337 203 685 477,580 8) et 263 - 1 (+922 337 203 685 477,580 7), avec une précision d'un dix-millième d'unité monétaire.
smallmoney
Valeurs de données monétaires comprises entre - 214 748,3648 et +214 748,3647, avec une précision d'un dix-millième d'unité monétaire.
float
Données numériques de précision en virgule flottante comprises entre -1.79E + 308 et 1.79E + 308.
real
Données numériques de précision en virgule flottante comprises entre -3.40E + 38 et 3.40E + 38.
date
Données de date comprises entre le 1er janvier 1753 et le 31 décembre 9999,
datetime
Données de date et d'heure comprises entre le 1er janvier 1753 et le 31 décembre 9999, avec une précision de trois centièmes de seconde ou de 3,33 millisecondes.
smalldatetime
Données de date et d'heure comprise entre le 1er janvier 1900 et le 6 juin 2079, avec une précision d'une minute.
char
Données non Unicode de longueur fixe d'un maximum de 8 000 caractères.
varchar
Données non Unicode de longueur variable text
Données non Unicode de longueur variable ne pouvant pas dépasser 231 - 1 (2 147 483 647) caractères.
Chaînes de caractères Unicode nchar
Données Unicode de longueur fixe ne pouvant pas dépasser 4 000 caractères.
nvarchar
Données Unicode de longueur variable ne pouvant pas dépasser 4 000 caractères.
ntext
Données Unicode de longueur variable ne pouvant pas dépasser 230 - 1 (1 073 741 823) caractères.
binary
Données binaires de longueur fixe. varbinary
Données binaires de longueur variable
image
Données binaires de longueur variable ne pouvant pas dépasser 231 - 1 (2 147 483 647) octets.
V. L’ordre ALTER TABLE
Après avoir créé vos tables, il peut arriver que vous deviez en modifier la structure pour ajouter une colonne oubliée ou que vous décidiez de changer une définition de colonne. Cela est possible grâce à l'ordre ALTER TABLE.
Vous pouvez ajouter des colonnes à une table en utilisant l'ordre ALTER TABLE avec la clause ADD.
Vous pouvez modifier des colonnes existantes d'une table au moyen de l'ordre ALTER TABLE avec la clause ALTER COLUMN.
Utilisez l'ordre ALTER TABLE pour :
- Ajouter ou supprimer une colonne
- Modifier une colonne existante
- Définir une valeur par défaut pour une nouvelle colonne
Syntaxe :
ALTER TABLE table
ADD (col1 datatype [DEFAULT expr]
[, col2 datatype]...);
ALTER TABLE table
ALTER COLUMN col1 datatype [DEFAULT expr]
[, col2 datatype]...
ALTER TABLE table
DROP COLUMN col1 [, col2 ]
L'exemple ci-dessus ajoute la colonne nommée JOB à la table DEPT30. La colonne JOB devient la dernière colonne de la table.
Remarque : si une table contient déjà des lignes lorsque l'on ajoute une colonne, la nouvelle colonne sera initialisée à NULL pour toutes les lignes sauf si l’on précise une valeur par défaut, dans ce cas pour toutes les lignes de la table, la colonne sera initialisée avec la valeur par défaut.
• Utilisez la clause ADD pour ajouter des colonnes.
Exemple :
ALTER TABLE dept ADD job VARCHAR(9);
La nouvelle colonne est placée à la fin.
EMPNO ENAME ANNSAL HIREDATE JOB
--------- ---------- --------- --------- ----
7698 BLAKE 34200 01-MAY-81
7654 MARTIN 15000 28-SEP-81 7499 ALLEN 19200 20-FEB-81
7844 TURNER 18000 08-SEP-81
--------- ---------- --------- --------- ----
Vous pouvez modifier la définition d'une colonne au moyen de l'ordre ALTER TABLE et de la clause ALTER COLUMN. Les modifications effectuées peuvent être des modifications du type de données, de taille et de la valeur par défaut. De nombreuses restrictions existent notamment lorsque la colonne est utilisée comme contrainte.
Conseils
- Vous pouvez augmenter la largeur ou la précision d'une colonne numérique.
- Réduire la largeur d'une colonne. Cette réduction peut entrainer des pertes de données par troncation.
- Modifiez le type de données dans la limite des capacités de conversion (Char en Varchar, ...)
- La modification d'une valeur par défaut ne s'appliquera qu'aux insertions ultérieures.
Modification de Colonnes
- Vous pouvez modifier le type de données, la taille et la valeur par défaut d'une colonne.
ALTER TABLE dept
ALTER COLUMN (DEPTNAME VARCHAR(65));
Vous pouvez supprimer une colonne au moyen de l'ordre ALTER TABLE et de la clause DROP COLUMN.
L'exemple ci-dessus supprime la colonne nommée JOB à la table DEPT30.
Exemple :
ALTER TABLE dept DROP COLUMN job;
VI. Suppression de Tables
L'ordre DROP TABLE supprime la définition d'une table
Lorsque vous supprimez une table, la base de données perd toutes les données de la table ainsi que tous les index associés.
Syntaxe
DROP TABLE nom de la table
Exemple
DROP TABLE dept
Conseils
- Toutes les données de la table sont supprimées.
- Les vues ne sont pas supprimées mais ne sont plus utilisables.
- Toute transaction en instance est validée.
- Seul le créateur de la table ou un utilisateur ayant le privilège DROP ANY TABLE peut supprimer une table.
Une fois exécuté, l'ordre DROP TABLE est irréversible.
Si vous êtes le propriétaire de la table ou disposez d'un niveau de privilège élevé, la table sera immédiatement supprimée.
Tous les ordres du LDD effectuent une validation qui rend la transaction permanente.
TRUNCATE TABLE est un autre ordre LDD, qui permet de supprimer toutes les lignes d'une table tout en libérant l'espace utilisé pour stocker cette table. L'ordre TRUNCATE TABLE ne peut être annulé.
Syntaxe
TRUNCATE TABLE nom de la table
L'ordre DROP quant à lui supprime aussi les lignes d'une table, mais il ne libère pas l'espace de stockage.
TRUNCATE TABLE department;
- Vous ne pouvez pas annuler un ordre TRUNCATE
- Vous pouvez aussi utiliser l'ordre DROP pour supprimer des lignes
VII. Copie de Tables
SELECT INTO est un autre ordre LDD, qui permet de créer une nouvelle table à partir des données d’une table de la base de données.
Syntaxe
SELECT col1 [, col2 ] INTO nouvelle table FROM ancienne table
Exemple
SELECT AV#, MARQUE, CAPACITE
INTO AVION1
FROM AVION
VIII. Résumé
CREATE TABLE
- Crée une table.
ALTER TABLE
- Modifie la structure d'une table.
- Change la largeur des colonnes, le type de données des colonnes, ajoute des colonnes, supprime des colonnes.
DROP TABLE
- Supprime les lignes et la structure d'une table.
- Cet ordre ne peut être annulé après exécution.
TRUNCATE
- Supprime toutes les lignes d'une table et libère l'espace occupé par la table.
- L'ordre DROP supprime seulement les lignes.
SELECT …INTO
- Crée une table à partir des données d’une table de la base de données.
IX. Atelier pratique
- Faire les exemples précédents.
- Créer la structure des bases de données études de cas : “Bibliothéque et Clinique ”
- Créer les structures des tables de la base de données avec précision des colonnes, types, tailles
- Modifier les structures des tables de la base de données : supprimer la table livre
- Modifier les structures des tables de la base de données : ajouter la table livre
- Modifier les structures des tables de la base de données : supprimer la colonne éditeur de la table livre
- Modifier les structures des tables de la base de données : ajouter la colonne éditeur de la table livre
- Modifier les structures des tables de la base de données : modifier le type et la taille la colonne éditeur de la table livre
Pas encore de commentaires.
Ajouter un commentaire
Veuillez vous connecter pour ajouter un commentaire.