SQL is how you communicate with a relational database system, such as SQL Server, MySQL, or SQL Anywhere.  SQL stands for Structured Query Language.  The following sections will guide you through the basics of SQL used in retrieving, updating, and deleting data from a database.

The Select Statement

The select statement is used to retrieve data from a database.  Select is the first SQL keyword used when retrieving the data.  Let's take a look at a simple example.

SELECT * FROM TABLE

In this example, all columns will be retrieved from the table.  In the location of the *, column names, separated by commas, can be placed to select individual columns, rather than all columns.  This feature is demonstrated in the next example.

The From Clause

From is used to determine what table to retrieve data from.  In the example above, we are selecting from a table named "table".

The Where Clause

Where is used to limit the result set retrieved from a table.  While the where clause is not required, it is used very often to retrieve the smallest result subset of data necessary.  The following example demonstrates the use of the where clause.

SELECT column1, column2, column3 FROM tablename WHERE column2 = 'Test String' AND column3 IS NOT NULL

In the example above, the conditions to limit the result set can be compared in a variety of ways.  The first comparison uses the equals operator to test where the value of column2 is equal to Test String.  In SQL, strings are enclosed in single quotes.  If single quotes are included in the string, a second single quote is used to escape the single quote character.  The second comparison will match values in column3 where the value is not equal to null.  Null is used to denote an empty column that has no value.

The Order By Clause

Order by is used to sort the result set into a format usable by your application.  Let's take a look at an example:

SELECT * FROM tablename ORDER BY column1 ASC, column4 DESC

In the example above, the result is sorted first in ascending order by column1, and then in descending order by column 4.  The order by clause always comes after the where clause.

The Update Statement

The update statement is used to change the value of existing data in a database.  The following is a simple example:

UPDATE tablename SET column1 = 5 WHERE id = 8

The table to update the data follows the update statement.  The set keyword is used to denote what data to update and in which columns.  The where clause is critical in an update statement.  It determines what rows will be updated.  If the where clause is omitted, all rows will be updated.

The Delete Statement

The delete statement is used to remove rows of data from a database table.  In the example below, the row with an id value of 8 will be removed.

DELETE FROM tablename WHERE id = 8

As with the update statement, the where clause is critical so you do not delete more rows than intended.  If the where clause is omitted, all data from the table will be removed!

Resources To Learn More

MySQL

• MySQL website - http://www.mysql.com/

SQL Server

• SQL Server website - http://www.microsoft.com/sqlserver/

For the many other relational database systems in existence, refer to the documentation that came with the database software or visit the company website to learn more.