Exemples: Base de données

SQL

  • Start the database: first create an empty file, then start the sqlite3 program 
    $ touch catalogue.sqlite
$ sqlite3 catalogue.sqlite
  • CREATE TABLE 
    CREATE TABLE `category` (
 `name` VARCHAR( 100 ) NOT NULL ,
 `categoryID` integer primary key autoincrement 
);

CREATE TABLE `article` (
 `articleID` integer primary key autoincrement ,
 `name` VARCHAR( 100 ) NOT NULL ,
 `vatID` INT NOT NULL ,
 `categoryID` INT NOT NULL ,
  `price` decimal(10,2) NOT NULL DEFAULT '0.00',
  `description` longtext NOT NULL
);

CREATE TABLE `vat` (
 `vatID` integer primary key autoincrement ,
 `rate` DECIMAL(10,2) NOT NULL  
) ;

--
-- Contenu de la table `article`
--

INSERT INTO `article` (`articleID`, `name`, `vatID`, `categoryID`, `price`, `description`) VALUES
(1, 'Le rouge et le noir', 1, 1, 1.50, 'Le chef d''oeuvre de Stendal'),
(2, 'Mercedes Class E', 1, 2, 100000.00, 'La même mercedes que celle de Lady Diana'),
(6, 'Le tour du Monde en 80 jours', 2, 1, 500.00, 'Edition originale'),
(5, '20 Minuten', 1, 1, 1.90, '20 minuten (Someone may pay for it)'),
(7, 'Laptop', 1, 0, 1500.00, '');

--
-- Contenu de la table `category`
--

INSERT INTO `category` (`name`, `categoryID`) VALUES
('livres', 1),
('voitures', 2);


--
-- Contenu de la table `vat`
--

INSERT INTO `vat` (`vatID`, `rate`) VALUES
(1, 8.00),
(2, 3.50);
    • Vous pouvez downloader la base de données Catalogue : catalogue.sqlite
  • .tables et .schema tableName 
    sqlite> .tables
article   category  vat 
sqlite> .schema vat;
CREATE TABLE `vat` (
 `vatID` integer primary key autoincrement ,
 `rate` DECIMAL(10,2) NOT NULL  
);
  • SELECT 
    sqlite> select * from vat;
1|8
2|3.5

sqlite> select name, price from article;

sqlite> select name, price from article 
    -> where articleID=1;
Le rouge et le noir|1.5
Mercedes Class E|100000
20 Minuten|1.9
Le tour du Monde en 80 jours|500
Laptop|1500

sqlite> select article.name, vat.rate, article.price 
    -> from article, vat where article.vatID= vat.vatID;
Le rouge et le noir|8|1.5
Mercedes Class E|8|100000
20 Minuten|8|1.9
Le tour du Monde en 80 jours|3.5|500
Laptop|8|1500
    
sqlite> select article.name, vat.rate, article.price, 
    -> article.price*(1+vat.rate/100) as priceWithVAT 
    -> from article, vat where article.vatID= vat.vatID;
Le rouge et le noir|8|1.5|1.5
Mercedes Class E|8|100000|100000
20 Minuten|8|1.9|1.9
Le tour du Monde en 80 jours|3.5|500|517.5
Laptop|8|1500|1500
    
sqlite> select vatID, count(vatID) 
    -> from article GROUP BY vatID;
  • SELECT avec un JOIN () 
    select article.name, vat.rate, article.price 
       from article inner join vat 
       on article.vatID= vat.vatID;

select article.name  from article left join vat 
       on article.vatID= vat.vatID 
       where vat.rate is null;

    • Base de données des emails

    Downloadez la base de donnée base_emails.sqlite. Vous trouvez les commandes executées pour initialiser la base ici: base_emails.sql
    • Selectionne toutes les femmes 
      SELECT * FROM `persons` WHERE gender =1;
    • Selectionner tous les mails 
        SELECT * FROM `emails` LEFT JOIN persons 
   ON emails.from_person_id=persons.person_id 
   WHERE 1 ;
    • Tous les mails venant de Emmanuel 
        SELECT * FROM `emails` LEFT JOIN persons 
   ON emails.from_person_id=persons.person_id 
   WHERE persons.first_name="Emmanuel";
    • Les mails de Emmanuel de novembre 2016 
      SELECT * FROM `emails` LEFT JOIN persons 
  ON emails.from_person_id=persons.person_id 
  WHERE persons.first_name="Emmanuel" 
  AND month(emails.timestamp)=11 
  AND year(emails.timestamp)=2016 ;
    • Les mails vers Emmanuel 
      SELECT * FROM `emails` 
  left join to_emails_persons 
    on emails.email_id=to_emails_persons.email_id 
  left join persons 
     on to_emails_persons.person_id_to=persons.person_id 
  WHERE 1 ;
BACK TO TOP © 2014-2021 Emmanuel Benoist