Handy queries for getting customer info in Magento

Select the customer whose email is test123@test.com

select * from customer_entity where email="test123@test.com";

Select customer accounts created after 2014-01-01 in website with website_id=3.

select * from customer_entity where website_id=3 and created_at > '2014-01-01';

Migrate the customer whose entity_id is 12345 to the website of website_id=10 and store_id=10

UPDATE customer_entity 
SET website_id='10', store_id='10' 
WHERE entity_id='123456';

Select all store infomations.

select * from core_store;

Select customers entity_id, store code, firstname, lastname email and acccount create date. Need to make sure attribute_id 5 and 7 are for firstname and lastname respectively.

select ce.entity_id, cs.code as storecode, 
cevar1.value as firstname, cevar2.value as lastname, ce.email, ce.created_at 
from customer_entity ce
left join core_store cs on ce.website_id=cs.website_id
left join customer_entity_varchar cevar1 on (ce.entity_id=cevar1.entity_id and cevar1.attribute_id=5)
left join customer_entity_varchar cevar2 on (ce.entity_id=cevar2.entity_id and cevar2.attribute_id=7);

Select attribute infomations whose attribute_code has the string “name” in it.

select * from eav_attribute where attribute_code like "%name%";

Select all customer attribute values for customer with customer id 123456.

SELECT ce.*, ea.attribute_code, 
    CASE ea.backend_type 
      WHEN 'datetime' THEN ce_datetime.value
      WHEN 'decimal' THEN ce_decimal.value
      WHEN 'int' THEN ce_int.value
      WHEN 'text' THEN ce_text.value
      WHEN 'varchar' THEN ce_varchar.value
      ELSE NULL
    END AS value
FROM customer_entity AS ce
LEFT JOIN eav_attribute AS ea ON ce.entity_type_id = ea.entity_type_id
LEFT JOIN customer_entity_decimal AS ce_decimal ON ce.entity_id = ce_decimal.entity_id AND ea.attribute_id = ce_decimal.attribute_id AND ea.backend_type = 'decimal'
LEFT JOIN customer_entity_datetime AS ce_datetime ON ce.entity_id = ce_datetime.entity_id AND ea.attribute_id = ce_datetime.attribute_id AND ea.backend_type = 'datetime'
LEFT JOIN customer_entity_int AS ce_int ON ce.entity_id = ce_int.entity_id AND ea.attribute_id = ce_int.attribute_id AND ea.backend_type = 'int'
LEFT JOIN customer_entity_text AS ce_text ON ce.entity_id = ce_text.entity_id AND ea.attribute_id = ce_text.attribute_id AND ea.backend_type = 'text'
LEFT JOIN customer_entity_varchar AS ce_varchar ON ce.entity_id = ce_varchar.entity_id AND ea.attribute_id = ce_varchar.attribute_id AND ea.backend_type = 'varchar'
where ce.entity_id=123456;