8 Essential MySQL Queries
Here's a list of queries used very often and that save a lot of development time.
1. Create a quick backup
Before testing a new
piece of code you suspect might mess up data in one or more tables it's
always a good practice to create a backup. To quickly create a backup
copy of a table, use this query:
CREATE TABLE backup SELECT * FROM TABLE original;
The query creates a table backup which is a copy of the original table and includes both structure and content.
2. Create/change/restore a password
Many
applications store MD5-crypted passwords in the database. If you want
to quickly create a new MD5-ed password, or you have forgotten your
password, use the following query to get a new one:
SELECT MD5('somepasshere');
This
statement will give you "b5bab206cc8002bf7c10d47b24a2d0e6" which is the
encrypted version of the string "somepasshere". There are other
function that crypt stings in MySQL using different algorithms, most
notably
PASSWORD()
which is using MySQL's own crypting algorithm.
3. Working with Unix timestamps
To
convert from human-readable MySQL date/time format into Unix timestamp,
use:
SELECT UNIX_TIMESTAMP();
Without parameters, this will give you
the timestamp of the current date and time. With parameters, you can
get timestamp for any date. For example:
SELECT UNIX_TIMESTAMP('2006-12-31');
This statement gives you the timestamp
1167541200. To get a date back from a timestamp, use:
SELECT FROM_UNIXTIME(1167541200);
This results in "2006-12-31 00:00:00"
4. Quick increment
To
increment an integer stored in a table (useful for stats for example),
use:
UPDATE sometable SET counter=counter+1 WHERE …;
Here "counter" is
the name of the field that stores the integer value.
5. Toggle a value
If
you have a field that stores a Boolean type of value, like 0/1 or
yes/no, you can easily toggle the value with one if-statement:
UPDATE sometable SET flag=(IF(flag='no','yes','no'));
6. Find/replace
Say you want to update a piece of text if all records in a table field. REPLACE() comes to the rescue:
UPDATE sometable SET field = REPLACE(field, 'black','white');
This
statement will replace all occurrences of the string "black" with the
string "white" in all records of the "field" column. Apart from the
string "white" the rest of the text contained in the field will be left
as is.
7. Get a random record
If you want to select a random row in your table, you can use the statement:
SELECT * FROM table ORDER BY RAND();
8. Upper/lower case
If you want to modify a value and make it upper or lowercase, use the UPPER or LOWER functions, like this:
SELECT LOWER("Value"); // gives you "value"
SELECT UPPER("Value"); // gives you "VALUE"