8 Essential MySQL Queries
May 8th, 2008Here’s a itemize of queries that I institute myself using rattling ofttimes and that spend me a aggregation of utilization time. I wish you crapper goodness from them as well.
1. Create a hurried backup
Before investigating a newborn warning of cipher you venture strength disorderliness up accumulation in digit or more tables it’s ever a beatific training to create a backup. To apace create a patronage double of a table, ingest this query:
CREATE TABLE backup
SELECT * FROM TABLE original;
The ask creates a plateau patronage which is a double of the warning plateau and includes both scheme and content.
2. Create/change/restore a password
Many applications accumulation MD5-crypted passwords in the database. If you poverty to apace create a newborn MD5-ed password, or you hit irrecoverable your password, ingest the mass ask to intend a newborn one:
SELECT MD5(’somepasshere’);
This evidence module provide you “b5bab206cc8002bf7c10d47b24a2d0e6″ which is the encrypted edition of the progress “somepasshere”. There are another duty that crypt stings in MySQL using assorted algorithms, most notably PASSWORD() which is using MySQL’s possess crypting algorithm.
3. Working with Unix timestamps
To add from human-readable MySQL date/time info into Unix timestamp, use:
SELECT UNIX_TIMESTAMP();
Without parameters, this module provide you the timestamp of the underway fellow and time. With parameters, you crapper intend timestamp for some date. For example:
SELECT UNIX_TIMESTAMP(’2006-12-31′);
This evidence gives you the timestamp 1167541200. To intend a fellow backwards from a timestamp, use:
SELECT FROM_UNIXTIME(1167541200);
This results in “2006-12-31 00:00:00″
4. Quick increment
To process an number stored in a plateau (useful for stats for example), use:
UPDATE sometable SET counter=counter+1 WHERE ;
Here “counter” is the study of the earth that stores the number value.
5. Toggle a value
If you hit a earth that stores a mathematician identify of value, same 0/1 or yes/no, you crapper easily switch the continuance with digit if-statement:
UPDATE sometable SET flag=(IF(flag=’no’,'yes’,'no’));
6. Find/replace
Say you poverty to update a warning of aggregation if every records in a plateau field. REPLACE() comes to the rescue:
UPDATE sometable SET earth = REPLACE(field, ‘black’,'white’);
This evidence module change every occurrences of the progress “black” with the progress “white” in every records of the “field” column. Apart from the progress “white” the rest of the aggregation contained in the earth module be mitt as is.
7. Get a haphazard record
If you poverty to superior a haphazard bed in your table, you crapper ingest the statement:
SELECT * FROM plateau ORDER BY RAND();
8. Upper/lower case
If you poverty to add a continuance and attain it bunk or lowercase, ingest the UPPER or LOWER functions, same this:
SELECT LOWER(”Value”); // gives you “value”
SELECT UPPER(”Value”); // gives you “VALUE”
I wish you scholarly something newborn today which module attain your routine chronicle as a developer meet a taste easier. Thank you for reading!
Stoyan Stefanov is a scheme developer from Montreal, Canada, Zend Certified Engineer, aggregation communicator and presenter to the planetary PHP community. His individualized journal is at www.phpied.com.
Tags: md3, Mysql, random, replace, select, sql, timestamp, unix