Types de requêtesLe langage SQL (implémentation mySQL)
Création d’une base de données et de tablesImportation des données et indexation
Recherche d’informationModifications, procédures et administration
Le langage SQL
D. Puthier1
1ERM206/Technologies Avancées pour le Génome et la Clinique,http://tagc.univ-mrs.fr/staff/Puthier,
ESIL, 2009
D. Puthier Bases de données
Types de requêtesLe langage SQL (implémentation mySQL)
Création d’une base de données et de tablesImportation des données et indexation
Recherche d’informationModifications, procédures et administration
Les projectionsLes sélectionsJointure
Exemple d’un schéma de base de données
D. Puthier Bases de données
Types de requêtesLe langage SQL (implémentation mySQL)
Création d’une base de données et de tablesImportation des données et indexation
Recherche d’informationModifications, procédures et administration
Les projectionsLes sélectionsJointure
Exemple d’un schéma de base de données(SBEAMS-Microarray)
D. Puthier Bases de données
Types de requêtesLe langage SQL (implémentation mySQL)
Création d’une base de données et de tablesImportation des données et indexation
Recherche d’informationModifications, procédures et administration
Les projectionsLes sélectionsJointure
introduction
Informations : stockées de façon structurée mais éparpillée dans lestables d’une base de données relationnelle.
Comment interagir avec la base de données ?
–> Utilisation de requêtes en langage SQL.
D. Puthier Bases de données
Types de requêtesLe langage SQL (implémentation mySQL)
Création d’une base de données et de tablesImportation des données et indexation
Recherche d’informationModifications, procédures et administration
Les projectionsLes sélectionsJointure
Pourquoi une requête ?
3 fonctions principales :Recherche d’informations dans la BDD.Maintenance de la BDD (mise à jour d’enregistrements, de tables,...).Création de vues (“views”).Ecriture de procédures et fonctions.
D. Puthier Bases de données
Types de requêtesLe langage SQL (implémentation mySQL)
Création d’une base de données et de tablesImportation des données et indexation
Recherche d’informationModifications, procédures et administration
Les projectionsLes sélectionsJointure
Recherche d’informations
Création d’une sous-table contenant les enregistrements répondant àcertains critères et appartenant à certains champs.
Porte à la fois sur les lignes et les colonnes d’une table, ou de plusieurstables liées par des relations.
Opérations : projection, selection, jointure.
D. Puthier Bases de données
Types de requêtesLe langage SQL (implémentation mySQL)
Création d’une base de données et de tablesImportation des données et indexation
Recherche d’informationModifications, procédures et administration
Les projectionsLes sélectionsJointure
Projection des données
Projection des donnéesSélection de colonnes.Nombre de lignes reste inchangé : pas de critère de sélection relatif aucontenu des enregistrements.
A B C D123
⇓
A D123
D. Puthier Bases de données
Types de requêtesLe langage SQL (implémentation mySQL)
Création d’une base de données et de tablesImportation des données et indexation
Recherche d’informationModifications, procédures et administration
Les projectionsLes sélectionsJointure
Les sélections
SélectionSélection des lignes selon des critères définis par l’utilisateur.Cas le plus simple : requête appliquée à une seule table.
A B C D
1
2
3
⇓
A B C D
1
3
D. Puthier Bases de données
Types de requêtesLe langage SQL (implémentation mySQL)
Création d’une base de données et de tablesImportation des données et indexation
Recherche d’informationModifications, procédures et administration
Les projectionsLes sélectionsJointure
Les jointures
Les jointures permettent d’exploiter pleinement le modèle relationneld’une base de données.
Une jointure permet de combiner les colonnes de plusieurs tables.
Différents types de jointures (croisées, internes, externes).
D. Puthier Bases de données
Types de requêtesLe langage SQL (implémentation mySQL)
Création d’une base de données et de tablesImportation des données et indexation
Recherche d’informationModifications, procédures et administration
Les projectionsLes sélectionsJointure
Les jointures
La jointure est une opération dans laquelle on effectue le produitcartésien de deux ensembles X et Y :
En mathématiques, le produit cartésien de deux ensembles X et Y estl’ensemble de tous les couples, dont la première composante appartient àX et la seconde à Y.
le produit cartesien de 2 tables correspond à une jointure croisée(“CROSS-JOIN”).
D. Puthier Bases de données
Types de requêtesLe langage SQL (implémentation mySQL)
Création d’une base de données et de tablesImportation des données et indexation
Recherche d’informationModifications, procédures et administration
Les projectionsLes sélectionsJointure
NOM PRENOM CODEMARTIN JEAN ENT2DURAND JACQUES ENT3DUPOND OLIVIER ENT1LEGRAND ALAIN NULL
CODE COMPAGNIEENT1 La PosteENT2 EDFENT3 SNCFENT4 SNCMNULL GOOGLE
NOM PRENOM CODE CODE COMPAGNIEMARTIN JEAN ENT2 ENT1 La PosteDURAND JACQUES ENT3 ENT1 La PosteDUPOND OLIVIER ENT1 ENT1 La PosteLEGRAND ALAIN NULL ENT1 La PosteMARTIN JEAN ENT2 ENT2 EDFDURAND JACQUES ENT3 ENT2 EDFDUPOND OLIVIER ENT1 ENT2 EDFLEGRAND ALAIN NULL ENT2 EDFMARTIN JEAN ENT2 ENT3 SNCFDURAND JACQUES ENT3 ENT3 SNCFDUPOND OLIVIER ENT1 ENT3 SNCFLEGRAND ALAIN NULL ENT3 SNCFMARTIN JEAN ENT2 ENT4 SNCMDURAND JACQUES ENT3 ENT4 SNCMDUPOND OLIVIER ENT1 ENT4 SNCMLEGRAND ALAIN NULL ENT4 SNCMMARTIN JEAN ENT2 NULL GOOGLEDURAND JACQUES ENT3 NULL GOOGLEDUPOND OLIVIER ENT1 NULL GOOGLELEGRAND ALAIN NULL NULL GOOGLE
D. Puthier Bases de données
Types de requêtesLe langage SQL (implémentation mySQL)
Création d’une base de données et de tablesImportation des données et indexation
Recherche d’informationModifications, procédures et administration
Les projectionsLes sélectionsJointure
Jointure interne
Si la jointure est effectuée sur un champs C, la valeur de C doit être nonnulle dans les deux tables.En règle générale on effectue un “equi-join” (table1.C = table2.C).
NOM PRENOM CODEMARTIN JEAN ENT2DURAND JACQUES ENT3DUPOND OLIVIER ENT1LEGRAND ALAIN NULL
CODE COMPAGNIEENT1 La PosteENT2 EDFENT3 SNCFENT4 SNCMNULL GOOGLE
⇓NOM PRENOM CODE CODE COMPAGNIEDUPOND OLIVIER ENT1 ENT1 La PosteMARTIN JEAN ENT2 ENT2 EDFDURAND JACQUES ENT3 ENT3 SNCF
NOM PRENOM CODE COMPAGNIEMARTIN JEAN ENT2 EDFDURAND JACQUES ENT3 SNCFDUPOND OLIVIER ENT1 La Poste
D. Puthier Bases de données
Types de requêtesLe langage SQL (implémentation mySQL)
Création d’une base de données et de tablesImportation des données et indexation
Recherche d’informationModifications, procédures et administration
Les projectionsLes sélectionsJointure
La jointure externe
Dans certaines circonstances, on souhaite toutes les lignes d’une tableparticulière qu’il y ait ou non correspondance avec l’autre table dejointure. Les colonnes pour lesquelles il n’y a pas de correspondancesont remplies avec la valeur NULL. Cette opération s’appelle une jointureexterne.
NOM PRENOM CODEMARTIN JEAN ENT2DURAND JACQUES ENT3DUPOND OLIVIER ENT1LEGRAND ALAIN NULL
CODE COMPAGNIEENT1 La PosteENT2 EDFENT3 SNCFENT4 SNCMNULL GOOGLE
⇓NOM PRENOM CODE COMPAGNIEMARTIN JEAN ENT2 EDFDUPOND OLIVIER ENT1 La PosteDURAND JACQUES ENT3 SNCFLEGRAND ALAIN NULL NULL
D. Puthier Bases de données
Types de requêtesLe langage SQL (implémentation mySQL)
Création d’une base de données et de tablesImportation des données et indexation
Recherche d’informationModifications, procédures et administration
SQL
SQL = Structured Query Language = language de gestion des BDrelationnelles que presque tous les SGBD comprennentinitialement développé par IBMSQL a été normalisé par l’ANSI (American National Standards Institute)et par l’ISO (International Organization for Standardization).Malgré la normalisation ISO, l’implémentation du SQL par les différentséditeurs de SGBD comporte des différences plus ou moins notablesconcernant la syntaxedialecte SQL ?
D. Puthier Bases de données
Types de requêtesLe langage SQL (implémentation mySQL)
Création d’une base de données et de tablesImportation des données et indexation
Recherche d’informationModifications, procédures et administration
Installation du serveur et du clientDémarrage du serveur et connectionCréation de la base de donnéesCréation des tablesTypage des attributsLes moteurs de stockage.
Sous UbuntuAvec le gestionnaire de paquets Synaptic.Ou avec la commande apt-get.
[user@machine] sudo apt-get install mysql-server-5.0 mysql-commonmysql-client-5.0 mysql-workbench-oss phpmyadmin
D. Puthier Bases de données
Types de requêtesLe langage SQL (implémentation mySQL)
Création d’une base de données et de tablesImportation des données et indexation
Recherche d’informationModifications, procédures et administration
Installation du serveur et du clientDémarrage du serveur et connectionCréation de la base de donnéesCréation des tablesTypage des attributsLes moteurs de stockage.
le serveur et le client SQL
Deux choses :Démarrer le serveur (si la base de donnée est sur ’localhost’)Pour se connecter au serveur on utilisera un client mySQL.
Le mot de passe peut être passé en argument.La base de donnée peut être passé en argument.
[user@machine] sudo /etc/init.d/mysql status[user@machine] sudo /etc/init.d/mysql start # si off[user@machine] mysql -u utilisateur -p’lePasswd’ -h leServeur laBaseDeDonnées[user@machine] mysql -u utilisateur -p’lePasswd’ -h leServeur[user@machine] # Connection ensembl[user@machine] mysql -u anonymous -h ensembldb.ensembl.org homo_sapiens_core_38_36[user@machine] mysql -u anonymous -h ensembldb.ensembl.org
D. Puthier Bases de données
Types de requêtesLe langage SQL (implémentation mySQL)
Création d’une base de données et de tablesImportation des données et indexation
Recherche d’informationModifications, procédures et administration
Installation du serveur et du clientDémarrage du serveur et connectionCréation de la base de donnéesCréation des tablesTypage des attributsLes moteurs de stockage.
Création de la base de données
Depuis le shell (mysqladmin)
Depuis le client SQL
[user@machine] sudo mysqladmin create PERSO;[user@machine] mysql -u utilisateur -p’lePasswd’ -h localhostmysql> SHOW DATABASES;+--------------------+| Database |+--------------------+| mysql || PERSO |+--------------------+mysql> CREATE DATABASE PERSONNEL;mysql> SHOW DATABASES;+--------------------+| Database |+--------------------+| mysql || PERSO || PERSONNEL |+--------------------+
D. Puthier Bases de données
Types de requêtesLe langage SQL (implémentation mySQL)
Création d’une base de données et de tablesImportation des données et indexation
Recherche d’informationModifications, procédures et administration
Installation du serveur et du clientDémarrage du serveur et connectionCréation de la base de donnéesCréation des tablesTypage des attributsLes moteurs de stockage.
Jeux de caractères et interclassement (collation)
MySQL supporte de nombreux jeux de caractères, dont ISO-8859-* etUTF-8 ainsi que des encodages plus “exotiques” : chinois, alphabetcyrillique (slave), japonais, coréens...
Pour chacun de ces jeux de caractères il peut exister desinterclassements différents (variante dans la manière d’effectuer les tris).
La collation peut être critique dans le cas de caractères accentués(diacritiques).
D. Puthier Bases de données
Types de requêtesLe langage SQL (implémentation mySQL)
Création d’une base de données et de tablesImportation des données et indexation
Recherche d’informationModifications, procédures et administration
Installation du serveur et du clientDémarrage du serveur et connectionCréation de la base de donnéesCréation des tablesTypage des attributsLes moteurs de stockage.
mysql> show character set;+----------+-----------------------------+---------------------+| Charset | Description | Default collation |+----------+-----------------------------+---------------------+| hp8 | HP West European | hp8_english_ci || koi8r | KOI8-R Relcom Russian | koi8r_general_ci || utf8 | UTF-8 Unicode | utf8_general_ci || latin1 | cp1252 West European | latin1_swedish_ci |...mysql> SHOW COLLATION;+----------------------+----------+-----+---------+----------+---------+| Collation | Charset | Id | Default | Compiled | Sortlen |+----------------------+----------+-----+---------+----------+---------+| latin1_german2_ci | latin1 | 31 | | Yes | 2 || latin1_bin | latin1 | 47 | | Yes | 1 || latin1_general_ci | latin1 | 48 | | Yes | 1 |....
mysql>CREATE DATABASE mydb CHARACTER SET utf8 COLLATE utf8_hungarian_ci;Query OK, 1 row affected (0.00 sec)
mysql> SHOW CREATE DATABASE mydb;+--------+---------------------------------------------------------------------------------------+|Database| Create Database |+--------+---------------------------------------------------------------------------------------+|mydb | CREATE DATABASE ‘mydb‘ /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_hungarian_ci*/|+--------+---------------------------------------------------------------------------------------+1 row in set (0.00 sec)
D. Puthier Bases de données
Types de requêtesLe langage SQL (implémentation mySQL)
Création d’une base de données et de tablesImportation des données et indexation
Recherche d’informationModifications, procédures et administration
Installation du serveur et du clientDémarrage du serveur et connectionCréation de la base de donnéesCréation des tablesTypage des attributsLes moteurs de stockage.
Création des tables
Commande principale : CREATE TABLE.
Cette fonction prendra en argument les noms des colonnes, leurs typeset leurs contraintes (ex : NOT NULL).
mysql> USE PERSONNEL;Database changedmysql> CREATE TABLE SALARIES (
-> salarieID MEDIUMINT UNSIGNED NOT NULL,-> nom VARCHAR(20) NOT NULL,-> prenom VARCHAR(20) NOT NULL,-> email VARCHAR(50) NOT NULL,-> anneeNaissance UNSIGNED SMALLINT,-> passwd VARCHAR(60) NOT NULL-> )-> ;
Query OK, 0 rows affected (0.01 sec)
D. Puthier Bases de données
Types de requêtesLe langage SQL (implémentation mySQL)
Création d’une base de données et de tablesImportation des données et indexation
Recherche d’informationModifications, procédures et administration
Installation du serveur et du clientDémarrage du serveur et connectionCréation de la base de donnéesCréation des tablesTypage des attributsLes moteurs de stockage.
Les clés d’une table
Il peut y avoir plusieurs clés dans une table, mais l’une d’entre elles doitêtre choisie comme clé primaire.
La clé primaire permet d’identifier une ligne de manière non ambiguë (Cf ;cours sur la modélisation).
mysql> -- AVEC UN CLEF NUMERIQUE AUTOINCREMENTEE.mysql> DROP TABLE SALARIES;Query OK, 0 rows affected (0.00 sec)mysql> CREATE TABLE SALARIES (
-> salarieID MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,-> nom VARCHAR(20) NOT NULL,-> prenom VARCHAR(20) NOT NULL,-> email VARCHAR(50) NOT NULL,-> anneeNaissance SMALLINT UNSIGNED,-> passwd VARCHAR(60) NOT NULL,-> PRIMARY KEY(salarieID));
Query OK, 0 rows affected (0.00 sec)
D. Puthier Bases de données
Types de requêtesLe langage SQL (implémentation mySQL)
Création d’une base de données et de tablesImportation des données et indexation
Recherche d’informationModifications, procédures et administration
Installation du serveur et du clientDémarrage du serveur et connectionCréation de la base de donnéesCréation des tablesTypage des attributsLes moteurs de stockage.
Les contraintes
On peut spécifier différentes contraintes au SGBD lors de la création destables.
Un attribut doit toujours avoir une valeur (NOT NULL).Un attribut (ou ensemble d’attributs) constitue(nt) la clé de la table(PRIMARY KEY).Un attribut dans une table est lié à la clé primaire d’une autre table(FOREIGN KEY).la valeur d’un attribut doit être unique au sein de la table (UNIQUE)un attribut ne peut prendre ses valeurs que parmi un ensemble pré-défini(type ENUM)
D. Puthier Bases de données
Types de requêtesLe langage SQL (implémentation mySQL)
Création d’une base de données et de tablesImportation des données et indexation
Recherche d’informationModifications, procédures et administration
Installation du serveur et du clientDémarrage du serveur et connectionCréation de la base de donnéesCréation des tablesTypage des attributsLes moteurs de stockage.
Informations sur les tables
mysql> SHOW TABLES;+---------------------+| Tables_in_PERSONNEL |+---------------------+| SALARIES |+---------------------+mysql> DESCRIBE SALARIES;+----------------+-----------------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+----------------+-----------------------+------+-----+---------+----------------+| salarieID | mediumint(8) unsigned | NO | PRI | NULL | auto_increment || nom | varchar(20) | NO | | NULL | || prenom | varchar(20) | NO | | NULL | || email | varchar(50) | NO | | NULL | || anneeNaissance | smallint(5) unsigned | YES | | NULL | || passwd | varchar(60) | NO | | NULL | |+----------------+-----------------------+------+-----+---------+----------------+mysql> SHOW CREATE TABLE SALARIES;
| SALARIES | CREATE TABLE ‘SALARIES‘ (‘salarieID‘ mediumint(8) unsigned NOT NULL auto_increment,‘nom‘ varchar(20) NOT NULL,‘prenom‘ varchar(20) NOT NULL,‘email‘ varchar(50) NOT NULL,‘anneeNaissance‘ smallint(5) unsigned default NULL,‘passwd‘ varchar(60) NOT NULL,PRIMARY KEY (‘salarieID‘)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |D. Puthier Bases de données
Types de requêtesLe langage SQL (implémentation mySQL)
Création d’une base de données et de tablesImportation des données et indexation
Recherche d’informationModifications, procédures et administration
Installation du serveur et du clientDémarrage du serveur et connectionCréation de la base de donnéesCréation des tablesTypage des attributsLes moteurs de stockage.
Typage des attributs/champs
le typages des attributs ne doit pas être fait à la légère.
Influe sur la vitesse des requêtes. On cherchera à avoir des attributs detailles limitées.
Améliore les opérations de recherche dans les tables.
D. Puthier Bases de données
Types de requêtesLe langage SQL (implémentation mySQL)
Création d’une base de données et de tablesImportation des données et indexation
Recherche d’informationModifications, procédures et administration
Installation du serveur et du clientDémarrage du serveur et connectionCréation de la base de donnéesCréation des tablesTypage des attributsLes moteurs de stockage.
Typages des numériques
Type Octets Valeur Minimum MaximumTINYINT 1 128 127TINYINT u 1 0 255SMALLINT 2 -32768 32767SMALLINT u 2 0 65535MEDIUMINT 3 -8388608 8388607MEDIUMINT u 3 0 16777215INT/INTEGER 4 -2147483648 2147483647INT/INTEGER u 4 0 4294967295BIGINT 8 -9223372036854775808 9223372036854775807BIGINT u 8 0 18446744073709551615FLOAT f,md 4 -3.402823466E+38 3.402823466E+38DOUBLE md 8 -1.7976931348623157E+308 1.7976931348623157E+308
u : UNSIGNEDf : Si UNSIGNED est activé, pas de valeurs négatives mais limite des nombres positifs identique.md : On rencontre aussi FLOAT(M,D)/DOUBLE(M,D). Un numérique sur M chiffres avec D décimales.
Plus d’informations : http ://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html
D. Puthier Bases de données
Types de requêtesLe langage SQL (implémentation mySQL)
Création d’une base de données et de tablesImportation des données et indexation
Recherche d’informationModifications, procédures et administration
Installation du serveur et du clientDémarrage du serveur et connectionCréation de la base de donnéesCréation des tablesTypage des attributsLes moteurs de stockage.
La contrainte ZEROFILL
Attention INTEGER(10) ne renseigne pas la taille du numérique mais lenombre de caractères à afficher.On peut l’utiliser conjointement avec ZEROFILL.
mysql> DROP TABLE IF EXISTS testType;mysql> CREATE TABLE testType (ID int ZEROFILL);mysql> INSERT testType (ID) VALUES (10);mysql> SELECT * from testType;+------------+| ID |+------------+| 0000000010 |+------------
mysql> DROP TABLE IF EXISTS testType;mysql> CREATE TABLE testType (ID int(5) ZEROFILL);mysql> INSERT testType (ID) values (10);mysql> select * from testType;+-------+| ID |+-------+| 00010 |+-------+
D. Puthier Bases de données
Types de requêtesLe langage SQL (implémentation mySQL)
Création d’une base de données et de tablesImportation des données et indexation
Recherche d’informationModifications, procédures et administration
Installation du serveur et du clientDémarrage du serveur et connectionCréation de la base de donnéesCréation des tablesTypage des attributsLes moteurs de stockage.
Les types ’caractères’ (string)
Type Minimum (Bytes)* Maximum (Bytes)CHAR(M) 0 255VARCHAR(M)a 0 255a
BINARY(M) b 0 255VARBINARY(M)b 0 255TINYBLOB 0 255TINYTEXT 0 255BLOB 0 65535TEXT 0 65535MEDIUMBLOB 0 16777215MEDIUMTEXT 0 16777215LONGBLOB 0 4294967295LONGTEXT 0 4294967295ENUM c - -
* : en utf8 un caractère est codé sur 1 octet dans la pluspart des cas.a : 0 to 65535 depuis MySQL 5.0.3b : BINARY signifie que les valeurs sont classées et triées en tenant compte de la casse, suivant l’ordre des caractères ASCII(http ://fr.wikipedia.org/wiki/ASCII).c : Une énumeration. Jusqu’à 65535 valeurs possibles.Plus d’informations : http ://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html
D. Puthier Bases de données
Types de requêtesLe langage SQL (implémentation mySQL)
Création d’une base de données et de tablesImportation des données et indexation
Recherche d’informationModifications, procédures et administration
Installation du serveur et du clientDémarrage du serveur et connectionCréation de la base de donnéesCréation des tablesTypage des attributsLes moteurs de stockage.
Les types temporelsmySQL propose un typage pour les valeurs temporelles
Type Octets Caractéristiques/FormatDATETIME 8 date et heure, YYYY-MM-DD HH :MM :SS (8 octets)DATE 3 date YYYY-MM-DDTIME 3 HH :MM :SSYEAR 1 1901 à 2155TIMESTAMP 4 stocke les dates depuis 1970 jusqu’à 2037 (4 octets)
D. Puthier Bases de données
Types de requêtesLe langage SQL (implémentation mySQL)
Création d’une base de données et de tablesImportation des données et indexation
Recherche d’informationModifications, procédures et administration
Installation du serveur et du clientDémarrage du serveur et connectionCréation de la base de donnéesCréation des tablesTypage des attributsLes moteurs de stockage.
Les moteurs de stockage : les tables MyISAM
Moteur par défaut.
Extrêmement rapides.
Crée trois fichiers par tables : [“.frm” (format des tables), “.MYD”(données), “.MYI” (index)].
Lorsque les opérations sont essentiellement de type SELECT et INSERT.
Nombre limite de lignes 1.844E+19
Très facile à administrer : possibilité de recopier directement les fichiersd’un serveur vers un autre.
mysql> -- ON peut spécifier le type de tablemysql> -- avec le mot clé ENGINE.mysql> CREATE TABLE t (i INT) ENGINE = MYISAM;
D. Puthier Bases de données
Types de requêtesLe langage SQL (implémentation mySQL)
Création d’une base de données et de tablesImportation des données et indexation
Recherche d’informationModifications, procédures et administration
Installation du serveur et du clientDémarrage du serveur et connectionCréation de la base de donnéesCréation des tablesTypage des attributsLes moteurs de stockage.
Les moteurs de stockage : les tables MyISAM
LE VIDEOCLUB en MyISAM.
mysql> DROP DATABASE IF EXISTS VIDEOCLUB;mysql> CREATE DATABASE VIDEOCLUB;mysql> USE VIDEOCLUB;mysql> CREATE TABLE FILM ( filmID INT(20) NOT NULL AUTO_INCREMENT,
-> genreID INT(10) NOT NULL,-> title VARCHAR(32) NOT NULL,-> PRIMARY KEY (filmID)) ENGINE=MyISAM;
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TABLE STYLE (-> genreID INT(10) NOT NULL AUTO_INCREMENT,-> genre VARCHAR(50) NOT NULL,-> PRIMARY KEY (genreID) ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO FILM (genreID, title) VALUES (1, ’Freaks’);Query OK, 1 row affected (0.00 sec)
D. Puthier Bases de données
Types de requêtesLe langage SQL (implémentation mySQL)
Création d’une base de données et de tablesImportation des données et indexation
Recherche d’informationModifications, procédures et administration
Installation du serveur et du clientDémarrage du serveur et connectionCréation de la base de donnéesCréation des tablesTypage des attributsLes moteurs de stockage.
Les moteurs de stockage : les tables InnoDB
Moteur transactionnel.Contrôle la cohérence des données à chaque modification (via les clésprimaires et étrangères notamment).Moteur “ACID compliant”
Atomicité : la transaction est accepté si toutes les actions sont validées,sinon retour à l’état initial.Cohérence : les changements induits par une transaction doiventimpérativement préserver la cohérence de la base de donnéesIsolation : Une transaction n’est pas affectée par le traitement des autrestransactions.Durabilité : les effets de la transaction sont durables (les résultats sont nonvolatiles, ils sont stockés physiquement).
mysql> -- ON peut spécifier le type de tablemysql> -- avec le mot clé ENGINE.mysql> CREATE TABLE t (i INT) ENGINE = InnoDB;
D. Puthier Bases de données
Types de requêtesLe langage SQL (implémentation mySQL)
Création d’une base de données et de tablesImportation des données et indexation
Recherche d’informationModifications, procédures et administration
Installation du serveur et du clientDémarrage du serveur et connectionCréation de la base de donnéesCréation des tablesTypage des attributsLes moteurs de stockage.
Les moteurs de stockage : les tables InnoDB
LE VIDEOCLUB en InnoDB.mysql> DROP DATABASE IF EXISTS VIDEOCLUB;mysql> CREATE DATABASE VIDEOCLUB;mysql> USE VIDEOCLUB;mysql> CREATE TABLE STYLE ( genreID INT(10) NOT NULL AUTO_INCREMENT,
-> genre VARCHAR(50) NOT NULL,-> PRIMARY KEY (genreID)-> ) ENGINE=InnoDB;
mysql> CREATE TABLE FILM ( filmID INT(20) NOT NULL AUTO_INCREMENT,-> genreID INT(10) NOT NULL,-> title VARCHAR(32) NOT NULL,-> PRIMARY KEY (filmID),-> FOREIGN KEY(genreID)-> REFERENCES STYLE(genreID)-> ON DELETE NO ACTION-> ) ENGINE=InnoDB;
mysql> INSERT INTO FILM (genreID, title) VALUES (1, ’Freaks’);ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
mysql> INSERT INTO STYLE (genre) VALUES (’Drame’);mysql> INSERT INTO FILM (genreID, title) VALUES (1, ’Freaks’);Query OK, 1 row affected (0.01 sec)
D. Puthier Bases de données
Types de requêtesLe langage SQL (implémentation mySQL)
Création d’une base de données et de tablesImportation des données et indexation
Recherche d’informationModifications, procédures et administration
Installation du serveur et du clientDémarrage du serveur et connectionCréation de la base de donnéesCréation des tablesTypage des attributsLes moteurs de stockage.
Les moteurs de stockage : les tables InnoDB
Gestion des contraintes relationnelles.CASCADE : en cas de délétion ou de mise à jour d’une table parente,délétion des lignes correspondantes dans les tables filles.SET NULL : en cas de délétion ou de mise à jour d’une table parente, leslignes correspondantes dans les colonnes correspondantes des tablesfilles sont mises à NULL.RESTRICT/NO ACTION : en cas de délétion ou de mise à jour d’une tableparente, refuser.
D. Puthier Bases de données
Types de requêtesLe langage SQL (implémentation mySQL)
Création d’une base de données et de tablesImportation des données et indexation
Recherche d’informationModifications, procédures et administration
Indexation
Importation des données
Objectif : remplir les tables
Généralement les données sont obtenues sous forme d’un fichier texte(tabulé idéalement).
Souvent nécéssité de re-formater les données (Shell, Perl ou awk).
Les valeurs manquantes doivent être remplacées par ’\N’ (attention pas’\n’).
Si un champ AUTO_INCREMENT existe, remplacer cette colonne dans lefichier par des zéros (l’incrémentation se fera lors de la lecture).
D. Puthier Bases de données
Types de requêtesLe langage SQL (implémentation mySQL)
Création d’une base de données et de tablesImportation des données et indexation
Recherche d’informationModifications, procédures et administration
Indexation
mysqlimport
Depuis le shell. Le fichier importé porte le nom de la table.
[user@machine] echo -e "0\tDRAME\n0\tDocumentaire\n0\tEpouvante"0 Drame0 Documentaire0 Epouvante[user@machine] echo -e "0\tDRAME\n0\tDocumentaire\n0\tEpouvante" > STYLE.txt[user@machine] mysqlimport --local -u root VIDEOCLUB STYLE.txtVIDEOCLUB.STYLE: Records: 3 Deleted: 0 Skipped: 0 Warnings: 0
#Ou eventuellement[user@machine] mysqlimport --local -u root VIDEOCLUB STYLE.txt > warnings.txt[user@machine] cat warnings.txtVIDEOCLUB.STYLE: Records: 3 Deleted: 0 Skipped: 0 Warnings: 0
D. Puthier Bases de données
Types de requêtesLe langage SQL (implémentation mySQL)
Création d’une base de données et de tablesImportation des données et indexation
Recherche d’informationModifications, procédures et administration
Indexation
Remarque
NB : il est possible de dialoguer avec la base depuis le shell.
[user@machine] echo "SHOW TABLES;" | mysql -u root VIDEOCLUBTables_in_VIDEOCLUBFILMSTYLE
[user@machine] echo "SHOW TABLES;" > script.sql[user@machine] mysql -u root VIDEOCLUB < script.sqlTables_in_VIDEOCLUBFILMSTYLE
D. Puthier Bases de données
Types de requêtesLe langage SQL (implémentation mySQL)
Création d’une base de données et de tablesImportation des données et indexation
Recherche d’informationModifications, procédures et administration
Indexation
Importation depuis le client mySQL
En utilisant la commande LOAD.
Le nom du fichier est sans importance.
mysql> USE VIDEOCLUB;mysql> DELETE FROM STYLE;Query OK, 12 rows affected (0.04 sec)mysql> LOAD DATA LOCAL INFILE ’/home/puthier/STYLE.txt’ INTO TABLE STYLE;Query OK, 3 rows affected (0.01 sec)Records: 3 Deleted: 0 Skipped: 0 Warnings: 0
D. Puthier Bases de données
Types de requêtesLe langage SQL (implémentation mySQL)
Création d’une base de données et de tablesImportation des données et indexation
Recherche d’informationModifications, procédures et administration
Indexation
La commande INSERT
Permet d’ajouter une ligne dans une table.
INSERT INTO table (nomattribut,...) VALUE (EXPRESSION,...);
D. Puthier Bases de données
Types de requêtesLe langage SQL (implémentation mySQL)
Création d’une base de données et de tablesImportation des données et indexation
Recherche d’informationModifications, procédures et administration
Indexation
Indexation
Les index permettent de trouver rapidement les données contenues dansune table.
Un index peut-être considérer comme l’index d’un livre.
Evite de parcourir tout le livre ( ’table scan’).
L’index indique à quel adresse disque se trouve l’enregistrement cherché.
Plus les termes recherchés sont courts plus les pages d’index sontlimités et plus la recherche est rapide.
D. Puthier Bases de données
Types de requêtesLe langage SQL (implémentation mySQL)
Création d’une base de données et de tablesImportation des données et indexation
Recherche d’informationModifications, procédures et administration
Indexation
La commande INSERT
Permet d’ajouter une ligne dans une table.
INSERT INTO table (nomattribut,...) VALUE (EXPRESSION,...);
D. Puthier Bases de données
Types de requêtesLe langage SQL (implémentation mySQL)
Création d’une base de données et de tablesImportation des données et indexation
Recherche d’informationModifications, procédures et administration
La commande SELECTFonctions d’aggrégationLes jointures
La commande SELECT
Commande la plus courantePermet de rechercher dans une table des lignes satisfaisant un ouplusieurs critères de sélection
mysql> -- On peut faire plus compliqué mais c’est un début.mysql> SELECT 1 + 1;+-------+| 1 + 1 |+-------+| 2 |+-------+
D. Puthier Bases de données
Types de requêtesLe langage SQL (implémentation mySQL)
Création d’une base de données et de tablesImportation des données et indexation
Recherche d’informationModifications, procédures et administration
La commande SELECTFonctions d’aggrégationLes jointures
Selection d’attributs
On renseignera les attributs à visualiser :
mysql> USE VIDEOCLUB;mysql> SELECT * FROM STYLE;+---------+--------------+| genreID | genre |+---------+--------------+| 1 | Drame || 2 | Action || 3 | Policier || 4 | Documentaire |+---------+--------------+
mysql> SELECT genre FROM STYLE;+--------------+| genre |+--------------+| Drame || Action || Policier || Documentaire |+--------------+
D. Puthier Bases de données
Types de requêtesLe langage SQL (implémentation mySQL)
Création d’une base de données et de tablesImportation des données et indexation
Recherche d’informationModifications, procédures et administration
La commande SELECTFonctions d’aggrégationLes jointures
Selection de ligne
On utilisera fréquemment la clause WHERE.
On utilisera conjointement des opérateurs de comparaison.
Possibilité d’utiliser des opérateurs logiques (AND, OR).
mysql> SELECT * FROM STYLE WHERE genreID=2;+---------+-----------+| genreID | genre |+---------+-----------+| 2 | Action |+---------+-----------+
D. Puthier Bases de données
Types de requêtesLe langage SQL (implémentation mySQL)
Création d’une base de données et de tablesImportation des données et indexation
Recherche d’informationModifications, procédures et administration
La commande SELECTFonctions d’aggrégationLes jointures
Opérateurs/Fonctions de comparaison
BETWEEN ... AND ... Teste si une valeur est dans un interval.COALESCE() Renvoie le premier élément non NULL.> Supérieur>= Supérieur ou égal.= Opérateur d’égalité.<=> Opérateur spaceship. Opérateur d’égalité. NULL est égal à NULLGREATEST(a,b,c,d,...) Renvoie le plus grand élément d’une liste (plusieurs éléments).MAX() Renvoie le plus grand élément (associé à un champ).IN(a,b,c,d,...) Teste si l’une des valeurs existe.IS NOT NULL Teste si la valeur est NULLLEAST(a,b,c,d,...) Renvoie le plus petit élément d’une liste (plusieurs éléments).MIN() Renvoie le plus petit élément (associé à un champ).LIKE Recherche de motifs.NOT BETWEEN ... AND ... Teste si une valeur est dans un interval.!=, <> Opérateurs d’inégalité.NOT IN(a,b,c,d,...) Teste si l’une des valeurs existe.NOT LIKE Recherche de motifs.
D. Puthier Bases de données
Types de requêtesLe langage SQL (implémentation mySQL)
Création d’une base de données et de tablesImportation des données et indexation
Recherche d’informationModifications, procédures et administration
La commande SELECTFonctions d’aggrégationLes jointures
L’opérateur LIKE
mysql> SELECT * FROM STYLE WHERE genre NOT LIKE ’% %’;+---------+--------------+| genreID | genre |+---------+--------------+| 1 | Drame || 2 | Action || 3 | Policier || 4 | Documentaire |+---------+--------------+
mysql> SELECT * FROM STYLE WHERE genre LIKE "%o%";+---------+--------------+| genreID | genre |+---------+--------------+| 2 | Action || 3 | Policier || 4 | Documentaire |+---------+--------------+
mysql> SELECT * FROM STYLE WHERE genre LIKE "_o%";+---------+--------------+| genreID | genre |+---------+--------------+| 3 | Policier || 4 | Documentaire |+---------+--------------+
mysql> SELECT * FROM STYLE WHERE genre LIKE "_o";Empty set (0.00 sec)
D. Puthier Bases de données
Types de requêtesLe langage SQL (implémentation mySQL)
Création d’une base de données et de tablesImportation des données et indexation
Recherche d’informationModifications, procédures et administration
La commande SELECTFonctions d’aggrégationLes jointures
Expressions régulières
La selection peut se faire sur une expression régulière.
mysql> SELECT * FROM STYLE WHERE genre REGEXP ’(r|e).$’;+---------+--------------+| genreID | genre |+---------+--------------+| 3 | Policier || 4 | Documentaire |+---------+--------------+
D. Puthier Bases de données
Types de requêtesLe langage SQL (implémentation mySQL)
Création d’une base de données et de tablesImportation des données et indexation
Recherche d’informationModifications, procédures et administration
La commande SELECTFonctions d’aggrégationLes jointures
LIMIT, ORDER BY, DISTINCT
Lorsque le nombre de lignes de la table est trop important on utiliseraLIMIT.
ORDER BY permet de trier les enregistrements selon un attribut. Le tripeut être croissant (ASC, par défaut) ou décroissant (DESC).
DISTINCT renvoie des enregistrements uniques (élimine les doublons).
mysql> SELECT * FROM STYLE-> WHERE genre NOT LIKE ’% %’-> ORDER BY genre DESC-> LIMIT 2;
+---------+-----------+| genreID | genre |+---------+-----------+| 3 | Policier || 1 | Drame |+---------+-----------+2 rows in set (0.00 sec)
D. Puthier Bases de données
Types de requêtesLe langage SQL (implémentation mySQL)
Création d’une base de données et de tablesImportation des données et indexation
Recherche d’informationModifications, procédures et administration
La commande SELECTFonctions d’aggrégationLes jointures
GROUP BY permet de d’appliquer une fonction d’aggrégation (ex :’count’) en fonction des classes d’un attribut.HAVING permet de restreindre la selection opérée par GROUP BY.Nécessite souvent d’utiliser un alias (avec la fonction AS)
mysql> insert into FILM(genreID,title) VALUES (1,’Sur la route de Madison’);mysql> insert into FILM(genreID,title) VALUES (3,’Créance de sang’);mysql> insert into FILM(genreID,title) VALUES (2,"Pulp fiction");mysql> insert into FILM(genreID,title) VALUES (2,"La ligne rouge");mysql> SELECT genreID,count(*) FROM FILM GROUP BY genreID;+---------+----------+| genreID | count(*) |+---------+----------+| 1 | 2 || 2 | 2 || 3 | 1 |+---------+----------+mysql> SELECT genreID,count(*) AS C FROM FILM GROUP BY genreID HAVING genreID > 1 ;+---------+---+| genreID | C |+---------+---+| 2 | 2 || 3 | 1 |+---------+---+
D. Puthier Bases de données
Types de requêtesLe langage SQL (implémentation mySQL)
Création d’une base de données et de tablesImportation des données et indexation
Recherche d’informationModifications, procédures et administration
La commande SELECTFonctions d’aggrégationLes jointures
Les selection imbriquées
Il ne faut pas en abuser.
Dans certain cas elles peuvent être remplacés par des jointures quipourront parfois s’avérer plus rapide.
mysql> SELECT genreID,title-> FROM FILM-> WHERE genreID IN ( SELECT genreID-> FROM STYLE-> WHERE genre-> LIKE "D%") ;
+---------+-------------------------+| genreID | title |+---------+-------------------------+| 1 | Freaks || 1 | Sur la route de Madison |+---------+-------------------------+2 rows in set (0.00 sec)
D. Puthier Bases de données
Types de requêtesLe langage SQL (implémentation mySQL)
Création d’une base de données et de tablesImportation des données et indexation
Recherche d’informationModifications, procédures et administration
La commande SELECTFonctions d’aggrégationLes jointures
Le cas des valeurs ’NULL’
Valeur à NULL = valeur inconnue = valeur non attribuée.Toute opération ou fonction appliquée à NULL donne pour résultat NULL.Le résultat d’une comparaison avec NULL peut varier avec l’opérateur.
mysql> SELECT 1 > NULL;+----------+| 1 > NULL |+----------+| NULL |+----------+mysql> SELECT NULL= NULL;+------------+| NULL=NULL |+------------+| NULL |+------------+mysql> SELECT NULL <=> NULL;+---------------+| NULL <=> NULL |+---------------+| 1 |+---------------+
mysql> ALTER TABLE FILM MODIFY genreID INT(10); -- != NOT NULLmysql> INSERT INTO FILM(genreID,title) VALUES (NULL,"The Shinning");mysql> SELECT * FROM FILM WHERE genreID=NULL;Empty set (0.00 sec)mysql> SELECT * FROM FILM WHERE genreID IS NULL;+--------+---------+--------------+| filmID | genreID | title |+--------+---------+--------------+| 6 | NULL | The Shinning |+--------+---------+--------------+
D. Puthier Bases de données
Types de requêtesLe langage SQL (implémentation mySQL)
Création d’une base de données et de tablesImportation des données et indexation
Recherche d’informationModifications, procédures et administration
La commande SELECTFonctions d’aggrégationLes jointures
Fonctions d’aggrégation
Ces fonctions opèrent sur un ensemble de valeurs.
AVG() Calcul la moyenne.MAX() Renvoie la plus grande valeur.MIN() Renvoie la plus petite valeur.STD() Renvoie l’écart-type.SUM() Renvoie la somme.VARIANCE() Renvoie la variance.
D. Puthier Bases de données
Types de requêtesLe langage SQL (implémentation mySQL)
Création d’une base de données et de tablesImportation des données et indexation
Recherche d’informationModifications, procédures et administration
La commande SELECTFonctions d’aggrégationLes jointures
Fonctions pour le traîtement de chaînes de caractères.
mysql> SELECT CONCAT("A","B");+-----------------+| CONCAT("A","B") |+-----------------+| AB |+-----------------+
mysql> SELECT SUBSTRING(UCASE("Stanley Kubrick"),9,7) AS NAME;+---------+| NAME |+---------+| KUBRICK |+---------+
mysql> SELECT LEFT(LCASE("Stanley Kubrick"),3);+----------------------------------+| LEFT(LCASE("Stanley Kubrick"),3) |+----------------------------------+| sta |+----------------------------------+
mysql> SELECT locate(" ", "Stanley Kubrick");+--------------------------------+| locate(" ", "Stanley Kubrick") |+--------------------------------+| 8 |+--------------------------------+mysql> SELECT LPAD(’hi’,5,’ABCDEE’);+-----------------------+| LPAD(’hi’,5,’ABCDEE’) |+-----------------------+| ABChi |+-----------------------+
Pour plus d’informations :http ://dev.mysql.com/doc/refman/5.0/en/string-functions.html
D. Puthier Bases de données
Types de requêtesLe langage SQL (implémentation mySQL)
Création d’une base de données et de tablesImportation des données et indexation
Recherche d’informationModifications, procédures et administration
La commande SELECTFonctions d’aggrégationLes jointures
Jointures croisées : plusieurs syntaxes
mysql> SELECT * FROM FILM,STYLE LIMIT 7;+--------+---------+-------------------------+---------+--------------+| filmID | genreID | title | genreID | genre |+--------+---------+-------------------------+---------+--------------+| 1 | 1 | Freaks | 1 | Drame || 1 | 1 | Freaks | 2 | Action || 1 | 1 | Freaks | 3 | Policier || 1 | 1 | Freaks | 4 | Documentaire || 2 | 1 | Sur la route de Madison | 1 | Drame || 2 | 1 | Sur la route de Madison | 2 | Action || 2 | 1 | Sur la route de Madison | 3 | Policier |+--------+---------+-------------------------+---------+--------------+
mysql> SELECT * FROM FILM CROSS JOIN STYLE LIMIT 7;+--------+---------+-------------------------+---------+--------------+| filmID | genreID | title | genreID | genre |+--------+---------+-------------------------+---------+--------------+| 1 | 1 | Freaks | 1 | Drame || 1 | 1 | Freaks | 2 | Action || 1 | 1 | Freaks | 3 | Policier || 1 | 1 | Freaks | 4 | Documentaire || 2 | 1 | Sur la route de Madison | 1 | Drame || 2 | 1 | Sur la route de Madison | 2 | Action || 2 | 1 | Sur la route de Madison | 3 | Policier |+--------+---------+-------------------------+---------+--------------+
D. Puthier Bases de données
Types de requêtesLe langage SQL (implémentation mySQL)
Création d’une base de données et de tablesImportation des données et indexation
Recherche d’informationModifications, procédures et administration
La commande SELECTFonctions d’aggrégationLes jointures
Jointures internes : plusieurs syntaxes
mysql> SELECT * FROM FILM,STYLE WHERE FILM.genreID=STYLE.genreID LIMIT 3 ;+--------+---------+-------------------------+---------+-----------+| filmID | genreID | title | genreID | genre |+--------+---------+-------------------------+---------+-----------+| 1 | 1 | Freaks | 1 | Drame || 2 | 1 | Sur la route de Madison | 1 | Drame || 4 | 2 | Pulp fiction | 2 | Action |+--------+---------+-------------------------+---------+-----------+
mysql> SELECT * FROM FILM INNER JOIN STYLE USING(genreID) LIMIT 3 ;+---------+--------+-------------------------+-----------+| genreID | filmID | title | genre |+---------+--------+-------------------------+-----------+| 1 | 1 | Freaks | Drame || 1 | 2 | Sur la route de Madison | Drame || 2 | 4 | Pulp fiction | Action |+---------+--------+-------------------------+-----------+
mysql> SELECT * FROM FILM INNER JOIN STYLE ON FILM.genreID=STYLE.genreID LIMIT 3;+--------+---------+-------------------------+---------+-----------+| filmID | genreID | title | genreID | genre |+--------+---------+-------------------------+---------+-----------+| 1 | 1 | Freaks | 1 | Drame || 2 | 1 | Sur la route de Madison | 1 | Drame || 4 | 2 | Pulp fiction | 2 | Action |+--------+---------+-------------------------+---------+-----------+
D. Puthier Bases de données
Types de requêtesLe langage SQL (implémentation mySQL)
Création d’une base de données et de tablesImportation des données et indexation
Recherche d’informationModifications, procédures et administration
La commande SELECTFonctions d’aggrégationLes jointures
Jointures externes : à gauche ou à droite
mysql> SELECT * FROM FILM LEFT OUTER JOIN STYLE using(genreID);+---------+--------+-------------------------+-----------+| genreID | filmID | title | genre |+---------+--------+-------------------------+-----------+| 1 | 1 | Freaks | Drame || 1 | 2 | Sur la route de Madison | Drame || 3 | 3 | Créance de sang | Policier || 2 | 4 | Pulp fiction | Action || 2 | 5 | La ligne rouge | Action || NULL | 6 | The Shinning | NULL |+---------+--------+-------------------------+-----------+
mysql> SELECT * FROM FILM RIGHT OUTER JOIN STYLE using(genreID);+---------+--------------+--------+-------------------------+| genreID | genre | filmID | title |+---------+--------------+--------+-------------------------+| 1 | Drame | 1 | Freaks || 1 | Drame | 2 | Sur la route de Madison || 2 | Action | 4 | Pulp fiction || 2 | Action | 5 | La ligne rouge || 3 | Policier | 3 | Créance de sang || 4 | Documentaire | NULL | NULL |+---------+--------------+--------+-------------------------+
D. Puthier Bases de données
Types de requêtesLe langage SQL (implémentation mySQL)
Création d’une base de données et de tablesImportation des données et indexation
Recherche d’informationModifications, procédures et administration
La fonction ALTER TABLELa fonction UPDATELes fonctions DELETE et DROPLes procédures stockéesNotions (très limitées) d’administration.
La fonction ALTER TABLE
ALTER TABLE permet de changer la structure d’une table existante.
Permet d’ajouter ou de supprimer des colonnes ou des index.
Permet de changer le type des colonnes existantes.
Permet de renommer les colonnes ou la table elle-même.
D. Puthier Bases de données
Types de requêtesLe langage SQL (implémentation mySQL)
Création d’une base de données et de tablesImportation des données et indexation
Recherche d’informationModifications, procédures et administration
La fonction ALTER TABLELa fonction UPDATELes fonctions DELETE et DROPLes procédures stockéesNotions (très limitées) d’administration.
La fonction ALTER TABLE
mysql> ALTER TABLE FILM MODIFY title TEXT NOT NULL;mysql> ALTER TABLE FILM RENAME film;mysql> ALTER TABLE film ADD dateReception TIMESTAMP;mysql> ALTER TABLE film DROP COLUMN dateReception;
D. Puthier Bases de données
Types de requêtesLe langage SQL (implémentation mySQL)
Création d’une base de données et de tablesImportation des données et indexation
Recherche d’informationModifications, procédures et administration
La fonction ALTER TABLELa fonction UPDATELes fonctions DELETE et DROPLes procédures stockéesNotions (très limitées) d’administration.
Permet de modifier un enregistrement.
mysql> UPDATE STYLE SET genre="Thriller" where genre="Policier";Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from STYLE;+---------+--------------+| genreID | genre |+---------+--------------+| 1 | Drame || 2 | Action || 3 | Thriller || 4 | Documentaire |+---------+--------------+
D. Puthier Bases de données
Types de requêtesLe langage SQL (implémentation mySQL)
Création d’une base de données et de tablesImportation des données et indexation
Recherche d’informationModifications, procédures et administration
La fonction ALTER TABLELa fonction UPDATELes fonctions DELETE et DROPLes procédures stockéesNotions (très limitées) d’administration.
mysql> --Destruction d’une ligne dans une tablemysql> DELETE FROM GENE WHERE cytoband = ’8q24’;
mysql> --Destruction d’une tablemysql> DROP TABLE GENE;
mysql> --Destruction d’une basemysql> DROP DATABASE MICROARRAY;
D. Puthier Bases de données
Types de requêtesLe langage SQL (implémentation mySQL)
Création d’une base de données et de tablesImportation des données et indexation
Recherche d’informationModifications, procédures et administration
La fonction ALTER TABLELa fonction UPDATELes fonctions DELETE et DROPLes procédures stockéesNotions (très limitées) d’administration.
Procédures stockées
Nouvelles fonctionnalités apparue avec mySQL 5.0.Une procédure stockées est un jeu de commandes SQL qui réside sur leserveur. Une fois qu’elle sont enregistrées, les clients n’ont pas besoin desoumettre chaque commande individuellement, mais peuvent les lancerd’un seul coup.Les procédures stockées fournissent un gain de performances, car moinsd’informations sont échangées entre le serveur et le clientPermet de distinguer complètement le travail effectué par la BD etl’application (ex php, Java, Perl,...).Décomposition du problème.En échange, cela augmente la charge du serveur, car ce dernier doitréaliser plus de travailLimite le code SQL dans l’application.
D. Puthier Bases de données
Types de requêtesLe langage SQL (implémentation mySQL)
Création d’une base de données et de tablesImportation des données et indexation
Recherche d’informationModifications, procédures et administration
La fonction ALTER TABLELa fonction UPDATELes fonctions DELETE et DROPLes procédures stockéesNotions (très limitées) d’administration.
Procédures stockées
mysql> DELIMITER $$mysql> CREATE PROCEDURE getFilms ()
-> BEGIN-> SELECT genre,title FROM film JOIN STYLE using(genreID);-> END$$
Query OK, 0 rows affected (0.00 sec)mysql> DELIMITER ;
mysql> CALL getFilms();+-----------+-------------------------+| styleName | name |+-----------+-------------------------+| Drame | Freaks || Drame | Sur la route de Madison || Action | Pulp fiction || Action | La ligne rouge || Thriller | Créance de sang |+-----------+-------------------------+
D. Puthier Bases de données
Types de requêtesLe langage SQL (implémentation mySQL)
Création d’une base de données et de tablesImportation des données et indexation
Recherche d’informationModifications, procédures et administration
La fonction ALTER TABLELa fonction UPDATELes fonctions DELETE et DROPLes procédures stockéesNotions (très limitées) d’administration.
Le mot de passe root
[user@machine] # Définir un mot de passe administrateur pour ’root’[user@machine] mysqladmin -u root password ’ThePasswd’[user@machine] mysql -u root -p’lepasswd’Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 18Server version: 5.0.51a-3ubuntu5.1 (Ubuntu)
Type ’help;’ or ’\h’ for help. Type ’\c’ to clear the buffer.
mysql> quitBye
[user@machine] mysql -u root -p’ThePasswd’mysql> -- Depuis la console mySQLmysql> SET PASSWORD FOR root= PASSWORD(’TheNewPasswd’);
D. Puthier Bases de données
Types de requêtesLe langage SQL (implémentation mySQL)
Création d’une base de données et de tablesImportation des données et indexation
Recherche d’informationModifications, procédures et administration
La fonction ALTER TABLELa fonction UPDATELes fonctions DELETE et DROPLes procédures stockéesNotions (très limitées) d’administration.
Le mot de passe root
mysql> -- Depuis la console mySQLmysql> SET PASSWORD FOR root= PASSWORD(’lepasswd’);
D. Puthier Bases de données
Types de requêtesLe langage SQL (implémentation mySQL)
Création d’une base de données et de tablesImportation des données et indexation
Recherche d’informationModifications, procédures et administration
La fonction ALTER TABLELa fonction UPDATELes fonctions DELETE et DROPLes procédures stockéesNotions (très limitées) d’administration.
Créer un utilisateur
mysql> GRANT ALL PRIVILEGESON TheDataBase.*TO TheUserName@localhostIDENTIFIED BY ’ThePasswdFromUser’;
D. Puthier Bases de données
Types de requêtesLe langage SQL (implémentation mySQL)
Création d’une base de données et de tablesImportation des données et indexation
Recherche d’informationModifications, procédures et administration
La fonction ALTER TABLELa fonction UPDATELes fonctions DELETE et DROPLes procédures stockéesNotions (très limitées) d’administration.
Les principaux droits
ALL [PRIVILEGES] Tous les droits sauf WITH GRANT OPTION.ALTER Autorise l’utilisation de ALTER TABLE.CREATE Autorise l’utilisation de CREATE TABLE.CREATE TEMPORARY TABLES Autorise l’utilisation de CREATE TEMPORARY TABLE.DELETE Autorise l’utilisation de DELETE.DROP Autorise l’utilisation de DROP TABLE.EXECUTE Autorise l’utilisateur à exécuter des procédures stockées (pour MySQL 5.0).FILE Autorise l’utilisation de SELECT ... INTO OUTFILE et LOAD DATA INFILE.INDEX Autorise l’utilisation de CREATE INDEX et DROP INDEX.INSERT Autorise l’utilisation de INSERT.SELECT Autorise l’utilisation de SELECT.SHOW DATABASES SHOW DATABASES affiche toutes les bases de donné. UPDATE Autorise l’utilisation de UPDATE.GRANT OPTION Synonyme pour WITH GRANT OPTION.
D. Puthier Bases de données
Types de requêtesLe langage SQL (implémentation mySQL)
Création d’une base de données et de tablesImportation des données et indexation
Recherche d’informationModifications, procédures et administration
La fonction ALTER TABLELa fonction UPDATELes fonctions DELETE et DROPLes procédures stockéesNotions (très limitées) d’administration.
Pour en savoir plus :Le site mySQL : http ://dev.mysql.com/doc/refman/5.0/fr/index.html
D. Puthier Bases de données
Types de requêtesLe langage SQL (implémentation mySQL)
Création d’une base de données et de tablesImportation des données et indexation
Recherche d’informationModifications, procédures et administration
La fonction ALTER TABLELa fonction UPDATELes fonctions DELETE et DROPLes procédures stockéesNotions (très limitées) d’administration.
MERCI POUR VOTRE ATTENTION
D. Puthier Bases de données