Podemos “comentar” un trozo de una consulta o query “comentándolo”:

mysql> SELECT field1, field2 /* ,field3 */ FROM table

Cuando aliaseamos una columna, podemos volver a usarla usando (SELECT alias) en los casos en los que “no trague”

mysql> SELECT whatever AS myfield, (5 + (SELECT myfield)) AS mycalc

Obtener los dominios más frecuentes a partir de una lista de emails:

mysql> SELECT DISTINCT SUBSTRING_INDEX(email, '@', -1) AS domaintld, COUNT(*) AS cuantos FROM tabladelistademails GROUP BY domaintld ORDER BY cuantos DESC LIMIT 10;

Reemplazar cadenas en columnas

mysql> update namefeed_provinces SET field=REPLACE(field, 'search', 'newstring') where country='in'

Obtener el tamaño de todas las bases de datos:

mysql> 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;

Obtener el tamaño de todas las TABLAS de una base de datos (cambiar databasename por el nombre de la base deseada)

mysql> SELECT table_name AS "Table", ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)" FROM information_schema.TABLES WHERE table_schema = "databasename" ORDER BY (data_length + index_length) DESC;

Dumpear solo las estructuras de una base:

mysqldump -q -u root -p --no-data --add-locks --skip-opt --quick --extended-insert --lock-tables --set-charset --disable-keys databasename > databasename-tables-struct-only.sql

Dump a .gz una tabla con where:

$ mysqldump -uroot dbname tablename -w "field >= '2010-01-01 00:00:00'" | gzip -9 > table2010rows.sql.gz

Importar una tabla desde un .gz:

$ zcat tabledump.sql.gz | mysql [...] --default-character-set=utf8 -uroot dbname

Dump a .bz2:

$ mysqldump -q -uroot dbname tablename | bzip2 -cq9 > crunched-table.sql.bz2

Special dump:

$ mysqldump --skip-opt --set-charset -q -uroot -p -h host --add-locks --create-options --hex-blob --complete-insert dbname tablename -w "field >= '2011-01-01 00:00:00'" | bzip2 -cq9 > table2011rows.sql.bz2

Note --skip-opt that disables the default --opt so it disables --set-charset, --add-drop-table, --add-locks, --create-options, --quick, --extended-insert, --lock-tables, and --disable-keys.
Note --add-locks --create-options
Note --hex-blob so blobs are written as hex

Importar desde .bz2

$ bunzip2 < table2011rows.sql.bz2 | mysql --force --default-character-set=utf8 -uroot dbname

Note --force in order to NOT stop on errors (i.e. create table, insert with duplicate key, etc). This way we guarantee the addition of new rows when doing the import

This will check, repair and optimize all tables in all databases

$ mysqlcheck -u root -p -h db1 --auto-repair --check --optimize --all-databases

This will check, repair, optimize all tables in “dbname”

$ mysqlcheck -u root -p -h db1 --auto-repair --check --optimize dbname