!!!MySql Tips
We often integrate with various databases and are often stumbling around for the right syntax. This is our [Cheat Sheet] for [MySQL] commands that we have found useful.

The UPPERCASE Values preceded by $ are names of values that would be substituted.
!!Loging in
{{{
mysql --user=$USERNAME --password=$PASSWORD --host=$DBHOST
}}}

!!Adding users
The following will allow the user to have all privileges but can only login to the host from the console. (ie. no remote connections)
{{{
GRANT ALL PRIVILEGES ON *.* TO '$USERNAME'@'localhost' IDENTIFIED BY '$PASSWORD' WITH GRANT OPTION;
}}}

The following will allow the user to login from any host.
{{{
GRANT ALL PRIVILEGES ON *.* TO '$USERNAME'@'%' IDENTIFIED BY '$PASSWORD' WITH GRANT OPTION;
}}}

!!Selecting a database:
{{{
USE $DATABASE;
}}}

!!Listing databases:
{{{
SHOW DATABASES;
}}}

!!Listing tables in a db:
{{{
SHOW TABLES;
}}}

!!Describing the format of a table:
{{{
DESCRIBE $TABLE;
}}}

!!Creating a database:
{{{
CREATE DATABASE $DATABASE;
}}}

!!Creating a table:
{{{
CREATE TABLE $TABLE ($FIELDNAMEONE TYPE(SIZE), $FIELDNAMETWO TYPE(SIZE));

Ex: mysql> CREATE TABLE pets (name VARCHAR(20), sex CHAR(1), birth DATE);
}}}

!!Load tab-delimited data into a table:
{{{
LOAD DATA LOCAL INFILE "infile.txt" INTO TABLE $TABLE;
(Use \n for NULL)
}}}

!!Inserting one row at a time:
{{{
INSERT INTO $TABLE VALUES ('MyName', 'MyOwner', '2002-08-31');
(Use NULL for NULL)
}}}

!!!Retrieving information (general):
Retrieving specified Columns
{{{
SELECT $COLUMNNAME FROM $TABLE WHERE $CONDITIONS;
}}}

!!All values: 
{{{
SELECT * FROM $TABLE;
}}}

!!Some values: 
{{{
SELECT * FROM $TABLE WHERE $COLUMNNAME = "$VALUE";
}}}

!!Multiple critera
{{{
SELECT * FROM $TABLE WHERE $COLUMNNAMEONE = "$VALUE1" AND $COLUMNNAMETWO = "$VALUE2";
}}}

!!Reloading a new data set into existing table
{{{
SET AUTOCOMMIT=1; # used for quick recreation of table

DELETE FROM $TABLE;
LOAD DATA LOCAL INFILE "infile.txt" INTO TABLE $TABLE;
}}}

!!Fixing all records with a certain value
{{{
UPDATE $TABLE SET $COLUMNNAME = "$VALUEONE" WHERE $COLUMNNAME = "$VALUE2";
}}}

!!Retrieving unique output records
{{{
SELECT DISTINCT $COLUMNNAME FROM $TABLE;
}}}

!!Sorting
{{{
SELECT $COLUMNNAME1, $COLUMNNAME2 FROM $TABLE ORDER BY $COLUMNNAME2;

!!Sorting Backwards
{{{
SELECT $COLUMNNAME1, $COLUMNNAME2 FROM $TABLE ORDER BY $COLUMNNAME2 DESC;
}}}

!!Date calculations
{{{
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
{{{
SELECT * FROM $TABLE WHERE $COLUMNNAME LIKE "blah%";
(% is wildcard - arbitrary # of chars)

Find 5-char values: SELECT * FROM $TABLE WHERE $COLUMNNAME like "_____";
(_ is any single character)

Extended Regular Expression Matching

SELECT * FROM $TABLE WHERE $COLUMNNAME 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
{{{
SELECT COUNT(*) FROM $TABLE;
}}}

!!Grouping with Counting
{{{
SELECT $COLUMNNAME, COUNT(*) FROM $TABLE GROUP BY $COLUMNNAME;
(GROUP BY groups together all records for each 'owner')
}}}

!!Selecting from multiple tables
{{{
SELECT $TABLE1.$COLUMNNAME1, $VALUE1 FROM $TABLE, $TABLE2 WHERE $TABLE1.$COLUMNNAME1 = TABLE2.VALUE2;
SELECT pet.name, comment FROM pet, event WHERE pet.name = event.name;
These just do not look right........
(You can join a $TABLE to itself to compare by using 'AS')
}}}

!!Currently selected database
{{{
SELECT DATABASE();
}}}

!!Maximum value
{{{
SELECT MAX($COLUMNNAME) AS $LABLENAME FROM $TABLE;
}}}

!!Auto-incrementing rows
{{{
CREATE TABLE $TABLE ($COLUMNNAME1 INT NOT NULL AUTO_INCREMENT, $COLUMNNAME2 CHAR(10) NOT NULL);

INSERT INTO $TABLE ($COLUMNNAME2) VALUES ("tom"),("dick"),("harry");
}}}

!!Adding a column to an already-created table
{{{
ALTER TABLE $TABLE ADD COLUMN [$NEWCOLUMNNAME syntax(int, char etc)] AFTER $COLUMNNAME;
}}}

!!Removing a column
{{{
ALTER $TABLE tbl DROP COLUMN col;
(Full ALTER $TABLE syntax available at mysql.com.)
}}}

!!Batch mode (feeding in a script)
{{{
# mysql --user=$USERNAME --password=$PASSWORD < batch_file
(Use -t for nice table layout and -vvv for command echoing.)
}}}

!!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.)
}}}

!! More Information
There might be more information for this subject on one of the following:
[{ReferringPagesPlugin before='*' after='\n' }]