| Les deux révisions précédentesRévision précédenteProchaine révision | Révision précédente |
| store_data_safely:an_intro_to_sqlite_suite [2013/10/31 09:52] – [15] auntiee | store_data_safely:an_intro_to_sqlite_suite [2013/10/31 11:11] (Version actuelle) – [25] auntiee |
|---|
| **The correct syntax for the foreign key specification can be rather complicated. With a little practice, this is straightforward; the following will fix the situation: | **The correct syntax for the foreign key specification can be rather complicated. With a little practice, this is straightforward; the following will fix the situation: |
| |
| DROP TABLE "Taddress" ; delete the existing table and data... | DROP TABLE "Taddress"; delete the existing table and data... |
| |
| CREATE TABLE "Taddress" ("student_id" CHAR NOT NULL ,"line_no" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL ,"address" CHAR, FOREIGN KEY("student_id") REFERENCES Tstudent ("student_id") ON DELETE CASCADE); | CREATE TABLE "Taddress" ("student_id" CHAR NOT NULL ,"line_no" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL ,"address" CHAR, FOREIGN KEY("student_id") REFERENCES Tstudent ("student_id") ON DELETE CASCADE); |
| DROP TABLE "Tadresse" ; supprime la table et les données existantes... | DROP TABLE "Tadresse" ; supprime la table et les données existantes... |
| |
| CREATE TABLE "Tadresse" ("id_etudiant" char NOT NULL, "numero_ligne" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "adresse" CHAR, FOREIGN KEY ("id_etudiant") REFERENCES Tetudiant ("id_etudiant") ON DELETE CASCADE); | CREATE TABLE "Tadresse" ("id_etudiant" CHAR NOT NULL, "numero_ligne" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "adresse" CHAR, FOREIGN KEY ("id_etudiant") REFERENCES Tetudiant ("id_etudiant") ON DELETE CASCADE); |
| |
| Cela forcera les mises à jour à vérifier que la clé étrangère id_etudiant (dans Tadresse) référence (correspond à) celle de Tetudiant (colonne "id_etudiant"). La phrase ON DELETE CASCADE va faire en sorte que la destruction d'un étudiant dans le parent (Tetudiant) supprime toutes les instances correspondantes de Tadresse. | Cela forcera les mises à jour à vérifier que la FOREIGN KEY "id_etudiant" (dans Tadresse) référence (correspond à) celle de Tetudiant (colonne "id_etudiant"). La phrase ON DELETE CASCADE va faire en sorte que la suppression d'un étudiant dans le parent (Tetudiant) supprime toutes les instances correspondantes de Tadresse. |
| |
| ====== 17 ====== | ====== 17 ====== |
| Because we have the data available, we shall load the initial data from a CSV file (created from a spreadsheet):** | Because we have the data available, we shall load the initial data from a CSV file (created from a spreadsheet):** |
| |
| Il ne nous importe pas vraiment que le numéro de ligne de l'adresse soit 1, 2, 3, ... ou pas ; il faut juste qu'il augmente. Nous avons pris des dispositions pour que la base de données le fasse, ce qui nous gagne du temps côté programme. | Il ne nous importe pas vraiment que le numéro de ligne de l'adresse soit 1, 2, 3, ... ou pas ; il faut juste qu'il augmente. Nous avons pris des dispositions pour que la base de données le fasse, ce qui nous fait gagner du temps côté programme. |
| |
| Un exemple simple (en Python) pour ajouter quelques lignes d'adresse serait : | Un exemple simple (en Python) pour ajouter quelques lignes d'adresse serait : |
| Notez que l'interface convertit un « None » Python en un « Null » en SQL, qu'il n'y a pas de gestion des erreurs et que le numéro de séquence de la ligne d'adresse est généré (à juste titre) par la base de données. | Notez que l'interface convertit un « None » Python en un « Null » en SQL, qu'il n'y a pas de gestion des erreurs et que le numéro de séquence de la ligne d'adresse est généré (à juste titre) par la base de données. |
| |
| Nous devons maintenant créer une nouvelle table super simple, qui sera un "fichier des codes" ; elle associe les codes d'examen avec leurs titres. C'est tellement simple que nous pouvons le saisir dans la zone « Exécuter le SQL » sans aucune contrainte. | Nous devons maintenant créer une nouvelle table super simple, qui sera un « fichier des codes » ; elle associe les codes d'examen avec leur titre. C'est tellement simple que nous pouvons le saisir dans la zone « Exécuter le SQL » sans aucune contrainte. |
| |
| CREATE TABLE Tnom_examen (id_examen CHAR PRIMARY KEY NOT NULL UNIQUE, titre_examen CHAR); | CREATE TABLE Tnom_examen (id_examen CHAR PRIMARY KEY NOT NULL UNIQUE, titre_examen CHAR); |
| (The multiple lines are purely for readability), we enter this into the “execute SQL” section:- Note the optional temporary renaming of the tables to single letter names, purely so the full qualification of field names is abbreviated. We don't have much data in our database, so it looks like that shown below.** | (The multiple lines are purely for readability), we enter this into the “execute SQL” section:- Note the optional temporary renaming of the tables to single letter names, purely so the full qualification of field names is abbreviated. We don't have much data in our database, so it looks like that shown below.** |
| |
| Nous avons maintenant un certain nombre de tables et nous sommes en mesure d'utiliser l'une des fonctionnalités les plus puissantes de SQL lorsque nous combinons deux ou plusieurs tables en même temps. Pour ce faire, nous utilisons le mot-clé JOIN. Il existe deux variétés principales de jointure disponibles – de loin le type le plus commun de jointure est sur des colonnes qui se correspondent (techniquement un INNER JOIN). Vous pouvez deviner le nom de l'autre type de JOIN ! | Nous avons maintenant de nombreuses tables et nous sommes en mesure d'utiliser l'une des fonctionnalités les plus puissantes de SQL lorsque nous combinons deux ou plusieurs tables. Pour ce faire, nous utilisons le mot-clé JOIN. Il existe deux variétés principales de jointure disponibles – de loin le type le plus commun de jointure est sur des colonnes qui se correspondent (techniquement un INNER JOIN). Vous pouvez deviner le nom de l'autre type de JOIN ! |
| |
| Lorsque nous spécifions notre jointure, un problème qui survient parfois est que nous avons utilisé le même nom pour une colonne dans deux tables, mais peut-être avec des significations différentes, auquel cas nous devons qualifier le nom de la colonne par le nom de la table. À titre d'exemple : | Lorsque nous spécifions notre jointure, un problème qui survient parfois est que nous avons utilisé le même nom pour une colonne dans deux tables, mais peut-être avec des significations différentes, auquel cas nous devons qualifier le nom de la colonne par le nom de la table. À titre d'exemple : |
| ON S.id_etudiant = A.id_etudiant; | ON S.id_etudiant = A.id_etudiant; |
| |
| (Les lignes multiples sont présentes pour la lisibilité), nous saisissons cela dans la section « Exécuter le SQL » : notez le changement de nom temporaire facultatif des tables avec une seule lettre, afin d'abréger la qualification complète des noms de champs. Nous n'avons pas beaucoup de données dans notre base de données, et ça ressemble à l'illustration ci-dessous. | (Les lignes multiples ne sont présentes que pour la lisibilité), nous saisissons cela dans la section « Exécuter le SQL » : notez le changement de nom temporaire facultatif des tables en une seule lettre, afin d'abréger la qualification complète des noms de champs. Puisque nous n'avons pas beaucoup de données dans notre base de données, ça ressemble à l'illustration ci-dessous. |
| |
| ====== 20 ====== | ====== 20 ====== |
| We can also create joins which are a JOIN of a table with itself, to create Views of a single table—perhaps to hide a particularly complex piece of SQL. It's also possible to join more than two tables in one statement—but then things tend to get complicated!** | We can also create joins which are a JOIN of a table with itself, to create Views of a single table—perhaps to hide a particularly complex piece of SQL. It's also possible to join more than two tables in one statement—but then things tend to get complicated!** |
| |
| Il arrive fréquemment que nous prenions l'habitude d'utiliser de nouveaux noms pour la même chose, en fait notre id_etudiant signifie la même chose dans les deux tables. Cela donne l'idée d'un « NATURAL JOIN » comme raccourci, dans lequel les noms correspondants sont supposés devoir être mis en correspondance, de sorte qu'un équivalent de la jointure précédente est : | Il arrive fréquemment que nous prenions l'habitude d'utiliser de nouveaux noms pour la même chose et, en fait, notre id_etudiant signifie la même chose dans les deux tables. Cela donne l'idée d'un « NATURAL JOIN » comme raccourci, dans lequel les noms correspondants sont supposés devoir être mis en correspondance, de sorte qu'un équivalent de la jointure précédente est : |
| |
| SELECT nom, prenom, adresse FROM Tetudiant | SELECT nom, prenom, adresse FROM Tetudiant |
| Si nous risquons de devoir utiliser notre liste d'adresses périodiquement, alors nous devrions créer une vue (en utilisant notre instruction SELECT ci-dessus). Il n'y a pas de surcharge à faire cela, puisque c'est juste une instruction sur la façon de consulter des tables existantes. Cependant, il est important de réaliser que, pour la même raison, seules les tables peuvent être modifiées — les vues sont simplement un reflet de l'état actuel des tables ; les données des vues ne peuvent pas être modifiées. | Si nous risquons de devoir utiliser notre liste d'adresses périodiquement, alors nous devrions créer une vue (en utilisant notre instruction SELECT ci-dessus). Il n'y a pas de surcharge à faire cela, puisque c'est juste une instruction sur la façon de consulter des tables existantes. Cependant, il est important de réaliser que, pour la même raison, seules les tables peuvent être modifiées — les vues sont simplement un reflet de l'état actuel des tables ; les données des vues ne peuvent pas être modifiées. |
| |
| Nous pouvons également créer des jointures d'une table avec elle-même, pour créer des vues d'une seule table — peut-être pour cacher un morceau particulièrement complexe de SQL. Il est également possible de joindre plus de deux tables dans une instruction, mais ensuite les choses ont tendance à se compliquer ! | Nous pouvons également créer des jointures d'une table avec elle-même, pour créer des vues d'une seule table — peut-être pour cacher un morceau particulièrement complexe de SQL. Il est également possible de joindre plus de deux tables dans une instruction, mais, alors, les choses ont tendance à se compliquer ! |
| |
| ====== 21 ====== | ====== 21 ====== |
| The exam score will be zero if they have not sat the exam yet. To access the score, we need the combination of student_id and exam_id to be our unique (primary key). This is a slightly tricky case, because we don't allow a student to take the same exam twice, but the data structure means that we need to accept duplicate student_id's and duplicate exam_id's, but reject any combinations of the two which are not unique. This is a situation where a composite primary key has the effect we want.** | The exam score will be zero if they have not sat the exam yet. To access the score, we need the combination of student_id and exam_id to be our unique (primary key). This is a slightly tricky case, because we don't allow a student to take the same exam twice, but the data structure means that we need to accept duplicate student_id's and duplicate exam_id's, but reject any combinations of the two which are not unique. This is a situation where a composite primary key has the effect we want.** |
| |
| Si nous avons une très grande base de données — plusieurs milliers de lignes dans plusieurs tables — les performances s'en ressentiront. Dans ce cas, nous devrions examiner comment nous utilisons notre base de données, en particulier en ce qui concerne les jointures, et utiliser le menu Index → Créer un index. Ceci est susceptible d'accélérer les requêtes, au détriment d'une petite pénalité pour les mises à jour de la base de données (nous devons maintenir chaque index supplémentaire). | Si nous avons une très grande base de données — plusieurs milliers de lignes dans plusieurs tables — les performances s'en ressentiront. Dans ce cas, nous devrions examiner comment nous utilisons notre base de données, en particulier en ce qui concerne les jointures, et utiliser le menu Index → Créer un index. Ceci est susceptible d'accélérer les requêtes, au détriment de la facilité des mises à jour de la base de données (nous devons maintenir chaque index supplémentaire). |
| |
| La dernière fonctionnalité importante qui peut être utilisée est l'instruction TRIGGER – quand il est souhaitable qu'un événement de base de données « déclenche » une suite d'actions. C'est vraiment un sujet assez avancé et je vous renvoie ailleurs ! | La dernière fonctionnalité importante qui peut être utilisée est l'instruction TRIGGER – quand il est souhaitable qu'un événement de base de données « déclenche » une suite d'actions. C'est vraiment un sujet assez avancé et je vous renvoie ailleurs ! |
| CREATE TABLE "Texamens" ("id_etudiant" CHAR NOT NULL, "id_examen" CHAR NOT NULL, "note" INTEGER DEFAULT 0, PRIMARY KEY ("id_etudiant", "id_examen")) | CREATE TABLE "Texamens" ("id_etudiant" CHAR NOT NULL, "id_examen" CHAR NOT NULL, "note" INTEGER DEFAULT 0, PRIMARY KEY ("id_etudiant", "id_examen")) |
| |
| Nous devrions vraiment déclarer id_etudiant comme clé étrangère afin qu'il soit validé par rapport à Tetudiant, et de façon similaire id_examen devrait être une clé étrangère afin qu'il soit validé par rapport à Tnom_examen . Donc, nous supprimons (avec DROP Texamens) et exécutons l'instruction SQL suivante ; comme vous pouvez le voir, elle est effectivement similaire à notre précédente rencontre avec les clés étrangères : | Nous devrions vraiment déclarer id_etudiant comme clé étrangère afin qu'il soit validé par rapport à Tetudiant, et, de façon similaire, id_examen devrait être une clé étrangère afin qu'il soit validé par rapport à Tnom_examen. Donc, nous supprimons (avec DROP Texamens) et exécutons l'instruction SQL suivante ; comme vous pouvez le voir, elle est effectivement similaire à notre précédente rencontre avec les clés étrangères : |
| |
| CREATE TABLE "Texamens" ("id_etudiant" CHAR NOT NULL, "id_examen" CHAR NOT NULL, "note" INTEGER DEFAULT 0, | CREATE TABLE "Texamens" ("id_etudiant" CHAR NOT NULL, "id_examen" CHAR NOT NULL, "note" INTEGER DEFAULT 0, |
| FOREIGN KEY("id_examen") REFERENCES Tnom_examen ("id_examen")); | FOREIGN KEY("id_examen") REFERENCES Tnom_examen ("id_examen")); |
| |
| (En fait , ce n'est pas tout à fait correct car SQLite permet que des caractères soient stockés dans des colonnes numériques, donc l'instruction CHECK ne fonctionnerait pas correctement ; nous pourrions résoudre ce problème avec une instruction CHECK plus compliquée, mais cela n'ajouterait rien à la lisibilité. Donc, nous allons le laisser comme ça). | (En fait , ce n'est pas tout à fait correct, car SQLite permet que des caractères soient stockés dans des colonnes numériques, donc l'instruction CHECK ne fonctionnerait pas correctement ; nous pourrions résoudre ce problème avec une instruction CHECK plus compliquée, mais cela n'ajouterait rien à la lisibilité. Donc, nous allons le laisser comme ça). |
| |
| ====== 23 ====== | ====== 23 ====== |
| Nous pouvons saisir tous les cas de test en utilisant INSERT, et ils devraient tous se comporter correctement. | Nous pouvons saisir tous les cas de test en utilisant INSERT, et ils devraient tous se comporter correctement. |
| |
| Avec notre dernière table, nous sommes en mesure de faire quelque chose de typique. Le collège a quelques règles assez arbitraires, qui changent de temps en temps, donc elles n'ont pas vraiment besoin d'être forcées par la base de données. Par exemple : « Aucun étudiant ne peut s'inscrire pour M829 sans avoir réussi M823 auparavant. » C'est facile à faire en SQL avec un peu de décision dans un programme. | Avec notre dernière table, nous sommes en mesure de faire quelque chose de typique. Le collège a quelques règles assez arbitraires, qui changent de temps en temps et elles n'ont donc pas vraiment besoin d'être appliquées par la base de données. Par exemple : « Aucun étudiant ne peut s'inscrire pour M829 sans avoir réussi M823 auparavant. » C'est facile à faire en SQL avec un peu de décision dans un programme. |
| |
| Comme légère variation, j'ai fourni une version qui s'exécute dans un écran de terminal ; elle pourrait facilement être convertie en un script bash. Beaucoup de programmes peuvent avoir la base de données ouverte, simultanément. | Comme légère variation, j'ai fourni une version qui s'exécute dans un écran de terminal ; elle pourrait facilement être convertie en un script bash. La base de données peut être ouverte dans beaucoup de programmes, simultanément. |
| |
| ====== 24 ====== | ====== 24 ====== |
| http://zetcode.com/db/sqliteruby/ - Ruby. | http://zetcode.com/db/sqliteruby/ - Ruby. |
| |
| http://zetcode.com/db/sqlitecsharp/ - C #. | http://zetcode.com/db/sqlitecsharp/ - C#. |
| |
| http://zetcode.com/db/sqlitevb/ - Visual Basic. | http://zetcode.com/db/sqlitevb/ - Visual Basic. |
| |
| http://en.wikipedia.org/wiki/List_of_relational_database_management_systems - Seulement d'intérêt général. | http://en.wikipedia.org/wiki/List_of_relational_database_management_systems - Seulement d'intérêt général. |
