James Hoffman

Techniques de l'Informatique - Cégep de Shawinigan

Menu

Base de données 1

1.3- Exercices

2.1- Introduction à SQL

2.2- Exercices

Modèle relationnel de données

Offre une représentation visuelle de la structure d'une base de données

  • Tables
  • Colonnes
    • Type de donnée
    • Attributs(Auto-incrément, Non null, Unicité)
    • Valeur par défaut

On vise à protéger l'intégrité des données via la définition explicite des caractéristiques de l'information stockée

  • Précise, Complète, Cohérente

Règles de rédaction de SQL

Quelques bonne pratiques à garder en tête lors de la rédaction de code SQL

  • Standardiser les mots-clés en MAJUSCULE vs minuscule
  • Nom des tables au pluriel
  • Nom des colonnes en lower_snake_case
  • Utiliser la forme explicite des commandes

Exemple de guide de style

Pour rappel,

  • 1 instance de MariaDB
  • Héberge plusieurs base de données
  • 1 BD peut contenir plusieurs tables

Bases de données

sql
copier
create [or replace] database [if not exists] school;

drop database [if exists] school;

-- ATTENTION
-- La manipulation des base de données de supprime pas les GRANTS associés...

Il faudra donc préciser la BD à utiliser pour les commandes SQL subséquentes

sql
copier
use db_name;

-- ou individuellement pour chaque commande

select * from db_name.my_table;

Tables

sql
CREATE [OR REPLACE] TABLE [IF NOT EXISTS] table_name (
column_name data_type [column_attributes],
other_colum_name data_type [column_attributes]
);

data_type:
https://mariadb.com/kb/en/data-types/

column_attributes:
[NOT NULL] [AUTO_INCREMENT KEY] [UNIQUE] [DEFAULT default_value]

📚 Documentation DATATYPES et CREATE TABLE

Une fois une base de données en fonction, on veut préserver l'information, tout en étant capable de la faire évoluer selon les besoins.

📚 ALTER TABLE

Créer les tables

sql
copier
create table teachers (
employee_number int auto_increment key,
name varchar(200) not null,
email varchar(200) unique not null
);

create table courses (
code char(10) not null unique,
credits double(2,1) not null default 1,
name varchar(200) not null,
description varchar(1000)
);

Manipuler les tables

sql
copier
-- Voir les tables d'une BD
show tables [from db_name];


-- Voir la structure d'une table
describe table_name;


-- Voir la requête de création d'une table
show create table table_name;


-- Supprimer une table
-- Attention, supprimer une table n'efface pas les privilèges associés
drop table [if exists] table_name [, table_name ...];


-- Reinitialiser une table (DROP + CREATE)
truncate table table_name;

Requêtes d'insertion

sql
INSERT INTO table_name [(column_name, ...)]
VALUES ({expr | DEFAULT}, ...), (...), ...

INSERT

Pour insérer des données, on fait correspondre une valeur à chacune des colonnes de la table, dans le même ordre qu'elles sont définies dans la table.

On peut également préciser un sous-ensemble des colonnes et associer une valeur seulement pour ces dernières. Les colonnes ignorées doivent avoir une valeur par défaut/auto_increment.

sql
copier
insert into teachers values ( default, 'nom a', 'email a' );
-- est equivalent a
insert into teachers (employee_number, name, email) values ( default, 'nom b', 'email b' );


-- La colonne AUTO_INCREMENT doit avoir la valeur DEFAULT pour être incrémentée automatiquement
-- ou être ignorée
insert into teachers (name, email) values ('nom c', 'email c' );


-- On peut 'déplacer' la valeur d'AUTO_INCREMENT en saisissant manuellement une valeur
insert into teachers
values ( 100, 'nom d', 'email d' ), (default, 'nom e', 'email e' );
-- default sera 101, et ainsi de suite pour les prochains insert

Requêtes de récupération

sql
SELECT column_def [, column_def, ...]
FROM table_name
[WHERE where_condition]

column_def :
Utiliser * pour lister toutes les colonnes, sinon nommer explicitement les colonnes
Opérateur AS permets de renommer une colonne

where_condition :
Applique un critère de sélection aux lignes à retourner
Les opérateurs logiques disponibles sont IS NULL, IS NOT NULL, >, >=, <, <=, =, <>, &&, ||

📚 SELECT

sql
copier
select * from teachers;


select name, email from teachers;


select name as nom, credits as `crédits` from courses;


select *
from courses
where description is not null;


select *
from courses
where (description is not null and description <> '') or (credits > 2);

Structure d'exécution