Simple MySQL Cheat Sheet

MySQL Using Join
select isoid, country from astccniso iso
LEFT JOIN astccnlist lis USING (isoid)
WHERE lis.isoid is null;

LEFT JOIN b USING (c1,c2,c3)

SELECT table1.* FROM table1
LEFT JOIN table2 ON table1.id=table2.id
WHERE table2.id IS NULL;

SELECT * FROM t1 LEFT JOIN (t2, t3, t4)
ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)

Get most recently generated ID:

mysql> SELECT LAST_INSERT_ID();

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\G; SHOW CREATE TABLE astpayment\G

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:

LOAD DATA LOCAL INFILE "infile.txt" INTO TABLE table_name;
(Use \n for NULL)

LOAD DATA INFILE '/etc/people.txt'
INTO TABLE people FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' IGNORE 1 LINES
(`internNum`, `lastName`,`firstName`, `funksjon`,`enhet`, `mobile`, `forward1`,`forward2`,@tmp);

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 just one record:

mysql> UPDATE table SET value = "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;

LEFT JOIN

SELECT co.cid, CONCAT_WS(' ', co.last_name, co.first_name) AS name,
co.phone as phone, co.mail as mail, ca.name as category
FROM (pbx_contact_directory co LEFT JOIN pbx_contact_category ca ON co.category = ca.cid) WHERE co.uid = 1;

select ccn, rategroup, rateplan,traffictype
from astccnlist lis LEFT JOIN astccnrate rat using (ccn)
where countrycode = 47 and (rateplan = 'cbktele' or rateplan is null)
order by ccn;

SELECT paid, pay.accountcode, paytype, pay.comment, paidamount, date(paiddate) paiddate,
date(pay.date_created) date_created, users.name, astaccount.uid
FROM astpayment pay LEFT JOIN astaccount ON pay.accountcode = astaccount.accountcode
LEFT JOIN cbk_users users ON astaccount.uid = users.uid;

SELECT paid, pay.accountcode, paytype, pay.comment, paidamount, date(paiddate) paiddate,
date(pay.date_created) date_created, users.name, astaccount.uid
FROM astpayment pay LEFT JOIN (astaccount,cbk_users users) ON
(pay.accountcode = astaccount.accountcode and astaccount.uid = users.uid);

SELECT * FROM t1 LEFT JOIN (t2, t3, t4)
ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)

If there is no matching row for the right table in the ON or USING part in a LEFT JOIN, a row with all
columns set to NULL is used for the right table. You can use this fact
to find rows in a table that have no counterpart in another table:

SELECT table1.* FROM table1
LEFT JOIN table2 ON table1.id=table2.id
WHERE table2.id IS NULL;

This example finds all rows in table1 with an id value that is not present in table2
(that is, all rows in table1 with no corresponding row in table2). This assumes
that table2.id is declared NOT NULL. See Section 7.2.9, “LEFT JOIN and RIGHT JOIN Optimization”.

CREATE TABLE IF NOT EXISTS orgevents (
`eventid` int(5) unsigned NOT NULL auto_increment,
`event_from_date` datetime NOT NULL default '0000-00-00',
`event_to_date` datetime NOT NULL default '0000-00-00',
`event_name` varchar(256) NOT NULL default '',
`event_location` varchar(256) NOT NULL default '',
`event_area` varchar(256) NOT NULL default '',
`event_description` varchar(14000) NOT NULL default '',
`tsc` timestamp NOT NULL default '0000-00-00',
ts timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`eventid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE OR REPLACE VIEW asv_iaxfixip AS
SELECT * from asv_iax where host != 'dynamic';

ALTER TABLE astinvoice ADD rateid int(6) unsigned NOT NULL default '0' AFTER billingid;

alter table astccnrate ADD key (rateid);

ALTER TABLE astworkflow1 ADD PRIMARY KEY (workflow);

ALTER TABLE astupload ADD `tsc` timestamp NOT NULL default CURRENT_TIMESTAMP;

If you have a bunch of data that you need to concatenate, take a look at MySQL's GROUP_CONCAT function.
select group_concat(phonenumber SEPARATOR ',') FROM astnrdb limit 10;