Toolkit DEV

Data Definition Language

  • Créer une base de données (un schéma)

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name

CREATE SCHEMA website;
  • Supprimer une base de données (un schéma)

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

DROP SCHEMA website;
  • Créer une table

CREATE TABLE [IF NOT EXISTS] tbl_name
(create_definition,…)
[table_option] …

CREATE TABLE client (
   id_client INT NOT NULL,
   nom CHAR(30) DEFAULT '',
   PRIMARY KEY (id_client)
) ENGINE=MyISAM;
  • Modifier la structure d’une table

ALTER TABLE tbl_name
alter_specification [, alter_specification] …

ALTER TABLE client ADD INDEX idx_nom(nom);
  • Supprimer une table

DROP TABLE [IF EXISTS]
tbl_name

DROP TABLE client;
  • Vider une table

TRUNCATE [TABLE] tbl_name

TRUNCATE TABLE session;

Data Manipulation Language

  • Lire les données d’une table

SELECT select_expr, …
[FROM table_references
[WHERE where_condition]
[GROUP BY {col_name}
[HAVING where_condition]
[ORDER BY {col_name}  [ASC | DESC], …]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]

SELECT nom, age, sexe FROM website.client WHERE id_client = 357;
SELECT count(*) as nbr_commande
    FROM website.commande
    WHERE date_cmd  >= now() - interval 30 day
    GROUP BY id_client ORDER BY nbr_commande
    LIMIT 10;
  • Insérer des données dans une table

INSERT [INTO] tbl_name [(col_name,...)]  {VALUES} ({expr},…),(…),…

INSERT INTO website.client (nom, prenom, sexe, job, age)
    VALUES ('DASINI', 'Olivier', 'H', 'Consultant MySQL', 34);
  • Modifier les données d’une table

UPDATE tbl_name
SET col_name1={expr1} [, col_name2={expr2}] …
[WHERE where_condition]

UPDATE website.client SET nom='toto', age=age+1 WHERE id_client=42;
  • Effacer les données d’une table

DELETE FROM tbl_name
[WHERE where_condition]

DELETE FROM website.client WHERE id=42;
  • Remplacer un enregistrement

REPLACE [INTO] tbl_name [(col_name,...)] {VALUES } ({expr},…),(…),…

INSERT INTO website.client (id_client, nom, prenom, sexe, job, age)
    VALUES (123, 'DASINI', 'Olivier', 'H', 'Expert MySQL', 34);
  • Importer des données dans une table

LOAD DATA INFILE ‘file_name’
[REPLACE | IGNORE]
INTO TABLE tbl_name
[{FIELDS} [TERMINATED BY 'string']
[ ENCLOSED BY 'char']
]
[LINES
[STARTING BY 'string']
[TERMINATED BY 'string']
]
[IGNORE number LINES]
[(col_name_or_user_var,...)]
[SET col_name = expr,...]

LOAD DATA INFILE '/tmp/produit.csv' INTO TABLE produit
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 2 LINES;

Transaction Control Language

  • Démarrer une transaction

START TRANSACTION

  • Valider une transaction

COMMIT

  • Annuler une transaction

ROLLBACK

  • Paramétrer le niveau d’ »auto validation » (autocommit)

SET autocommit = {0 | 1}

START TRANSACTION;
INSERT INTO client (id_client, 'nom', 'prenom', id_vendeur)
    VALUES (333, 'Sa', 'Kila', 21);
UPDATE vente SET vente=vente+1 WHERE id_vendeur=21;
DELETE FROM client_en_attente WHERE id=333;
COMMIT;