Toolkit DEV
Data Definition Language
- Créer une base de données (un schéma)
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
1 |
CREATE SCHEMA website; |
- Supprimer une base de données (un schéma)
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name
1 |
DROP SCHEMA website; |
- Créer une table
CREATE TABLE [IF NOT EXISTS] tbl_name
(create_definition,…)
[table_option] …
1 2 3 4 5 |
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] …
1 |
<span style="color: #993300;"><strong>ALTER TABLE client ADD INDEX idx_nom(nom);</strong></span> |
- Supprimer une table
DROP TABLE [IF EXISTS]
tbl_name
1 |
<strong><span style="color: #993300;">DROP TABLE client;</span></strong> |
- Vider une table
TRUNCATE [TABLE] tbl_name
1 |
<span style="color: #993300;"><strong>TRUNCATE TABLE session;</strong></span> |
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}]
1 |
<strong><span style="color: #993300;">SELECT nom, age, sexe FROM website.client WHERE id_client = 357;</span></strong> |
1 |
<strong><span style="color: #993300;">SELECT count(*) as nbr_commande</span></strong> |
1 |
<strong><span style="color: #993300;"> FROM website.commande</span></strong> |
1 |
<strong><span style="color: #993300;"> WHERE date_cmd >= now() - interval 30 day</span></strong> |
1 |
<strong><span style="color: #993300;"> GROUP BY id_client ORDER BY nbr_commande</span></strong> |
1 |
<strong><span style="color: #993300;"> LIMIT 10;</span></strong> |
- Insérer des données dans une table
INSERT [INTO] tbl_name [(col_name,…)] {VALUES} ({expr},…),(…),…
1 2 |
<span style="color: #993300;"><strong>INSERT INTO website.client (nom, prenom, sexe, job, age) VALUES ('DASINI', 'Olivier', 'H', 'Consultant MySQL', 34);</strong></span> |
- Modifier les données d’une table
UPDATE tbl_name
SET col_name1={expr1} [, col_name2={expr2}] …
[WHERE where_condition]
1 |
<span style="color: #993300;"><strong>UPDATE website.client SET nom='toto', age=age+1 WHERE id_client=42;</strong></span> |
- Effacer les données d’une table
DELETE FROM tbl_name
[WHERE where_condition]
1 |
<span style="color: #993300;"><strong>DELETE FROM website.client WHERE id=42;</strong></span> |
- Remplacer un enregistrement
REPLACE [INTO] tbl_name [(col_name,…)] {VALUES } ({expr},…),(…),…
1 2 |
<span style="color: #993300;"><strong>INSERT INTO website.client (id_client, nom, prenom, sexe, job, age) VALUES (123, 'DASINI', 'Olivier', 'H', 'Expert MySQL', 34);</strong></span> |
- 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,…]
1 2 3 4 5 6 7 |
<span style="color: #993300;"><strong>LOAD DATA INFILE '/tmp/produit.csv' INTO TABLE produit FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 2 LINES; </strong></span> |
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}
1 |
<span style="color: #993300;"><strong>START TRANSACTION;</strong></span> |
1 2 3 4 |
<span style="color: #993300;"><strong>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;</strong></span> |
1 |
<span style="color: #993300;"><strong>COMMIT;</strong></span> |