Archives de catégorie : SQL

Le SQL (Structured query language ou en français Langage Structuré de Requêtes) est un pseudo-langage informatique, de type requête, standard et normalisé, destiné à interroger ou à manipuler une base de données relationnelle avec :
un langage de définition de données (DDL, Data definition language)
un langage de manipulation de données (DML, Data manipulation language), la partie la plus courante et la plus visible de SQL
un langage de contrôle de données (DCL, Data control language)
un langage de contrôle des transactions (TCL, Transaction control language)
et d’autres modules destinés notamment à écrire des routines (procédures, fonctions ou déclencheurs) et interagir avec des langages externes
C’est un langage puissant, important et beaucoup utilisé, il possède de nombreuses possibilités trop souvent méconnues des programmeurs. Mon but est de partir à la chasse de ces fonctionnalités, de les comprendre, et de coucher tout ça sur papier billet.

Débuter avec SPARQL et le Web sémantique

Publié dans SQL | Laisser un commentaire
26 Oct 2012

Faire ses premiers pas dans le monde merveilleux (??) du Web sémantique, c’est un peu comme faire le premier par sur la Lune… il faut au mois parcourir 384 460 km avant de commencer à trotter joyeusement ! Histoire de rendre vos premiers kilomètres un peu moins difficiles, voici quelques ressources qui m’ont été bien utiles autour du langage de requête sémantique SPARQL.

Vocabulaire utile

  • SPARQL : SPARQL Protocol and RDF Query Language
  • These queries are executed against RDF datasets, consisting of RDF graphs (d’après SPARQL par l’exemple)
  • A SPARQL endpoint accepts queries and returns results via HTTP (idem)
    • Generic endpoints will query any Web-accessible RDF data
    • Specific endpoints are hardwired to query against particular datasets

Continuer la lecture →

Gestion rapide des dates sous Oracle

Publié dans SQL | Laisser un commentaire
07 Juil 2010

En ce moment j’ai l’occasion de faire un petit peu de SQL à la mode Oracle. Et je viens de passer quelques minutes avec les dates… Le plus simple est de montrer 2 exemples d’utilisation.

Transformer une chaîne de caractères en date

SELECT * FROM ma_table WHERE date = to_date('18/03/1988', 'DD/MM/YYYY')

Transformer une date en chaîne de caractères

SELECT to_char(date, 'DD/MM/YYYY') AS maDate FROM ma_table

Format de date

A l’heure où j’écris ces lignes, nous sommes le 6 juillet 2010 et il est 17h18 et des brouettes. Je vais m’en servir pour les exemples.

Continuer la lecture →

Mysql et la recherche sur plusieurs champs : avec CONCAT ou CONCAT_WS

Publié dans SQL | 3 commentaires
27 Oct 2009

Après avoir parlé un peu de la recherche en MySQL via LIKE puis REGEXP, et avant d’aller plus loin, parlons un peu de la recherche dans plusieurs champs à la fois. Eh oui, c’est possible grâce à CONCAT, ou plutôt CONCAT_WS.
Prenons une table contenant les billets d’un blog, et donc possédant les champs id, titre et contenu. Une recherche classique cherchera un mot clef dans le titre et dans le contenu du billet. Pour cela, on pourrait utiliser OR, mais un CONCAT qui concatène (i.e. met l’un à la suite de l’autre) le contenu de titre et de contenu, c’est pas mal aussi ! Exemple à l’appui :

SELECT * FROM billet WHERE CONCAT(titre, contenu) LIKE "%mot%clef%";

On peut ainsi concaténer autant de champs que l’on souhaite. Un petit détails cependant, si l’un de ces champs est NULL, le résultat renvoyé est NULL. Ce qui implique qu’un article sans titre dont le contenu correspond au mot clef, ne sera pas récupérée. Même si l’exemple est débile, c’est un peu dommage. Pour éviter cela, il convient d’utiliser CONCAT_WS qui fonctionne de la même manière, sans se soucier des champs vides.
Le deuxième exemple ci-dessous est certes moins compréhensible, mais il illustre ce dont nous venons de parler, et explique bien l’intérêt d’utiliser CONCAT plutôt que OR dans certains cas.

SELECT * FROM billet WHERE CONCAT_WS(date, ' ', heure, 'h', minute) LIKE "10/11/2009 23h30";

Bien sûr, il est possible d’utiliser la commande REGEXP au lieu de LIKE ! Bref, vive la concaténation !

Puisque ses rétroliens ne semblent pas fonctionner, merci à CrazyCat de G33k-zone pour m’avoir fait découvrir cette commande SQL bien utile.

Mysql et la recherche : avec REGEXP

Publié dans SQL | 6 commentaires
14 Oct 2009

Dans un précédent billet, on a vu comment effectuer une recherche assez poussée en MySQL avec LIKE. Mais LIKE n’est vraiment rien comparée à ce que l’on peut faire avec REGEXP OU RLIKE (RLIKE est un alias pour REGEXP. Il a été créé pour un souci de similitude avec mSQL). Et en même temps, je trouve que LIKE suffit la plupart du temps… REGEXP permet d’utiliser les expressions régulières pour retrouver une chaine de caractères dans une table SQL. Il existe 2 types de regex en PHP (petit nom des expressions régulières ^^) :

  • PCRE : issue du langage Perl, ces regex sont réputées difficiles, mais sont rapides et performantes
  • POSIX : mise en avant avec le PHP, ces regex se veut un peu plus simple que les PCRE, elles sont néanmoins moins performantes. MySQL utilise ce type de regex.

Sur son tutoriel de PHP, M@téo21 explique le fonctionnement des regex PCRE pour PHP. Si on comprend les PCRE, on comprend les POSIX, donc si vous n’y connaissez rien en regex, je vous conseille de lire son cours à ce sujet, il est particulièrement bien fait : Cours sur les regex de M@téo21.

Utiliser REGEXP

Une fois que l’on maitrise les regex (voir ci-dessous), utiliser REGEXP n’est pas un problème. Un exemple :

SELECT * FROM table WHERE champ REGEXP "<regex>"

Continuer la lecture →

SQL et RENAME

Publié dans SQL | Laisser un commentaire
07 Oct 2009

Cela fait plusieurs fois que je cherche à renommer une table en ligne de commandes, et plusieurs fois que je re-re-regarde la doc MySQL sur RENAME. Dans l’espoir que ça rentre, et par ne pas avoir à re-re-re-rechercher la prochaine fois, voici comment fonctionne cette commande. L’idée est de renommer la table « chaise » pour l’appeler « chaise_pliante » (hum, original !).
Pour cela, rien de plus simple :

RENAME TABLE chaise TO chaise_pliante;

Pour renommer plusieurs tables à la fois, il suffit de séparer les blocs ancien_nom TO nouveau_nom par une virgule.

RENAME TABLE chaise TO chaise_pliante, tableau TO tableau_pliante;

Mais cette commande est aussi utile pour déplacer une table d’une base de données dans une autre. Pour cela, il faut faire précéder le nom de la table par le nom de la base de données. Donc en résumé, on a :

RENAME TABLE [ancien_nom_db.]ancien_nom_table TO [nouveau_nom_db.]nouveau_nom_table;

A noter que cela fonctionne aussi pour les vues (tant qu’on ne change pas de base de données) et pour les tables temporaires. Et on peut aussi faire cela avec ALTER TABLE :

ALTER TABLE ancien_nom RENAME nouveau_nom;

Bon, avec un peu de chance, je crois que ça va me rester !

Import/Export MySQL en ligne de commandes

Publié dans SQL | Laisser un commentaire
01 Oct 2009

Dans un précédent billet où j’expliquais mes déboires avec les grosses bases de données (on y trouvait aussi des conseils pour ne pas s’arracher les cheveux), j’ai expliqué comment importer une base de données MySQL à partir d’un fichier SQL en ligne de commandes grâce à source ou mysql. Aujourd’hui, j’ai découvert ce que je cherchais depuis pas mal de temps : exporter une base de données MySQL dans un fichier SQL en ligne de commandes, grâce à mysqldump. !

Importer un fichier SQL en ligne de commandes

J’ai une table de 3 millions de tuples (très très grosses quoi), que je souhaite importer dans ma base de données. Pour se faire, j’ai un fichier SQL assez volumineux contenant les requêtes d’insertions qui vont bien. Utiliser PhpMyAdmin serait long et il y a des risques que cela ne fonctionne pas si le fichier est trop gros. La solution consiste à utiliser des lignes de commandes, comme ça, pas de risques d’arrêts inopinés (ou presque) et c’est vraiment plus rapide.
Continuer la lecture →

Mysql et la recherche : avec LIKE

Publié dans SQL | Un commentaire
22 Sep 2009

Souvent pour un moteur de recherches sur un site, on propose à l’utilisateur d’entrer un mot clef que l’on cherchera ensuite dans une base de données pour renvoyer les résultats le contenant. Ce billet, et 2 autres qui le suivront, vont présenter 3 méthodes pour effectuer ce type de recherche, en commençant par l’utilisation de LIKE. La première méthode pour effectuer une recherche avec Mysql est d’utiliser LIKE sur un champ texte.

SELECT * FROM table WHERE message LIKE "mot clef";

Cette requête renverra tous les tuples dont le champ message contient exactement la valeur « mot clef », quelque soit la casse. Pour que le résultat dépende de la casse, il faut utiliser LIKE BINARY :

SELECT * FROM table WHERE message LIKE BINARY "Mot Clef";

Mais LIKE permet de faire bien plus que cela. Pour recherche le mot « mot clef » qui se trouverait en plein milieu d’une phrase, on va utiliser le pourcentage ( % ). Le pourcentage est un joker qui correspond à « ou ou plusieurs caractères, n’importe lequel » ou à « pas de caractère ».

SELECT * FROM table WHERE message LIKE "%mot clef%";

Par exemple, les phrases : « Bonjour, j’aime ce joli mot clef tout bleu » ou « Salut beau mot clef » seront renvoyé par cette requête. Mais pas « Salut jolis mots clefs ». Dans le même style, on peut utiliser l’underscore ( _ ), c’est un autre joker qui correspond à « un et un seul caractère, n’importe lequel ».

SELECT * FROM table WHERE message LIKE "%mot_ clef_";

Continuer la lecture →

Les requêtes imbriquées c’est (parfois) le pied

Publié dans SQL | Laisser un commentaire
12 Août 2009

J’ai une table annonce1 contenant 10 000 tuples, et une autre annonce2 qui en contient aussi 10 000. Tous les tuples sont différents. Le but ? Fusionner ces 2 tables. Export/Import classique créé des problèmes de Primary Key qui se dupliquent (mon ID est en auto_increment).
Moralité :

INSERT INTO annonce (ann_surface, ann_prix)
	SELECT ann_surface, ann_prix
	FROM annonce2
	WHERE ann_surface>150 AND ann_ville!=0;

Une petite requête SQL imbriquée (embedded) et pof le tour est joué !
Deux choses importantes à remarquer :

  • La syntaxe : le VALUES n’apparait pas ici. (moi je m’étais fait avoir)
  • Inutile de préciser de préfixes même si le champ des tables ont le même nom. D’ailleurs j’ai testé avec annonce1 a1 (a1.ann_surface, ... et cela n’a pas fonctionné. J’en déduis que les préfixes doivent être interdit avec la commande INSERT.

La doc MySQL n’est pas avare à propos de la commande INSERT, pas plus que les commentaires. Profitons-en !

Comment importer une grosse base de données

Publié dans SQL | Un commentaire
03 Août 2009

Ce billet aurait aussi pu se nommer « Vive la ligne de commandes ! ».
Je pense arriver au bout de mes peines avec ma grosse tables de 3 millions de tuples (qui n’en fait d’ailleurs toujours pas 3 millions, alors j’espère que ça tiendra le coup quand tous les tuples seront là), et j’ai notamment pas mal galéré pour la transférer d’une base de données à une autre. (local -> serveur de développement) Si on utilise PHP, ou PhpMyAdmin, pour transférer sa BDD, on se retrouve vite avec de très long temps d’attente et avec une machine qui rame jusqu’à la mort. Quelques idées pour survivre entier à ce calvaire :
sql

  • Préférer le transfert à l’aide de fichiers. Car le copier/coller de requêtes fonctionnent pour de petites tables, mais ça fait très vite ramer votre machine de course pour de grosses tables. Il suffit d’exporter les tables de son choix via PhpMyAdmin (à l’accueil d’une base > exporter : pour exporter les tables de son choix. A l’accueil d’une table > exporter : pour exporter directement la table en question), puis dans les importer via PhpMyAdmin, ou en ligne de commandes. Il vaut mieux éviter de cocher la case « Insertion Complète » qui créé une seule requête INSERT INTO table (val1, val2) VALUES ('blabla', 'bloublou'), ('blibli', 'bleble'), ..., ('blublu', 'bloblo'); ce qui peut être un peu trop lourd. Mieux vaut créer autant de requêtes qu’il y a d’insertions dans ce cas. (le mieux serait de faire une requête pour une trentaine d’insertions, c’est vraiment plus rapide, mais cela oblige à coder tout ça soit même. Edit: apparemment, il faut choisir « Insertions Etentdues ».)
  • Préférer l’utilisation des lignes de commandes. Pour plusieurs raisons : plus de problèmes avec les timeout de MySQL ou de PHP puisqu’on effectue une commande à la fois. (sauf si on commande est trop lourde. Dans le cas d’une insertion complète par exemple), on contrôle réellement tout ce qui se passe en temps réel et on peut réellement tout arrêter en temps réel. (suffit de fermer le terminal quoi !), on ne passe par aucun intermédiaire et c’est donc vraiment plus rapide.

Continuer la lecture →

Optimiser ses requêtes MySQL

Publié dans SQL | 4 commentaires
10 Juil 2009

Je travaille actuellement sur une table MySQL qui va contenir près de 7 millions de tuples (avec une quinzaine de colonnes chacun bien sûr, ce ne serait pas drôle sinon !), il se trouve qu’elle en contient déjà près de 3 millions, et que je suis obligé de faire une vingtaine de requêtes différentes dans cette table pour chaque page et que, par conséquent, le site qui l’utilise plante à fond. La mémoire utilisable par MySQL est dépassée. Alors certes, on peut modifier la taille de la mémoire utilisable par MySQL (et il faudrait aussi sûrement modifier la durée maximum d’exécution d’un script PHP), mais le but c’est quand même de charger des pages en moins de 30 secondes. J’ai donc cherché des réponses sur le Web et auprès des développeurs de ma boîte. Voici donc quelques points que j’ai pu retenir.

Structure de la table

Il vaut mieux éviter, sauf si c’est nécessaire, l’utilisation de champ NULL par défaut. En effet, comme on peut le voir sur cet article d’Apprendre PHP NULL est une valeur spéciale qui nécessite un traitement spécial, MySQL ne peut pas le tester avec un égal et ne peut le comparer à la chaine vide ou à 0, il est obligé d’utiliser IS ou le symbole équivalent <=>. NULL par défaut est d’autant plus à bannir pour les index. Utiliser des colonnes avec le meilleur type et la meilleure taille possible. Un prénom n’a par exemple par besoin d’être en VARCHAR(255), un VARCHAR(20) suffit plus que largement. Une bonne technique pour optimiser ses tables est d’utiliser PROCEDURE ANALYSE(). On créé ses tables au feeling (je parle juste de la structure, vous ne couperez pas à la normalisation tout ça, héhé), on les remplie avec un contenu à peu près définitif est on utilise une requête du style :

SELECT id, titre, message FROM blog PROCEDURE ANALYSE()

MySQL va nous répondre un joli petit array avec la taille max et min de chaque colonne id, titre et message, quelques autres informations intéressantes et surtout une proposition de type(taille) optimisée à votre table. Encore une fois, c’est l’article d’Apprendre PHP qui en montre un exemple vraiment parlant.
Continuer la lecture →