To know the current MYSQL 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 ;
This is very commonly asked question: How do I show or list all the tables in a MySQL (or MariaDB) database using
mysql command line tool?
In brief, to show the tables in a MySQL database, run the command:
I have a
date field in my mysql database table. I want to replace year 2017 in this field to
2019 Following command would do this.
UPDATE races SET `date` =REPLACE( `date` , ‘2018’, ‘2019’ ) WHERE INSTR( `date` , ‘2018’ ) >0
Just saving it for reference here.
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
You can print the table structure of MySQL table in PHP using simple php script. All you need to do is to make a ‘describe table’ call and process it using php script, similar to one you use to print or get records from a mysql table. This can prove to be a very useful piece of code in, Continue reading
Why to consider changing database character set? Why do i need to do this? As to find a more straight answer please look at the image below, taken from one of my websites built in WordPress: Continue reading
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
Here’s the scenario.
I have two fields in my “listings” table with help of which i wanted to sort (order by) results. First field is “activation_date”. It is the activation date of a listing. Second one is “featured” which stores featured tag for a listing in a value of 1 or 0. Continue reading
While working on a web links directory i wanted to update categories table’s num_listings column with the count of listings under each category. The category id was stored something like this in listings table: Continue reading
Just a few notes on joining a MySQL table to itself to get unique combination of data stored in its fields.
Suppose we have a MySQL table named `keywords` having fields `word1`, `word2` and `word3`. Once we have got a few records inserted into this table we would want to show all possible combination of keywords stored in those fields. For example, a select query returns a following set of data: Continue reading