Sql Reference

Hello this time around I will make a reference guide for SQL as I develop my site Im using MySQL database for this. This is for my reference and you whoever it is whose reading. Enjoy!

- SELECT [field1, field2 or * ] FROM tablename WHERE field = 1 or field ='1' are the same.
- SELECT [fields] FROM tablename LEFT JOIN tablename2 ON field1 = field2 ORDER BY fieldname [ASC or DESC] : Joins table that has the value of the left table. Will return values from the original table but return NULL values from connecting tables if there is no matching value. Opposite of RIGHT JOIN.
- SELECT ... FROM tablename JOIN tablename2 ON field1=field2 : Will return only matching pairs of record from both tables.
- SELECT ... FROM tablename ORDER BY fieldname ASC LIMIT 0,10 : Limits the record to be return.
- INSERT INTO tablename (customer,day_of_order,product, quantity) VALUES('value1','8/1/08','Stapler',1): Normal inserting of record.
- INSERT INTO tablename VALUES('value1','value2') : Shortcut for inserting.
- UPDATE tablename SET fieldname='2' WHERE fieldname='2' for updating a record.
- UPDATE tablename SET fieldname=2,fieldname2="name" WHERE fieldname=2: for updating multiple fields.
- UPDATE tablename SET fieldname=1: Will update all records please be careful of this!
- SELECT ... FROM tablename WHERE id IN(2,4,5): multiple look up.
- SELECT SQL_SQL_CALC_FOUND_ROWS fieldname1, ... FROM tablename: Allows to get ALL record account even though it is limited by the LIMIT use this sql to get the all record counts. 'SELECT FOUND_ROWS() AS count'.
- SELECT name, COUNT(name) AS counted FROM tablename GROUP BY name; Group unique values and can be counted. You can use other expressions to take advantage of group by such as SUM, AVG, MAX, MIN, LEN, ROUND
- SELECT .. FROM .. GROUP BY fieldname1 HAVING fieldname2<100
- IS NULL : SELECT ... FROM tablename WHERE fieldname IS NULL or IS NOT NULL.
- SELECT RANDOM records to retrieve use this "ORDER BY RAND()" in your sql query eg. "SELECT *FROM tablename ORDER BY RAND() LIMIT 0,10"

SQL Functions
- SUM(column_name) - Total sum of the column that are in usually a number.
- AVG(column_name) - Total avg of the column numbers.
- MID(column_name, start[,length]) - Extract character from the column kinda like SUBSTR.
- LEN(column_name) - Length of characters in the text.
- NOW() - Returns date and time of the current system formatted like this 10/7/2008 11:25:02 AM.
- UCASE(column_name)/LCASE(column_name) : make a text to uppercase or lowercase.
- FIRST(column_name) : Get the first value of the column from list of records returned.
- LAST(column_name): Get the last value of the column from the list of records returned.