SELECT concat(table_schema,'.',table_name),
concat(round(table_rows/1000000,2),'M') rows,
concat(round(data_length/(1024*1024*1024),2),'G') DATA,
concat(round(index_length/(1024*1024*1024),2),'G') idx,
concat(round((data_length+index_length)/(1024*1024*1024),2),'G') total_size,
round(index_length/data_length,2) idxfrac 
FROM information_schema.TABLES 
ORDER BY data_length+index_length DESC LIMIT 20;

select *,
	(3963.1676 * acos(cos(radians(@latitude)) * cos(radians(latitude)) *
		cos(radians(longitude) - radians(@longitude)) +
		sin(radians(@latitude)) * sin(radians(latitude)))) as distance
from geoname
where latitude between (@latitude - 1) and (@latitude + 1)
and longitude between (@longitude - 1) and (@longitude + 1)
order by distance asc

# Change all aliases with paths like foo/* to bar/* 
UPDATE url_alias SET dst = REPLACE(dst, 'foo/', 'bar/') WHERE dst LIKE 'foo/%';


# Delete all aliases with paths like foo/*
DELETE FROM `url_alias` WHERE dst LIKE 'category/%';

ALTER TABLE pages_htmlpage ADD sidebar_image VARCHAR(100) NULL;

mysql_query("DELETE FROM `wp_posts` WHERE `post_status` = 'revision'");

# This will create and then populate a MySQL table with a list of the names and
# ISO code abbreviations for the provinces and territories of Canada.


CREATE TABLE `ca_provinces` (
    `id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
    `name` VARCHAR( 255 ) NOT NULL ,
    `iso` CHAR( 2 ) NOT NULL
);

INSERT INTO `ca_provinces` (`id`, `name`, `iso`)
VALUES 
    (NULL, 'Alberta', 'AB'),
    (NULL, 'British Columbia', 'BC'),
    (NULL, 'Manitoba', 'MB'),
    (NULL, 'New Brunswick', 'NB'),
    (NULL, 'Newfoundland and Labrador', 'NL'),
    (NULL, 'Northwest Territories', 'NT'),
    (NULL, 'Nova Scotia', 'NS'),
    (NULL, 'Nunavut', 'NU'),
    (NULL, 'Ontario', 'ON'),
    (NULL, 'Prince Edward Island', 'PE'),
    (NULL, 'Quebec', 'QC'),
    (NULL, 'Saskatchewan', 'SK'),
    (NULL, 'Yukon', 'YT');

PURGE BINARY LOGS BEFORE 'yyyy-mm-dd';

Retrieving a Full Tree

The first common task when dealing with hierarchical data is the display of the entire tree, usually with some form of indentation. The most common way of doing this is in pure SQL is through the use of a self-join:


SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parent = t1.category_id
LEFT JOIN category AS t3 ON t3.parent = t2.category_id
LEFT JOIN category AS t4 ON t4.parent = t3.category_id
WHERE t1.name = 'ELECTRONICS';


TABLE STRUCTURE AS AN EXAMPLE 
===============================

CREATE TABLE category(
category_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20) NOT NULL,
parent INT DEFAULT NULL);


INSERT INTO category
VALUES(1,'ELECTRONICS',NULL),(2,'TELEVISIONS',1),(3,'TUBE',2),
(4,'LCD',2),(5,'PLASMA',2),(6,'PORTABLE ELECTRONICS',1),
(7,'MP3 PLAYERS',6),(8,'FLASH',7),
(9,'CD PLAYERS',6),(10,'2 WAY RADIOS',6);

CREATE TABLE users(
user_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT ,
first_name VARCHAR( 20 ) NOT NULL ,
last_name VARCHAR( 40 ) NOT NULL ,
email VARCHAR( 60 ) NOT NULL ,
pass CHAR( 40 ) NOT NULL ,
registration_date DATETIME NOT NULL ,
PRIMARY KEY ( user_id )
)

SELECT
	face_image,
	first_name,
	last_name,
	phone_number
FROM
	humanity
WHERE
	gender = 'woman' -- Под понятие идеальной девушки попадает только женский пол, я думаю…
	
	AND NOT married -- не замужем, не женатая
	AND (SELECT COUNT(*) FROM humanity WHERE mother_id = humanity.id) = 0 -- без детей
	AND social_status IN ('student', 'employee', 'business lady') -- не сидит впросидь дома
	
	AND weight / POW(height, 2) BETWEEN 17 AND 22 -- Индекс Массы Тела
	AND breast_size BETWEEN 2 AND 4 -- Рамер груди
	
	AND iq_index BETWEEN 95 AND 110 -- Не глупая, но и не перегибая палку
	AND (SELECT COUNT(DISTINCT word) FROM spoken_words WHERE human_id = humanity.id) > 6000 -- Есть, о чём поговорить
	
	AND cooking_level /*+ football_interesting*/ + computer_education > /*220*/ 180 -- Любые комбинации приветствуются
	
	AND (
		SELECT
			COUNT(*)
		FROM
			books
			JOIN bag_content ON bag_content.object_id = books.id AND bag_content.object_type = 'books'
			JOIN bag ON bag.id = bag_content.bag_id
		WHERE
			bag.owner_id = humanity.id
			AND books.author_last_name = 'Донцова'
	) = 0 -- Без комментариев
	
	AND NOT smoking -- !!!