SELECT

SELECT * FROM tablename

returns all data from tablename

SELECT COUNT(columnA) FROM tablename

returns count of rows in tablename

SELECT COUNT(DISTINCT columnA) FROM tablename

returns count of distinct values in columnA

SELECT COUNT(*)/COUNT(DISTINCT columnA) FROM tablename

returns average occurence of distinct values from columnA in tablename

SELECT columnA FROM tablename ORDER BY columnA

returns ordered list of values in columnA. Default sort is ascending (ASC) order. Use DESC for reverse sort.

SELECT SQL_CALC_FOUND_ROWS columnA FROM tablename LIMIT 2; SELECT FOUND_ROWS();

SQL_CALC_FOUND_ROWS option allows use of FOUND_ROWS() function to determine the number of rows that would have been returned without LIMIT option

SELECT tableA.id, FROM tableA WHERE date IS NULL;

select fields with null values

SELECT tableA.id, FROM tableA WHERE date IS NOT NULL;

select fields with non-null values

WHERE

SELECT columnA FROM tablename WHERE columnA >='J'

returns all values from columnA where first letter is greater than or equal to 'J'

SELECT columnA FROM tablename WHERE columnA like '%gh%'

returns all values from columna containing character string 'gh'. Comparisons with LIKE are unable to make use of indexes and therefore can be extremely slow

SELECT columnA FROM tablename WHERE columnB in (1,3,9)

returns all values from columnA where value of columnB is either 1, 3 or 9

SELECT columnA FROM tablename WHERE ISNULL(columnB)

returns all values of columnA where columnB has a NULL value

JOINS

SELECT columnA, column1 FROM tableA, tableB WHERE tableA.myID = tableB.myID

SELECT columnA, column1 FROM tableA LEFT JOIN tableB ON tableA.myID = tablB.myID

SELECT columnA, column1 FROM tableA LEFT JOIN tableB USING(myID)

Three equivalent selects that return values from tables A and B with matching values in the myID column.

SELECT columnA, column1 FROM tableA, rel_A_B, tableB WHERE tableA.aID = rel_A_B.aID AND tableB.bID = rel_A_B.bID ORDER BY columnA

joins three tables

SELECT DISTINCT columnA, column1 FROM tableA, tableB, tableC, tableD WHERE tableA.aID = tableB.aID AND tableC.cID = tableB.cID AND tableD.dID = tableA.dID

joins four tables

SELECT p1.firstname, p1.surname, p1.address FROM table as p1, table as p2 WHERE p1.surname = p2.surname AND p1.id != p2.id ORDER BY p1.surname;

self-joining a table

UNION

SELECT * table1998 UNION SELECT * FROM table1999

returns all data from tables table1998 and table1999

(SELECT * FROM tableA WHERE columnA LIKE 'A%' ORDER BY columnA LIMIT 10) UNION (SELECT * FROM tableB WHERE columnB = 'YES' ORDER BY columnA LIMIT 5) ORDER BY columnC LIMIT 5

returns five rows from a possible maximum of 15 rows returned from combined query on two tables

GROUP BY

SELECT columnA, COUNT(columnB) AS Bcount FROM tableA, tableB WHERE tableA.bID = tableB.bID GROUP BY columnA ORDER BY columnA

returns ordered list of columnA and count of columnB

SELECT columnA, COUNT(columnB) AS Bcount FROM tableA LEFT JOIN tableB ON tableA.bID = tableB.bID GROUP BY columnA ORDER BY Bcount DESC

returns list of columnA and count of columnB in descending order

SELECT album, GROUP_CONCAT(name ORDER BY name SEPERATOR ',') AS players, COUNT(musicians.mID) AS cnt FROM albums, musicians, rel_album_musician WHERE musicians.mID = rel_album_musician.mID AND album.aID = rel_album_musician.aID GROUP BY album.albumID HAVING cnt > 1 ORDER BY album

returns list of albums, musicians and count of musicians

SELECT country, town, street, COUNT(*) FROM orders GROUP BY country, town, street WITH ROLLUP

produces count for addresses in street, plus count for addresses in town, plus count for address in country, plus an overall count

INSERT

INSERT INTO tablename(columnA, columnC) VALUES ('Michael', 2008)

insert given values to named columns in one row

INSERT INTO tablename(columnA, columnC) VALUES ('Michael', 2008), ('Fred', 2007), ('David', 2009)

insert given values to named columns in three rows

INSERT INTO tablename VALUES (NULL, 'A', 123, '', NULL, NULL)

inserts values into tablename. Because column names are not specified, values must match ordering and number of columns in table definition

INSERT INTO table SET col1='value1', col2 = 'value2';

insert given values to named columns in one row

SELECT LAST_INSERT_ID()

returns AUTO_INCREMENT value of last INSERT command for current connection

UPDATE

UPDATE tableA SET columnA = 'Red' WHERE columnB = 2002343

updates value of columnA to 'Red' for every row where columnB has a value of '2002343'

UPDATE tableA SET columnA = 'Red'

updates value of columnA to 'Red' for every row. Could be a huge mistake - use the --i-am-a-dummy option to avoid updates/deletes without a WHERE clause.

UPDATE tableA SET columnA = columnA*1.07

adds seven percent to columnA

UPDATE tableA SET columnA = 0 ORDER BY columnB LIMIT 10

sets value of columnA to 0 for first ten records ordered by columnB

DELETE

DELETE FROM tableA WHERE columnA = 2338

deletes all rows with value '2338' in columnA

DELETE FROM albums WHERE albums.aID = rel_album_musician.aID AND musicians.mID = rel_album_musician.mID and musician.name = 'Ringo Starr'

deletes all albums for musician 'Ringo Starr'

DELETE FROM albums, rel_album_musician, musicians WHERE albums.aID = rel_album_musician.aID AND musicians.mID = rel_album_musician.mID and musician.name = 'Ringo Starr'

deletes all entries in albums table for musician 'Ringo Starr', deletes entry in musicians table and links in rel_album_musician table that linked 'Ringo Starr' to albums table

DELETE FROM tableA ORDER BY columnA DESC limit 1

deletes last record ordered by columnA from tableA