Tired of those wide tables that wrap? You can end your query with a \G to get output like this:

mysql> select * from users\G
*************************** 1. row ***************************
     id: 1
   name: xinu
created: 20050115210745
*************************** 2. row ***************************
     id: 2
   name: cygnus
created: 20050115210803
2 rows in set (0.00 sec)

Dumping an entire database is easy, but dumping a single table requires a few options:

$ mysqldump --opt -u <username> -p <database> <table> > outfile.sql

Consider the following schema:

CREATE TABLE loadtest (
    pkey int(11) NOT NULL auto_increment,
    name varchar(20),
    exam int,
    score int,
    time_enter timestamp(14),
    PRIMARY KEY (pkey),
);

And the data you need to load:

'name22999990',2,94
'name22999991',3,93
'name22999992',0,91

Running this query would load the data into the columns name, exam, score:

mysql> LOAD DATA INFILE '/tmp/out.txt' INTO TABLE loadtest
    -> FIELDS TERMINATED BY ',' (name,exam,score);

** Note: This tip borrowed from the Linux Gazette. You can read the complete article at http://www.linuxgazette.com/node/9059.

To create a new database:

$ mysqladmin create <database_name>

To grant permissions to a user, run this:

$ mysql -u root -p
Password: (enter password)
mysql> GRANT ALL ON db_name.* TO username@localhost IDENTIFIED BY "userpasswd";

To flush the privilege tables, run this:

$ mysqladmin flush-privileges

or:

mysql> flush privileges;

To revoke the privileges from a particular user/host pair:

mysql> revoke all privileges, grant option from username;

In your ~/.psqlrc, add this to emulate MySQL-style borders in query results:

\pset border 2

To log into MySQL using the CLI in windows:

(in the mysql\bin directory)
c:\mysql\bin> mysql --user=<user> --pass=<pass> --port=3306

It is sometimes useful when working with SQL to grab random rowsets, for debugging stored procedures or simply grabbing a random ad for an ad rotator. Either way, different databases use different functions to select this randomization.

• Select a random row with MySQL:

  SELECT * FROM mytable
  ORDER BY RAND()
  LIMIT 1;
  

• Select a random row with PostgreSQL:

  SELECT * FROM mytable
  ORDER BY RANDOM()
  LIMIT 1;
  

• Select a random row with Microsoft SQL Server:

  SELECT TOP 1 column FROM table
  ORDER BY NEWID();
  

Deleting all the rows from the table will leave you adding rows where the last one left off. To bring it back to the beginning, run truncate tablename.

A quick way to see all of the databases on the system and (optionally) the number of tables in each:

$ mysqlshow

Use the -v flag to include number of tables.

In MySQL you can describe tables to get their columns and types, but if you need to get the SQL that created them, you can run the following:

mysql> show create table <table_or_view_name>;