Magento userful SQL queries

Get sorted attributes for products

SELECT *
FROM  `eav_attribute` ea
LEFT JOIN  `catalog_eav_attribute` cea ON ea.attribute_id = cea.attribute_id
WHERE ea.`entity_type_id` =4
AND cea.`used_for_sort_by` =1
LIMIT 0 , 30; #the value of entity_type_id might be different, but it can be found in the table named `eav_entity_type`
Note: `eav_attribute` is the table of basic product attributes' settings, while `catalog_eav_attribute` is containing additional or advanced settings for product attributes. Most of the product attributes' settings could be found in these two tables.

Set all simple products invisible by SQL Query

update catalog_product_entity_int set value =1 where attribute_id = 102 and  entity_id in (select entity_id from `catalog_product_entity` where `type_id`= 'simple')

Set the position value for all of the products by SQL Query

update `catalog_category_product` set position=2000 where position!=1000;

How to re-install module database

Where in database does magento record the database set-up of the module?

All of the modules and their database set-up versions are recorded in the table named 'core_resource'.

The steps of re-installing a module

1. Disable the module in the app configuration file located in the folder of 'app/etc/modules/' by changing the value of 'active' from true to false.

2. Find the module set-up record in the database table of 'core_resource' and delete the record.

3. Refresh any page of magento site and the module will be newly installed.