Modification et création d'une base de données¶
Les types de données en SQL¶
Types numériques¶
INTEGER¶
Type de données entier. La plage de valeurs possibles dépend du SGBDR utilisé.
Pour MySQL (version 32 bits): de -2147483648 to 2147483647 (de -2<sup>31</sup> à 2<sup>31</sup>-1) -> stockage sur 4 octets
L'entier peut être défini non signé grâce à l'instruction ''UNSIGNED'' (''INTEGER UNSIGNED'').
SMALLINT¶
Petit entier. Stockage moins important en mémoire.
Pour MySQL (version 32 bits): -32768 à 32767 (de -2<sup>15</sup> à 2<sup>15</sup>-1) -> stockage sur 2 octets
L'entier peut être défini non signé grâce à l'instruction ''UNSIGNED'' (''SMALLINT UNSIGNED'').
TINYINT¶
Un très petit entier. Stockage encore moins important en mémoire.
Pour MySQL (version 32 bits): -128 à 127 (de -2<sup>7</sup> à 2<sup>7</sup>-1) -> stockage sur 1 octets
DECIMAL¶
Nombre décimal stockée sous la forme d'une chaîne de caractères.
''DECIMAL(M,D)'':
-
M -> nombre total de chiffres décrivant le nombre décimal
-
D -> nombre de chiffres après la virgule
Par exemple, pour stocker des euros: ''DECIMAL(5,2)'' permettra de stocker les euros, au centime près, jusqu'à de -999.99 à 999.99 euros.
FLOAT et DOUBLE¶
Nombre réel à virgule flottante: une mantisse + un exposant.
La précision de ''FLOAT'' et ''DOUBLE'' dépend du SGBDR (et de la machine) utilisé(s).
Types caractères¶
CHARACTER ou CHAR¶
Stockage d'un ou plusieurs caractères:
-
''CHAR'' : stockage d'un seul caractère
-
''CHAR(M)'' : stockage de chaînes de M caractères. Si la chaîne entrée n'atteint pas M caractères, le SGBDR ajoute des caractères blancs pour compléter la chaîne jusqu'à M caractères.
-
''VARCHAR(M)'' : idem que pour ''CHAR(M)'' mais sans complétion par des caractères blancs. La longueur de stockage (en octets) est alors variable.
Types temporels¶
DATE¶
''DATE'' représente une date du calendrier limitée du 1er janvier de l'an 1 ('''0001-01-01''') au 31 décembre de l'an 9999 ('''9999-12-31''').
TIME¶
''TIME'' désigne un temps sur vingt-quatre heures en heures, minutes et secondes ('''HH:MM:SS''').
TIMESTAMP¶
''TIMESTAMP'' désigne une date combinée à un temps ('''AAAA-MM-JJ HH:MM:SS''')
Types BLOB/TEXT¶
Une valeur de type ''BLOB'' est un objet binaire de grande taille, qui peut contenir une quantité variable de données. Les quatre types ''BLOB'' (''TINYBLOB'', ''BLOB'', ''MEDIUMBLOB'', et ''LONGBLOB'') ne différent que par la taille maximale de données qu'ils peuvent stocker.
Les quatre types ''TEXT'' (''TINYTEXT'', ''TEXT'', ''MEDIUMTEXT'', et ''LONGTEXT'' correspondent aux types ''BLOB'' équivalents, et ont les mêmes contraintes de stockage. Les seules différences entre les colonnes de type ''BLOB'' et celles de type ''TEXT'' se situent aux niveau des tris et comparaisons: Les tris, faits sur les ''BLOB'', contrairement à ceux faits sur les ''TEXT'', tiennent compte de la casse. En d'autres termes, une valeur ''TEXT'' est une valeur ''BLOB'' insensible à la casse.
Si vous assignez une valeur trop grande à une colonne de type ''BLOB'' ou ''TEXT'', la valeur sera tronquée à la taille maximale possible.
Dans la majorité des cas, vous pouvez considérer une colonne de type ''TEXT'' comme une colonne de type ''VARCHAR'', aussi grande que vous le souhaitez.
Autres types de données¶
BOOLEAN¶
''BOOLEAN'' ou ''LOGICAL'' permet de stocker des variables booléennes qui peuvent prendre les valeurs ''TRUE'', ''FALSE'' ou ''UNKNOWN''.
DOMAIN¶
Il est possible en SQL de créer ses propres types à partir des types de données pré-existants.
<code mysql|exemple d'utilisation de DOMAIN>
CREATE DOMAIN DOM_CODE_POSTAL AS CHAR(5)
</code>
Créations des éléments d'une base de données¶
Nom des objets d'une même base de données¶
Il doit obéir à certains critères:
-
Le nom doit être unique dans le SGBDR utilisé (c'est un identifiant de la BDR).
-
Il doit commencer par un caractère alphabétique (A-Z ou a-z)
-
Le nom ne doit contenir que le jeu de caractères de A à Z, de 0 à 9 et le caractère blanc souligné (''%%_%%'')
-
Il ne doit pas être plus long que 128 caractères
-
Il ne doit pas être un mot réservé (ex. ''WHERE'', ''SELECT'', etc.)
Création/Suppression d'une base de données¶
La commande ''CREATE DATABASE'' crée une base de données.
<code mysql|création d'une base de données>
mysql> CREATE DATABASE Base_test;
</code>
La commande ''DROP DATABASE'' supprime une base de données ainsi que son contenu.
<code mysql|suppression d'une base de données>
mysql> DROP DATABASE Base_test;
</code>
Le nom de la base de données est sensible à la casse.
Création/Suppresion d'une table¶
La création d'une table s'effectue avec la commande ''CREATE TABLE'', la suppression avec la commande ''DROP TABLE'' suivi du nom de la table. Ce nom est sensible à la casse.
Syntaxes:
<code mysql|Syntaxe de CREATE TABLE>
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] nomTable
(nom_colonne type_colonne [, nom_colonne2 type_colonne2 ...]);
</code>
<code mysql|Exemple de creation d'une table>
mysql> CREATE TABLE Table_test
(Nombre INTEGER,
Reel FLOAT,
Chaine VARCHAR(100));
mysql> desc Table_test;
+--------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
| ----- | ---- | ---- | --- | ------- | ----- |
+--------+--------------+------+-----+---------+-------+
| Nombre | int(11) | YES | | NULL | |
| ------ | ------- | --- | | ---- | |
| Reel | float | YES | | NULL | |
| Chaine | varchar(100) | YES | | NULL | |
+--------+--------------+------+-----+---------+-------+
3 rows in set (0.35 sec)
</code>
<code mysql|Syntaxe de DROP TABLE>
DROP [TEMPORARY] TABLE [IF EXISTS]
tbl_name [, tbl_name] ...
</code>
Contraintes de colonnes¶
Un certain nombre de contraintes peuvent être posées directement sur les colonnes des tables.
Lorsqu'une contrainte est posée, aucune insertion, modification ou suppression n'est possible sans le respect absolue de la contrainte.
Donnée obligatoire¶
Par le mot-clé ''NOT NULL'', on peut obliger une colonne à toujours posséder une valeur.
<code mysql| Utilisation de NOT NULL>
mysql> CREATE TABLE Table_test
(Nombre INTEGER NOT NULL,
Reel FLOAT,
Chaine VARCHAR(100));
mysql> desc Table_test;
+--------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
| ----- | ---- | ---- | --- | ------- | ----- |
+--------+--------------+------+-----+---------+-------+
| Nombre | int(11) | NO | | NULL | |
| ------ | ------- | -- | | ---- | |
| Reel | float | YES | | NULL | |
| Chaine | varchar(100) | YES | | NULL | |
+--------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
</code>
Valeur par défaut¶
Mot-clé: ''DEFAULT''
<code mysql| Utilisation de DEFAULT>
mysql> CREATE TABLE Table_test
(Nombre INTEGER NOT NULL,
Reel FLOAT,
Chaine VARCHAR(100) DEFAULT "Toto",
Date TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
mysql> desc Table_test;
+--------+--------------+------+-----+-------------------+-------+
| Field | Type | Null | Key | Default | Extra |
| ----- | ---- | ---- | --- | ------- | ----- |
+--------+--------------+------+-----+-------------------+-------+
| Nombre | int(11) | NO | | NULL | |
| ------ | ------- | -- | | ---- | |
| Reel | float | YES | | NULL | |
| Chaine | varchar(100) | YES | | Toto | |
| Date | timestamp | YES | | CURRENT_TIMESTAMP | |
+--------+--------------+------+-----+-------------------+-------+
4 rows in set (0.00 sec)
</code>
colonne clef¶
Une colonne unique peut servir de clef à une table. Cela peut être précisé à la définition de la colonne grâce au mot-clé ''PRIMARY KEY''.
En principe, lorsqu'on définit une clé sur une table, la valeur de cette clé doit être unique. Il est possible pour se faciliter la tâche d'employer en plus un autre mot-clé: ''AUTO_INCREMENT'' qui incrémente automatiquement à chaque ajout d'une ligne de données la valeur de la colonne correspondante.
<code mysql|Utilisation de PRIMARY KEY et de AUTO_INCREMENT>
mysql> CREATE TABLE animals (
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (id)
);
mysql> desc animals;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
| ----- | ---- | ---- | --- | ------- | ----- |
+-------+--------------+------+-----+---------+----------------+
| id | mediumint(9) | NO | PRI | NULL | auto_increment |
| -- | ------------ | -- | --- | ---- | -------------- |
| name | char(30) | NO | | NULL | |
+-------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
</code>
Valeur unique¶
Une colonne peut recevoir une valeur ayant un caractère unique au sein de la colonne grâce au mot-clé ''UNIQUE''.
<code mysql| Utilisatin de UNIQUE>
mysql> CREATE TABLE Table_test
(Nombre INTEGER NOT NULL UNIQUE,
Reel FLOAT,
Chaine VARCHAR(100) DEFAULT "Toto",
Date TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
+--------+--------------+------+-----+-------------------+-------+
| Field | Type | Null | Key | Default | Extra |
| ----- | ---- | ---- | --- | ------- | ----- |
+--------+--------------+------+-----+-------------------+-------+
| Nombre | int(11) | NO | PRI | NULL | |
| ------ | ------- | -- | --- | ---- | |
| Reel | float | YES | | NULL | |
| Chaine | varchar(100) | YES | | Toto | |
| Date | timestamp | YES | | CURRENT_TIMESTAMP | |
+--------+--------------+------+-----+-------------------+-------+
4 rows in set (0.00 sec)
</code>
Contrôle de validité¶
La contrainte ''CHECK'' permet de préciser une condition de validité des valeurs de la colonne.
<code mysql| CHECK>
mysql> CREATE TABLE Table_test
(Nombre INTEGER NOT NULL UNIQUE CHECK (Nombre BETWEEN 1 AND 102),
Reel FLOAT,
Chaine VARCHAR(100) DEFAULT "Toto",
Date TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
mysql> desc Table_test;
+--------+--------------+------+-----+-------------------+-------+
| Field | Type | Null | Key | Default | Extra |
| ----- | ---- | ---- | --- | ------- | ----- |
+--------+--------------+------+-----+-------------------+-------+
| Nombre | int(11) | NO | PRI | NULL | |
| ------ | ------- | -- | --- | ---- | |
| Reel | float | YES | | NULL | |
| Chaine | varchar(100) | YES | | Toto | |
| DATE | timestamp | YES | | CURRENT_TIMESTAMP | |
+--------+--------------+------+-----+-------------------+-------+
4 rows in set (0.00 sec)
</code>
Clé(s) étrangère(s)¶
Si une colonne se trouve être une référence à une clé dans un autre table, on peut établir un contrôle de validité sur cette colonne grâce aux mot-clés ''FOREIGN KEY'' et ''REFERENCES''
<code mysql| Utilisation de FOREIGN KEY et REFERENCES>
mysql>CREATE TABLE Joueurs
(
JoueurID INTEGER AUTO_INCREMENT NOT NULL,
Numero INTEGER NOT NULL,
Prenom VARCHAR(100) NOT NULL,
Nom VARCHAR(100) NOT NULL,
Poste VARCHAR(100) NOT NULL,
Date_de_naissance DATE NOT NULL,
Nationalite VARCHAR(3) NOT NULL,
ClubID INTEGER NOT NULL,
PRIMARY KEY (JoueurID),
FOREIGN KEY (ClubID) REFERENCES Club,
UNIQUE (Numero)
);
mysql> desc Joueurs;
+-------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
| ----- | ---- | ---- | --- | ------- | ----- |
+-------------------+--------------+------+-----+---------+----------------+
| JoueurID | int(11) | NO | PRI | NULL | auto_increment |
| -------- | ------- | -- | --- | ---- | -------------- |
| Numero | int(11) | NO | | NULL | |
| Prenom | varchar(100) | NO | | NULL | |
| Nom | varchar(100) | NO | | NULL | |
| Poste | varchar(100) | NO | | NULL | |
| Date_de_naissance | date | NO | | NULL | |
| Nationalite | varchar(3) | NO | | NULL | |
| ClubID | int(11) | NO | MUL | NULL | |
+-------------------+--------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)
</code>
Contraintes de table¶
D'autres contraintes peuvent être imposées sur la table elle-même et, par conséquent, concerner plusieurs colonnes de la table. Elles sont définies par le mot-clé ''CONSTRAINT''.
Clé de table¶
''PRIMARY KEY''
<code mysql| PRIMARY KEY dans une contrainte de table>
mysql> CREATE TABLE Table_test
( Nom VARCHAR(100),
Prenom VARCHAR(100),
Date_de_naissance DATE,
Ville VARCHAR(100),
Nationalite VARCHAR(100),
CONSTRAINT Identifieur PRIMARY KEY (Nom, Prenom, Date_de_naissance)
);
mysql> desc Table_test;
+-------------------+--------------+------+-----+------------+-------+
| Field | Type | Null | Key | Default | Extra |
| ----- | ---- | ---- | --- | ------- | ----- |
+-------------------+--------------+------+-----+------------+-------+
| Nom | varchar(100) | NO | PRI | NULL | |
| --- | ------------ | -- | --- | ---- | |
| Prenom | varchar(100) | NO | PRI | NULL | |
| Date_de_naissance | date | NO | PRI | 0000-00-00 | |
| Ville | varchar(100) | YES | | NULL | |
| Nationalite | varchar(100) | YES | | NULL | |
+-------------------+--------------+------+-----+------------+-------+
5 rows in set (0.00 sec)
</code>
Unicité de données¶
Mot-clé: ''UNIQUE''
<code mysql|Exemple d'utilisation de UNIQUE avec CONSTRAINT>
mysql>CREATE TABLE Joueurs
(
JoueurID INTEGER AUTO_INCREMENT NOT NULL,
Numero INTEGER NOT NULL,
Prenom VARCHAR(100) NOT NULL,
Nom VARCHAR(100) NOT NULL,
Poste VARCHAR(100) NOT NULL,
Date_de_naissance DATE NOT NULL,
Nationalite VARCHAR(3) NOT NULL,
ClubID INTEGER NOT NULL,
PRIMARY KEY (JoueurID),
FOREIGN KEY (ClubID) REFERENCES Club,
CONSTRAINT Numero_dans_club UNIQUE (Numero, ClubID)
);
</code>
Clés étrangères¶
Mot-clé: ''FOREIGN KEY''
Modification d'une table¶
<code mysql|Syntaxe de ALTER TABLE>
ALTER [IGNORE] TABLE tbl_name
alter_specification [, alter_specification] ...
alter_specification:
ADD [COLUMN] column_definition [FIRST | AFTER col_name ]
| ADD [COLUMN] (column_definition,...)
| ------------------------------------
| ADD {INDEX | KEY} [index_name] [index_type] (index_col_name,...)
| ADD [CONSTRAINT [symbol]]
PRIMARY KEY [index_type] (index_col_name,...)
| ADD [CONSTRAINT [symbol]]
| -------------------------
UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,...)
| ADD [FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (index_col_name,...)
| ------------- | --------------- | --------------------------------------
| ADD [CONSTRAINT [symbol]]
FOREIGN KEY [index_name] (index_col_name,...)
[reference_definition]
| ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
| -------------------------------------------- | -------------
| CHANGE [COLUMN] old_col_name column_definition
[FIRST|AFTER col_name]
| MODIFY [COLUMN] column_definition [FIRST | AFTER col_name]
| ---------------------------------------- | ---------------
| DROP [COLUMN] col_name
| DROP PRIMARY KEY
| DROP {INDEX | KEY} index_name
| DROP FOREIGN KEY fk_symbol
| DISABLE KEYS
| ENABLE KEYS
| RENAME [TO] new_tbl_name
| ORDER BY col_name
| table_option ...
</code>
<code mysql|Exemple d'ajout d'une colonne pointure de chaussure>
mysql> alter table Joueurs
-> add column Pointure INTEGER DEFAULT 41 NOT NULL;
Query OK, 32 rows affected (0.03 sec)
Records: 32 Duplicates: 0 Warnings: 0
mysql> select Nom, Prenom, Pointure from Joueurs where numero < 10;
+-----------+-------------------+----------+
| Nom | Prenom | Pointure |
| --- | ------ | -------- |
+-----------+-------------------+----------+
| Coupet | Gregory | 41 |
| ------ | ------- | -- |
| Muller | Patrick | 41 |
| Cris | Cristiano Marques | 41 |
| Clerc | Francois | 41 |
| Cacapa | Claudio Roberto | 41 |
| Kallstrom | Kim | 41 |
| Juninho | Pernambucano | 41 |
| Carew | John | 41 |
| Fabinho | | 41 |
| Ciani | Mickael | 41 |
+-----------+-------------------+----------+
10 rows in set (0.00 sec)
mysql> alter table Joueurs drop column Pointure;
Query OK, 32 rows affected (0.01 sec)
Records: 32 Duplicates: 0 Warnings: 0
</code>
Insertion de nouvelles données¶
<code mysql| INSERT>
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [(col_name,...)]
VALUES ({expr | DEFAULT},...),(...),...
[ ON DUPLICATE KEY UPDATE col_name=expr, ... ]
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name SET col_name={expr | DEFAULT}, ... [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]
INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [(col_name,...)]
SELECT ...
[ ON DUPLICATE KEY UPDATE col_name=expr, ... ]
</code>
<code mysql|Insertion d'un nouveau club>
mysql> insert Club (Nom) VALUES ("RC Strasbourg");
Query OK, 1 row affected (0.00 sec)
mysql> select * from Club where Nom="RC Strasbourg";
+--------+---------------+
| ClubID | Nom |
| ------ | --- |
+--------+---------------+
| 21 | RC Strasbourg |
| -- | ------------- |
+--------+---------------+
1 row in set (0.00 sec)
</code>
Modification de la valeur des données¶
<code mysql|UPDATE>
Single-table syntax:
UPDATE [LOW_PRIORITY] [IGNORE] tbl_name SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE where_condition] [ORDER BY ...] [LIMIT row_count]
Multiple-table syntax:
UPDATE [LOW_PRIORITY] [IGNORE] table_references
SET col_name1=expr1 [, col_name2=expr2 ...]
[WHERE where_condition]
</code>
Suppression de données¶
<code mysql|DELETE>
Single-table syntax:
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [WHERE where_condition] [ORDER BY ...] [LIMIT row_count]
Multiple-table syntax:
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] tbl_name[.] [, tbl_name[.]] ... FROM table_references [WHERE where_condition]
Or:
DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
FROM tbl_name[.] [, tbl_name[.]] ...
USING table_references
[WHERE where_condition]
</code>