Category Archives: MySQL

MYSQL – Useful commands you must know – Part-1

To know the current MYSQL version:
SELECT version();

To format and formulate a float value:
FORMAT(SUM(amount) * 0.6, 2)
The above line sums up entire amount value, cut it down to 60% of total value and returns value precised up to 2 decimal places.

To get the size of database in MB
SELECT table_schema “Data Base Name”, sum( data_length + index_length) / 1024 / 1024
“Data Base Size in MB” FROM information_schema.TABLES GROUP BY table_schema ;

mysql-logo

Updating two columns with a subquery in MySQL

mysql-logo

Sometimes you wanted to update columns of one MySQL table by quering another MySQL table and getting a column value from the later table. For example you have a ‘students’ table and you wanted to create a new field ‘totalMarks’ in it and wanted to fill or update by taking sum of the ‘marks’ field from another table named ‘marks’. Here comes the use of subquery to update column. Continue reading

How to delete duplicate MySQL records with duplicate field value (as ID)

For some reason i had duplicate entries in my WP database. The ID field in wp_users table was tempered with and was changed to Non Auto-Increment to insert records on the will. I deleted all ID=null records manually and then tried to change it to AUTO INCREMENT but it came up with Duplicate Entry error and then i found that it had duplicated/multiple entries with same IDs as well. Here’s the remedy i had to apply in order to delete those duplicate entries and change ID field to AUTO INCREMENT once again. Continue reading