• Exemples en vrac

• CREATE
• DESC
• SELECT
• INSERT
• UPDATE
• DELETE
• ALTER
• DROP

• Données sous Oracle
• Données sous MySQL

L'utilisation la plus commune de CREATE est pour une table dans la base de données.

CREATE TABLE nom_table (
  nom_colonne1 type_colonne1 [DEFAULT expression1] [contrainte1],
  nom_colonne2 type_colonne2 [DEFAULT expression2] [contrainte2],
  ...
  nom_colonneN type_colonneN [DEFAULT expressionN] [contrainteN],
  [TABLESPACE nom_espace_tables]
);

CREATE TABLE [dbo].[Client] (
    [Id] [INT] IDENTITY(1,1) NOT NULL,
    [TIMESTAMP] [TIMESTAMP] NOT NULL,
    [Name] [VARCHAR](30) NOT NULL,
    [AddressId] [INT] NOT NULL,
    [CreationDate] [datetime] NOT NULL,
)

L'exemple suivant est donné avec des types pris en charge dans Oracle.

CREATE TABLE Etudiant (
  etd_id         NUMBER(10) PRIMARY KEY,
  etd_nom        VARCHAR2(15),
  etd_prenom     VARCHAR2(15),
  etd_adresse    VARCHAR2(30),
  etd_naissance  DATE
);

Autre exemple avec des contraintes (SQL Oracle) :

CREATE TABLE Documents (
  ISBN     NUMBER(10) CONSTRAINT ISBN_PK_Documents PRIMARY KEY,
  Titre    VARCHAR2(30),
  Auteur   VARCHAR2(30),
  TYPE     VARCHAR2(15) CONSTRAINT Type_CK_Documents CHECK (TYPE IN ('Livre', 'Video', 'CD', 'DVD', 'BD')),
  Editeur  VARCHAR2(30),
  Annee    DATE
);
 
CREATE TABLE Exemplaire (
  Numero   NUMBER(10),
  Etat     VARCHAR(5) CONSTRAINT Etat_CK_Exemplaire CHECK (Etat IN ('Neuf', 'Bon', 'Moyen', 'Mauvais')),
  ISBN     NUMBER(10) CONSTRAINT ISBN_FK_Documents REFERENCES Documents(ISBN),
                            CONSTRAINT ISBN_PK_Exemplaire PRIMARY KEY (ISBN, Numero)
);
 
CREATE TABLE Membre (
  Numero    NUMBER(10) CONSTRAINT Numero_PK_Membre PRIMARY KEY,
  Nom       VARCHAR2(30),
  Prenom    VARCHAR2(30),
  Adresse   VARCHAR2(30),
  Tel       VARCHAR2(10),
  FinAbonnement DATE    	
);
 
CREATE TABLE Emprunt (
  Numero      NUMBER(10) PRIMARY KEY,
  Membre      NUMBER(10),
  ISBN        NUMBER(10),
  Exemplaire  NUMBER(10),
  RenduLe     DATE,
  CONSTRAINT Exemplaire_FK_Emprunt FOREIGN KEY (Numero, Exemplaire) REFERENCES Exemplaire(ISBN, Numero),
  CONSTRAINT FK_Emprunt_Membre FOREIGN KEY (Membre) REFERENCES Membre(Numero)
);

Donne la description d'un objet, comme par exemple d'une table qui aura ses noms de champs et ses types affichés.

DESC nom_de_la_table[;]

INSERT [INTO] nom_de_la_table_cible [(liste_des_colonnes_visées)] 
{VALUES (liste_des_valeurs) | requête_select | DEFAULT VALUES }

INSERT INTO Documents VALUES(1, 'Mobidick', 'Alain Thérieur', 'Livre', 'Vieille edition', 1906);                                        
INSERT INTO Documents VALUES(2, 'Alice aux pays des merveilles', 'Yvon Pascroire','DVD','Paramont', 2001);                                      
INSERT INTO Documents VALUES(3, 'La légende de Zoro', 'Emesse Dosse', 'BD', 'Couler', 1987);                               

Lors d'une insertion, il est possible de déterminer les colonnes qui seront affectés.

INSERT INTO Clients (CNT_NOM, CNT_PRENOM) VALUES ('Jean', 'Jacques')

DELETE FROM nom_table
WHERE nom_colonne=valeur

Faire la suppression avec un JOIN, par exemple pour voir si l'enregistrement existe déjà (éliminer les enfants de doublons). Le Parent est actif ou non, s'il ne l'est pas, on vérifie son OriginalId et on supprime l'enfant dont un autre enfant aurait le même parent avec l'OriginalId pour éviter d'autres doublons ou bien des problèmes de clés dupliquées si on change le IdParent de l'enfant par l'OriginalId.

DELETE E
FROM Enfant E
INNER JOIN Parent P ON E.IdParent = L.Id
INNER JOIN Enfant E2 ON E.Id = E2.Id AND E2.IdParent = P.OriginalId
WHERE P.Actif = 0 AND E2.IdParent IS NOT NULL

UPDATE table_name
SET column1=VALUE, column2=value2,...
WHERE some_column=some_value

ALTER TABLE nomTable
{ADD COLUMN spécificationColonne|
DROP COLUMN nomColonne [RESTRICT|CASCADE]|
ADD spécificationContrainte|
DROP nomContrainte [RESTRICT|CASCADE]|
ALTER nomColonne SET DEFAULT valeurDéfaut|
ALTER nomColonne DROP DEFAULT}

Comme les insertions peuvent comprendre des dates et que les dates ont différents formats, il faut modifier ce format avant d'insérer d'autres enregistrements.

ALTER SESSION SET NLS_DATE_FORMAT='yyyy-mm-dd';

NLS → Natural Language Support, permet d'adapter la présentation des données en fonction du pays et de la langue.

Voici quelques exemples concrets qui peuvent aider à comprendre le SELECT.

La liste contenant le titre et l'auteur de tous les documents de la bibliothèque.

SELECT Titre, Auteur FROM Documents;

La liste des documents produits en 2007.

SELECT * FROM Documents WHERE EXTRACT (YEAR FROM Annee) = 2007;

La liste des livres édités entre 2000 et 2005.

SELECT * FROM Documents WHERE EXTRACT (YEAR FROM Annee) BETWEEN 2000 AND 2007;

La liste des documents dont le titre contient le mot « java », peu importe la casse.

SELECT * FROM Documents WHERE LOWER(Titre) LIKE '%java%';

La liste de tous les documents dont la bibliothèque ne dispose que d'un seul exemplaire.

1. Affiche seulement l'ISBN

SELECT ISBN
FROM Exemplaire
GROUP BY ISBN
HAVING COUNT(*)=1;
 
SELECT *
FROM Document
WHERE (
  SELECT COUNT(*)
  FROM Exemplaire
  WHERE Exemplaire.ISBN=Document.ISBN
) = 1;

La liste des exemplaires de documents dont l'état est « Mauvais ».

SELECT * FROM Exemplaires WHERE Etat = 'Mauvais';

La liste des éditeurs différents présents dans la bibliothèque.

SELECT DISTINCT Editeur FROM Documents;

Le nombre d'éditeurs différents présents dans la bibliothèque.

SELECT COUNT(DISTINCT Editeur) FROM Documents; 

Les nom, prénom et téléphone des membres dont l'abonnement a expiré.

SELECT Nom, Prenom, Tel FROM Membre WHERE SYSDATE > FinAbonnement;

Les nom, prénom et téléphone des membres dont l'abonnement a expiré depuis au moins une année.

SELECT Nom, Prenom, Tel FROM Membre WHERE FinAbonnement < SYSDATE - 365;

Les noms et prénom des membres qui ont emprunté plus de trois documents.

SELECT Nom, Prenom FROM Membre WHERE Numero IN (
  SELECT Membre FROM Emprunt GROUP BY Membre HAVING COUNT(Membre) > 3;
)
 
SELECT Nom, Prenom
FROM Membre
WHERE (
  SELECT COUNT(*)
  FROM Emprunt
  WHERE Emprunt.Membre = Membre.Numero
) > 3;

SELECT Nom, Prenom
FROM Membre
WHERE (
  SELECT COUNT(*)
  FROM Emprunt
  WHERE Emprunt.Membre = Membre.Numero
  GROUP BY Emprunt.ISBN
  HAVING COUNT(ISBN) >= 2
) > 0;

SELECT DISTINCT Auteur
FROM Document
WHERE NOT EXISTS (
  SELECT *
  FROM Exemplaire
  WHERE Exemplaire.ISBN = Document.ISBN
  AND Exemplaire.Etat <> 'Mauvais'
);

Syntaxe simple:

SELECT *
FROM TABLE
LIMIT 5

Certaines autres syntaxes SQL peuvent utiliser TOP (ex: SQL Server):

SELECT TOP 5 *
FROM TABLE;

LIKE dans SQL Server : http://msdn.microsoft.com/en-us/library/aa933232(SQL.80).aspx

DROP TABLE Emprunt;
DROP TABLE Membre;
DROP TABLE Exemplaire;
DROP TABLE Documents;

CREATE TABLE Client (
  noCLIENT INTEGER NOT NULL
    CHECK(noClient >0 AND noClient < 100000),
   nomClient VARCHAR(15) NOT NULL,
   noTéléphone VARCHAR(15) NOT NULL
)

• SQL Style Guide