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:

• SQL Quick Help