background preloader

Sql-Base de données

Facebook Twitter

SQL cheat sheet. If there’s one thing that almost no application can live without, it’s the database.

SQL cheat sheet

The pillar that holds the data, the ultimate source of the data conflict resolution, the storage that survives power outages. Working with a database correctly is the key to successful application design. That is why we’re dedicating this cheat sheets to the important topic of SQL. In the past we’ve released many other cheat sheets including: Now it’s time to turn our attention to the crown jewel of the declarative programming languages: SQL. Download and print out this cheat sheet so you can use it whenever you need it. I’d like to thank Lukas Eder for early feedback on this cheat sheet, including advice what to include and telling me that it’s actually a good idea to make an SQL cheat sheet. This cheat sheet is brought to you by XRebel, a tool to remind you about your app performance when you are actually working on it, not later when your clients think it is already slow. Basic SQL queries Conclusion.

MySQL Uppercase Words (or ucfirst & ucwords) I recently needed to clean up a MySQL Table which contained people's names.

MySQL Uppercase Words (or ucfirst & ucwords)

Upon searching the MySQL commands, I was surprised to find there was no equivalent of PHP's ucfirst or ucwords. There were commands to convert entire strings into upper or lower case, but not just the first letter. However, I quickly found a simple script to make a word uppercase: UPDATE table SET field=CONCAT(UCASE(SUBSTRING(field, 1, 1)),LCASE(SUBSTRING(field, 2))); What if the name needs two capitals? But then I found an issue; what if someone has a hyphen in their name (like O'Reily) or have a double barreled name (like Smith-John)? MySQL 5.7 Reference Manual. Calculs dans une base de données MySQL. Cet article présente les différentes façon de faire des calculs dans le langage MySQL.

Calculs dans une base de données MySQL

Ces fonctions de calcul sont appelées agrégation des données. Ainsi, une commande permet de connaître le nombre de valeurs dans une colonne spécifiée, la moyenne (par exemple sur une colonne de commande, la moyenne des commandes), la somme, l’écart-type… Les principaux calculs en langage MySQL : 1 – Moyenne2 – Effectifs3 – Écart-type4 – Minimum5 – Maximum6 – Somme Avant de commencer, vous devez vous connecter sur la base de données dans laquelle vous souhaitez effectuer les opérations. MySQL 5.7 Reference Manual. 13.5.1 String Comparison Functions.

Expr LIKE pat [ESCAPE 'escape_char'] Pattern matching using an SQL pattern.

13.5.1 String Comparison Functions

Returns 1 (TRUE) or 0 (FALSE). If either expr or pat is NULL, the result is NULL. The pattern need not be a literal string. For example, it can be specified as a string expression or table column. MySQL 5.7 Reference Manual. A select_expr can be given an alias using AS alias_name.

MySQL 5.7 Reference Manual

The alias is used as the expression's column name and can be used in GROUP BY, ORDER BY, or HAVING clauses. Guide utilisateur — documentation phpMyAdmin 4.7.0-dev. 13.2.5 INSERT Syntax. INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [(col_name,...)]

13.2.5 INSERT Syntax

{VALUES | VALUE} ({expr | DEFAULT},...),(...),... [ ON DUPLICATE KEY UPDATE col_name=expr [, col_name=expr] ... ] Or: INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name SET col_name={expr | DEFAULT}, ... [ ON DUPLICATE KEY UPDATE col_name=expr [, col_name=expr] ... ] INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [(col_name,...)] SELECT ... [ ON DUPLICATE KEY UPDATE col_name=expr [, col_name=expr] ... ] INSERT inserts new rows into an existing table.

You can use REPLACE instead of INSERT to overwrite old rows. Tbl_name is the table into which rows should be inserted. You can provide a comma-separated list of column names following the table name. Column values can be given in several ways: If you are not running in strict SQL mode, any column not explicitly given a value is set to its default (explicit or implicit) value. Cours et Tutoriels sur le Langage SQL.

DB Browser for SQLite. Injection SQL. Un article de Wikipédia, l'encyclopédie libre.

Injection SQL

Exemple[modifier | modifier le code] Considérons un site web dynamique (programmé en PHP dans cet exemple) qui dispose d'un système permettant aux utilisateurs possédant un nom d'utilisateur et un mot de passe valides de se connecter. Ce site utilise la requête SQL suivante pour identifier un utilisateur : SELECT uid FROM Users WHERE name = '(nom)' AND password = '(mot de passe hashé)'; L'utilisateur Dupont souhaite se connecter avec son mot de passe « truc » hashé en MD5. 13.6 Numeric Functions and Operators. MySQL 5.7 Reference Manual.