Some Essential MySQL Queries

Here”s a list of queries that I found myself using very often and that save me a lot of development time. I hope you can benefit from them as well

.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. 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”));

4. 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.

5. 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();

6. 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”

I hope you learned something new today which will make your everyday life as a developer just a bit easier. Thank you for reading!

Selecting random record from MySQL database table.

The simplest way of selecting random rows from the MySQL database is to use “ORDER BY RAND()” clause in the query.

Solution 1 [SQL]
SELECT * FROM `table` ORDER BY RAND() LIMIT 0,1;

The problem with this method is that it is very slow. The reason for it being so slow is that MySQL creates a temporary table with all the result rows and assigns each one of them a random sorting index. The results are then sorted and returned.

There are several workarounds to speed things up.

10 Useful articles about Database design

This is a list of ten useful articles about database design which includes some tips and practical suggests to help you to design quickly databases for your web projects.

The list includes some tips to define relationships-entities model, common database design mistakes, database normalization, how to use PHP and SQL to create tables and relationships and a correct approach to define relationships between tables.