mySQL UNIX cheat sheet

Selecting a database:
 
mysql> USE database;
 
Listing DATABASES:
 
mysql> SHOW DATABASES;
 
Listing tables IN a db:
 
mysql> SHOW TABLES;
 
Describing the FORMAT of a table:
 
mysql> DESCRIBE table;
 
Creating a database:
 
mysql> CREATE DATABASE db_name;
 
Creating a table:
 
mysql> CREATE TABLE table_name (field1_name TYPE(SIZE), field2_name TYPE(SIZE));
Ex: mysql> CREATE TABLE pet (name VARCHAR(20), sex CHAR(1), birth DATE);
 
Load tab-delimited data INTO a table:
 
mysql> LOAD DATA LOCAL INFILE "infile.txt" INTO TABLE table_name;
(USE \n for NULL)
 
Inserting one row at a TIME:
 
mysql> INSERT INTO table_name VALUES ('MyName', 'MyOwner', '2002-08-31');
(USE NULL for NULL)
 
Retrieving information (general):
 
mysql> SELECT from_columns FROM table WHERE conditions;
All VALUES: SELECT * FROM table;
SOME VALUES: SELECT * FROM table WHERE rec_name = "value";
Multiple critera: SELECT * FROM TABLE WHERE rec1 = "value1" AND rec2 = "value2";
 
Reloading a new data SET INTO existing table:
 
mysql> SET AUTOCOMMIT=1; # used for quick recreation of table
mysql> DELETE FROM pet;
mysql> LOAD DATA LOCAL INFILE "infile.txt" INTO TABLE table;
 
Fixing all records with a certain value:
 
mysql> UPDATE table SET column_name = "new_value" WHERE record_name = "value";
 
Selecting specific COLUMNS:
 
mysql> SELECT column_name FROM table;
 
Retrieving UNIQUE output records:
 
mysql> SELECT DISTINCT column_name FROM table;
 
Sorting:
 
mysql> SELECT col1, col2 FROM table ORDER BY col2;
Backwards: SELECT col1, col2 FROM table ORDER BY col2 DESC;
 
DATE calculations:
 
mysql> SELECT CURRENT_DATE, (YEAR(CURRENT_DATE)-YEAR(date_col)) AS time_diff [FROM table];
MONTH(some_date) extracts the MONTH value AND DAYOFMONTH() extracts DAY.
 
Pattern Matching:
 
mysql> SELECT * FROM table WHERE rec LIKE "blah%";
(% IS wildcard - arbitrary # of chars)
Find 5-CHAR VALUES: SELECT * FROM table WHERE rec LIKE "_____";
(_ IS any single character)
 
Extended Regular Expression Matching:
 
mysql> SELECT * FROM table WHERE rec RLIKE "^b$";
(. for CHAR, [...] for CHAR class, * for 0 OR more instances
^ for beginning, {n} for REPEAT n times, AND $ for END)
(RLIKE OR REGEXP)
To FORCE case-sensitivity, USE "REGEXP BINARY"
 
Counting Rows:
 
mysql> SELECT COUNT(*) FROM table;
 
Grouping with Counting:
 
mysql> SELECT owner, COUNT(*) FROM table GROUP BY owner;
(GROUP BY groups together all records for each 'owner')
 
Selecting FROM multiple tables:
 
(Example)
mysql> SELECT pet.name, comment FROM pet, event WHERE pet.name = event.name;
(You can JOIN a table to itself to compare BY USING 'AS')
 
Currently selected database:
 
mysql> SELECT DATABASE();
 
Maximum value:
 
mysql> SELECT MAX(col_name) AS label FROM table;
 
Auto-incrementing rows:
 
mysql> CREATE TABLE table (number INT NOT NULL AUTO_INCREMENT, name CHAR(10) NOT NULL);
mysql> INSERT INTO table (name) VALUES ("tom"),("dick"),("harry");
 
Adding a COLUMN to an already-created table:
 
mysql> ALTER TABLE tbl ADD COLUMN [column_create syntax] AFTER col_name;
 
Removing a COLUMN:
 
mysql> ALTER TABLE tbl DROP COLUMN col;
(Full ALTER TABLE syntax available at mysql.com.)
 
Batch mode (feeding IN a script):
 
# mysql -u user -p < batch_file
(USE -t for nice table layout AND -vvv for command echoing.)
Alternatively: mysql> source batch_file;
 
Backing up a database with mysqldump:
 
# mysqldump --opt -u username -p database > database_backup.sql
(USE 'mysqldump --opt --all-databases > all_backup.sql' to backup everything.)
(More info at MySQL's docs.)