SQL Lurks Everywhere
As a user of any database-driven software program, you’re probably using SQL, even if you don’t know it. For example, a database-driven dynamic web page takes user input from forms and clicks and uses it to compose a SQL query that retrieves information from the database required to generate the next web page.Consider the example of a simple online catalog with a search function. The search page might consist of a simple form containing only a text box where the user enters a search term and a search button. Upon clicking the button, the web server retrieves any records from the product database containing the search term and uses the results to create a web page specific to that user’s request.
If a user is searching for products containing the term "Irish", the web server might use the following SQL statement to retrieve related products:
SELECT *Translated, this command retrieves any records from the database table named "products" that contain the characters "irish" anywhere within the product name.
FROM products
WHERE name LIKE '%irish%'
Data Manipulation Language
The Data Manipulation Language (DML) contains the subset of SQL commands used most frequently – those that simply manipulate the contents of a database in some form. The four most common DML commands are used to retrieve information from a database (the SELECT) command, add new information to a database (the INSERT command), modify information currently stored in a database (the UPDATE command) and remove information from a database (the DELETE command).Data Definition Language
The Data Definition Language (DDL) contains commands that are less frequently used. DDL commands modify the actual structure of a database, rather than the database’s contents. Examples of commonly used DDL commands include those used to generate a new database table (CREATE TABLE), modify the structure of a database table (ALTER TABLE), and delete a database table (DROP TABLE).Data Control Language
The Data Control Language (DCL) is used to manage user access to databases. It consists of two commands: the GRANT command, used to add database permissions for a user, and the REVOKE command, used to take away existing permissions. These two commands form the core of the relational database security model.Structure of an SQL Command
Fortunately for those of us who aren’t computers, SQL commands are designed to have a syntax very similar to the English language. They normally begin with a command statement describing the action you wish to take, then a clause that describes the target of the command (such as the specific table within a database affected by the command) and then a series of clauses that provide additional instructions.Often, simply reading an SQL statement out loud will give you a very good idea of what the command is intended to do. Take a moment to read this example of a SQL statement:
DELETECan you guess what this statement will do? It accesses the students table of the database and deletes all records for students who graduated in 2011.
FROM students
WHERE graduation_year = 2011
