SELECT USER FROM mysql.user;

Sets the password secretpassword for user someuser.

UPDATE mysql.user SET password=PASSWORD('secretpassword') WHERE USER='someuser';

SELECT table_schema "Database", Round(SUM(data_length + index_length) / 1024 / 1024, 1) "MB" FROM information_schema.tables GROUP  BY table_schema;

The following query, selects one random entry from the table table.

SELECT * FROM TABLE ORDER BY RAND()  LIMIT 1

This query will select lastname and firstname from the table employees with the condition that the first character of lastname match either of the uppercase letters M, B or T:

SELECT lastname, firstname FROM employees WHERE lastname REGEXP  '^(M|B|T)';

A structurally simple format is the CSV format that is supported by many data processing software packages (Excel, etc…) and MySQL can both import and export from CSV.

This line exports data from the table account to the file /tmp/dump.csv in a comma-separated value format.

SELECT * FROM account INTO OUTFILE '/tmp/dump.csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n';

Imports are preformed symmetrically, by preserving the FIELD and LINES setting:

LOAD DATA INFILE '/tmp/dump.csv' INTO TABLE account FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n';

To dump a database to a file, issue:

mysqldump -u root -p database > database.sql

to restore a database from a file, issue:

mysqldump -u root -p database < database.sql

Given the following table:

we want to find out the total score for Ty Ass. We can then write the SQL query:

SELECT Name,SUM(Score) AS Total FROM TABLE WHERE Name='Ty Ass';

which will return the following table:

The same can be done if the scores are distributed on multiple columns:

To find out the total, summative score for Amanda Lay, we use the query:

SELECT Name,(Crouched + Sideways + Laying) AS Total FROM TABLE WHERE Name='Amanda Lay';

which will return the table:

To empty the database mydatabase, issue:

SELECT CONCAT('DROP TABLE IF EXISTS ', TABLE_NAME, ';') FROM information_schema.tables WHERE table_schema = 'mydatabase';

mysql -Be "select concat('repair table ', TABLE_SCHEMA, '.', table_name, ';') from information_schema.tables where table_schema<>'information_schema';" | tail -n+2 | mysql

or

mysqlcheck --auto-repair -A

First, stop MySQL using:

/etc/init.d/mysql stop

Now we run MySQL by omitting to read the grant tables:

mysqld --skip-grant-tables

And we log-in as root:

mysql -u root

Then we run a query to replace the root password:

FLUSH PRIVILEGES;
UPDATE mysql.user SET Password=PASSWORD('password') WHERE USER='root';
FLUSH PRIVILEGES;
exit

And after that we restart MySQL:

/etc/init.d/mysql restart

In a directory containing .sql files, run:

ls -1 *.sql | awk '{ print "source",$0 }' | mysql --batch -u username --password=password database

where:

• username is the user name allowed to import
• password is the password for the user
• database is the database to import the data to

This can be accomplished with:

mysql -u USER --password=PASSWORD -Nse 'SHOW TABLES' DATABASE | while read table; do mysql -u USER --password=PASSWORD -e "DROP TABLE $table" DATABASE; done

where:

• USER is the username to use
• PASSWORD is the password for the user
• DATABASE is the database to drop all the tables in

This can be done with:

mysql -u USER --password=PASSWORD -Nse 'show tables' DATABASE | while read table; do mysql -u USER --password=PASSWORD -e "TRUNCATE TABLE $table" DATABASE; done

where:

• USER is the username to use
• PASSWORD is the password for the user
• DATABASE is the database to truncate all the tables in