SQL

[SQL] Commande SQL pour créer la table *animals*

*create table* animals ( . . . name text, . . . species text, . . . birthdate date );

[SQL] syntaxe pour ajouter une nouvelle table à la BDD avec des restrictions

*create table* nomtable (colonne1 *type* [restrictions], colonne2 *type* [restrictions], ... [ligne restrictions]);

[SQL] syntaxe pour ajouter une nouvelle table à la BDD sans restrictions

*create table* nomtable (colonne1 type , colonne2 type, ... );

[SQL] Voici une déclaration sélective qui s'exécute dans la base de données du zoo. Il sélectionne les espèces ayant les cinq populations les plus élevées du zoo. Changez-le en une instruction qui crée une vue nommée "topfive". select species, count(*) as num . . . from animals . . . group by species . . . order by num desc . . . limit 5;

*create view topfive as* select species, count(*) as num . . . from animals . . . group by species . . . order by num desc . . . limit 5; select * from topfive;

[SQL] Syntaxe d'une vue

*create view* nomvue as select ...

[SQL] Syntaxe de la commande delete en SQL

*delete* *from* table *where* column = "valeur";

[SQL] Syntaxe pour supprimer une table en SQL

*drop table* name [options];

[SQL] La syntaxe de base pour l'instruction d'insertion :

*insert into* table (colonne 1,colonne 2, ....) *values* ( val1, val2, ....) ; ou Si les valeurs sont dans le même ordre que les colonnes de la table *insert into* table *values* ( 'val1', 'val2', ....) ;

[SQL] Jointure de gauche

*left join* Une jointure gauche retourne toutes ses lignes, plus les lignes où la table gauche a une entrée mais pas la table droite

[SQL] Syntaxe pour fusionner 2 tables

*select* T.colonne1, S.colonne2 . . . *from* T join S . . . *on* T.cible = S.correspondance ou *select* T.colonne1, S.colonne2 . . . *from* T, S *where* T.cible = S.correspondance

[SQL] Syntaxe de la clause *having* qui suit l'agrégation *group by*

*select* columns *from* tables *group by* column *having* condition ;

[SQL] La syntaxe de l'instruction *select* avec une clause *where* :

*select* columns *from* tables *where* condition # Les colonnes sont séparées par des virgules #utilisez * pour sélectionner toutes les colonnes.

[SQL] Syntaxe de la clause *having* qui suit l'agrégation *count* , *max* ou *sum*

*select* columns, *count(*) as* num *from* sales *having* num > 5; # Afin d'appliquer having à une colonne agrégée, donner lui un nom en utilisant as.

[SQL] Combien de fois avons-nous vendu chaque produit? Utiliser *left join*

*select* products.name, products.sku, *count*(sales.sku) *as* *num* . . . *from* products *left join* sales . . . . . .*on* products.sku = sales.sku . . . *group by* products.sku;

[SQL] Remplacer les spams par "cheese". Le tableau s'appelle "posts" et a pour colonnes "content", "time" et "id"

*update* posts *set* content = 'cheese' *where* content *like* '%spam%' ; #le signe % fait correspondre n'importe quelle sous-chaîne, y compris la chaîne vide.

[SQL] Syntaxe de l'instruction update

*update* table *set* column = value *where* restriction ;

[SQL] Données de type Date et catégories de temps :

- *date* : une date civile, y compris l'année, le mois et le jour. - *time* : un moment de la journée. - *timestamp* : une date et une heure ensemble.

[SQL] Données de types numériques:

- *integer* : une valeur entière, comme Python int. - *real* : une valeur à virgule flottante, comme un float Python. - *double precision* : une valeur en virgule flottante de plus grande précision. - *decimal* : une valeur décimale exacte.

[SQL] Données de types de texte et de chaînes de caractères:

- *text* : une chaîne de n'importe quelle longueur, comme Python str ou les types unicode. - *char(n)* : une chaîne de n caractères exactement. - *varchar(n)* : une chaîne de n caractères maximum.

[SQL] Clauses de sélection :

- *where* restrictions - *limit* count - *limit* count *offset* skip - *order by* columns - *order by* columns *desc* - *group by* columns ex: QUERY = "select name, birthdate from animals order by name limit 10 offset 20;"

[SQL] Les fonctions d'agrégation SQL les plus communes :

- count : nombre de rangées - avg : moyenne - max : le nombre maximum - min : le nombre minimum - sum : la somme

[SQL] Démarrer la machine virtuelle

- dans le terminal, accéder au sous-répertoire "vagrant" - lancer la commande "vagrant up" - lancer la commande "vagrant ssh" - vous êtes connecté

[SQL] Déconnexion et connexion de la machine virtuelle

- exit ou ctrl + D pour se déconnecter - pour se reconnecter : --> • aller dans le répertoire /vagrant • exécuter vagrant up • vagrant ssh

[SQL] Qu'est-ce qu'une base de données ?

- format de stockage de données - programme qui gère ses bases sur un serveur (ex: MySQL) - outil flexible pour l'interrogation et le résumé des données

[SQL] Les dates

- format standard international : "1999-12-31" - mettre des guillemets autour des dates

[SQL] Pourquoi est-il préférable de faire les requête directement dans la BDD plutôt que dans Python ?

- la BDD a une rapidité d'exécution plus grande que python - le traitement des données en Python nécessite beaucoup de mémoire.

[SQL] Avantages et inconvénients d'une base de données

- persiste dans le temps - donne la possibilité d'accéder aux données et de les modifier en même temps - nécessite des règles pour protéger les données - compliqué de trouver le nom d'une table ou des colonnes lorsque l'on ne connait pas la base de données

[SQL] Opérateur *like*

- prend en charge une forme simple de mise en correspondance de motifs de texte - ce qui se trouve à gauche de l'opérateur sera comparé au motif de droite. - motif = chaîne de texte SQL entre guillemets simples

[SQL] Vagrant

- programme qui configure la machine virtuelle. - permet de partager des fichiers entre votre ordinateur hôte et le système de fichiers de la VM

[SQL] Configurer la machine virtuelle

- télécharger un fichier de configuration VM sur github ou autre - à l'aide du terminal, accéder dans ce fichier au répertoire appelé "vagrant"

[SQL] Les avantages des vues sont :

- éviter de taper une requête très longue - masquer certaines données à certains utilisateurs. Utiliser une vue à la place d'une sous-requête, c'est comme utiliser une fonction python à la place de recopier le code

[SQL] Clause de sélection permettant de modifier le comportement des agrégations telles que *max*, *count* et *sum*

... *group by* columns ex: QUERY = "*select* species, min(birthdate) from animals *group by* species;"

[SQL] Clause de sélection permettant de retourner seulement les premières lignes de comptage du tableau des résultats.

... *limit* count

[SQL] Clause de sélection permettant de retourner les lignes de comptage commençant après les premières lignes de saut.

... *limit* count *offset* skip

[SQL] Clause de sélection permettant de trier par ordre croissant les lignes en utilisant les colonnes

... *order by* columns

[SQL] Clause de sélection permettant de trier par ordre décroissant les lignes en utilisant les colonnes

... *order by* columns *desc*

[SQL] Clause de sélection permettant de filtrer une table pour les lignes qui suivent une règle particulière

... *where* columns restriction

[SQL] 4 règles pour normaliser les tables :

1) Chaque ligne a le même nombre de colonnes 2) Il y a une clé unique, et tout ce qu'il y a dans une rangée dit quelque chose sur la clé. La clé peut être une ou plusieurs colonnes, un numéro de série 3) les faits qui ne se rapportent pas à la clé appartiennent à des tables différentes 4) le tableau ne devrait pas impliquer des relations qui n'existent pas

[SQL] Etapes pour installer une machine virtuelle:

1) Utiliser un terminal 2) Installer virtual box 3) Installer Vagrant 4) Télécharger la configuration de la machine virtuelle 5) Démarrer la machine virtuelle

[SQL] Sélectionner tous les animaux sauf l'espèce gorille et les noms Max

3 façons : select name from animals where (not species ='gorilla') and (not name = 'Max'); select name from animals where not (species ='gorilla') and (not name = 'Max'); select name from animals where species != 'gorilla' and name != 'Max';

[SQL] Types de requêtes et sous-requêtes :

3 types :

[SQL] Qu'est-ce que la DB-API?

Ce n'est pas une librairie mais un standard de librairie Python qui permet au code de se connecter à la base de données. Il spécifie les fonctions que vous allez appeler pour vous connecter à une base de données pour envoyer des requêtes et recevoir les résultats.

[SQL] Correspondance entre les systèmes de BDD SQL et les modules DB-API

Ces modules permettent d'adapter le code Python d'un système à un autre

[SQL] Anatomie d'une table

Elle est composée de 2 parties: en-tête de table : - nom de la table - nom des colonnes - type de colonnes corps de la table: - colonnes - rangées

[SQL] Que permet les jointures ?

Les jointures en SQL permettent d'associer plusieurs tables dans une même requête

[SQL] Avantage de la sous-requête

On peut remplacer une seule requête par 2

[SQL] Agrégation

Permet de faire une opération sur une colonne et retourne le résultat sous forme de table

[SQL] Forme normalisée

Pour éviter la duplication, nous divisons l'information qui se trouvait dans un objet en 2 tables distinctes

[SQL] Trouvez les noms des animaux qui mangent du poisson. Le tableau des animaux comporte des colonnes (nom, espèce, date de naissance) pour chaque individu. Le tableau diététique comporte des colonnes (espèces, aliments) pour chaque aliment qu'une espèce mange.

QUERY = ''' *select* animals.name . . . *from* animals join diet . . . . *on* animals.species = diet.species . . . *where* food= 'fish' ''' ou QUERY = ''' select name from animals, diet . . . where animals.species = diet.species . . . . and diet.food = 'fish';

[SQL] Trouvez l'aliment qui n'est mangé que par un seul animal. Le tableau des animaux comporte des colonnes (nom, espèce, date de naissance) pour chaque individu. Le tableau diététique comporte des colonnes (espèces, aliments) pour chaque aliment qu'une espèce mange.

QUERY = ''' *select* food, *count*(animals.name) *as* num *from* diet *join* animals *on* diet.species = animals.species *group by* food *having* num = 1 ou *select* food, *count*(animals.name) *as* num *from* diet, animals *where* diet.species = animals.species *group by* food *having* num = 1

[SQL] Rédigez une requête qui retourne toutes les espèces du zoo, avec le nombre d'animaux de chaque espèce, triées par nombre d'espèces décroissant. Le résultat doit avoir deux colonnes: espèce et nombre. Le tableau des animaux comporte des colonnes (nom, espèce, date de naissance) pour chaque individu.

QUERY = '''select count(*) as num, species . . . from animals . . . group by species . . . order by num desc;

[SQL] Insérez un opossum de nouveau-né dans le tableau des animaux et vérifiez qu'il a été ajouté. SELECT_QUERY devrait trouver les noms et dates de naissance de tous les opossums. INSERT_QUERY devrait ajouter un nouvel opossum à la table, dont la date de naissance est aujourd'hui. (Ou vous pouvez choisir n'importe quelle autre date que vous voulez.) Le tableau des animaux comporte des colonnes (nom, espèce, date de naissance) pour chaque individu. SELECT_QUERY = ''' select ... where ...; ''' INSERT_QUERY = ''' insert into animals ...; '''

SELECT_QUERY = ''' *select* name, birthdate *from* animals *where* species = 'opossum'; ''' INSERT_QUERY = ''' *insert into* animals *values(*'tito', 'opossum', '2018-08-01'); '''

[SQL] Signification de SQL

Structured Query Language

[SQL] SGBD

Système de gestion de base de données : PostgreSQL ou MySQL ou SQLite

[SQL] 3 catégories que SQL supporte :

Types de texte et de chaînes de caractères Types numériques Date et catégories de temps

[SQL] Clé primaire

Une clé primaire est la donnée qui permet d'identifier de manière unique un enregistrement dans une table.

[SQL] Clé étrangère

Une clé étrangère identifie une colonne ou un ensemble de colonnes d'une table comme référençant une colonne ou un ensemble de colonnes d'une autre table (la table référencée).

[SQL] Avantage de la clé étrangère

Une clé étrangère, dans une base de données relationnelle, est une contrainte qui garantit l*'intégrité référentielle* entre deux tables.

[SQL] Sous-requête

Une sous-requête est une requête à l'intérieur d'une autre requête. # ex : ici, le résultat de la sous-requête est la table "maxes"

[SQL] Les vues

Une vue dans une base de données est une synthèse d'une requête d'interrogation de la base. Une vue est une requête de sélection stockée dans la base de données d'une manière qui vous permet de l'utiliser comme une table.

[SQL] Outils permettant d'installer et de gérer la machine virtuelle

Vagrant Virtual Box

[SQL] dans un terminal, afficher les colonnes de la table "posts" de la BDD de type psql:

\d posts

[SQL] dans un terminal, lister toutes les tables de la base de données "forum" de type psql :

\dt

[SQL] dans un terminal, lister les tables plus des informations supplémentaires (notamment la taille de chaque table sur le disque) de la base de données "forum" de type psql :

\dt+

[SQL] Se connecter à une BDD avec python, dans le système SQLite

conn = *sqlite3.connect*("nom_BDD") # la fonction connect() retourne un objet de connexion

[SQL] Se déconnecter de la BDD avec python et dans le système SQLite

conn*.close()*

[SQL] fonction python pour valider la transaction courante à la BDD

conn.*commit()*

[SQL] fonction python pour annuler les modifications apportées par la transaction en cours dans la BDD

conn.*rollback()*

[SQL] Type de données pris en compte en fonction du types de BDD

create table matable (colonne 1 *type*, colonne2 *type*, ... ) types : integers, booleans, bit strings, text, dates and times, IP addresses, XML, JSON, arrays...

[SQL] syntaxe de la clé primaire en SQL

create table nom_table ( colonne1 type *primary key*, colonne2 type, ... ) # On écrit donc *primary keys* après le type de la 1ère colonne. ex : create table students (id serial primary key, name text, birthdate date );

[SQL] syntaxe de la clé primaire en SQL, lorsqu'elle est composée de plusieurs colonnes

create table nom_table ( colonne1 type , colonne2 type, colonne3 type, *primary key* (colonne1, colonne2)); # on indique la clé primaire après avoir cité toutes les colonnes.

[SQL] Déclarer les relations entre 2 tables dont leurs colonnes portent un nom différent

create table nom_table1 (colonne1 type *references* nom_table2 *(colonne_a)*, colonne2 type, colonne3 type);

[SQL] Déclarer les relations entre 2 tables dont leurs colonnes portent le même nom

create table nom_table1 (colonne1 type *references* nom_table2, colonne2 type, colonne3 type); # ex : create table sales (sku text references products, sale_date date, count integer);

[SQL] Créer une table students ayant pour clé primaire la colonne "id", et une colonne "name." Créer une table "courses" ayant pour clé primaire la colonne "id", et une colonne "name". Créer une table "grade" avec les colonnes suivantes: - "student" ayant pour référence l'id de la table "students" - "course" ayant pour référence la colonne id de la table "courses" - "grade"

create table students ( id serial primary key, name text); create table courses ( id text primary key, name text); create table grades ( student integer references students (id), course text references courses (id), grade text);

[SQL] Créer un objet *cursor* avec python, dans le système SQLite

cursor = conn*.cursor()*

[SQL] Envoyer une requête SQL "requête" avec python dans le système SQLite

cursor*.execute(*"requête_SQL")

[SQL] Transformer ces 2 requêtes en une seule : def lightweights(cursor): . . . cursor.execute("select avg(weight) as av from players;") . . . av = cursor.fetchall()[0][0] # first column of first (and only) row . . . cursor.execute("select name, weight from players where weight < " + str(av)) . . . return cursor.fetchall()

def lightweights(cursor): . . . cursor.execute("*select name, weight from players, (select avg(weight) as av from players) as subq where weight < av;"*) . . . row = cursor.fetchall() . . . return row résultat : [('George Moose', 400), ('Edna Moose', 340), ('Emmy Moose', 410), ('Betty McMoose', 400)] Queries performed: 1

[SQL] Commande SQL qui permet de supprimer des valeurs

delete

[SQL] Virtual box

exécute la machine virtuelle

[SQL] Opérateurs de comparaison

identiques à ceux de python sauf pour le égal, SQL utilise *= =* au lieu de *==*

[SQL] Importer la librairie python qui permet de communiquer avec le système de BDD SQLite

import *sqlite3*

[SQL] Soit une BDD de type SQLite qui s'appelle *students*. Elle a 2 colonnes : - *name* : nom des étudiants - *id* : numéro d'étudiant Avec python, récupérer la liste des étudiants classée par ordre alphabétique:

import *sqlite3* # Récupérer les noms des élèves dans la BDD db = sqlite3.connect("students") c = db.cursor() query = "select name, id from students order by name;" c.execute(query) rows = c.fetchall() # Premièrement, quelle structure de données avons-nous obtenue ? print "Row data:" print rows # Et itérons à travers une boucle for: print "Student names:" for row in rows: print " ", row[0] db.close()

[SQL] Soit une BDD de type SQLite qui s'appelle *testdb*. Elle a 2 colonnes : - *color* - *content* Avec python, insérer dans la table *ballons* une ligne "blue, water", puis valider l'insertion.

import sqlite3 db = sqlite3.connect("testdb") c = db.cursor() c.execute("insert into balloons values ('blue', 'water') « ) db.commit() db.close()

[SQL] Librairie python qui permet de s'assurer qu'un commentaire sera toujours traité de la même manière

librairie bleach

[SQL] Opérateur SQL qui permet de faire correspondre à un motif de texte

like ex : pour une ligne de tableau où la colonne fish a la valeur " salmon " une restriction serait : fish like 'salmon'

[SQL] A quoi peut servir une machine virtuelle?

permet d'exécuter un serveur de BDD

[SQL] dans un terminal, se connecter à la base de données "forum" de type psql

psql forum

[SQL] Récupérer les résultats d'une requête SQL avec python et dans le système SQLite

results = cursor*.fetchall()*

[SQL] Récupérer les résultats d'une requête SQL en une fois avec python et dans le système SQLite

results = cursor*.fetchone()*

[SQL] A partir de la table "residences", trouver des colocataires en utilisant les jointures SQL

select a.id, b.id, a.building, a.room . . . from residences as a, residences as b where a.building = b.building . . . and a.room = b.room . . . and a.id < b.id order by a.building, a.room;

[SQL] Requête SQL pour joindre 2 tableaux

select animals.name, animals.species, diet.food . . . from animals join diet . . . on animals.species = diet.species;

[SQL] Dressez la liste de tous les ordres taxonomiques, en utilisant leurs noms communs, triés selon le nombre d'animaux de cet ordre que le zoo possède. Le tableau des animaux comporte (nom, espèce, date de naissance) pour chaque individu. La table de taxonomie a (nom, espèce, genre, famille, famille, t_ordre) pour chaque espèce. La table des noms d'ordre a (t_order, nom) pour chaque ordre. Attention: Chacune de ces tables a une colonne "nom", mais elles n'ont pas la même signification ! animals.name est le nom individuel d'un animal. taxonomy.name est le nom commun d'une espèce (comme 'ours brun').

select ordernames.name, count(*) as num . . . from animals, taxonomy, ordernames . . . where animals.species = taxonomy.name . . . and taxonomy.t_order = ordernames.t_order . . . group by ordernames.name . . . order by num desc ou select ordernames.name, count(*) as num . . from (animals join taxonomy . . . . . . . . on animals.species = taxonomy.name) . . . . . . . . as ani_tax . . . . . . join ordernames . . . . . . . .on ani_tax.t_order = ordernames.t_order . . group by ordernames.name . . order by num desc

[SQL] Voici deux tableaux décrivant les bogues trouvés dans certains programmes. Le tableau "programmes" donne le nom de chaque programme et les fichiers qui le composent. La table "bugs" donne le fichier dans lequel chaque bug a été trouvé. create table programs ( . . . name text, . . . filename text . . . ); create table bugs ( . . . filename text, . . . description text, . . . id serial primary key ); Ecrire une requête qui compte le nombre de bugs dans chaque programme. Y inclure également les lignes pour les programmes sans bugs:

select programs.name, count(bugs.filename) as num . . . from programs left join bugs . . . . . . on programs.filename = bugs.filename . . . group by programs.name . . . order by num;

[SQL] Le résultat d'une requête est toujours :

une table

[SQL] Commande SQL qui permet de changer les valeurs dans les rangées existantes.

update