Chapter 5 - Chapter summary

Output

Quick SQL Reference

Create a database
CREATE DATABASE databaseName
		CHARACTER SET uft8
		COLLATE utf8_general_ci;
Create a table
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 ;
Add data to a table
INSERT INTO tableName (col1,col3,col7) VALUES(val1,val3,val7);
INSERT INTO tableName VALUES(null,val2,val3,null,val5,val6);
Update a record
UPDATE tableName SET col1=newval1,col2=newval2 WHERE colX=valX;
Delete a record
DELETE FROM tableName WHERE col=val AND col1<val1 LIMIT 3;
Select data from the table
SELECT tableName.tableColName FROM tableName;
Search for a value in a table
SELECT tableName.tableColName FROM tableName WHERE colx LIKE _al%;
Sort the returned results
SELECT tableName.tableColName FROM tableName ORDER BY colName DESC, col2Name ASC;
Restrict data from the table based on certain conditions
SELECT * FROM tableName WHERE colx>val OR coly BETWEEN 10 AND 25;
Limit the number of results returned from the query
SELECT * FROM tableName ORDER BY colName DESC LIMIT 1;
Create an Alias
SELECT col1 as Alias1, col2 as Alias2 FROM tableName WHERE Alias2="string";

Common SQL Functions

  • NOW()
  • SHA1()
  • CONCAT(x,y..) & CONCAT_WS(S,x,y...)
  • LENGTH()
  • REPLACE(haystack,needle,replacement)
  • UPPER() & LOWER()
  • CEILING() & FLOOR()
  • ROUND(number,number_of_decimals)
  • RAND()
  • FORMAT(number,number_of_decimals)
  • MONTH(), MONTHNAME(), YEAR()
  • DAY(), DAYNAME(), DAYOFMONTH()
  • HOUR(), MINUTE(), SECOND()
  • CURDATE() & CURTIME()
  • SUM()
more Functions
Implementation
Source
<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&lt;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&gt;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..) &amp; CONCAT_WS(S,x,y...)</li>
	<li>LENGTH()</li>
	<li>REPLACE(haystack,needle,replacement)</li>
	<li>UPPER() &amp; LOWER()</li>
	<li>CEILING() &amp; 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() &amp; 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>