SQL : Un Langage Relationnel (08-09)

SQL : Un Langage Relationnel(08-09) Witold LITWIN

SQL • Inventé à IBM San Jose, 1974 (Boyce & Chamberlin) pour System R • Basé sur le calcul de tuple & algèbre relationnelle • Relationnellement complet (et plus) • Le langagede SGBD relationnels • En évolution contrôlée par ANSI (SQL1, 2, 3...)

SQL • Offre l’interface de commande • Interactive et pour les lanagages de programmation • Voir le cours « SQL Imbriqué » • Il existe aussi plusieurs dialectes • Les possibilités basiques sont simples • Celles avancées peuvent être fort complexes • Signalées dans ce qui suit par

Langage de base de données(Database Language) • Un sous-langage de programmation • Consiste traditionnellement de deux parties: • langage de définition de données • langage de manipulation de données • langage interactif (de requêtes) • langage imbriqué (embedded) • SQL pas QBE • En pratique, les deux parties sont imbriquées • définition de vues, de contraintes d’intégrité…

Définition de Données Relationnelles SQL et QBE Witold LITWIN 08-09

SQL: Définition de Données • CREATE TABLE CREATE VIEW CREATE INDEX • ALTER TABLE • DROP TABLE DROP VIEW DROP INDEX

CREATE TABLE(clauses essentielles) • Définit la table réelle (de base) CREATE TABLE table (column [,column]... [, primarykey] ; column := name type [NOT NULL] type :=INTEGER, CHAR (n), GRAPHIC, ICON, DATE, TIME, TIMESTAMP

EXAMPLE CREATE TABLE S(S# CHAR (5) NOT NULL,SNAME CHAR (20),STATUS INT,CITY CHAR (15), PRIMARY KEY (S#) ) ;

CREATE TABLEClause CONSTRAINT • Clauses CONSTRAINT permettent de définir • les clés étrangères FOREIGN KEY • les contraintes d'intégrité CHECK • sur un attribut • inter-attribut d’une table • Autres

CREATE TABLEClause CONSTRAINT • La puissance expressive varie entre les dialectes • le standard est le plus puissant • notamment permet tout SELECT dans CHECK • les dialectes ne permettent que • aucun CHECK (MsAccess) • contrainte sur les valeurs d’un même tuple (DB-2) • une par attribut (SQL-Server, DB2) • pas de sous-requêtes (SQL-Server)

CREATE TABLE(multibase) • On peut créer une table dans une autre base que celle courante (ouverte) • SQL Server, SQL (seulement) de MsAccess, SQL-2 CREATE TABLE AUTRE-BASE.S(S# CHAR (5) NOT NULL,SNAME CHAR (20),STATUS INT,CITY CHAR (15), PRIMARY KEY (S#) ) ; Autre-Base Base courante

ALTER & DROP TABLE ALTER TABLE S ADD DISCOUNT SMALLINT ; • certains systèmes: ALTER TABLE S DROP DISCOUNT SMALLINT ; ALTER TABLE S RENAME SNAME NAME ; ..... DROP TABLE P ;

Indexes CREATE [UNIQUE] INDEX index ON table ( column [order] [, column...) [CLUSTER] ; CREATE UNIQUE INDEX XS ON S (S#) ; CREATE UNIQUE INDEX XSP ON SP (S# ASC, P# DESC) ; • UNIQUE = pas de duplicata de valeurs indexées • L’indexe peut accélérer l’accès 100 – 10.000 fois • Indexes uniques obligatoires pour les clés dans le DB2

Indexes • En principe, une table peut avoir un nombre quelconque d'indexes • Les indexes accélèrent les recherches • Mais pénalisent les mises à jour ! • Pourquoi ? • Définition des indexes ne devait pas être à ce niveau de SQL (c'est la propriété du schéma interne)

Indexes • Les indexes (Linear) Hash de Postgres & de MySql résultent de recherche à l’INRIA du soussigné • Les manuels au CRIO • Wikipedia • Idem pour « Analysis Services » de SQL Server

Un dialecte de SQLSQL-MsAccess • Le dialecte le plus répandu aujourd'hui • Définition de données est considérablement plus élaborée que dans le SQL Standard • Certaines options du standard sont toutefois • sous restriction • s'expriment sous mots-clés différents • voir MsAccess Aide • pas toujours nécessaires

Un dialecte de SQLSQL-MsAccess • Définition de données par les commandes n’est utile en pratique que pour SQL-MsAccess Imbriqué • On verra ces commandes plus loin • Autrement on utilise l’interface graphique • Remarquable par sa puissance • On verra celle de MsAccess 2007

Interface Graphique de Définition de Donnés de MsAccess • Création de la base • Nom et type • Pas d’instruction CreateDatabase en SQL standard • Incroyable, mais vrai • Mot de passe de la base • Optionnel • Se défini en ouverture de la base en mode exclusive

Interface Graphique de MsAccessCréation de Tables • Approche QBE • Query By Example • Mode Création • Le plus expressif • On verra l’écran plus loin

Interface Graphique de MsAccessCréation de Tables • Mode Feuille de Données • On introduit les valeurs et (re)nomme les colonnes • Par défaut : N°, Champ1, Champ2… • MsAccessdevine le type de données • 100 serait Numérique Entier Long • 10,23 serait Numérique Réel Double • Paris serait Texte • …

Interface Graphique de MsAccessCréation de Tables • Mode Modèle de Table • Schémas Prédéfinis • Table Contacts • Table Tâches • Table Problèmes • … • On peut ajuster en Mode Création

ModeCréation Table P de S-P Table P de la base S-P

MsAccess: Contraintes d’Intégrité • Voir le générateur d’expression • On dispose de: • Constantes • Chaîne vide, faux, null, vrai • Opérateurs • Comparaison <, <=, >, >=, <>, Imp, Entre • Arithmétiques +,-, /, *, ^, \, Mod • Logiques Et, Ou, Pas, Ou_X, Eqv, Imp • Fonctions nombreuses (voir) • Parenthèses

MsAccess: Types de Données • Text • limité par défaut à 50 caractères • clause FIELD SIZE permet 256 caractères • supporte les prédicats SQL & QBE • Par ex. WHERE Contrat LIKE (*Voiture de tourisme*) • Mémo • taille < 64K caractères • supporte les prédicats SQL & QBE • Sous MsAccess 2007

MsAccess: Types de Données • Date/Heure • supporte l’arithmétique de dates/temps • 21/3 - 21/2 = 28 • 21/4 - 21/3 = 31 ? • prévu pour 21-ème siècle • 1/1/00 à 31/12/29 signifie 1/1/2000 à 31/12/2029 • 1/1/30 à 31/12/99 signifie 1/1/1930 à 31/12/1999 • Monétaire : • Dévises

MsAccess: Types de Données • NuméroAuto • compteur automatique (+1 à chaque tuple crée) • option incrément dans Nouvelles Valeur • OID pour chaque tuple crée • option aléatoire dans Nouvelles Valeur • Dans Create Table: CREATE TABLE table1(id1 AUTOINCREMENT,…

MsAccess: Types de Données • Hyperlien • comme son l ’indique • nom symbolique < 2048 octets • URL ou UNC< 2048 octets • sous-adresse (dans le fichier ou la page) CajunDelights#http://www.cajundelights.com#Price

MsAccess: Types de Données • Objet OLE • tout objet Windows • multimédia ou programme • peut être copié dans la table • les MAJ de l’original ne sont pas visibles dans la BD • peut être seulement référencé • gain de place • les MAJ de l’original sont visibles dans la BD • il faut double-cliquer sur sa description textuelle dans le tuple pour voir l’objet

MsAccess: Types de Données • Pièce Jointe (2007) • Tout objet Windows sécurisé • multimédia ou programme • Peut être copié ou seulement référencé • On peut attacher dans une même valeur plusieurs PJs.

MsAccess: Champ Numériqueclause Field size • Octet 0 à 255 • Entier-32,768 à 32,767, 2 octets. • Entier Long -2,147,483,648 à 2,147,483,647. 4 octets. • Précision Simple : Six digits -3.402823E38 à 3.402823E38. 4 octets. • Double (Default) 10 digits de précision 1.79769313486232E308 à 1.79769313486232E308. 8 octets. • Replication ID • Pour les bases dupliquées - 16 octets • un OID • peut être aussi dans le type Autonumber

MsAccess: Champ Yes/No A utiliser comme son nom l ’indique • Vrai/Faux Oui/Non Actif/Inactif • fixé par le champ Format et Liste de Choix • visualisé par défaut comme Faux ou Non ou Inactif • Valeur 0 = Faux, -1 = Vrai • mais, il y a d ’autres possibilités - taille: 1 octet

Intégrité référentielle

MsAccess : domaines • On peut les simuler (en QBE) par : • une table D source de valeurs • table de la base ou une liste de valeurs • une zone de texte ou zone de texte modifiable sur l’attribut A à valeurs dans D • déclaré dans la définition de A (partie Liste de choix) • une requête déclarée dans la définition de A (dans « contenu »)

MsAccess : surprises • Seules les valeurs apparaissant dans la 1-ère colonne du box et donc dans D peuvent être dans A • Même si l’on indique une autre « colonne liée » • Type de données Assistant Liste de choix réalise cette manipulation • Drôle de type de données • Attention aux bugs de cet assistant • Access 03 • Aussi à l ’option « Limiter à la liste » • On peut la faire aussi sans cet assistant (et mieux)

MsAccess : surprises • La table peut hériter l’attribut A si l’on déclare: • L’attribut héritant à le même nom que le 1èr attribut de D déclaré dans SELECT • L’attribut A est le 2-ème dans SELECT • La 1-ère longueur de colonne = 0cm  Dans notre exemple DB, SP peut ainsi hériter SNAME  Question: et si l’on sélectionne une autre colonne liée ? • Par ex. on utilise d’abord pour saisir les valeurs de P.CITY la requête • SELECT S.City, S.SName FROM S; • Puis, on la remplace par: • SELECT S.Sname, S.City FROM S;

CREATE INDEX(MSAccess) • Interface graphique (QBE) ou Commande • Onglet Index dans le Menu Affichage ou bouton droit • Quand la table est ouverte

MsAccess : CREATE INDEX • Pas d'indexe CLUSTER sous MsAccess • Commande SQL • Syntaxe habituelle create index xs on S (snameasc, status desc);

CREATE INDEX(SQL MSAccess) • Création d’un index qui n’a pas été fait par la clause de contrainte • Création d’un index qui ne peut pas être fait par la clause de contrainte • Options d’ordre…

CREATE INDEX(SQL MSAccess) • Création alternative de la clé primaire • Création d’un pseudo-index sur la table liée • Pas d'indexe CLUSTER sous MsAccess

CREATE INDEX(SQL MSAccess) • Syntaxe: CREATE [ UNIQUE ] INDEX index ON table (champ [ASC|DESC][, champ [ASC|DESC], ...]) [WITH { PRIMARY | DISALLOW NULL | IGNORE NULL }] • Exemple create index xp on P (city desc, pnameasc)

SQL MsAccess : CREATE TABLECONSTRAINT = INDEX CREATE TABLE [Friends] ([First Name] TEXT, [Last Name] TEXT); CREATE TABLE Friends1 ([First Name] TEXT, [Last Name] TEXT, [Date of Birth] DATETIME, CONSTRAINTMyTableConstraint UNIQUE ([First Name], [Last Name], [Date of Birth])); CREATE TABLE Friends2 ([First Name] TEXT, [Last Name] TEXT, SSN INTEGER CONSTRAINTMyFieldConstraint PRIMARY KEY); • SSN est la clé primaire. • On peut créer une table sans clé primaire • alors elle accepte des duplicata • contrairement à la théorie du relationnel

SQL MsAccess : CREATE TABLECONSTRAINT = Contraintes d'intégrité • Contrainte sur attribut unique: CONSTRAINT nom {PRIMARY KEY | UNIQUE | NOT NULL | REFERENCES foreigntable [(foreignfield1, foreignfield2)]} • Contrainte sur plusieurs attributs: CONSTRAINT name {PRIMARY KEY (primary1[, primary2 [, ...]]) | UNIQUE (unique1[, unique2 [, ...]]) | NOT NULL (notnull1[, notnull2 [, ...]]) | FOREIGN KEY (ref1[, ref2 [, ...]]) REFERENCES foreigntable [(foreignfield1 [, foreignfield2 [, ...]])]} • foreigntable = la table avec la clé primaire référencée

SQL MsAccess : CREATE TABLECONSTRAINT = Contraintes d'intégrité • L’interface QBE de MsAccess permet de définir + de contraintes • Comme on a vu en partie • Surtout les contraintes prédicatives d’intégrité • Mono ou multi-attribut, • Mais, mono-table.

SQL MsAccess : CREATE TABLETypes de Données • Sont différents de ceux de QBE • BINARY : 1 octet par caractère • BIT : 1 octet Valeurs Yes et No • TINYINT : 1 octet • MONEY : 8 octets Entier • DATETIME : 8 octets • UNIQUEIDENTIFIER : 128 bits

SQL MsAccess : CREATE TABLETypes de Données • REAL : 4 octets • FLOAT : 8 octets • SMALLINT : 2 octets • INTEGER : 4 octets • DECIMAL : 17 octets • TEXT : 2 octets par caractère • Zéro à 2,14 giga-octets au maximum. • Unicode

SQL MsAccess : CREATE TABLETypes de Données • IMAGE : • Zéro à 2,14 giga-octets. • Pour les objets OLE. • CHARACTER : 2 octets par caractère • Zéro à 255 caractères. • Unicode • COUNTER ou AUTOINCREMENT • Pour NuméroAuto

ALTER TABLE(MSAccess) • La syntaxe ALTER TABLE table {ADD {COLUMN type champ[(taille)] [NOT NULL] [CONSTRAINT index] | ALTER COLUMN type champ[(size)] | CONSTRAINT indexmultichamp} | DROP {COLUMN champ I CONSTRAINT nomindex} } • Permet d’ajouter / supprimer les attributs et les contraintes • les indexes notamment

DROP (MSAccess) • La syntaxe DROP {TABLE table | INDEX index ON table | PROCEDURE procédure | VIEW vue} • Permet de supprimer : • une table • un index • une vue • une procédure • Un choix hors standard SQL de MS

Contraintes référentielles mutuelles en SQL-2 • On utilise CREATE SCHEMA ou combinaison de CREATE TABLE et ALTER TABLE CREATE SCHEMA AUTHORIZATION Witold CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT REFERENCES t2(c1)) CREATE TABLE t2 (c1 INT PRIMARY KEY, c2 INT REFERENCES t1(c1))

La table nommée P_1 est en fait la table P Les clauses CASCADE n ’existent qu’en QBE de MsAccess

SQL : Un Langage Relationnel (08-09)