<h4>Quick SQL Reference</h4>
<dl>
<dt>Create a database</dt>
<dd><pre>CREATE DATABASE databaseName
CHARACTER SET uft8
COLLATE utf8_general_ci;</pre></dd>
<dt>Create a table</dt>
<dd><pre>CREATE TABLE tableName (
col1Name col1Datatype(length) col1Constraint[s],
col2Name col2Datatype(length) col2Constraint[s],
PRIMARY KEY(colName),
INDEX_TYPE index_name (columns),
FOREIGN KEY (fkColName) REFERENCES table (columnName)
ON DELETE action ON UPDATE action
[action=NO ACTION/CASCADE/SET NULL; only works between tables that are Innodb and same type, collation, charset]
)
ENGINE = type ;</pre></dd>
<dt>Add data to a table</dt>
<dd><pre>INSERT INTO tableName (col1,col3,col7) VALUES(val1,val3,val7);</pre>
<pre>INSERT INTO tableName VALUES(null,val2,val3,null,val5,val6);</pre></dd>
<dt>Update a record</dt>
<dd><pre>UPDATE tableName SET col1=newval1,col2=newval2 WHERE colX=valX;</pre></dd>
<dt>Delete a record</dt>
<dd><pre>DELETE FROM tableName WHERE col=val AND col1<val1 LIMIT 3;</pre></dd>
<dt>Select data from the table</dt>
<dd><pre>SELECT tableName.tableColName FROM tableName;</pre></dd>
<dt>Search for a value in a table</dt>
<dd><pre>SELECT tableName.tableColName FROM tableName WHERE colx LIKE _al%;</pre></dd>
<dt>Sort the returned results</dt>
<dd><pre>SELECT tableName.tableColName FROM tableName ORDER BY colName DESC, col2Name ASC;</pre></dd>
<dt>Restrict data from the table based on certain conditions</dt>
<dd><pre>SELECT * FROM tableName WHERE colx>val OR coly BETWEEN 10 AND 25;</pre></dd>
<dt>Limit the number of results returned from the query</dt>
<dd><pre>SELECT * FROM tableName ORDER BY colName DESC LIMIT 1;</pre></dd>
<dt>Create an Alias</dt>
<dd>SELECT col1 as Alias1, col2 as Alias2 FROM tableName WHERE Alias2="string";</dd>
</dl>
<h4>Common SQL Functions</h4>
<ul>
<li>NOW()</li>
<li>SHA1()</li>
<li>CONCAT(x,y..) & CONCAT_WS(S,x,y...)</li>
<li>LENGTH()</li>
<li>REPLACE(haystack,needle,replacement)</li>
<li>UPPER() & LOWER()</li>
<li>CEILING() & FLOOR()</li>
<li>ROUND(number,number_of_decimals)</li>
<li>RAND()</li>
<li>FORMAT(number,number_of_decimals)</li>
<li>MONTH(), MONTHNAME(), YEAR()</li>
<li>DAY(), DAYNAME(), DAYOFMONTH()</li>
<li>HOUR(), MINUTE(), SECOND()</li>
<li>CURDATE() & CURTIME()</li>
<li>SUM()</li>
</ul>
<a target="_blank" href="http://dev.mysql.com/doc/refman/5.5/en/functions.html" title="Official 5.5 Functions for MySQL">more Functions</a>
<h5>Implementation</h5>